The purpose of this assignment is to define a high-level standard Sales, Purchasing, and Inventory ERP system in an ER diagram.

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 -

• 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.

Every product in SS has been ordered from the vendor at least once. However, not all products have been sold to the customer and will eventually be removed from stores.

• 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 OnHand Qty, Safety Stock (SS), and Reorder Point for each SKUNo in each warehouse. When a product is 'out of stock', the OnHand Qty is equal to 0.

Sales Cycle -

• Customer: The database records customers who have placed at least one 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 (i.e., multiple SKUNos) each with a different order quantity in the same order. Conversely, the same product can be purchased in different sales orders. (M:M relationship)
• 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, 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, WHID, ShipDate, one or many SKUNos and the ShipQty for each SKUNo.

Purchasing Cycle - the purchase cycle and the sales cycle share similar business processes in a reverse pattern.

• Vendor: In this database, we will only record the VendorID, name, and email address.
• Purchase Order: When a product inventory reaches its reorder point, a purchase order (PO) is generated to replenish the inventory. Similar to the Sales cycle, one PO contains 1 or more SKU's, and one SKU can appear in more than one PO, i.e., M:M relationship. A PO consists of a unique PONo, vendor ID, one or many SKUNos, order quantity for each SKU, order date, and calculated total order amount.
• Receiving: Each ReceiptNo is generated for the delivery of one PO into one warehouse. Each warehouse receives multiple deliveries for multiple PO's on a daily basis. The receiving entity contains unique ReceiptNo, PONo, VendorID, receiving WHID, receiving date, SKUNo (one or multiple) and receiving quantity for each SKU.

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 entity 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 high-volume orders on a quarterly basis. Each premium customer only works with one sales rep. Note that not all customers are premium and not all employees are in Sales or Purchasing.

Hints and Requirements:

1. There are 12 entities in this ERD. The business rules above describe 10 entities. You will need to add 2 more associative entities to resolve M:M relationships in this ERD.
2. In addition to identifiers, you will need to use notations for composite, derived, and multi-values attributes where applicable.
3. Don't make up entities and attributes not mentioned in this assignment.
4. Try to group all entities for the Sales cycle on one side, all entities for the Purchase cycle on the other side, and all inventory and employee entities in the middle.
5. Use SINGULAR nouns and BOLD all your entity names. Bold and underline your identifiers. See an example below.

6. 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. Alternatively, 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.

Grading:

Entity identifiers, attributes, notations make up 80 points. Relationship cardinalities make up 40 points.

See due date on Canvas

• Draw your ERDs with Drawio using the Crows Foot model
• Submit one ERD based on the business rules listed above in one .pdf file.
• Save your file name as HW3-Student Name.pdf
• Submissions must be posted to Canvas to be graded.