Download Database Systems Homework 4: Query Execution and Cost Estimation and more Assignments Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Spring 2007
HW
Due: 1:55pm CST, 4/26/
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.
Problem 1 Query Execution 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.E Plan 2: 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 C for S to retrieve all of the tuples s of S such that s.C=r.C 4: For each of these tuples s, if s.B=r.B, output r.A, r.B, r.C, s.B, s.C, s.D, s.E
(a) Analyze the above two plans carefully in terms of their behavior regarding accesses to disk, and explain which of the plans is better. Be sure to include in your analysis which accesses to disk are sequential accesses and which ones are random accesses. (b) Suggest an alternative plan that is better than the above two in terms of disk access? You do not need to submit detail computations for this question. Briefly explain your assumptions and your alternative plan and argue why your plan is better.
Problem 3 Algebraic Laws Suggested reading: Chapter 16.
Exercise 16.2.2 part b) c) d) (Page 809)
Problem 4 Dynamic Programming Suggested reading: Chapter 16.
Compute the optimal plan for R ./ S ./ T ./ U using the dynamic programming algorithm, assuming the following: R (a,b,c), S(b,c,d), T(b,d,e), U(e,f) T(R) =1000, T(S) = 500, T(T) = 250, T(U) = 125 V(R,c) = V(S,c)=10, V(S, b)= V(R,b)= V(T,b)= V(S, d)= V(T,d) =20, V(T,e)=V(U,e)= The cost of a join is estimated to be the cost of the subplans plus the size of the intermediate results (the same as the cost model we have in Lecture). (a) Please draw the table for dynamic programming, to show how you compute the optimal plan for all possible join orders allowing all trees. (You only need to show columns with two or more joins) (b) Please draw the table for dynamic programming, to show how you compute the optimal plan for all possible join orders allowing left-deep trees only. (You only need to show columns with two or more joins)
Problem 5 Cost Estimation Suggested reading: Chapter 16.
Consider two relations R(a,b,c) and S(c,d,e) with the following statistics: B(R)=100; T(R) = 1000; V(R,a) = 20; V(R,b) = 10; V(R,c)= B(S)= 500; T(S)= 1500; V(S,c)=30; V(S,d)=10; V(S,e)= (a) Estimate the number of tuples in σa=10(R) (b) Estimate the number of tuples in σc=10(R) (c) Estimate the number of tuples in σ(b=10)∧(c=20)(R) (d) Estimate the number of tuples in σb< 25 (R) (e) Estimate the number of tuples in σ(b=25)∨(c>5)(R) (g) Estimate the number of tuples in R ./ S (h) Estimate the number of tuples in R ./R.c=S.c AN D R.b=S.d S
Problem 6 Failure Recovery Suggested reading: Chapter 17
Consider the following transaction log from the start of the run of a database system that is using undo/redo logging with (Nonquiescent) Checkpointing for crash recovery.
- <T1,X, 20,10>
- <T1,Y, 40,0>
- <T1,X, 50,20>
- <T2,Z,30,20>
- <T3,U,60,30>
- <T2,V,50,25>
- <start ckpt(T2, T3)>
- <T2,Z, 45, 30>
- <T4,W, 80, 10>
- <T4,W,100,80>
What is the value for the data items X, Y, Z, U, V and W on disk after recovery: (a) if the system crashes just before line 13 is written to disk? (b) if the system crashes just before line 14 is written to disk? (c) if the system crashes just before line 19 is written to disk?
