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:
Put simply, a relation (or table) is in 2NF if:
If any regular (non-prime) attributes are predictable (dependent) on another (non-prime) attribute, that is addressed in third normal form.
A functional dependency on a proper subset of any candidate key (UID) 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 (regular/non-UID attributes) have part-key dependencies (they're on any of these candidate keys (UIDs). Or put simply, if any regular attributes (non-UID attributes) are predictable from one attribute of the UID (or part of the UID), then it is not in 2NF.
To make a 1NF relation a 2NF relation, remove the functionally dependent attributes in the partial dependencies of the first normal form relation, and place those partial dependency dependent attributes in a relation where their corresponding determinant attributes are an entire candidate key.
The following relation does not satisfy 2NF because:
In other words, since {Manufacturer country} is a non-prime attribute functionally dependent on a proper subset of a candidate key, the relation is in violation of 2NF.
Manufacturer | Model | Manufacturer country |
---|---|---|
Forte | X-Prime | Italy |
Forte | Ultraclean | Italy |
Dent-o-Fresh | EZbrush | USA |
Brushmaster | SuperBrush | USA |
Kobayashi | ST-60 | Japan |
Hoch | Toothmaster | Germany |
Hoch | X-Prime | Germany |
To make the design conform to 2NF, it is necessary to have two relations. To create these relations:
As seen below, {Manufacturer country} is removed from the original table:
Manufacturer | Model |
---|---|
Forte | X-Prime |
Forte | Ultraclean |
Dent-o-Fresh | EZbrush |
Brushmaster | SuperBrush |
Kobayashi | ST-60 |
Hoch | Toothmaster |
Hoch | X-Prime |
As seen below, the partial dependency is put into a new relation where the dependency can exist without being a partial dependency:
Manufacturer | Manufacturer country |
---|---|
Forte | Italy |
Dent-o-Fresh | USA |
Brushmaster | USA |
Kobayashi | Japan |
Hoch | Germany |