|
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
box.com.
Background
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
- The employee hourly rate must meet minimum wage.
- There is only one labor ticket per employee per work
order.
- 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
Entities
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.
Constraints
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.
Relationships
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.
Keys
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...
Learn more about our Microsoft Access courses..
|
