Databases Reference
In-Depth Information
Figure 4-16
SALESPERSON
Salesperson
Number
Salesperson
Name
Commission
Percentage
Year of
Hire
Manager
SALESPERSON table with Manager foreign key.
Converting a Many-to-Many Unary Relationship
Though not a common situation, you can have many-to-many unary relation-
ships. One is shown in Figure 4-17. You have a situation where one product can
be constructed out of a set of other products. For example, you might have the
products Cup, Saucer, and Plate as separate occurrences of the PRODUCT entity.
You could also have an occurrence called Place Setting, made up of a Cup, a
Saucer, and a Plate.
As Figure 4-18 indicates, this relationship requires two tables in the con-
version. The PRODUCT table has no foreign keys. The COMPONENT table indi-
cates which items go into making up which other items. This is commonly
referred to as a bill of materials. COMPONENT also contains any intersection
data that might exist in the many-to-many relationship. In this example, the
Quantity attribute indicates how many of a particular item go into making up
another item.
The fact that we wind up with two tables in this conversion is not surpris-
ing. The general rule is that in the conversion of a many-to-many relationship
Figure 4-17
PRODUCT
Product
Number
Product
Name
Unit Price
*
Part of
Quantity
Many-to-many unary relationship.
