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
DataBase System Design of the Bank Database Example( decompose,分解): emiplouce id employee_ nanze telepone number start- date 123456789Kim 882-000 19844329 987654321kim 869.9999 1981-01-16 maplovee id llemiplowee-naa mplowermame elephone number start date 123-456789Kim 882-00O 1s4B-29 987654321Kim 869-9999 191-01-16 emplovee_ii employe name teleplone number start _date 123456789Kim 882.0000 198403-29 12345-6789Kim 869999 1981-01-1 5-432K 98765-4321Kim 869-55 1981-01-16 H we cannot reconstruct the original employee relation Haichang Gao, Software School, Xidian University 8
DataBase System Haichang Gao , Software School , Xidian University 8 Design of the Bank Database Example (decompose, 分解) : we cannot reconstruct the original employee relation
DataBase System Design of the Bank Database e com bining loan branch and loan into H Schema: loan amt br=(loan number, amount, branch name) Isa“good” relation schema ce combine borrow and loan to get H Schema: bor loan=(customer id, loan number, amount) H Is nOT a"good ' relation schema G Decide whether a particular relation R is in"good""or NOT? G Suppose we had started with bor loan. How would we know to split up decompose, b #)it into borrower and loan? c Normalization theory is the tools used to solve those questions. Haichang Gao, Software School, Xidian University
DataBase System Haichang Gao , Software School , Xidian University 9 Design of the Bank Database combining loan_branch and loan into Schema: loan_amt_br = (loan_number, amount, branch_name) Is a “good” relation schema combine borrow and loan to get Schema: bor_loan = (customer_id, loan_number, amount ) Is NOT a “good” relation schema Decide whether a particular relation R is in “good” or NOT? Suppose we had started with bor_loan. How would we know to split up (decompose, 分解) it into borrower and loan? Normalization theory is the tools used to solve those questions
DataBase System CA Running Example G Employee Information: emp_id emp name emp_phone dept name dept_phone dept_mgrname skill id skill_name From one up to a large skill date number of skills useful to the company skill lvI Haichang Gao, Software School, Xidian University 10
DataBase System Haichang Gao , Software School , Xidian University 10 Employee Information: A Running Example From one up to a large number of skills useful to the company
DataBase System CA Running Example G Employee Information: emp_info emp_ id emp_name∴ skill_id skill_name skill. date skill_1v1 09112 Jones 44 librarian 03-15-99 09112 Jones 26 PC-admin 06-30-98 10 09112 Jones 89 word-proc 01-1500 12 12231 Smith 26 PC-admin 04-1599 12231 Smith 39 bookkeeping 07-30-97 13597 Brown 7 statistics 09-1599 5769 14131 Blake 26 PC-admin 05-30- 14131 Blake word-proc09-30-99 10 , Haichang Gao, Software School, Xidian University 11
DataBase System Haichang Gao , Software School , Xidian University 11 Employee Information: A Running Example
DataBase System CE Anomalies of a Bad Database design emp info emp_id emp_name skill_idski11_name skill_ date skill_lvl 09112 Jones 44 librarian 03-15-99 12 09112 26 PC-admin 06-30-98 10 09112 ones 89 WOrd-Droc 01-15-00 12 12231 Smith 26 PC-admin 0415-99 12231 Smith 39 bookkeeping 07-30-97 576 13597Brown 27 statistIcs 09-15-99 14131Blake 26 PC-admin 05-30-98 14131 Blake 89 word-proc 0930-99 10 Update Anomaly(修改异常) aa table T is subject to an update anomaly when changing a single attribute value for an entity instance or relationship instance represented in the table may require that several rows of t be updated Haichang Gao, Software School, Xidian University 12
DataBase System Haichang Gao , Software School , Xidian University 12 Update Anomaly (修改异常) A table T is subject to an update anomaly when changing a single attribute value for an entity instance or relationship instance represented in the table may require that several rows of T be updated. Anomalies of a Bad Database Design