---

CS554 - Homework 6

---

• Question 1 (Exercise 16.1.3a)
  • Give the parse tree for the following query about relations R(a,b) and S(b,c): (10 pts)

    SELECT a , c FROM R, S WHERE R.b = S.b;

    Answer:

    ---

  • Given the grammar:

    Expr ::= Term | Expr + Term | Expr - Term Term ::= Factor | Term * Factor | Term / Factor Factor ::= number | identifier

    Show the parse tree for the following expression: (15 pts)

    a - 3 * b + 7

    Answer:

  ---

  ---

  ---

  ---

• Question 2 (Exercise 16.3.2a) (25 pts)
  • Give the initial query plan (= relational algebra tree) for the following query about relations R(a,b) and S(b,c):

    SELECT a , c FROM R, S WHERE R.b = S.b;

    Answer:

  ---

  ---

  ---

  ---

• Question 3 (Exercise 16.3.2b)
  • Give the initial query plan (= relational algebra tree) for the following query about relations R(a,b) and S(b,c) using a two-argument selection operation: (10 pts)

    SELECT a FROM R WHERE b IN (SELECT a FROM R, S WHERE R.b = S.b)

    Answer:

    ---

    ---

  • Give the initial query plan (= relational algebra tree) for the following query about relations R(a,b) and S(b,c) without using a two-argument selection operation: (15 pts)

    Answer:

  ---

  ---

  ---

  ---

• Question 4 (Exercise 16.4.1)         (5 pts each)
  • Given the statistics of 4 relations:

    W(a, b) X(b, c) Y(c,d) Z(d,e) =============================================================== T(W) = 100 T( X ) = 200 T(Y) = 300 T(Z) = 400 V(W,a) = 20 V(X,b) = 50 V(Y,c) = 50 V(Z,d) = 40 V(W,b) = 60 V(X,c) = 100 V(Y,d) = 50 V(Z,e) = 100

    Give an estimate for the size of the following results:

    W ⋈ X ⋈ Y ⋈ Z              σc=20(Y) ⋈ Z σa=1 AND b=2(W) W × Y X ⋈X.c < Y.c Y

    Solutions:

    1. W ⋈ X ⋈ Y ⋈ Z T(W) * T(X) T(Y) T(Z) ------------------- * ----------------- * ------------------ max(V(W,b),V(X,b)) max(V(X,c),V(Y,c)) max(V(Y,d),V(Z,d)) 100 * 200 300 400 = ------------ * ------------- * ------------ max(60,50) max(100,50) max(50,40) 100 * 200 300 400 200 * 300 * 400 = ------------ * ----- * ----- = ------------------- 60 100 50 60 * 50 200 * 300 * 400 = ------------------- = 20 * 400 = 8000 3000

    2. σc=20(Y) ⋈ Z Naive way: T(σc=20(Y)) = 1/V(Y,c) * T(Y) = 1/50 * 300 = 6 T(σc=20(Y)) * T(Z) size( σc=20(Y) ⋈ Z ) = ------------------------- max(V(σc=20(Y),d),V(Z,d)) 6 * 400 = -------------- max(6,40) 6 * 400 = ---------- = 6 * 10 = 60 (incorrect: -2 pts) 40 Better way: σc=20(Y) ⋈ Z = σc=20( Y ⋈ Z ) T(Y) * T(Z) 300 * 400 T( Y ⋈ Z ) = -------------------- = ------------ max(V(Y,d),V(Z,d)) max(50,40) 300 * 400 = ------------ = 300 * 8 = 2400 50 T( σc=20( Y ⋈ Z ) ) = 1/V(Y,c) * T( Y ⋈ Z ) = 1/50 * 2400 = 48

    3. σa=1 AND b=2(W) 1 1 T(σa=1 AND b=2(W)) = ------- * ------- * T(W) V(W,a) V(W,b) = 1/20 * 1/60 * 100 = 1 (or 0)

    4. W × Y T(W &tims; Y) = T(W) * T(Y) = 100 * 300 = 30,000

    5. X ⋈X.c < Y.c Y = σX.c < Y.c( X × Y ) = 1/3 * T(X) * T(Y) = 1/3 * 200 * 300 = 200 * 100 = 20,000 (The approximation can be improved IF it was given that the set of values assumed by X.c and Y.c were ORDERED: X.c assumes: c1 < c2 < c3 .... < C50 < C51 < .... < C100 Y.c assumes: c1 < c2 < c3 .... < C50 The question of text book apparently did not make this assumption )