Question

Determine the functional dependencies that exist in the following table and then convert the following table to an equivalent collection of tables that are in third normal form. Please show each step from first to third normal form.

ITEM (TIEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOURSE, PRICE, (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME, NUM_ORDERED, QUOTED_PRICE))

Answer 1

Dear Student ,

As per requirement submitted above kindly find below solution.

Consider relation given

ITEM (TIEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOURSE, PRICE, (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME, NUM_ORDERED, QUOTED_PRICE))

First Normal Form (1NF) :

• Given relation is in the first normal form (1NF) because all columns in the relation is atomic in nature.
• No column in the table contains multiple values and no duplicate column exists.

Second Normal Form (2NF ):

• Second normal form says table needs to be in the first normal form.
• All the non key columns in the 2NF needs to depends on primary key column.
• This means partial dependency is not allowed in 2NF.

Above relation needs to normalize into 2NF remove partial dependency.Here need to identify new tables like

• Customer :This table stores customer details like Customer number and name
• CATEGORY :This table store category details
• Item :This table stores item details like item number , description , quantity on hand price etc.

Below are tables in 2NF.

1.Table Name :Customer

Schema :Customer (Customer_Num,Customer_Name)

FD :Customer_Num ==> Customer_Name

2.Table Name :Category

NOTE :This table is created to store category details which is additional table with two column category number and name.

Schema :Category(Category_Num,Category_Name)

FD :Category_Num ==> Category_Name

3.Table Name :Item

Schema :Item(ITEM_NUM, DESCRIPTION, ON_HAND,STOREHOURSE, PRICE)

FD :ITEM_NUM==>DESCRIPTION, ON_HAND,STOREHOURSE, PRICE

4.Table Name :Orders

Schema :Orders (ORDER_NUM, ORDER_DATE, NUM_ORDERED, QUOTED_PRICE)

Third Normal Form (3NF) :

• Third Normal Form (3NF) says table should be in the second normal form (2NF).
• In 3NF transitive dependency is not allowed.
• Transitive dependency means non key column in the table should depends on non key column in the table.

Above table needs to normalize into 3NF to remove transitive dependency.Below are tables in 3NF.

1.Table Name :Customer

Schema :Customer (Customer_Num,Customer_Name)

FD :Customer_Num ==> Customer_Name

2.Table Name :Category

NOTE :This table is created to store category details which is additional table with two column category number and name.

Schema :Category(Category_Num,Category_Name)

FD :Category_Num ==> Category_Name

3.Table Name :Item

Schema :Item(ITEM_NUM, DESCRIPTION, ON_HAND,STOREHOURSE, PRICE,Category_Num )

FD :ITEM_NUM,Category_Num ==>DESCRIPTION, ON_HAND,STOREHOURSE, PRICE

4.Table Name :Orders

Schema :Orders (ORDER_NUM, ORDER_DATE, Customer_Num)

FD:ORDER_NUM,Customer_Num==>ORDER_DATE

5.Table Name :OrderDetails

Schema :OrderDetails (Order_Num,Item_Num,NUM_ORDERED, QUOTED_PRICE)

FD :Order_Num,Item_Num==>NUM_ORDERED, QUOTED_PRICE

NOTE :PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.