Share
Explore BrainMass

Custom order entry system

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.

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