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
- Customer type
- 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.
See the attached file.
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:
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
The solution discusses the custom order entry system.