Question 1: Relational algebra
A database records information about tutorials in a particular unit offering (such as ICT285 this semester). A student is supposed to attend only one of several available tutorials, each of which is taken by a tutor at a particular day and time. A tutor might take several different tutorials.
The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
STUDENT (StudentNo, FamilyName, FirstName, StudentAddress, StudentEmail)
TUTORIAL (TutorialNo, DayAndTime, Lab)
TUTOR (StaffNo, TutorName, TutorEmail)
CLASS (StaffNo, TutorialNo)
ATTENDS (StudentNo, TutorialNo)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
a. List the student number, full name and email of all students.
b. List the day and time for all tutorials held in lab Lon 3.32
c. List the day and time, lab and tutor name for all tutorials.
d. List the student number and full name of all students in the Wednesday 10.30 lab.
e. List the student number and full name of all students in Val's classes.
f. List the student number, full name, tutorial details and tutor name of all students.
g. List the student number of any students who have signed up to attend both the Monday 4.30 and Monday 5.30 tutorial.
h. List the student number of any students who have signed up to attend any or all of the Wednesday tutorials (they are at 9.30, 10.30 and 11.30)
i. List all the students who aren't in any tutorial yet.
j. List the full name of any student who has signed up to attend all tutorials.