Problem solve Get help with specific problems with your technologies, process and projects.

An overview of normalization forms

Our expert defines Fourth Normal Form and Fifth Normal Form.

Please define Fourth Normal Form and Fifth Normal Form? What are the advantages of using Fifth Normal Form?

The following answer is summarized from my book, Database administration: The complete guide to practices and procedures. The book goes into more details on normalization and walks through examples.

Normalization is a series of steps followed to obtain a database design that allows for efficient access and storage of data in a relational database. These steps reduce data redundancy and the chances of data becoming inconsistent. A table is said to be normalized if it satisfies certain constraints. Codd's original work defined three such forms but there are now five generally accepted steps of normalization. The output of the first step is called First Normal Form (1NF), the output of the second step is Second Normal Form (2NF), etc.

A row is in first normal form if and only if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship. A row is in second normal form if and only if it is in first normal form and every non-key attribute is fully dependent on the key. 2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key. A row is in third normal form if and only if it is in second normal form and every non-key attribute is non-transitively dependent on the primary key. 3NF eliminates functional dependencies on non-key fields by putting them in a separate table. At this stage, all non-key fields are dependent on the key, the whole key and nothing but the key.

But normalization does not stop with 3NF. Additional normal forms have been identified and documented. However, normalization past 3NF does not occur often in normal practice because most tables in 3NF are usually also in 5NF. The additional normal forms are:

* Boyce Codd Normal Form (BCNF) is a further refinement of 3NF. Indeed, in his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if and only if every determinant is a candidate key. Most entities in 3NF are already in BCNF.

* An entity is in Fourth Normal Form (4NF) if and only if it is in 3NF and has no multiple sets of multi-valued dependencies. In other words, 4NF states that no entity can have more than a single one-to-many relationship within an entity if the one-to-many attributes are independent of each other.

* Fifth Normal Form (5NF) specifies that every join dependency for the entity must be a consequence of its candidate keys.

For more information on normalization consult the following links:

A comparison of BCNF and 3NF is given here:

A complete definition of 4NF is given here:

Finally, walk thru the presentation at this link for a nice introduction to normalization thru 5NF:

Dig Deeper on IBM DB2 management