Second normal form

Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. Codd in 1971.[1]

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.

2NF and candidate keys

A functional dependency on part of any candidate key is a violation of 2NF. In addition to the primary key, the table may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.

Multiple candidate keys occur in the following table:

Electric Toothbrush Models
Manufacturer Model Model Full Name Manufacturer Country
ForteX-PrimeForte X-PrimeItaly
ForteUltracleanForte UltracleanItaly
Dent-o-FreshEZbrushDent-o-Fresh EZbrushUSA
KobayashiST-60Kobayashi ST-60Japan
HochToothmasterHoch ToothmasterGermany
Hoch X-PrimeHoch X-PrimeGermany

Even if the designer has specified the primary key as {Model Full Name}, the table is not in 2NF. {Manufacturer, Model} is also a candidate key, and Manufacturer Country is dependent on a proper subset of it: Manufacturer. To make the design conform to 2NF, it is necessary to have two tables:

Electric Toothbrush Manufacturers
Manufacturer Manufacturer Country
ForteItaly
Dent-o-FreshUSA
KobayashiJapan
HochGermany
Electric Toothbrush Models
Manufacturer Model Model Full Name
ForteX-PrimeForte X-Prime
ForteUltracleanForte Ultraclean
Dent-o-FreshEZbrushDent-o-Fresh EZbrush
KobayashiST-60Kobayashi ST-60
HochToothmasterHoch Toothmaster
Hoch X-PrimeHoch X-Prime

See also

References

  1. 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.

Further reading

External links

This article is issued from Wikipedia - version of the Wednesday, May 04, 2016. The text is available under the Creative Commons Attribution/Share Alike but additional terms may apply for the media files.