DATABASE QUESTIONS.

DATABASE QUESTIONS.

1. Consider what happens with the locks in order to get each of the following SQL isolation levels:

(a) READ UNCOMMITTED

(b) READ COMMITTED

(c) REPEATABLE READ

(d) SERIALIZABLE

For each of the above, you have to

(i) Briefly explain what "locking policies" the DBMS enforces to get that isolation level.

(ii) Briefly explain why this locking policy ensures that particular isolation level

(iii) Briefly explain why this locking policy does not ensure the next isolation level. (you don't have to do thisfor the SERIALIZABLE part).

2.

(a) For this part of the problem, you can assume we are using the immediate update protocol we saw that

UNDOs have to be done right to left i.e. later writes are undone first and then earlier writes.

Give anexample to show why it should be done this way by doing the following:

i. Find a simple example where it does make a difference whether the UNDOs are done left to right orright to left.

ii. Show what the results will be if the UNDOs are done right to left.

iii. Show what the results will be if the UNDOs are done left to right.

(b) For this part of the problem, you can assume we are using the immediate update protocol we saw thatREDOs have to be done left to right i.e. earlier writes are redone first and then later writes. Give anexample to show why it should be done this way by doing the following:

i. Find a simple example where it does make a difference whether the REDOs are done left to right orright to left.

ii. Show what the results will be if the REDOs are done left to right.

iii. Show what the results will be if the REDOs are done right to left.

(c) Explain why is it that if we are using the immediate update protocol we need to keep the old value (BFIM)of an item along with the new value (AFIM), but if we are using deferred update protocol, we need to onlykeep the new value.

3. Suppose that we use the immediate update protocol for the schedule given as the last slide of theweek 4 power point notes.

(a) Show what will be the steps taken at the time of the recovery process i.e. specify, in the correct order, whatare all the operations (undo and/or redo) which are carried out by showing what changes are made. Noadditional explanation is needed.

(b) What are the final values of A and B; no explanation needed.

4. This problem is based on tables T1 and T2 from Elmasri Figure Tables 6.15 ;

For each of the following relational algebra queries, show the output table (no explanations required, just show the table):

5. This problem is based on Elmasri's company database. For each of the following, specify the queryusing relational algebra:

(a) Find the SSN of employees who work on Project number 27.

(b) Find the names of employees who do not work in the Accounting department.

(c) Find the name of the employee supervising Michael Green.

(d) Find the names of employees who work on ProjectX and who do not have a dependent.

(e) Find the names of employees who work on every project.

(f) For every employee who has a son, find the name and salary of the employee, and the names of thedepartment the employee works for.

Click Here To Get More on This Topic......