---

CS554, Homework 6

---

• Question 1 (20 pts)
  • Given:

    The content of a relation R is as follows: d d d d .... d a a a a ... a c c c c .... c b b b b ....b ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^ ^^^^^^^^^^^^^^ ^^^^^^^^^^^^^ 100 tuples 100 tuples 100 tuples 100 tuples R has a ordered clustering index file on its tuples: (a b c d) The index file contains the record location of the first tuple with that given value

    We use the ordered index to access all tuples of relation R in a sorted manner as follows:

    Read index file to get the location of the tuple with the next smallest value Access all (clustering) tuples with the next smallest value

    Questions:

    If each data block can store exactly 100 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file) If each data block can store exactly 50 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file) If each data block can store exactly 51 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file) If each data block can store exactly 49 tuples, how many block accesses will this operation perform ? (ignore the block accesses to the index file)

  ---

  ---

  ---

  ---

• Question 2 (40 pts)
  • Given:

    R = R(A, B, C) // R has attributes A, B, C B(R) = 1000 block T(R) = 10000 tuples V(R, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 10000/100 = 100 for any value a S = S(A, D, E) // S has attributes A, D, E B(S) = 200 block T(S) = 4000 tuples V(S, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 4000/100 = 40 for any value a

    ---

    ---

  • What is the minimum memory requirement for the following algorithms:

    One-pass join R ⋈ S: The IO-efficient version of the Two-pass TPMMS based join R ⋈ S: The IO-intensive version of the Two-pass TPMMS based join R ⋈ S: Two-pass Hashing based join R ⋈ S: Three-pass Hashing based join R ⋈ S: Block-based nested loop join R ⋈ S: Suppose S has an ordered clustering index on it join attribute. (so you can use the Index join algorithm) What's the minimum # buffers needed for the Index join algorithm (you can ignore the buffers to read the index file): Suppose S has an ordered non-clustering index on their join attribute. What's the minimum # buffers needed for the Index join algorithm (you can ignore the buffers to read the index file): Suppose R and S both have a ordered clustering index on their join attribute. What's the minimum # buffers needed for the "zig-zag" join algorithm (you can ignore the buffers to read the index file): Suppose R and S both have a sorted non-clustering index on their join attribute. What's the minimum # buffers needed for the "zig-zag" join algorithm (you can ignore the buffers to read the index file):

  ---

  ---

  ---

  ---
• Question 3 (40 pts)
  • Given the same database:

    R = R(A, B, C) // R has attributes A, B, C B(R) = 1000 block T(R) = 10000 tuples V(R, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 10000/100 = 100 for any value a S = S(A, D, E) // S has attributes A, D, E B(S) = 200 block T(S) = 4000 tuples V(S, A) = 100 // We assume that # tuples with // attribute value A = a is equal to: // 4000/100 = 40 for any value a

    ---

    ---

  • What is the # disk I/O operations for the following algorithms:

    One-pass join R ⋈ S: The IO-efficient version of the Two-pass TPMMS based join R ⋈ S: The IO-intensive version of the Two-pass TPMMS based join R ⋈ S: Two-pass Hashing based join R ⋈ S: Three-pass Hashing based join R ⋈ S: Block-based nested loop join R ⋈ S using M = 101 buffers : Suppose S has an ordered clustering index on its join attribute. What's the I/O cost of the index join algorithm (you can ignore the I/O cost to read the index file): Suppose S has an ordered non-clustering index on its join attribute. What's the I/O cost of the Index join algorithm (you can ignore the I/O cost to read the index file): Suppose R and S are not sorted but have an ordered clustering index on their join attribute. What's the I/O cost of the "zig-zag" join algorithm: (you can ignore the I/O cost to read the index file) Suppose R and S are not sorted but have an ordered non-clustering index on their join attribute. What's the I/O cost of the "zig-zag" join algorithm: (you can ignore the I/O cost to read the index file)