Databases Reference
In-Depth Information
in one or more areas, and relvar PR takes care of the facts that each product has one or more responsible
representatives and each representative is responsible for sales of one or more products. Note, however, that the
user does need to be told that the join of RA, AP, and PR does
not
involve any “connection trap” (i.e., that the 3-way
cyclic rule holds). Let's explore this point. First of all, the predicates for RA, AP, and PR are as follows:
RA:
Representative RNO is responsible for area ANO.
AP:
Product PNO is sold in area ANO.
PR:
Product PNO is sold by representative RNO.
Note, incidentally, that a well architected DBMS─sadly, not one that's on the market today, so far as I know─would
allow the designer to tell it about these predicates.
Note:
Telling the DBMS about the predicates would serve to tell
the user too, of course. The difference is that this latter can be done informally (in fact, it has to be done informally,
in today's systems), but the former, if it could be done at all, would have to be done formally (see Chapter 15).
Back to the 3-way rule. Clearly the designer can't just tell the user that the join of relvars RA, AP, and PR is
equal to relvar RAP, because after the decomposition relvar RAP no longer exists. However, we might define that
join as a view (or “virtual relvar”):
VAR RAP VIRTUAL ( JOIN { RA , AP , PR } )
KEY { RNO , ANO , PNO } ;
And that same well architected DBMS would then be able to infer the following as a predicate for view RAP:
Representative RNO is responsible for area ANO
and
product PNO is sold in area ANO
and
product PNO is
sold by representative RNO.
But this predicate is less than the truth (it doesn't capture the 3-way cyclic rule). Ideally, therefore, there ought to be
a way for the designer to tell the DBMS (as well as the user) that the predicate is actually as follows:
7
Representative RNO is responsible for area ANO
and
product PNO is sold in area ANO
and
product PNO is
sold by representative RNO
and
representative RNO sells product PNO in area ANO.
Note that this latter predicate is stronger than the former, in that if a certain (RNO,PNO,ANO) triple satisfies the
latter, it certainly satisfies the former.
9.4
No answer provided.
7
This is thus one of those situations where the user (or in this case the designer) definitely knows more than the system does.
