---

CS554 - Homework 7

---

• 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 previous 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:

    Extra page for Question 4

  ---

  ---