Second normal form

Second normal form (2NF), in database normalization, is a normal form. 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.

History

The second normal form was originally defined by E. F. Codd in 1971.

Decomposition of 1NF 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/candidate key: "Manufacturer" and "Model".
• {Manufacturer country} is functionally dependent (predictable) on {Manufacturer}.
• {Manufacturer} is a proper subset of the {Manufacturer, Model} candidate key.
• {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.)

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

It may now be desirable to add a numeric identifier to each row in the "Electric toothbrush models" table to ensure it remains in 1NF, or, the table may be left as is, allowing each unique combination of values in each row to be usable as a primary key, thus allowing the columns {Manufacturer, Model} in this table to be used as the candidate key of the table.

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. Archived from the original on 2012-12-09. Retrieved 2006-08-16.
• Date, C. J. (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. 26 (2): 120–125. doi:10.1145/358024.358054.

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