Editing Database normalization (section)

== Normal forms ==
Codd introduced the concept of normalization and what is now known as the [[first normal form]] (1NF) in 1970.<ref name="Codd1970">{{cite journal |first=E. F. |last=Codd |author-link=Edgar F. Codd |title=A Relational Model of Data for Large Shared Data Banks |journal=[[Communications of the ACM]] |volume=13 |issue=6 |date=June 1970 |pages=377–387 |doi=10.1145/362384.362685 |s2cid=207549016 |doi-access=free }}</ref> Codd went on to define the [[second normal form]] (2NF) and [[third normal form]] (3NF) in 1971,<ref name="Codd, E.F 1971">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> and Codd and [[Raymond F. Boyce]] defined the [[Boyce–Codd normal form]] (BCNF) in 1974.<ref name="CoddBCNF">Codd, E. F. "Recent Investigations into Relational Data Base Systems". IBM Research Report RJ1385 (April 23, 1974). Republished in ''Proc. 1974 Congress'' (Stockholm, Sweden, 1974), N.Y.: North-Holland (1974).</ref>

[[Ronald Fagin]] introduced the [[fourth normal form]] (4NF) in 1977 and the [[fifth normal form]] (5NF) in 1979. [[Christopher J. Date]] introduced the [[sixth normal form]] (6NF) in 2003.

Informally, a relational database relation is often described as "normalized" if it meets third normal form.<ref name="DateIntroDBSys">{{cite book |first=C. J. |last=Date |title=An Introduction to Database Systems |publisher=Addison-Wesley |year=1999 |page=290}}</ref> Most 3NF relations are free of insertion, updation, and deletion anomalies.

The normal forms (from least normalized to most normalized) are:

{{columns-list|colwidth=20em|
* UNF: [[Unnormalized form]]
* 1NF: [[First normal form]]
* 2NF: [[Second normal form]]
* 3NF: [[Third normal form]]
* EKNF: [[Elementary key normal form]]
* BCNF: [[Boyce–Codd normal form]]
* 4NF: [[Fourth normal form]]
* ETNF: [[Essential tuple normal form]]
* 5NF: [[Fifth normal form]]
* DKNF: [[Domain-key normal form]]
* 6NF: [[Sixth normal form]]
}}

{| class="wikitable"
! Constraint<br><span style="font-weight:normal">{{small|(informal description in parentheses)}}</span>
! scope="col" | [[Unnormalized form|UNF]]<br><span style="font-weight:normal">{{small|(1970)}}</span>
! scope="col" | [[First normal form|1NF]] <br><span style="font-weight:normal">{{small|(1970)}}</span>
! scope="col" | [[Second normal form|2NF]] <br><span style="font-weight:normal">{{small|(1971)}}</span>
! scope="col" | [[Third normal form|3NF]] <br><span style="font-weight:normal">{{small|(1971)}}</span>
! scope="col" | [[Elementary key normal form|EKNF]]<br><span style="font-weight:normal">{{small|(1982)}}</span>
! scope="col" | [[Boyce–Codd normal form|BCNF]]<br><span style="font-weight:normal">{{small|(1974)}}</span>
! scope="col" | [[Fourth normal form|4NF]] <br><span style="font-weight:normal">{{small|(1977)}}</span>
! scope="col" | [[Essential tuple normal form|ETNF]] <br><span style="font-weight:normal">{{small|(2012)}}</span>
! scope="col" | [[Fifth normal form|5NF]] <br><span style="font-weight:normal">{{small|(1979)}}</span>
! scope="col" | [[Domain-key normal form|DKNF]] <br><span style="font-weight:normal">{{small|(1981)}}</span>
! scope="col" | [[Sixth normal form|6NF]] <br><span style="font-weight:normal">{{small|(2003)}}</span>
|-
| Unique rows (no duplicate records)<ref name="Codd1970" /> || {{MaybeCheck}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}}
|-
| Scalar columns (columns cannot contain relations or composite values)<ref name="Codd, E.F 1971" /> || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}}
|-
| Every non-prime attribute has a full [[functional dependency]] on each [[candidate key]] (attributes depend on the ''whole'' of every key)<ref name="Codd, E.F 1971" /> || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}}
|-
| Every non-trivial functional dependency either begins with a [[superkey]] or ends with a prime attribute (attributes depend ''only'' on candidate keys)<ref name="Codd, E.F 1971" /> || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}}
|-
| Every non-trivial functional dependency either begins with a superkey or ends with an [[Elementary key normal form|elementary prime attribute]] (a stricter form of 3NF)|| {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{n/a}}
|-
| Every non-trivial functional dependency begins with a superkey (a stricter form of 3NF)|| {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{n/a}}
|-
| Every non-trivial [[multivalued dependency]] begins with a superkey || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{ya}} || {{n/a}}
|-
| Every [[join dependency]] has a superkey component<ref>{{cite conference |url=https://researcher.watson.ibm.com/researcher/files/us-fagin/icdt12.pdf |archive-url=https://web.archive.org/web/20160306173449/http://researcher.watson.ibm.com/researcher/files/us-fagin/icdt12.pdf |archive-date=2016-03-06 |url-status=live |title=A Normal Form for Preventing Redundant Tuples in Relational Databases |first1=Hugh |last1=Darwen |first2=C. J. |last2=Date |first3=Ronald |last3=Fagin |year=2012 |conference=EDBT/ICDT 2012 Joint Conference |conference-url=http://edbticdt2012.dima.tu-berlin.de/ |book-title=Proceedings of the 15th International Conference on Database Theory |publisher=[[Association for Computing Machinery]] |series=ACM International Conference Proceeding Series |isbn=978-1-4503-0791-8 |oclc=802369023 |doi=10.1145/2274576.2274589 |access-date=2018-05-22 |page=114}}</ref> || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{ya}} || {{n/a}}
|-
| Every join dependency has only superkey components || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{ya}} || {{n/a}}
|-
| Every constraint is a consequence of domain constraints and key constraints || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} || {{na}}
|-
| Every join dependency is trivial || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{na}} || {{ya}} 
|}