Download Database Systems Final Exam Fall 2005 and more Exams Deductive Database Systems in PDF only on Docsity!
CPSC 310: Database Systems / CSPC 603: Database Systems and Applications
Final Exam
Fall 2005
Name:
Instructions:
- This is a closed book exam. Do not use any notes or books, other than your three 8.5-by-11 inch
review sheets. Do not confer with any other student. Do not use any computer equipment.
- Show your work. Partial credit will be given. Grading will be based on correctness, clarity and
neatness.
- I suggest that you read the whole exam before beginning to work any problem. Budget your time
wiselyâaccording to the point distribution.
- There are 6 questions worth a total of 100 points, on 8 pages (including this page).
DO NOT BEGIN THE EXAM UNTIL INSTRUCTED TO DO SO. GOOD LUCK!
Please sign the academic integrity statement:
âOn my honor, as an Aggie, I have neither given nor received unauthorized aid on this academic work.
In particular, I certify that I have not received or given any assistance that is contrary to the letter or the
spirit of the guidelines for this exam.â
Signature:
References for these problems:
web site for our textbook
Teach Yourself SQL in 10 Minutes , Ben Forta, Sams Publishing, Indianapolis, IN, 2004.
Database System Concepts, A. Silberschatz, H. Korth and S. Sudarshan, McGraw-Hill, 2006.
- (15 pts total)
(a) (6 pts) Consider the following sequence of log records for undo logging:
<T,A,10>
<U,B,20>
<T,C,30>
<U,D,40>
<T,E,50>
Suppose the last log record that appears on disk at the time of a crash is . What will
the recovery manager do to recover from this crash, in terms of updates to the disk and to the log?
(b) (9 pts) Consider the following sequence of log records for undo/redo logging:
<S,A,60,61>
<T,A,61,62>
<U,B,20,21>
<T,C,30,31>
<U,D,40,41>
<V,F,70,71>
<T,E,50,51>
<V,B,21,22>
What is the initial state of the database (3 pts)? Which transactions must be indicated in the checkpoint
start (3 pts)? Recall that there is flexibility in when the corresponding end checkpoint record occurs. Circle
each log record that can be immediately followed by the corresponding end checkpoint record (3 pts).
- (20 pts total) Two-phase locking.
(a) (4 pts) What are the rules for two-phase locking?
(b) (3 pts) Circle all of the following properties that two-phase locking guarantees concerning the schedules
that it generates:
serial
serializable
recoverable
conflict-serializable
avoid cascading rollback
strict
(c) (3 pts) What are the rules for strict two-phase locking?
(d) (4 pts) Circle all of the following properties that strict two-phase locking guarantees concerning the
schedules that it generates:
serial
serializable
recoverable
conflict-serializable
avoid cascading rollback
strict
(e) (6 pts) Consider these two transactions:
T
: r 1 (X ); w 1
(Y );
T
: r 2
(Y ); w 2
(X );
For each of the following schedules of transactions T 1
and T 2
, indicate whether it can be generated by
both two-phase locking (2PL) and strict two-phase locking (S-2PL), by 2PL only, by S-2PL only, or by
neither.
`
(X ); r 1
(X ); `
(Y )u 1
(X )`
(X ); w 1
(Y ); u 1
(Y ); `
(Y ); r 2
(Y ); w 2
(X ); u 2
(Y ); u 2
(X );
`
(Y ); r 2
(Y ); u 2
(Y ); `
(X ); w 2
(X ); u 2
(X )`
(X ); r 1
(X ); `
(Y ); w 1
(Y ); u 2
(X ); u 2
(Y );
`
(X )`
(Y )r 1
(X ); w 1
(Y ); u 1
(X ); u 1
(Y ); `
(Y ); `
(X ); r 2
(Y ); w 2
(X ); u 2
(Y ); u 2
(X );
- (15 pts total) Suppose there are three transactions, T 1
with timestamp 100, T 2
with timestamp 200, and
T
with timestamp 300. Suppose that at some point in time, the following queue of lock requests is waiting
to be processed (notation L i (X ) means transaction T i requests a lock on database element X ):
L
(A); L
(B ); L
(C ); L
(B ); L
(C ); L
(A);
For simplicity, we make the following assumptions:
If a transaction dies or is wounded, it immediately gives up its locks and waits D time before restart-
ing. D is chosen to be large enough so that any transaction that is active at time t has completed
(committed or aborted) by time t + D. So all of the dead/wounded transactionâs lock requests go to
the end of the lock request queue.
If a transaction ever gets all the locks it wants, then it completes its work, commits and releases its
locks in a negligible amount of time (i.e., imagine it is instantaneous).
When a lock is released, it is immediately given to a transaction waiting for it (in first-come first-
served manner).
(a) (7 pts) Assume that the wait-die algorithm is used to handle lock requests and avoid deadlock. In what
order do the transactions T 1
, T
and T 3
commit?
(b) (8 pts) Assume that the wound-wait algorithm is used to handle lock requests and avoid deadlock. In
what order do the transactions T 1
, T
, and T 3 commit?
- (24 pts total) Short answer.
(a) (3 pts) Suppose 10 tuples of R can fit in one block, R has 100,000 tuples, and there are among these
tuples 20,000 distinct tuples. We wish to compute Ă (R ) (duplicate elimination) in one pass. How many
main memory buffers are needed to do this? Assume each main memory buffer is the same size as a disk
block.
(b) (3 pts) What is the elevator algorithm and what is it good for?
(c) (3 pts) Multiple choice: Consider a relation R that always contains exactly one tuple. Is R in Boyce-
Codd Normal Form (BCNF)? Circle one:
yes
no
canât tell without seeing the actual schema and FDâs
canât tell without seeing the actual data
canât tell without seeing the actual schema, FDâs, and data
(d) (4 pts) Suppose we have a relation R (A; B ; C ; D ; E ) and functional dependencies A! D , B! C ,
D! E , C E! B. What are the key(s) of R?
(e) (3 pts) Let R (x; y ; z ) be a relation. Which one of the following relational algebra formulas is an identity
(true of all such relations R )? Circle it.
x;y
(Ă (R )) = Ă (
x;y
(R ))
x;y
(R ) ./
y ;z
(R ) = R
x;y ;z
(R ) = Ă (R )
(f) (2 pts) What does it mean to swizzle a pointer and why is it important to do so?
(g) (2 pts) What is the main advantage of using a B-tree index over using a sequential index?
(h) (4 pts) Suppose we have these relations in a database schema:
Branch(branch_name,branch_city,assets)
Account(account_number,branch_name,balance)
Customer(customer_name,account_number)
Consider the following initial logical query plan to find the name of all customers that bank at the Snook
branch and have a balance less than 1000. Draw an optimized version of this logical query plan, in which
you have tried to reduce the size of intermediate relations as much as possible.
