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