数据库系统概念全套配套课件PPT appB.ppt 21页

'AppendixB:AdvancedRelationalDatabaseDesign AppendixB:AdvancedRelationalDatabaseDesignReasoningwithMVDsHighernormalformsJoindependenciesandPJNFDKNF TheoryofMultivaluedDependenciesLetDdenoteasetoffunctionalandmultivalueddependencies.TheclosureD+ofDisthesetofallfunctionalandmultivalueddependencieslogicallyimpliedbyD.Soundandcompleteinferencerulesforfunctionalandmultivalueddependencies:1.Reflexivityrule.Ifisasetofattributesand,thenholds.2.Augmentationrule.Ifholdsandisasetofattributes,thenholds.3.Transitivityrule.Ifholdsandholds,thenholds. TheoryofMultivaluedDependencies(Cont.)4.Complementationrule.Ifholds,thenR––holds.5.Multivaluedaugmentationrule.IfholdsandRand,thenholds.6.Multivaluedtransitivityrule.Ifholdsandholds,then–holds.7.Replicationrule.Ifholds,then.8.Coalescencerule.IfholdsandandthereisasuchthatRand=and,thenholds. SimplificationoftheComputationofD+WecansimplifythecomputationoftheclosureofDbyusingthefollowingrules(provedusingrules1-8).Multivaluedunionrule.Ifholdsandholds,thenholds.Intersectionrule.Ifholdsandholds,thenholds.Differencerule.IfIfholdsandholds,then–holdsand–holds. ExampleR=(A,B,C,G,H,I)D={ABBHICGH}SomemembersofD+:ACGHI.SinceAB,thecomplementationrule(4)impliesthatAR–B–A.SinceR–B–A=CGHI,soACGHI.AHI.SinceABandBHI,themultivaluedtransitivityrule(6)impliesthatBHI–B.SinceHI–B=HI,AHI. Example(Cont.)SomemembersofD+(cont.):BH.Applythecoalescencerule(8);BHIholds.SinceHHIandCGHandCGHI=Ø,thecoalescenceruleissatisfiedwithbeingB,beingHI,beingCG,andbeingH.WeconcludethatBH.ACG.ACGHIandAHI.Bythedifferencerule,ACGHI–HI.SinceCGHI–HI=CG,ACG. NormalizationUsingJoinDependenciesJoindependenciesconstrainthesetoflegalrelationsoveraschemaRtothoserelationsforwhichagivendecompositionisalossless-joindecomposition.LetRbearelationschemaandR1,R2,...,RnbeadecompositionofR.IfR=R1R2….Rn,wesaythatarelationr(R)satisfiesthejoindependency*(R1,R2,...,Rn)if:r=R1(r)⋈R2(r)⋈……⋈Rn(r)AjoindependencyistrivialifoneoftheRiisRitself.Ajoindependency*(R1,R2)isequivalenttothemultivalueddependencyR1R2R2.Conversely,isequivalentto*((R-),)However,therearejoindependenciesthatarenotequivalenttoanymultivalueddependency. Project-JoinNormalForm(PJNF)ArelationschemaRisinPJNFwithrespecttoasetDoffunctional,multivalued,andjoindependenciesifforalljoindependenciesinD+oftheform*(R1,R2,...,Rn)whereeachRiRandR=R1R2...Rnatleastoneofthefollowingholds:*(R1,R2,...,Rn)isatrivialjoindependency.EveryRiisasuperkeyforR.Sinceeverymultivalueddependencyisalsoajoindependency,everyPJNFschemaisalsoin4NF. ExampleConsiderLoan-info-schema=(branch-name,customer-name,loan-number,amount).Eachloanhasoneormorecustomers,isinoneormorebranchesandhasaloanamount;theserelationshipsareindependent,hencewehavethejoindependency*(=(loan-number,branch-name),(loan-number,customer-name),(loan-number,amount))Loan-info-schemaisnotinPJNFwithrespecttothesetofdependenciescontainingtheabovejoindependency.ToputLoan-info-schemaintoPJNF,wemustdecomposeitintothethreeschemasspecifiedbythejoindependency:(loan-number,branch-name)(loan-number,customer-name)(loan-number,amount) Domain-KeyNormalForm(DKNY)Domaindeclaration.LetAbeanattribute,andletdombeasetofvalues.ThedomaindeclarationAdomrequiresthattheAvalueofalltuplesbevaluesindom.Keydeclaration.LetRbearelationschemawithKR.Thekeydeclarationkey(K)requiresthatKbeasuperkeyforschemaR(KR).Allkeydeclarationsarefunctionaldependenciesbutnotallfunctionaldependenciesarekeydeclarations.Generalconstraint.Ageneralconstraintisapredicateonthesetofallrelationsonagivenschema.LetDbeasetofdomainconstraintsandletKbeasetofkeyconstraintsforarelationschemaR.LetGdenotethegeneralconstraintsforR.SchemaRisinDKNFifDKlogicallyimplyG. ExampleAccountswhoseaccount-numberbeginswiththedigit9arespecialhigh-interestaccountswithaminimumbalanceof2500.Generalconstraint:``Ifthefirstdigitoft[account-number]is9,thent[balance]2500.""DKNFdesign:Regular-acct-schema=(branch-name,account-number,balance)Special-acct-schema=(branch-name,account-number,balance)Domainconstraintsfor{Special-acct-schema}requirethatforeachaccount:Theaccountnumberbeginswith9.Thebalanceisgreaterthan2500. DKNFrephrasingofPJNFDefinitionLetR=(A1,A2,...,An)bearelationschema.Letdom(Ai)denotethedomainofattributeAi,andletallthesedomainsbeinfinite.ThenalldomainconstraintsDareoftheformAidom(Ai).LetthegeneralconstraintsbeasetGoffunctional,multivalued,orjoindependencies.IfFisthesetoffunctionaldependenciesinG,letthesetKofkeyconstraintsbethosenontrivialfunctionaldependenciesinF+oftheformR.SchemaRisinPJNFifandonlyifitisinDKNFwithrespecttoD,K,andG. EndofAppendixB FigureB.01 FigureB.02 FigureB.03 FigureB.04 FigureB.05 FigureB.06 FigureB.07'