Super Sports (SS) is a sporting goods company head-quartered in Tempe, Arizona. Defined by its excellent services and unique merchandise, the company has earned the reputation as one of the best sporting goods stores in terms of customer satisfaction. Due to its continuous growth, the company has embarked a project to redesign its antiquated database system according to the business rules listed below. Sales Cycle - - Product: SS carries a large variety of sporting goods in different product categories, such as tennis, swimming, golfing, and hiking. Each product has a unique Stock Keeping Unit (SKU) number, description, category, and unit price. Each category has a CatID, CatDesc, PromoCode, and AccountCode. - Customer: The database records customers who have placed at least one sales order with the company to show CustID, customer name, phone, email address, and customer address in terms of street, city, state, and country. - Sales Order: Each sales order has a sales order number (SONo) to uniquely define the customer ID, order date, and calculated total order amount. It is possible that a customer can purchase multiple products each with a different order quantity in the same order. Conversely, the same product can be purchased in different sales orders (i.e., M:M relationship for sales order and product) - Shipment: Customers can purchase products in the store or online. For online orders, SS ships all products within the same order in one shipment. In other words, for online orders, each SONo is associated with one unique ShipNo. Please note that store purchased orders don't have a ShipNo. Each shipment record contains ShipNo, SONo, CustID, ShipDate. Purchasing Cycle - the purchase cycle and the sales cycle share similar business processes in a reverse pattern. - Vendor: In this database, we will record the VendoriD, name, and email address. - Purchase Order: When a product inventory reaches its reorder point, a purchase order is generated to purchase from vendors. Similar to the Sales cycle, one purchase order contains one or more products with different order qunatity, and one product can appear in more than one purchase order (i.e., M:M relationship for purchase order and product.) A purchase order consists of a unique PONo, vendor ID, order date, and calculated total order amount. Support Cycle - - Employee: The database will record all SS employees with their unique employee ID, name (First, Last), hire date, department, and manager ID. - Each employee reports to one manager except for the CEO. An employee can manage zero or many employees. Use a unary relationship to describe this business rule. - Each purchasing employee is assigned to work with multiple vendors while each vendor must work with only one purchasing employee. - Some sales employees are assigned to work with premium customers who have highvolume orders on a quarterly basis. Each premium customer only works with one sales employee. Note that not all customers are premium and not all employees are in sales or purchasing (i.e., there are employees who don't work customers or vendors). Hints and Requirements: 1. There are 9 entities in this ERD. The business rules above describe 7 entities. You will need to add 2 more associative entities to resolve M:M relationships in this ERD. 2. In addition to primary keys, you will need to use notations for composite, derived, and multivalues attributes where applicable. 3. Don't make up entities and attributes not mentioned in this assignment. 4. Use SINGULAR nouns for entity names. Bold and underline your primary keys. See an example below. 5. Drawing tips: - Reference Drawio Tutorial document part 1 on Canvas for the ERD diagram. - Draw the diagram on a piece of paper before transferring the drawing to Drawio. - Arrange all entities in Drawio before you connect them with relationships. Practice Exercises ERD exercises in a smaller scale are available on Canvas to illustrate different degrees of entities and relationship cardinalities
