This preview shows pages 1–3. Sign up to view the full content.
This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: CS411 Database Systems Spring 2007 HW #4 Due: 1:55pm CST, 4/26/07 Note: Print your name and NetID in the upper right corner of every page of your submission. Handin your homework to Trisha Benson in 4322 SC. In case Trisha is not in office, slide your homework under the door. To grade homeworks faster, The homework is partitioned into three parts. Please, submit each part separately. For each part, make sure to write down your name and NetID. This homework is partitioned into 3 parts as follows: Part 1: Problem 1 - Problem 2 Part 2: Problem 3 - Problem 4 Part 3: Problem 5 - Problem 6 There is a bonus credit of 2% if your homework is formatted correctly: Each part must be separable so that it can be independently graded. If you submit a paper copy then each part should i) be separately stapled and ii) include your netid. For I2Cs students: If you submit an electronic copy (or fax) then each part should start on a new page and include your netid at the top of each page. Handwritten submissions will be graded but they will take longer to grade. For clarity, machine formatted text is preferable: Expect to lose points if your handwritten answer is unclear or misread by the grader. 1 Problem 1 Query Execution [20 points] Suggested reading: Chapter 15 Suppose you have 2 relations, R(A, B, C) and S(B, C, D, E). You have a clustered unique (no duplicate keys) B+-tree index on attribute A for relation R. Assume this index is kept entirely in memory (i.e., you do not need to read it from disk). For relation S, you have two indexes: (i) a non-clustered non-unique B+-tree index for attribute B, and (ii) a clustered non-unique B+-tree index for attribute C. Assume that these two indexes are also kept in memory. Also, assume that all of the tuples of S that agree on attribute C are stored in sequentially adjacent blocks on disk (that is, if more than one block is needed to store all of the tuples with some value of C, then these blocks will be sequentially located on the disk). Other relevant data: 2000 tuples of R are stored per block on disk. T(R) = 2,000,000 (number of tuples of R) 200 tuples of S are stored per block on disk. T(S) = 200,000 (number of tuples of S) V(B,S)=40,000 (number of distinct values of attribute B in S) V(C,S)=800 (number of distinct values of attribute C in S) You want to execute the following query: SELECT * FROM R, S WHERE (R.B=S.B) AND (R.C=S.C) We present you with two query plans: Plan 1: 1: For every block BL of R, retrieved using the clustered index on A for R 2: For every tuple r of BL 3: Use the index on B for S to retrieve all of the tuples s of S such that s.B=r.B 4: For each of these tuples s, if s.C=r.C, output r.A, r.B, r.C, s.B, s.C, s.D, s.EFor each of these tuples s, if s....
View Full Document
- Spring '07