Database used in the ASSIGNMENT:

You will be using the following tables in the exam.

drop table grades;
drop table student;
drop table teacher;
drop table class;

create table student(
sid integer, --- student ID
sname varchar(50), --- student name
primary key (sid));

create table teacher(
tid integer, --- teacher ID
tname varchar(50), ---- teacher name
primary key (tid));

create table class(
cid integer, --- class ID
cname varchar(50),--- class name
year integer, --- year of class
semester varchar(10), -- fall or spring
credit integer, -- number of credit
tid integer, --- teacher id
primary key (cid),
foreign key (tid) references teacher(tid));

create table grades(
sid integer, --- student ID
cid integer, --- product ID
grade integer, --- grade: 4.0:A, 3: B, 2: C, 1:D, 0:F
primary key (sid, cid),
foreign key (sid) references student(sid),
foreign key (cid) references class(cid));

insert into student values (1, 'John');
insert into student values (2, 'Alice');
insert into student values (3, 'Bob');
insert into student values (4, 'Cathy');
insert into student values (5, 'Jeff');

insert into teacher values (1, 'Dr. Chen');
insert into teacher values (2, 'Dr. Smith');

insert into class values(1,'IS 633', 2015, 'fall', 3,1);
insert into class values(2,'IS 633', 2014, 'fall', 3,1);
insert into class values(3,'IS 603', 2015, 'fall', 3,2);
insert into class values(4,'IS 603', 2015, 'spring', 3,2);

insert into grades values(1,1,4);
insert into grades values(2,1,3);
insert into grades values(4,1,2);

insert into grades values(3,2,3);
insert into grades values(5,2,4);

insert into grades values(1,3,4);
insert into grades values(5,3,3);

insert into grades values(2,4,3);
insert into grades values(3,4,2);
insert into grades values(4,4,4);

Problem 1: Please create a PL/SQL procedure to print out names and grade of students taking a given class in a given year and semester. The input parameters are the class name, year, and semester. There is no output parameter.

Problem 2: Please create a PL/SQL function to return the average grade of students taking a given class in a given year and semester. The input parameters are the class name, year, and semester. Please write an anonymous PL/SQL program to call the function and print out returned value if it is not null. Please check returned value when calling the function.

Problem 3: Please answer questions a and b on RAID levels.

a) Suppose you are given 6 data blocks and 4 disks in RAID level 10. Please fill in data blocks in the figure below. You can use 1, 2, ... to represent data blocks 1, 2, ....

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 your answers.

i. A flight control system. The system needs to handle many flights and the airplanes' positions are changing all the time.
ii. Storing temporary tables in a database. These tables are only used for a short period of time and will be discarded afterwards.
iii. A data warehouse for managers to analyze sales. Data is updated once a week.

Problem 4:

Please specify what indexes you want to create to speed up the SQL queries below. These queries use the database created on page 2 (you can assume the tables have many rows so indexes are needed). You need to specify the table and columns 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 * from grades where grade < 2 and cid = 3;

Query 2. select * from student s, grades g where s.sid = g.sid;

Query 3. select sid, avg(grade) from grades group by sid;

Query 4. Select * from class where cname like '%633%';

Problem 5: Below is the schedule for three transactions T1, T2, and T3. Please briefly explain which transactions satisfy two-phase locking protocol and which don't. 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                               T3

T1: Lock-X(A)          

T1: Read(A)  

T1: A := A - 50         

T1: Write(A) 

T1: Unlock(A)          

                                                                        T3: Lock-S(A)

                                                                        T3: Read(A)

                                                                        T3: Print(A)

                                                                        T3: Unlock(A)

T2: Lock-S(B)

                                    T2: Read(B)

                                    T2: Print(B)

                                    T2: Unlock(B)                                                                       

T1: Lock-X(B)          

T1: Read(B)  

T1: B : = B + 50        

T1: Write(B) 

T1: Unlock(B)

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.

T1                                T2                                T3             

T1: Lock-X(C)                       

T1: Write(C)             

                                     T2: Lock-X(B)           

                                    T2: Write(B)  

                                                                        T3: Lock-S(A)

T3: Read(A)

                                                                        T3: Lock-S(C)

T1: Lock-S(B)                                               

                                    T2: Lock-S(A)

                                    T2: Read(A)   

Problem 7: For each of the following statement, decide whether it is true or false. Please use a sentence or two to explain why. The grade depends on both your answer and explanation. [20 points, 4 points per question]

1. Based on the following parameters, disk 1 is faster than disk 2.
Disk 1: seek time 2 milliseconds, 10000 rpm
Disk 2: seek time 3 milliseconds, 7200 rpm

2. Query optimization is done manually by the database administrator because DBA knows how to optimize a query.

3. If a transaction T1 already gets a shared lock on a data item A, and there is no other transaction that has a lock on A, T1 can get an exclusive lock on A.

4. To find out whether an index is used by a SQL query, you can do the following: 1) run the query when the index is not created; 2) create the index; 3) run the same query again and check whether the query runs faster after the index is created. If so the index is used.

5. Two-phase locking protocol not only ensures that concurrent execution of transactions will give correct results, but also prevents deadlock.