浙江大学数据库系统概念PPT第七章,对应原版教材第五版ppt课件.ppt

《浙江大学数据库系统概念PPT第七章,对应原版教材第五版ppt课件.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Chapter7:RelationalDatabaseDesignIntroductionofRelationalDatabaseDesignAtomicDomainsandFirstNormalFormDecompositionUsingFunctionalDependenciesDecompositionFunctionalDependencyBoyce-CoddNormalFormThirdNormalFormMulti-ValueDependencyand4NFOverallDatabaseDesignProcessIntroductionThegoalofarela

2、tionaldatabasedesignistogenerateasetofrelationschemasthatallowsusTostoreinformationwithoutunnecessaryredundancyToretrieveinformationeasilyThisisaccomplishedbydesigningschemasthatareinanappropriatenormalform.Basically,itishowtogeneralagoodrelationschemastorepresentanenterprisedatabase.TheBan

3、kingSchemabranch=(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=(accou

4、nt_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)savi

5、ngs_account=(account_number,interest_rate)checking_account=(account_number,overdraft_amount)DesignAlternative:LargerSchema?Supposewecombineborrowerandloantogetbor_loan=(customer_id,loan_number,amount)Resultispossiblerepetitionofinformation(L-100inexamplebelow)ACombinedSchemaWithoutRepetitio

6、nConsidercombiningloan_branchandloanloan_amt_br=(loan_number,amount,branch_name)Norepetition(assuggestedbyexamplebelow)DesignAlternative:Smallerschemas?Supposewehadstartedwithbor_loan.Howwouldweknowtosplitup(decompose)itintoborrowerandloan?Onlydeterminetheprimarykey(candidatekeys)ofbor_loan

7、,whichis(customer_id,loan_number)isthiscase,doesnothelptosplitup.Therequirementanalyzermaycomingupwithrulelikes,“eachspecificvalueforloan_numbercorrespondstoatmostoneammount”Denoteasafunctionaldependency:loan_numberamountObservationsofsuchruleswillleadt