kc第8讲数据库规范化理论

《kc第8讲数据库规范化理论》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、第8讲:(第7章)关系数据库设计：规范化理论(一)重庆大学计算机学院课程名称:数据库系统--------------------第8讲：关系数据库设计：规范化理论(一)项目驱动目标：如何得到一个优化的关系数据库结构！一、良好关系模式的标准二、第一范式(1NF)三、函数依赖四、巴赫范式&第三范式五、规范化基础理论主要讨论问题：好的关系模式有什么标准什么是第一范式(1NF)什么是函数依赖，有何作用什么是BCNF和第三范式什么是函数依赖集的闭包，有何作用什么是属性集的闭包，有何作用Exercise8TheBankingSchema-根据ER图获得的bran

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

3、number,branch_name)loan_branch=(loan_number,branch_name)borrower=(customer_id,loan_number)depositor=(customer_id,account_number,access_date)cust_banker=(customer_id,employee_id,type)works_for=(worker_employee_id,manager_employee_id)---payment=(loan_number,payment_number,payment_date

4、,payment_amount)---savings_account=(account_number,interest_rate)checking_account=(account_number,overdraft_amount)一良好关系模式的标准1-1这是最终的数据结构？1-2好的结构有无判定标准？问题1答案随意地CombineSchemasSupposewecombineborrowerandloantogetbor_loan=(customer_id,loan_number,amount)Resultispossiblerepetitionofinfo

5、rmation(L-100inexamplebelow)1-3模式可以合并吗？良好关系模式的标准可能会产生数据冗余！冗余数据返回ACombinedSchemaWithoutRepetitionConsidercombiningloan_branchandloanloan_amt_br=(loan_number,amount,branch_name)Norepetition重复(assuggestedbyexamplebelow)1-4模式合并有无价值？良好关系模式的标准可起到简化存储和提高查询效率的作用！WhatAboutSmallerSchemas?不能随意

6、分解：Notalldecompositionsaregood.Supposewedecomposeemployeeintoemployee1=(employee_id,employee_name)employee2=(employee_name,telephone_number,start_date)Thenextslideshowshowweloseinformation--wecannotreconstructtheoriginalemployeerelation--andso,thisisalossydecomposition(有损分解).分解有价值、有

7、标准：Supposewehadstartedwithbor_loan参考.Howwouldweknowtosplitup(decompose)itintoborrowerandloan?Writearule“iftherewereaschema(loan_number,amount),thenloan_numberwouldbeacandidatekey”Denoteitasafunctionaldependency:loan_numberamountInbor_loan,becauseloan_numberisnotacandidatekey,theamo

8、untofaloanmayhaveto