CSC540 Fall 2003 | Final Exam | December 9, 2003
The exam is closed everything, except at most two pages with notes. Write your name on the top of this page. Write your solutions in the spaces provided. You have until 9pm to solve the problems.
Problem 1 (10 points total). Consider a relation schema R(A,B,C,D). Assume that the following functional dependencies hold on the schema R: BD -> C; AB -> D; AC -> B; BD -> A.
(1.1) (5 points) Submit the result of decomposing the relation R into BCNF. (If the relation is already in BCNF, please submit ‘already in BCNF.’)
Your solution [problem 1.1]: _______________________________________________________ (1.2) (5 points) Find at least one key of the relation R.
Your solution [problem 1.2]: _______________________________________________________ Problem 2 (5 points). Suppose relation R(A,B) has tuples:
and relation S(C,D) has tuples:
A
B
2
1
3
2
1
5
5
7
C
D
4
3
2
4
5
6
7
2
Submit the answer, on R and S, to the SQL query
SELECT * FROM R
WHERE NOT EXISTS
(SELECT * FROM S WHERE C = R.A);
Your solution [problem 2]:_________________________________________________________
Problem 3 (10 points). Consider a database consisting of the following relations:
• Movies(title, type, director): stores information about movies, including the type and director of each movie (assume title is the key of this relation)
• Stars(name, birthday, address, phone): stores information about stars, including the name, birthday, address, and phone number of each star (assume name is the key of this relation)
• Contracts(star, movie, salary): indicates the salary of each star in each movie; each movie
can employ many stars and each star can contract with many movies with different salaries; assume (star, movie) is the key of this relation
Attribute types: All attributes are of string type, except Contracts.salary is a number. Assignment: correct the given SQL query so that it corresponds to its specification. Write all your
corrections over the given SQL query definition.
Specification: find the names and phones of all stars whose average salary is greater than $200K. Query:
SELECT name, address FROM Stars, Contracts
WHERE 200000 < (SELECT avg(salary) FROM Contracts);
Problem 4 (12 points total). Consider these CREATE TABLE statements:
CREATE TABLE Movies (
title VARCHAR(30) PRIMARY KEY,
type VARCHAR(20),
director VARCHAR(20));
CREATE TABLE Contracts (
star VARCHAR(30),
movie VARCHAR(30) REFERENCES Movies(title),
salary INT,
PRIMARY KEY (star,movie));
Suppose
• Movies has two tuples (‘Total Recall’,’science fiction’,’Verhoeven’)
and (‘Star Wars’,’fantasy’,’Lucas’), and
• Contracts has one tuple (‘Harrison Ford’,’Star Wars’,200000).
On this database, tell which of the following data-modification statements will be accepted or rejected. Write just 'accepted' or 'rejected' next to each statement.
1. DELETE FROM Movies WHERE director = 'Lucas'; 2. DELETE FROM Contracts;
3. INSERT INTO Contracts(star,movie) VALUES ('Tom
Cruise', 'Minority Report');
Problem 5 (16 points). For the following GRANT diagram
give all consequences, for all users, of the statement issued by Y: REVOKE R FROM T CASCADE;
For each user, submit (1) whether the user still has the privilege, and (2) whether the user the GRANT OPTION on the privilege.
Your solution [problem 5]:
X: privilege:___________________________; grant option:______________________________ Y: privilege:___________________________; grant option:______________________________ Z: privilege:___________________________; grant option:______________________________ T: privilege:___________________________; grant option:______________________________
Problem 6 (7 points). Consider the following transactions on relation P(A): S:
(a) INSERT INTO P VALUES (1);
(b) SELECT average(A) FROM P;
T:
(c) DELETE FROM P WHERE A = 1;
(d) INSERT INTO P VALUES (2);
Submit the value returned by S (in operation (b)) if both transactions run under isolation level SERIALIZABLE and if the interleaving of the operations is as follows. You may assume that before both transactions start, P is empty.
Interleaving: (a)(c)(d)(b).
Your solution [problem 6]:_________________________________________________________
Problem 7 (16 points total). Submit all possible values of each data item on disk right after the system crash (i.e., before recovery), for the transactions
S [X := X - 1; Y := Y + 5] and
T [Z := Z * 3; W := W + 1],
and for the following log:
UNDO log:
Your solution [problem 7]: X:_____________________________________________________________________________ Y :_____________________________________________________________________________ Z:_____________________________________________________________________________ W:____________________________________________________________________________
Problem 8 (15 points total). Consider the following UNDO/REDO log:
1
2
3
4
5
6
7
8
9
10
[system crash]
Submit the line numbers in the log for all the lines that are used to assign data values on disk during recovery. Important: For each line number you submit, specify whether the action for the log record is “undo” or “redo.”
Your solution [problem 8]: ________________________________________________________
Problem 9 (9 points total). Consider a schedule
w1(A),r2(A),r1(B),r3(C),w2(A),w3(C),w1(B),w2(B)
Determine whether the given schedule (1) is serial, and (2) is conflict-serializable. In answering each question, respond by writing just “yes” or “no.”
Your solution [problem 9]:_(1) serial?_____________; (2) conflict-serializable?____________