CINS 370
Sample Final Exam for Review
NAME
Directions: This test is open book and notes. Show your work on all problems, giving any
(reasonable) assumptions. 100 total
points. Points per problem are listed in
parentheses in front of each problem.
Five total pages.
1. (25 points total) Use the
schedule below to answer the following subparts to this question. You may assume that at time 12 none of the
transactions is complete yet, i.e. none have reached their end of transaction:
Time T1
T2 T3
_______________________________________________________________________
1
Read(B)
2 Read(A)
3 Read(C)
4 Write(B)
5 Read(B)
6 Write(B)
7 Read(A)
8 Write(A)
9 Write(C)
10 Read(B)
11 Write(B)
a) (10 points) Draw the
precedence graph for the above schedule.
Is the above schedule conflict-serializable? Why or why not?
b) (15 points) Using
timestamping as a technique to control concurrency, trace through what would
happen using the schedule above. Show
all transaction and read and write timestamps and explain what would happen
with each advance of time:
2. (20 points total, 10
points each subpart) Using the five relations given below for a University
database, give the following two queries in SQL. Make any reasonable assumptions.
Student(Name, Studentnumber,
Class, Major)
Course(Coursename,
Coursenumber, CreditHours, Department)
Section(SectionIdentifier,
Coursenumber, Semester, Year, Instructor)
GradeReport(Studentnumber,
SectionIdentifier, Grade)
a) For each course section
offered in Fall 2002 give the course name and section identifier and the total
number of students enrolled.
b) Retrieve the names and
numbers of all courses taught by Professor Hilzer during the Fall 1997
semester, ordered by course number.
3. (20 points) The relation
below is not in 3rd normal form. Draw
the dependency diagram for this relation and show what the final schema will be
once this relation is broken up into 3rd normal form relations. You do not need to give the data for each of
these new tables, just the attributes for each table. You can assume that a property has only one
listed owner.
Coldwell/Banker Real Estate
Agent’s Current Listings Report
Report Number: 1023679 Report Date: 02-12-2003
Agent Name: John Doe Agent
Number: 1834
Agent Office Address:
Agent Status: High Producer
Property Property Owner’s Owner’s
Owner’s Listing
Code Address ID Name Phone Price
L1 122 Main St.,
H3 82 First Ave.,
CL 344 Main St.,
Total Listings: 4
Total Dollar Amount of Listings: $1,395,000
4. (15 points) For the
parallel schedule given below for transactions T1, T2 and T3 :Trace through the
schedule below and show where deadlock occurs using a rigorous two-phase
locking scheme. Assume that a locking
scheme of upgradeable locks is used.
I.e., when one wants to read a data item, they ask for a shared lock,
when they want to write that same data item later on, they request an exclusive
lock. Explain how the wait-die scheme
for deadlock prevention would prevent deadlock from occurring.
time T1 T2 T3
1 read(A)
2 read(B)
3 read(X)
4 read(A)
5 write(B)
6 write(A)
7 read(X)
8 read(A)
9 write(X)
10 read(B)
- EOT
11 write(A) - EOT
12 read(Y)
- EOT
5. (20 points) The following list represents
the log entries for four transactions T1, T2, T3, and T4 at the point of a
system crash. Suppose that the immediate
update protocol with checkpointing has been used. Describe the recovery process
from the point of the system crash.
Describe how recovery occurs in this situation. Suppose that the initial values of variables
are X=45, Y=65, A=70, B=25. What are the
values of each of these variables after recovery takes place? Is this a recoverable schedule? Is there any cascading rollback? At the end of your recovery, in giving the
values of variables, assume only undo and redo has occurred, but no fail
transactions have been rerun as yet.
NOTE: The form of the write statements in the log
is: [write_item, transaction_no, variable, old_value, new_value]
[start_transaction, T4]
[read_item, T4, A]
[start_transaction, T1]
[write_item, T4, A, 70, 75]
[read_item, T4, B]
[start_transaction, T3]
[read_item, T3, X]
[write_item, T3, X, 45, 55]
[start_transaction, T2]
[write_item,T4, B, 25, 30]
[read_item, T2, B]
[read_item, T1, X]
[read_item, T1, Y]
[commit T4]
[write_item, T2, B, 30, 92]
[ checkpoint; L = T1, T2, T3]
[read_item, T2, A]
[write_item, T2, A, 75, 23]
[commit T2]
[read_item, T1, Y]
[write_item, T1, Y, 65, 82]
[write_item, T1, X, 55, 77]
[commit T3]
<------- system crash
The following list represents the log
entries for four transactions: T1, T2, T3, and T4 at the point of a system
crash. Suppose that the immediate update protocol with checkpointing has
been used. Describe the recovery process from the point of the system
crash. Describe how recovery occurs in this situation. Suppose the
initial values of variables are X=30, Y=10, A=20, B=50. Is this a recoverable
schedule? What are the values of each of these variables after recovery
takes place? Is there a cascading rollback? At the end of your
recovery, in giving the values of variables, assume only undo and redo has
occurred, but no failed transactions have been rerun as yet.
Note: the form of the write
statements in the log is: [write_item, transaction_num, variable, old_value,
new_value]
Start of log...
[start_transaction, T2]
[read_item, T2, X]
[start_transaction, T3]
[write_item, T2, X, 30, 40]
[read_item, T2, Y]
[read_item, T3, X]
[write_item, T2, Y, 10, 20]
[read_item, T3, Y]
[commit, T2]
[start_transaction, T1]
[start_transaction, T4]
[read_item, T1, A]
[write_item, T1, A, 20, 30]
[checkpoint, L= {T4, T3, T1}
[read_item, T3, A]
[read_item, T4, B]
[write_item, T3, A, 30, 35]
[write_item, T4, B, 50, 60]
[commit, T4]
[read_item, T1, B]
[write_item, T1, B, 60, 70]
<...................................
system crash
Return to Melody's Home Page.