ER Diagram 05: Invoices

Problem Description

A store sells products. Customers buy products. Employees get paid for selling the products. A customer becomes a customer only after making at least one purchase. Every time a customer makes a purchase, an invoice is created. A purchase is defined as a number of products that are ordered together. All of the products that are ordered in a purchase appear on the same invoice together, with each product having its own detail line on the invoice. An invoice is written by an employee. Each employee can write many invoices, but each invoice is written by a single employee. Not all employees sell products. When an invoice is written, only a single customer's name is on the invoice. The store puts the customer's name and address on the top part of the invoice. The detail lines are grouped on the bottom part of the invoice. The store needs to be able to send bills to customers who purchase on credit, as well as to send advertisements from time to time. Employees are paid commissions based on their sales, so the store needs to keep track of the sales for each representative. Paychecks are sent to employees' homes, and the store needs to be able to call the employees on their cell phones. Reports are generated each month showing the sales performance (total sales) of each employee. Generic product information is stored for each product sold (quantity on hand, price, reorder level), but no specific information is stored for individual products. That is, if a customer purchases an Ace Broom Model 1000, all we care about is that we have one less Ace Broom Model 1000; there is no information that uniquely identifies each individual Ace Broom Model 1000 (they all have the same bar code and if we have 50 at the beginning of the day and 40 at the end of the day, it doesn't matter which 10 were sold, all that matters is that 10 were sold). Each product is purchased from a single vendor (e.g. the store only buys "Ace Brooms" from the Ace Broom Company, although the Ace Broom Company may sell many different models of Ace Brooms such as Model 1000, Model 2000, etc.). Whenever the inventory gets below a certain point (that point varies for each product), new product needs to be ordered from the vendor, so we need to know who sold us what. Inventory reports need to be generated on demand. The store needs to keep track of enough information about each vendor to be able to place an order and to pay bills.


Do the following:

         Identify each entity class.

         For each entity class, list all of its attributes (separate from your ER diagram).

         Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.

         Identify all non-key attributes for each entity class.

         Identify each foreign key for each entity class. Italicize foreign keys.

         For each entity class, identify relationships between it and other entity classes.

         Draw the ER diagram using MySQL Workbench.