Explore BrainMass

Explore BrainMass

    Improving Table Designs Using Normalization.

    Not what you're looking for? Search our solutions OR ask your own Custom question.

    This content was COPIED 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 March 5, 2021, 12:20 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.49

    ADVERTISEMENT