Third normal form
From Free net encyclopedia
The third normal form (3NF) is a normal form used in database normalization to check if all the non-key attributes of a relation depend only on the candidate keys of the relation. This means that all non-key attributes are mutually independent or, in other words, that a non-key attribute cannot be transitively dependent of another non-key attribute.
The 3NF differs from the second normal form in that all non-key attributes in 3NF are required to be directly dependent on each candidate key of the relation. The 3NF therefore insists, in the words of Bill Kent, that "the relation is based on the key, the whole key and nothing but the key", to which some add "so help me Codd."
Formal definition
Let:
- <math>R</math> be a relation schema,
- <math>F</math> be the set of functional dependencies given to hold over <math>R</math>,
- <math>X</math> be a subset of the attributes of <math>R</math>, and
- <math>A</math> be an attribute of <math>R</math>
<math>R</math> is in third normal form if for every functional dependency of the form <math>X \to A</math> one of the following statements is true:
- <math>A \in X</math>; that is, <math>A</math> is a trivial functional dependency , or
- <math>X</math> is a superkey, or
- <math>A</math> is part of some key for <math>R</math>
Any relation with a partial dependency or a transitive dependency violates the third normal form.
Example
Consider a relation that defines a machine part as follows:
| Part number (primary key) | Manufacturer name | Manufacturer address |
|---|---|---|
| 1000 | Toyota | Park Avenue |
| 1001 | Mitsubishi | Lincoln Street |
| 1002 | Toyota | Park Avenue |
In this case, the manufacturer address does not belong on this relation, because it is a fact about the manufacturer of the part, rather than the part itself. The manufacturer address should therefore be moved into a separate table with the attributes:
| Manufacturer name (primary key) | Manufacturer address |
|---|---|
| Toyota | Park Avenue |
| Mitsubishi | Lincoln Street |
...and the original table should be redefined as:
| Part number (primary key) | Manufacturer name |
|---|---|
| 1000 | Toyota |
| 1001 | Mitsubishi |
| 1002 | Toyota |
The problem with a table not being in 3NF is that for every manufacturer's name we have to maintain a redundant address (i.e. an address for each part number, rather than one for each manufacturer's name).
Sources
- Ramakrishnan, Raghu and Johannes Gehrke. "Schema Refinement and Normal Forms." Database Management Systems, Third Edition. ISBN 0072465638. McGraw Hill. August 14, 2002.
- Gupta, Gopal. "Third Normal Form (3NF)." Single Valued Normalization. 1996.