ANSWERS TO REVIEW QUESTIONS NOT COMPLETED IN CLASS

2. (25 points total, 10 points each subpart) Using the four relations given below for a University database, give the following two queries in SQL. Make any reasonable assumptions.

Student(Name, Studentnumber, Class, MajorDepartment)

Course(Coursename, Coursenumber, CreditHours, Department)

Section(SectionIdentifier, Coursenumber, Semester, Year, Instructor)

GradeReport(Studentnumber, SectionIdentifier, Grade)

a) (15 points) For each Major Department, give the total number of students in that major who were enrolled in Math 109 during the Spring of 2006.

SELECT S.MAJORDEPARTMENT, COUNT (*)

FROM STUDENT S JOIN COURSE C JOIN SECTION T JOIN GRADEREPORT G

WHERE C.DEPARTMENT = “MATH” AND C.COURSENUMBER = 109 AND T.SEMESTER = “SPRING” AND T.YEAR = 2006

GROUP BY S.MAJORDEPT;

b) (10 points) For each Department give the total number of distinct courses offered by that Department in the Fall of 2005.

SELECT C.DEPARTMENT, COUNT( DISTINCT C.COURSENUMBER)

FROM COURSE C JOIN SECTION T

WHERE T.SEMESTER = “FALL” AND T.YEAR = 2005

GROUP BY C.DEPARTMENT;

4. (25 points) For the parallel schedule given below for transactions T1, T2 and T3 :

Trace through the schedule below and show where deadlock occurs using a rigorous two-phase locking scheme. Assume that a locking scheme of upgradeable locks is used. I.e., when one wants to read a data item, they ask for a shared lock, when they want to write that same data item later on, they request an exclusive lock. Explain how the wound-wait scheme for deadlock prevention would prevent deadlock from occurring.

Time T1 T2 T3 _______________________________________________________________________

1 Read(A)

2 Read(C)

3 Read(B)

4 Read(B)

5 Read(A)

6 Write(A)

7 Write(B)

8 Read(B)

9 Write(B)

10 Write(A)

11 Write (C)

12 EOT

13 Read (C)

14 EOT

15 Read (C)

16 EOT

At times 1-5 all read locks would be granted. At Time 6 transaction T1 requests a write lock on item A and since T3 possesses a read lock, the request would be denied and at that time T1 is waiting for T3 to release it’s lock on A. Next, T2 requests a read lock on B at time 8 and it is granted, but at time 9, T2’s write lock request is denied since both T2 and T3 possess read locks on B. At time 10, T3 requests a write lock on A and it is denied since T1 possesses a read lock on A. Thus deadlock occurs at time 10 since there is now a cycle between transactions T1 and T3 in the wait for graph. With the wound-wait deadlock prevention protocol, at time 6 T1 would be allowed to wait since it is younger than T3. At time 9 T2 would be allowed to wait since since it is younger than T3. At time 10 T3 – an older transaction – tries to obtain a write lock on A, but T1, a younger transaction already possesses a read lock, thus T1 would be rolled back and its lock on A would be released. T3 would execute to completion and release it’s locks. T2 would finish and release it’s locks and T1 would have to be restarted.