书签 分享 收藏 举报 版权申诉 / 52
上传文档赚钱

类型数据库原理-英文课件Chapter3-The-Relational-Data-Model.ppt

  • 上传人(卖家):晟晟文业
  • 文档编号:4072741
  • 上传时间:2022-11-08
  • 格式:PPT
  • 页数:52
  • 大小:671.55KB
  • 【下载声明】
    1. 本站全部试题类文档,若标题没写含答案,则无答案;标题注明含答案的文档,主观题也可能无答案。请谨慎下单,一旦售出,不予退换。
    2. 本站全部PPT文档均不含视频和音频,PPT中出现的音频或视频标识(或文字)仅表示流程,实际无音频或视频文件。请谨慎下单,一旦售出,不予退换。
    3. 本页资料《数据库原理-英文课件Chapter3-The-Relational-Data-Model.ppt》由用户(晟晟文业)主动上传,其收益全归该用户。163文库仅提供信息存储空间,仅对该用户上传内容的表现方式做保护处理,对上传内容本身不做任何修改或编辑。 若此文所含内容侵犯了您的版权或隐私,请立即通知163文库(点击联系客服),我们立即给予删除!
    4. 请根据预览情况,自愿下载本文。本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
    5. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007及以上版本和PDF阅读器,压缩文件请下载最新的WinRAR软件解压。
    配套讲稿:

    如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。

    特殊限制:

    部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。

    关 键  词:
    数据库 原理 英文 课件 Chapter3 The Relational Data Model
    资源描述:

    1、1Database Principles2Chapter 3The Relational Data ModeluRelational ModeluFunctional Dependencies3Contentsu3.1 Basics of the Relational Modelu3.3 From E/R diagrams to Relational Designsu3.5 Functional Dependenciesu3.7 Design of Relational Database SchemasuReading GuideuExercise4The things you should

    2、knowuThe basic principle of relational database is proposed by E.F.Codd in 1970.uThe first RDBMS production is System RuThe most popular RDBMS DB2,Oracle,Ingres,Sybase,Informix,53.1 Basic of the Relational Model uThe relational model gives us a single way to represent data:as a two-dimensional table

    3、 called a relation.6uAttributes(属性)w Attributes of a relation serve us as names for the columns of the relation.w Usually,the attributes describes the meaning of entries in the column below.7uSchemas(模式)w The name of a relation and the set of attributes for a relation is called the schema for that r

    4、elation.w We show the schema for the relation with the relation name followed by a parenthesized list of its attributes.8uTuples(元组)w The rows of a relation,other than the header row containing the attributes,are called tuple.For example:w Relations,however,are sets of tuples,and it is impossible fo

    5、r a tuple to appear more than once in a given relation.9uDomains(域)w The relational model requires that each component of each tuple be atomic;that is,it must be of some elementary type such as integers or string.w Each attributes of a relation has particular elementary type,thus domain is decided.1

    6、0uEquivalent Representations of a Relationw The attributes of the relation can be reordered without change the relation.11uRelation Instance(关系实例)w A relation about movies is not static;rather,relations change over time.w It is not common for the schema of a relation to change.w We shall call a set

    7、of tuples for a given relation an instance of that relation.12An Example of Relation InstanceRelation:Person(Name,Address,Telephone)Relation Instance:NameAddressTelephoneBob123 Main St555-1234Bob128 Main St555-1235Pat123 Main St555-1235Harry456 Main St555-2221Sally456 Main St555-2221Sally456 Main St

    8、555-2223Pat12 State St555-123513More uRelation(Instance)=a set of tuplesuDatabase=collection of relationsuRelation schema=relation name+attributesw Example:Movies(title,year,length,fileType)uDatabase schema=a set of all relation schemasw Movies(Title,Year,Length,FileType)w Star(Name,Age)w Studio(Stu

    9、dioName,Addr)14Name Addr Tel N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T7Name Addr Tel N1 A1 T1 N1 A1 T2 N1 A1 T3 .N1 A1 T7 N1 A2 T1 N1 A3 T1 N2 A1 T1TupleDomainComponentAttribute15Integrity Constrain of RelationsuEntity Constrainw The attributes belong to key can not be set as NULL.uReference Const

    10、rainw Foreign Key:an non-key attribute A in R is a key in S,then the A is called a foreign key of R.w The value of foreign key can only be NULL or same as what is in S.uUser-define Constrainw Users define the constrains themselves.16补充:关系的完整性u实体完整性u参照完整性u用户定义完整性 实体完整性和参照完整性是关系模型必须满足的,被称作关系的不变性,由关系数据

    11、库系统自动支持17实体完整性u规则:若属性A是基本关系R的主属性,则属性A不能取空值u说明:基本关系的主码中的任何属性都不能取空值,而不仅是主码整体不能取空值u依据:现实世界的实体是唯一可分的例:学生(例:学生(学号学号,姓名,性别,专业号,年龄),姓名,性别,专业号,年龄)课程(课程(课程号课程号,课程名,学分),课程名,学分)选修(选修(学号学号,课程号课程号,成绩),成绩)18例:学生实体与专业实体间的关系:例:学生实体与专业实体间的关系:学生(学生(学号学号,姓名,性别,专业号,年龄),姓名,性别,专业号,年龄)专业(专业(专业号专业号,专业名),专业名)关系参照图外码外码参照关系参照

    12、关系被参照关系被参照关系例:学生,课程,学生与课程之间的多对多联系:例:学生,课程,学生与课程之间的多对多联系:学生(学生(学号学号,姓名,性别,专业号,年龄),姓名,性别,专业号,年龄)课程(课程(课程号课程号,课程名,学分),课程名,学分)选修(选修(学号学号,课程号课程号,成绩),成绩)关系参照图被参照关被参照关系系参照关系参照关系学生关系专业关系学生关系专业关系专业号专业号学生关系学生关系 选修关系课程关系选修关系课程关系学号学号课程号课程号参照完整性主码?外码?19参照完整性u定义:外码定义:外码w 设F是参照关系R的一个或一组属性,若F与被参照关系S的主码相对应,则称F是R的外码(

    13、详细定义见教材P54)u规则:参照关系R中每个元组在外码F上的值必须为:或者取空值(F的每个属性值均为空值)或者等于S中某个元组的主码值例:学生(例:学生(学号学号,姓名,性别,专业号,年龄,班长),姓名,性别,专业号,年龄,班长)参照关系参照关系被参照关系被参照关系外码外码20用户定义完整性u用户定义的、具体应用中的数据必须满足的约束条件w 成绩:0100之间w 身份证、身份证和生日对应关系 213.2 From E/R Diagrams to RelationuFrom Entity Sets to Relationw Simplest approach(not always best):

    14、convert each E.S.to a relation.w Create a relation of the same name and with the same set of attributes.22Movies(title,year,length,filmType)Stars(name,address)Studios(name,address)Example23u From E/R Relationships to RelationwRelationships in the E/R model are also represented by relations.The relat

    15、ion for a given relationship R has the following attributes;1.For each entity set involved in relationship R,we take its key attributes as part of the schema of the relation for R.2.If the relationship has attributes,then these are also attributes of relation R.24Example:Owns(title,year,studioname)S

    16、tars-in(title,year,starName)25E-RE-R图向关系模型的转换原则图向关系模型的转换原则u一个实体转换为一个关系模式,实体的属性就一个实体转换为一个关系模式,实体的属性就是关系的属性,实体的码就是关系的码是关系的属性,实体的码就是关系的码u对实体间的联系对实体间的联系w 一个一个1:11:1联系可以转换为一个独立的关系模式,也联系可以转换为一个独立的关系模式,也可以与任意对应的关系模式合并可以与任意对应的关系模式合并w 一个一个1:n1:n联系可以转换为一个独立的关系模式,也联系可以转换为一个独立的关系模式,也可以与可以与n n端对应的关系模式合并端对应的关系模式合

    17、并w 一个一个m:nm:n联系转换为一个关系模式联系转换为一个关系模式w 三个或三个以上实体间的一个多元联系可以转换为三个或三个以上实体间的一个多元联系可以转换为一个关系模式一个关系模式w 具有相同码的关系模式可以合并具有相同码的关系模式可以合并补充:补充:26如公司部门管理系统的如公司部门管理系统的E-R图及其转换成的关系模式图及其转换成的关系模式部门部门项目项目职工职工电话电话包括包括承担承担n1n1办公室号办公室号,面积,面积项目号项目号,预算费,预算费参与参与mn办公室办公室包含包含1n包括包括n1部门号部门号,预算费,预算费,领导人职工号领导人职工号电话号码电话号码,说明,说明职工号

    18、职工号,姓名,办公电话,姓名,办公电话分担任务分担任务 部门部门(部门号部门号,部门预算费,领导人职工号,部门预算费,领导人职工号)职工职工(职工号职工号,姓名,办公电话,部门号,姓名,办公电话,部门号)办公室办公室(办公室号办公室号,面积,部门号,面积,部门号)项目项目(项目号项目号,项目预算费,部门号,项目预算费,部门号)电话电话(电话号码电话号码,说明,办公室号,说明,办公室号)项目承担情况项目承担情况(职工号职工号,项目号项目号,分担任务,分担任务)27Exerciseu Required:Convert the E-R diagram you designed in last exe

    19、rcise into a relational database schema.283.5 Functional DependenciesuDefinition of Functional Dependency(函数依赖)w X-A is an assertion about a relation R that whenever two tuples of R agree on all the attributes of X,then they must also agree on the attribute A.w Say“X-A holds in R.”w Convention X,Y,Z

    20、 represent sets of attributes;A,B,C,represent single attributes.29ExampleMovies(title,year,length,filmType,studioName,starname)We can assert the three dependencies:1.title,year-length2.title,year-filmType3.title,year-studioName30uFDs With Multiple Attributesw No need for FDs with multiple attribute

    21、on right.But sometimes for convenient we can combine them.Example:title,year-length title,year-filmType title,year-starName become title,year-length,filmType,starNamew Multiple attribute on left may be essential.Example:title,year-length31uTrivial Dependenciesw A functional dependency A1A2An-B is sa

    22、id to be trivial(平凡)if B is one of the As,otherwise is said to be nontrivial(非平凡).w Example:Suppose Functional Dependencies title,year-title is a trivial dependency.32uTransitive(传递)Functional Dependenciesw Suppose we have a relation R with three attributes A,B,and C,w the FDs A-B and B-C both hold

    23、for R.Then it is easy to see that the FD A-c also holds for R,w So C is said to depend on A transitively,via B33u Keys of Relations from FDs viewwWe say a set of one or more attributes A1,A2,An is a key for relation if:1.Those attributes functionally determine all other attributes of the relation.Th

    24、at is,it is impossible for two distinct tuples of R to agree on all of A1,A2,An.2.No proper subset of A1,A2,An functionally determines all other attributes of R;i.e.,a key must be minimal34uExamplew Attributes title,year,starName form a key for the Movie relation of Fig.above.w Sometimes a relation

    25、has more than one key.If so,it is common to design one of the keys as the primary key.35uSuperKeysw Superkey satisfies the first condition of a key;w However,a superkey need not satisfy the second condition;w Example Attribute set title,year,starName form a key for the Movie.Any superset of this att

    26、ribute set,such astitle,year,starName,lengthis a superkey.36uRules for discovering Keys of Relationw First rule:If the relation comes from an entity set then the key for the relation is the key attributes of this entity set.w Second rule:If a relation R is comes from a relationship,then the multipli

    27、city of the relationship affects the key for R.There are three cases:If the relationship is many-many,then the keys of both connected entity sets are the key attributes for R.If the relationship is many-one from entity set E1 to entity set E2,then the key attributes of E1 are key attributes of R,but

    28、 those of E2 are not.If the relationship is one-one,then the key attributes for either of the connected entity sets are key attributes of R.Thus,there is not a unique key for R.37w Example Owns:It is a many-one relation between Movies to Studios,Thus,the key for the relation Owns is the key attribut

    29、es title and year,which come from the key for Movies.Owns(title,year,studioName)Star-in:It is a many-many relationship between Movies and Stars.So all attributes of the resulting relation are key attributes Stars-in(title,year,starName)383.7 Design of Relational Database Schemau Anomalies(异常)w Probl

    30、em occur when we try to cram too much into a single relation are called anomalies.Redundancy:Information may be repeated unnecessarily in several tuples.Insertion Anomalies:Tuple insertion may be failed due to lack some other information in the current database.Deletion Anomalies:If a set of values

    31、becomes empty,we may lose other information as a side effect.Update Anomalies:We may change information in one tuple but leave the same information unchanged in another.39w Example:w Goal of relational schema design is to avoid anomalies and redundancy.40uNormal Formw A relation schema is said to be

    32、 in particular normal form if it satisfies a certain prescribed set of conditions.1NF,2NF,3NF,BCNFuNormalization Procedurew The successive reduction of a given collection of relation schema to some more desirable form.NFNFNFBCNF12341u1NFw A relation R is in 1NF if and only if,every tuple contains ex

    33、actly one value for each attributes.Relations in Relational database always in 1NF.But,a relation schema only in 1NF is also always undesirable for a number of relations.42u2NFw A relation R is in 2NF if and only if:it is in 1NF and every non-key attributes is full functional dependency on the prima

    34、ry key.w Full Functional Dependency In relation R,if X-Y,and any subset of X,X-Y,say Y full functional dependency to X,that X-FY.Otherwise X-PY,Y is partly function dependency to X.w Example:If in relation R(A,B,C),existing functional dependencies (A,B)-C,A-C,B-C,so (A,B)-FC and R is in 2NF43u3NFw A

    35、 relation R is in 3NF if:it is in 2NF and there is no transitive functional dependency existed.w Example:If in relation R(A,B,C),existing functional dependencies A-B and B-C,then R is not 3NF.44uBCNFw We say a relation R is in BCNF if:whenever X-A is a nontrivial FD and X is a superkey.Nontrivial me

    36、ans A is not a member of set X.Superkey is any superset of a key(not necessarily a proper superset).45uExample 1w Movies(title,year,length,filmType,studioName,starName)FD:title,year-length,filmType,studioNameThe only key is title,year,starNameIn each FD,the left side is not a superkey.These FDs show

    37、s Movies is not in BCNF.46uExample 2w Movies(title,year,length,filmType,studioName)FDs:title,year-length,filmType,studioNameThe only key is title,year.In each FD,the left side is a superkey.These FDs shows Movies is in BCNF47uDecomposition(分解)into BCNFw The decomposition strategy is to look for a no

    38、ntrivial dependency A1,A2,An-B1,B2,Bm that violates BCNF;i.e.,A1,A2,An is not a superkey,w as a heuristic,we shall generally add to the right side as many attributes as are functionally determined by A1,A2,An.48Example1The key is(title,year,satrName),but the following FD exist:title,year-length,film

    39、Type,studioNameThus,the relation is a BCNF violation.We shall decompose the relation into following two:R1 title,year,length,filmType,studioNameR2 title,year,starName49uExample2w We suppose a relation S-L-C(Sno,Sdept,Sloc,Cno,G)Only key of this relation is(Sno,Cno)FDs:(Sno,Cno)-FG,Sno-Sdept,(Sno,Cno

    40、)-pSdept Sno-Sloc,(Sno,Cno)-pSloc,Sdept-Sloc Relation S-L-C is only in 1NF.Please decompose it into BCNF.50uStep1:Decompose it into 2NFw SC(Sno,Cno,G)w S-L(Sno,Sdept,Sloc)uStep2:Decompose it into 3NFNow,SC is in 3NF,but S-L only in 2NF.So we need to Decompose S-L into 3NFw SC(Sno,Cno,G)w S-D(Sno,Sdept)w D-L(Sdept,Sloc)51uStep3:Decompose it into BCNFw Now,all of relations SC,S-D,D-L are in BCNF.uThe final result isw SC(Sno,Cno,G)w S-D(Sno,Sdept)w D-L(Sdept,Sloc)52Reading GuideuA First Course in Database Systems:w Required:3.1 and 3.3w Recommended:3.5 and 3.7u数据库系统概论w 推荐:第五章,第六章

    展开阅读全文
    提示  163文库所有资源均是用户自行上传分享,仅供网友学习交流,未经上传用户书面授权,请勿作他用。
    关于本文
    本文标题:数据库原理-英文课件Chapter3-The-Relational-Data-Model.ppt
    链接地址:https://www.163wenku.com/p-4072741.html

    Copyright@ 2017-2037 Www.163WenKu.Com  网站版权所有  |  资源地图   
    IPC备案号:蜀ICP备2021032737号  | 川公网安备 51099002000191号


    侵权投诉QQ:3464097650  资料上传QQ:3464097650
       


    【声明】本站为“文档C2C交易模式”,即用户上传的文档直接卖给(下载)用户,本站只是网络空间服务平台,本站所有原创文档下载所得归上传人所有,如您发现上传作品侵犯了您的版权,请立刻联系我们并提供证据,我们将在3个工作日内予以改正。

    163文库