SQL and Relational Theory, 2nd Edition

CHAPTER 7

Here first are answers to certain exercises that were stated inline in the body of the chapter. In one, we were given relvars as follows—

     S   { SNO }        /* suppliers               */
     SP  { SNO , PNO }  /* supplier supplies part  */
     PJ  { PNO , JNO }  /* part is used in project */
     J   { JNO }        /* projects                */

—and we were asked for a SQL formulation of the query “Get all (sno,jno) pairs such that sno appears in S, jno appears in J, and supplier sno supplies all parts used in project jno.” A possible formulation is as follows:

     SELECT SX.SNO , JX.JNO
     FROM   S AS SX , J AS JX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PX
            WHERE  EXISTS
                 ( SELECT *
                   FROM   PJ AS PJX
                   WHERE  PJX.PNO = PX.PNO
                   AND    PJX.JNO = JX.JNO )
            AND    NOT EXISTS
                 ( SELECT *
                   FROM   SP AS SPX
                   WHERE  SPX.PNO = PX.PNO
                   AND    SPX.SNO = SX.SNO ) )

Note: For a detailed discussion of how to tackle complicated queries like this one in SQL, see Chapter 11.

Another inline exercise asked what happens if (a) r1 and r2 are relations with no attribute names in common, (b) r2 is empty, (c) we form the product r1 TIMES r2, and finally (d) we divide that product by r2. Answer: It should be clear that the product is empty, and hence the final result is empty too (it has the same heading as r1, but of course it isn’t equal to r1, in general). Do note, however, that dividing by an empty relation isn’t an error (it’s not like dividing by zero in arithmetic).

Another inline exercise asked why the following Tutorial D and SQL expressions weren’t quite equivalent:

 S WHERE SUM ( !!SP , ...