Second normal form

From Wikipedia, the free encyclopedia
Jump to navigation Jump to search

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

A relation is in the second normal form if it fulfills the following two requirements:

  1. It is in first normal form.
  2. It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.

Put simply, a relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key. Note that it does not put any restriction on the non-prime to non-prime attribute dependency. That is addressed in third normal form.

2NF and candidate keys[edit]

A functional dependency on part of any candidate key is a violation of 2NF. In addition to the primary key, the relation 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.

The following relation does not satisfy 2NF because:

  • {Manufacturer country} is functionally dependent on {Manufacturer}
  • {Manufacturer country} is not part of a candidate key, so it is a non-prime attribute
  • {Manufacturer} is a subset of {Manufacturer, Model} candidate key
  • Therefore {Manufacturer country} is a non-prime attribute functionally dependent on a part of a candidate key, and is in violation of 2NF
Electric toothbrush models
Manufacturer Model Model full name Manufacturer country
Forte X-Prime Forte X-Prime Italy
Forte Ultraclean Forte Ultraclean Italy
Dent-o-Fresh EZbrush Dent-o-Fresh EZbrush USA
Brushmaster SuperBrush Brushmaster SuperBrush USA
Kobayashi ST-60 Kobayashi ST-60 Japan
Hoch Toothmaster Hoch Toothmaster Germany
Hoch X-Prime Hoch X-Prime Germany

Even if the designer has specified the primary key as {Model full name}, the relation is not in 2NF because of the other candidate keys. {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 relations:

Electric toothbrush manufacturers
Manufacturer Manufacturer country
Forte Italy
Dent-o-Fresh USA
Brushmaster USA
Kobayashi Japan
Hoch Germany
Electric toothbrush models
Manufacturer Model Model full name
Forte X-Prime Forte X-Prime
Forte Ultraclean Forte Ultraclean
Dent-o-Fresh EZbrush Dent-o-Fresh EZbrush
Brushmaster SuperBrush Brushmaster SuperBrush
Kobayashi ST-60 Kobayashi ST-60
Hoch Toothmaster Hoch Toothmaster
Hoch X-Prime Hoch X-Prime

See also[edit]

References[edit]

  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 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), Data Base Systems: Courant Computer Science Symposia Series 6. Prentice-Hall, 1972.

Further reading[edit]

  • Litt's Tips: Normalization
  • Date, C. J., Lorentzos, N., Darwen, H. (2002). Temporal Data & the Relational Model[permanent dead link] (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9.
  • C. J. Date (2004). Introduction to Database Systems (8th ed.). Boston: Addison-Wesley. ISBN 978-0-321-19784-9.
  • Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, vol. 26, pp. 120–125.

External links[edit]