数据库原理-英文课件Chapter3-The-Relational-Data-Model

1DatabasePrinciples1DatabasePrinciples2Chapter3

TheRelationalDataModelRelationalModelFunctionalDependencies2Chapter3

TheRelationalData3Contents3.1BasicsoftheRelationalModel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise3Contents3.1BasicsoftheRel4Thethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopularRDBMSDB2,Oracle,Ingres,Sybase,Informix,…4Thethingsyoushouldknow…Th53.1BasicoftheRelationalModel

Therelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltablecalledarelation.

53.1BasicoftheRelationalM6Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.6Attributes(属性)7Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.7Schemas（模式）8Tuples（元组）Therowsofarelation,

otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.8Tuples（元组）9Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.9Domains（域）10EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.10EquivalentRepresentationso11RelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.11RelationInstance（关系实例）12AnExampleofRelationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:Name Address TelephoneBob 123MainSt 555-1234Bob 128MainSt 555-1235Pat 123MainSt 555-1235Harry 456MainSt 555-2221Sally 456MainSt 555-2221Sally 456MainSt 555-2223Pat 12StateSt 555-123512AnExampleofRelationInsta13More…Relation(Instance)=asetoftuplesDatabase

=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr)13More…Relation(Instance)=14NameAddrTelN1A1T1N2A2T2N3A3T3 N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute14NameAddrTelTupleDoma15IntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.15IntegrityConstrainofRelat16补充：关系的完整性实体完整性参照完整性用户定义完整性

实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★16补充：关系的完整性实体完整性★17实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）17实体完整性规则：若属性A是基本关系R的主属性，则属性A不18例１：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图外码参照关系被参照关系例２：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图

被参照关系参照关系学生关系专业关系专业号学生关系选修关系课程关系学号课程号参照完整性主码？外码？18例１：学生实体与专业实体间的关系：外码参照关系被参照关系19参照完整性定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值例３：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码19参照完整性定义：外码例３：学生（学号，姓名，性别，专业号20用户定义完整性用户定义的、具体应用中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系

