Guideline: Please read the following bullets before you start. • There are 7 problems with 100 points in total. • If a clarification question is common to others, the instructor will post the answer as an announcement. So please check course announcement regularly. • For PL/SQL programs, just submit the statements themselves. There is no need to print out results or show screen shots. Database used in the EXAM: You will be using the following tables in the exam. drop table reservation; drop table room_rate; drop table room; drop table guest; create table guest( gid integer, -- guest ID gname varchar(50), -- guest name gaddress varchar(100), -- guest address gcard varchar(16), -- credit card primary key (gid) ); insert into guest values (1, 'John', '123 Hilltop Rd, 21250', '1233123498709898'); insert into guest values (2, 'Alice', '2230 Baltimore National Pike,21042', '7233553455557777'); insert into guest values (3, 'Bob', '145 Main St, 21043', '9999344455556666'); create table room( rid integer, --- room id rsize varchar(20), --- room size, e.g., '2 queen size bed', primary key (rid) ); insert into room values(123,'2 queen size bed'); insert into room values(400,'1 king size bed'); insert into room values(423,'2 queen size bed'); create table room_rate( room_rate_id integer, --- a primary key for room_rate. rid integer, --- room id startdate date, --- start date of this rate enddate date, --- end date of this rate price number, --- room price within that period primary key (room_rate_id), foreign key (rid) references room ); insert into room_rate values(1, 123, date '2016-10-1',date '2016-12-20',123); insert into room_rate values(2, 123, date '2016-12-20',date '2017-1-10',163); insert into room_rate values(3, 400, date '2016-10-1',date '2016-12-20',113); insert into room_rate values(4, 400, date '2016-12-20',date '2017-1-10',153); create table reservation( reservation_id integer, --- reservation id gid integer, --- guest id rid integer, --- room id checkindate date, --- check in date checkoutdate date, --- check out date numguest integer, --- number of guests total number, --- total charge primary key(reservation_id), foreign key (gid) references guest, foreign key (rid) references room); insert into reservation values(1, 1, 123, date '2016-10-1', date '2016-10-2', 2, null); insert into reservation values(2, 3, 400, date '2016-12-22', date '2016-12-27', 1, null); insert into reservation values(3, 2, 123, date '2016-12-27', date '2016-12-29', 2, null); insert into reservation values(4, 1, 123, date '2016-12-21', date '2016-12-23', 2, null); Problem 1: [15 points] Please create a PL/SQL procedure to print out the reservation ID, checkin date, and checkout date of reservations made by a guest. The input parameters is the name of the guest. There is no output parameter. Problem 2: [15 points] Please write a PL/SQL function that given a reservation ID, returns the name of guest who made that reservation. If there is no such reservation in the database, return null. Please call this function with input reservation ID 1, and print out the returned value. You need to print No such reservation if the returned value is null. Problem 3: [15 points] Please answer questions a and b on RAID levels. a) [6 points] Suppose you are given 8 data blocks, 4 parity blocks, and 3 disks in RAID level 5. Please fill in data blocks in the figure below. You can use 1, 2, … to represent data blocks and P1, P2, … to represent parity blocks. b) Please decide for each of the following applications, which RAID level (level 0, 10, or 5) may be the most appropriate. Please also briefly explain the reason for your answers. [9 points] i. An online video editing system. The system allows thousands of customers to upload their videos and use tools to edit uploaded videos. Customers will download the final version to their local storage. ii. A flight control system that keeps track of planes near an airport. The positions of planes need to be constantly updated. iii. A data warehouse for a big retailer. The retailer will use the data warehouse to analyze sales. Data is updated once a day during midnight. Problem 4: [20 points] Please specify for each of the following SQL query, what indexes you want to create to speed up the SQL query. • These queries use the database created on page 2. You can assume the tables have many rows. • You need to specify the table and column you want to index. You don't need to write create index statements. • Please also briefly explain why you select this index. Your grade depends on both the index and explanation. Query 1. select price from room_rate where rid = 123 and startdate <= date '2016-11-15' and enddate > date '2016-11-15'; Query 2. select count(*) from reservation r, guest g where g.gid = r.gid and g.gname = 'John'; Query 3. select gname, count(*) from reservation r, guest g where r.gid = g.gid group by gname Query 4. select * from guest where gname like '%James%'; Problem 5: [5 points] Below is the schedule for two transactions T1 and T2. Please briefly explain which transactions satisfy two-phase locking protocol and which does not. Your grade depends on both your answer and explanation. Each line is an operation starting with the transaction the operation belongs to. Lock-X(o) means request an exclusive lock on o. Lock-S(o) means request a shared lock on o. Unlock(o) means release the lock it holds on o. T1 T2 Lock-X(A) Read(A) A := A – 50 Write(A) Lock-X(B) Read(B) B : = B + 50 Write(B) Unlock(A) Unlock(B) Lock-S(A) Read(A) Unlock(A) Lock-S(B) Read(B) Unlock(B) Problem 6. [15 points] Please briefly explain whether the following schedule has a deadlock. Your grade depends on both your answer and explanation. T1, T2, and T3 are 3 transactions. A, B, and C are 3 database rows. Lock-X means requesting an exclusive lock, Lock-S means requesting a shared lock. If you draw a wait-for graph, you will get partial credits even if your answer is wrong. T1 T2 T3 Lock-X(C) Write(C) Lock-X(B) Write(B) Lock-S(A) Read(A) Lock-S(B) Lock-S(C) Lock-S(A) Problem 7: For each of the following statements, decide whether it is true or false. Please use a sentence or two to explain why. Your grade depends both on your answer and explanation. [15 points, 3 points per question] 1. Given the parameters for the following disks, disk 2 is the best. Disk 1: seek time 5 milliseconds, 5400 rpm Disk 2: seek time 4 milliseconds, 10000 rpm Disk 3: seek time 4 milliseconds, 7200 rpm 2. To check whether an index is used to answer a SQL query, you can simply run the same query twice, the first time before the creation of the index and the second time after creation of the index and check whether the second execution is faster than the first one. 3. Query optimization is done manually by DBA because DBA knows how to execute a SQL statement efficiently. 4. Two-Phase-Locking protocol not only ensures that concurrent execution of multiple transactions always gives correct result, but also prevents deadlock. 5. Once a transaction gets rolled back, it cannot be committed.