Online Marketplace Company DB Requirements
• Each product has a unique SKU, price, name, condition, ship time, quantity, pictures, average rating
• Each customer has name, unique email, password, card information, order history, review history, Premium membership status
• Each review has a rating (1 to 5), date, customer, text
• Each supplier has a product list
• Each employee has a name, employee ID, unique company email, banking information,
position, salary
• Keep track of price and pricing history for each product
• Keep track of the average rating for each product and the total number of ratings
• Keep track of the quantity available of the product
• Keep track of the transaction history of the customer
• Keep track of the date the product is ordered by the customer
• One and only one customer writes one or more reviews
• Many employees assist many suppliers
• One and only one employee assists one and only one customer
• One and only one customer purchases one or more products
• One and only one supplier supplies one or more products
• The database is being designed for an online marketplace company.
• The database is will be used by all three human entities: suppliers, customers, and
employees. However, the views of the database that these three entities see vary
greatly. The database manipulation will mostly be done by employees of the company.
• Some queries expected
o Return average review of product
o Return all orders for a given week
o Return all products manufactured by a specific supplier
o Return all reviews written by a specific customer
o Return all reviews listed for a given product
o Return average price for each year for the last five years
o Return the sum of the salaries for a specific group of employees
o Return quantity ordered of a specific product for a specific date range
• Some updates expected (performed by employees, more than like database admins, IT professionals, etc.)
o Update quantity available after a product is purchased o Update product list to add new products
o Update product list to remove old products
o Update ship time during peak times, delays, etc.
o Update price at the request of the supplier or due to our own analysis
o Update password if a customer requests a password change
o Update average rating for product as new reviews are written by customers o Update employee’s salary and position if they are promoted
o Update transaction history and order history as new orders are placed and filled o Update product picture if supplier changes appearance of said product
o Update membership status accordingly if customer purchases status or wishes to
discontinue status
ER Diagram (from ERDPlus)