SQL and Relational Theory, 2nd Edition

EXAMPLE 5: NAMING SUBEXPRESSIONS

Another query: “Get full supplier details for suppliers who supply all purple parts.” Note: This query, or one very like it, is often used to demonstrate a flaw in the relational divide operator as originally defined. See the further remarks on this topic at the end of the present section.

Here first is a logical formulation:

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

(“names of suppliers SX such that, for all parts PX, if PX is purple, there exists a shipment SPX with SNO equal to the supplier number for supplier SX and PNO equal to the part number for part PX”). First we apply the implication law:

     { SX } WHERE FORALL PX ( NOT ( PX.COLOR = 'Purple' ) OR
                  EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) )

Next De Morgan:

     { SX } WHERE
            FORALL PX ( NOT ( ( PX.COLOR = 'Purple' ) AND
            NOT EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) ) )

Apply the quantification law:

     { SX } WHERE
            NOT EXISTS PX ( NOT ( NOT ( ( PX.COLOR = 'Purple' ) AND
            NOT EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) ) ) )

Double negation:

     { SX } WHERE
            NOT EXISTS PX ( ( PX.COLOR = 'Purple' ) AND
            NOT EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) )

Drop some parentheses and map to SQL:

     SELECT *
     FROM   S AS SX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PX
            WHERE  PX.COLOR = 'Purple'
            AND    NOT EXISTS
                 ( SELECT *
                   FROM   SP AS SPX
                   WHERE  SPX.SNO = SX.SNO
                   AND    SPX.PNO = PX.PNO ) )

Recall now from Chapter 7 that if there aren’t ...