Question

Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and order data.

Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice) .

Normalize the above relation to 3 rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.

Answer 1

Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice)

Here,

• SupplierNum determines SupplierName and Supp_Phone
• ProductNum determines Description and Product_type
• SupplierNum, ProductNum together determines SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type and QuotedPrice

=> SupplierNum, ProductNum is the primary key of Orders table.

Since SupplierNum and ProductNum individually determines some attributes, the orders table is not in 2NF.

To make it into 2NF, we split the table

• Suppliers (SupplierNum, SupplierName, Supp_Phone)
• Products (ProductNum, Description, Product_type)
• Orders (SupplierNum, ProductNum, QuotedPrice)

Now, we need to check the above tables are in 3NF (there should not be any transitive dependencies)

The above tables don't have any transitive dependencies, all are in 3NF.

• Suppliers (SupplierNum, SupplierName, Supp_Phone) SupplierNum --> SupplierName, Supp_Phone
• Products (ProductNum, Description, Product_type) ProductNum --> Description, Product_type
• Orders (SupplierNum, ProductNum, QuotedPrice)   SupplierNum, ProductNum --> QuotedPrice

Dependencies are also preserved.