Normalize the Relational Table (S_ID, S_name, Contact_no, Address, C_ID, C_name) to 2NF

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.