Normalization

Normalzation is a formal technique for analyzing relations based on their primary key (or candidate key) and functional dependencies (Codd, 1972). The technique involves a series of rules that can be used to test individual relations so that a database can be normalized to any degree. When a requirement is not met, the relation violating the requirement must be decomposed into relations that individually meet the requirements of normalization.
 First Normal Form
   Second Normal Form  
   Third Normal Form  
   Boyce-Codd Normal Form  
   Fourth Normal Form  
   Fifth Normal Form  
 
 
 
 
 

First Normal Form

A relation is in First Normal Form if the intersection of of each row and column contains one and only one value.
The domain of attributes must include only atomic (simple, indivisible) values and the value of any attribute in a tuple must be a single value from the domain of that attribute.
Example

Second Normal Form

A relation is in Second Normal Form if it is in 1NF and every non-primary-key attributes is fully functionally dependent on the primary key.
Example

Third Normal Form

A relation is in Third Normal Form if it is in 2NF and no non-primary-key attribute is transitively dependent on the primary key.
Example

Boyce-Codd Normal Form

A relation is in BCNF, if and only if, every determinant is a candidate key.
Example

Before moving to higher level normal forms (which are not used very often) let's review the normalization process.

Fourth Normal Form

Multi-valued dependency (MVD) Represents a dependency between attributes (for example, A, B, and C) in a relation, such that for each value of A there is a set of values for B and a set values for C. However, the set of values for B and C are independent of each other.

We represent a MVD between attributes A, B, and C using the following notation: 
 A ->> B
 A ->> C
A multi-valued dependency A ->>B can be trivial if
(a) B is a subset A or
(b) A union B = R
and nontrivial otherwise.
A relation is in 4NF if it is in BCNF and contains no nontrivial multi-valued dependencies.
Example

Fifth Normal Form

Lossless-join dependency A property of decomposition, which ensures that no spurious tuples are generated when relations are reunited through a natural join operation.

Join dependency Describes a type of dependency. For example, for a relation R with subsets of the attributes of R denoted as A, B, ..., Z, a relation R satisfies a join dependency if, and only if, every legal value of R is equal to the join of its projections on A, B, ..., Z.

We've seen a lot of examples where we decompose a relation into two relations. However, there are cases where we require to perform a lossless-join decompose of a relation into more than two relations. These cases are the focus of the lossless-join dependency and fifth normal form.
A relation is in 5NF if it has no join dependency.
Example