Editing Third normal form (section)

=="Nothing but the key"==
An approximation of Codd's definition of 3NF, paralleling the traditional [[sworn testimony|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".<ref name="Kent">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.</ref> A common variation supplements this definition with the oath "so help me [[Edgar F. Codd|Codd]]".<ref name="Diehr">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.</ref>

Requiring existence of "the key" and requiring that non-key attributes be dependent on "the whole key" ensures [[Second normal form|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 [[Necessity and sufficiency|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.

[[Christopher J. Date|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."<ref name="DateIntro">Date, C. J. ''An Introduction to Database Systems'' (7th ed.) (Addison Wesley, 2000), p. 379.</ref> 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:
{| class="wikitable"
|+ Tournament winners
! <u>Tournament</u> !! <u>Year</u> !! Winner !! Winner's 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'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:
{| style="border-spacing:2em 0; margin-left:-2em"
| valign="top" |
{| class="wikitable"
|+ Tournament winners
! <u>Tournament</u> !! <u>Year</u> !! Winner
|-
|Indiana Invitational||1998||Al Fredrickson
|-
|Cleveland Open||1999||Bob Albertson
|-
|Des Moines Masters||1999||Al Fredrickson
|-
|Indiana Invitational||1999||Chip Masterson
|}
| valign="top" |
{| class="wikitable"
|+ Winner's dates of birth
! <u>Winner</u> !! 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, 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.