2. For the Auto Dealership ERD shown in Figure 1, identify and resolve errors and note incompleteness in the specifications. Your solution should include a list of errors and a revised ERD. For each error, identify the type of error (diagram or design) and the specific error within each error type. Note that the ERD may have both diagram and design errors. Specifications for the ERD are presented in the following narrative.
Mountain High Quality Vehicles serves a metropolitan market with a medium size inventory of pre-owned cars and trucks. The vehicle inventory includes a variety of makes and models such as Acura, Chrysler, BMW, Cadillac, Ford, Chevrolet, Toyota, Honda, Mercedes-Benz, and more. A small staff manages the major functions of the business, purchasing, transporting, marketing, cleaning, maintaining, and selling the vehicles. They carefully inspect and certify the vehicles before they are available to the public for sale.
The dealership would like to develop an inventory management database to improve its tracking of vehicles, sales, and expenses. The dealership also would like to track information about its customers and car(s) sold to its customers.
· Vehicle Acquisitions: Periodically the owners attend auctions and purchase pre owned cars seeking reasonable prices and quality vehicles. They also purchase pre-owned vehicles from the wholesale market. The purchased vehicles are transported to the dealership and inspected for mechanical problems. Each vehicle is fixed and cleaned before being placed for sale.
· Vehicle Improvements: Apart from purchases, the dealership has additional expenses to prepare vehicles for market. The expenses typically involve transporting the purchased vehicle to the dealership, checking the vehicle for any potential problem, repairs and maintenance if necessary, marketing and cleaning.
· Sales Details: Customers purchase vehicles at the dealership. Each sale involves one customer even for married couples. Although customers can purchase more than one vehicle, each vehicle is recorded as a separate sale. When a sale is completed, the employee associated with the sale and payments are recorded. Typically, vehicles remain on the lot for a period of time before sales occur.
· Vehicle Details: The database tracks the unique vehicle identifier and vehicle identification number (VIN) to complete a sales transaction. The database also tracks vehicle characteristics such as make, model, year, mileage, exterior and interior colors, transmission type (automatic or manual), and number of cylinders (4 or 6).
· Customer Details: The database records the unique customer number, first and last names, address, city, state, postal code, primary phone number, and cellphone number.
· Expense Details: Each vehicle expense has a unique expense identifier, expense type, expense description, expense amount, expense paid date, account, and associated vehicle.
· Account Details: The database tracks account details such as a unique account identifier, account description, related expenses, and related payments.
· Payment Details: The database also tracks the vehicle sale (payment process). Each payment has a unique invoice number and payment method. The payment options are cash, credit card, or external line of credit as no financing is available at the dealership. Typically one payment is made per sale although multiple payments are sometimes made if a customer provides cash for part of the sale. If a customer is paying with a credit card, the payment includes the credit card number, expiration date, name on the credit card, and payment description. The employee that completed the sales transaction and vehicle should be recorded. The same employee works as sales associate and processes the payment to complete the sale. Each payment is associated with one account for company accounting purposes.
Figure 1: ERD for the Auto Dealership Database