• Lab Create a Car Dealership Database
• Add Primary and foreign keys in your dealership database
• Create an E-R diagram for a car dealership.
• The dealership sells both new and used cars, and it operates a service facility. Base your design on the following business rules: A salesperson may sell many cars, but each car is sold by only one salesperson.
• A customer may buy many cars, but each car is sold to only one customer.
• A salesperson writes a single invoice for each car he or she buys.
• A customer gets an invoice for each car he or she buys.
• A customer may come in just to have their car serviced, that is, one need not buy a car to be classified as a customer.
• When a customer takes one or more cars in for repair or service, one service ticket is written for each car.
• The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the cars serial number.
• A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
• A car that is serviced may or may not need parts. (For example, adjusting a carburetor or cleaning a fuel injector nozzle does not require the use of parts).
• Salespeople sell cars to customers thru sales invoices.
• Customers buy cars from Salespeople thru sales invoices.
• A salesperson writes one invoice to one customer per car sold.
• A customer gets one invoice per car bought.
• A customer initiates a car service thru a service ticket.
• A customer gets one service ticket per car serviced.
• The dealership uses the serial number of a car to look up service ticket history.
• Mechanics are associated with cars and the services that they perform on cars thru the service/mechanic table. The service/mechanic table associates information about the service done with the service ticket.
• Cars serviced often use parts. The Parts Used table associates the parts with the service ticket.