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. Product/Inventory CYcle - - Producti SS carries a large variety of sporting goods in different product categories, such as tennis, swimming, golfing, and hiking. Each product has a unique Product ID (ProdID), description, category, and unit price. Not all products have been sold to the customer and a zero-sales product will eventually be removed from stores after 1 year. - Warehouse: SS owns different warehouse stores located in different cities and states. Each store has a warehouse ID (WHID). Each product must be stored in at least one warehouse. Likewise, each warehouse must store at least one product. - Inventory: The company tracks inventory quantities in each warehouse including OnHand Qty, Safety Stock (SS), and Reorder Point for each ProdID. Though these quantities are derived from an inventory planning model, define them as simple attributes in this ERD. Each warehouse tracks the inventory of multiple products and each product can reside in multiple warehouses. (M: M relationship) Sales Cycle - - Customer: The database records customers who have placed at least one order with the company to show CustID, first name, last name, phone, email address, and customer address in terms of street, city, state, and country. - Sales Order: A customer can purchase multiple products in the same order, each with a different order quantity. Conversely, the same product can be purchased by different customers. (M: M relationship]. Each sales order has a sales order number (SONo), CustID, ProdID, order quantity, unit price, order date, and calculated total order line amount. SONo is used to identify each unique product in an order, e.g., 10001-1 and 10001-2 are two different products in order 10001. - Shipment: Customers can purchase products in store or online. Each online order has a shipment delivery with a unique ShipNo. Each shipment record contains ShipNo, SONo, ProdID, CustID, WHID, ShipDate, and ShipQty. Examples of the Ship No are 50350-1 and 50350-2 for the delivery of two relationship]. Each sales order has a sales order number (SONo), CustID, ProdID, order quantity, unit price, order date, and calculated total order line amount. SONo is used to identify each unique product in an order, e.g., 10001-1 and 10001-2 are two different products in order 10001. - Shipmenti Customers can purchase products in store or online. Each online order has a shipment delivery with a unique ShipNo. Each shipment record contains ShipNo, SONo, ProdID, CustID, WHID, ShipDate, and ShipQty. Examples of the ShipNo are 50350-1 and 50350-2 for the delivery of two products in the same order. Note that a store purchased order doesn't require a shipment. Hints and Requirements: 1. Use the provided ERD template to complete this assignment by filling in the entity names, identifiers, attributes, and relationship cardinalities. 2. You will need to use notations for composite and derived attributes where applicable. 3. There are 6 entities in this ERD including 2 associative entities. Use a composite identifier for the inventory associative entity. NOTE the difference between a composite 'identifier' and a composite 'attribute'. - Composite identifier: the identifier has more than one attribute, e.g., studentID and courselD together forms a composite identifier to determine a student's grade of a course. - Composite attribute: the attribute can be decomposed into multiple simple attributes, e.g., Address can be decomposed into street, city, state, and country. 4. Don't make up entities and attributes not mentioned in this assignment. Grading: Entity identifiers, attributes, and notations ( 60 points). Relationship cardinalities ( 20 points)
