databasedev.co.uk - database solutions and downloads for microsoft access

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:

  1. How many Models does a Manufacturer make?
  2. Do we have any Vehicles of a particular Model?
  3. Do we have any Vehicles in stock that have Air Conditioning and Satellite Navigation?
  4. 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.

To view the data model please go to: Relationships for Vehicle Details and Information

Please download the data model relating to Relationships for Vehicle Details and Information which is contained in the zipped up Microsoft Word document.