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: ...
Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.