西安电子科技大学：《数据库系统 DataBase System》课程教学资源（PPT课件讲稿）normalization

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