Database Requirements:
• Customer Data: The name of every customer who ever purchased goods or services from the dealership, or those have visited the dealership. This includes name, address, phone #, email address, and type such as purchase, service, or visit.
• Employee Data: The information of every employees at the dealership, including name, address, phone#, email address, etc.
• Vehicle Data: Pertinent information on every vehicle the dealership ever worked on. This includes make, model, year, vehicle identification number, price.
• Sales Data: Data on every vehicle sold through the dealership. In addition to the vehicle data above, the date sold, the customer it was sold to, the temporary tag number, any customization work done to the vehicle, a link to the billing data for that sale, ...
• Service Data: Data covering each service visit at the dealership. The vehicle and customer involved, the mechanic(s) who worked on the vehicle, the vehicle mileage when it arrived at the dealership and when it left the dealership, preliminary estimates, what was actually done to the car, and final billing information, the date the vehicle entered the dealership, the time estimate for the work on the vehicle, the date the work was completed, the date the customer picked up the vehicle...
• Inventory Data: It is useful for the dealership to be able to track where a vehicle is located. A vehicle can be located at their parking lot or other dealerships’ parking lot. Shipments of new vehicles arrive, vehicles are sold, various customer vehicles arrive for service and leave. This can be confusing, but it is important (in cases of theft, or to avoid costly mistakes) that the dealership be able to track this information. Some dealerships will have other parking lots at remote sites as well as their main dealership lot.
• Billing Data: Relation connecting customers and billing events -- the date, payment method, customer information, and what dealership goods or service are being purchased.
• Contract Data: Dealerships are involved with a number of contracts that require database management -- car leases, service contracts with vehicle fleets for small companies, service contracts with other dealers, promotional contracts, and the like. Representing these appropriately is also important for the dealership database.
• Different Views: The dealership has a number of different employee types who require access to the database. Mechanics need to look at vehicle and customer data, and update service data. Sales people need a different view of the database, focussed on vehicle sales. Billing personnel would have a third set of requirements and responsibilities. It would be useful to be able to support giving each type of access their own view of the database.
Tasks
• Sell a vehicle: vehicles sold might be off the lot, in which case the lot inventory needs to be modified, or they might be sold in expectation of delivery from the factory.
• Lease a vehicle: this is much as with selling vehicles, except that a lease contract is involved, and leased vehicles are treated differently with regard to regular maintenance and service.
• Order a vehicle from the factory: describe the vehicle desired, including all options (leather seats, four-wheel drive, moon roof, whatever), and request that the factory build one like that and send it to the dealership.
• Transfer inventory: it might be useful to support sending and receiving new vehicles from other nearby dealerships, for example if our dealership had a surplus of Rav4s and a Bowie dealership was out of that model.
• Customize a new vehicle: add post-factory options, like roofracks, stereo systems, rust-resistant coating, and the like.
• Service a vehicle: Add a new service record when a vehicle enters the dealership for service; keep track of the ongoing status of the vehicle during service; update the service record after the service is completed and again when the vehicle is