This is an individual exercise, NOT a group assignment. Include only necessary entities, attribute identifier(s), and relationship cardinalities. Identifiers will be bold and underlined. Use curly, square, or round brackets around attributes where appropriate. Resolve any M:N relationship and show the final diagram. 1. Combine all the diagrams in HW2.1 into one diagram (23 points) 2. Add additional attributes to each entity based on the following more detailed business rules. (27 points) Store Entity: The system tracks the StoreID, city, state, and store manager ID, e.g., EmpID. Employee Entity Employee information consists of an EmpID, first name, middle initial, last name, hire date, monthly salary, job title, and their manager's ID. Customer Entity : Customer data tracked in the database include CustID, customer name, address (street, city, state, zip), area code, phone, sales rep ID, and credit limit. Order Entity: The database tracks the order no, order date, customer ID, and the calculated order total amount. Line Item Entity: In addition to the composite identifier (OrderNo and ProdID), the Line Item Entity stores the order quantity, ship date (for online order), and calculated item total amount. Product Entity: The product record consists of product ID, description, and unit price. Shipment Entity: Shipment record include ShipNo, ProdID, CustID, OrderNo, ship date, and shipment qty. Vendor Entity: A vendor record has vendor ID, vendor name, and one or multiple locations. Receiving Entity: receiving quantity for each product and receiving date are additional attributes to be captured in the database.

HW2.1

Include only necessary entities, attribute identifier(s), and relationship cardinalities. Identifiers will be bold and underlined. Resolve any M:N relationship via an associative entity and show the final diagram. In each associative entity, display all identifiers (primary keys) and foreign keys if different. Use SINGULAR for all your entity names for all assignments. 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. The company has embarked a project to redesign its antiquated database system according to the business rules listed below. Draw a separate ERD for each business rule (each question) 1. SS has different stores located in different cities, and each store has one or many employees. Store entity identifier is StoreID. Employee entity identifier is EmpID. (5 points) 2. Each employee reports to one manager except for the president. Each manager manages one or many employees. (5 points) 3. A customer can place one or multiple orders. CustID is the identifier of the Customer entity. The identifier of the Order entity is OrderNo. (5 points) 4. An order can consist of one or many line items and each line item corresponds to a product. Conversely, a product can be purchased through different orders. For example, OrderNo 100 contains product X, Y, and Z; product X is purchased by OrderNo 100, 105, and 109. The identifier for the Product entity is ProdID. OrderNo and ProdID together forms a composite identifier for the Line Item entity. Products without a sales order within 9 months will be discontinued. (10 points) 5. Each line item ordered online is shipped to the customer via one shipment. Multiple line items for the same customer can be bundled within the same shipment. As a result, ShipNo and ProdID is the composite identifier of the shipment entity. A customer can receive multiple shipments for different order lines. If the product is purchased in store, there will not be a shipment. (10 points) 6. A store can receive multiple products from multiple vendors. Likewise, a product can be sourced from multiple vendors and sold in multiple stores. A vendor can also bundle multiple products into one delivery. Therefore, receiving number and ProdID together forms the composite identifier of each receiving record. VendorID is the identifier of the Vendor entity. (15 points)