Explore BrainMass
Share

Explore BrainMass

    Custom order entry system

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

    The system development team at Wilson Company is working on developing a new customer order entry system. In the process on designing the new system, the team has identified the following class and its attributes:

    - Inventory order
    - Order number,
    - Order Date
    - Street address
    - City
    - State
    - Zip
    - Customer type
    - Initials
    - District number
    - Region number
    ...1 to 22 occurrences of: Item Name, Quantity order, Item Unit, Quantity, Item out, Quantity Received

    State the rule that is applied to place a class in first normal form revise the above class diagram so that it is in INF.

    State the rule that is applied to place a class into second normal form. Create a class diagram for the Wilson
    Company using the class and attributes described to place it in.

    © BrainMass Inc. brainmass.com October 10, 2019, 6:09 am ad1c9bdddf
    https://brainmass.com/computer-science/databases/custom-order-entry-system-531777

    Solution Preview

    See the attached file.

    <b>Solution</b>

    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
    Order_Number
    Customer_id
    Customer_name
    Customer_address
    Customer_city
    Customer_state
    Customer_zip
    Customer_type

    Item_id
    Item_Name
    Item_qty
    Item_unit
    Item_out
    Item_received

    The following FDs (functional dependencies) hold on the above table:

    Order_Id -> Customer_id, Order_date, Order_number
    Item_Id, Order_Id -> Item_Name, Item_Qty, Item_Unit, Item_out, Item_received
    Customer_id -> Customer_Name, Customer_Address, Customer_City, Customer_State, Customer_zip
    Item_Id -> Item_name, Item_qty, Item_unit..,

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

    Solution Summary

    The solution discusses the custom order entry system.

    $2.19