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.