DBMS: Normalize table (Pid, Pname, Price, Mid, Mname, Address, Phone). Solved.

Normalize the following table.

Pid Pname Price Mid Mname Address Phone
1 ABC 1000 101 EFG Ktm, Bkt 4412
2 XYZ 2000 101 EFG Bkt 4561
1 ABC 3000 102 MNP Ltp, Ktm 2341
3 ABC 1000 105 IBD Brt, Bkt 2169
4 XYZ 1000 105 IBD Ktm 6635

1NF

Since the column Address has non-atomic values, the table is not in 1NF. Thus the table will be broken down into R1 and R2.

Based on the data in the above table, we see that the Address is derived from the combination of Pid and Mid. Thus in R1, we will have Primary Key(Pid, Mid) and Address. The Composite Primary Key (Pid, Mid, Address) will uniquely identify each tuple.

R1

Pid Mid Address
1 101 Ktm
1 101 Bkt
2 101 Bkt
1 102 Ltp
1 102 Ktm
3 105 Brt
3 105 Bkt
4 105 Ktm

 

In R2, we will have the remaining columns.

R2

Pid Pname Price Mid Mname Phone
1 ABC 1000 101 EFG 4412
2 XYZ 2000 101 EFG 4561
1 ABC 3000 102 MNP 2341
3 ABC 1000 105 IBD 2169
4 XYZ 1000 105 IBD 6635

R1 and R2 are in 1NF.

2NF

R1 is already in 2NF because there is no non-prime attribute and hence there are no partial dependencies.

In R2 however, there are a few partial dependencies.

{Pid, Mid} Pname

is a partial dependency because Pname can be derived from only Pid.

Similarly,

{Pid, Mid} Mname

is a partial dependency because Mname can be derived from only Mid.

To convert table R2 into 2NF, we break down the table into R3, R4, and R5.

R3

Pid Pname
1 ABC
2 XYZ
3 ABC
4 XYZ

 

R4

Mid Mname
101 EFG
102 MNP
105 IBD
 

R5

Pid Mid Price Phone
1 101 1000 4412
2 101 2000 4561
1 102 3000 2341
3 105 1000 2169
4 105 1000 6635

 

Now, the following dependencies occur:

In R3,
Pid Pname
 
In R4,
Mid Mname
 
and In R5,
{Pid, Mid}Price
{Pid, Mid}Address
{Pid, Mid}Phone

 

As we can see there aren't any partial dependencies now, we can confirm that the tables R1, R3, R4 and R5 are in Second Normal Form(2NF).

3NF

Table R1 is in 3NF because there isn't any Transitive Dependency.

Table R3 is in 3NF because there isn't any Transitive Dependency.

Table R4 is in 3NF because there isn't any Transitive Dependency.

Similarly, there isn't any Transitive Dependency in R5. Thus we can confirm that tables R1, R3, R4 and R5 are in Third Normal Form(3NF).

The question does not mention up to which level we need to normalize the table. In most cases, tables in 3NF are safe enough to prevent all kinds of database anomalies. Thus we leave them to 3NF.