Answer to the questions
Budget: $10 – $30 USD
Consider the following query Q over R(A,B,C) and S(D,E,F):
SELECT A,F
FROM R, S
WHERE C=D AND A < 1000 AND E='e'
Assume there is a clustering index on E of S, an index on D of S, an index on C of R, and a clustering index on A of R. Assume the indices have equal height. Assume R and S have roughly equal sizes (in both number of blocks and number of records), and assume that σE='e'(S) is twice the size of σA<1000(R).
a Draw the canonical query tree for the query Q.
b Transform the canonical query tree for Q into a final query tree that is efficient to execute.
c Describe the best query evaluation plan (with minimal cost) for Q for the information given.
d Draw another left-deep query tree whose cost is higher than the one given in 6.c.
e Give one example instance of relational algebra rewriting, for each rewriting rule that was used in the trees of a and b
and
Consider a database containing two relations: R(A,B,C) and S(D,E,F).
For each of three (your choice) of the 12 rewriting rules for relational algebra expressions, give an example pair of relational algebra expressions involving R and S such that the rule rewrites one expression to the other expression, and give another example expression pair to show the rewriting rule cannot be used since some of the conditions for the rule are not satisfied.
SELECT A,F
FROM R, S
WHERE C=D AND A < 1000 AND E='e'
Assume there is a clustering index on E of S, an index on D of S, an index on C of R, and a clustering index on A of R. Assume the indices have equal height. Assume R and S have roughly equal sizes (in both number of blocks and number of records), and assume that σE='e'(S) is twice the size of σA<1000(R).
a Draw the canonical query tree for the query Q.
b Transform the canonical query tree for Q into a final query tree that is efficient to execute.
c Describe the best query evaluation plan (with minimal cost) for Q for the information given.
d Draw another left-deep query tree whose cost is higher than the one given in 6.c.
e Give one example instance of relational algebra rewriting, for each rewriting rule that was used in the trees of a and b
and
Consider a database containing two relations: R(A,B,C) and S(D,E,F).
For each of three (your choice) of the 12 rewriting rules for relational algebra expressions, give an example pair of relational algebra expressions involving R and S such that the rule rewrites one expression to the other expression, and give another example expression pair to show the rewriting rule cannot be used since some of the conditions for the rule are not satisfied.