Database Systems Lab 2
The Relational Algebra
The Islamic University of Gaza
Engineering Faculty
Department of Computer Engineering
Authors: Usama R. Al Zayan & Rasha E. Kahil2023/02/07
Expecteed outcomes
- To be familiar with the relational data model.
- To be familiar with RelaX relational algebra calculator.
- Apply some relational algebra practically.
- Be able to solve simple and complex queries by applying relational algebra expressions.
Lab 2: Time and Plan
Tasks |
Timing |
Quiz 1 |
10 min |
Task 1 |
35 min |
Task 2 |
15 min |
Task 3 |
15 min |
Task 4 |
20 min |
Our database schema
- Let’s take a look at our database schema.

- And we can describe it like this:
- classroom = { building, room_number, capacity }
- department = { dept_name, building, budget }
- course = { course_id, title, dept_name, credits }
- instructor = { ID, name, dept_name, salary }
- section = {course_id, sec_id, semester, year, building, room_number, time_slot_id}
- teaches = { ID, course_id, sec_id, semester, year }
- student = { ID, name, dept_name, tot_cred }
- takes = { ID, course_id, sec_id, semester, year, grade }
- advisor = { s_ID, i_ID }
- time_slot = { time_slot_id, day, start_time, end_time }
- prereq = { course_id, prereq_id }
Lab Tasks
Task 1: Relational algebra operations (20 marks)
The Select Operation (σ) (3 marks)
- Get all instructors of the’ Physics’ department.
- Find all instructors who earn a salary greater than $70,000.
- Find all instructors of the ‘Physics’ department with salaries greater than $70,000.
- Find all departments whose names are the same as their building names.
The Project Operation (π) (3 marks)
- Display the ID, name, and salary of all instructor
- Find the names of all instructors in the ‘Physics’ department.
- Find the list of course IDs of all courses taught in the spring 2009 semester.
The Cartesian-Product Operation (X) (2 marks)
- Display all instructors with courses they teach.
- List instructor name, and course ID for the course that they teach.
The Join Operation (⋈) (2 marks)
- Display all instructors with all courses they teach.
Set Operations - Union operation (υ) (2 marks)
- Find all the courses offered in either Fall 2009, Spring 2010, or both semesters.
Set Operations - Intersection (∩) (2 marks)
- Find all the courses offered in both the Fall 2009 and Spring 2010 semesters.
Set Operations - Difference (-) (2 marks)
- Find all the courses taught in the Fall 2009 semester but not in Spring 2010 semester.
The Assignment Operation (←) (2 marks)
Note: The assignment operator in RelaX is ( = ) not an arrow.
- Find all the courses taught in Fall-2009 semester and assign it to “courses_fall_2009”
NOTE: An assignment (= definition of a variable) is invalid relational algebra expression on its own.
If you miss the actual query in RelaX, an error is thrown
(Error: only assignments found; query is missing).
The Rename Operation (ρ) (2 marks)
- Return the student relation and rename it to S.
- Find all students with credits greater than the credit of the student named “Levy”.
Try some Equivalent Quires
- Give two relational algebra expressions that find information about courses taught by all instructors in the ‘Physics’ department.
OR
Lab Task 2 (5 marks)
- Find the ID and name of each instructor in the ‘Physics’ department.
- Find the ID and name of each instructor in a department located in the building ‘Watson’.
- Find the Instructor who earns a salary greater than the budget of his department.
Lab Task 3 (5 marks)
- Find the ID and name of each student who has taken at least one course section in the year 2010.
- Find the ID and name of each student who has not taken any course section in the year 2010.
Lab Task 4 (10 marks)
- Find the ID and name of all students who have taken all the courses offered by the Comp. Sc. department.
- Find the ID and name of all students who have taken all the courses that are taken by student named 'Zhang'.
- Find the name and salary of all instructors who earn the highest salary.
End Of Lab 1