Car Dealership:  This database is for a typical car dealership.  For each dealership, we will track the name of the dealership, the address, and multiple phone numbers.  We will keep track of all the cars on the lot.  For each car we will track a car ID, the model (L, S, E), the price, a status (available, sold), and a list of options the car has (Fog Lights, Moon Roof, Satellite Radio).  A car may have more than one option (or may have none).  A car belongs to just one dealership. When the car is sold, we will keep track of the date, and the salesman that sold the car.   Cars are sold by a single salesman.  For each salesman, we will keep track of their name, address, type (full, part, temp) and total amount sold (total prices of all cars sold by that salesman).  A salesman works for a single dealership.

Requirements:

1. (Using a procedure) The system should be able to add new dealerships making sure that the name is not a duplicate. Multiple phone numbers can be added manually (without a procedure).   A message should be returned with the success or failure of the action.
2. (Using a procedure) The system should be able to add a salesman with a valid type to the database without duplicates. If there is a duplicate, a message should be shown. Total amount sold is initially zero. A message should be returned with the success or failure of the action.
3. (Using a procedure) The system should be able to add valid cars to the database making sure they have a valid model.  A message should be returned with the success or failure of the action.  Status should be initially available.  Note:  Options may be added manually (without a procedure if necessary).
4. (Using a procedure and trigger) Given a car ID and a salesman ID, the system should be able to record a car’s sale, adding the price of the car to the correct salesman’s total (via trigger) and changing the car’s status to “sold”.
5. (Using a procedure) Given a state, the system should be able to list all dealerships in that state.
6. (Using a procedure) Given a valid option, the system should be able to list all of the cars on the lot that have that option.
7. (Using a procedure) Given a valid car id and a price, update the car to the new price.  The price of the car cannot be zero.
8. [EXTRA CREDIT]: Write a trigger to log the information when the price of a car is updated.  When the price of a car is changed, capture in a log, the old and new prices, the person who updated the price, and the date.

NOTE:  You must use at least one check constraint.  You should use primary and foreign keys as appropriate.  You must have at least one trigger that maintains total sales amount for the salesman and another trigger for validation.  Reference data may be added without procedures.

Important:  Each requirement should translate into at least one procedure depending on how you decide to implement the requirement.  #4 will require at least one procedure and one trigger.