CS634 – Homework 6

 

Instructions: The homework is due in class on Monday, May 9. Please prepare paper copies (either typeset or hand-written copies are fine, as long as the hand writing is clear).

 

Question 1 Transaction Rollback

Exercise 18.4 in the textbook.  Read the solution to 18.3 to provide a model for your solution of part 2.

 

Question 2 Crash Recovery

Exercise 18.5 in the textbook. Read the solution manual for the most complicated case (two crashes during recovery) for format and getting started.

a.       First show the recovery that would occur if there were no crashes during recovery.

b.      Then show the recovery that would occur if there is one crash during recovery, after the first recovery wrote two records

 

Question 3 OLAP Cross-tabs

a. Exercise 25.2. This pivot is to a cross-tabs display on pid and timeid, represented by their pnames and year numbers. Assume timeid=1 is 1995, timeid=2 is 1996, and timeid 3 is 1997, to be consistent with Figure 25.5, which is your model of the right format for the answer here. (For some reason, the time dimension table is not given in the book.)

b. Exercise 25.3 (parts 1,3)

 

Question 4 Bitmap Indexes

a.       Exercise 25.6. First fix Figure 25.9 by giving Woo custid 122, a proper unique value.  The RIDs here (page#, row# in file) are (0,0), (0,1), (0,2), (0,3), so that the rows are laid out on one page, with row numbers in the expected order (their placement on the page is arbitrary), and have row indexes = 0, 1, 2, and 3.

Sample question for 26(b): How many customers have rating 1?  Answer: count 1's in bitmap for rating 1.

b.      Exercise 25.8, part 2 only

Start of answer: for key = 'Madison', bitvector = 1111111110000000000 (one bit for each row in Sales, assumed to have row indexes in order shown in Fig. 25.2.)

 

Question 5  Views and Materialized Views

Exercise 25.9 (parts 1 and 2)

Part 1. Assumes NumReservations is an ordinary view, not materialized, so you need to show SQL for the base tables.

Part 2. Here NumReservations is an ordinary view or a materialized view.  Diagram or discuss in detail the query plans for both possibilities and the pros and cons of each.