Questions 1 - 8 below relate to the database of the Anywhere Travel Agency. The agency offers a variety of tours to locations all over the world. One of the tables in the database stores details about tour packages, including the total number of bookings (TOUR_RSV) for each.
| TOUR_CODE | TOUR_RSV |
|---|---|
| AU1234 | 10 |
| EG3872 | 18 |
| FR0O92 | 25 |
| BC1298 | 5 |
Table 1: Initial database values.
1. The following table shows the execution of two transactions for the tour agency's database. Using the initial database values shown in Table 1, complete the following table by showing the stored value for each Read and Write step.
| Time | Transaction | Step | Stored Value |
|---|---|---|---|
| 1 | T1 | Read TOUR_RSV for TOUR_CODE = 'EG3872' | |
| 2 | T1 | TOUR_RSV = TOUR_RSV + 1 | |
| 3 | T1 | Write TOUR_RSV for TOUR_CODE = 'EG3872' | |
| 4 | T1 | *****ROLLBACK***** | |
| 5 | T2 | Read TOUR_RSV for TOUR_CODE = 'EG3872' | |
| 6 | T2 | TOUR_RSV = TOUR_RSV + 2 | |
| 7 | T2 | Write TOUR_RSV for TOUR_CODE = 'EG3872' |
2. Do the transactions shown in Question 1 create any data integrity or consistency problems? If so, identify the type of problem and explain why it occurred.
3. The following table shows the execution of two transactions for the tour agency's database. Using the initial database values shown in Table 1, complete the following table by showing the stored value for each Read and Write step.
| Time | Transaction | Step | Stored Value |
|---|---|---|---|
| 1 | T1 | Read TOUR_RSV for TOUR_CODE = 'FR0092' | |
| 2 | T1 | TOUR_RSV = TOUR_RSV + 5 | |
| 3 | T2 | Read TOUR_RSV for TOUR_CODE = 'BC1298' | |
| 4 | T1 | Write TOUR_RSV for TOUR_CODE = 'FR0092' | |
| 5 | T2 | TOUR_RSV = TOUR_RSV + 2 | |
| 6 | T2 | Write TOUR_RSV for TOUR_CODE = 'BC1298' |
4. Do the transactions shown in Question 3 create any data integrity or consistency problems? If so, identify the type of problem and explain why it occurred.
5. The following table shows the execution of two transactions for the tour agency's database. Using the initial database values shown in Table 1, complete the following table by showing the stored value for each Read and Write step.
| Time | Transaction | Step | Stored Value |
| 1 | T1 | Read TOUR_RSV for TOUR_CODE = 'AU1234' | |
| 2 | T1 | TOUR_RSV = TOUR_RSV - 3 | |
| 3 | T2 | Read TOUR_RSV for TOUR_CODE = 'AU1234' | |
| 4 | T1 | Write TOUR_RSV for TOUR_CODE = 'AU1234' | |
| 5 | T2 | TOUR_RSV = TOUR_RSV + 2 | |
| 6 | T2 | Write TOUR_RSV for TOUR_CODE = 'AU1234' |
6. Do the transactions shown in Question 5 create any data integrity or consistency problems? If so, identify the type of problem and explain why it occurred.
For problems 7 and 8, assume that the Anywhere Travel Agency database is a multiuser database with a large number of real-time users executing simultaneous transactions
7. Which technique should be used to control deadlocks in the Anywhere Travel Agency database: deadlock prevention, deadlock detection, or deadlock avoidance? Explain your answer.
8. What lock granularity would you recommend for the Anywhere Travel Agency database: database-level, table-level, page-level, row-level, or field-level? Explain your answer.
9. In the transactions shown in Question 5, what would happen if the DBMS used row-level exclusive locking? Assume locks are released after a transaction updates the row.
10. Explain why transaction management is not as important in a single-user database, such as Microsoft Access.