1 Suppose that we decompose the schema into Show that this decomposition is a losslessjoin decomposit ion if the following set of functional dependencies holds BC CD Answer A decomposition is a losslessjoin decomposition if or Let and Since is a ID: 20128
Download Pdf The PPT/PDF document "C H A P T E R Relational Database Design..." 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.
CHAPTER 8RelationalDatabaseDesignExercises 8.1SupposethatwedecomposetheschemaRD(A,B,C,D,E)into(A,B,C)(A,D,E).Showthatthisdecompositionisalossless-joindecompositionifthefollowingsetFoffunctionaldependenciesholds:A!BCCD!EB!DE!A Answer: AdecompositionfR1;R2gisalossless-joindecompositionifR1\R2!R1orR1\R2!R2.LetR1D(A;B;C);R2D(A;D;E);andR1\R2DA.SinceAisacandidatekey(seePracticeExercise8.6),ThereforeR1\R2!R1.8.2Listallfunctionaldependenciessatis®edbytherelationofFigure8.17. Answer: Thenontrivialfunctionaldependenciesare:A!BandC!B,andadependencytheylogicallyimply:AC!B.Thereare19trivialfunctionaldependenciesoftheforma!b,whereba.CdoesnotfunctionallydetermineAbecausethe®rstandthirdtupleshavethesameCbutdifferentAvalues.ThesametuplesalsoshowBdoesnotfunctionallydetermineA.Likewise,AdoesnotfunctionallydetermineCbecausethe®rsttwotupleshavethesameAvalueanddifferentCvalues.ThesametuplesalsoshowBdoesnotfunctionallydetermineC.8.3Explainhowfunctionaldependenciescanbeusedtoindicatethefol-lowing:9 10Chapter8 RelationalDatabaseDesign ·Aone-to-onerelationshipsetexistsbetweenentitysetsstudentandinstructor.·Amany-to-onerelationshipsetexistsbetweenentitysetsstudentandinstructor. Answer: LetPk(r)denotetheprimarykeyattributeofrelationr.·ThefunctionaldependenciesPk(student)!Pk(instructor)andPk(instructor)!Pk(student)indicateaone-to-onerelationshipbecauseanytwotupleswiththesamevalueforstudentmusthavethesamevalueforinstructor,andanytwotuplesagreeingoninstructormusthavethesamevalueforstudent.·ThefunctionaldependencyPk(student)!Pk(instructor)indicatesamany-to-onerelationshipsinceanystudentvaluewhichisrepeatedwillhavethesameinstructorvalue,butmanystudentvaluesmayhavethesameinstructorvalue.8.4UseArmstrong'saxiomstoprovethesoundnessoftheunionrule.(Hint:Usetheaugmentationruletoshowthat,ifa!b,thena!ab.Applytheaugmentationruleagain,usinga!g,andthenapplythetransitivityrule.) Answer: Toprovethat:ifa!banda!gthena!bgFollowingthehint,wederive:a!bgivenaa!abaugmentationrulea!abunionofidenticalsetsa!ggivenab!gbaugmentationrulea!bgtransitivityruleandsetunioncommutativity8.5UseArmstrong'saxiomstoprovethesoundnessofthepseudotransitiv-ityrule. Answer: ProofusingArmstrong'saxiomsofthePseudotransitivityRule:ifa!bandgb!d,thenag!d.a!bgivenag!gbaugmentationruleandsetunioncommutativitygb!dgivenag!dtransitivityrule8.6ComputetheclosureofthefollowingsetFoffunctionaldependenciesforrelationschemaRD(A;B;C;D;E). Exercises 11A!BCCD!EB!DE!AListthecandidatekeysforR. Answer: Note:ItisnotreasonabletoexpectstudentstoenumerateallofFC.SomeshorthandrepresentationoftheresultshouldbeacceptableaslongasthenontrivialmembersofFCarefound.StartingwithA!BC,wecanconclude:A!BandA!C.SinceA!BandB!D,A!D(decomposition,transitive)SinceA!CDandCD!E,A!E(union,decom-position,transi-tive)SinceA!A,wehave(re¯exive)A!ABCDEfromtheabovesteps(union)SinceE!A,E!ABCDE(transitive)SinceCD!E,CD!ABCDE(transitive)SinceB!DandBC!CD,BC!ABCDE(augmentative,transitive)Also,C!C,D!D,BD!D,etc.Therefore,anyfunctionaldependencywithA,E,BC,orCDonthelefthandsideofthearrowisinFC,nomatterwhichotherattributesappearintheFD.Allow*torepresentanysetofattributesinR,thenFCisBD!B,BD!D,C!C,D!D,BD!BD,B!D,B!B,B!BD,andallFDsoftheformA!a,BC!a,CD!a,E!awhereaisanysubsetoffA;B;C;D;Eg.ThecandidatekeysareA;BC;CD;andE.8.7UsingthefunctionaldependenciesofPracticeExercise8.6,computethecanonicalcoverFc. Answer: ThegivensetofFDsFis:-A!BCCD!EB!DE!ATheleftsideofeachFDinFisunique.AlsononeoftheattributesintheleftsideorrightsideofanyoftheFDsisextraneous.ThereforethecanonicalcoverFcisequaltoF. 12Chapter8 RelationalDatabaseDesign 8.8ConsiderthealgorithminFigure8.18tocomputeaC.Showthatthisalgorithmismoreef®cientthantheonepresentedinFigure8.8(Sec-tion8.4.2)andthatitcomputesaCcorrectly. Answer: Thealgorithmiscorrectbecause:·IfAisaddedtoresultthenthereisaproofthata!A.Toseethis,observethata!atriviallysoaiscorrectlypartofresult.IfA62aisaddedtoresulttheremustbesomeFDb!gsuchthatA2gandbisalreadyasubsetofresult.(Otherwisefdcountwouldbenonzeroandtheifconditionwouldbefalse.)Afullproofcanbegivenbyinductiononthedepthofrecursionforanexecutionofaddin,butsuchaproofcanbeexpectedonlyfromstudentswithagoodmathematicalbackground.·IfA2aC,thenAiseventuallyaddedtoresult.Weprovethisbyinductiononthelengthoftheproofofa!AusingArmstrong'saxioms.Firstobservethatifprocedureaddiniscalledwithsomeargumentb,alltheattributesinbwillbeaddedtoresult.AlsoifaparticularFD'sfdcountbecomes0,alltheattributesinitstailwillde®nitelybeaddedtoresult.Thebasecaseoftheproof,A2a)A2aC,isobviouslytruebecausethe®rstcalltoaddinhastheargumenta.Theinductivehypothesesisthatifa!AcanbeprovedinnstepsorlessthenA2result:IfthereisaproofinnC1stepsthata!A,thenthelaststepwasanapplicationofeitherre¯exivity,augmentationortransitivityonafacta!bprovedinnorfewersteps.Ifre¯exivityoraugmentationwasusedinthe(nC1)ststep,Amusthavebeeninresultbytheendofthenthstepitself.Otherwise,bytheinductivehypothesisbresult.Thereforethedependencyusedinprovingb!g,A2gwillhavefdcountsetto0bytheendofthenthstep.HenceAwillbeaddedtoresult.Toseethatthisalgorithmismoreef®cientthantheonepresentedinthechapternotethatwescaneachFDonceinthemainprogram.TheresultingarrayappearshassizeproportionaltothesizeofthegivenFDs.Therecursivecallstoaddinresultinprocessinglinearinthesizeofappears.HencethealgorithmhastimecomplexitywhichislinearinthesizeofthegivenFDs.Ontheotherhand,thealgorithmgiveninthetexthasquadratictimecomplexity,asitmayperformtheloopasmanytimesasthenumberofFDs,ineachloopscanningallofthemonce.8.9GiventhedatabaseschemaR(a;b;c),andarelationrontheschemaR,writeanSQLquerytotestwhetherthefunctionaldependencyb!choldsonrelationr.AlsowriteanSQLassertionthatenforcesthefunc-tionaldependency.Assumethatnonullvaluesarepresent.(AlthoughpartoftheSQLstandard,suchassertionsarenotsupportedbyanydatabaseimplementationcurrently.) Answer: Exercises 13a.Thequeryisgivenbelow.Itsresultisnon-emptyifandonlyifb!cdoesnotholdonr.selectbfromrgroupbybhavingcount(distinctc)�1b.createassertionb to ccheck(notexists(selectbfromrgroupbybhavingcount(distinctc)�1))8.10Ourdiscussionoflossless-joindecompositionimplicitlyassumedthatattributesontheleft-handsideofafunctionaldependencycannottakeonnullvalues.Whatcouldgowrongondecomposition,ifthispropertyisviolated? Answer: Thenaturaljoinoperatorisde®nedintermsofthecartesianproductandtheselectionoperator.Theselectionoperator,givesunknownforanyqueryonanullvalue.Thus,thenaturaljoinexcludesalltupleswithnullvaluesonthecommonattributesfromthe®nalresult.Thus,thedecompositionwouldbelossy(inamannerdifferentfromtheusualcaseoflossydecomposition),ifnullvaluesoccurintheleft-handsideofthefunctionaldependencyusedtodecomposetherelation.(Nullvaluesinattributesthatoccuronlyintheright-handsideofthefunctionaldependencydonotcauseanyproblems.)8.11IntheBCNFdecompositionalgorithm,supposeyouuseafunctionalde-pendencya!btodecomposearelationschemar(a;b;g)intor1(a;b)andr2(a;g).a.Whatprimaryandforeign-keyconstraintdoyouexpecttoholdonthedecomposedrelations?b.Giveanexampleofaninconsistencythatcanariseduetoanerroneousupdate,iftheforeign-keyconstraintwerenotenforcedonthedecomposedrelationsabove.c.Whenarelationisdecomposedinto3NFusingthealgorithminSection8.5.2,whatprimaryandforeignkeydependencieswouldyouexpectwillholdonthedecomposedschema? 14Chapter8 RelationalDatabaseDesignAnswer: a.ashouldbeaprimarykeyforr1,andashouldbetheforeignkeyfromr2,referencingr1.b.Iftheforeignkeyconstraintisnotenforced,thenadeletionofatuplefromr1wouldnothaveacorrespondingdeletionfromthereferencingtuplesinr2.Insteadofdeletingatuplefromr,thiswouldamounttosimplysettingthevalueofatonullinsometuples.c.Foreveryschemari(ab)addedtotheschemabecauseofarulea!b,ashouldbemadetheprimarykey.Also,acandidatekeygfortheoriginalrelationislocatedinsomenewlycreatedrelationrk,andisaprimarykeyforthatrelation.Foreignkeyconstraintsarecreatedasfollows:foreachrelationricreatedabove,iftheprimarykeyattributesofrialsooccurinanyotherrelationrj,thenaforeignkeyconstraintiscreatedfromthoseattributesinrj,referencing(theprimarykeyof)ri.8.12LetR1;R2;:::;RnbeadecompositionofschemaU.Letu(U)bearela-tion,andletriD5RI(u).Showthatur11r211rn Answer: Considersometupletinu.NotethatriD5Ri(u)impliesthatt[Ri]2ri,1in.Thus,t[R1]1t[R2]1:::1t[Rn]2r11r21:::1rnBythede®nitionofnaturaljoin,t[R1]1t[R2]1:::1t[Rn]D5a(sb(t[R1]t[R2]:::t[Rn]))wheretheconditionbissatis®edifvaluesofattributeswiththesamenameinatupleareequalandwhereaDU.Thecartesianproductofsingletuplesgeneratesonetuple.Theselectionprocessissatis®edbecauseallattributeswiththesamenamemusthavethesamevaluesincetheyareprojectionsfromthesametuple.Finally,theprojectionclauseremovesduplicateattributenames.Bythede®nitionofdecomposition,UDR1[R2[:::[Rn,whichmeansthatallattributesoftareint[R1]1t[R2]1:::1t[Rn].Thatis,tisequaltotheresultofthisjoin.Sincetisanyarbitrarytupleinu,ur11r21:::1rn8.13ShowthatthedecompositioninPracticeExercise8.1isnotadependency-preservingdecomposition. Answer: ThedependencyB!Disnotpreserved.F1,therestrictionofFto(A;B;C)isA!ABC,A!AB,A!AC,A!BC, Exercises 15A!B,A!C,A!A,B!B,C!C,AB!AC,AB!ABC,AB!BC,AB!AB,AB!A,AB!B,AB!C,AC(sameasAB),BC(sameasAB),ABC(sameasAB).F2,therestrictionofFto(C;D;E)isA!ADE,A!AD,A!AE,A!DE,A!A,A!D,A!E,D!D,E(sameasA),AD,AE,DE,ADE(sameasA).(F1[F2)CiseasilyseennottocontainB!DsincetheonlyFDinF1[F2withBastheleftsideisB!B,atrivialFD.WeshallseeinPracticeExercise8.15thatB!DisindeedinFC.ThusB!Disnotpreserved.NotethatCD!ABCDEisalsonotpreserved.Asimplerargumentisasfollows:F1containsnodependencieswithDontherightsideofthearrow.F2containsnodependencieswithBontheleftsideofthearrow.ThereforeforB!DtobepreservedtheremustbeanFDB!ainFC1anda!DinFC2(soB!Dwouldfollowbytransitivity).SincetheintersectionofthetwoschemesisA,aDA.ObservethatB!AisnotinFC1sinceBCDBD.8.14Showthatitispossibletoensurethatadependency-preservingdecom-positioninto3NFisalossless-joindecompositionbyguaranteeingthatatleastoneschemacontainsacandidatekeyfortheschemabeingdecom-posed.(Hint:Showthatthejoinofalltheprojectionsontotheschemasofthedecompositioncannothavemoretuplesthantheoriginalrelation.) Answer: LetFbeasetoffunctionaldependenciesthatholdonaschemaR.LetsDfR1;R2;:::;Rngbeadependency-preserving3NFdecompo-sitionofR.LetXbeacandidatekeyforR.ConsideralegalinstancerofR.LetjD5X(r)15R1(r)15R2(r):::15Rn(r).WewanttoprovethatrDj.Weclaimthatift1andt2aretwotuplesinjsuchthatt1[X]Dt2[X],thent1Dt2.Toprovethisclaim,weusethefollowinginductiveargument±LetF0DF1[F2[:::[Fn,whereeachFiistherestrictionofFtotheschemaRiins.ConsidertheuseofthealgorithmgiveninFigure8.8tocomputetheclosureofXunderF0.Weuseinductiononthenumberoftimesthattheforloopinthisalgorithmisexecuted.·Basis:Inthe®rststepofthealgorithm,resultisassignedtoX,andhencegiventhatt1[X]Dt2[X],weknowthatt1[result]Dt2[result]istrue.·InductionStep:Lett1[result]Dt2[result]betrueattheendofthekthexecutionoftheforloop.SupposethefunctionaldependencyconsideredinthekC1thexecutionoftheforloopisb!g,andthatbresult.bresultimpliesthatt1[b]Dt2[b]istrue.Thefactsthatb!gholdsforsomeattributesetRiins,andthatt1[Ri]andt2[Ri]arein5Ri(r)implythatt1[g]Dt2[g]isalsotrue.Sincegisnowaddedtoresultbythealgorithm,weknowthatt1[result]Dt2[result]istrueattheendofthekC1thexecutionoftheforloop. 16Chapter8 RelationalDatabaseDesign Sincesisdependency-preservingandXisakeyforR,allattributesinRareinresultwhenthealgorithmterminates.Thus,t1[R]Dt2[R]istrue,thatis,t1Dt2±asclaimedearlier.Ourclaimimpliesthatthesizeof5X(j)isequaltothesizeofj.Notealsothat5X(j)D5X(r)Dr(sinceXisakeyforR).Thuswehaveprovedthatthesizeofjequalsthatofr.UsingtheresultofPracticeExercise8.12,weknowthatrj.HenceweconcludethatrDj.NotethatsinceXistriviallyin3NF,s[fXgisadependency-preservinglossless-joindecompositioninto3NF.8.15GiveanexampleofarelationschemaR0andsetF0offunctionaldepen-denciessuchthatthereareatleastthreedistinctlossless-joindecompo-sitionsofR0intoBCNF. Answer: GiventherelationR0D(A;B;C;D)thesetoffunctionaldependenciesF0DA!B,C!D,B!CallowsthreedistinctBCNFdecompositions.R1Df(A;B);(C;D);(B;C)gisinBCNFasisR2Df(A;B);(C;D);(A;C)gR2Df(A;B);(C;D);(A;C)gR3Df(B;C);(A;D);(A;B)g8.16Letaprimeattributebeonethatappearsinatleastonecandidatekey.Letaandbbesetsofattributessuchthata!bholds,butb!adoesnothold.LetAbeanattributethatisnotina,isnotinb,andforwhichb!Aholds.WesaythatAistransitivelydependentona.Wecanrestateourde®nitionof3NFasfollows:ArelationschemaRisin3NFwithrespecttoasetFoffunctionaldependenciesiftherearenononprimeattributesAinRforwhichAistransitivelydependentonakeyforR.Showthatthisnewde®nitionisequivalenttotheoriginalone. Answer: SupposeRisin3NFaccordingtothetextbookde®nition.Weshowthatitisin3NFaccordingtothede®nitionintheexercise.LetAbeanonprimeattributeinRthatistransitivelydependentonakeyaforR.ThenthereexistsbRsuchthatb!A;a!b;A62a;A62b;andb!adoesnothold.Butthenb!Aviolatesthetextbookde®nitionof3NFsince·A62bimpliesb!Aisnontrivial·Sinceb!adoesnothold,bisnotasuperkey·Aisnotanycandidatekey,sinceAisnonprime Exercises 17NowweshowthatifRisin3NFaccordingtotheexercisede®nition,itisin3NFaccordingtothetextbookde®nition.SupposeRisnotin3NFaccordingthethetextbookde®nition.ThenthereisanFDa!bthatfailsallthreeconditions.Thus·a!bisnontrivial.·aisnotasuperkeyforR.·SomeAinb aisnotinanycandidatekey.ThisimpliesthatAisnonprimeanda!A.LetgbeacandidatekeyforR.Theng!a;a!gdoesnothold(sinceaisnotasuperkey),A62a,andA62g(sinceAisnonprime).ThusAistransitivelydependentong,violatingtheexercisede®nition.8.17Afunctionaldependencya!biscalledapartialdependencyifthereisapropersubsetgofasuchthatg!b.Wesaythatbispartiallydependentona.ArelationschemaRisinsecondnormalform(2NF)ifeachattributeAinRmeetsoneofthefollowingcriteria:·Itappearsinacandidatekey.·Itisnotpartiallydependentonacandidatekey.Showthatevery3NFschemaisin2NF.(Hint:Showthateverypartialdependencyisatransitivedependency.) Answer: Referringtothede®nitionsinPracticeExercise8.16,arelationschemaRissaidtobein3NFifthereisnonon-primeattributeAinRforwhichAistransitivelydependentonakeyforR.Wecanalsorewritethede®nitionof2NFgivenhereas:ªArelationschemaRisin2NFifnonon-primeattributeAispartiallydependentonanycandidatekeyforR.ºToprovethatevery3NFschemaisin2NF,itsuf®cestoshowthatifanon-primeattributeAispartiallydependentonacandidatekeya,thenAisalsotransitivelydependentonthekeya.LetAbeanon-primeattributeinR.LetabeacandidatekeyforR.SupposeAispartiallydependentona.·Fromthede®nitionofapartialdependency,weknowthatforsomepropersubsetbofa,b!A.·Sinceba,a!b.Also,b!adoesnothold,sinceaisacandidatekey.·Finally,sinceAisnon-prime,itcannotbeineitherbora.Thusweconcludethata!Aisatransitivedependency.Hencewehaveprovedthatevery3NFschemaisalsoin2NF.8.18GiveanexampleofarelationschemaRandasetofdependenciessuchthatRisinBCNFbutisnotin4NF. 18Chapter8 RelationalDatabaseDesignAnswer: R(A,B,C)A!!B Exercises 19result:D;;/*fdcountisanarraywhoseithelementcontainsthenumberofattributesontheleftsideoftheithFDthatarenotyetknowntobeinaC*/fori:D1tojFjdobeginletb!gdenotetheithFD;fdcount[i]:Djbj;end/*appearsisanarraywithoneentryforeachattribute.TheentryforattributeAisalistofintegers.EachintegerionthelistindicatesthatAappearsontheleftsideoftheithFD*/foreachattributeAdobeginappears[A]:DNIL;fori:D1tojFjdobeginletb!gdenotetheithFD;ifA2bthenadditoappears[A];endendaddin(a);return(result);procedureaddin(a);foreachattributeAinadobeginifA62resultthenbeginresult:Dresult[fAg;foreachelementiofappears[A]dobeginfdcount[i]:Dfdcount[i] 1;iffdcount[i]:D0thenbeginletb!gdenotetheithFD;addin(g);endendendendFigure8.18.AnalgorithmtocomputeaC.