Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 277

All 1

A 20 B 40 C 60 D 80

AB 600 AC 500 AD 400 BC 300 BD 200 CD 100

ABC 2000 ABD 8000 ACD 3000 BCD 5000

ABCD 10000

Fig. 7.18 A data cube lattice

7.5 By means of examples, explain the propagate and refresh algorithm for
the aggregate functions AVG , MIN ,and COUNT . For each aggregate
function, write the SQL command that creates the summary-delta
table from the tables containing the inserted and deleted tuples in the
fact table, and write the algorithm that refreshes the view from the
summary-delta table.
7.6 Suppose that a cube Sales ( A , B , C , D , Amount ) has to be fully materi-
alized. The cube contains 64 tuples. Sorting takes the typical n log( n )
time. Every GROUP BY with k attributes has 2 k tuples:

(a) Compute the cube using the PipeSort algorithm.
(b) Compute the gain of applying the PipeSort compared to the cost
of computing all the views from scratch.

7.7 Consider the graph in Fig. 7.18 , where each node represents a view and
the numbers are the costs of materializing the view. Assuming that the
bottom of the lattice is materialized, determine using the view selection
algorithm the five views to be materialized first.
7.8 Consider the data cube lattice of a three-dimensional cube with
dimensions A , B ,and C . Extend the lattice to take into account the
hierarchies A → A 1 → All . Since the lattice

is complex to draw, represent it by giving the list of nodes and the list
of edges.
7.9 Modify the algorithm for selecting views to materialize in order to
consider the probability that each view has to completely match a given
query. In other words, consider that you know the distribution of the

All and B → B 1 → B 2 →