Transforming Data Models into Database Designs - Database Processing: Fundamentals, Design, and Implementation - page 250

Suppose, however, that the business has a rule that each CUSTOMER can place or-
ders only with a particular SALESPERSON. In this case, the ternary relationship ORDER:
CUSTOMER:SALESPERSON is constrained by an additional binary 1:N relationship between
SALESPERSON and CUSTOMER. To represent the constraint, we need to add the key of
SALESPERSON to CUSTOMER. The three tables will now be:

CUSTOMER ( CustomerNumber , {nonkey data attributes}, SalespersonNumber )
SALESPERSON ( SalespersonNumber , {nonkey data attributes})
ORDER ( OrderNumber , {nonkey data attributes}, CustomerNumber ,
SalespersonNumber )

The constraint that a particular CUSTOMER is sold to by a particular SALESPERSON
means that only certain combinations of CustomerNumber and SalespersonNumber can exist
together in ORDER. Unfortunately, this constraint cannot be expressed in a relational model.
It must be documented in the design, however, and enforced by program code, as shown in
Figure 6-23.
A constraint that requires one entity to be combined with another entity is called a
MUST constraint . Other similar constraints are the MUST NOT constraint and the MUST
COVER constraint. In a MUST NOT constraint , the binary relationship indicates combina-
tions that are not allowed to occur in the ternary relationship. For example, the ternary rela-
tionship PRESCRIPTION:DRUG:CUSTOMER shown in Figure 6-24 can be constrained by a
binary relationship in the ALLERGY table that lists the drugs that a customer is not allowed
to take.
In a MUST COVER constraint , the binary relationship indicates all combinations that
must appear in the ternary relationship. For example, consider the relationship AUTO_
REPAIR:REPAIR:TASK in Figure 6-25. Suppose that a given REPAIR consists of a number of
TASKs, all of which must be performed for the REPAIR to be successful. In this case, in the table
AUTO_REPAIR, when a given AUTO_REPAIR has a given REPAIR, then all of the TASKs for that
REPAIR must appear as rows in that table.
None of the three types of binary constraints discussed here can be represented in the
relational design. Instead, they are documented in the design and implemented in applica-
tion code.

SALESPERSON Table

Figure 6-23
Ternary Relationship with a
MUST Constraint

SalespersonNumber Other nonkey data

10
20
30

CUSTOMER Table

CustomerNumbe r Other nonkey data

SalespersonNumber
10
20
30

1000
2000
3000

Binary MUST Constraint

ORDER Table

OrderNumber

Other nonkey data SalespersonNumber CustomerNumber

100
200
300
400
500

10
20
10
30

1000
2000
1000
3000
2000

Only 20 is allowed here