|
Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd in 1971.〔Codd, E.F. "Further Normalization of the Data Base Relational Model." (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems," New York City, May 24th-25th, 1971.) IBM Research Report RJ909 (August 31st, 1971). Republished in Randall J. Rustin (ed.), ''Data Base Systems: Courant Computer Science Symposia Series 6''. Prentice-Hall, 1972.〕 A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table. Put simply, a table is in 2NF if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key. ==Example== Consider a table describing employees' skills (underlining denotes the key of this table): Neither nor is a candidate key for the table. This is because a given Employee might need to appear more than once (the employee might have multiple Skills), and a given Skill might need to appear more than once (it might be possessed by multiple Employees). Only the composite key qualifies as a candidate key for the table. The remaining attribute, Current Work Location, is dependent on only part of the candidate key, namely Employee. Therefore, the table is not in 2NF. Note the redundancy in the way Current Work Locations are represented: we are told three times that Jones works at 114 Main Street, and twice that Brown works at 73 Industrial Way. This redundancy makes the table vulnerable to update anomalies: it is, for example, possible to update Jones' work location on his "''Shorthand''" and "''Typing''" records and not update his "''Whittling''" record. The resulting data would imply contradictory answers to the question "What is Jones' current work location?", unless it is meant that Jones exercises different skills at different locations. A 2NF alternative to this design would represent the same information in two tables: an "Employees" table with candidate key , and an "Employees' Skills" table with candidate key : |} Neither of these tables can suffer from update anomalies. Not all 2NF tables are free from update anomalies, however. An example of a 2NF table which suffers from update anomalies is: Even though Winner and Winner Date of Birth are determined by the whole key and not part of it, particular Winner / Winner Date of Birth combinations are shown redundantly on multiple records. This leads to an update anomaly: if updates are not carried out consistently, a particular winner could be shown as having two different dates of birth. The underlying problem is the transitive dependency to which the Winner Date of Birth attribute is subject. Winner Date of Birth actually depends on Winner, which in turn depends on the key Tournament / Year. This problem is addressed by third normal form (3NF). 抄文引用元・出典: フリー百科事典『 ウィキペディア（Wikipedia）』 ■ウィキペディアで「Second normal form」の詳細全文を読む スポンサード リンク
|