6.1

   CONSTRAINT ...
        COUNT (   JOIN { TJ , TS } ) =
        COUNT ( ( JOIN { TJ , TS } ) { S , J } ) ;

Or, using the alternative style for constraints described in the answer to Exercise 4.8:

     CONSTRAINT ... JOIN { TJ , TS } KEY { S , J } ;

6.2 Let LT and CT be the projections of RX2A′ on {CLASS,STATUS} and {CITY,STATUS}, respectively. Then (a) {CLASS} and {CITY} will be foreign keys in RX2B′, referencing LT and CT, respectively, and (b) the following multirelvar constraint will also hold:

     CONSTRAINT ... WITH ( LTX := LT RENAME { STATUS AS X } ,
                           CTY := CT RENAME { STATUS AS Y } ) :
                    AND ( JOIN { RX2B′ , LTX , CTY } , X = Y ) ;

6.3 The first of the given FDs means {STREET,CITY,STATE} is a key; the second means the relvar isn’t in BCNF. However, if we use Heath’s Theorem to decompose it (on the basis of the FD {ZIP} → {CITY,STATE}) into BCNF projections as follows—

     ZCT { ZIP , CITY , STATE }
         KEY { ZIP }

     ZR  { ZIP , STREET }
         KEY { ZIP , STREET }

—then we lose the FD {STREET,CITY,STATE} → {ZIP}. As a result, relvars ZCT and ZR can’t be independently updated. (Subsidiary exercise: Develop some sample values for ZCT and ZR to illustrate this point.) Of course, if we don’t perform this decomposition, there’ll be some redundancy; to be specific, the fact that a given zip code corresponds to a particular city and state will appear several times. But does that redundancy cause problems? Given that the zip code for a given city and state doesn’t change very often, the answer is “possibly, but not very often.” (On the other hand, it’s not true to say zip codes never change.)

6.4 Here’s an irreducible cover for RX1:

     { SNO , PNO } → { QTY }
     { SNO }       → { CITY }
     { CITY }      → { STATUS}

The 3NF procedure yields {SNO,PNO,QTY}, {SNO,CITY}, and {CITY,STATUS}.

Next, RX3. An irreducible cover:

     { SNO }   → { CLASS }
     { CLASS } → { CITY }
     { CITY }  → { STATUS }

The 3NF procedure yields {SNO,CLASS}, {CLASS,CITY}, and {CITY,STATUS}.

Finally RX2. Irreducible cover:

     { SNO }   → { CLASS }
     { SNO }   → { CITY }
     { CLASS } → { STATUS }
     { CITY }  → { STATUS }

The 3NF procedure yields {SNO,CLASS,CITY}, {CLASS,STATUS}, and {CITY,STATUS}. The interesting thing about this example is that (as was shown in the body of the chapter) if we decompose on the basis of the FD {SNO} → {CLASS,CITY}, we obtain {SNO,CLASS,CITY} and {CLASS,CITY,STATUS} as the 3NF projection headings, and that’s not what we get from the 3NF procedure. In fact, the result of the 3NF procedure requires the following rather complicated multirelvar constraint to be maintained:

     CONSTRAINT ... JOIN { SLC , LT } = JOIN { SLC , CT } ;

(“for a given supplier, class status = city status”; SLC, LT, and CT here denote the projections of RX2 on {SNO,CLASS,CITY}, {CLASS,STATUS}, and {CITY,STATUS}, respectively). The example thus illustrates the point that although the 3NF procedure is certainly guaranteed to yield 3NF projections and not to lose any FDs, it probably shouldn’t be followed too blindly.

Note: Suppose we were to name the status attributes in relvars LT and CT differently, thus:

     LT { CLASS , CLASS_STATUS }
     CT { CITY , CITY_STATUS }

Then the constraint that the two status values must be equal for any given supplier might be stated thus:

     CONSTRAINT ... IS_EMPTY ( ( JOIN { SLC , LT , CT } )
                               WHERE CLASS_STATUS ≠ CITY_STATUS ) ;

(The Tutorial D expression IS_EMPTY (r) returns TRUE if relation r is empty and FALSE otherwise.) Alternatively:

     CONSTRAINT ...
        AND ( JOIN { SLC , LT , CT } , CLASS_STATUS = CITY_STATUS ) ;

The overall message of this example might be put this way: This whole business of losing or preserving FDs in particular is really just a special case of a more general phenomenon. In fact, it should be obvious that, in general, if we start with some design DBD1 and map it into some logically equivalent design DBD2, then that process will necessarily involve some restructuring of constraints as well as of relvars.

6.5 Assumptions: No star plays more than one role in any given movie; no movie has more than one director. (Are these assumptions reasonable?) FDs:

     { S , M } → { R }
     { M }     → { D , Y }
     { S }     → { B }
     { B }     → { Z , C }
     { Z , C } → { H }

{S,M} is a key. The BCNF procedure yields {S,M,R}, {M,D,Y}, {S,B}, {B,Z,C}, and {Z,C,H}. No FDs are lost.