Objectives
Produce an ERD in 3NF from source documents and client input.
Demonstrate initial table, first, second, and third normal forms by showing the database tables as they exist in each of the three normal forms
Merge ERDs from different views to create one final merged ERD
Requirements
Given the source documents and company description create a database design demonstrating the concept of normalization, showing all the attributes as they exist in the initial table, 1NF, 2NF, and 3NF. You will do this for each of the three views and create an ERD for each view in 3NF, as well as the final merged ERD.
The Company
Stuff B Gone is a company that sells customers items for them. In return for this service they collect a fee for each item they sell for the customer. Currently Stuff B Gone sells customers items through physical stores, Facebook groups/Marketplace, and Kijiji.
As an incentive to thank repeat customers, they have a reward program where customers can get discounts on their consignments.
In order to provide the best experience for the customers, all staff are able to take training where they can improve their skills.
Table Details
Consignment Agreement View:
The category cost may change over time, so Stuff B Gone wants to record the category cost for each consignment (historical information) as well as record the current category cost.
Line # identifies each item on a particular consignment.
Start Price and Low Price are the starting selling price for the item and Low Price is the lowest price that the customer is willing to sell the item for.
Only one staff member works on a consignment.
Staff Training View:
Staff can take many different training courses.
Many staff can take the same training course.
The same training can be offered many different times. Each time it is offered it is given a new Training ID.
Each Staff is a certain type. These could include Sales, Promotion, etc…
Customer Rewards View:
Merged ERD:
Create a merged ERD of your final database design for Stuff B Gone.
Lab Submission will include the following:
An electronic copy of your Lab (not compressed) will be submitted to Moodle
A 3NF solution for each view including ALL the attributes and entities as they appear in the initial table, 1NF, 2NF, and 3NF.
An ERD for each 3NF solution of each view and an ERD of the merged solution (4 ERDs total) created with an ERD modeling tool approved by your instructor (Excel, Visio, Word, Lucidchart, etc.).
Please upload to your instructor with a short discussion about the lab including:
o What you liked/disliked about the lab
o How long it took you to complete the lab
o How prepared you felt you were for the lab
o Recommendations for future labs (if any)
Any additional requirements as specified by your instructor.
Other Considerations
Do not make assumptions. If you have questions about the company, ask your instructor (client). This is not a group project. Working with another student on lab material may result in a grade of 0 for this lab. Up to 3 Marks may be deducted for incomplete lab submission requirements or poor client communication (check the provided documentation for the answer before asking the client).
Reference Solution file
ESP Normalization Exercise Solution
Customer Details View
Initial Table
CustomerNumber, Name, Address, City, Province, PostaleCode, HomePhone
1NF
CustomerNumber, FirstName, LastName, Address, City, Province, PostaleCode, HomePhone
2NF
CustomerNumber, FirstName, LastName, Address, City, Province, PostaleCode, HomePhone
3NF
CustomerNumber, FirstName, LastName, Address, City, Province, PostaleCode, HomePhone
View ERD
Customer Orders View
Initial Table
CustomerNumber, CustomerName, Address, Phone, Date, OrderNumber, (ItemNumber,
Description,
Quantity, SellPrice, CurrentPrice, Amount), Subtotal , GST, Total
1NF
CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone,
Date, OrderNumber, Subtotal , GST, Total
OrderNumber, ItemNumber, Description, Quantity, SellPrice, CurrentPrice, Amount
2NF
CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone,
Date, OrderNumber, Subtotal , GST, Total
OrderNumber, ItemNumber, Quantity, SellPrice, Amount
ItemNumber, Description, CurrentPrice
3NF
CustomerNumber, Date, OrderNumber, Subtotal , GST, Total
CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone
OrderNumber, ItemNumber, Quantity, SellPrice, Amount
ItemNumber, Description, CurrentPrice
Merge ERD(Customers Order View with Current ERD)
In the Customer Orders View we discovered three new entities and a number of attributes that relate to what we discovered from the Customer Details view. If any new attributes about a customer where discovered they would be added to the Customer entity.
Customer Payments Against Orders
Initial Table
OrderNumber, OrderDate, OrderTotal, CustomerName, CustomerNumber, (PaymentDate,
PaymentAmount, PaymentNumber, BalanceOwing, PaymentType, DepositBatchNumber)
1NF
OrderNumber, OrderDate, OrderTotal, CustomerName, CustomerNumber
OrderNumber, PaymentDate, PaymentAmount, PaymentNumber, BalanceOwing, PaymentType,
DepositBatchNumber
2NF
OrderNumber, OrderDate, OrderTotal, CustomerName, CustomerNumber
OrderNumber, PaymentDate, PaymentAmount, PaymentNumber, BalanceOwing, PaymentType,
DepositBatchNumber
3NF
OrderNumber, OrderDate, OrderTotal, CustomerNumber
CustomerNumber, CustomerFirstName, CustomerLastName
OrderNumber, PaymentDate, PaymentAmount, PaymentNumber, BalanceOwing, PaymentType,
DepositBatchNumber
Merge Customer Payments Against Orders View with current ERD
In the Customer Payments Against Orders View we discovered a new OrderPayment entity.
No new attributes where discovered for the existing entities.
Inventory Control
Initial Table
ItemNumber, ItemDescription, CurrentSalePrice, QOH, ReorderLvl (OrderDate, SupplierNumber,
PONumber, Quantity, Cost)
1NF
ItemNumber, ItemDescription, CurrentSalePrice, QOH, ReorderLvl
OrderDate, SupplierNumber, PONumber, Quantity, Cost, ItemNumber
2NF
ItemNumber, ItemDescription, CurrentSalePrice, QOH, ReorderLvl
PONumber, Quantity, Cost, ItemNumber
PONumber, OrderDate, SupplierNumber
3NF
ItemNumber, ItemDescription, CurrentSalePrice, QOH, ReorderLvl
PONumber, Quantity, Cost, ItemNumber
PONumber, OrderDate, SupplierNumber
Merge Inventory Control View with current ERD
In the Inventory Control View we discovered 2 new entities (PurchaseOrder and PurchaseOrderItem). We also see that they merge to the existing design through the Item entity. No new attributes where discovered for the existing entities, however, the different views contained the same attribute (CurrentPrice and CurrentSalePrice) under different names. This can happen quite often since the data sometimes has a different context in different views and therefore may be called something different. You would just need to choose one name for the attribute. In this case I chose CurrentPrice.
Purchase Orders
Initial Table
PurchaseOrderNumber, SupplierNumber, SupplierName, Address, City, Phone, Date, (Item#,
SuppliersItemNumber, SuppliersDescription, Qty, Cost, Amount), SubTotal, GST, Total
1NF
PurchaseOrderNumber, SupplierNumber, SupplierName, Address, City, Province, PostalCode, Phone, Date, SubTotal, GST, Total
PurchasOrderNumber, Item#, SuppliersItemNumber, SuppliersDescription, Qty, Cost, Amount
2NF
PurchaseOrderNumber, SupplierNumber, SupplierName, Address, City,Province, PostalCode, Phone, Date, SubTotal, GST, Total
PurchasOrderNumber, Item#, SuppliersItemNumber, SuppliersDescription, Qty, Cost, Amount
3NF
PurchaseOrderNumber, SupplierNumber, Date , SubTotal, GST, Total
SupplierNumber, SupplierName, Address, City, Province, PostalCode, Phone,
PurchasOrderNumber, Item#, SuppliersItemNumber, SuppliersDescription, Qty, Cost, Amount
Merge Purchase Order View with current ERD
In the Purchase Order View we discovered a new Supplier entity that merge to the existing design through the Purchase Order Entity. As well, a number of new attributes for PurchaseOrder and PurchaseOrderItem were discovered.
Purchase Order Payments
Initial Table
Date, ChequeNumber, ChequeAmount, SupplierNumber, SupplierName, (PurchaseOrderNumber,
PurchaseOrderDate)
1NF
Date, ChequeNumber, ChequeAmount, SupplierNumber, SupplierName
ChequeNumber, PurchaseOrderNumber, PurchaseOrderDate
2NF
Date, ChequeNumber, ChequeAmount, SupplierNumber, SupplierName
ChequeNumber, PurchaseOrderNumber, PurchaseOrderDate
3NF
Date, ChequeNumber, ChequeAmount, SupplierNumber
SupplierNumber, SupplierName
ChequeNumber, PurchaseOrderNumber, PurchaseOrderDate
Merge Purchase Order Payments View with current ERD
Thanks for Reading!
If you looling any other database desining and implementation related help then send your request or requirement details at:
And get instant help with an affordable price.
Comments