SQL and Relational Theory, 2nd Edition

IMAGE RELATIONS bis

In this section, I just want to present a series of examples that show the usefulness of image relations in connection with aggregate operators as discussed in the previous section.

Example 1: Get suppliers for whom the total shipment quantity, taken over all shipments for the supplier in question, is less than 1000.

     S WHERE SUM ( !!SP , QTY ) < 1000

For any given supplier, the expression SUM(!!SP,QTY) denotes, precisely, the total shipment quantity for the supplier in question. An equivalent formulation without the image relation is

     S WHERE SUM ( SP MATCHING RELATION { TUPLE { SNO SNO } } , QTY ) < 1000

Here for interest is an SQL “analog”—“analog” in quotes because actually there’s a trap in this example; the SQL expression shown is not quite equivalent to the Tutorial D expressions shown previously (why not?):

     SELECT S.*
     FROM   S , SP
     WHERE  S.SNO = SP.SNO
     GROUP  BY S.SNO , S.SNAME , S.STATUS , S.CITY
     HAVING SUM ( SP.QTY ) < 1000

Incidentally, I can’t resist pointing out in passing that (as this example suggests) SQL lets us say “S.*” in the SELECT clause but not in the GROUP BY clause, where it would make just as much sense.

Example 2: Get suppliers with fewer than three shipments.

     S WHERE COUNT ( !!SP ) < 3

Example 3: Get suppliers for whom the minimum shipment quantity is less than half the maximum shipment quantity (taken over all shipments for the supplier in question in both cases).

     S WHERE MINX ( !!SP , QTY , 0 ) < 0.5 * MAXX ( !!SP , QTY , 0 )

Example 4: Get ...