Question 6 of 8

Question 6 (4+6+7+3=20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Student(Id,Name,Country), Course(CrsCode,CrsName,Type,Instructor), Results(Id,CrsCode,Grade); Type classifies each course (e.g. MATH, STAT, SYSC, TTMG, ELEC).

Find. (a) students in TTMG or SYSC; (b) students in every course; (c) students who complete every SYSC course OR every TTMG course; (d) a plain-English reading of a given relational-algebra query.

Approach. (a) is a simple join+filter with OR; (b) and (c) are relational-division patterns like Question 5(a)(ii), expressed here directly in SQL; (d) is read inside-out: innermost selection/projection first, then each join, then the outer projection.

1. (a) TTMG or SYSC.

   SELECT DISTINCT r.Id
   FROM   Results r JOIN Course c ON r.CrsCode = c.CrsCode
   WHERE  c.Type = 'TTMG' OR c.Type = 'SYSC';

2. (b) every course (division over the WHOLE course catalogue).

   SELECT s.Id FROM Student s
   WHERE NOT EXISTS (
     SELECT c.CrsCode FROM Course c
     WHERE NOT EXISTS (
       SELECT 1 FROM Results r WHERE r.Id = s.Id AND r.CrsCode = c.CrsCode
     )
   );

3. (c) every SYSC course OR every TTMG course (division over each Type-restricted catalogue, then unioned by OR).

   SELECT s.Id FROM Student s
   WHERE NOT EXISTS (
           SELECT c.CrsCode FROM Course c WHERE c.Type = 'SYSC'
           AND NOT EXISTS (SELECT 1 FROM Results r WHERE r.Id = s.Id AND r.CrsCode = c.CrsCode)
         )
      OR NOT EXISTS (
           SELECT c.CrsCode FROM Course c WHERE c.Type = 'TTMG'
           AND NOT EXISTS (SELECT 1 FROM Results r WHERE r.Id = s.Id AND r.CrsCode = c.CrsCode)
         );

   Check — vacuous-truth edge case

   Relational division is vacuously TRUE for a course type with zero courses of that type, and close to it when a type has only one course: a student who has never taken ANY SYSC course still satisfies "took every SYSC course" if there happen to be zero SYSC courses on file, and a student who has taken the ONE existing TTMG course trivially satisfies "every TTMG course" even though that is a weak sense of "every." This is the standard division semantics (and matches the source's own division-style Q6(b)); a stricter reading would add AND EXISTS (SELECT 1 FROM Course c WHERE c.Type='SYSC') (or the TTMG equivalent) to rule out the zero-course case.
4. (d) reading the given relational-algebra query. π_Name( π_CrsCode(σ_Type='SYSC'Course) ⋈ (σ_Grade='D'Result) ⋈ Student ). Working inside-out: σType='SYSC'Course selects only SYSC courses, and πCrsCode reduces that to just their course codes. Joining that against σGrade='D'Result (natural join on the shared CrsCode column) keeps only D-grade result rows whose course is one of those SYSC codes. Joining THAT against Student (natural join on Id) attaches each surviving result to its student, and the outer πName keeps just the name. In plain English: the query returns the names of students who received a grade of D in at least one SYSC course.