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

Start Free Trial

No credit card required