Homework assignment #3
CS 643, Spring 2002
Due: Wednesday, Feb. 27, 2002
Total points: 100

In class, You're be asked to perform the following exercises with the tables in the HR schema. Save all transactions in "transaction.sql". Submit the file along with your homework report. Make sure that you give proper documentation of the transactions. This section can be shared by two students.

1. Write a transaction to update change the JOB_ID "AD_VP" into "TEMP_VP". Remember that there may be records in other tables that refer to "AD_VP". Make sure those reference be changed to "TEMP_VP" too. Do not use update cascade feature. 
2. Write a transaction that first gives all shipping clerks the lesser of 5% raise. Then, this transaction will try to give all stocking clerks the lesser of 3% raise. However, if the total raise exceed the budget of $5000, rollback the raise for all stocking clerks. 
3. Write a transaction that will insert three records into the REGIONS table. Define an exception to catch duplicate region names. You can implement this either through a user-defined exception or by creating an index with no duplicates on the region name field. Rollback all inserts if such exception is raised. 

For the homework assignment, you're asked to answer the following questions. It is very important that you use concrete examples to illustrate the concepts. This section needs to be individual work.

1. In the first transaction above, what happen in recovery if a media failure occurs when transaction is updating the JOB_HISTORY table.
2. For each of the problems listed in examples 19.1 - 19.3, describe a similar and meaningful scenario that could occur in the HR database. For each scenario, also include a table in the format similar to those of Figures 19.4 - 19.6 to illustrate the problem.
3. Exercise 19.2.