Databases Reference
In-Depth Information
You can think of this result being produced as follows (see later for a formal definition):
First, the expression SP GROUP ({PNO,QTY} AS PQ) is evaluated, to produce an intermediate
result
spq
, say; then, in each tuple in
spq
, the PQ value—which is a relation, of course—is
replaced by a TQY value, that TQY value being obtained by computing the “summary”
SUM(QTY) on that PQ relation.
Aside:
In fact the foregoing SUMMARIZE invocation is logically equivalent to a certain
EXTEND invocation—to be specific, the invocation EXTEND SP{SNO}:{TQY :=
SUM(‼SP,QTY)}, where ‼SP denotes a certain image relation—and there are reasons to
prefer this EXTEND formulation over its SUMMARIZE equivalent. Again, however,
detailed discussion of such matters is beyond the scope of this topic. Refer to
SQL and
Relational Theory
for further explanation.
End of aside
.
Here now is another example (“for each supplier, compute the sum of
distinct
shipment
quantities”):
SUMMARIZE SP { SNO , QTY } BY { SNO } : { TQY := SUM ( QTY ) }
This expression denotes a summarization of a certain projection of SP—the projection on SNO
and QTY, to be specific—and that projection contains, for each supplier number, just the
pertinent distinct shipment quantities, as required.
6
Note:
Given the sample value of SP in Fig.
12.1, the expression overall happens to yield the same result as the previous example does, but it
won't do so in general, of course.
Here now is a definition of this form of SUMMARIZE:
7
Definition:
Let relation
r
have attributes called
A1
,
A2
,
...,
An
(and possibly others) and no
attribute called
B
. Then the expression SUMMARIZE
r
BY {
A1
,
A2
,...,
An
} : {
B
:=
summary
} returns a relation with heading {
A1
,
A2
,...,
An
,
B
} and body the set of all tuples
t
such that
t
is equal to the projection of some tuple of
r
on
A1
,
A2
,
...,
An
, extended with a
value
b
for
B
. That value
b
is computed by evaluating
summary
over all tuples of
r
that
have the same value for attributes
A1
,
A2
,
...,
An
as
t
does.
Aside:
I assume for the purposes of this topic that the notion of “evaluating a summary”
can be defined precisely.
8
I certainly don't want to attempt such a definition here—there
are certain complexities involved, complexities that (perhaps fortunately) have no bearing
6
As this example should be sufficient to suggest,
Tutorial D
has no need for—nor does it support anything analogous to—
SQL's ad hoc trick of allowing summaries to include the keyword DISTINCT.
7
Observe that this definition makes use of the tuple version of projection, also a tuple version of the relational EXTEND
operator. See
SQL and Relational Theory
for further discussion.
8
In the example, evaluating the summary SUM(QTY) actually requires invoking the SUM
aggregate operator
on the QTY
attribute of what I earlier referred to as “the PQ relation.” For further explanation, see
SQL and Relational Theory
.
