Normalization Process in DBMS
Pre-Requisite: Introduction to Database Normalization
Database Normalization is a stepwise formal process that allows us to decompose database tables in such a way that both data dependency and update anomalies are minimized. It makes use of functional dependency that exists in the table and the primary key or candidate key in analyzing the tables. Normal forms were initially proposed called
Subsequently, R, Boyce, and E. F. Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form. With the exception of 1NF, all these normal forms are based on functional dependency among the attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However, these later normal forms deal with situations that are very rare.
Normalization Process in DBMS
Now, we are going to describe the process of Normalization by considering an Example. Let us look into that.
Normalization Process Example
In this example, we will look into that how we can normalize the database with the help of different Normal Forms. We will look into each of the Normal Forms separately in this example.
Consider the table 1 shown below:
Full Name |
Institute |
Courses |
Result |
Naveen Kumar |
IIT Delhi |
DBMS, OS |
Pass |
Utkarsh Tiwari |
IIT Bombay |
CN. COA |
Fail |
Utkarsh Tiwari |
IIT Kanpur |
OS |
Fail |
Now, we are re-structuring the table according to the 1st Normal Form.
Rules of 1st Normal Form
- Each table should contain a single value.
- Each record needs to be unique.
Table 1 after applying 1st Normal Form:
Full Name |
Institute |
Subject |
Result |
Naveen Kumar |
IIT Delhi |
DBMS |
Pass |
Naveen Kumar |
IIT Delhi |
OS |
Pass |
Utkarsh Tiwari |
IIT Bombay |
CN |
Fail |
Utkarsh Tiwari |
IIT Bombay |
COA |
Fail |
Utkarsh Tiwari |
IIT Kanpur |
OS |
Fail |
Consider the table 2 shown below,
Full Name |
Institute |
Subject |
Result |
Utkarsh Tiwari |
IIT Bombay |
COA |
Fail |
Utkarsh Tiwari |
IIT Kanpur |
OS |
Fail |
Here, People having the same name are from different institutes. So, we require Full Name and Institute to Identify a Row of the database. For this, we have to remove Partial Dependency.
Let’s look at the 2nd Normal Form.
Rules of 2nd Normal Form
- The table should be in 1NF.
- Primary Key does not functionally dependent on any subset of Candidate Key.
Table 1
ID |
Full Name |
Institute |
Result |
1 |
Naveen Kumar |
IIT Delhi |
Pass |
2 |
Utkarsh Tiwari |
IIT Bombay |
Fail |
3 |
Utkarsh Tiwari |
IIT Kanpur |
Fail |
Table 2
ID |
Subject |
1 |
DBMS |
1 |
OS |
2 |
CN |
2 |
COA |
3 |
OS |
Here, the Id in Table 2 is Foreign Key to the Id in Table 1. Now, we have to remove Transitive Functional Dependency from our Table to Normalize our Database. A Transitive Functional Dependency basically tells us that there is an indirect relationship between functional dependency.
Now, let us look at the 3rd Normal Form.
Rules of 3rd Normal Form
- The tables should be in 2NF.
- There will be no Transitive Dependency.
Table 1
ID |
Full Name |
Institute |
Result_ID |
1 |
Naveen Kumar |
IIT Delhi |
1 |
2 |
Utkarsh Tiwari |
IIT Bombay |
2 |
3 |
Utkarsh Tiwari |
IIT Kanpur |
2 |
Table 2
ID |
Subject |
1 |
DBMS |
1 |
OS |
2 |
CN |
2 |
COA |
3 |
OS |
Table 3
Result_ID |
Result |
1 |
Pass |
2 |
Fail |
3 |
On Hold |
Finally, Our Database is Normalized. From the above-mentioned example, we have reached our level of Normalization. In fact, there are also some higher forms or next levels of Normalization. Now, we are going to discuss them one by one.
Other Normal Forms
Boyce-Codd Normal Form (BCNF)
Sometimes, when the database is in the 3rd Normal Form, there exist some anomalies in DBMS, like when more than one Candidate Keys is present in the Database. This has to be removed under BCNF. BCNF is also called 3.5NF.
4th Normal Form
Whenever a Database contains multivalued and independent data in two or more tables, then the database is to be considered in the 4th Normal Form.
5th Normal Form
Whenever a Database Table is not in 4NF, and we cannot divide it into smaller tables keeping our data safe with us, then our Database is in 5th Normal Form.
Summary of Normalization in a Nutshell
Normal Form |
Test |
Remedy (Normalization) |
1NF |
The relation should have no non-atomic attributes or nested relations. |
Form a name relation for each non-atomic attribute or nested relation. |
2NF |
For relations where the primary key contains multiple attributes, no non-key
attributes should be functionally dependent on a part of the primary key.
|
Decompose and set up a new relation for each partial key with its dependent attributes.
Make sure to keep a relationship with the original primary key and any attributes that are fully functionally dependent on it.
|
3NF |
The relation should not have a non-key attribute functionally determined by another
non-key attribute (or by a set of non-key attributes) i.e., there should be no
transitive dependency of a non-key attribute of the primary key.
|
Decompose and set up a relation that includes the non-key attribute(s) that functionally determine(s) another non-key attribute (s). |
BCNF |
The relation should not have any attribute in Functional Dependency which is
non-prime, the attribute that doesn’t occur in any candidate key.
|
Make sure that the left side of every functional dependency is a candidate key. |
4NF |
The relation should not have a multi-value dependency means it occurs when
two attributes of a table are independent of each other but both depend on a
third attribute.
|
Decompose the table into two subtables. |
5NF |
The relation should not have join dependency means if a table can be recreated by joining multiple tables and each of the tables has a subset of the attributes of the table, then the table is in Join Dependency. |
Decompose all the tables into as many as possible numbers in order to avoid dependency. |
Last Updated :
06 May, 2023
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...