Download Final Exam with Solution Set | Database Systems | CS 411 and more Exams Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2006
Department of Computer Science
University of Illinois at Urbana-Champaign
Final Examination
December 15, 2006
Time Limit: 180 minutes
- Print your name and NetID below. In addition, print your NetID in the upper right
corner of every page.
Name: NetID:
- Including this cover page, this exam booklet contains 13 pages. Check if you have
missing pages.
- The exam is closed book and closed notes. No calculators or other electronic devices
are permitted. Any form of cheating on the examination will result in a zero grade.
- Please write your solutions in the spaces provided on the exam. You may use the blank
areas and backs of the exam pages for scratch work. Please do not use any additional
scratch paper.
- Please make your answers clear and succinct; you will lose credit for verbose, convo-
luted, or confusing answers. Simplicity does count!
Problem 1 2 3 4 5 6 7 8 Total
Points 10 10 11 16 12 10 16 15 100
Score
Grader
Turn over the page when instructed to do so.
Problem 1 (10 points) True/False Questions
For each of the following statements, indicate whether it is TRUE or FALSE by circling your choice. If you change your mind, cross out both responses and write “True” or “False”. You will get 1 point for each correct answer, 0 points for each incorrect answer.
(1) T rue F alse Triggers can operate on insertion, deletion, and updates.
(2) T rue F alse A user application executes a database Trigger by invoking “RUN TRIGGER triggername”
(3) T rue F alse Secondary storage is volatile.
(4) T rue F alse Seek time is part of the disk latency time.
(5) T rue F alse When an index is clustered it requires that the data records are sorted in the search key order of the index.
(6) T rue F alse B+ trees can include duplicate keys.
(7) T rue F alse B+ trees can be used to perform ranged queries.
(8) T rue F alse When a sparse index is used, each record must have an entry in the index.
(9) T rue F alse In fixed-length records, a character field of type CHAR is NOT allowed.
(10) T rue F alse Spanned records refer to records that are longer than blocks and therefore are broken into fragments.
Problem 3 (11 points) Data Storage
(1) How many blocks are on a disk with the following characteristics? Sector size of 512 bytes, 16 sectors per track, 16384 tracks per surface, 4 double sided platter, and 4096 bytes per block. [2 points]
(2) Explain (i) what it means for a block to be pinned and (ii) give one reason for pinning a block. [3 points]
(3) What is the purpose of a tombstone in an offset table [2 points]?
(4) How many records can we put into a block of size 4096 bytes with the following conditions? [4 points] a) Each record has the following fields in order: a real of 6 bytes, a character string of length 17 bytes, and an 8 byte timestamp. b) The record header consists of two 4 byte pointers and one 10 byte date. c) Fields must start at a byte location that is a multiple of 4. d) Block header includes eight additional 4-byte integers.
Problem 4 (16 points) Triggers
Consider the following tables with the following inserts and triggers:
CREATE TABLE UserBucket (firstname VARCHAR(20), lastname VARCHAR(20), bucket INT DEFAULT NULL);
CREATE TRIGGER UserBeforeTrigger BEFORE INSERT ON UserBucket FOR EACH ROW REFERENCING NEW ROW AS NewTuple WHEN (NewTuple.firstname LIKE ’E%’) SET NewTuple.bucket=1;
CREATE TABLE BucketCount (bucketid INT, count INT); INSERT INTO BucketCount(bucketid, count) VALUES(0, 0); INSERT INTO BucketCount(bucketid, count) VALUES(1, 0);
CREATE TRIGGER UserAfterTrigger AFTER INSERT ON UserBucket FOR EACH ROW REFERENCING NEW ROW AS NewTuple UPDATE BucketCount SET count=count+1 WHERE bucketid=NewTuple.bucket;
CREATE TRIGGER UserTableTrigger AFTER UPDATE OF lastname ON UserBucket REFERENCING OLD TABLE As OldStuff NEW TABLE AS NewStuff BEGIN UPDATE BucketCount SET count=(SELECT count(*) FROM OldStuff) END
(a) Show the contents of the UserBucket table after executing the following query: [3 points]
INSERT INTO UserBucket(firstname, lastname) VALUES(’Evan’, ’Smith’);
Problem 5 (12 points) B+ Tree Consider the B+ tree index of degree 3 shown in the figure below. For each problem below, you only need to show the final resulting B+ Tree.
5 13
(^1 247 )
Figure 1: Problem 5
a) Show the tree that would result from inserting a data entry with key 19 into this tree. [2 points]
b) Show the B+ tree that would result from inserting a data entry with key 3 into the original tree. [3 points]
c) Show the B+ tree that would result from deleting the data entry with key 10 from the original tree. [2 points]
d) Show the B+ tree that would result from deleting the data entry with key 14 from the original tree. [3 points]
e) Explain why in a real database most relations only require B+ trees with two or three levels. [ points]
Problem 7 (16 points) Transaction Logs
A database has four elements, A, B, C, and D. Assume that the following is a normal sequence of undo log records, using non-quiescent checkpointing:
1
2 <T1,B,40>
3
4 <T2,A,56>
5 <T2,C,34>
6
7
8 <T3,B,12>
9
10 <T3,D,89>
11
12 <T4,C,7>
13 <T3,A,22>
14
15 <T3,A,99>
16
(a) When is the latest time for transaction T1, T2 that ”dirty data” can be flushed onto disk (ie, the time Output(X) for data X can be performed)? [2 points]
For T1: Output(s) before Log Line. For T2: Output(s) before Log Line.
(b) Suppose we start checkpointing right after Log 5, indicate where and what the start check- pointing record would look like. Then, indicate where and what the earliest end checkpoint record would look like. [2 points]
Between Log Line 5 and 6, <START CKPT( , )> Between Log Line and ,
(c) Continue from (b). Suppose the system crashes right after Log 14 and the end checkpoint has been written out to disk. What is the contents of the earliest log line we must examine? And which transaction records do we need to undo in sequence? [4 points]
Earliest Log Line Contents: < >, Transaction Sequence:
(d) Now, suppose that the log is a redo log. When is the earliest time for transactions T3 and T4 that ”dirty data” can be flushed onto disk? [4 points] For T3: Output(s) after Log Line. For T4: Output(s) after Log Line.
(e) Show the contents of log line 13 if this was extended to be undo-redo log. Assume the values shown are the undo log entries. [2 points]
(f) Explain one advantage of using a redo log as opposed to an undo log. [2 points]
c) Grand Challenge: Design another plan that can further reduce the amount of data transferred between the two sites than plans A and B. Briefly describe your plan and calculate the total number of bytes transferred between sites X and Y for your plan. [Hint: Try to avoid transferring the whole relation.] [5 points]
END OF CS411 FINAL EXAM
CS411 Database Systems
Fall 2006
Department of Computer Science
University of Illinois at Urbana-Champaign
Final Examination Solution Set
December 15, 2006
Time Limit: 180 minutes
b) The record header consists of two 4 byte pointers and one 10 byte date. c) Fields must start at a byte location that is a multiple of 4. d) Block header includes eight additional 4-byte integers.
For each record (56): Header: 4+4+12(10)= Fields: 8(6)+20(17)+8=
For the block: (4096-32)/56=4064/56=72 records
Problem 4 (18 points) Triggers
Consider the following tables with the following inserts and triggers:
CREATE TABLE UserBucket (firstname VARCHAR(20), lastname VARCHAR(20), bucket INT DEFAULT NULL);
CREATE TRIGGER UserBeforeTrigger BEFORE INSERT ON UserBucket FOR EACH ROW REFERENCING NEW ROW AS NewTuple WHEN (NewTuple.firstname LIKE ’E%’) SET NewTuple.bucket=1;
CREATE TABLE BucketCount (bucketid INT, count INT); INSERT INTO BucketCount(bucketid, count) VALUES(0, 0); INSERT INTO BucketCount(bucketid, count) VALUES(1, 0);
CREATE TRIGGER UserAfterTrigger AFTER INSERT ON UserBucket FOR EACH ROW REFERENCING NEW ROW AS NewTuple UPDATE BucketCount SET count=count+1 WHERE bucketid=NewTuple.bucket;
CREATE TRIGGER UserTableTrigger AFTER UPDATE OF lastname ON UserBucket REFERENCING OLD TABLE As OldStuff NEW TABLE AS NewStuff BEGIN UPDATE BucketCount SET count=(SELECT count(*) FROM OldStuff) END
(a) Show the contents of the UserBucket table after executing the following query: [3 points]
INSERT INTO UserBucket(firstname, lastname) VALUES(’Evan’, ’Smith’);
firstname, lastname, bucket ’Evan’, ’Smith’, 1
(b) Starting with the initial problem (ie ignoring the queries in subproblem a), show the contents of the UserBucket table after executing the following query: [3 points]
INSERT INTO UserBucket(firstname, lastname) VALUES(’Angela’, ’Smith’);
firstname, lastname, bucket ’Angela’, ’Smith’, null
(c) With the initial problem (ie, ignoring the queries in the previous subproblems), show the contents of the BucketCount table after executing the following queries: [3 points]
START TRANSACTION; INSERT INTO UserBucket(firstname, lastname) VALUES(’Laura’, ’Stuart’); INSERT INTO UserBucket(firstname, lastname) VALUES(’Evonne’, ’Chu’); ROLLBACK;
bucketid, count 0, 0 1, 0
(d) Starting with original problem (ie ignoring the queries in the previous subproblems), show the contents of the BucketCount table after executing the following queries: [3 points]
INSERT INTO UserBucket(firstname, lastname) VALUES(’Larry’, ’Smith’); INSERT INTO UserBucket(firstname, lastname) VALUES(’Edward’, ’Johnson’); UPDATE UserBucket SET lastname=’Jones’ WHERE firstname=’Edward’ AND lastname=’Johnson’;
bucketid, count 0, 1 1, 1
(e) Explain one advantage and one disadvantage of using a trigger to enforce a constraint com- pared to using a CHECK constraint [4 points] Triggers works better when the constraints involve multiple relationships. With CHECK con- straints violations can occur when modifications are being made to the referenced relationship in the CHECK. Triggers are more efficent and have finer granular control, they execute based upon on a specificed type of event such as before and after insertion, updates, and deletes. A disadvantage of triggers is that they are not available on every DBMS. Another disadvantage is triggers can not prevent an operation but only correct it. Disadvantage have to tell triggers when to activate.
c) Show the B+ tree that would result from deleting the data entry with key 10 from the original tree. [2 points]
d) Show the B+ tree that would result from deleting the data entry with key 14 from the original tree. [3 points]
e) Explain why in a real database most relations only require B+ trees with two or three levels. [ points]
Problem 6 (10 points) Indexing Consider indexing the following key values using a linear hash table. Here is the specification of these index structures.
The hash function h(n) for key n is h(n) = n mod 16; i.e., the hash function is the remainder after the key value is divided by 16. Thus, the hash value is 4 bits. Assume that each bucket can hold 2 data items. We adopt the policy that the average occupancy of a bucket cannot exceed 85%.
Suppose that we insert the keys in the order of: 45, 36, 31, 56, 34.
(a) Draw the linear hash index after all the keys are inserted. Show how the keys and their hash values are distributed within the buckets. [6 points]
(b) Briefly explain the advantages and disadvantages of linear hash tables, in terms of the following aspects, compared to B+ Tree? [4 points]
Problem 7 (16 points) Transaction Logs
A database has four elements, A, B, C, and D. Assume that the following is a normal sequence of undo log records, using non-quiescent checkpointing:
1
2 <T1,B,40>
3
4 <T2,A,56>
5 <T2,C,34>
6
7
8 <T3,B,12>
9
10 <T3,D,89>
11
12 <T4,C,7>
13 <T3,A,22>
14
15 <T3,A,99>
16
(a) When is the latest time for transaction T1, T2 that ”dirty data” can be flushed onto disk (ie, the time Output(X) for data X can be performed)? [2 points]
For T1: Output(s) B before Log Line 7. For T2: Output(s) A and C before Log Line 9.
(b) Suppose we start checkpointing right after Log 5, indicate where and what the start check- pointing record would look like. Then, indicate where and what the earliest end checkpoint record would look like. [2 points]
Between Log Line 5 and 6, <START Checkpoint(T1, T2)> Between Log Line 9 and 10,
(c) Continue from (b). Suppose the system crashes right after Log 14 and the end checkpoint has been written out to disk. What is the contents of the earliest log line we must examine? And which transaction records do we need to undo in sequence? [4 points]
Earliest Log File Contents: <START Checkpoint(T1, T2)> Log lines 5 and 6, Transaction Sequence: <T3,A,22>, <T3,D,89>, <T3,B,12>
(d) Now, suppose that the log is a redo log. When is the earliest time for transactions T3 and T4 that ”dirty data” can be flushed onto disk? [4 points] For T3: Output(s) A, D, and B after Log Line 16. For T4: Output(s) C after Log Line 14.
(e) Show the contents of log line 13 if this was extended to be undo-redo log. Assume the values shown are the undo log entries. [2 points]
