Databases Reference
In-Depth Information
Figure 4-6
SALESPERSON
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
Office
Number
OFFICE
Office
Number
Telephone
Size
Separate SALESPERSON and OFFICE tables, first version.
becomes immediately evident. The salesperson entity is involved in relationships
with other entities in addition to the office entity. It is related to the CUSTOMER
entity through a one-to-many relationship and the PRODUCT entity through a
many-to-many relationship.
Figure 4-6 is a better choice. There are separate tables for the SALES-
PERSON and OFFICE entities. In order to record the relationship (which
salesperson is assigned to which office), the Office Number attribute is placed
as a foreign key in the SALESPERSON table. This connects each salesperson
with the office to which he or she is assigned. Again, look at the modalities
in the E-R diagram of Figure 4-3. Each salesperson is assigned to exactly one
office, as is indicated by the two “ones” adjacent to the office entity. That
translates directly into each record in the SALESPERSON table of Figure 4-6
having a value (and a single value at that) for its Office Number foreign key
attribute. Each unassigned office will have a record in the OFFICE table, with
Office Number as the primary key. In this case, unassigned offices aren't seen
as a problem. Their office numbers will simply not appear as foreign key val-
ues in the SALESPERSON table.
The third option is shown in Figure 4-7. Instead of placing Office Number
as a foreign key in the SALESPERSON table, you place Salesperson Number as
a foreign key in the OFFICE table. Recall from the E-R diagram that the modal-
ity of zero adjacent to the salesperson entity says that an office might be empty.
As a result, some or perhaps many records of the OFFICE table in Figure 4-7
would have no value or a null in their Salesperson Number foreign key attribute
positions.
It follows that if the modalities were reversed, meaning that the zero
modality was adjacent to the OFFICE entity box and the one modality was
adjacent to the SALESPERSON entity box, then the design in Figure 4-7 might
be preferable. Reversed modality would mean that every office must have a
salesperson assigned to it, but a salesperson may or may not be assigned to an
office. There are different circumstances that could result in this situation. For
