Third normal form

Third normal form

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. CoddCodd, 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 24th-25th, 1971.) IBM Research Report RJ909 (August 31st, 1971). Republished in Randall J. Rustin (ed.), "Data Base Systems: Courant Computer Science Symposia Series 6". Prentice-Hall, 1972.] 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 (i.e. directly dependent) on every key of R.

A non-prime attribute of R is an attribute that does not belong to any candidate key of R.Codd, 43.] A transitive dependency is a functional dependency in which "X" → "Z" ("X" determines "Z") indirectly, by virtue of "X" → "Y" and "Y" → "Z" (where it is not the case that "Y" → "X"). [Codd, 45-46.]

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" → "A", at least one of the following conditions holds:

* "X" contains "A" (that is, "X" → "A" is trivial functional dependency), or
* "X" is a superkey, or
* "A" is a prime attribute (i.e., "A" is contained within a candidate key)Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." "ACM Transactions on Database Systems" 7(3), September 1982.]

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 ("A" is a prime attribute").

"Nothing but the key"

A memorable summary of Codd's definition of 3NF, paralleling the traditional pledge 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."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.] A common variation supplements this definition with the oath: "so help me Codd".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.]

Requiring that non-key attributes be dependent on "the whole key" ensures that a table is in 2NF; further requiring that non-key attributes be dependent on "nothing but the key" ensures that the table is in 3NF.

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."Date, C.J. "An Introduction to Database Systems" (7th ed.) (Addison Wesley, 2000), p. 379.] Here the requirement is concerned with every attribute in the table, not just non-key attributes.

Example

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

Update anomalies cannot occur in these tables, which are both in 3NF.

Derivation of Zaniolo's conditions

A lemma proved by Zaniolo states that a table is in 3NF if and only if, for each of its functional dependencies "X" → "A", at least one of the following conditions holds:

* "X" contains "A", or
* "X" is a superkey, or
* "A" is a prime attribute (i.e., "A" is contained within a candidate key)

The lemma is proved 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-key attribute. Also let Y be a key of R. Then Y → X. Therefore A is not transitively dependent on Y if and only if X → Y, that is, if and only if X is a superkey. [Zaniolo, 494.]

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.

Notes & References

ee also

*Attribute-value system
*Fourth normal form

Further reading

* Date, C. J. (1999), " [http://www.aw-bc.com/catalog/academic/product/0,1144,0321197844,00.html An Introduction to Database Systems] " (8th ed.). Addison-Wesley Longman. ISBN 0-321-19784-4.
* Kent, W. (1983) " [http://www.bkent.net/Doc/simple5.htm A Simple Guide to Five Normal Forms in Relational Database Theory] ", Communications of the ACM, vol. 26, pp. 120-125

External links

* [http://www.troubleshooters.com/littstip/ltnorm.html Litt's Tips: Normalization]
* [http://www.datamodel.org/NormalizationRules.html Rules Of Data Normalization]
* [http://databases.about.com/od/specificproducts/a/normalization.htm Database Normalization Basics] by Mike Chapple (About.com)
* [http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html An Introduction to Database Normalization] by Mike Hillyer.
* [http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html Normalization] by ITS, University of Texas.
* [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
* [http://www.dbdebunk.com Database Debunkings] : Fabian Pascal, Chris Date, and Hugh Darwen


Wikimedia Foundation. 2010.

Look at other dictionaries:

  • third normal form — noun A stage in the normalization of a relational database in which a database is in second normal form and all non key attributes are mutually independent (no transient dependencies.) See Also …   Wiktionary

  • Fourth normal form — (4NF) is a normal form used in database normalization. Introduced by Ronald Fagin in 1977, 4NF is the next level of normalization after Boyce Codd normal form (BCNF). Whereas the second, third, and Boyce Codd normal forms are concerned with… …   Wikipedia

  • Second normal form — (2NF) is a normal form used in database normalization. 2NF was originally defined by E.F. CoddCodd, E.F. Further Normalization of the Data Base Relational Model. (Presented at Courant Computer Science Symposia Series 6, Data Base Systems, New… …   Wikipedia

  • fifth normal form — noun A stage in the normalization of a relational database in which a database is in fourth normal form and every join dependency is implied by the candidate keys. See Also: first normal form …   Wiktionary

  • second normal form — noun A stage in the normalization of a relational database in which it is in first normal form and every non key attribute is dependent upon the entire primary key. See Also: first normal form …   Wiktionary

  • fourth normal form — noun A stage in the normalization of a relational database in which a database is in Boyce Codd normal form and all multi valued dependencies are functional dependencies. See Also: first normal form, second normal form, third normal form …   Wiktionary

  • first normal form — noun A stage in the normalization of a relational database in which repeating groups and attributes have been eliminated by putting each into a separate table connected by a primary key foreign key relationship. See Also: second normal form …   Wiktionary

  • Jordan normal form — In linear algebra, a Jordan normal form (often called Jordan canonical form)[1] of a linear operator on a finite dimensional vector space is an upper triangular matrix of a particular form called Jordan matrix, representing the operator on some… …   Wikipedia

  • First normal form — (1NF or Minimal Form) is a normal form used in database normalization. A relational database table that adheres to 1NF is one that meets a certain minimum set of criteria. These criteria are basically concerned with ensuring that the table is a… …   Wikipedia

  • Domain/key normal form — (DKNF) is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints. A domain constraint specifies the permissible values for a given attribute, while a …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”