NORMALIZATION

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization rules divides larger tables into smaller tables and links them using relationships. The purpose of Normalization in SQL is to eliminate redundant (repetitive) data and ensure data is stored logically.

Database Normal Forms

Here is a list of Normal Forms

• 1NF (First Normal Form)
• 2NF (Second Normal Form)
• 3NF (Third Normal Form)
• BCNF (Boyce-Codd Normal Form)
• 4NF (Fourth Normal Form)
• 5NF (Fifth Normal Form)
• 6NF (Sixth Normal Form)

Though there are 6 normal forms ,in most practical applications, normalization achieves its best in 3rd Normal Form.

Database Normalization With Examples

Database Normalization Example can be easily understood with the help of a case study. Assume, a video library maintains a database of movies rented out. Without any normalization in database, all information is stored in one table as shown below. Let’s understand Normalization in database with tables example:

Table 1

Here you see Movies Rented column has multiple values. Now let’s move into 1st Normal Forms:

1NF (First Normal Form) Rules

• Each table cell should contain a single value.
• Each record needs to be unique.

The above table in 1NF-

1NF Example

Table 1: in 1NF

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single column or combination of multiple columns

Note: Columns in a table that are NOT used to identify a record uniquely are called non-key columns.

A Primary key is a single column value used to identify a database record uniquely.

It has following attributes

• A primary key cannot be NULL
• A primary key value must be unique

A composite key is a primary key composed of multiple columns used to identify a record uniquely

In our database, we have two people with the same name Robert Phil, but they live in different places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a composite key.

2NF (Second Normal Form) Rules

• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key

It is clear that we can’t move forward to make our simple database in 2nd Normalization form unless we partition the table above.

Table 1

Table 2

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table 1. Records can be uniquely identified in Table 1 using membership id

In table 2 Membership_id is the foreign key(Foreign Key references the primary key of another Table! It helps connect your Tables).

Before moving into 3NF, we have to know about transitive functional dependency

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name may change Salutation.

3NF (Third Normal Form) Rules

• Rule 1- Be in 2NF
• Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

Below is a 3NF example in SQL database:

Table 1

Table 2

Table 3

We have again divided our tables and created a new table which stores Salutations.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3

In most practical applications, normalization achieves its best in 3rd Normal Form.

Let’s have a out look on BCNF

BCNF (Boyce-Codd Normal Form)

For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:

1. It should be in the Third Normal Form.
2. And, for any dependency A → B, A should be a super key.

The second point sounds a bit tricky, right? In simple words, it means, that for a dependency A → B, A cannot be a non-prime attribute, if B is a prime attribute.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

6th Normal Form is not standardized, yet however, it is being discussed by database experts for some time. Hopefully, we would have a clear & standardized definition for 6th Normal Form in the near future…