数据库系统概念5e-Chapter_7__Relational_Database_Design.ppt

《数据库系统概念5e-Chapter_7__Relational_Database_Design.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Chapter7:RelationalDatabaseDesignChapter7:RelationalDatabaseDesignFeaturesofGoodRelationalDesignAtomicDomainsandFirstNormalFormDecompositionUsingFunctionalDependenciesFunctionalDependencyTheoryAlgorithmsforFunctionalDependenciesDecompositionUsingMultivaluedDependenciesMoreNormalFo

2、rmDatabase-DesignProcessModelingTemporalDataTheBankingSchemabranch=(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_

3、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_i

4、d)payment=(loan_number,payment_number,payment_date,payment_amount)savings_account=(account_number,interest_rate)checking_account=(account_number,overdraft_amount)CombineSchemas?Supposewecombineborrowerandloantogetbor_loan=(customer_id,loan_number,amount)Resultispossiblerepetitiono

5、finformation(L-100inexamplebelow)ACombinedSchemaWithoutRepetitionConsidercombiningloan_branchandloanloan_amt_br=(loan_number,amount,branch_name)Norepetition(assuggestedbyexamplebelow)WhatAboutSmallerSchemas?Supposewehadstartedwithbor_loan.Howwouldweknowtosplitup(decompose)itintobo

6、rrowerandloan?Writearule“iftherewereaschema(loan_number,amount),thenloan_numberwouldbeacandidatekey”Denoteasafunctionaldependency:loan_numberamountInbor_loan,becauseloan_numberisnotacandidatekey,theamountofaloanmayhavetoberepeated.Thisindicatestheneedtodecomposebor_loan.Notalldec

7、ompositionsaregood.Supposewedecomposeemployeeintoemployee1=(employee_id,employee_name)employee2=(employee_name,telephone_number,start_date)Thenextslideshowshowweloseinformation--wecannotreconstructtheoriginalemployeerelation--andso,thisisalossydecomposition.ALossyDecompositionFirs

8、tNormalFormDomainisatomicifitsele