O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required