Reference no: EM132838831

Draw an enhanced entity-relationship diagram for the following case. AutoPlanet is a company that sells and services cars and light trucks through a nationwide network of dealerships. Each dealership is authorized to both sell and service both cars and light trucks. AutoPlanet intends to develop a new information system to improve its competitiveness.

Each dealership is identified by a unique dealership number assigned by AutoPlanet. The company also wants to store the dealership's address, phone number, and the name of its general manager. AutoPlanet want to have good relations with the cities in which its dealerships are located. For each such city, identified by state and city names, it wants to store the name of its mayor, the address of its city hall, and its main telephone number. There can be more than one AutoPlanet dealership in a city.

AutoPlanet wants to keep track of each dealership's employees. AutoPlanet assigns each employee an employee number that is unique across the country. It also wants to maintain each employee's name, home address, and cell phone number. Employees have dependents (spouse and children) and the company stores their names, ages (for insurance purposes), and gender. Some employees have no dependents.

There are several categories of employees, two of which are salesperson and mechanic. It is possible than an employee functions in more than one category. In addition to the common data about employees, AutoPlanet wants to store the year a salesperson was hired and the salesperson's sales commission percentage. Some salespersons are sales managers who manage other salespersons while also selling cars, themselves. All mechanics are required to attend periodic training programs. These programs are identified by a unique name, cost, and length in days. AutoPlanet wants to maintain the dates that a mechanic took a particular course and the grade that the mechanic received at the end of it.

There are only two types of mechanics: car mechanics and light truck mechanics. All mechanics are restricted to working only on the type of vehicles (i.e. cars or light trucks) that they specialize in. For car mechanics, the company wants to record the mechanic's current salary; for light truck mechanics the company wants to record the mechanic's skill rating.

Beyond what has been described above, AutoPlanet wants to focus on car sales for now and will add light truck sales at a later time. Each car is uniquely identified by its vehicle identification number (VIN), plus its model and year of manufacture. Customers are identified by a unique customer number assigned by AutoPlanet, plus their name, address, and telephone number. AutoPlanet wants to record which salesperson sold which car to which customer, including the date of the sale and the selling price.