See Description

Name: _________________________________________ Date: __________________

Week 7 – Assignment #7: Chapters 20-22 Grade: 5% of Class Grade

CH20: PHYSICAL DATABASE DESIGN AND TUNING

#20.5 – Under what situations would denormalization of a database schema be used? Give examples of denormalization.

#20.8 – Illustrate the types of changes to SQL queries that may be worth considering for improving the performance during database tuning.

CH 21: INTRODUCTION TO TRANSACTION PROCESSING CONCEPTS and THEORY

#21.14 - Change transaction T 2 in Figure 21.2b to read:

read_item(X);

X:= X+M;

if X > 90 then exit

else write_item(X);

Discuss the final result of the different schedules in Figure 21.3 (a) and (b), where M = 2 and N = 2, with respect to the following questions. Does adding the above condition change the final outcome? Does the outcome obey the implied consistency rule (that the capacity of X is 90)?

#21.18 - How many serial schedules exist for the three transactions in Figure 21.8 (a)? What are they? What is the total number of possible schedules?

#21.20 - Why is an explicit transaction end statement needed in SQL but not an explicit begin statement?

CH 22: CONCURRENCY CONTROL TECHNIQUES

#22.22 - Prove that strict two-phase locking guarantees strict schedules.

#22.26 - Repeat Exercise 22.25, but use the multi-version timestamp ordering method.