• Images
• Videos
• Documents
• Books
• Articles

Definition of third normal form

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E. F. Codd in 1971.²

Codd's definition states that a table is in 3NF if and only if both of the following conditions hold:

• The relation R (table) is in second normal form (2NF).
• Every 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.³ Codd defines a transitive dependency of an attribute set X on an attribute set Z 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.⁴

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:⁵⁶[need quotation to verify]

• 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 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.").

"Nothing but the key"

An approximation of Codd's definition of 3NF, paralleling the traditional oath to give true evidence in a court of law, was given by Bill Kent: "[every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key".⁷ A common variation supplements this definition with the oath "so help me Codd".⁸

Requiring existence of "the key" and requiring that non-key attributes be dependent on "the whole key" ensures 2NF; further requiring that non-key attributes be dependent on "nothing but the key" ensures 3NF. While this phrase is a useful mnemonic, the fact that it only mentions a single key means it defines some necessary but not sufficient conditions to satisfy the 2nd and 3rd normal forms. Both 2NF and 3NF are concerned equally with all candidate keys of a table and not just any one key.

Chris Date refers to Kent's summary as "an intuitively attractive characterization" of 3NF and notes that with slight adaptation it may serve as a definition of the slightly stronger Boyce–Codd normal form: "Each attribute must represent a fact about the key, the whole key, and nothing but the key."⁹ The 3NF version of the definition is weaker than Date's BCNF variation, as the former is concerned only with ensuring that non-key attributes are dependent on keys. Prime attributes (which are keys or parts of keys) must not be functionally dependent at all; they each represent 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 candidate keys, since each part of any such key would violate the "whole key" clause.)

An example of a table that fails to meet the requirements of 3NF is:

Because each row in the table needs to tell us who won a particular Tournament in a particular Year, the composite key {Tournament, Year} is a minimal set of attributes guaranteed to uniquely identify a row. That is, {Tournament, Year} is a candidate key for the table.

The breach of 3NF occurs because the non-prime attribute (Winner's date of birth) is transitively dependent on the candidate key {Tournament, Year} through the non-prime attribute Winner. The fact that Winner's date of birth is functionally dependent on Winner makes the table vulnerable to logical inconsistencies, as there is nothing to stop the same person from being shown with different dates of birth on different records.

In order to express the same facts without violating 3NF, it is necessary to split the table into two:

Update anomalies cannot occur in these tables, because unlike before, Winner is now a candidate key in the second table, thus allowing only one value for Date of birth for each Winner.

Computation

A relation can always be decomposed in third normal form, that is, the relation R is rewritten to projections R1, ..., Rn whose join is equal to the original relation. Further, this decomposition does not lose any functional dependency, in the sense that every functional dependency on R can be derived from the functional dependencies that hold on the projections R1, ..., Rn. What is more, such a decomposition can be computed in polynomial time.¹⁰

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.¹¹

Equivalence of the Codd and Zaniolo definitions of 3NF

The definition of 3NF offered by Carlo Zaniolo in 1982, and given above, can be shown to be equivalent to the Codd definition in the following way: Let X → A be a nontrivial FD (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 there is a functional dependency X → Y iff X is a superkey of R.

Normalization beyond 3NF

Most 3NF tables are free of update, insertion, and deletion anomalies. Certain types of 3NF tables, rarely met with in practice, are affected by such anomalies; these are tables which either fall short of Boyce–Codd normal form (BCNF) or, if they meet BCNF, fall short of the higher normal forms 4NF or 5NF.

Considerations for use in reporting environments

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.¹²¹³ Hadley Wickham's "tidy data" framework is 3NF, with "the constraints framed in statistical language".¹⁴

See also

• Attribute-value system
• First normal form (1NF)
• Second normal form (2NF)
• Fourth normal form (4NF)
• Fifth normal form (5NF)
• Sixth normal form (6NF)

Further reading

• Date, C. J. (1999), An Introduction to Database Systems (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
• Kent, W. (1983) A Simple Guide to Five Normal Forms in Relational Database Theory, Communications of the ACM, vol. 26, pp. 120–126

External links

• Litt's Tips: Normalization
• 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
• Third Normal Form with Simple Examples by exploreDatabase

References

1. Codd, E. F. "Further Normalization of the Data Base Relational Model", p. 34. ↩

2. 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. ↩

3. Codd, p. 43. ↩

4. Codd, p. 45–46. ↩

5. Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata". ACM Transactions on Database Systems 7(3), September 1982. ↩

6. Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts (5th edition), p. 276–277. /wiki/Abraham_Silberschatz ↩

7. Kent, William. "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26 (2), Feb. 1983, pp. 120–125. http://www.bkent.net/Doc/simple5.htm ↩

8. 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. ↩

9. Date, C. J. An Introduction to Database Systems (7th ed.) (Addison Wesley, 2000), p. 379. ↩

10. Serge Abiteboul, Richard B. Hull, Victor Vianu: Foundations of Databases. Addison-Wesley, 1995. http://webdam.inria.fr/Alice/ ISBN 0201537710. Theorem 11.2.14. /wiki/Serge_Abiteboul ↩

11. Hammo, Bassam. "Decomposition, 3NF, BCNF" (PDF). Archived (PDF) from the original on 2023-03-15. https://faculty.ksu.edu.sa/sites/default/files/E-%20Decomposition.pdf ↩

12. "Comparisons between Data Warehouse modelling techniques – Roelant Vos". Roelant Vos. 12 February 2013. Retrieved 5 March 2018. http://roelantvos.com/blog/?p=740 ↩

13. "Hadoop Data Modeling Lessons | EMC". InFocus Blog | Dell EMC Services. 23 September 2014. Retrieved 5 March 2018. https://infocus.dellemc.com/william_schmarzo/hadoop-data-modeling-lessons-vin-diesel/ ↩

14. Wickham, Hadley (2014-09-12). "Tidy Data". Journal of Statistical Software. 59: 1–23. doi:10.18637/jss.v059.i10. ISSN 1548-7660. https://doi.org/10.18637/jss.v059.i10 ↩