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 e Delete Anomaly H a table T is subject to a delete anomaly when deleting some row of he table to reflect the disappearance of some instance of an entity or relationship can cause us to lose information about some instance of a different entity or relationship that we do not wish to forget Haichang Gao, Software School, Xidian University 13
DataBase System Haichang Gao , Software School , Xidian University 13 Delete Anomaly A table T is subject to a delete anomaly when deleting some row of the table to reflect the disappearance of some instance of an entity or relationship can cause us to lose information about some instance of a different entity or relationship that we do not wish to forget. Anomalies of a Bad Database Design
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 Jones 26 PC-admin 06-30-98 10 09112 Jones 89 WOrd-Droc 01-15-00 12 12231 Smith 26 PC-admin 0415-99 5 12231 Smith 39 bookkeeping 07-30-97 13597Brown 27 statistIcs 09-15-99 6 14131Blake 26 PC-admin 05-30-98 14131 Blake 89 word-proc 0930-99 10 e Insert Anomaly a We cannot represent information about some entity or instance without including information about some other instance of an entity or relationship that does not exist Haichang Gao, Software School, Xidian University 14
DataBase System Haichang Gao , Software School , Xidian University 14 Insert Anomaly We cannot represent information about some entity or instance without including information about some other instance of an entity or relationship that does not exist. Anomalies of a Bad Database Design
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 Jones 26 PC-admin 06-30-98 10 09112 Jones 89 WOrd-Droc 01-15-00 12 12231 Smith 26 PC-admin 0415-99 5 12231 Smith 39 bookkeeping 07-30-97 13597Brown 27 statistIcs 09-15-99 6 14131Blake 26 PC-admin 05-30-98 14131 Blake 89 word-proc 0930-99 10 G Redundant data(数据冗余) H An entity instance or relationship instance represented in a table t may account for several rows of t Haichang Gao, Software School, Xidian University 15
DataBase System Haichang Gao , Software School , Xidian University 15 Redundant Data （数据冗余） An entity instance or relationship instance represented in a table T may account for several rows of T. Anomalies of a Bad Database Design
ANomalies of a Bad Database Design g Normalize the relation mp_id emp_name emp_phone emps skills dept name emp id kill id dept phone decompose emp_phone skill name dept_mgrname dept_name skill date skill id dept_ph ki11_1v1 skill name dept_mgrname skill date skill lvi Haichang Gao, Software School, Xidian University 16
DataBase System Haichang Gao , Software School , Xidian University 16 Normalize the relation… Anomalies of a Bad Database Design decompose
DataBase System Functional Dependencies Functional Dependencies(函数依赖) The functional dependency a→B holds on R if and only if for any legal relations r(R) whenever any two tuples t, and t2 of r agree on the attributes a(在α上取值相同), they also agree on the attributes B. That is t1cl=2o→t16l=t26l 另一定义:一但α的值确定了,就一定能唯一确定β的值 即使不知道的确切值,但可以明确6只能取一个值) Haichang Gao, Software School, Xidian University 17
DataBase System Haichang Gao , Software School , Xidian University 17 Functional Dependencies (函数依赖) The functional dependency → holds on R if and only if for any legal relations r(R), whenever any two tuples t1 and t2 of r agree on the attributes (在上取值相同), they also agree on the attributes . That is, t1 [] = t2 [] t1 [ ] = t2 [ ] 另一定义：一但的值确定了，就一定能唯一确定的值 (即使不知道的确切值，但可以明确只能取一个值) Functional Dependencies