COMP9311 Exercise 2

This exercise is aimed at a better understanding of the relational algebra.

Your task: write the relational algebra expressions for the following questions.

Question 1

Consider the following table:

SidNameCourse21333AnneBE21876DaveBE21531JohnBSc21623TimeBScMarkSubjectStudent741011213337010212133368201121333941011215319010212153150101121623STUDENTMARK

  1. Get the student ids of the students who got more than 70 marks in the subject 1011.

  2. Get the names and subject codes for which the students got more than 70 marks in the subject.

  3. Get the name of students whose average marks were over 80.

Question 2

Consider the following relational schema:

author (authorID, firstName, lastName)

authorPub (authorID, pubID, institution)

book (bookID, title, month, year, authorID)

pub (pubID, ptitle, bookID)

  1. Find the name of all authors who are book editors.

  2. Find the name of all authors who are not book editors.

  3. Find the name of all authors who have at least one publication in the database.

  4. Find the name of the authors authored a pub that was published in July.

  5. Find the name of all authors who are book editors but do not have any publication in the database.

Question 3

Consider the following relational schema:

Student (studentID, name, age, gender)

Course (courseID, courseName, faculty)

Enrolment (courseID, studentID)

JobRequirement (job, courseID)

  1. Find the name of students who have enrolled more than 3 courses.

  2. Find the name of female students who have enrolled in at least one course required by the "designer" job.

  3. Find the name of students who have enrolled in all the courses needed by the "designer" job and never enrolled in any courses offered by the "law" faculty.

  4. Find the name of courses in which the students are all male students or all female students.