9.1 Joining SP and PJ is discussed in the body of the chapter. Joining PJ and JS yields the spurious tuple (S2,P2,J1), which is then eliminated because there’s no (S2,P2) tuple in SP. Joining JS and SP yields the spurious tuple (S1,P2,J2), which is then eliminated because there’s no (P2,J2) tuple in PJ.

9.2

   CONSTRAINT ... SPJ = JOIN { SPJ { SNO , PNO } ,
                           SPJ { PNO , JNO } ,
                           SPJ { JNO , SNO } } ;

9.3 First of all, we’ll presumably need three relvars for representatives, areas, and products, respectively:

     R { RNO , ... } KEY { RNO }
     A { ANO , ... } KEY { ANO }
     P { PNO , ... } KEY { PNO }

Now, if representative r is responsible for area a, and product p is sold in area a, and representative r sells product p, then r sells p in a. This is a 3-way cyclic rule. So if we were to have a relvar RAP looking like this—

     RAP { RNO , ANO , PNO } KEY { RNO , ANO , PNO }

(with the obvious predicate)—then the following JD would hold in that relvar:

      { { RNO , ANO } , { ANO , PNO } , { PNO , RNO } }

The relvar would thus be subject to redundancy. So let’s replace it by its three binary projections:

     RA { RNO , ANO } KEY { RNO , ANO }
     AP { ANO , PNO } KEY { ANO , PNO }
     PR { PNO , RNO } KEY { PNO , RNO }

(Now there are several equality dependencies that need to be stated and enforced—e.g., the projections R{RNO}, RA{RNO}, and PR{RNO} must always be equal—but the details are straightforward and I omit them here.)

Next, each representative is responsible for sales in one or more areas, and each area has one or more responsible representatives. But this information is already contained in relvar RA, and nothing more is necessary. Similarly, relvar AP takes care of the facts that each area has one or more products sold in it and each product is sold 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:

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:

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:^[194]

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.