View source for Second normal form

{{Short description|Level of database normalization}}
{{More footnotes needed|date=June 2024}}

'''Second normal form''' ('''2NF''') is a level of [[database normalization]] defined by English computer scientist [[Edgar F. Codd]]. A relation (or a [[Table (database)|''table'']], in [[SQL]]) is in 2NF if it is in [[first normal form]] (1NF) and contains no partial dependencies. A partial dependency occurs when a [[non-prime attribute]] (that is, one not part of any [[candidate key]]) is [[Functional dependency|functionally dependent]] on only a [[proper subset]] of the attributes making up a [[candidate key]]. To be in 2NF, a relation must have every non-prime attribute depend on the whole set of attributes of every candidate key.

For instance, a relation with the composite key {Country, District} would violate 2NF if any attribute was added whose values' meanings didn't depend on both the Country and the District to which they applied. A CountryLeader attribute would vary between and provide information specific to each Country but not specific to each District, and would therefore depend on only half of the composite key. This would have several drawbacks, including that any leader would be redundantly duplicated for each District in their Country.

The purpose of normalization to 2NF is to reduce such redundancy and to make a database's structure generally more clear and flexible by organizing it by functional dependencies. 2NF and [[third normal form]] (3NF) were both defined in Codd's paper "Further Normalization of the Data Base Relational Model" in 1971,<ref name="Codd">Codd, E. F. "Further Normalization of the Data Base Relational Model". (Presented at Courant Computer Science Symposia Series 6, "Data Base Systems", New York City, May 24–25, 1971.) IBM Research Report RJ909 (August 31, 1971). Republished in Randall J. Rustin (ed.), ''Data Base Systems: Courant Computer Science Symposia Series 6''. Prentice-Hall, 1972.</ref> a year after Codd defined 1NF in "A Relational Model of Data for Large Shared Data Banks" in 1970.{{Citation needed|date=July 2025}} All normal forms make up part of Codd's [[relational model]] of database design.

== Example ==

=== Design which violates 2NF ===
The following relation in [[first normal form]] contains a [[composite key]], {Manufacturer, Model}. The [[non-prime attribute]] ManufacturerCountry is [[Functional dependency|functionally dependent]] on the attribute Manufacturer (as each Manufacturer will be associated with a separate ManufacturerCountry), but not on the attribute Model. Thus, ManufacturerCountry depends only on a [[proper subset]] of the key, {Manufacturer}, making it only partially dependent on the key and violating 2NF.

{| class="wikitable"
|+ Toothbrush
! <u>Manufacturer</u> !! <u>Model</u> !! ManufacturerCountry
|-
| 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
|}

=== Design which complies with 2NF ===
To bring a relation already in 1NF in line with 2NF, any attributes which depend on only part of a composite key must be extracted to separate relations where the attributes they depend on compose the entirety of a [[candidate key]]. As seen below, the attribute ManufacturerCountry can be removed from the original Toothbrush relation and put into a new relation where the attribute Manufacturer makes up the full [[primary key]]. The new Country attribute thereby depends on the full key rather than only a part of it, and so the previous ''partial'' dependency has become a ''full'' dependency, putting both relations in 2NF.

{{Col-float}}
{{Col-float-break|style=margin-right: 20px;}}
{| class="wikitable"
|+ Toothbrush
! <u>Manufacturer</u> !! <u>Model</u>
|-
| Forte || X-Prime
|-
| Forte || Ultraclean
|-
| Dent-o-Fresh || EZbrush
|-
| Brushmaster || SuperBrush
|-
| Kobayashi || ST-60
|-
| Hoch || Toothmaster
|-
| Hoch || X-Prime
|}
{{Col-float-break}}
{| class="wikitable"
|+ Manufacturer
! <u>Manufacturer</u> !! Country
|-
| Forte || Italy
|-
| Dent-o-Fresh || USA
|-
| Brushmaster || USA
|-
| Kobayashi || Japan
|-
| Hoch || Germany
|}
{{Col-float-end}}

==See also==
* [[Attribute-value system]]
* [[First normal form]] (1NF)
* [[Third normal form]] (3NF)
* [[Boyce–Codd normal form]] (BCNF or 3.5NF)
* [[Fourth normal form]] (4NF)
* [[Fifth normal form]] (5NF)
* [[Sixth normal form]] (6NF)

==References==
{{Reflist}}

==Further reading==
{{Refbegin}}
* Codd, E. F. (1970). A Relational Model of Data for Large Shared Data Banks. IBM Research Laboratory, San Jose, California.
* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* {{cite book |last1=Date |first1=C. J. |authorlink1=Christopher J. Date |last2=Lorentzos |first2=N. |last3=Darwen |first3=H. |year=2002 |title=Temporal Data & the Relational Model |edition=1st |publisher=Morgan Kaufmann |isbn=1-55860-855-9 |url=http://www.elsevier.com/wps/product/cws_home/680662 |url-status=dead |access-date=2006-08-16 |archive-date=2012-12-09 |archive-url=https://archive.today/20121209052842/http://www.elsevier.com/wps/product/cws_home/680662}}
* {{cite book |last=Date |first=C. J. |authorlink=Christopher J. Date |year=2004 |title=Introduction to Database Systems |edition=8th |publisher=Addison-Wesley |location=Boston |isbn=978-0-321-19784-9 |url-access=registration |url=https://archive.org/details/introductiontoda0000date}}
* {{cite journal |last=Kent |first=W. |year=1983 |title=A Simple Guide to Five Normal Forms in Relational Database Theory |journal=Communications of the ACM |volume=26 |issue=2 |pages=120–125 |doi=10.1145/358024.358054 |url=http://www.bkent.net/Doc/simple5.htm |doi-access=free}}
{{Refend}}

==External links==
* [http://mikehillyer.com/articles/an-introduction-to-database-normalization/ An Introduction to Database Normalization] by Mike Hillyer.
* [http://phlonx.com/resources/nf3/ A tutorial on the first 3 normal forms] by Fred Coulson
* [http://support.microsoft.com/kb/283878 Description of the database normalization basics] by Microsoft

{{Database normalization}}

[[Category:Database normalization|2NF]]

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