ICT285 Database Concepts

Question 1: Relational algebra

You have been asked to work with a database that records information about various lectures that are available for students at a conference. Each lecture addresses a topic, is presented by a lecturer. There are a limited number of seats in each of the venues, and this is reflected in the maximum number of students who can attend a lecture (capacity).The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
Provide relational algebra (NOT SQL) queries to find the following information.

NOTE:

You can use the symbols  s, P, etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer. 

You do not need to try to make efficient queries – just correct ones.

Where you use a join, always show the join condition.

a. List the name and university of all lecturers.

b. List the lecture name and description for all lectures on the topic of ‘Biological Diversity’. 

c. List the names of students who attended a lecture on the topic of ‘Biological Diversity’, ‘Bugs and Bubs’, or both.

d. List the names of all students who attended a lecture by a lecturer from a university in Malaysia.

e. List the names of students from Murdoch University who went to a lecture on the theme of ‘Biological Diversity’ run by a lecturer from a university in Italy.

f. List the lecture number, name and topic of all lectures, and the names of the students that attended, if any.

g. List the name of any students who went to both the lecture named ‘Introduction to Biology’ and the lecture ‘Darwin 101’.

h. List the name, biography and university of all lecturers from a university in Greece.

i. List the names of the students who did NOT attend the lecture named ‘Grassland Cultivation Masterclass’.

j. List the names of any students who went to all lectures.