The homework is due on Nov.7 (Thursday).

Each student must submit one copy of the homework

and one copy of the spool file.

The following shows the file name format:

 Zno_FirstName_LastName_HW02.pdf
 Zno_FirstName_LastName_HW02_spool.txt

Example)
Z00000001_KwangSoo_Yang_HW02.pdf
Z00000001_KwangSoo_Yang_HW02_spool.txt

 The pdf file should contain all answers for both Question and Lab Parts.
 Please do not use the zip file to upload the files. Upload two separate files.
 After submitting the homework, please download it and see if the submission is correct.
 Late submissions are not accepted after two days (i.e., Nov. 9 (Saturday)).
 Late penalty

 1st day: You lose -25 points.

 2nd day: You lose -50 points.

Question Part (50 points)

1. (24 points) Consider the following relations, where the primary keys are underlined.
 Employee (eid, ename, age, sup_eid)
 WorkOn (eid, pid, date)
 Project (pid, pname, budget, lid)
 Location(lid, street_name, city, state, zip_code)
Employee.sup_eid refers to Employee.eid, WorkOn.eid refers to Employee.eid, WorkOn.pid refers to
Project.pid, and Project.lid refers to Location.lid.
Let E be Employee, let W be WorkOn, let P be Project, and let L be Location.
Write the relational algebra for the following queries.
1) (3 points) List the names of employees who worked on the ‘Database’ project.
2) (3 points) List the names of employees who worked on the project located in the city of ‘Boca’.
3) (3 points) List the names of employees who worked on the ‘Database’ project, but never worked
on the ‘Data Mning’ project.
4) (3 points) List the names of employees who never worked on the project located in the city of
‘Boca’.
5) (3 points) List the names of employees who worked on the same project as their supervisor.
6) (3 points) List the names of employees who worked on the same project on different dates
7) (3 points) List the names of employees who worked on all the project located in the city of ‘Boca’.
8) (3 points) (Do not use the aggregation function) List the names of the oldest employees.
2. (8 points) Consider the following B+ index (Use 2-3 split rule).

a) (4 points) Show the B+ tree that would result from inserting a data entry with key 26.
b) (4 points) Given the result of (a), show the B+ tree that would result from deleting a data
entry with key 56.

3. (6 points) Consider the following extendable hashing index.

a) (3 points) Show the index that would result from inserting a data entry with key 16.
b) (3 points) Given the result of (a), show the index that would result from inserting a data entry
with key 21.

4. (6 points) Consider the following schedules.

S: R3(B);W2(B);R1(C);W1(C);R1(B);R1(C);W3(C);R3(C);R3(A);R3(A);C1;C3;C2
 Draw the timetable and precedence graph for the schedule. Is the schedule conflict-
serializable?
 Is the schedule recoverable?

5. (6 points) Consider the following schedule.

S: W3(B);W3(C);R3(C);W3(C);W3(B);W1(C);W2(A);R1(C);R2(A);W1(C);C2;C1;C3
Assume that Strict 2PL is applied to the schedules. Draw both deadlock detection table (including
shared and exclusive locks) and wait-for-graph. Does the schedule have 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 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';

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 */ count(G.id)
FROM guest G
WHERE G.id BETWEEN 1 AND 10;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>'ALLSTATS LAST'));

SELECT /*+ GATHER_PLAN_STATISTICS */ count(G.id)
FROM guest G
WHERE G.age BETWEEN 1 AND 10;

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 */ count(H.id)
FROM hotel H
WHERE H.name LIKE '%pton Inn'

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>'ALLSTATS LAST'));

SELECT /*+ GATHER_PLAN_STATISTICS */ count(H.id)
FROM hotel H
WHERE H.name LIKE 'Hamp%';

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>'ALLSTATS LAST'));

3. (5 points) Execute the following SQL. How many tables were accessed to produce the outcome, and how
many indexes were utilized in the process?
SELECT /*+ GATHER_PLAN_STATISTICS */ G.fname
FROM guest G, reserve R, hotel H
WHERE G.id = R.gid AND R.hid = H.id
AND G.id = 1047;

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 */ H.name
FROM guest G, reserve R, hotel H, city C
WHERE G.id = R.gid AND R.hid = H.id AND H.cid = C.id
AND C.name ='Blowing Rock' AND G.fname ='Darryl' AND G.lname ='Getty';

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 */ G.fname
FROM guest G, reserve R, room RM, hotel H, bill B
WHERE G.id = R.gid AND R.hid = RM.hid AND R.rno = RM.no AND RM.hid = H.id AND R.id = B.rid
AND H.name = 'Michiana Motel' AND B.payment_date='09-JUN-23' AND RM.no = 3;

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>'ALLSTATS LAST'));

Lab Part 02 (15 points)
Preliminary
 Open three 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 customer ( id integer,
age integer,
primary key (id) );

 Execute SET AUTOCOMMIT OFF. This command suppresses automatic committing so that you
must commit changes manually (Note that # represents the order of the SQL execution).
# User 1 User 2 User3
1 SET AUTOCOMMIT OFF
2 SET AUTOCOMMIT OFF
3 SET AUTOCOMMIT OFF

 TURN ON the spool.

# User 1 User 2 User 3
1 SPOOL user1.log
2 SPOOL user2.log
3 SPOOL user3.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 User 3
1 SPOOL OFF
2 SPOOL OFF
3 SPOOL OFF
Transaction Processing and Recovery
1. (5 points) What is the result after STEP 8 and STEP10? Explain why this result makes sense or why it does
not make sense?
# User 1 User 2 User 3
1 DELETE FROM customer;
2 COMMIT;
INSERT INTO customer (id, age)
3
VALUES (1, 20);
UPDATE customer
4
SET age = age +10;
5 COMMIT;
UPDATE customer
6
SET age = age +10;
7 COMMIT;
SELECT id, age
8
FROM customer;
9 COMMIT;
SELECT id, age
10
FROM customer;

2. (5 points) What is the result of STEP 7? Explain why this result makes sense or why it does not make sense?
# User 1 User 2 User 3
DELETE FROM customer;
COMMIT;
INSERT INTO customer (id, age)
1
VALUES (1, 20);
INSERT INTO customer (id, age)
2
VALUES (2, 20);
3 COMMIT;
DELETE FROM customer
4
WHERE id = 1 OR id =2;
5 COMMIT;
6 COMMIT;
SELECT id, age
7
FROM customer;

3. (5 points) What happens after STEP 7 and STEP 8? What is the result of Step 9? Explain why this result
makes sense or why it does not make sense?
# User 1 User 2 User 3
1 DELETE FROM customer;
2 COMMIT;
INSERT INTO customer (id, age)
3
VALUES (1, 20);
UPDATE customer
4 SET age =30
WHERE id = 1;
INSERT INTO customer (id,age)
5
VALUES (2, 20);
INSERT INTO customer (id,age)
6
VALUES (1, 30);
INSERT INTO customer (id, age)
7
VALUES (2, 30);
INSERT INTO customer (id, age)
8
VALUES (2, 40);
9 COMMIT;
10 COMMIT;
11 COMMIT;