數(shù)據(jù)庫原理-英文課件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補充：關系的完整性實體完整性參照完整性用戶定義完整性

實體完整性和參照完整性是關系模型必須滿足的，被稱作關系的不變性，由關系數(shù)據(jù)庫系統(tǒng)自動支持★16補充：關系的完整性實體完整性★17實體完整性規(guī)則：若屬性A是基本關系R的主屬性，則屬性A不能取空值說明：基本關系的主碼中的任何屬性都不能取空值，而不僅是主碼整體不能取空值依據(jù)：現(xiàn)實世界的實體是唯一可分的例：學生（學號，姓名，性別，專業(yè)號，年齡）課程（課程號，課程名，學分）選修（學號，課程號，成績）17實體完整性規(guī)則：若屬性A是基本關系R的主屬性，則屬性A不18例１：學生實體與專業(yè)實體間的關系：學生（學號，姓名，性別，專業(yè)號，年齡）專業(yè)（專業(yè)號，專業(yè)名）關系參照圖外碼參照關系被參照關系例２：學生，課程，學生與課程之間的多對多聯(lián)系：學生（學號，姓名，性別，專業(yè)號，年齡）課程（課程號，課程名，學分）選修（學號，課程號，成績）關系參照圖

被參照關系參照關系學生關系專業(yè)關系專業(yè)號學生關系選修關系課程關系學號課程號參照完整性主碼？外碼？18例１：學生實體與專業(yè)實體間的關系：外碼參照關系被參照關系19參照完整性定義：外碼設F是參照關系R的一個或一組屬性，但不是R的碼，若F與被參照關系S的主碼相對應，則稱F是R的外碼（詳細定義見教材P54）規(guī)則：參照關系R中每個元組在外碼F上的值必須為：或者取空值（F的每個屬性值均為空值）或者等于S中某個元組的主碼值例３：學生（學號，姓名，性別，專業(yè)號，年齡，班長）參照關系被參照關系外碼19參照完整性定義：外碼例３：學生（學號，姓名，性別，專業(yè)號20用戶定義完整性用戶定義的、具體應用中的數(shù)據(jù)必須滿足的約束條件成績：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圖向關系模型的轉(zhuǎn)換原則一個實體轉(zhuǎn)換為一個關系模式，實體的屬性就是關系的屬性，實體的碼就是關系的碼對實體間的聯(lián)系一個1:1聯(lián)系可以轉(zhuǎn)換為一個獨立的關系模式，也可以與任意對應的關系模式合并一個1:n聯(lián)系可以轉(zhuǎn)換為一個獨立的關系模式，也可以與n端對應的關系模式合并一個m:n聯(lián)系轉(zhuǎn)換為一個關系模式三個或三個以上實體間的一個多元聯(lián)系可以轉(zhuǎn)換為一個關系模式具有相同碼的關系模式可以合并補充：25E-R圖向關系模型的轉(zhuǎn)換原則一個實體轉(zhuǎn)換為一個關系模式，26如公司部門管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關系模式部門項目職工電話包括承擔n1n1辦公室號，面積項目號，預算費參與mn辦公室包含1n包括n1部門號，預算費，領導人職工號電話號碼，說明職工號，姓名，辦公電話分擔任務部門(部門號，部門預算費，領導人職工號)職工(職工號，姓名，辦公電話，部門號)辦公室(辦公室號，面積，部門號)項目(項目號，項目預算費，部門號)電話(電話號碼，說明，辦公室號)項目承擔情況(職工號，項目號，分擔任務)26如公司部門管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關系模式部門項目27ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.27ExerciseRequired:Convertth283.5FunctionalDependenciesDefinitionofFunctionalDependency(函數(shù)依賴)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數(shù)據(jù)庫系統(tǒng)概論推薦：第五章，第六章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補充：關系的完整性實體完整性參照完整性用戶定義完整性

實體完整性和參照完整性是關系模型必須滿足的，被稱作關系的不變性，由關系數(shù)據(jù)庫系統(tǒng)自動支持★16補充：關系的完整性實體完整性★69實體完整性規(guī)則：若屬性A是基本關系R的主屬性，則屬性A不能取空值說明：基本關系的主碼中的任何屬性都不能取空值，而不僅是主碼整體不能取空值依據(jù)：現(xiàn)實世界的實體是唯一可分的例：學生（學號，姓名，性別，專業(yè)號，年齡）課程（課程號，課程名，學分）選修（學號，課程號，成績）17實體完整性規(guī)則：若屬性A是基本關系R的主屬性，則屬性A不70例１：學生實體與專業(yè)實體間的關系：學生（學號，姓名，性別，專業(yè)號，年齡）專業(yè)（專業(yè)號，專業(yè)名）關系參照圖外碼參照關系被參照關系例２：學生，課程，學生與課程之間的多對多聯(lián)系：學生（學號，姓名，性別，專業(yè)號，年齡）課程（課程號，課程名，學分）選修（學號，課程號，成績）關系參照圖

被參照關系參照關系學生關系專業(yè)關系專業(yè)號學生關系選修關系課程關系學號課程號參照完整性主碼？外碼？18例１：學生實體與專業(yè)實體間的關系：外碼參照關系被參照關系71參照完整性定義：外碼設F是參照關系R的一個或一組屬性，但不是R的碼，若F與被參照關系S的主碼相對應，則稱F是R的外碼（詳細定義見教材P54）規(guī)則：參照關系R中每個元組在外碼F上的值必須為：或者取空值（F的每個屬性值均為空值）或者等于S中某個元組的主碼值例３：學生（學號，姓名，性別，專業(yè)號，年齡，班長）參照關系被參照關系外碼19參照完整性定義：外碼例３：學生（學號，姓名，性別，專業(yè)號72用戶定義完整性用戶定義的、具體應用中的數(shù)據(jù)必須滿足的約束條件成績：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圖向關系模型的轉(zhuǎn)換原則一個實體轉(zhuǎn)換為一個關系模式，實體的屬性就是關系的屬性，實體的碼就是關系的碼對實體間的聯(lián)系一個1:1聯(lián)系可以轉(zhuǎn)換為一個獨立的關系模式，也可以與任意對應的關系模式合并一個1:n聯(lián)系可以轉(zhuǎn)換為一個獨立的關系模式，也可以與n端對應的關系模式合并一個m:n聯(lián)系轉(zhuǎn)換為一個關系模式三個或三個以上實體間的一個多元聯(lián)系可以轉(zhuǎn)換為一個關系模式具有相同碼的關系模式可以合并補充：25E-R圖向關系模型的轉(zhuǎn)換原則一個實體轉(zhuǎn)換為一個關系模式，78如公司部門管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關系模式部門項目職工電話包括承擔n1n1辦公室號，面積項目號，預算費參與mn辦公室包含1n包括n1部門號，預算費，領導人職工號電話號碼，說明職工號，姓名，辦公電話分擔任務部門(部門號，部門預算費，領導人職工號)職工(職工號，姓名，辦公電話，部門號)辦公室(辦公室號，面積，部門號)項目(項目號，項目預算費，部門號)電話(電話號碼，說明，辦公室號)項目承擔情況(職工號，項目號，分擔任務)26如公司部門管理系統(tǒng)的E-R圖及其轉(zhuǎn)換成的關系模式部門項目79ExerciseRequired:ConverttheE-Rdiagramyoudesignedinlastexerciseintoarelationaldatabaseschema.27ExerciseRequired:Convertth803.5FunctionalDependenciesDefinitionofFunctionalDependency(函數(shù)依賴)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