CS 377 - Database Systems

Hw8: Query Processing and Transaction Management

Out: Wednesday, November 16

Due: Wednesday, November 23, 11:59pm



1)    Consider three relations, R1(A,B,C), R2(C,D,E), R3(E, F, A). We want to estimate the cost (number of disk IO’s) in answering the query R1 ./ R2 ./ R3 where ./ denotes natural join. Assume that we only consider a hash join algorithm (not a hybrid join), as described in class, and assume there is enough memory for running hash join. Also assume pipelined processing is used. We have the following information:


• R1 contains 10,000 tuples, stored contiguously in 1,000 blocks.

• R2 contains 20,000 tuples, stored contiguously in 2,000 blocks.

• R3 contains 30,000 tuples, stored contiguously in 3,000 blocks.

• If we compute the partial result R1 ./ R2, it would contain 1,500 tuples that if stored on disk would occupy 300 blocks.

• If we compute the partial result R2 ./ R3, it would contain 1,000 tuples that if stored on disk would occupy 200 blocks.

• If we compute the partial result R1 ./ R3, it would contain 500 tuples that if stored on disk would occupy 100 blocks.


a)    Briefly describe the best query plan (order of joins) to compute the query and why.

b)    How many disk block accesses (both reading and writing) are required for this plan? You don’t have to count the cost for writing the final join results. Briefly explain how you obtain your answer or show each cost component.


2)    Examine the schedule given below. There are three transactions, T1, T2, and T3. Initially, the salary = 2 and the tax = 1. Assume immediate update and undo logging with nonquiescent checkpointing are used. The assignments happen within the memory space of the transactions and the updates are not reflected in the database until the WRITE operation.



       T1                                       T2                                             T3             


0                                                                                                 start

1                                                                                                 READ tax

2                                                                                                 tax := tax + 1

3     start

4     READ salary

5     salary := salary + 1

6                                                                                                 WRITE tax

7                                                                                                 commit

8   ------------------------------ checkpoint start -------------------------------

9                                                start

10                                              READ tax

11                                              READ salary

12                                              tax := tax + salary

13                                              WRITE tax

14                                              commit

15   READ tax

16   tax := tax + salary

17   WRITE salary

18   commit


a)    Show the undo log file entries (including checkpoint entries) that would be generated by this execution.  For each log entry, indicate which line above generates it.

b)    Assume the system crashes immediately after statement 7. What are the values of salary and tax in the database? (Assume that all the log records up to this point are on disk.) Which transactions would have to be undone? What are the values of salary and tax in the database after the recovery?

c)    Assume the system crashes just after statement 13. Redo question b) above.

d)    Assume the system crashes just after statement 17. Redo question b) above.



3)    Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below.


T1: r1(x); r1(z); w1(x)

T2: r2(z); r2(y); w2(z); w2(y)

T3: r3(x); r3(y); w3(y)


S1: r1(x); r2(z); r1(x); r3(x); r3(y); w1(x); w3(y); r2(y); w2(z); w2(y)

S2: r1(x); r2(z); r3(x); r1(z); r2(y); r3(y); w1(x); w2(z); w3(y); w2(y)


a)    Draw the serializibility (precedence) graphs for S1 and S2 and state whether each schedule is conflict serializable. If a schedule is serializable, write down the equivalent serial schedule(s).


·           Please place your solution in your cs377 directory ~/cs377/hw8/hw8.pdf