Microsoft Access Factory 2000 Student Database

Articles Index

Course Listing

Free Catalog

Request Information

What's New

Background | Business Rules | Database Description | Referential Integrity | Text Schema


This article presents the Factory 2000 Microsoft Access sample database used in many of our classes. It is abstracted from a factory cost accounting application. It is purposefully very simple in the spirit of Chris Date's famous Supplier_Parts sample database.

You can download the Microsoft Access database at


The Factory 200 database is used to record employee labor hours expended against work orders. The hours expended are recorded on a labor ticket. As might be expected for such a simple database, the design is fully normalized.

Business Rules

  1. The employee hourly rate must meet minimum wage.
  2. There is only one labor ticket per employee per work order.
  3. Each labor ticket must have a start time. End time can be recorded when the employee finishes but must be greater than or equal to the start date.

Database Description


There are three tables: employees, labor and work_orders. The employees table holds the employee information including their hourly wage rate. Work_orders contains the information about a work order assigned to the plant floor. It holds the standard hours expected as well as a field to accumulate running total hours expended. Hours worked on a work order are recorded in a labor ticket.


In the employees table lName and rate are required. Further, rate has a validation rule to ensure the rate is at or above minimum wage. There is also a validation rule to ensure the labor ticket end date is greater than or equal to the start date. This rule has to be created via the table's property sheet as it involves more than one column. Technically, this is what is called a table constraint. The others are column constraints.

The database also has referential integrity constraints discussed below.


The labor ticket forms an intersection table between the employees and the work orders. Employees may work on more than one work order and a given work order may be worked on by multiple employees. This is a many-to-many relationship which is represented in a physical design by an intersection table. In this case the labor ticket which we need anyway to record the hours.

There are one-to-many relationships from employees and work_orders to labor tickets. (Many-to-many relationships always break down into two one-to-many relationships like this). Access can't represent the following directly but the many side of each relationship is optional and the one side is mandatory. A labor ticket must refer to a valid employee and work order. But a new hire or trainee may not have done any work yet. Similarly a work order may be newly arrived on the factory floor with no work done on it yet.

Factory 2000 ERD


The primary keys in the employees and work_orders tables are empno and wono, respectively. Empno is like an employee badge number: 1,2,T4 for a temp, etc. Wono is a work order like A1, C2, etc.

The primary key in the labor table is the combination of empno and wono. Two column keys are called composite keys. So this is a composite primary key.

Empno and wono in the labor table also serve as foreign keys to the employees and work orders table, respectively. Since this primary key is unique this satisfies our second business rule.

Referential Integrity

Referential integrity is enforced from the labor ticket to both employees and work orders. This means that when a labor ticket is added to the database, Access will check to ensure that a valid employee and work order exists. You can tell by the little annotations or symbols of 1 and infinity that referential integrity is being enforced.

We also set the option to cascade updates. This means that if the primary key in a parent table (the table on the one side) is changed, Access will automatically update the foreign keys in the child table(s) to maintain the integrity of the reference. In this case if empno or wono are changed in the employees or work order tables the labor ticket foreign keys will be updated automatically.

You can also cascade deletes. This means that if the parent record is deleted the corresponding child records are also deleted. If deletes are not cascaded then the delete of the parent record will generate an error message. This is technically called delete or update restrict. We did not set cascade delete in this database.

Text Format Schema

The schema can also be represented in text with a few conventions: bold = required field, underline = primary key, italic = foreign key.

Employees(empno, fname, lname, rate, mgrno)

Work_Orders(wono, descr, std, accum)

Labor(empno, wono, start, end, hours)

Hopefully, you have found this a useful explanation of the Factory 2000 student database. You may be working with this database in our classes so a little background could prove helpful. Perhaps others can profit from this article as well.

Dan D'Urso
Laguna Niguel, CA 92677
June 2012

Submit the form below to be notified of new articles or other Microsoft Access resources made available...

Receive course announcements and news. Sign Up Today!

Email Marketing by VerticalResponse

Learn more about our Microsoft Access courses..

View MS Access course catalog.


Please send comments and suggestions to WebMaster at

Copyright 2017 D.H.D'Urso & Associates
Laguna Niguel, Orange County, California

Index | Close Window | Back | Top