Scribd
Upload
31 views2 pages

Car Dealership Database Model Design

This document outlines the requirements for a database model for a car dealership. It defines five key entities - Vehicle Manufacturer, Vehicle Model, Vehicle Details, Vehicle Features, and Customer. These entities are related such that manufacturers have models, models have details, vehicles can have multiple features, and customers can buy multiple vehicles. It also provides 10 sample questions the database needs to be able to answer and asks how tables would need to be modified for new customers or vehicles.

Uploaded by

chanyoung4951
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
31 views2 pages

Car Dealership Database Model Design

This document outlines the requirements for a database model for a car dealership. It defines five key entities - Vehicle Manufacturer, Vehicle Model, Vehicle Details, Vehicle Features, and Customer. These entities are related such that manufacturers have models, models have details, vehicles can have multiple features, and customers can buy multiple vehicles. It also provides 10 sample questions the database needs to be able to answer and asks how tables would need to be modified for new customers or vehicles.

Uploaded by

chanyoung4951
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

This is a data model created for a new/used car dealership.

These facts define the requirements which the Database must meet and should be agreed
between the Database User and the Database Designer prior to physical creation.
The draft facts have been defined as:
The Entities required should include:

Vehicle Manufacturer
Vehicle Model
Vehicle Details
Vehicle Features
Customer

The Entities are related as follows:

One Vehicle Manufacturer may have one or many Vehicle Models


One Vehicle Model may have one or many Vehicle Details
One Vehicle may have one or many Vehicle Features, a Vehicle Feature may be
associated with many Vehicle Details
A customer may buy one or more vehicles, vehicles may be bought by one or more
customers.

When asking questions of the database we may need to know:


1.
2.
3.
4.

What are the models of Toyota?


If there are any Vehicles that have Air Conditioning, list them?
Do we have any Vehicles stock manufactured by Ford for over $50,000?
What is the average price of Honda? What vehicles cost more than the average price
of the Hondas?
5. Which customers prefer Hondas?
6. How many customers bought Hondas?
7. What are the registration numbers of blue cars with power steering?
8. What are the features on the car that Harry Potter bought?
9. How much did each customer save? (asking price-price) (total of all purchases)
10. What was the total revenue (price) for each manufacturer?

Write the SQL statements needed to answer those questions.


Which tables need to be modified if a new customer wanted to buy one of the vehicles ?
What tables need to be modified if a new vehicle is added to the inventory?
What did the E-R diagram look like before the physical design was created in Access?

The next page is a copy of the relationships from the Access Database which is also posted.

You might also like