Solution 5

October 3, 2005

Total score: 25 points

1. Problem 5.11 (15 points) One should realize that the information can be extracted from the Trascript relation of the system. As stated in the problem description, the first step is to have the list of all the classes with their enrollment. This can be achieved by using the following expression (based on the operation count_{A/B}(R))

R_{1} = count_{StudID/{CrsCode,Semester}}(T ranscript)[CrsCode, Semester, T otalCount]

(5 points) Similarly, we can compute the number of students with grade < 2 in each class using

R_{2} = count_{StudID/{CrsCode,Semester}}(σ_{Grade<2}(T ranscript))[CrsCode, Semester, NoOfF ail] (5 points) The rest is then simple:

π_{CrsCode,Semester}(σ_{NoOfF ail/T otalCount>0.2}(R_{1}⊳⊲R_{2}))

(5 points)

2. Problem 5.12 (a) (5 points) List all semesters in which all courses (that were ever taught) were offered.

3. Problem 5.13 (5 points) List the names of all students who took CS305 or CS315. Equivalent SQL query

      SELECT S.Name
      FROM Student S, Transcript T
      WHERE S.Id = T.StudId
      AND (T.CrsCode = 'CS305' OR T.CrsCode = 'CS315')