Question Part (50 points)
1. (30 points) Consider the following relations, where the primary keys are underlined.
 Student (sid, sname, age, mentor_sid)
 Enroll (sid, cid)
 Course (cid, cname, semseter)
Let S be Student, let E be Enroll, and let C be Course. Enroll.sid refers to Student.sid, Enroll.cid
refers to Course.cid, and Student.mentor_sid refers to Student.sid.
Write the relational algebra for the following queries.
1) (2 points) Retrieve the names of students who have enrolled in the ‘Database’ course.
2) (4 points) Retrieve the names of sutdents who is older than her/his mentor.
3) (4 points) Retrieve the names of student who enrolled in both ‘Database’ and ‘DataMining’
courses.
4) (4 points) Retrieve the names of student who never enrolled in the ‘Database’ course.
5) (4 points) Retrieve the names of student who enrolled the ‘Database’ more than once.
6) (4 points) Retrieve the names of courses enrolled by all students whose age is gretater than 30.
7) (4 points) (Do not use the aggregation function) Retrieve the names of the oldest students.
8) (4 points) (Do not use the aggregation function) Retrieve the names of the oldest students who
enrolled in ‘Database’ course.

2. (10 points) Consider the following B+ index (Use 2-3 split rule).
a) (5 points) Show the B+ tree that would result from inserting a data entry with key 85.
b) (5 points) Given the result of (a), show the B+ tree that would result from deleting a data
entry with key 1.
3. (10 points) Consider the following extendable hashing index.
a) (3 points) Show the index that would result from inserting a data entry with key 17.
b) (3 points) Given the result of (a), show the index that would result from inserting a data entry
with key 21.
4. (5 points) Consider the following schedules.
S: W1(X);R3(Y);R2(Y);W2(X);R2(X);R1(Y);R1(Z);R2(X);R2(X);W3(Z);W2(Z);C3;C2;C1
 Draw the precedence graph for the schedule. Is the schedule conflict-serializable?
 Is the schedule recoverable?
5. (5 points) Consider the following schedule.
S1: R2(X);W3(Y);R1(X);W2(Z);R2(Y);W2(Y);W1(Y);R1(Z);W3(Z);C1;C2;C3
Assume that Strict 2PL is applied to the schedules. Draw both timetables (including shared and
exclusive locks and unlock actions) and wait-for-graph. Does the schedule has a deadlock?

Lab Part 01 (35 points)
Preliminary
 Login into Linux machine (oraclelinux.eng.fau.edu)
 Connect to the database (e.g., sqlplus username/password)
 ALTER SESSION SET CURRENT_SCHEMA = COP6731;
Please note that the schema name is defined as upper case characters.
 Execute the following SQL and identify all required tables.
o SELECT table_name from all_tables where owner = ‘COP6731’;
 Please note that the owner’s name is defined as upper case characters.
 Change line size and page size:
o SET LINESIZE 400
o SET PAGESIZE 0
Use the “spool” command to create a log file for the output of SQL (e.g., SPOOL filename and SPOOL OFF)
Note: Submit both answers and SPOOL files (i.e., an explanation and a SPOOL file).
Consider the following relational schema.
 Execute the following SQL and review the primary keys
SELECT cols.table_name || ‘, ‘ || cols.column_name || ‘, ‘ || cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = ‘P’ AND cons.constraint_name = cols.constraint_name
AND cons.owner = ‘COP6731’
ORDER BY cols.table_name, cols.position;
 Execute the following SQL and review the indexed keys.
SELECT table_name||’, ‘||index_name||’, ‘||column_name||’, ‘||column_position
FROM all_ind_columns
WHERE table_owner =’COP6731′
ORDER BY index_name, column_position;
 Execute the following SQL and review the index structure.
SELECT index_name || ‘, ‘ || index_type || ‘, ‘ || blevel || ‘, ‘ || leaf_blocks || ‘, ‘ || table_name || ‘, ‘ ||
avg_leaf_blocks_per_key || ‘, ‘ || avg_data_blocks_per_key || ‘, ‘ || clustering_factor || ‘, ‘ || distinct_keys
FROM all_indexes
WHERE table_owner=’COP6731′
ORDER BY index_name;

Query Evaluation
 Use the Spool command to log the output of SQL (e.g., SPOOL filename and SPOOL OFF)
 There are two files you should submit: 1) explanation for query execution plans and 2) recorded spool file.
