Third normal form

From: http://en.wikipedia.org/wiki/Third_normal_form

Third normal form is the third step in normalizing a Database design to reduce the duplication of data and ensure Referential integrity by ensuring that (a) the entity is in Second normal form and (b) all the attributes in a table are dependent on the primary key and only the primary key.^[1]

Contents

  [hide] 

• 1 Definition of third normal form
• 2 "Nothing but the key"
• 3 Derivation of Zaniolo's conditions
• 4 Normalization beyond 3NF
• 5 See also
• 6 References
• 7 Further reading
• 8 External links

Definition of third normal form[edit]

The third normal form (3NF) is a normal form used in database normalization. 3NF was originally defined by E.F. Codd in 1971.^[2] 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 every superkey of R.

A non-prime attribute of R is an attribute that does not belong to any candidate key of R.^[3] 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).^[4]

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
• Every element of A-X, the set difference between A and X, is a prime attribute (i.e., each attribute in A-X is contained in some candidate key)^{[5][6][need quotation to verify]}

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 A-X, the set difference between A and X, is a prime attribute").

"Nothing but the key"[edit]

A memorable statement 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."^[7] A common variation supplements this definition with the oath: "so help me Codd".^[8]

Requiring existence of "the key" ensures that the table is in 1NF; 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.

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."^[9] 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. (It should be noted here that 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 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

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 Date of Birth is transitively dependent on the candidate key {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

Winner Dates of Birth Winner 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.

Derivation of Zaniolo's conditions[edit]

The definition of 3NF offered by Carlo Zaniolo in 1982, and given above, 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.

Normalization beyond 3NF[edit]

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.

See also[edit]

• Attribute-value system

References[edit]

1. Jump up^ "What is Third Normal Form?" Cory Janssen, Technopedia, retrieved 24 April 2014
2. Jump up^ 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.
3. Jump up^ Codd, p. 43.
4. Jump up^ Codd, p. 45–46.
5. Jump up^ Zaniolo, Carlo. "A New Normal Form for the Design of Relational Database Schemata." ACM Transactions on Database Systems 7(3), September 1982.
6. Jump up^ Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts (5th edition), p. 276-277
7. Jump up^ Kent, William. "A Simple Guide to Five Normal Forms in Relational Database Theory", Communications of the ACM 26 (2), Feb. 1983, pp. 120–125.
8. Jump up^ 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. Jump up^ Date, C.J. An Introduction to Database Systems (7th ed.) (Addison Wesley, 2000), p. 379.

Further reading[edit]

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

• 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

[hide] v t e Database normalization Main topics Normalization Denormalization Normal forms 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