Third normal form

From Wikipedia, the free 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 on 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."

1 Formal definition
2 Example
3 Sources
4 References
5 External links

[edit] Formal definition

Let:

$R$ be a relation schema,
$F$ be the set of functional dependencies given to hold over $R$ ,
$X$ be a subset of the attributes of $R$ , and
$A$ be an attribute of $R$

$R$ is in third normal form if for every functional dependency of the form $X \to A$ one of the following statements is true:

$A \in X$ ; that is, $A$ is a trivial functional dependency , or
$X$ is a superkey, or
$A$ is part of some key for $R$

Any relation with a partial dependency or a transitive dependency violates the third normal form.

[edit] Example

Consider a relation that defines a machine part as follows:

Mechanical part
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
Manufacturer name (primary key)	Manufacturer address
Toyota	Park Avenue
Mitsubishi	Lincoln Street

...and the original table should be redefined as:

Mechanical part
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).

Or look at the following example:

Order
Order Number (primary key)	Customer Number	Unit Price	Quantity	Total
1000	David	$35.00	3	$105.00
1001	Jim	$25.00	2	$50.00
1002	Bob	$25.00	3	$75.00

In this case Total does not belong to the table because Total can be inferred from multiplying Unit Price by Quantity. To comply with 3NF, remove the Total column (in a query, instead of "SELECT Orders.Total FROM Orders", use SELECT UnitPrice * Quantity FROM Orders).

Order
Order Number (primary key)	Customer Number	Unit Price	Quantity
1000	David	$35.00	3
1001	Jim	$25.00	2
1002	Bob	$25.00	3

[edit] Sources

Ramakrishnan, Raghu and Johannes Gehrke. "Schema Refinement and Normal Forms." Database Management Systems, Third Edition. ISBN 0-07-246563-8. McGraw Hill. August 14, 2002.
Gupta, Gopal. "Third Normal Form (3NF)." Single Valued Normalization. 1996.