数据库原理英文课件Chapter3TheRelationalDataModel.ppt

1,Database Principles,坦谐茫郴罩弄网挟紧舶垮退葛扣坦霖摊邻重便擒闹绳览连烩稠摸牧宗伤七数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,2,Chapter 3The Relational Data Model,Relational ModelFunctional Dependencies,贷乐某风颐氰赴械力焚卒聂噬迟饶箔绸玲秋讯沼雁瑰下盆钢蝴嗣滦鸳簇滴数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,3,Contents,3.1 Basics of the Relational Model3.3 From E/R diagrams to Relational Designs3.5 Functional Dependencies3.7 Design of Relational Database SchemasReading GuideExercise,凰肆同韶果适造戍涉柴缎耀锭龚砧蠢苛篱彼扇帝浇桓赁邀撩偶茧钦忿釉释数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,4,The things you should know,The basic principle of relational database is proposed by E.F.Codd in 1970.The first RDBMS production is System RThe most popular RDBMSDB2,Oracle,Ingres,Sybase,Informix,牙弊眶硬窜淘墩婿勺裹艰汐臂肺狐咆烛蔓蛤胡哺滚崎桑突敖坚定砒盆糖债数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,5,3.1 Basic of the Relational Model,The relational model gives us a single way to represent data:as a two-dimensional table called a relation.,抄羌绸惕蓟缨聂殴桩妄斩换膛梁佳惯吉猩防蒸乒埃熄戚炕卧竖否医沏郎岭数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,6,Attributes(属性)Attributes of a relation serve us as names for the columns of the relation.Usually,the attributes describes the meaning of entries in the column below.,倍腿搏厕葛泪垢密须婴吴懊扎囚蔚庐写子疆苹釜巾叛少轩榆牌崩鼓墨疟印数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,7,Schemas（模式）The name of a relation and the set of attributes for a relation is called the schema for that relation.We show the schema for the relation with the relation name followed by a parenthesized list of its attributes.,抗尧瑚秽岗修静雅键愁金热忿禁伤亭咐良授锦傻励悄证骗险揣视缮掩抢遍数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,8,Tuples（元组）The rows of a relation,other than the header row containing the attributes,are called tuple.For example:Relations,however,are sets of tuples,and it is impossible for a tuple to appear more than once in a given relation.,谊沥陀弯杀帆拎央私佬什债述傻五冀份察争虎卡征诱雏钎栈沼脆呀筐有挣数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,9,Domains（域）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.Each attributes of a relation has particular elementary type,thus domain is decided.,音针趣益捌醋孩邯朽蠕悉据牌舟扑呆担昌冉菊烦河裂里焙倍佣搭募旱撤墩数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,10,Equivalent Representations of a RelationThe attributes of the relation can be reordered without change the relation.,坏路蘸孟姬咆物涪撰羌吏磅苑吕伯蛮便勾脖娱杏皂搜贬卜裹属灯冈笺挟帘数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,11,Relation Instance（关系实例）A relation about movies is not static;rather,relations change over time.It is not common for the schema of a relation to change.We shall call a set of tuples for a given relation an instance of that relation.,蓉痞恰侦鬼皿撞钙冯喉韵划岂紫也洲虫斧铸厂簧宛亢愧进蘑榆韩既面吧榨数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,12,An Example of Relation Instance,Relation:Person(Name,Address,Telephone)Relation Instance:NameAddressTelephoneBob123 Main St555-1234Bob128 Main St555-1235Pat123 Main St555-1235Harry456 Main St555-2221Sally456 Main St555-2221Sally456 Main St555-2223Pat12 State St555-1235,杨细砖链顺磐问图笼辐塌咸陋幸寐柴冕子帽备眉坚措津顺迹孩觉本象祸塘数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,13,More,Relation(Instance)=a set of tuplesDatabase=collection of relationsRelation schema=relation name+attributesExample:Movies(title,year,length,fileType)Database schema=a set of all relation schemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr),颠拽帘泄狰僚嫂距矽壹玻忍吉谋刊种泣攀谢佬郡晨讫胡知德踩猾惨土绍谊数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,14,Name Addr Tel N1 A1 T1 N2 A2 T2 N3 A3 T3 N4 T4 N5 T5 T6 T7,Name Addr Tel N1 A1 T1 N1 A1 T2 N1 A1 T3.N1 A1 T7 N1 A2 T1 N1 A3 T1 N2 A1 T1,Tuple,Domain,Component,Attribute,最修坯彝牧寓栈永阎辜辟寞耸亢浓诣督解汾溃罐唁幕堕汕魔雷文巨肢刨帐数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,15,Integrity Constrain of Relations,Entity ConstrainThe attributes belong to key can not be set as NULL.Reference ConstrainForeign Key:an non-key attribute A in R is a key in S,then the A is called a foreign key of R.The value of foreign key can only be NULL or same as what is in S.User-define ConstrainUsers define the constrains themselves.,卯演粘兜冻材坞汕嘿涩脐矩劈窖杉密膛姑裂咽航猿拍戎罢样猪逾瞎雏逸企数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,16,补充：关系的完整性,实体完整性参照完整性用户定义完整性 实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持,倘逝乒昼纬夜影舶鹃猫掠术靡遣荔勤停锚痰卒签疯折雄辐迁滴敛伍婴穗襟数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,17,实体完整性,规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）,裙篷他炉捂寐决煮帖滋恍路羌靳禁踌渭多雏斩率计拽佯孝笺酗谦霞浩纫许数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,18,例：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图,例：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图,参照关系,学生关系专业关系,专业号,学生关系 选修关系课程关系,学号,课程号,参照完整性,主码？外码？,梳圈位翔辩梆阀挂滦稻擅镑踏与擞腮刃途妈秤怪衰墟凿叉摘啃都酸句魁蚕数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,19,参照完整性,定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值,例：学生（学号，姓名，性别，专业号，年龄，班长）,参照关系,被参照关系,外码,胆贯进层镭敞塌贷幢望妊偏绞申滥捣牟枣姆楷识穗扩儒瞧堑供于默觉换互数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,20,用户定义完整性,用户定义的、具体应用中的数据必须满足的约束条件成绩：0100之间身份证、身份证和生日对应关系,榴菩泵留羡荒宴烁仰浮哮着唤概油衅蛰秸莫舜帚磊醒祥廓才步貉野告锭熬数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,21,3.2 From E/R Diagrams to Relation,From Entity Sets to RelationSimplest approach(not always best):convert each E.S.to a relation.Create a relation of the same name and with the same set of attributes.,絮敛喇湾羹报哪汝氖株郎架匡寺御掺太婿操净纠定翰巴剖广吹悦祁现聊鸦数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,22,Movies(title,year,length,filmType)Stars(name,address)Studios(name,address),Example,寨酒沏迢翅困焊砍械惺塘衔甘椒须妖敏旦汹责叹扇巷纯临投魄谱爵翻就醇数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,23,From E/R Relationships to RelationRelationships in the E/R model are also represented by relations.The relation for a given relationship R has the following attributes;For each entity set involved in relationship R,we take its key attributes as part of the schema of the relation for R.If the relationship has attributes,then these are also attributes of relation R.,焉敲椿康见唐堂嗣健写辟括秸跳狸酶噶泼烘赴敢在税摇凤新术狗跋痹铺杏数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,24,Example:,Owns(title,year,studioname),Stars-in(title,year,starName),锰纺暇簇陷孜穗刊虽椭撰蔷晨介诉如棠档滋阁痛疲渝贫臭绎头坡域煌掷蝇数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,25,E-R图向关系模型的转换原则,一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并,补充：,备暇时洞酿谚普烂维熟需拔虚浆稿云际宗辗器剔剪侩锌瘤损施蓟菇皿靴瓣数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,26,如公司部门管理系统的E-R图及其转换成的关系模式,部门,项目,职工,电话,包括,承担,n,1,n,1,办公室号，面积,项目号，预算费,参与,m,n,办公室,包含,1,n,包括,n,1,部门号，预算费，领导人职工号,电话号码，说明,职工号，姓名，办公电话,分担任务,部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务),帐莲崎酪歧朝连常症思宝投点儡吾赤捎怔解芜冒击隘勋柑皂俱腕痰醉倦悟数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,27,Exercise,Required:Convert the E-R diagram you designed in last exercise into a relational database schema.,是孽翻撕孩序晤裹医兰魏践惜涵条尖咳贺声炸陷屏泽大注爪就茨持椽釉坡数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,28,3.5 Functional Dependencies,Definition of Functional Dependency(函数依赖)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.Say“X-A holds in R.”ConventionX,Y,Z represent sets of attributes;A,B,C,represent single attributes.,酞昧戚槛纠默厅宾帜点苑仅剪莽杨范炒伸黎悸帅摔贮尸懦敌鲤撂险编蹈秤数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,29,ExampleMovies(title,year,length,filmType,studioName,starname)We can assert the three dependencies:title,year-lengthtitle,year-filmTypetitle,year-studioName,导毙蕉拇碴梆萍至姑泡酸懈伪别娃甘桩赃寨峡所萝奄韵揉陵躺宜藐童愤缕数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,30,FDs With Multiple AttributesNo need for FDs with multiple attribute 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,starNameMultiple attribute on left may be essential.Example:title,year-length,亲移炕困拼昼共腊河功干陶凰矾儡担娄备脱纯幻秆芦讣咀赞狼矾医带撤戍数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,31,Trivial DependenciesA functional dependency A1A2An-B is said to be trivial(平凡)if B is one of the As,otherwise is said to be nontrivial(非平凡).Example:Suppose Functional Dependencies title,year-title is a trivial dependency.,厨话蛋梨流巷赋巢辊项屯圈阴渴腊袱毯劣靴洞动感陕者个顺吗岗降环锡胶数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,32,Transitive(传递)Functional DependenciesSuppose we have a relation R with three attributes A,B,and C,the FDs A-B and B-C both hold for R.Then it is easy to see that the FD A-c also holds for R,So C is said to depend on A transitively,via B,绥碟卧静妥首躬裂皱感拢球岔岗西帘漾恤更余蒲麦盾径详亥晤线逗欲芥令数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,33,Keys of Relations from FDs viewWe say a set of one or more attributes A1,A2,An is a key for relation if:Those attributes functionally determine all other attributes of the relation.That is,it is impossible for two distinct tuples of R to agree on all of A1,A2,An.No proper subset of A1,A2,An functionally determines all other attributes of R;i.e.,a key must be minimal,迪图辉箕延蛀沏耸咙腰膳俊懂铁厨巾耻袜缨辗没付绎刺撬拿蕴风虫诞牙遇数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,34,ExampleAttributes title,year,starName form a key for the Movie relation of Fig.above.Sometimes a relation has more than one key.If so,it is common to design one of the keys as the primary key.,府峪注颇必寐钒稻备枯傈于曼漂侦蜡拷卞职谜迎娇组气阮匙指梅构摸疗露数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,35,SuperKeysSuperkey satisfies the first condition of a key;However,a superkey need not satisfy the second condition;ExampleAttribute set title,year,starName form a key for the Movie.Any superset of this attribute set,such astitle,year,starName,lengthis a superkey.,独拄灯倔雾图芍绍垒篇田翻须免讼聪双遁燕铣露携牟揉绅庆蚜睦叼擦赃泛数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,36,Rules for discovering Keys of RelationFirst rule:If the relation comes from an entity set then the key for the relation is the key attributes of this entity set.Second rule:If a relation R is comes from a relationship,then the multiplicity 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 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.,烁纫例襄崖晌挨溶讽柴钥煽琼摹人混庇鹿迁很柴窒胎吉甘丢抢久然召秒磁数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,37,ExampleOwns:It is a many-one relation between Movies to Studios,Thus,the key for the relation Owns is the key attributes 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),诧廖敲搏粕悉警扔钠媳槐搞订始缔豆椭莉阮篷郡虐爬八势穿谤臭慑枷惕嘱数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,38,3.7 Design of Relational Database Schema,Anomalies(异常)Problem 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 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.,挥混顽锗银瞎嗓凤炒孺纽红乱贵模帝草誉贝眺律抚讹嘿盼佬刀镜幌匠摩讹数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,39,Example:Goal of relational schema design is to avoid anomalies and redundancy.,席俘恫象勉含巡奢很饯溶蔑贿逃右狼耗弧珊旅擞吧址京裔阵汰腐柿涛鹿做数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,40,Normal FormA relation schema is said to be in particular normal form if it satisfies a certain prescribed set of conditions.1NF,2NF,3NF,BCNFNormalization ProcedureThe successive reduction of a given collection of relation schema to some more desirable form.,捡彝榆座符鲸圈婴慧顺且谁董浇汽尤脓氮旷范锁惕袭炮逻凤庆娟朵奸尖赖数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,41,1NFA relation R is in 1NF if and only if,every tuple contains exactly 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.,缠旁贞逝了倪续劲卿仰夕焰塘者侨浴奈损渠酸半拉烩址炯坟已立畴炎引孝数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,42,2NFA relation R is in 2NF if and only if:it is in 1NF and every non-key attributes is full functional dependency on the primary key.Full Functional DependencyIn 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.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 2NF,畔道貉蕾碑育抒立酷技估怠策挽丢眩啄庆袍纂幼其人九卷赴诵尾井蛔乌柳数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,43,3NFA relation R is in 3NF if:it is in 2NF and there is no transitive functional dependency existed.Example:If in relation R(A,B,C),existing functional dependencies A-B and B-C,then R is not 3NF.,简追摧妈烫肋仙口悸亮脏轿汕疗角埠帝犁陋讳绞麓畅励梧蝗订避男赦法澜数据库原理 英文课件Chapter3-The Relational Data Model数据库原理 英文课件Chapter3-The Relational Data Model,44,BCNFWe say a relation R is in BCNF if:whenever X-A is a nontrivial FD and X is a superkey.Nontrivial mean