1. (5 points) Execute the following two SQLs and explain which access method is used in each query. Explain
why one outperforms another.
SELECT /*+ GATHER_PLAN_STATISTICS */ P.cid, P.eid
FROM Purchase P
WHERE P.cid = 100;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ P.cid, P.eid
FROM Purchase P
WHERE P.eid = 100;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
2. (5 points) Execute the following two SQLs and explain which access method is used in each query. Explain
why one outperforms another.
SELECT /*+ GATHER_PLAN_STATISTICS */ I.no
FROM Item I
WHERE I.pid = 100;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ I.sid
FROM Item I
WHERE I.pid = 100;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
3. (5 points) Execute the following two SQLs and explain which access method is used in each query. Explain
why one outperforms another.
SELECT /*+ GATHER_PLAN_STATISTICS */ C.fname
FROM Customer C
WHERE C.fname like ‘Rom%’;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ C.fname
FROM Customer C
WHERE C.fname like ‘%ana’;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
4. (10 points) Execute the following SQL and draw the query tree for the SQL. Describe what kinds of access
methods are used for each table. Explain which join method is used in each join operation.
SELECT /*+ GATHER_PLAN_STATISTICS */ C.lname, S.address, Pr.name
FROM Customer C, Purchase Pu, Item I, Store S, Product Pr
WHERE C.id = Pu.cid AND Pu.pid = I.pid AND Pu.ino = I.no AND I.sid = S.id AND I.pid = Pr.id
AND C.fname = ‘Tesla’;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
5. (10 points) Execute the following SQL and draw the query tree for the SQL. Describe what kinds of access
methods are used for each table. Explain which join method is used in each join operation.

SELECT /*+ GATHER_PLAN_STATISTICS */ C.fname, S.address, Pr.name
FROM Customer C, Purchase Pu, Item I, Store S, Product Pr
WHERE C.id = Pu.cid AND Pu.pid = I.pid AND Pu.ino = I.no AND I.sid = S.id AND I.pid = Pr.id
AND Pr.name LIKE ‘Blueberries%’;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
Lab Part 02 (15 points)
Preliminary
 Open two terminals (e.g., putty or XShell) and login Oracle database
 Assume that each terminal represents each user (i.e., User1 and User2).
 Create the student table using the following query.
CREATE TABLE student ( zno integer, name varchar2(20), grade integer, primary key (zno) );
 Execute SET AUTOCOMMIT OFF. This command suppresses automatic committing so that you
must manually commit changes (Note that # represents the order of the SQL execution).
# User 1 User 2
1 SET AUTOCOMMIT OFF
2 SET AUTOCOMMIT OFF
 TURN ON the spool.
# User 1 User 2
1 SPOOL user1.log
2 SPOOL user2.log
 Execute the SQLs in each question and answer the question.
 Save the spool files and submit both answers and spool files.
# User 1 User 2
1 SPOOL OFF
2 SPOOL OFF

Transaction Processing and Recovery
1. (5 points) What are the results at STEPs 3,5,6, and 9? Explain why this result makes sense or why it does not make
sense?
# User 1 User 2
1 DELETE FROM student;
2 COMMIT;
3 SELECT count (*)
FROM student;
4 INSERT INTO student (zno, name, grade)
VALUES (1, ‘James’, 20);
5 SELECT count (*)
FROM student;
6 SELECT count (*)
FROM student;
7 COMMIT;
8 COMMIT;
9 SELECT count (*)
FROM student;
2. (5 points) What are the results of STEPs 7 and 10? Explain why this result makes sense or why it does not make sense?
# User 1 User 2
1 DELETE FROM student;
2 INSERT INTO student (zno, name, grade)
VALUES (1, ‘James’, 20);
3 INSERT INTO student (zno, name, grade)
VALUES (2, ‘Susan’, 30);
4 COMMIT;
5 UPDATE student
SET grade = (SELECT max(grade) + 1 FROM student);
6 UPDATE student
SET grade = (SELECT min(grade) + 1 FROM student);
7 SELECT zno, grade
FROM student;
8 COMMIT;
9 COMMIT;
10 SELECT zno, grade
FROM student;
3. (5 points) What is the effect of Step 8? What is the result of STEP 11? Explain why this result makes sense or why it
does not make sense?
# User 1 User 2
1 DELETE FROM student;
2 INSERT INTO student (zno, name, grade)
VALUES (1, ‘James’, 20);
3 INSERT INTO student (zno, name, grade)
VALUES (2, ‘Susan’, 30);
4 COMMIT;
5
UPDATE student
SET grade = (SELECT max(grade) + 1 FROM student)
WHERE zno = 1;
6
UPDATE student
SET grade = (SELECT min(grade) -1 FROM student)
WHERE zno = 2;
UPDATE student
SET grade = (SELECT max(grade) + 1 FROM student)
WHERE zno = 2;
8
UPDATE student
SET grade = (SELECT min(grade) -1 FROM student)
WHERE zno = 1;
9 COMMIT;
10 COMMIT;
11 SELECT zno, grade
FROM student;