Normalization of Relation(EmpName, Dept, Age, Address) into 3NF

Normalize the following table into 3NF.

EmpName Dept Age Address
John ECE, EEE 28 Butwal
Peter ME 31 Pokhara
James BBM, BBS 30 Dang

First Normal Form(1NF)

The above relation is NOT in First Normal Form because of the column Dept. It has non-atomic values. Thus let's decompose the relation into R1 and R2.

R1 will have the columns EmpName and Dept working together as Composite Primary Key.

R1

EmpName Dept
John ECE
John EEE
Peter ME
James BBM
James BBS

 

R2 will have columns other than Dept. EmpName will be its primary key.

R2

EmpName Age Address
John 28 Butwal
Peter 31 Pokhara
James 30 Dang

 

The non-atomic values are eliminated and R1 and R2 are now in First Normal Form.

 

Second Normal Form(2NF)

Let's check if there are any Partial Dependencies in R1 and R2.

In R1, both EmpName and Dept are Prime Attributes. Hence, there isn't any Partial Dependency.

In R2, we have a single(non-composite) Primary key. So, there isn't any Partial Dependency in R2 too.

Hence, we can say R1 and R2 are in the Second Normal Form.

 

Third Normal Form(3NF)

R1 does not have any Transitive Dependency.

R2 does not have any Transitive Dependency.

Thus, both R1 and R2 are in the Third Normal Form(3NF).

 

Additional Steps:

If we consider only the given data, then the relations R1 and R2 are good enough to be called Normalized tables. However, the schema for the tables isn't ideal. Ideally, they should have unique primary keys. The attribute EmpName will not be unique if another record with the same name(EmpName) is added. Thus, let's add a unique attribute EmpId and make it the Primary Key. Our updated tables will be:

R2_Updated

EmpId EmpName Age Address
E01 John 28 Butwal
E02 Peter 31 Pokhara
E03 James 30 Dang

 

R1_Updated

EmpId Dept
E01 ECE
E01 EEE
E02 ME
E03 BBM
E03 BBS