Problem 1: 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: 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: Please answer questions a and b on RAID levels.

a) 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.

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:

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:

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.

Problem 6.

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.

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.

Attachment:- Database used in the EXAM.rar