Database normalization is a database schema design technique, by which an existing schema is modified to minimize redundancy and dependency of data.
Normalization split a large table into smaller tables and define relationships between them to increases the clarity in organizing data.
Some Facts About Database Normalization
- The words normalization and normal form refer to the structure of a database.
- Normalization was developed by IBM researcher E.F. Codd In the 1970s.
- Normalization increases clarity in organizing data in Databases.
Normalization of a Database is achieved by following a set of rules called 'forms' in creating the database.
Database Normalization Rules
First Normal Form (1NF)
Each column is unique in 1NF.
Example:
Sample Employee table, it displays employees are working with multiple departments.
| Employee | Age | Department |
|---|---|---|
| Melvin | 32 | Marketing, Sales |
| Edward | 45 | Quality Assurance |
| Alex | 36 | Human Resource |
Employee table following 1NF:
| Employee | Age | Department |
|---|---|---|
| Melvin | 32 | Marketing |
| Melvin | 32 | Sales |
| Edward | 45 | Quality Assurance |
| Alex | 36 | Human Resource |
Second Normal Form (2NF)
The entity should be considered already in 1NF, and all attributes within the entity should depend solely on the unique identifier of the entity.
Example:
Sample Products table:
| productID | product | Brand |
|---|---|---|
| 1 | Monitor | Apple |
| 2 | Monitor | Samsung |
| 3 | Scanner | HP |
| 4 | Head phone | JBL |
Product table following 2NF:
Products Category table:
| productID | product |
|---|---|
| 1 | Monitor |
| 2 | Scanner |
| 3 | Head phone |
Brand table:
| brandID | brand |
|---|---|
| 1 | Apple |
| 2 | Samsung |
| 3 | HP |
| 4 | JBL |
Products Brand table:
| pbID | productID | brandID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 3 |
| 4 | 3 | 4 |
Third Normal Form (3NF)
The entity should be considered already in 2NF, and no column entry should be dependent on any other entry (value) other than the key for the table.
If such an entity exists, move it outside into a new table.
3NF is achieved, considered as the database is normalized.
Boyce-Codd Normal Form (BCNF)
3NF and all tables in the database should be only one primary key.
Fourth Normal Form (4NF)
Tables cannot have multi-valued dependencies on a Primary Key.
Fifth Normal Form (5NF)
A composite key shouldn't have any cyclic dependencies.
Well, this is a highly simplified explanation for Database Normalization. One can study this process extensively, though. After working with databases for some time, you'll automatically create Normalized databases, as it's logical and practical.