DataBase System Part2 normalization iNtroduction ce Functional Dependencies e Normal forms G Lossless decompositions Additional Design Considerations Haichang Gao, Software School, Xidian University 3
DataBase System Haichang Gao , Software School , Xidian University 3 Introduction Functional Dependencies Normal Forms Lossless Decompositions Additional Design Considerations Part2 normalization
DataBase System Introduction Normalization(规范化) is another approach to logical design of a relational database. g E-R approach and normalization approach reinforce each other g Normalization starts with a real-world situation to be modeled and lists the data items that are candidates to become column names in relational tables, together with a list of rules about the relatedness of these data items g The aim is to represent all these data items as attributes of tables that obey restrictive conditions associated with what we call normal forms(范式 E INF-->2NF-->3NF-->BCNF-->4NF--> 5NF Haichang Gao, Software School, Xidian University
DataBase System Haichang Gao , Software School , Xidian University 4 Normalization (规范化) is another approach to logical design of a relational database. E-R approach and normalization approach reinforce each other. Normalization starts with a real-world situation to be modeled and lists the data items that are candidates to become column names in relational tables, together with a list of rules about the relatedness of these data items. The aim is to represent all these data items as attributes of tables that obey restrictive conditions associated with what we call normal forms (范式). 1NF --> 2NF --> 3NF --> BCNF --> 4NF --> 5NF Introduction
DataBase System KL Design of the Bank Database brrandl-cifu 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) borrower loan branch=(loan number, branch name) borrower =(customer id, lo omn ubel depositor =(customer id, account number) frou muler cust banker=(customer id, employee id, type works for =(worker employee id, manager employee id) linGer works fo payment=(loan number, payment number, payment date, payment amount savings account =(account number, interest rate checking account=(account number, interest-ra overdraft_amound) overdraft amount) Haichang Gao, Software School, Xidian University
DataBase System Haichang Gao , Software School , Xidian University 5 Design of the Bank Database 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)
DataBase System es Design of the Bank Database G Suppose we combine borrow and loan to get H Schema: bor loan=(customer id, loan number, amount) A nstance. LIcun umber amount 23-652 L-Ioo L-100 1000D L-100 23-521 L-100 borrower 23-652 L-10 100o 15-202 L-I0D LODDO 23-521 L-1u0 tor lorn H Result is possible repetition of information CuStomer borrower loan H For borrower is M: N relationship Haichang Gao, Software School, Xidian University 6
DataBase System Haichang Gao , Software School , Xidian University 6 Design of the Bank Database Suppose we combine borrow and loan to get Schema: bor_loan = (customer_id, loan_number, amount ) Instance: Result is possible repetition of information For borrower is M:N relationship
DataBase System Design of the Bank Database Consider combining loan branch and loan E Schema: loan amt br=(loan number, amount, branch name) A nstance. Mn amber L100 1000 L-100 Springfield Iox lorn finch fon ranlu'r lomi branc L-100 10000 Sp pringfield oNt_mtbr loan -number A No repetition H For loan branch is I:N relationship borrower Haichang Gao, Software School, Xidian University
DataBase System Haichang Gao , Software School , Xidian University 7 Design of the Bank Database Consider combining loan_branch and loan Schema: loan_amt_br = (loan_number, amount, branch_name) Instance: No repetition For loan_branch is 1:N relationship