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
the entity is in second normal form and all the attributes in a table are dependent
on the primary key and only the primary key.
Definition of third normal form The third normal form 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 is in second normal form 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 other candidate key of R. A transitive dependency is a functional
dependency in which X → Z indirectly, by virtue of X → Y and Y → Z.
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, or X is a superkey, or
Every element of A-X, the set difference between A and X, is a prime attribute
Zaniolo's definition gives a clear sense of the difference between 3NF and the more stringent
Boyce–Codd normal form. BCNF simply eliminates the third alternative.
"Nothing but the key" A 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." A common variation supplements this definition with the oath: "so help me
Codd". 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." 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 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. An example of a 2NF 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 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: Update anomalies cannot occur in these tables,
which are both in 3NF. Derivation of Zaniolo's conditions
The definition of 3NF offered by Carlo Zaniolo in 1982, and given above, is proven in the
following way: Let X → A be a nontrivial FD and let A be a non-key attribute. Also
let Y be a key of R. Then Y → X. 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 or, if they meet BCNF, fall short of the higher
normal forms 4NF or 5NF. See also
Attribute-value system References
Further reading
External links Litt's Tips: Normalization
Database Normalization Basics by Mike Chapple 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