Redesigning Super Sports (SS) Database System
Overview of Super Sports (SS)
Super Sports (SS) is a sporting goods company headquartered in Tempe, Arizona. Known for its exceptional services and unique merchandise, SS has established itself as one of the top sporting goods stores in terms of customer satisfaction. To accommodate its growth, the company has initiated a project to revamp its outdated database system.
Sales Cycle
Product: SS offers a wide range of sporting goods across various categories including tennis, swimming, golfing, and hiking. Each product is identified by a unique Stock Keeping Unit (SKU) number and includes a description, category, and unit price. Each category is represented by a CatID, CatDesc, PromoCode, and AccountCode.
Customer: The database records customers who have placed at least one sales order with SS. Customer information includes CustID, customer name, phone number, email address, and address details (street, city, state, country).
Sales Order: Each sales order is assigned a unique Sales Order Number (SONo) that captures customer ID, order date, and the total order amount. Customers can purchase multiple products with varying quantities in a single order, while the same product can be part of multiple sales orders (M:M relationship).
Shipment: Customers can purchase products in-store or online. Online orders are shipped together in a single shipment identified by a unique ShipNo. Store-purchased orders do not have a ShipNo. Each shipment record includes ShipNo, SONo, CustID, and ShipDate.
Purchasing Cycle
Vendor: The database includes records of vendors with VendorID, name, and email address.
Purchase Order: When product inventory reaches its reorder point, a purchase order is generated to procure goods from vendors. Similar to the Sales cycle, a purchase order can contain multiple products with different order quantities, and one product can be part of multiple purchase orders (M:M relationship). A purchase order comprises a unique Purchase Order Number (PONo), vendor ID, order date, and total order amount.
Support Cycle
Employee: All SS employees are recorded in the database with unique EmployeeID, name (First, Last), hire date, department, and manager ID. Each employee reports to one manager except for the CEO. Employees may manage zero or many employees, and each purchasing employee collaborates with multiple vendors. Notably, some sales employees are assigned to elite customers with high-volume quarterly orders, with each premium customer exclusively working with one sales employee.
Exercise Question:
Based on the information provided, how can you design an Entity Relationship Diagram (ERD) for Super Sports (SS) to accurately represent its data requirements and relationships?
Answer:
The student is required to create an ERD for Super Sports (SS) based on the given business rules. The ERD should encompass entities such as Product, Customer, Sales Order, Shipment, Vendor, Purchase Order, and Employee. To address the M:M relationships between entities, the student must introduce two associative entities.