Explore BrainMass
Share

Improving Table Designs Using Normalization.

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

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

© BrainMass Inc. brainmass.com October 25, 2018, 7:22 am ad1c9bdddf
https://brainmass.com/business/management-information-systems/improving-table-designs-using-normalization-500907

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 ...

$2.19
See Also This Related BrainMass Solution

Normalisation of Database Design

Normalize the tables you have designed in module 2. Explain how you redesign your tables to satisfy the requirements of 1NF, 2NF and 3NF. When necessary, it is perfectly okay to create another table. If you believe your tables designed in module 2 already met the normalization requirement, explain why.
Modify the database you created in Module 3: drop the tables that you have redesigned for normalization, and keep those that are intact.
Create new tables as required in your new design, using SQL in DBMS.
Include the SQL statements and screenshots of your new tables in the paper.

SLP assignment expectations
Create tables that met normalization standards.
Use SQL to create and drop tables.

View Full Posting Details