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