Databases Reference
In-Depth Information
Ternary Relationships
A ternary relationship is a relationship that involves three different entity types. If
the entity types are A, B, and C, then we might illustrate this as:
B
A
C
To demonstrate this concept in the broadest way using the General Hardware
Co. database, let's slightly modify part of the General Hardware premise. The
assumption has always been that there is a one-to-many relationship between
salespersons and customers. A salesperson is responsible for several customers,
while a customer is in contact with (is sold to by) exactly one of General Hardware's
salespersons. For the purposes of describing a general ternary relationship,
we change that premise temporarily to a many-to-many relationship between
salespersons and customers. That is, we now assume that any salesperson can make
a sale to any customer and any customer can buy from any salesperson.
With that change, consider the ternary relationship among salespersons,
customers, and products. Such a relationship allows us to keep track of which
salesperson sold which product to which customer. This is very significant. In this
environment, a salesperson can sell many products and a salesperson can sell to
many customers. A product can be sold by many salespersons and can be sold
to many customers. A customer can buy many products and can buy from many
salespersons. All of this leads to a lot of different possibilities for any given sale. So,
it is very important to be able to tie down a particular sale by noting and recording
which salesperson sold which product to which customer. For example, we might
store the fact that salesperson 137 sold some of product number 24013 to customer
0839, Figure 6.8.
Relations a, b, and c of Figure 6.9 show the SALESPERSON, CUSTOMER,
and PRODUCT relations, respectively, from the General Hardware relational
database of Figure 6.1, except for one change. Since there is no longer a one-to-
many relationship between salespersons and customers, the Salesperson Number
foreign key in the CUSTOMER relation has been removed! The three relations are
now all quite independent with no foreign keys in any of them.
Figure 6.9d, the SALES relation, shows how this ternary relationship is
represented in a relational database. Similarly to how we created an additional
relation to accommodate a binary many-to-many relationship, an additional relation
has to be created to accommodate a ternary relationship, and that relation is
Figure 6.9d. Clearly, as in the binary many-to-many case, the primary key of the
additional relation will be (at least) the combination of the primary keys of the
entities involved in the relationship. Thus, in Figure 6.9d, the Salesperson Number,
Customer Number, and Product Number attributes all appear as foreign keys and
the combination of the three serve as part of the primary key. Why just ''part of''
Search WWH ::
Custom Search