Online Marketplace Company DB Requirements
Prepared by Andrew Nottingham, Hanlin Ye, and Guel Kim
· 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
· Return average review of product
· Return all orders for a given week
· Return all products manufactured by a specific supplier
· Return all reviews written by a specific customer
· Return all reviews listed for a given product
· Return average price for each year for the last five years
· Return the sum of the salaries for a specific group of employees
· 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.)
· Update quantity available after a product is purchased
· Update product list to add new products
· Update product list to remove old products
· Update ship time during peak times, delays, etc.
· Update price at the request of the supplier or due to our own analysis
· Update password if a customer requests a password change
· Update average rating for product as new reviews are written by customers
· Update employee’s salary and position if they are promoted
· Update transaction history and order history as new orders are placed and filled
· Update product picture if supplier changes appearance of said product
· Update membership status accordingly if customer purchases status or wishes to discontinue status
ER Diagram (from ERDPlus)
/docProps/thumbnail.jpeg