FunctionalDependenciesPart2 InpracticeitisoftenthecasethatsomeFDsareeasiertoseewhileothersaremoresubtleandhardertoobserveSomeofthosesubtleFDsfortunatelycanbederivedfromtheeasyonesInotherwordst ID: 336855
Download Pdf The PPT/PDF document "Department of computer science and engin..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Indesigningadatabase,forthepurposeofminimizingredundancy,weneedtocollectasetFoffunctionaldependencies(FD)thatre ecttheconstraintsoftheunderlyingapplication.Ideally,wedonotwanttomissanyFD,i.e.,wewanttoobtainanFthatisaslargeaspossible.However,inpractice,FDcollectionisadicultprocess.NoonecanguaranteealwaysdiscoveringallFDs. FunctionalDependencies:Part2 Inpractice,itisoftenthecasethatsomeFDsareeasiertosee,whileothersaremoresubtleandhardertoobserve.SomeofthosesubtleFDs,fortunately,canbederivedfromtheeasyones.Inotherwords,thederivationpermitsusto\rescue"someFDsthathaveskippedourattention.HowaboutthesubtleFDsthatcannotbederivedfromtheeasyones?Thereisnothingwecandoaboutthem,unfortunately,andwillhavetocontinuethedesignwithoutthem.Thisiswhyevenanexperienceddatabaseprofessionalmaynotalwaysbeabletocomeupwithaperfectdesign! FunctionalDependencies:Part2 LetusidentifyanimportantspecialtypeofFDs: Denition AfunctionaldependencyX!YisregularifYcontainsonlyasingleattribute. Forexample,AB!Cisregular,butAB!CDisnot,whereA;B;C,andDareattributes. FunctionalDependencies:Part2 ThereisanequivalencethatexplainswhywecancaptureallirregularFDsbyconsideringonlyregularones: AnirregularFDX!A1A2:::At(whereXisanattributeset,andeachAi(1it)isanattribute)isequivalentto:X!A1X!A2:::X!AtExample:AB!CDifandonlyifAB!CandAB!D. FunctionalDependencies:Part2 Again,letFbethesetofFDswehavecollected.Then: Denition TheclosureofF,denotedasF+,isthesetofallregularFDsthatcanbederivedfromF. DonotconfusetheclosureofFwiththeclosureofanattributeset. FunctionalDependencies:Part2 Example.Assumethatthereare4attributesA;B;C;D,andthatF=fA!B;B!Cg.Then,F+includesallthefollowingFDs:A!A,A!B,A!C,B!B,B!C,C!C,D!D,AB!A,AB!B,AB!C,AC!A,AC!B,AC!C,AD!A,AD!B,AD!C,AD!D,BC!B,BC!C,BD!B,BD!C,BD!D,CD!C,CD!D,ABC!A,ABC!B,ABC!C,ABD!A,ABD!B,ABD!C,ABD!D,BCD!B,BCD!C,BCD!D,ABCD!A,ABCD!B,ABCD!C,ABCD!D. FunctionalDependencies:Part2 FindingtheClosureofaSetofFDs algorithm(F)/*FisasetofFDs*/1.F+=;2.foreachpossibleattributesetX3.computetheclosureX+ofXonF4.foreachattributeA2X+5.addtoF+theFD:X!A5.returnF+ FunctionalDependencies:Part2 Example.Assumethatthereare4attributesA;B;C;D,andthatF=fA!B;B!Cg.TocomputeF+,werstget: A+=AB+=AC+=ABC+=fA;B;Cg B+=BC+=fB;Cg C+=fCg D+=fDg AD+=fA;Dg BC+=fB;Cg BD+=BCD+=fB;C;Dg ABD+=ABCD+=fA;B;C;Dg ACD+=fA;C;DgItiseasytogeneratetheFDsinF+fromtheclosuresoftheaboveattributesets. FunctionalDependencies:Part2 CandidateKeyRevisited Increatingatable,itmayseemthatsofarwehavebeenspecifyingcandidatekeysbasedonourpreferences.ThisillusioniscreatedbecausewedidnotunderstandFDs.Infact,candidatekeysarenotuptousatall.Instead,theyareuniquelydeterminedbythesetFoffunctionaldependenciesfromtheunderlyingapplication.Seethenextslide. FunctionalDependencies:Part2 CandidateKeyRevisited LetFbeasetofFDs,andRarelation. Denition AcandidatekeyisasetXofattributesinRsuchthat X+includesalltheattributesinR. ThereisnopropersubsetYofXsuchthatY+includesalltheattributesinR. Note:ApropersubsetYisasubsetofXsuchthatY6=X(i.e.,XhasatleastoneelementnotinY). FunctionalDependencies:Part2 Example.ConsideratableR(A;B;C;D),andthatF=fA!B;B!Cg. Aisnotacandidatekey,becauseA+=fA;B;CgwhichdoesnotincludeD. ABDisnotacandidatekeyeventhoughABD+=fA;B;C;Dg.ThisisbecauseAD+=fA;B;C;Dg,namely,thereisapropersubsetADofABDsuchthatAD+includesalltheattributes. ADisacandidatekey. FunctionalDependencies:Part2