|
Creating the first table relationship
To create the first relationship put your mouse cursor on the
EmpNo primary key (PK) in the employees table. Click and
drag to the EmpNo PK in employee_benefits. In this latter
table the PK is also serving as a foreign key (FK). This
creates a one-to-one relationship between the two tables.
The table to the left in the edit relationships dialogue
holds the PK. The table to the right, the FK. Sometimes
these are referred to as the parent table and the child
table, respectively. These one-to-one relationships are not
as common as the one-to-many relationships we will see
later.
Check that you want to enforce referential integrity. This
means you will not be able to add a benefits record that
incorrectly refers to an employee that does not exist. Check
also cascade update. This means that if you change the PK in
the parent (employees) table the FK's in the related child
table (employee_benefits) will be updated to the same value.
Usually you want to do this.
You can cascade deletes as well. This means that if we delete
an employee record the corresponding benefits record will be
deleted. It will depend on your business rules whether to
check this one or not.
Click create and Microsoft Access will create the
relationship. It should look as shown below. Note the 1
symbols at the end of each line meaning one-to-one. If we
had not checked enforce referential integrity we would not
see these symbols. There is another button on the edit
relationships dialogue labeled join properties. We will look
at that later in this article.
Creating the remaining relationships
To create the second relationship between the employees and
labor table do the same thing. Click and drag from EmpNo,
the PK in the employees table, to the corresponding FK in
the labor table. Again, specify you want to enforce
referential integrity and cascade updates. Repeat for the
relationship between Work_Orders and Labor, joining on WoNo.
When done your relationship design window should look like
the one below. Note that the latter two relationships are
one-to-many, symbolized by the 1 symbol and the infinity
symbol. This means there could be many labor tickets per
employee and many labor tickets per work order. Conversely,
each labor ticket would refer to only one employee and one
work order.
An additional point of interest is the implied relationship
between employees and work orders above. It is many-to-many.
This means each employee may work on many work orders and
each work order may have been worked on by many employees.
This is a valid design time relationship but cannot be
directly implemented in a relational database. Instead one
has to create a junction table between the two with the
primary keys from each migrated to the junction table.
There, the two columns will form a composite primary key
(EmpNo,WoNo in this case).
Editing Relationships
We will modify referential integrity and the join properties
in the remainder of this article.
Continue to page 3
- modifying referential integrity...
|
