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:
In the table R2, the following dependencies occur:
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.