Use the relational algebra to ?nd the list of all “problematic” classes (i.e., coursesemester pairs) where the failure rate is higher than 20%. (Assume, for simplicity, that grades are numbers between 1 and 4, and that a failing grade is anything less than 2.)

Question

Because the relational algebra does not have aggregate operators, we must add them to be able to solve the above problem. The additional operator you should use is count A/B(r).
The meaning of this operator is as follows: A and B must be lists of attributes in r. The schema of count A/B(r) consists of all attributes in B plus one additional attribute, which represents the counted value. The contents of count A/B(r) are de?ned as follows: for each tuple, t ? ?B(r) (the projection on B ), take ?A(?B=t(r)) and count the number of tuples in the resulting relation (where ?B=t(r) stands for the set of all tuples in r whose value on the attributes in B is t ). Let us denote this number by c(t ). Then the relation count A/B(r) is defined as {< t , c(t ) >| t ? ?B(r)}.
You should be able to recognize the above construction as a straightforward adaptation of GROUP BY of SQL to the relational algebra.

Answer 1

First compute the total number of students in each course and the number of failures:





Computing the “problematic” classes is now easy:

```

?CrsCode(?FailCount/TotCount>0.2(AuxCounts))

```