Second normal form


Second normal form is a normal form used in database normalization. 2NF was originally defined by E. F. Codd in 1971.
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

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:
ManufacturerModelModel full nameManufacturer country
ForteX-PrimeForte X-PrimeItaly
ForteUltracleanForte UltracleanItaly
Dent-o-FreshEZbrushDent-o-Fresh EZbrushUSA
BrushmasterSuperBrushBrushmaster SuperBrushUSA
KobayashiST-60Kobayashi ST-60Japan
HochToothmasterHoch ToothmasterGermany
Hoch X-PrimeHoch X-PrimeGermany

Even if the designer has specified the primary key as, the relation is not in 2NF because of the other candidate keys. 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:
ManufacturerManufacturer country
ForteItaly
Dent-o-FreshUSA
BrushmasterUSA
KobayashiJapan
HochGermany

ManufacturerModelModel full name
ForteX-PrimeForte X-Prime
ForteUltracleanForte Ultraclean
Dent-o-FreshEZbrushDent-o-Fresh EZbrush
BrushmasterSuperBrushBrushmaster SuperBrush
KobayashiST-60Kobayashi ST-60
HochToothmasterHoch Toothmaster
Hoch X-PrimeHoch X-Prime