Description
my sql,Relational algebra
Unformatted Attachment Preview
The homework is due on Nov.16 (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
user1.log
user2.log
Example)
Z00000001_KwangSoo_Yang_HW02.pdf
Z00000001_KwangSoo_Yang_HW02_spool.txt
user1.log
user2.log
The pdf file should contain all answers for the Question Part and the Lab Part.
No spool file: -50 points.
Late submission: 1 day: -25 (Friday), 2 day -50 (Saturday)
Please do not use the zip file to upload the files. Upload two separate files.
INCORRECT homework submission is NOT acceptable. Be sure to double-check the
assignment you are uploading and the file type before you submit it. You also need to
DOWNLOAD these files and CHECK if all files are correct.
Question Part (50 points)
1. (20 points) Consider the following relations, where the primary keys are underlined.
Customer (cid, name, city)
Reserve (cid, hid, rno date)
Room (hid, no, bed_sizes)
Hotel (id, name, city)
Reserve.cid refers to Customer.cid, Reserve.(hid,rno) refers to Room.(hid.no), and Room.hid refers to
Hotel.id.
Let C be Customer, let R be Reserve, let RM be Room, and let H be Hotel.
Write the relational algebra for the following queries.
a) (4 points) Retrieve the names of customers who have reserved room no. 5 in the ‘FAU’ hotel.
b) (4 points) Retrieve the names of customers who have reserved a room in a hotel located in the
same city as their city of residence.
c) (4 points) Retrieve the names of customers who have never reserved a room.
d) (4 points) Retrieve the names of customers who have have reserved more than one room on the
same date.
e) (4 points) Retrieve the names of customers who have reserved all rooms in the ‘FAU’ hotel.
2. (6 points) Consider the following B+ index (Use 2-3 split rule).
a) (3 points) Show the B+ tree that would result from inserting a data entry with key 101.
b) (3 points) Given the result of (a), show the B+ tree that would result from inserting a data
entry with key 124.
3. (6 points)
a) (3 points) Show the B+ tree that would result from deleting a data entry with key 1.
b) (3 points) Given the result of (a), show the B+ tree that would result from deleting a data
entry with key 2.
4. (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 25.
5. (6 points) Consider three transactions: T1, T2, and T3.
S: W1(A);R2(A);W3(B);W2(A);W1(A);R2(C);R3(B);R3(A);W3(B);C1;C2;C3
a) (3 points) Draw the precedence graph for the schedule. Is the schedule conflictserializable?
b) (3 points) Is the schedule recoverable?
6. (6 points) Consider the following schedule.
S: R1(C);R3(A);W1(A);W3(C);W3(C);W3(B);W1(A);R2(A);W3(A);R3(B)
Assume that Strict 2PL is applied to the schedules. Draw timetable (including shared and exclusive
locks and unlock actions) and wait-for-graph. Determine whether 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’;
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 */ E.lname
FROM Employee E
WHERE E.fname like ‘a%’;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ E.lname
FROM Employee E
WHERE E.fname like ‘%a’;
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 */ R.hid
FROM Room R WHERE R.no = 300;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ R.no
FROM Room R WHERE R.hid = 300;
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 */ count(*)
FROM Employee E
WHERE E.id 10;
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (format=>’ALLSTATS LAST’));
SELECT /*+ GATHER_PLAN_STATISTICS */ count(*)
FROM Employee E
WHERE E.id = 10;
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 */ G.fname, H.name
FROM Guest G, Reserve R, Room RM, Hotel H
WHERE G.id = R.gid AND R.hid = RM.hid AND R.rno = RM.no AND R.hid = H.id
AND H.zipcode =’20814′;
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, R.check_in_date, H.name
FROM Guest G, Reserve R, Room RM, Hotel H
WHERE G.id = R.gid AND R.hid = RM.hid AND R.rno = RM.no AND R.hid = H.id
AND G.id = 1;
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 Book table using the following query.
CREATE TABLE Book (id integer, name varchar2(100), 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).
#
1
2
User 1
SET AUTOCOMMIT OFF
User 2
SET AUTOCOMMIT OFF
TURN ON the spool.
#
1
2
User 1
User 2
SPOOL user1.log
SPOOL user2.log
Execute the SQLs in each question and answer the question.
Save the spool files and submit both answers and spool files.
#
1
2
User 1
User 2
SPOOL OFF
SPOOL OFF
Transaction Processing and Recovery
1. (5 points) What are the results of STEPs 5, 6 and 9? Explain why this result makes sense or why it does not
make sense?
#
1
2
3
4
5
6
7
8
9
User 1
DELETE FROM Book;
COMMIT;
INSERT INTO Book (id,name) VALUES (1,’James’);
User 2
INSERT INTO Book (id,name) VALUES (2,’Susan’);
SELECT id FROM Book;
SELECT id FROM Book;
COMMIT;
COMMIT;
SELECT id FROM Book;
2. (5 points) What are the results of STEPs 5, 6, and 10? Explain why this result makes sense or why it does
not make sense?
#
1
2
3
4
5
6
7
8
9
10
User 1
DELETE FROM Book;
COMMIT;
INSERT INTO Book (id,name) VALUES (1,’James’);
User 2
INSERT INTO Book (id,name) VALUES (2,’Susan’);
SELECT name FROM Book;
SELECT name FROM Book;
UPDATE Book
SET name = ‘Jane’
WHERE name = ‘Susan’;
COMMIT;
COMMIT;
SELECT name FROM Book;
3. (5 points) What are the results after STEPs 6 and 9? Explain why this result makes sense or why it does not
make sense?
#
1
2
3
4
5
6
7
8
9
User 1
DELETE FROM Book;
COMMIT;
INSERT INTO Book (id,name) VALUES (1,’James’);
User 2
INSERT INTO Book (id,name) VALUES (2,’Susan’);
INSERT INTO Book (id,name) VALUES (2,’Jane’);
INSERT INTO Book (id,name) VALUES (1,’Peter’);
COMMIT;
COMMIT;
SELECT id, name
FROM Book;
Purchase answer to see full
attachment
