top of page

Database Designing And Implementation Help | Sample Paper

Objectives

  1. Produce an ERD in 3NF from source documents and client input.

  2. Demonstrate initial table, first, second, and third normal forms by showing the database tables as they exist in each of the three normal forms

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


bottom of page