View source for Third normal form

{{Short description|Level of database normalization}}

'''Third normal form''' ('''3NF''') is a level of [[database normalization]] defined by English computer scientist [[Edgar F. Codd]]. A relation (or ''[[Table (database)|table]]'', in [[SQL]]) is in third normal form if it is in [[second normal form]] and also lacks non-key dependencies, meaning that no [[Candidate key|non-prime attribute]] is [[Functional dependency|functionally dependent]] on (that is, contains a fact about) any other non-prime attribute. In other words, each non-prime attribute must depend solely and [[Transitive dependency#Database management systems|non-transitively]] on each candidate key.<ref>Codd, E. F. "Further Normalization of the Data Base Relational Model", p. 34.</ref> William Kent summarised 3NF with the dictum that "a non-key field must provide a fact about the key, the whole key, and nothing but the key".<ref name="Kent" />{{Citation needed|date=July 2025|reason=A source is needed to verify that Kent was the first to say this.}}

An example of a violation of 3NF would be a Patient relation with the attributes PatientID, DoctorID and DoctorName, in which DoctorName would depend first and foremost on DoctorID and only transitively on the key, PatientID (via DoctorID's dependency on PatientID). Such a design would cause a doctor's name to be redundantly duplicated across each of their patients. A database compliant with 3NF would store doctors' names in a separate Doctor relation which Patient could reference via a [[foreign key]].

3NF was defined, along with 2NF (which forbids dependencies on proper subsets of [[Composite key|composite keys]]), 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> which came after 1NF's definition in "A Relational Model of Data for Large Shared Data Banks" in 1970.{{Citation needed|date=July 2025}} 3NF was itself followed by the definition of [[Boyce–Codd normal form]] in 1974, which seeks to prevent anomalies possible in relations with several overlapping composite keys.

==Definition of third normal form==
Codd's definition states that a [[Relation (database)|relation]] R is in 3NF if and only if it is in [[second normal form]] (2NF) and every [[Candidate key|non-prime attribute]] of R is non-transitively dependent on each [[candidate key]]. A non-prime attribute of R is an attribute that does not belong to any candidate key of R.<ref name="Codd2">Codd, p. 43.</ref>

Codd defines a [[Transitive dependency#Database management systems|transitive dependency]] of an attribute set ''Z'' on an attribute set ''X'' as a [[functional dependency]] chain ''X'' → ''Y'' → ''Z'' that must be satisfied for some attribute set ''Y'', where it is not the case that ''Y'' → ''X'', and all three sets must be disjoint.<ref>Codd, p. 45–46.</ref>

A 3NF definition that is equivalent to Codd's, but expressed differently, was given by Carlo Zaniolo in 1982. This definition states that a table is in 3NF if and only if for each of its functional dependencies ''X'' → ''Y'', at least one of the following conditions holds:<ref name="Zaniolo">Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata". ''ACM Transactions on Database Systems'' 7(3), September 1982.</ref><ref>[[Abraham Silberschatz]], [[Henry F. Korth]], S. Sudarshan, ''[http://www.db-book.com/ Database System Concepts]'' (5th edition), p. 276–277.</ref>{{Request quotation|date=November 2010}}
* ''X'' contains ''Y'' (that is, ''Y'' is a subset of ''X'', meaning ''X'' → ''Y'' is a trivial functional dependency),
* ''X'' is a [[superkey]],
* every element of ''Y'' \ ''X'', the [[Complement (set theory)#Relative complement|set difference]] between Y and X, is a ''prime attribute'' (i.e., each attribute in ''Y'' \ ''X'' is contained in some candidate key).

To rephrase Zaniolo's definition more simply, the relation is in 3NF if and only if for every non-trivial functional dependency X → Y, X is a superkey or ''Y'' \ ''X'' consists of prime attributes. Zaniolo's definition gives a clear sense of the difference between 3NF and the more stringent [[Boyce–Codd normal form]] (BCNF). BCNF simply eliminates the third alternative ("Every element of ''Y'' \ ''X'', the set difference between ''Y'' and ''X'', is a prime attribute.").

The definition offered by Zaniolo can be shown to be equivalent to the Codd definition in the following way: let X → A be a nontrivial functional dependency (i.e., one where X does not contain A) and let A be a non-prime attribute. Also let Y be a candidate key of R. Then Y → X. Therefore, A is not transitively dependent on Y if and only if there is a functional dependency X → Y, i.e., if and only if X is a superkey of R.

== Example ==

=== Design which violates 3NF ===
The following relation, with the [[composite key]] {Name, Year}, fails to meet the requirements of 3NF. The non-prime attributes WinnerName and WinnerBirthdate are only ''transitively'' dependent on the composite key via their dependence on the non-prime attribute WinnerID. This creates redundancy and the potential for inconsistency in the case that a winner of multiple tournaments is accidentally given different dates of birth in different tuples.

{{Table alignment}}
{| class="wikitable col2right col3right"
|+ Tournament
! <u>Name</u> !! <u>Year</u> !! WinnerID !! WinnerName !! WinnerBirthdate
|-
| Indiana Invitational || 1998 || 1 || Al Fredrickson || 1975-07-21
|-
| Cleveland Open ||1999 || 2 || Bob Albertson || 1968-09-28
|-
| Des Moines Masters || 1999 || 1 || Al Fredrickson || 1975-07-21
|-
| Indiana Invitational || 1999 || 3 || Chip Masterson || 1977-03-14
|}

=== Design which complies with 3NF ===
To bring the relation into compliance with 3NF, WinnerID, WinnerName and WinnerBirthdate can be transferred to a separate table.

{{Col-float}}
{{Col-float-break|style=margin-right: 20px;}}
{{Table alignment}}
{| class="wikitable col2right col3right"
|+ Tournament
! <u>Name</u> !! <u>Year</u> !! WinnerID
|-
| Indiana Invitational || 1998 || 1
|-
| Cleveland Open || 1999 || 2
|-
| Des Moines Masters || 1999 || 1
|-
| Indiana Invitational || 1999 || 3
|}
{{Col-float-break}}
{{Table alignment}}
{| class="wikitable col1right"
|+ Winner
! <u>WinnerID</u> !! Name !! Birthdate
|-
| 1 || Al Fredrickson || 1975-07-21
|-
| 2 || Bob Albertson || 1968-09-28
|-
| 3 || Chip Masterson || 1977-03-14
|}
{{Col-float-end}}

Tournament's WinnerID attribute now acts as a [[foreign key]] referencing the primary key of Winner. Unlike before, it is not possible for a winner to be associated with multiple dates of birth.

=="Nothing but the key"==
A paraphrase of Codd's definition of 3NF parodying the traditional [[sworn testimony|oath to tell the truth]] in a court of law was given by William Kent: "a non-key field must provide a fact about the key, the whole key, and nothing but the key".<ref name="Kent">Kent, William. [http://www.bkent.net/Doc/simple5.htm "A Simple Guide to Five Normal Forms in Relational Database Theory"], ''Communications of the ACM'' 26 (2), Feb. 1983, pp. 120–125.</ref> Requiring that non-key attributes be dependent on "the whole key" ensures compliance with [[Second normal form|2NF]], and further requiring their dependency on "nothing but the key" ensures compliance with 3NF. A common variation supplements the paraphrase with the addendum "so help me [[Edgar F. Codd|Codd]]".<ref name="Diehr">The author of a 1989 book on database management credits one of his students with coming up with the "so help me Codd" addendum. Diehr, George. ''Database Management'' (Scott, Foresman, 1989), p. 331.</ref>

While the phrase is a useful mnemonic, the mention of only a single key makes fulfilling it [[Necessity and sufficiency|necessary but not sufficient]] to satisfy 2NF and 3NF, both of which are concerned with ''all'' candidate keys of a relation and not just any one.{{Citation needed|date=July 2025}}

[[Christopher J. Date]] notes that, adapted to refer to ''all'' fields rather than just non-key fields, the summary can also encompass the slightly stronger [[Boyce–Codd normal form]], in which prime attributes must not be functionally dependent at all.<ref name="DateIntro">Date, C. J. ''An Introduction to Database Systems'' (7th ed.) (Addison Wesley, 2000), p. 379.</ref> Prime attributes are considered to provide a fact about the key in the sense of providing part or all of the key itself. (This rule applies only to functionally dependent attributes, as applying it to all attributes would implicitly prohibit composite keys, since each part of any such key would violate the "whole key" clause.)

== Computation ==
{{Confusing|section|date=July 2025}}
A relation can always be decomposed in third normal form, that is, the relation R is rewritten to [[relational projection|projections]] R<sub>1</sub>, ..., R<sub>n</sub> whose [[natural join|join]] is equal to the original relation. Further, this decomposition does not lose any [[functional dependencies|functional dependency]], in the sense that every functional dependency on R can be derived from the functional dependencies that hold on the projections R<sub>1</sub>, ..., R<sub>n</sub>. What is more, such a decomposition can be computed in [[polynomial time]].<ref>[[Serge Abiteboul]], Richard B. Hull, [[Victor Vianu]]: Foundations of Databases. Addison-Wesley, 1995. http://webdam.inria.fr/Alice/ {{ISBN|0201537710}}. Theorem 11.2.14.</ref>

To decompose a relation into 3NF from 2NF, break the table into the [[canonical cover]] functional dependencies, then create a relation for every candidate key of the original relation which was not already a subset of a relation in the decomposition.<ref>{{Cite web |last=Hammo |first=Bassam |title=Decomposition, 3NF, BCNF |url=https://faculty.ksu.edu.sa/sites/default/files/E-%20Decomposition.pdf |archive-url=https://web.archive.org/web/20230315013047/https://faculty.ksu.edu.sa/sites/default/files/E-%20Decomposition.pdf |archive-date=2023-03-15 |url-status=live }}</ref>

==Considerations for use in reporting environments==
{{Confusing|section|date=July 2025}}
While 3NF was ideal for machine processing, the segmented nature of the data model can be difficult to intuitively consume by a human user. [[Analytics]] via query, reporting, and dashboards were often facilitated by a different type of data model that provided pre-calculated analysis such as trend lines, period-to-date calculations (month-to-date, quarter-to-date, year-to-date), cumulative calculations, basic statistics (average, standard deviation, moving averages) and previous period comparisons (year ago, month ago, week ago) e.g. [[dimensional modeling]] and beyond dimensional modeling, flattening of stars via [[Hadoop]] and [[data science]].<ref>{{cite news |title=Comparisons between Data Warehouse modelling techniques – Roelant Vos |newspaper=Roelant Vos |date=12 February 2013 |url=http://roelantvos.com/blog/?p=740 |access-date=5 March 2018}}</ref><ref>{{cite web |date=23 September 2014 |title=Hadoop Data Modeling Lessons {{!}} EMC |website=InFocus Blog {{!}} Dell EMC Services |url=https://infocus.dellemc.com/william_schmarzo/hadoop-data-modeling-lessons-vin-diesel/|access-date=5 March 2018}}</ref> Hadley Wickham's "tidy data" framework is 3NF, with "the constraints framed in statistical language".<ref>{{Cite journal |last=Wickham |first=Hadley |date=2014-09-12 |title=Tidy Data |journal=Journal of Statistical Software |language=en |volume=59 |issue=10 |pages=1–23 |doi=10.18637/jss.v059.i10 |issn=1548-7660|doi-access=free }}</ref>

==See also==
*[[Attribute-value system]]
*[[First normal form]] (1NF)
*[[Second normal form]] (2NF)
*[[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}}
*Date, C. J. (1999), * {{cite book |last=Date |first=C. J. |year=1999 |title=An Introduction to Database Systems |edition=8th |publisher=Addison-Wesley Longman |url=http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html |isbn=0-321-19784-4 |archive-url=https://web.archive.org/web/20050404010227/http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html |archive-date=4 April 2005 }}
* {{cite journal |last=Kent |first=William |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–126 |doi=10.1145/358024.358054 |url=http://www.bkent.net/Doc/simple5.htm }}

{{Refend}}

==External links==
*[http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
*[http://databases.about.com/od/specificproducts/a/normalization.htm Database Normalization Basics] by Mike Chapple (About.com)
*[http://mikehillyer.com/articles/an-introduction-to-database-normalization/ An Introduction to Database Normalization] by Mike Hillyer.
*[https://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
*[http://exploredatabase.com/2014/02/third-normal-form-3nf-with-example.html Third Normal Form with Simple Examples] by exploreDatabase

{{Database normalization}}

[[Category:Database normalization|3NF]]

[[de:Normalisierung (Datenbank)#Dritte Normalform (3NF)]]