Normalize the following table into 3NF.

EmpName | Dept | Age | Address |
---|---|---|---|

John | ECE, EEE | 28 | Butwal |

Peter | ME | 31 | Pokhara |

James | BBM, BBS | 30 | Dang |

## 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 |