Second normal form is a normal form used in database normalization.
2NF was originally defined by E.F.
Codd in 1971.
A table that is in first normal form must meet additional criteria if it is to qualify
for second normal form.
Specifically: a table is in 2NF if and only if it is in 1NF and no non-prime attribute
is dependent on any proper subset of any candidate key of the table.
A non-prime attribute of a table is an attribute that is not a part of any candidate key of
the table.
Put simply, a table is in 2NF if and only if it is in 1NF and every non-prime attribute
of the table is dependent on the whole of a candidate key.
Example Consider a table describing employees' skills:
Neither {Employee} nor {Skill} is a candidate key for the table.
This is because a given Employee might need to appear more than once, and a given Skill
might need to appear more than once.
Only the composite key {Employee, Skill} qualifies as a candidate key for the table.
The remaining attribute, Current Work Location, is dependent on only part of the candidate
key, namely Employee.
Therefore the table is not in 2NF.
Note the redundancy in the way Current Work Locations are represented: we are told three
times that Jones works at 114 Main Street, and twice that Brown works at 73 Industrial
Way.
This redundancy makes the table vulnerable to update anomalies: it is, for example, possible
to update Jones' work location on his "Shorthand" and "Typing" records and not update his "Whittling"
record.
The resulting data would imply contradictory answers to the question "What is Jones' current
work location?"
A 2NF alternative to this design would represent the same information in two tables: an "Employees"
table with candidate key {Employee}, and an "Employees' Skills" table with candidate key
{Employee, Skill}: Neither of these tables can suffer from update
anomalies.
Not all 2NF tables are free from update anomalies, however.
An example of a 2NF table which suffers from update anomalies is:
Even though Winner and Winner Date of Birth are determined by the whole key {Tournament,
Year} and not part of it, particular Winner / Winner Date of Birth combinations are shown
redundantly on multiple records.
This leads to an update anomaly: if updates are not carried out consistently, a particular
winner could be shown as having two different dates of birth.
The underlying problem is the transitive dependency to which the Winner Date of Birth attribute
is subject.
Winner Date of Birth actually depends on Winner, which in turn depends on the key Tournament
/ Year.
This problem is addressed by third normal form.
2NF and candidate keys A table for which there are no partial functional
dependencies on the primary key is typically, but not always, in 2NF.
In addition to the primary key, the table 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.
Multiple candidate keys occur in the following table:
Even if the designer has specified the primary key as {Model Full Name}, the table is not
in 2NF.
{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 tables:
See also
Attribute-value system References
Further reading
External links Database Normalization Basics by Mike Chapple
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