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
EmpId Dept
E02 ME
E03 BBM
E03 BBS