Second normal form

Short description: Term in database normalization

Second normal form (2NF) 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 (i.e. it lacks partial dependencies). 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 (or table) is in 2NF if:

1. it is in 1NF and has a single attribute unique identifier (UID)(in which case every non key attribute is dependent on the entire UID), or
2. it is in 1NF and has a multi-attribute unique identifier, and every regular attribute (not part of the UID) is dependent on all attributes in the multi-attribute UID, not just one attribute (or part) of the UID.

If any regular (non-prime) attributes are predictable (dependent) on another (non-prime) attribute, that is addressed in third normal form.

2NF and candidate keys

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.

How to decompose into 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.

Example

The following relation does not satisfy 2NF because:

• There is a multi-attribute unique identifier: Manufacturer and Model.
• {Manufacturer country} is functionally dependent (predictable) on {Manufacturer}.
• {Manufacturer country} is not part of a candidate key, so it is a non-prime attribute. (It is assumed that it is possible for two manufacturers in the same country to make a toothbrush with the same model name, so {Manufacturer country, Model} is not a candidate key even though in the current table the pair uniquely identify rows.)
• {Manufacturer} is a proper subset of the {Manufacturer, Model} candidate key.

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.

Electric toothbrush models

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:

1. Remove the functionally dependent attributes in the partial dependencies of the first normal form relation. In this example, {Manufacturer country} is the functionally dependent attribute which will be removed.
2. Place those partial dependency dependent attributes (i.e. {Manufacturer country}) in a relation where their corresponding determinant attributes are a candidate key (i.e. {Manufacturer}).

As seen below, {Manufacturer country} is removed from the original table:

Electric toothbrush models

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:

Electric toothbrush manufacturers

Manufacturer	Manufacturer country
Forte	Italy
Dent-o-Fresh	USA
Brushmaster	USA
Kobayashi	Japan
Hoch	Germany

See also

• Attribute-value system

Further reading

• Litt's Tips: Normalization
• Date, C. J.; Lorentzos, N.; Darwen, H. (2002). Temporal Data & the Relational Model (1st ed.). Morgan Kaufmann. ISBN 1-55860-855-9. http://www.elsevier.com/wps/product/cws_home/680662. Retrieved 2006-08-16. 
• Date, C. J. (2004). Introduction to Database Systems (8th ed.). Boston: Addison-Wesley. ISBN 978-0-321-19784-9. https://archive.org/details/introductiontoda0000date. 
• Kent, W. (1983). "A Simple Guide to Five Normal Forms in Relational Database Theory". Communications of the ACM 26 (2): 120–125. doi:10.1145/358024.358054. http://www.bkent.net/Doc/simple5.htm. 

External links

• Database Normalization Basics by Mike Chapple (About.com)
• An Introduction to Database Normalization by Mike Hillyer.
• A tutorial on the first 3 normal forms by Fred Coulson
• Description of the database normalization basics by Microsoft

de:Normalisierung (Datenbank)#Zweite Normalform (2NF)