20用户定义完整性213.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.Createarelationofthesamenameandwiththesamesetofattributes.213.2FromE/RDiagramstoRel22Movies(title,year,length,filmType)Stars(name,address)Studios(name,address)Example22Movies(title,year,length,23FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasthefollowingattributes;ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.23FromE/RRelationshipstoRe24Example:Owns(title,year,studioname)Stars-in(title,year,starName)24Example:Owns(title,year,s25E-R图向关系模型的转换原则一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并补充：25E-R图向关系模型的转换原则一个实体转换为一个关系模式，26如公司部门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与mn办公室包含1n包括n1部门号，预算费，领导人职工号电话号码，说明职工号，姓名，办公电话分担任务部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务)26如公司部门管理系统的E-R图及其转换成的关系模式部门项目27ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.27ExerciseRequired:Convertth283.5FunctionalDependenciesDefinitionofFunctionalDependency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”ConventionX,Y,Zrepresentsetsofattributes;

A,B,C,…representsingleattributes.283.5FunctionalDependenciesD29ExampleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:title,year->lengthtitle,year->filmTypetitle,year->studioName29Example30FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.Butsometimesforconvenientwecancombinethem.Example:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential.Example:title,year->length30FD’sWithMultipleAttribute31TrivialDependenciesAfunctionaldependencyA1A2…An->Bissaidtobetrivial(平凡)ifBisoneoftheA’s,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependencies

title,year->titleisatrivialdependency.31TrivialDependencies32Transitive(传递)

FunctionalDependenciesSupposewehavearelationRwiththreeattributesA,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsforR,SoCissaidtodependonAtransitively,viaB32Transitive(传递)FunctionalDe33KeysofRelationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:Thoseattributesfunctionallydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,…,An.Nopropersubsetof{A1,A2,…,An}functionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal33KeysofRelationsfromFD’s34ExampleAttributes{title,year,starName}formakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.34Example35SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;ExampleAttributeset{title,year,starName}formakeyfortheMovie.Anysupersetofthisattributeset,suchas{title,year,starName,length} isasuperkey.35SuperKeys36RulesfordiscoveringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthekeyforR.Therearethreecases:Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.Iftherelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.Iftherelationshipisone-one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.36RulesfordiscoveringKeyso37ExampleOwns:Itisamany-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.

Owns(title,year,studioName)Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.SoallattributesoftheresultingrelationarekeyattributesStars-in(title,year,starName)37Example383.7DesignofRelationalDatabaseSchemaAnomalies(异常)Problemoccurwhenwetrytocramtoomuchintoasinglerelationarecalledanomalies.Redundancy:Informationmayberepeatedunnecessarilyinseveraltuples.InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.DeletionAnomalies:Ifasetofvaluesbecomesempty,wemayloseotherinformationasasideeffect.UpdateAnomalies:Wemaychangeinformationinonetuplebutleavethesameinformationunchangedinanother.383.7DesignofRelationalDat39Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.39Example:40NormalFormArelationschemaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.1NF,2NF,3NF,BCNFNormalizationProcedureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.40NormalForm411NFArelationRisin1NFifandonlyif,everytuplecontainsexactlyonevalueforeachattributes.RelationsinRelationaldatabasealwaysin1NF.But,arelationschemaonlyin1NFisalsoalwaysundesirableforanumberofrelations.411NF422NFArelationRisin2NFifandonlyif:itisin1NFandeverynon-keyattributesisfullfunctionaldependencyontheprimarykey.FullFunctionalDependencyInrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctionaldependencytoX,thatX-F>Y.OtherwiseX-P>Y,YispartlyfunctiondependencytoX.Example:IfinrelationR(A,B,C),existingfunctionaldependencies(A,B)->C,A-\>C,B-\>C,so(A,B)-F>CandRisin2NF422NF433NFArelationRisin3NFif:itisin2NFandthereisnotransitivefunctionaldependencyexisted.Example:IfinrelationR(A,B,C),existingfunctionaldependenciesA->BandB->C,thenRisnot3NF.433NF44BCNFWesayarelationRisinBCNFif:wheneverX->AisanontrivialFDandXisasuperkey.NontrivialmeansAisnotamemberofsetX.Superkeyisanysupersetofakey(notnecessarilyapropersuperset).44BCNF45Example1Movies(title,year,length,filmType,studioName,starName) FD:title,year->length,filmType,studioName Theonlykeyis{title,year,starName} IneachFD,theleftsideisnotasuperkey. TheseFD’sshowsMoviesisnotinBCNF.45Example146Example2Movies(title,year,length,filmType,studioName) FD’s:title,year->length,filmType,studioName Theonlykeyis{title,year}. IneachFD,theleftsideisasuperkey. TheseFD’sshowsMoviesisinBCNF46Example247Decomposition(分解)

intoBCNFThedecompositionstrategyistolookforanontrivialdependencyA1,A2,…,An->B1,B2,…,BmthatviolatesBCNF;i.e.,{A1,A2,…,An}isnotasuperkey,asaheuristic,weshallgenerallyaddtotherightsideasmanyattributesasarefunctionallydeterminedby{A1,A2,…,An}.47Decomposition(分解)intoBCNF48Example1Thekeyis(title,year,satrName),butthefollowingFDexist:{title,year->length,filmType,studioName}Thus,therelationisaBCNFviolation.Weshalldecomposetherelationintofollowingtwo: R1{title,year,length,filmType,studioName} R2{title,year,starName}48Example1Thekeyis(title,y49Example2WesupposearelationS-L-C(Sno,Sdept,Sloc,Cno,G)Onlykeyofthisrelationis(Sno,Cno)FD’s:(Sno,Cno)-F>G,Sno->Sdept,(Sno,Cno)-p>SdeptSno->Sloc,(Sno,Cno)-p>Sloc,Sdept->SlocRelationS-L-Cisonlyin1NF.PleasedecomposeitintoBCNF.49Example250Step1:Decomposeitinto2NFSC(Sno,Cno,G)S-L(Sno,Sdept,Sloc)Step2:Decomposeitinto3NFNow,SCisin3NF,butS-Lonlyin2NF.SoweneedtoDecomposeS-Linto3NFSC(Sno,Cno,G)S-D(Sno,Sdept)D-L(Sdept,Sloc)50Step1:Decomposeitinto2NF51Step3:DecomposeitintoBCNFNow,allofrelationsSC,S-D,D-LareinBCNF.ThefinalresultisSC(Sno,Cno,G)S-D(Sno,Sdept)D-L(Sdept,Sloc)

51Step3:DecomposeitintoBCN52ReadingGuideAFirstCourseinDatabaseSystems:Required:3.1and3.3Recommended:3.5and3.7数据库系统概论推荐：第五章，第六章52ReadingGuideAFirstCourse53DatabasePrinciples1DatabasePrinciples54Chapter3

TheRelationalDataModelRelationalModelFunctionalDependencies2Chapter3

TheRelationalData55Contents3.1BasicsoftheRelationalModel3.3FromE/RdiagramstoRelationalDesigns3.5FunctionalDependencies3.7DesignofRelationalDatabaseSchemasReadingGuideExercise3Contents3.1BasicsoftheRel56Thethingsyoushouldknow…ThebasicprincipleofrelationaldatabaseisproposedbyE.F.Coddin1970.ThefirstRDBMSproductionisSystemRThemostpopularRDBMSDB2,Oracle,Ingres,Sybase,Informix,…4Thethingsyoushouldknow…Th573.1BasicoftheRelationalModel

Therelationalmodelgivesusasinglewaytorepresentdata:asatwo-dimensionaltablecalledarelation.

53.1BasicoftheRelationalM58Attributes(属性)Attributesofarelationserveusasnamesforthecolumnsoftherelation.Usually,theattributesdescribesthemeaningofentriesinthecolumnbelow.6Attributes(属性)59Schemas（模式）Thenameofarelationandthesetofattributesforarelationiscalledtheschemaforthatrelation.Weshowtheschemafortherelationwiththerelationnamefollowedbyaparenthesizedlistofitsattributes.7Schemas（模式）60Tuples（元组）Therowsofarelation,

otherthantheheaderrowcontainingtheattributes,arecalledtuple.Forexample:Relations,however,aresetsoftuples,anditisimpossibleforatupletoappearmorethanonceinagivenrelation.8Tuples（元组）61Domains（域）Therelationalmodelrequiresthateachcomponentofeachtuplebeatomic;thatis,itmustbeofsomeelementarytypesuchasintegersorstring.Eachattributesofarelationhasparticularelementarytype,thusdomainisdecided.9Domains（域）62EquivalentRepresentationsofaRelationTheattributesoftherelationcanbereorderedwithoutchangetherelation.10EquivalentRepresentationso63RelationInstance（关系实例）Arelationaboutmoviesisnotstatic;rather,relationschangeovertime.Itisnotcommonfortheschemaofarelationtochange.Weshallcallasetoftuplesforagivenrelationaninstanceofthatrelation.11RelationInstance（关系实例）64AnExampleofRelationInstanceRelation:Person(Name,Address,Telephone)RelationInstance:Name Address TelephoneBob 123MainSt 555-1234Bob 128MainSt 555-1235Pat 123MainSt 555-1235Harry 456MainSt 555-2221Sally 456MainSt 555-2221Sally 456MainSt 555-2223Pat 12StateSt 555-123512AnExampleofRelationInsta65More…Relation(Instance)=asetoftuplesDatabase

=collectionofrelationsRelationschema=relationname+attributesExample:Movies(title,year,length,fileType)Databaseschema=asetofallrelationschemasMovies(Title,Year,Length,FileType)Star(Name,Age)Studio(StudioName,Addr)13More…Relation(Instance)=66NameAddrTelN1A1T1N2A2T2N3A3T3 N4T4N5T5T6T7NameAddrTelN1A1T1N1A1T2N1A1T3...N1A1T7N1A2T1N1A3T1N2A1T1TupleDomainComponentAttribute14NameAddrTelTupleDoma67IntegrityConstrainofRelationsEntityConstrainTheattributesbelongtokeycannotbesetasNULL.ReferenceConstrainForeignKey:annon-keyattributeAinRisakeyinS,thentheAiscalledaforeignkeyofR.ThevalueofforeignkeycanonlybeNULLorsameaswhatisinS.User-defineConstrainUsersdefinetheconstrainsthemselves.15IntegrityConstrainofRelat68补充：关系的完整性实体完整性参照完整性用户定义完整性

实体完整性和参照完整性是关系模型必须满足的，被称作关系的不变性，由关系数据库系统自动支持★16补充：关系的完整性实体完整性★69实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值说明：基本关系的主码中的任何属性都不能取空值，而不仅是主码整体不能取空值依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）17实体完整性规则：若属性A是基本关系R的主属性，则属性A不70例１：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图外码参照关系被参照关系例２：学生，课程，学生与课程之间的多对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图

被参照关系参照关系学生关系专业关系专业号学生关系选修关系课程关系学号课程号参照完整性主码？外码？18例１：学生实体与专业实体间的关系：外码参照关系被参照关系71参照完整性定义：外码设F是参照关系R的一个或一组属性，但不是R的码，若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）规则：参照关系R中每个元组在外码F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值例３：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码19参照完整性定义：外码例３：学生（学号，姓名，性别，专业号72用户定义完整性用户定义的、具体应用中的数据必须满足的约束条件成绩：0－100之间身份证、身份证和生日对应关系

20用户定义完整性733.2FromE/RDiagramstoRelationFromEntitySetstoRelationSimplestapproach(notalwaysbest):converteachE.S.toarelation.Createarelationofthesamenameandwiththesamesetofattributes.213.2FromE/RDiagramstoRel74Movies(title,year,length,filmType)Stars(name,address)Studios(name,address)Example22Movies(title,year,length,75FromE/RRelationshipstoRelationRelationshipsintheE/Rmodelarealsorepresentedbyrelations.TherelationforagivenrelationshipRhasthefollowingattributes;ForeachentitysetinvolvedinrelationshipR,wetakeitskeyattributesaspartoftheschemaoftherelationforR.Iftherelationshiphasattributes,thenthesearealsoattributesofrelationR.23FromE/RRelationshipstoRe76Example:Owns(title,year,studioname)Stars-in(title,year,starName)24Example:Owns(title,year,s77E-R图向关系模型的转换原则一个实体转换为一个关系模式，实体的属性就是关系的属性，实体的码就是关系的码对实体间的联系一个1:1联系可以转换为一个独立的关系模式，也可以与任意对应的关系模式合并一个1:n联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并一个m:n联系转换为一个关系模式三个或三个以上实体间的一个多元联系可以转换为一个关系模式具有相同码的关系模式可以合并补充：25E-R图向关系模型的转换原则一个实体转换为一个关系模式，78如公司部门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与mn办公室包含1n包括n1部门号，预算费，领导人职工号电话号码，说明职工号，姓名，办公电话分担任务部门(部门号，部门预算费，领导人职工号)职工(职工号，姓名，办公电话，部门号)办公室(办公室号，面积，部门号)项目(项目号，项目预算费，部门号)电话(电话号码，说明，办公室号)项目承担情况(职工号，项目号，分担任务)26如公司部门管理系统的E-R图及其转换成的关系模式部门项目79ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.27ExerciseRequired:Convertth803.5FunctionalDependenciesDefinitionofFunctionalDependency(函数依赖)X->AisanassertionaboutarelationRthatwhenevertwotuplesofRagreeonalltheattributesofX,thentheymustalsoagreeontheattributeA.Say“X->AholdsinR.”ConventionX,Y,Zrepresentsetsofattributes;

A,B,C,…representsingleattributes.283.5FunctionalDependenciesD81ExampleMovies(title,year,length,filmType,studioName,starname)Wecanassertthethreedependencies:title,year->lengthtitle,year->filmTypetitle,year->studioName29Example82FD’sWithMultipleAttributesNoneedforFD’swithmultipleattributeonright.Butsometimesforconvenientwecancombinethem.Example:title,year->lengthtitle,year->filmTypetitle,year->starNamebecometitle,year->length,filmType,starNameMultipleattributeonleftmaybeessential.Example:title,year->length30FD’sWithMultipleAttribute83TrivialDependenciesAfunctionaldependencyA1A2…An->Bissaidtobetrivial(平凡)ifBisoneoftheA’s,otherwiseissaidtobenontrivial(非平凡).Example:SupposeFunctionalDependencies

title,year->titleisatrivialdependency.31TrivialDependencies84Transitive(传递)

FunctionalDependenciesSupposewehavearelationRwiththreeattributesA,B,andC,theFDsA->BandB->CbothholdforR.ThenitiseasytoseethattheFDA->calsoholdsforR,SoCissaidtodependonAtransitively,viaB32Transitive(传递)FunctionalDe85KeysofRelationsfromFD’sviewWesayasetofoneormoreattributes{A1,A2,…,An}isakeyforrelationif:Thoseattributesfunctionallydetermineallotherattributesoftherelation.Thatis,itisimpossiblefortwodistincttuplesofRtoagreeonallofA1,A2,…,An.Nopropersubsetof{A1,A2,…,An}functionallydeterminesallotherattributesofR;i.e.,akeymustbeminimal33KeysofRelationsfromFD’s86ExampleAttributes{title,year,starName}formakeyfortheMovierelationofFig.above.Sometimesarelationhasmorethanonekey.Ifso,itiscommontodesignoneofthekeysastheprimarykey.34Example87SuperKeysSuperkeysatisfiesthefirstconditionofakey;However,asuperkeyneednotsatisfythesecondcondition;ExampleAttributeset{title,year,starName}formakeyfortheMovie.Anysupersetofthisattributeset,suchas{title,year,starName,length} isasuperkey.35SuperKeys88RulesfordiscoveringKeysofRelationFirstrule:Iftherelationcomesfromanentitysetthenthekeyfortherelationisthekeyattributesofthisentityset.Secondrule:IfarelationRiscomesfromarelationship,thenthemultiplicityoftherelationshipaffectsthekeyforR.Therearethreecases:Iftherelationshipismany-many,thenthekeysofbothconnectedentitysetsarethekeyattributesforR.Iftherelationshipismany-onefromentitysetE1toentitysetE2,thenthekeyattributesofE1arekeyattributesofR,butthoseofE2arenot.Iftherelationshipisone-one,thenthekeyattributesforeitheroftheconnectedentitysetsarekeyattributesofR.Thus,thereisnotauniquekeyforR.36RulesfordiscoveringKeyso89ExampleOwns:Itisamany-onerelationbetweenMoviestoStudios,Thus,thekeyfortherelationOwnsisthekeyattributestitleandyear,whichcomefromthekeyforMovies.

Owns(title,year,studioName)Star-in:Itisamany-manyrelationshipbetweenMoviesandStars.SoallattributesoftheresultingrelationarekeyattributesStars-in(title,year,starName)37Example903.7DesignofRelationalDatabaseSchemaAnomalies(异常)Problemoccurwhenwetrytocramtoomuchintoasinglerelationarecalledanomalies.Redundancy:Informationmayberepeatedunnecessarilyinseveraltuples.InsertionAnomalies:Tupleinsertionmaybefailedduetolacksomeotherinformationinthecurrentdatabase.DeletionAnomalies:Ifasetofvaluesbecomesempty,wemayloseotherinformationasasideeffect.UpdateAnomalies:Wemaychangeinformationinonetuplebutleavethesameinformationunchangedinanother.383.7DesignofRelationalDat91Example:Goalofrelationalschemadesignistoavoidanomaliesandredundancy.39Example:92NormalFormArelationschemaissaidtobeinparticularnormalformifitsatisfiesacertainprescribedsetofconditions.1NF,2NF,3NF,BCNFNormalizationProcedureThesuccessivereductionofagivencollectionofrelationschematosomemoredesirableform.40NormalForm931NFArelationRisin1NFifandonlyif,everytuplecontainsexactlyonevalueforeachattributes.RelationsinRelationaldatabasealwaysin1NF.But,arelationschemaonlyin1NFisalsoalwaysundesirableforanumberofrelations.411NF942NFArelationRisin2NFifandonlyif:itisin1NFandeverynon-keyattributesisfullfunctionaldependencyontheprimarykey.FullFunctionalDependencyInrelationR,ifX->Y,andanysubsetofX,X’-\>Y,sayYfullfunctionaldependencytoX,t