Tutorial5 - sol

# Tutorial5 - sol - Problem 1 Query Execution Consider the...

This preview shows pages 1–3. Sign up to view the full content.

Problem 1: Query Execution Consider the following two relations: Students (sid, name, latest-course-id) TakeCourses (sid, cid, cname) where sid, cid, and cname are student id, course id, and course name, respectively. Students has 10,000 tuples, with 25 tuples fitting on a block. TakeCourses has 5,000 tuples, with 50 tuples fitting on a block. There is no index on any attribute of the relations. You may assume the tables are "clustered". (1) Suppose the memory buffer has 101 blocks. Compute the cost of using a block-nested loop join to join the above two relations. (2) Suppose we wanted to join the two relations using a block-nested loop join and limit the cost to 900. What is the smallest value M can be? (3) What is the cost of joining R1 and R2 using a hash-based join? Solution (1) B(S) = 10,000 / 25 = 400 B(T) = 5,000 / 50 = 100 Cost = B(T) + B(S) x ( B(T) / (M-1) ) = 100 + 400 x ( 100/(101-1) ) = 500 (2) B(S) = 10,000 / 25 = 400 B(T) = 5,000 / 50 = 100 B(T) + B(S) x ( B(T) / (M-1) ) 900 100 + 400*( 100/(M-1) ) 900 100/(M-1) 2 50 M -1 51 M So M must be at least 51 blocks. (3) 3B(R) + 3B(S) = 1,500

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Problem 2: Cost Estimation Consider a database with three relations, R1(A,B,C), R2(B,D), R3(C,E,F), with statistics: T(R1)=100 V(R1,A)=100 V(R1,B)=20 V(R1,C)=50 T(R2)=2,000 V(R2,B)=50 V(R2,D)=100 T(R3)=1,000 V(R3,C)=100 V(R3,E)=200 V(R3,F)=250 Assume that the data in every relation for each field is the set of integer values from 1 to the number of individual values specified for each field, evenly distributed. For instance, V(R1,B) = 20 implies that the B field in relation R1 has an even distribution of the integer values 1 to 20. For each query, estimate the number of tuples returned and briefly show your work:
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/17/2012 for the course CS 411 taught by Professor Winslett during the Spring '07 term at University of Illinois at Urbana–Champaign.

### Page1 / 5

Tutorial5 - sol - Problem 1 Query Execution Consider the...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online