Express each of the following queries in tuple relational calculus, domain relational calculus, and QBE using the schema of Figure 3.4

a. Find all courses that are taught by professors who belong to either of the departments EE and MGT.
b. List the names of all students who took courses in spring 1997 and fall 1998.
c. List the names of all students who took courses from at least two professors in di?erent departments.
d. Find all courses in department MGT that were taken by all students.
e. Find every department that has a professor who has taught all courses ever o?ered by that department.

Compare the two calculi, QBE, and SQL with respect to the ease of their use for formulating the above queries.

a. TRC:



{C.CrsCode, C.CrsName |

        Course(C) AND ?T ?Teaching ?P ?Professor

        (P.Id= T.ProfId AND T.CrsCode= C.CrsCode

          AND (P.DeptId= ’EE’ OR P.DeptId= ’MGT’ )) }

DRC



{CrsCode, CrsName |

        ?DeptId1?DeptId2?Descr?ProfId?Semester?Name

                 (Course(DeptId1,CrsCode,CrsName,Descr) AND

                    Teaching(ProfId,CrsCode,Semester) AND

                    Professor(ProfId,Name,DeptId2) AND

                      (DeptId2= ’EE’ OR DeptId2= ’MGT’ )) }

b. TRC:



{S.Name | Student(S) AND ?T1 ?Transcript ?T2 ?Transcript

               (S.Id = T1.StudId AND T1.Semester = ’S1997’

                AND S.Id = T2.StudId AND T2.Semester = ’F1998’) }

DRC:



{Name |?Id?Address?Status?CrsCode1?CrsCode2?Grade1?Grade2

                (Student(Id,Name,Address,Status) AND

                  Transcript(Id,CrsCode1,’S1997’,Grade1) AND

                  Transcript(Id,CrsCode2,’F1998’,Grade2) ) }

c. TRC:



{S.Name | Student(S) AND ?R1 ? Transcript ?R2 ? Transcript

                 ?T1 ? Teaching ?T2 ? Teaching

                 ?P1 ? Professor ?P2 ? Professor

                 (R1.StudId= S.Id AND R2.StudId= S.Id

                 AND R1.CrsCode= T1.CrsCode AND R2.CrsCode= T2.CrsCode

                 AND T1.ProfId= P1.ProfId AND T2.ProfId= P2.ProfId

                 AND P1.DeptId = P2.DeptId) }

DRC:



{Name |?Id?Address?Status

                ?CrsCode1?Semester1?Grade1

                ?CrsCode2?Semester2?Grade2

                ?ProfId1?ProfId2

                ?PName1?DeptId1?PName2?DeptId2

                (Student(Id,Name,Address,Status) AND

                 Transcript(Id,CrsCode1,Semester1,Grade1) AND

                 Transcript(Id,CrsCode2,Semester2,Grade2) AND

                 Teaching(ProfId1,CrsCode1,Semester1) AND

                 Teaching(ProfId2,CrsCode2,Semester2) AND

                 Professor(ProfId1,PName1,DeptId1) AND

                 Professor(ProfId2,PName2,DeptId2) AND

                      (DeptId1 = DeptId2 ) }

d. TRC:



{C.CrsCode, C.CrsName | Course(C) AND C.DeptId = ’MGT’ AND

                ?S ? Student ?R ? Transcript

                (R.StudId= S.Id AND R.CrsCode= C.CrsCode) }

DRC:



{CrsCode, CrsName |?Descr (Course(’MGT’,CrsCode,CrsName,Descr)) AND

                ?Id ? Student.Id ?Semester?Grade

                    (Transcript(Id,CrsCode,Semester,Grade) ) }

e. TRC:



{P.DeptId | Professor(P) AND

        ?C ? Course(C.DeptId= P.DeptId ?

                   ?T ? Teaching(T.ProfId= P.Id AND T.CrsCode= C.CrsCode))}

DRC:



{DeptId |?ProfId?Name (Professor(ProfId, Name, DeptId) AND

        ?CrsCode?CrsName?Descr

                  (Course(DeptId, CrsCode, CrsName, Descr) ?

                   ?Semester Teaching(ProfId, CrsCode, Semester) )) }

The DRC formulation is more complicated due to the need to quantify every domain variable. However, in cases (a), (b), and (c), all quanti?ers are existential and so they can be dropped according to the convention discussed in this chapter. Dropping quanti?cation is not feasible in (d) and (e) without introducing ambiguity, because ? and ? are mixed together (see the discussion at the end of Section 13.3).