Coopers Automotive is a company specializing in the buying, restoration, and selling of vintage automobilesprimarily Porsches. Cooper (yes, his first name is Gary) has worked on Porsches for his entire adult life, and he knows almost everything there is to know about them. His reputation for honesty and reasonable prices (for vintage cars) have won him a loyal following among vintage car enthusiasts. He enjoys many repeat customers.

Coopers Automotive tracks their automobile inventory by individual item, i.e., each car is tracked individually by an assigned tag number. They categorize their inventory 1) by model, e.g, 356, Spyder, or 912 for Porsche. They also categorize their inventory by condition, e.g, poor condition, average condition, good condition, very good condition, and excellent condition.

Coopers Automotive has a number of suppliers that search out cars for him, but he also will purchase cars directly from their owners if they are recommended to him by one of his established suppliers. Coopers often pays a small referral fee if he buys a particularly good car or cars, so they keep track of which suppliers recommended other suppliers. They do not specifically separate suppliers into different categories.

Coopers sets a standard purchase price for vintage autos that depends on both the model and its condition, e.g. a 356 in good condition would have a different price than a 912 in average condition. In those circumstances where the cars are not drivable, they also pay reasonable transportation-in costs for the car. Each supplier is paid in full soon after Coopers receives the car(s), verifies the title(s), and confirms the condition(s). Coopers pays any referral fee to the referring supplier at the same time. In some cases, Coopers purchases more than one car from the supplier in the same transaction. Coopers assigns a unique auto tag number to each car at purchase and places it in their warehouse to await restoration.

Although Gary Cooper is among the best auto restorer in the area, he is usually too busy to perform the restorations himself. Coopers typically uses local free-lance auto restorers on a per diem basis to restore the recently purchased cars. These artisans do not all have the same skill levels, but they are always able to improve the condition of the cars. The free-lance restorers select one or more newly purchased cars to work on. As they complete individual jobs, like tuning an engine, removing rust, etc., they fill out a restoration slip for their work, identifying what they did to each car. When they complete work for the day, one of Coopers employees inspects the work and approves the restoration slip by initialing next to each item listed that was completed satisfactorily. The restorer turns in the initialed restoration slip to the cashier who writes them a check for that days work. Cooper monitors the restoration progress and assigns a new condition category to an auto after each restoration. Cooper does not track all the categories the car might go through, just the current condition.

Coopers salespeople then sell these items to customers. There is always strong demand for the restored cars. Occasionally, Coopers will send out mailers to their current customers and vintage car clubs describing the cars that will be available for sale in the near future. The sales price for each car is based on both the condition category and the model. Although it doesnt happen often, a sale can include more than one car. Coopers customers typically pay by cash or check at the time of the sale. In some cases, however, the customer pays in advance, and Coopers delivers the car to them. If Coopers delivers a car to the customer, they record the specific delivery cost for that car.

Coopers employees sometimes specialize in just buying or selling. The firm, however, does not specifically separate employees into different categories. Employees are entered into the database before they handle any transactions. Cooper, himself, is considered an employee and should be included on your REA diagram.

REQUIRED: Using the data items below (don't add or subtract and no attribute should be listed twice unless it is a foreign key), complete the REA diagram with attributes. In a separate assignment, prepare a data dictionary to define the tables for a relational database identifying the primary and foreign keys appropriately.

1. YTD dollar sales for this model
2. auto model
3. condition category#
4. employee#
5. employee name
6. condition category description
7. customer#
8. sale date
9. sale delivery cost for this car
10. number of items on this sale
11. sale invoice#
12. individual accounts receivable
13. individual accounts payable
14. restorer name
15. YTD dollar sales for this condition category and this model
16. supplier#
17. supplier name
18. restorer hourly charge
19. % of restoration time spent on a particular automobile
20. restoration date
21. restoration slip#
22. purchase date
23. purchase invoice#
24. purchase delivery cost for this car
25. auto tag#
26. auto description
27. restorer reputation
28. bank account#
29. restoration fee
30. standard purchase price
31. standard sale price
32. check#
33. check date
34. check amount
35. total sale $ amount
36. cash receipt #
37. Receipt date
38. customer payment type
39. customer check number
40. current balance for this bank account
41. referral fee for this purchase
42. total referral fees for this supplier

(0.1) (0.1) Cuba (1) Cash Disbursement (1,1) (0.1) Supplier (1,1) (0.N) (1.1) Autos (0,N) (1.1) NON (0.N) Purchase Employee (0.N) (ON) Restoration Restorer (1.1) (1.1) Condition Models (ON) ( N) (N) (1.N) Sale Customer (N) SCH (1.1) CR.. > Cash Receipt (0,N) (0.1) (0.1) Cuba (1) Cash Disbursement (1,1) (0.1) Supplier (1,1) (0.N) (1.1) Autos (0,N) (1.1) NON (0.N) Purchase Employee (0.N) (ON) Restoration Restorer (1.1) (1.1) Condition Models (ON) ( N) (N) (1.N) Sale Customer (N) SCH (1.1) CR.. > Cash Receipt (0,N)