1. Draw the Entity Relationship diagram for the following prompt:

Partners

a.

Consider a car dealership that sells new and used cars. You would like to be able to track the information about the cars that are on the lot, the sales of the cars, and your sales staff. Each sales person will have a first, middle and last name, an address, a hire date, a base salary, a phone number, and a commission rate. Each car will have a Vehicle Identification Number, a date that the dealership purchased it, a price the dealership purchased it for, a cost of repairs, a year, a make (Chevy, dodge, etc), a model, the number of miles on the car, one or more colors, a list price, and whether the car is new or used. We also need to track information about the sale of a car. We need to know what car was sold, which salesperson performed the sale, the sales price, the profit (sales price minus [cost of repairs and purchase]), the date of the sale, and the commission paid to the salesperson (commission rate * profit). We do not need to store customer information.

A Sale can only have one associated car, and one associated salesperson. If a customer wants to purchase multiple cars, that will be counted as multiple sales. Sales people will not work together on a sale.

In the real world, a car can technically be sold multiple times. A person could buy the car, then a few years later trade it back in to the dealership so it can be sold again. However, even though in the real world it is the same car, our database will treat them like different cars. While they have the same VIN, the date that the dealership purchased it will be different (among other things such as mileage). So the same VIN could appear twice in our Car table. If we overwrite the information about the car, we would corrupt the information about the previous time the car was sold. So from our database perspective its not the same car, even though it has the same VIN. So VIN cannot be the primary key.

A salespersons commission can change over time. If that happens, the commission they were paid for past sales is unaffected. The profit for a car sale only needs to be calculated once. The value will not change after that.