DBMS: Normalize Table(Name, Birth_year, Address, College_name, Birth_place, Phone) to 2NF

Normalize the following table to 2NF:

Name Birth_year Address College_name Birth_place Phone
XYZ 1970 CHABAHIL KANTIPUR PHARPING 9806, 9804
MNO 1980 LOKANTHALI KATHMANDU HETAUDA 9802, 9805
PQR 1970 CHABAHIL KATHMANDU HETAUDA 9806, 9808
XYZ 1990 POKHARA ASCOL HETAUDA 9854

1NF

Firstly, the table is NOT in 1NF because the column Phone is having multiple(non-atomic) values. To convert it to 1NF, the table will be broken down into R1 and R2.

In R1, we will have Primary Key(Name, Birth_Year) and Phone. The new Primary Key will be Composite Key [Name, Birth_year, Phone].

R1

Name Birth_year Phone
XYZ 1970 9806
XYZ 1970 9804
MNO 1980 9802
MNO 1980 9805
PQR 1970 9806
PQR 1970 9808
XYZ 1990 9854

 

In R2, we will have Primary Key(Name, Birth_Year) and the columns other than Phone.

R2

Name Birth_year Address College_name Birth_place
XYZ 1970 CHABAHIL KANTIPUR PHARPING
MNO 1980 LOKANTHALI KATHMANDU HETAUDA
PQR 1970 CHABAHIL KATHMANDU HETAUDA
XYZ 1990 POKHARA ASCOL HETAUDA

 

R1 and R2 are in 1NF.

2NF

Now, the tables R1 and R2 to be in 2NF, there shouldn't be any partial dependency.

In table R1, the following dependency occurs:

{Name,  Birth_year} → Phone

 

In the table R2, the following dependencies occur:

{Name,  Birth_year} → Address
{Name,  Birth_year} → College_name
{Name,  Birth_year} → Birth_place

 

From the given table and data, we can see that Name alone or Birth_year alone can not uniquely identify any non-prime attribute(Address, College_name, Birth_place). In other words, the non-prime attributes completely depend on the primary (composite) key.  Thus it can be concluded that there is no partial dependency in the tables R1 and R2. Therefore, R1 and R2 are in 2NF.

Asked in Year
2016
Course
BBM
University
TU