Database Normalization Basics

First Normal Form (1NF):

• Identify related groups of data (ex: orders, customers, products) and create separate tables for each group.
• All tables should have a Primary Key identifying an individual row in a table.
• No two columns in a table should have the same information and each column can contain only one attribute.

In essence 1st normal form is about removing redundant data and creating tables for related groups of data and creating a primary key.

Second Normal Form (2NF):

• The database is required to be in the 1NF form.
• Identify data that applies to multiple rows in a table and extract it to a separate table(s)
• Create Foreign Key relationships between the new tables and the original table.

Third Normal Form (3NF):

• The database is required to be in the 2NF form.
• A table should not contain columns that are not dependent on the Primary Key. For example, an Order table may contain a CustomerID column to denote the customer who placed the order, but may not contain the customers date of birth because its not related in any sense to the order.

Fourth Normal Form (4NF):

• The database is required to be in the 3NF form.
• A table should not contain 2 or more multi-valued facts about an entity.

For example, consider a table as shown below:

-------------------------------------
| EmployeeId  | Skill    | Language |
-------------------------------------
|  1          | Type     | German   |
-------------------------------------
|  1          | Cook     | Greek    |
-------------------------------------
|  1          |          | Spanish  |
-------------------------------------

This table violates the 4NF form since it has 2 multi-valued facts (Skill and Language) in the same table. Instead this table should be split into two tables that individually satisfy the 4NF.

-----------------------          -----------------------------
| EmployeeId | Skill  |          | EmployeeId   | Langugage  |
-----------------------          -----------------------------
|  1         | Type   |          |    1         | German     |
-----------------------          -----------------------------
|  1         | Cook   |          |    1         | Greek      |
----------------------           -----------------------------
                                 |    1         | Spanish    |
                                 -----------------------------

References: http://www.bkent.net/Doc/simple5.htm