Resources

Online Shop

Info

Microsoft Access Data Model Example

Vehicle Details and Information Scenario

The following data model is designed to hold information relating
to Vehicle Details, in particular Vehicle Manufacturers, Models and
associated Vehicle information. For this scenario we need to define
the following facts:

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 Manufacturers

Vehicle Models

Vehicle Details

Vehicle Features

Vehicle Fuel Types

Vehicle Colour

The Entities are related as follows:

One Vehicle Manufacturer can have zero, one or many Vehicle Models

One Vehicle Model can have zero, one or many Vehicle Details

One Vehicle Fuel Type can have zero, one or many Vehicle Details.

One Vehicle can have zero, one or many Vehicle Features, a Vehicle
Feature can be associated with many Vehicle Details

The design allows a Vehicle to have multiple Vehicle Features (Air
Conditioning, Alloy Wheels, CD Player, Electric Windows etc.) assigned
to it. These Vehicle Features can also be associated with many of
the Vehicles, therefore we have a Many-To-Many relationship defined.
This is broken into the two separate One-To-Many relationships shown
using the LINK table - tblLINKFeaturesToVehicles.

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

How many Models does a Manufacturer make?

Do we have any Vehicles of a particular Model?

Do we have any Vehicles in stock that have Air Conditioning and
Satellite Navigation?

Do we have any Vehicles manufactured by Ford for under £10,000

The following data model allows these questions to be answered and
allows the information contained above to be stored logically and
in a structured manner.