Mary and Mike Miller own a chain of Mini dealerships throughout Colorado. They have used systemsin the business for several years to handle payroll, typical accounting functions, and customer order entry. Because of the dealership's expansion both in the number of used car locations and the number of cars handled, they need a relational database to track their used car inventory, dealership locations, managers, salespeople, customers, and prospects (CRM-like).

 

The dealership handles both new and used cars at several locations but the new car inventory is managed by a program supplied by BMW. While the information they need to track on both types of cars is essentially the same (VIN#, make, model, year, style, color, engine, cost, selling price, transmission, etc) for used cars they want to track information about the car's condition, mileage, repairs, and previous owner. Some used cars are trade-ins, some are lease returns, and some have been purchased at auction. New cars are tracked by the BMW ERP system. A salesperson can sell a car from another dealership location, but usually cars will stay at one location.

 

The owners also wish to keep track of their salesforce. Each location has a manager who is also a salesperson at that location. They would like to know the typical employee information as well as which customer is assigned to an individual salesperson.

 

Customers are categorized by whether they have purchased in the past or whether they are a prospect. Prospects are further categorized as to what stage of the buying process they are in. A customer may have purchased one or more cars and a prospect may be interested in one or more models. The database should keep track of contacts made with the prospects and any quotes. For commission purposes, a customer may be assigned to more than one salesperson. The owners wish to track which car(s) a customer purchased over time. They wish to actively market to their prospect and customer base, both electronically and through the mail. The Miller family takes a great deal of pride in their service levels.

 

The owners wish to be able to produce reports that show used car inventory and availability. They also want to do direct database marketing to their prospective customers. The system should be able to produce information about salesperson production as well as show the customer/salesperson relationship. The system should be able to provide a wide variety of ad-hoc queries from information in the database.

 

Miller Mini wishes to consolidate this information into a database application. You are to do the design work. Develop a conceptual data model for this case using the entity-relationship methodology shown in class. Consider the mission of the business and what they are trying to accomplish. Make sure you label your entities; show your relationships and cardinalities; and include or list all attributes and note any assumptions you have made. I NEED a graphic representation of the data model. You may use any graphic software or a tool like Visio; the design must be neat and readable.