SQL and Relational Theory, 2nd Edition

EXAMPLE 6: MORE ON NAMING SUBEXPRESSIONS

I’ll give another example to illustrate the usefulness of introducing symbolic names for subexpressions. The query is “Get suppliers such that every part they supply is in the same city as that supplier.” Here’s a logical formulation:

     { SX } WHERE FORALL PX
                ( IF EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO )
                  THEN PX.CITY = SX.CITY )

(“suppliers SX such that, for all parts PX, if there’s a shipment of PX by SX, then PX.CITY = SX.CITY”).

This time I’ll just show the transformations without naming the transformation laws involved at each step (I’ll leave that as an exercise for you):

     {SX } WHERE FORALL PX ( IF exp1 THEN exp2 )
     {SX } WHERE NOT EXISTS PX ( NOT ( IF exp1 THEN exp2 ) )
     {SX } WHERE NOT EXISTS PX ( NOT ( NOT ( exp1 ) OR exp2 ) )
     {SX } WHERE NOT EXISTS PX ( NOT ( NOT ( exp1 AND NOT ( exp2 ) ) ) )
     {SX } WHERE NOT EXISTS PX ( exp1 AND NOT ( exp2 ) )

Now expand exp1 and exp2 and map to SQL:

     SELECT *
     FROM   S AS SX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PX
            WHERE  PX.CITY <> SX.CITY
            AND    EXISTS
                 ( SELECT *
                   FROM   SP AS SPX
                   WHERE  SPX.SNO = SX.SNO
                   AND    SPX.PNO = PX.PNO ) )

Result:

SNO	SNAME	STATUS	CITY
S3	Blake	30	Paris
S5	Adams	30	Athens

By the way, if you find this result a little surprising, note that supplier S3 supplies just one part, part P2, and supplier S5 supplies no parts at all; logically speaking, therefore, both of these suppliers do indeed satisfy the condition that “every part they supply” is in the same city.

Here for interest is a Tutorial D ...