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

Microsoft Access Data Model Example

Employees and Qualifications

The following data model is designed to hold information relating to Employees and Qualifications that each has achieved. 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:

  • Employee Details
  • Departments
  • Employee Qualifications
  • Qualifications
  • Qualification Types

The Entities are related as follows:

  • One Department can have Many Employees
  • One Qualification Type can have Many Qualifications
  • One Employee can achieve Zero, One or Many Qualifications
  • One Qualification can be achieved by Zero, One or Many Employees

The design allows an Employee to be assigned to many Qualifications. Some Qualifications may have an expiry date (they may only be valid for a set period of time), therefore we need to allow the Qualification to be assigned to an Employee many times, hence the inclusion of the DateQualificationAchieved as part of the composite Primary Key.

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

  1. Which Qualification does each Employee hold?
  2. How many Qualifications are held by each Employee?
  3. Have any Qualifications expired and need to be renewed by Employees?

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 model please go to: Relationships for Employees and Qualifications Data Model

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