A: Normalization of Relation(EmpName, Dept, Age, Address) into 3NF - 47

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