Third normal form

From Wikipedia, the free encyclopedia

Contents

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd[1] 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.[2] A transitive dependency is a functional dependency in which XZ (X determines Z) by virtue of XY and YZ; i.e. X determines Z only indirectly.

An alternative formulation of Codd's definition, given by Carlo Zaniolo[3] in 1982, is this: a table is in 3NF if and only if, for each of its functional dependencies XA, 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)

Zaniolo's definition has the advantage of giving 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").

[edit] "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."[4] A common variation supplements this definition with the oath: "so help me Codd".[5]

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."[6] Here the requirement is concerned with every attribute in the table, not just non-key attributes.

[edit] Example

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

Tournament Winners
Tournament Year Winner Winner Date of Birth
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977

The only candidate key is {Tournament, Year}.

The breach of 3NF occurs because the non-prime attribute Winner Date of Birth is transitively dependent on {Tournament, Year} via the non-prime attribute Winner. The fact that Winner 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:

Tournament Winners
Tournament Year Winner
Indiana Invitational 1998 Al Fredrickson
Cleveland Open 1999 Bob Albertson
Des Moines Masters 1999 Al Fredrickson
Indiana Invitational 1999 Chip Masterson
Player Dates of Birth
Player Date of Birth
Chip Masterson 14 March 1977
Al Fredrickson 21 July 1975
Bob Albertson 28 September 1968

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

[edit] 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.

[edit] Notes & References

  1. ^ 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 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.
  2. ^ Codd, 43.
  3. ^ Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." ACM Transactions on Database Systems 7(3), September 1982.
  4. ^ Kent, William. "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26 (2), Feb. 1983, pp. 120-125.
  5. ^ 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.
  6. ^ Date, C.J. An Introduction to Database Systems (7th ed.) (Addison Wesley, 2000), p. 379.

[edit] See also

[edit] Further reading

[edit] External links

Topics in Database normalization

First normal form | Second normal form | Third normal form
Boyce-Codd normal form | Fourth normal form | Fifth normal form | Domain/key normal form | Sixth normal form
Denormalization