Second normal form

Second normal form (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. CoddCodd, 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.] in 1971. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a 1NF table is in 2NF if and only if, given any candidate key and any attribute that is not a constituent of a candidate key, the non-key attribute depends upon the whole of the candidate key rather than just a part of it.

In slightly more formal terms: a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key. (A non-prime attribute is one that does not belong to any candidate key.)

Note that when a 1NF table has no composite candidate keys (candidate keys consisting of more than one attribute), the table is automatically in 2NF.

Example

Consider a table describing employees' skills:

Update anomalies cannot occur in these tables, which are both in 2NF.

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 {Tournament, Year} and not part of it, particular Winner / Winner Date of Birth combinations are shown redundantly on multiple records. This problem is addressed by third normal form (3NF).

2NF and candidate keys

A table for which there are no partial functional dependencies on the primary key is typically, but not always, in 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:

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.

References

ee also

*Attribute-value system
*First normal form
*Third normal form

Further reading

* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* [http://www.datamodel.org/NormalizationRules.html Rules Of Data Normalization]
* Date, C. J., & Lorentzos, N., & Darwen, H. (2002). " [http://www.elsevier.com/wps/product/cws_home/680662 Temporal Data & the Relational Model] " (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9.
* Date, C. J. (1999), " [http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html An Introduction to Database Systems] " (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
* Kent, W. (1983) " [http://www.bkent.net/Doc/simple5.htm A Simple Guide to Five Normal Forms in Relational Database Theory] ", Communications of the ACM, vol. 26, pp. 120-125
* Date, C.J., & Darwen, H., & Pascal, F. " [http://www.dbdebunk.com Database Debunkings] "

External links

* [http://databases.about.com/od/specificproducts/a/normalization.htm Database Normalization Basics] by Mike Chapple (About.com)
* [http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html An Introduction to Database Normalization] by Mike Hillyer.
* [http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html Normalization] by ITS, University of Texas.
* [http://phlonx.com/resources/nf3/ A tutorial on the first 3 normal forms] by Fred Coulson
* [http://www.marcrettig.com/poster/ Free PDF poster available] by Marc Rettig
* [http://support.microsoft.com/kb/283878 Description of the database normalization basics] by Microsoft