SQL and Relational Theory, 2nd Edition

EXAMPLE 3: IMPLICATION AND UNIVERSAL QUANTIFICATION

A query example this time—“Get part names for parts whose weight is different from that of every part in Paris.” Here’s a straightforward logical (i.e., relational calculus) formulation:

     { PX.PNAME } WHERE FORALL PY ( IF PY.CITY = 'Paris'
                                    THEN PY.WEIGHT ≠ PX.WEIGHT )

This expression can be interpreted as follows: “Get PNAME values from parts PX such that, for all parts PY, if PY is in Paris, then PY and PX have different weights.” Note that I use the terms where and such that interchangeably—whichever seems to read best in the case at hand—when I’m giving natural language interpretations like the one under discussion.

As a first transformation, let’s apply the quantification law:

     { PX.PNAME } WHERE NOT EXISTS PY ( NOT ( IF PY.CITY = 'Paris'
                                              THEN PY.WEIGHT ≠ PX.WEIGHT ) )

Next, apply the implication law:

     { PX.PNAME } WHERE
                  NOT EXISTS PY ( NOT ( NOT ( PY.CITY = 'Paris' )
                                        OR ( PY.WEIGHT ≠ PX.WEIGHT ) ) )

Apply De Morgan:

     { PX.PNAME } WHERE
                  NOT EXISTS PY ( NOT ( NOT ( ( PY.CITY = 'Paris' )
                                   AND NOT ( PY.WEIGHT ≠ PX.WEIGHT ) ) ) )

Tidy up, using the double negation law, plus the fact that NOT (a ≠ b) is equivalent to a = b:

     { PX.PNAME } WHERE NOT EXISTS PY ( PY.CITY = 'Paris' AND
                                        PY.WEIGHT = PX.WEIGHT )

Map to SQL:

     SELECT DISTINCT PX.PNAME
     FROM   P AS PX
     WHERE  NOT EXISTS
          ( SELECT *
            FROM   P AS PY
            WHERE  PY.CITY = 'Paris'
            AND    PY.WEIGHT = PX.WEIGHT )

Incidentally, that DISTINCT is really needed in the opening SELECT clause here! Here’s the result: ...