Database Reference
In-Depth Information
a
Product
Key
Time
Key
Max
Quantity
Count
p2
t2
150
4
p3
t3
100
5
p4
t4
50
6
p5
t5
150
5
p6
t6
100
3
p7
t7
100
2
b
c
Product
Key
Time
Key
SD Max
Quantity
SD Count
Product
Key
Time
Key
Max
Quantity
Count
p1
t1
100
2
p1
t1
100
2
p2
t2
100
2
p2
t2
150
4
p3
t3
100
2
p3
t3
100
7
p4
t4
100
2
p4
t4
100
2
p5
t5
100
-2
p5
t5
150
5
p6
t6
100
-2
p6
t6
100
1
p7
t7
100
-2
p7
t7
?
?
Fig. 7.5
An example of the propagate and refresh algorithm with aggregate function
MAX
.(
a
)Originalview
DailySalesMax
.(
b
) Summary-delta table
SD DailySalesMax
.
(
c
) Updated view
DailySalesMax
SELECT ProductKey, TimeKey, Quantity, COUNT(*)
FROM
Δ
+
(
Sales
)
S1
WHERE Quantity = (
SELECT MAX(Quantity)
FROM
Δ
+
(
Sales
)
S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.TimeKey = S2.TimeKey )
GROUP BY ProductKey, TimeKey
UNION ALL
SELECT ProductKey, TimeKey, Quantity, -1 * COUNT(*)
FROM
Δ
−
(
Sales
)
S1
WHERE Quantity = (
SELECT MAX(Quantity)
FROM
Δ
−
(
Sales
)
S2
WHERE S1.ProductKey = S2.ProductKey AND
S1.TimeKey = S2.TimeKey )
GROUP BY ProductKey, TimeKey )
Finally, Fig.
7.5
c shows the view after the update. Let us consider first
theinsertions.Thetuplefor
p1
in the summary-delta table does not have a
corresponding tuple in the view, and thus, it is inserted in the view. The tuple
for
p2
in the summary-delta table has a maximum value smaller than that in
the view so the view is not modified. The tuple for
p3
in the summary-delta
Search WWH ::
Custom Search