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 shipments such that at least two other shipments involve the same quantity.

     SP WHERE COUNT ( !!( SP RENAME { SNO AS SN , PNO AS PN } ) ) > 2

This example is very contrived, but it illustrates the point that we might occasionally need to do some attribute renaming in connection with image relation references. In the example, the renaming is needed in order to ensure that the image relation we want, in connection with a given shipment tuple, is defined in terms of attribute QTY only. The introduced names SN and PN are arbitrary.

I remark in passing that the RENAME invocation in this example—

     SP RENAME { SNO AS SN , PNO AS PN }

—illustrates the “multiple” form of the RENAME operator. The individual renamings in such a RENAME invocation are effectively executed in parallel. Note: As a consequence of this fact, a RENAME of the following form can be used to switch the names of the specified attributes:

     R RENAME { A AS B , B AS A }

Similar “multiple” forms are defined for various other operators, too, including EXTEND in particular (I’ll give an example later).

Example 5: Update suppliers for whom the total shipment quantity, taken over all shipments for the supplier in question, is less than 1000, reducing their status to half its previous value.

     UPDATE S WHERE SUM ( !!SP , QTY ) < 1000 : { STATUS := 0.5 * STATUS } ;