DBMS: Solution: Normalization up to 3NF

1NF

First of all, let's check if the above relation is in 1NF. Since all the values in the table(relation) are atomic, we can say that the above relation is already in 1NF.

2NF

To check if the relation is in 2NF, let's check if there is any partial dependency. Here, E_ID is the Primary Key. Thus the following dependencies occur:

E_ID E_Name
E_ID E_Post
E_ID E_Sex
E_ID E_Salary
E_ID E_Add

 

There aren't any partial dependencies here. Thus we can say that the above relation is already in 2NF.

3NF

Let's check if there are any transitive dependency in the above relation. The E_Salary  can be determined from E_Post. Thus, the following transitive dependency occur:

E_ID E_Post
E_Post E_Salary

 

Thus, let's break down the relation into R1 and R2.

R1

E_ID E_Name E_Post E_Sex E_Add
1 Rahul Sr. Engineer M Kathmandu
2 Sushila Web Developer F Birgunj
3 Rahul Web Developer M Balaju
4 Sasim Jr. Engineer M Dhangadhi
5 John Jr. Engineer M New York

R2

E_Post E_Salary
Sr. Engineer 100000
Web Developer 40000
Jr. Engineer 70000

The transitive dependency is removed and hence the tables R1 and R2 are in 3NF.