Database Normalization
Normalization is a database design technique that reduces data redundancy and eliminates unwanted features such as Insert, Update and Delete Anomalies. Normalization rules divide larger tables into smaller tables and connect them using relationships. The purpose of Normalization in SQL is to eliminate unnecessary (repetitive) data and ensure that data is stored logically.
Database Normalization with Examples
Database Normalization Example can be easily understood with the help of a case study. Suppose a video library maintains a database of rented movies. Without any normalization in the database, all information is stored in a single table as shown below.
Here you see that the Movies Rented column has multiple values.
1NF (First Normal Form) Rules
- Each table cell must contain a single value.
- Each record must be unique.
- There should only be 1 value in each column.
1NF Example:
In our database, there are 3 people with the same name, Robert Phil, and two of them live in different places.
Therefore, we need both Name and Address to uniquely identify a record. This represents a Composite Key.
2NF (Second Normal Form) Rules
- Be on 1NF
- Single Column Primary Key that does not functionally depend on any subset of the candidate key relationship.
We will carry out the operations to transform our simple database into the 2nd Normalization form without partitioning the table above.
We split our 1NF table into two tables; Table 1 and Table 2.
Table 1 contains member information. Table 2 contains information on movie rentals.
We added a new column called MEMBERSHIP_ID, which is the Primary Key for Table 1. Records can be uniquely identified in Table 1 using the membership ID.
For Table 2, MEMBERSHIP_ID is defined as Foreign Key.
Foreign Key references the Primary Key of another Table and helps you connect tables.
· A Foreign Key may have a different name than the Primary Key.
· Allows rows in one table to have corresponding rows in another.
· Unlike Primary Key, they do not need to be unique. Most of the time they are not.
· Foreign Key may be empty even if the Primary Key cannot be reset.
What are transitive functional dependencies?
A transitive functional dependency can cause changing a non-key column to change any of the other non-key columns.
Consider Table 1. Changing the non-key Name column can change the Salutation column.
3NF (Third Normal Form) Rules
- Be in 2NF
- No transitive functional dependencies
To move our 2NF table to our 3NF table, we need to split the table again.
Below is an example of 3NF in a SQL database:
We split our tables again and created a new table that stores the Salutation column.
There are no transitive functional dependencies and therefore our table is in 3NF.
The 3rd table contains the SALUTATION_ID Primary_Key and the 1st table contains the SALUTATION_ID Foreign Key.
