CS-A1153 - Databases (summer course), 04.06.2020-03.09.2020

Consider the relation RoomBooking( event, room, date, numberOfPeople). It is part of a larger database back-end for an organizer of company events, and tracks room reservations for a specific event. event is the event ID, room is the room ID, and date the date of the reservation (for simplicity let this be full day events only), while numberOfPeople is an integer value tracking the number of registered attendants for that particular booking. Assume that for the date ’2020-09-03’ there is only a single tuple present in the table:

(’S-20’, ’A2’, ’2020-09-03’, 75)

At this stage there are two partially concurrent transactions:

T1 with the following operations:

1. Update tuple with key (’S-20’, ’A2’, ’2020-09-03’) setting numberOfPeople to 88,
2. Calculate the total number of people for all rooms and events on date ’2020-09-03’,
3. If total > 200 then rollback (i.e. change the value of numberOfPeople of the updated tuple back to 75 and end the transaction) else commit.

T2 with the following operations:

1. Insert a new tuple (’S-21’, ’B5’, ’2020-09-03’, 120) into RoomBooking,
2. Calculate the total number of people for all rooms and events on date ’2020-09-03’,
3. If total > 200 then rollback (i.e. remove the newly inserted tuple from RoomBooking and end the transaction) else commit.

The operations within a particular transaction happens in the order given, and a single operation is considered atomic.

It is not known in which order the transactions start or finish in relation to each other, only that they can be considered concurrent.

No triggers or constraints which would affect the execution of T1, and T2 are active on the database.

There are no other active transactions which would update the data for date ’2020-09-03’.

Now, assume that transaction T1 suffer an interruption between steps 2 and 3, while transaction T2 completes all of its steps. Provide answers for the following three (independent) scenarios:

a) (3 p) Let the ACID constraints hold for the database transactions. Which of the following end results are possible for date ’2020-09-03’ after T1 and T2 are executed? Give the letter or letters corresponding to all options that apply as your answer.

• X Room A2 has 88 people; There is no entry for Room B5
• Y Room A2 has 88 people; Room B5 has 120 people
• Z Room A2 has 75 people; There is no entry for Room B5
• W Room A2 has 75 people; Room B5 has 120 people

b) (2 p) Let the ACID properties, except for atomicity, hold for the database transactions. Which of the following end results are possible for date ’2020-09-03’ after T1 and T2 are executed? Give the letter or letters corresponding to all options that apply as your answer.

• X Room A2 has 88 people; There is no entry for Room B5
• Y Room A2 has 88 people; Room B5 has 120 people
• Z Room A2 has 75 people; There is no entry for Room B5
• W Room A2 has 75 people; Room B5 has 120 people

c) (2 p) Let the ACID properties hold otherwise, except that isolation is not guaranteed, for the database transactions. Which of the following end results are possible for date ’2020-09-03’ after T1 and T2 are executed? Give the letter or letters corresponding to all options that apply as your answer.

• X Room A2 has 88 people; There is no entry for Room B5
• Y Room A2 has 88 people; Room B5 has 120 people
• Z Room A2 has 75 people; There is no entry for Room B5
• W Room A2 has 75 people; Room B5 has 120 people