Question

Consider the following relation and convert to the normal form indicated. Make sure your Primary Key and its attribute(s) is/are underlined for full credit. Also indicate foreign keys using (FK) if any.

0NF:

ORDER[order_num, date, SSN, cust_name, phone, email, (SKU, item_name, price)]

Notes:An order has only one customer, but a customer can place many orders. Each order can have multiple items.  

1NF:

2NF:

3NF:

Answer 1

Answer : Relations in 3NF :

ORDER[order_num, date, SSN] with primary key order_num and foreign key SSN
CUSTOMER[SSN, cust_name, phone, email] with primary key SSN
ITEM[SKU, item_name, price] with primary key SKU
ORDER_ITEMS[SKU, order_num] with primary key {SKU, order_num} amd foreign key order_num

Explanation :

Converting to 1NF : The ORDER relation is in 0NF. Each order has one customer but can have multiple items. So, the relation is not in 1NF. It can be converted to 1NF using decomposition as shown below :

ORDER[order_num, date, SSN, cust_name, phone, email] with primary key order_num
ORDER_ITEMS[SKU, item_name, price, order_num] with primary key {SKU, order_num} as the item and order_num is unique for each order

The relation above has following functional dependencies :
ORDER :
order_num -> date, SSN
SSN -> cust_name, phone, email
ORDER_ITEMS :
SKU -> item_name, price

Converting 2NF : The relations in 2NF can not have partial functional dependencies. In above relations, there is a partial dependency : SKU -> item_name, price. This functional dependency can be removed using decomposition as shown below :
ITEM[SKU, item_name, price] with primary key SKU
ORDER_ITEMS[SKU, order_num] with primary key {SKU, order_num} amd foreign key order_num
ORDER[order_num, date, SSN, cust_name, phone, email] with primary key order_num
Above relations are in 2NF.

Converting to 3NF : The relation is in 3NF if it is in 2NF and there are no transitive dependencies. Above tables are in 2NF. There is a transitive dependency in relation ORDER : SSN -> cust_name, phone, email. It can be removed using decomposition :
ORDER[order_num, date, SSN] with primary key order_num and foreign key SSN
CUSTOMER[SSN, cust_name, phone, email] with primary key SSN
ITEM[SKU, item_name, price] with primary key SKU
ORDER_ITEMS[SKU, order_num] with primary key {SKU, order_num} amd foreign key order_num

Above relations are in 3NF.