[All]
The definition of first, second, third, fourth and fifth normal forms.
Abstract: The definition of first, second, third, fourth and fifth normal forms.
Problem:
The definition of first, second, third, fourth and fifth normal forms.
Solution:
In the process of efficiently storing data, and eliminating redundancy, tables in a database are
designed and created to be in one of five possible normal forms. Each normal form contains and
enforces the rules of the previous form, and, in turn, applies some stricter rules on the design of
tables.
A set of tables in a database are initially said to be in 0 normal form.
First Normal Form:
===============
Tables are said to be in first normal form when:
- The table has a primary key.
- No single attribute (column) has multiple values.
- The non-key attributes (columns) depend on the primary key.
Some examples of placing a table in first normal form are:
author_id: stories:
000024 novelist, playwright // multiple values
000034 magazine columnist
002345 novella, newpaper columnist // multiple values
In first normal form the table would look like:
author_id: stories:
000024 novelist
000024 playwright
000034 magazine columnist
002345 novella
002345 newpaper columnist
Second Normal Form:
=================
Tables are said to be in second normal form when:
- The tables meet the criteria for first normal form.
- If the primary key is a composite of attributes (contains multiple
columns), the non key attributes (columns) must depend on the whole
key.
Note: Any table with a primay key that is composed of a single
attribute (column) is automatically in second normal form.
Third Normal Form:
================
Tables are said to be in third normal form when:
- The tables meet the criteria for second normal form.
- Each non-key attribute in a row does not depend on the entry in
another key column.
Fourth Normal Form:
================
Tables are said to be in fourth normal form when:
- The table meets the criteria for third normal form.
- Situations where non-key attributes depend on the key column
exclusive of other non-key columns are eliminated.
Fifth Normal Form:
===============
Tables are said to be in fifth normal form when:
- The table meets the criteria for fourth normal form.
- The table consists of a key attribute and a non-key attribute only.