Databases Reference
In-Depth Information
Note that this relation contains redundant information; for example,
we are told no less than three times that supplier S1 was able to supply some-
thing on day 6. The desired result, eliminating all such redundancy, is clearly
as follows (let us call it RESULT):
S#
DURING
S1
[d04, d10 ]
S2
[d02, d04 ]
S2
[d08, d10 ]
S3
[d08, d10 ]
S4
[d04, d10 ]
We call this result the coalesced form of T1 on DURING. Note that the
DURING value for a given supplier in this coalesced form does not necessar-
ily exist as an explicit DURING value for that supplier in the relation T1
from which the coalesced form is derived (see supplier S4 for an example).
Now, we will eventually reach a point where we can obtain this coa-
lesced form by means of a simple expression of the form
T1 COALESCE DURING
However, we need to build up to that point gradually.
Observe first of all that we were using the term coalesced form in the
previous two paragraphs in a sense slightly different from that in which we
used it in Section 5.7. The COALESCE operator as defined in that previous
section took a set of intervals as input and produced a set of intervals as
output. Here, however, we are talking about a different version—in fact, an
overloading—of that operator that takes a unary relation as input and pro-
duces another unary relation (with the same heading) as output, and it is the
tuples in those relations that contain the actual intervals.
Here, then, are the steps to take us from T1 to RESULT:
WITH ( T1 GROUP ( DURING ) ASX)AST2:
The GROUP operator is used here to nest the DURING values with
respect to S# values, such that each supplier number is paired with a set of
intervals instead of with a single interval.
