Question 4

The following table contains the relational algebra operators covered in our course. You should only use these operators to answer the following questions.

Consider the following relation schemas:

Student={SID, Name, Degree, College, Address, Phone} with the primary key {SID},

Course={CourseNo, College, Semester} with the primary key {CourseNo, Semester},

Tutor={TID, Email, CourseNo, Semester} with the primary key {TID, CourseNo, Semester} and the foreign keys: [CourseNo,Semester]⊆Course[CourseNo,Semester] and [TID]⊆Student[SID],

Enrol={SID, CourseNo, Semester, Unit, Status} with the primary key {SID, CourseNo, Semester} and the foreign keys: [CourseNo,Semester]⊆Course[CourseNo,Semester] and [SID]⊆Student[SID].

4.1 Answer the following questions using relational algebra queries. You should only use the relational algebra operators in the above table. You are encouraged to use relational algebra expressions to represent intermediate results if needed.

[a] List the phone numbers of students who studied COMP2400 in ‘S2 2020’ and became a tutor for COMP2400 in ‘S2 2021’.

b] List the TIDs of tutors who had tutored exactly one course in ‘S2 2021’.

4.2 Optimise the following relational algebra query (Your marks will depend on how well you optimise the query in your solution). Additionally, draw the query trees of the query before and after your optimisation.

πSID,Address,P hone(σCourseNo=‘COMP 24000 ((Course ./ Enrol) ./Enrol.SID=Student.SID Student))