数据库系统概念5e-Chapter_7__Relational_Database_Design

DatabaseSystemConcepts,5thEd.©Silberschatz,KorthandSudarshanSee©Silberschatz,KorthandSudarshan7.2DatabaseSystemConcepts-5thEdition,July28,2005.Chapter7:RelationalDatabaseDesignFeaturesofGoodRelationalDesignAtomicDomainsandFirstNormalFormDecompositionUsingFunctionalDependenciesFunctionalDependencyTheoryAlgorithmsforFunctionalDependenciesDecompositionUsingMultivaluedDependenciesMoreNormalFormDatabase-DesignProcessModelingTemporalData©Silberschatz,KorthandSudarshan7.3DatabaseSystemConcepts-5thEdition,July28,2005.TheBankingSchemabranch=(branch_name,branch_city,assets)customer=(customer_id,customer_name,customer_street,customer_city)loan=(loan_number,amount)account=(account_number,balance)employee=(employee_id.employee_name,telephone_number,start_date)dependent_name=(employee_id,dname)account_branch=(account_number,branch_name)loan_branch=(loan_number,branch_name)borrower=(customer_id,loan_number)depositor=(customer_id,account_number)cust_banker=(customer_id,employee_id,type)works_for=(worker_employee_id,manager_employee_id)payment=(loan_number,payment_number,payment_date,payment_amount)savings_account=(account_number,interest_rate)checking_account=(account_number,overdraft_amount)©Silberschatz,KorthandSudarshan7.4DatabaseSystemConcepts-5thEdition,July28,2005.CombineSchemas?Supposewecombineborrowerandloantogetbor_loan=(customer_id,loan_number,amount)Resultispossiblerepetitionofinformation(L-100inexamplebelow)©Silberschatz,KorthandSudarshan7.5DatabaseSystemConcepts-5thEdition,July28,2005.ACombinedSchemaWithoutRepetitionConsidercombiningloan_branchandloanloan_amt_br=(loan_number,amount,branch_name)Norepetition(assuggestedbyexamplebelow)©Silberschatz,KorthandSudarshan7.6DatabaseSystemConcepts-5thEdition,July28,2005.WhatAboutSmallerSchemas?Supposewehadstartedwithbor_loan.Howwouldweknowtosplitup(decompose)itintoborrowerandloan?Writearule“iftherewereaschema(loan_number,amount),thenloan_numberwouldbeacandidatekey”Denoteasafunctionaldependency:loan_numberamountInbor_loan,becauseloan_numberisnotacandidatekey,theamountofaloanmayhavetoberepeated.Thisindicatestheneedtodecomposebor_loan.Notalldecompositionsaregood.Supposewedecomposeemployeeintoemployee1=(employee_id,employee_name)employee2=(employee_name,telephone_number,start_date)Thenextslideshowshowweloseinformation--wecannotreconstructtheoriginalemployeerelation--andso,thisisalossydecomposition.©Silberschatz,KorthandSudarshan7.7DatabaseSystemConcepts-5thEdition,July28,2005.ALossyDecomposition©Silberschatz,KorthandSudarshan7.8DatabaseSystemConcepts-5thEdition,July28,2005.FirstNormalFormDomainisatomicifitselementsareconsideredtobeindivisibleunitsExamplesofnon-atomicdomains:Setofnames,compositeattributesIdentificationnumberslikeCS101thatcanbebrokenupintopartsArelationalschemaRisinfirstnormalformifthedomainsofallattributesofRareatomicNon-atomicvaluescomplicatestorageandencourageredundant(repeated)storageofdataExample:Setofaccountsstoredwitheachcustomer,andsetofownersstoredwitheachaccountWeassumeallrelationsareinfirstnormalform(andrevisitthisinChapter9)©Silberschatz,KorthandSudarshan7.9DatabaseSystemConcepts-5thEdition,July28,2005.FirstNormalForm(Cont’d)Atomicityisactuallyapropertyofhowtheelementsofthedomainareused.Example:StringswouldnormallybeconsideredindivisibleSupposethatstudentsaregivenrollnumberswhicharestringsoftheformCS0012orEE1127Ifthefirsttwocharactersareextractedtofindthedepartment,thedomainofrollnumbersisnotatomic.Doingsoisabadidea:leadstoencodingofinformationinapplicationprogramratherthaninthedatabase.©Silberschatz,KorthandSudarshan7.10DatabaseSystemConcepts-5thEdition,July28,2005.Goal—DeviseaTheoryfortheFollowingDecidewhetheraparticularrelationRisin“good”form.InthecasethatarelationRisnotin“good”form,decomposeitintoasetofrelations{R1,R2,...,Rn}suchthateachrelationisingoodformthedecompositionisalossless-joindecompositionOurtheoryisbasedon:functionaldependenciesmultivalueddependencies©Silberschatz,KorthandSudarshan7.11DatabaseSystemConcepts-5thEdition,July28,2005.FunctionalDependenciesConstraintsonthesetoflegalrelations.Requirethatthevalueforacertainsetofattributesdeterminesuniquelythevalueforanothersetofattributes.Afunctionaldependencyisageneralizationofthenotionofakey.©Silberschatz,KorthandSudarshan7.12DatabaseSystemConcepts-5thEdition,July28,2005.FunctionalDependencies(Cont.)LetRbearelationschemaRandRThefunctionaldependencyholdsonRifandonlyifforanylegalrelationsr(R),wheneveranytwotuplest1andt2ofragreeontheattributes,theyalsoagreeontheattributes.Thatis,t1[]=t2[]t1[]=t2[]Example:Considerr(A,B)withthefollowinginstanceofr.Onthisinstance,ABdoesNOThold,butBAdoeshold.141537©Silberschatz,KorthandSudarshan7.13DatabaseSystemConcepts-5thEdition,July28,2005.FunctionalDependencies(Cont.)KisasuperkeyforrelationschemaRifandonlyifKRKisacandidatekeyforRifandonlyifKR,andfornoK,RFunctionaldependenciesallowustoexpressconstrai