Improving Table Designs Using Normalization.
Consider a typical sales invoice that would include the following information. Design a single table to hold all of the information required to store an invoice including this information. Next, apply normalization to reduce this table to third normal form. Prepare a Word document showing the original and final tables. Include a short summary (2-3 paragraphs) of the problems with the original single table design and how normalization improved the design.
Orders:
Order_id
Order_date
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
Item_id
Item_description
Item_qty
Item_price
Item_total_price
Order_total_price
Solution Preview
Let us start with a single table with all columns in it. Let's remember that, in all tables candidate keys (also known as Primary Keys) are in BOLD
Let's call the below table OrderInfo table:
Table OrderInfo:
Order_id
Order_date
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
Item_id
Item_description
Item_qty
Item_price
Item_total_price
Order_total_price
The following FDs (functional dependencies) hold on the above table:
Order_Id -> Customer_id, Order_date, Order_total_price
Item_Id, Order_Id -> Item_Qty, Item_total_price, Item_description, Item_Price
Customer_id -> Customer_Name, Customer_Address, Customer_City, Customer_State
Item_Id -> Item_description, Item_price
Primary key of the above table is a composite candidate key Order_id and Item_id
Clearly, this table is in 1 NF, but it is not in 2NF, so obviously not in 3 NF.
The table is not in 2 NF because we have FDs with part of candidate key Order_id and Item_id and also Customer_id
So, we have to separate the fields according to the FDs and create more tables.
Hence, at first we take the FD
Customer_id -> Customer_Name, Customer_Address, Customer_City, Customer_State
Then, we need to put the below fields in Customer table
Customer_id
Customer_name
Customer_address
Customer_city
Customer_state
In a Customer ...