Question

Consider a table Inventory with the following functional dependencies: cus_code, prod_id  cus_name,cus_address,prod_name,unit_cost, quantity cus_code  cus_name, cus_address prod_id  prod_name,unit_cost. a) In what normal form is relation R? Explain why? b) Is it possible to decompose R into a number of relations in order to achieve a higher normal form? Show the tables that result from the decomposition.

Answer 1

Hi

Below is the answer-

The above table structure is in first normal form as the column of a table is not holding multiple values.

This table structure is not in second normal form because the non prime attributes should be fully dependent on the proper subset of candidate key. We can see prod_name given above can be determined by prod_id itself and it has no dependency on cus_code.

Hence, in order to convert this structure into second normal form, we need to break the cust table into 2 sub tables namely cust and prod as below-

a) cus_code --> {cus_name,cus_address , cus_name, cus_address }

b) prod_id -->{prod_name,unit_cost, quantity}

Regards,

Vinay