2 of 19

What is Normalization?�

Normalization is the process of organizing the data in the database.
Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
Normalization divides the larger table into smaller and links them using relationships.
The normal form is used to reduce redundancy from the database table.

3 of 19

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate anomalies leads to data redundancy and can cause data integrity and other problems as the database grows. Normalization consists of a series of guidelines that helps to guide you in creating a good database structure.

4 of 19

Data modification anomalies can be categorized into three types:

Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a relationship due to lack of data.
Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data results in the unintended loss of some other important data.
Updatation Anomaly: The update anomaly is when an update of a single data value requires multiple rows of data to be updated.

5 of 19

Database Normal Forms�

Normalization works through a series of stages called Normal forms. The normal forms apply to individual relations. The relation is said to be in particular normal form if it satisfies constraints.

6 of 19

Following are the various types of Normal forms:

7 of 19

Database Normalization With Examples�

8 of 19

1NF (First Normal Form) Rules�

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

9 of 19

1NF Example�

10 of 19

2NF (Second Normal Form) Rules�

Rule 1- Be in 1NF
Rule 2- Single Column Primary Key that does not functionally dependant on any subset of candidate key relation

12 of 19

It is clear that we can’t move forward to make our simple database in 2^nd Normalization form unless we partition the table above.
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

13 of 19

What are transitive functional dependencies?�

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.

�

14 of 19

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.

15 of 19

3NF Example�

Below is a 3NF example in SQL database:

16 of 19

BCNF (Boyce-Codd Normal Form)�

Even when a database is in 3^rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.
Sometimes is BCNF is also referred as 3.5 Normal Form.

17 of 19

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 4^th Normal Form.

18 of 19

5NF (Fifth Normal Form) Rules�

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

1 of 19