Answered step by step
Verified Expert Solution
Question
1 Approved Answer
[ Question 1 ] Consider the following relational schema: Student ( S# , Sname, Dept ) Prof ( P# , Pname, Dept, Salary ) Course
Question Consider the following relational schema:
Student S# Sname, Dept Prof P# Pname, Dept, Salary
Course C# Cname, Text StCourse S# C# ProfCourse P# C#
For the following SQL query
SELECT Sname
FROM Student, Prof, StCourse, ProfCourse
WHERE StudentDept Prof. Dept OR
ProfSalary AND Student.Dept Prof. Dept
AND Prof. P# Prof.Course.P#
AND ProfCourse.C# StCourse.C#
AND StCourse.S# Student.S#
a Write the initial query tree corresponding to the above SQL query.
b Apply query optimization heuristics on the tree to produce the most efficient
query tree.
c Write the Relational Algebra query corresponding to the most efficient tree.
Question Find out if the following schedules are conflict serializable or not? If
a schedule is conflict serializable, get its corresponding serial schedule: Hint:
assuming all transactions are committed at the end of the schedule
a Rx Rx Wx Rx Wx
b Rx Rx Wx Wx Rx
c Rx Rx Wx Rx Wx
d Rx Rx Rx Wx Wx
e Rx Rz Rz Rx Ry Wx Wy Ry Wz Wy
f Rx Rz Rx Rz Rx Ry Wx Wz Wy Wy
Question Consider the following three transactions:
T: Rx Rz Wx C
T: Rz Rx Wz Wy C
T: Rx Ry Wy C
a Add Locks and Unlocks to each transaction such that the transaction follows
the PL protocol.
b Add Locks and Unlocks to each transaction such that the transaction follows
the Strict PL protocol SPL
c Add Locks and Unlocks to each transaction such that the transaction follows
the Rigours PL protocol RPL
d Write a possible schedule that may be produced by each locking protocol
PL SPL RPL
e Show whether the schedule is deadlock free or not.
Question Consider the following schedule where, for clarity, we have included
all the operations that will appear in the log plus additional helpful detail.
Operation Column
T STARTS
T reads item B
T writes item B with old value new value
T STARTS
T reads item B
T writes item B with old value new value
T STARTS
T reads item A
T writes item A with old value new value
T reads item A
T writes item A with old value new value
T COMMITS
T reads item D
T writes item D with old value new value
T COMMITS
T COMMITS
a What serial schedule is this equivalent to If none, then explain why.
c Is this schedule consistent with two phase locking? If your answer is yes,
then in column insert into the schedule a minimal set of additional operations
that will make the schedule no longer consistent with two phase locking. Do not
introduce any new transactions. Make sure to show exactly where your new
operations should be inserted in the original schedule. If your answer is no then
in column remove from the schedule a minimal set of operations, so that the
revised schedule is consistent with two phase locking.
d Consider the version of the schedule that is consistent with two phase
locking either the original one or your revised version, depending on your
answer to the previous part Is that version consistent with strict two phase
locking? Why or why not?
c Now consider an undo log corresponding to the original schedule. Suppose
that a crash occurs right after the log record with asterisks next to it is written to
disk, and the recovery procedure is then run.
i What transactions get rolled back?
ii Does cascading rollback take place? Why or why not?
iii Which operations in the log get undone?
iv At the time of the crash, which of the five data write
operations are guaranteed to have taken place on disk?
v At the time of the crash, which of the five data write
operations are guaranteed to not have taken place on disk
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started