[SOLVED] R SQL database 1. Assume that a DBMS has the transactions Q, R, S and T with the timeline depicted in the figure being executed.

1.Assume that a DBMS has the transactions Q, R, S and T with the timeline depicted in the figure being executed.
You can also assume that:
Transaction Q updates the value of x from 0 to 20
Transaction R updates the value of y from 1 to 5
Transaction S updates the value of z from 8 to 12
Transaction T updates the value of w from 2 to 20
A checkpoint is written at timepoint t4
The DBMS crashes at timepoint t6.

A.Assume that the DBMS uses immediate update protocol: Updates from transactions are applied to the database as soon as possible. A log record is written before the database update, and it can be used to undo the transaction if needed. Every record that is affected will be returned to the original state using the log entries. Notice that because the log entry is written before the actual database update, in the case of a failure: the transactions that have not completed need to be undone (and this is done record by record using the log) and the transactions that have completed need to be redone (again record by record using the log) just in case the log was written but the database update was not jet finished. Now answer the following questions:
a.Which transactions, if any, need to be redone?
b.Which transactions, if any, need to be undone?
c.Which transactions, if any, are not affected by the crash?
d.What will the final value of x,y,z and w be?
B.Now assume that the DBMS uses the deferred update protocol: the DBMS records all the database writes in the log and does not write to the database until the transaction is ready to commit. Then a log record is written for the commit of the transaction and then the database is updated using the log records. In an event of a failure the results of the transaction need to be applied again in the database (redo using the log the transaction is not processed again). Transactions that have aborted or have not been completed (no commit record at the log file) do not need to be undone since they have had no effect in the database (they only have written records in the log files). Now answer the same questions as before.

2.Using your Oracle database, write SQL command to create the following tables and then answer each of the questions:
Student(StudentId, StudentFName, StudentLName, Major, Credits) Faculty(FacultyId, FacultyName, Department, Rank) Class(ClassId, FacultyId, Timeslot, RoomNbr) Enroll(StudentId, ClassId, Grade)
a.Create user Tom201 and give him permissions to read and update the student class enrollments, however he should not have access to the students grade. How can you do that? (Hint: Create a new view and give the user access to this view)
b.Allow everyone who works in the Deans Office to read student data. To do that: – Create a role named DeansOffice that has full read access to the Student table – Give this role to every user in the deans office: you first need to create users for these people it is Bob202 and Alice303.
c.Revoke the privileges of part a.