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

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.

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

Similarly,

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:

 

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.