Database Reference
In-Depth Information
Figure 10.61. Designating the type of participation for the primary and foreign keys
of the STAFF table
In this case, you must have at least one staff member with a valid staff identification num-
ber (the primary key) who can serve as a manager. Conversely, you need not provide a
manager identification number (the foreign key) for a brand-new staff member; this person
may have just been hired earlier today and has not yet been assigned to a particular depart-
ment or project.
Identifying the Degree of Participation for Each Table
Now that you've determined how each table will participate within the relationship, you
must determine the degree to which each table will participate. The
degree of participation
indicates the minimum number of records that a given table
must have
associated with a
single record in the related table and the maximum number of records that the table
is al-
lowed to have
associatedwithasinglerecordintherelatedtable.Thefactorsyouusetode-
termine the
degree
of participation—obvious circumstances, common sense, or conform-
ancetosomesetofstandards—arethesameasthoseyouusedtodeterminethe
type
ofpar-
ticipation. You'll commonly identify the degree of participation for some tables now and
revisit the remaining tables when you define business rules for the database.
You use two numbers separated by a comma and enclosed within parentheses to represent
the degree of participation for a given table. The first number indicates the required min-
imum number of related records and the second number indicates the allowable maximum
number of related records. For example, a degree of participation such as (2,11) indicates
that the table must have at least 2 but no more than 11 of its records related to a single re-
cord in the other table.
Consider the EMPLOYEES and CUSTOMERS tables once again. There is a one-to-many
relationship between these tables, which means that a given customer can be associated
with only one employee and a given employee can be associated with any number of cus-
tomers. (Yes, I know; this is the obvious part.) Assume, however, that your organization
has just instituted a new policy that focuses sharply on quality customer service. In order
