1NF
As there are non-atomic values in the column Contact_no, the table is violating 1NF. To make it conform to 1NF, let's decompose the table into R1 and R2.
In R1, we will place S_ID and Contact_no. The Primary Key here will be Composite Key(S_ID, Contact_no).
R1
| S_ID | Contact_no |
|---|---|
| S01 | 9841 |
| S01 | 3762 |
| S02 | 5532 |
| S03 | 5361 |
| S03 | 9857 |
| S04 | 2734 |
| S05 | 5736 |
In R2, we will have the remaining columns(other than Contact_no).
R2
| S_ID | S_name | Address | C_ID | C_name |
|---|---|---|---|---|
| S01 | Ram | Birgunj | C01 | Database |
| S01 | Ram | Birgunj | C02 | E-Commerce |
| S02 | Sita | Kohalpur | C03 | MIS |
| S03 | Krishna | Bhojpur | C01 | Database |
| S04 | Radha | Damak | C03 | E-Commerce |
| S05 | Rawan | Dolpa | C03 | MIS |
| S03 | Krishna | Bhojpur | C03 | MIS |
After eliminating the non-atomic values, the tables R1 and R2 conform to 1NF.
2NF
Here we will check if there are any partial dependencies.
In R1, there are no non-prime attributes. Thus, table R1 is already in 2NF.
In R2, we have considered the Composite Primary Key(S_ID, C_ID) and these are the partial functional dependencies:
is a partial dependency because S_name can be derived from S_ID.
is a partial dependency because Address can be derived from S_ID.
is a partial dependency because C_name can be derived from C_ID.
To eliminate these partial dependencies, we will decompose R2 into R3 and R4.
R3
| S_ID | S_name | Address |
|---|---|---|
| S01 | Ram | Birgunj |
| S02 | Sita | Kohalpur |
| S03 | Krishna | Bhojpur |
| S04 | Radha | Damak |
| S05 | Rawan | Dolpa |
R4
| C_ID | C_name |
|---|---|
| C01 | Database |
| C02 | E-Commerce |
| C03 | MIS |
As we have eliminated the partial dependencies, the tables R3 and R4 conform to 2NF. Thus the final tables R1, R3 and R4 are in 2NF.