CS 178: Homework 6 – Due April 15, 12 noon.

 

Submit using blackboard. No hardcopy submissions allowed.

 

Ques.1:: Explain how the relational algebra equivalence σP(r1) U σP(r2) = σP (r1 U  r2) can be applied to improve efficiency of certain queries (symbol U stands for  Union).



Ques.2: Let relations r1(A,B,C) and r2(C,D,E) have following properties:

 

    •      r1 has 5,000 tuples
    •      r2 has 10,000 tuples
    •      10 tuples of r1 fit on one block
    •      25 tuples of r2 fit on one block
    • disk block addresses require 10 bytes, search key field requires 10 bytes and disk blocks are 1024 bytes.


Estimate the number of disk block accesses required using each of the following join strategies for (r1 JOIN r2) – note that the join attribute is C since it is common to both relations:

(a) simple block based iteration

(b) sort-merge join

(c) hash-join

Equality search ( x=A) on r1 using a clustered B-tree index on r1.

Equality search (A=x) on r1 using a Hash index, with 1000 buckets.

Ques.3: Consider the SQL query:

            SELECT B

            FROM r1, r2

            WHERE (r1.A = ‘Smith’) and (r1.C = r2.C);

Determine the different access plans (i.e., methods to compute the query) and their efficiency (i.e., time in terms of I/O operations) to execute the above query. The relations r1,r2 are the relations specified in Question 2 and assume you have the choice of (i) hash index on any attribute in relation r1, (ii) clustered B+ tree index on any attribute in relation r1, (iii) unclustered B+tree index on any attribute in relation r1, and (iv) sorting the file on any attribute in relation r1.