This project includes a database design and implementation for a CAR DEALERSHIP. Start by creating the initial E-R diagram. Your E-R Model will be the blue-print for developing the set of tables that will be the center of your database. In developing the tables for your database you will follow the general rules governing relationships among tables. Be on the look-out for data anomalies and integrity rules violations. Make sure your tables are in 3NF/BCNF levels of Normality. Once you have completed the above, you finish your project by implementing your Conceptual and Logical Design as a MySQL DBMS Model. Here are the things you need to do.

1. Create the initial E-R diagram. Since the dealership sells both new and used cars and operates a service facility, you should base your design on the following business rules:

a. A salesperson may sell many cars but each car is sold by only one salesperson.
b. A customer may buy many cars but each car is sold to only one customer.
c. A salesperson writes a single invoice for each car sold.
d. A customer gets an invoice for each car she/he buys.
e. A customer might come in just to have his/her car serviced; that is, the customer need not buy a car to be classified as a customer.
f. When a customer takes one or more cars in for repair or service, one service ticket is written for each car.
g. The car dealership maintains a service history for each of the cars serviced. The service records are referenced by the car’s serial number.
h. A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
i. A car that is serviced may or may not need parts. (For example, adjustments of a carburetor or cleaning of a fuel injector nozzle does not require the use of parts.)

As you examine the initial E-R diagram, note that business rules (a – d) above refer to the relationships of four main entities in the database: SALESPERSON, INVOICE, CUSTOMER, and CAR. Note also that an INVOICE requires a SALESPERSON, a CUSTOMER and a CAR. Business rule e indicates that INVOICE is optional to CUSTOMER and CAR because a CAR is not necessarily sold to a CUSTOMER. (Some customers only have their cars serviced.) If a customer brings in a car for service and the car was not bought at the dealership, it must be added to the CAR table in order to enable the system to keep a record of the service. This is why the CUSTOMER-owns-CAR relationship exists. Also, note that not all cars are owned by a customer; some cars belong to the dealership. Your initial E-R diagram may be revised as the description of operations becomes more detailed and accurate. As to advance in your design additional entities and relationships are likely to be developed. Note, for example, that the car’s history may be updated through its service records, thus requiring a new relationship between HISTORY and SERVICE. Additional changes are likely to be generated as you go through the normalization procedures.

1. Use the E-R Model as a guide to developing the tables that define your database. Start by firming up entity relationships, attributes and their domains. The Normalization process is not required in this project

2. Use SQL queries and the tools of MYSQL to create at least two of the required tables that are necessary within the schema of the project.

Your output from this project is the following:

1. An easy-to-read, full-page, E-R diagram that uses the Text Book notations. The diagram should include the relationships of all Entities their cardinalities and participation rules.

2. A MYSQL database that contains at least two related tables with all queries used to create the tables or insert data into the tables. Two-to-Three records in each table should have data so the user might test relationships through his or her own ad hoc queries. Make sure that all primary and foreign keys and all referential integrity rules are properly defined to the MYSQL DBMS for each of the tables.

THE DELIVERY OF YOUR COMPLETED PROJECT SHOULD SHOW THE FOLLOWING:

1. An easy to see E-R diagram that allows me to see Entities, Relationships, Cardinalities and Participation Rules. And allows you to explain these relationships to me.

2. A MYSQL Database that can be used to show at least two related tables, the primary and foreign key relationships vis a vis relational integrity, along with a discussion of how the tables were obtained (I need to see the screenshot).

3. A discussion of any special problems or approaches you used in completing the project should be presented at this point.