SQL and Relational Theory, 2nd Edition

SUMMARIZATION

Definition: Let relations r1 and r2 be such that r2 has the same heading as some projection of r1, and let the attributes of r2 be A, B, ..., C. Then the summarization SUMMARIZE r1 PER (r2) : {X := summary} is a relation with (a) heading the heading of r2 extended with attribute X and (b) body the set of all tuples t such that t is a tuple of r2 extended with a value x for attribute X. That value x is computed by evaluating summary over all tuples of r1 that have the same value for attributes A, B, ..., C as tuple t does. Observe that the result has cardinality equal to that of r2 and degree equal to that of r2 plus one. The type of X in that result is the type of summary. Note: I assume for simplicity that relations r1 and r2 don’t already have an attribute named X.

Here’s an example (which I’ll label SX1—“SUMMARIZE Example 1”—for purposes of subsequent reference):

     SUMMARIZE SP PER ( S { SNO } ) : { PCT := COUNT ( PNO ) }

Given our usual sample values, the result looks like this:

SNO	PCT
S1	6
S2	2
S3	1
S4	3
S5	0

In other words, the result contains one tuple for each tuple in the PER relation—i.e., one tuple for each of the five supplier numbers, in the example—extended with the corresponding count.

Aside: Note carefully that the “summary” COUNT(PNO)—I deliberately call it a “summary” and not an expression, because it isn’t an expression (at least, not in the usual Tutorial D sense)—in the foregoing SUMMARIZE is not an invocation of the COUNT aggregate operator. That aggregate operator ...