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

  1. To be familiar with the relational data model.
  2. To be familiar with RelaX relational algebra calculator.
  3. Apply some relational algebra practically.
  4. 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)

  1. Get all instructors of the’ Physics’ department.
σ dept_name = 'Physics' (instructor)
  1. Find all instructors who earn a salary greater than $70,000.
sigma salary > 70000 (instructor)
  1. Find all instructors of the ‘Physics’ department with salaries greater than $70,000.
σ dept_name = 'Physics' ∧ salary > 70000 (instructor)
  1. Find all departments whose names are the same as their building names.
σ dept_name = building (department)

The Project Operation (π) (3 marks)

  1. Display the ID, name, and salary of all instructor
π ID, name, salary (instructor)
  1. Find the names of all instructors in the ‘Physics’ department.
π name (σ dept_name = 'Physics' (instructor))
  1. Find the list of course IDs of all courses taught in the spring 2009 semester.
π course_id (σ semester = 'Spring'year=2009 (section))

The Cartesian-Product Operation (X) (2 marks)

  1. Display all instructors with courses they teach.
σ instructor.ID = teaches.ID (instructor ⨯ teaches)
  1. List instructor name, and course ID for the course that they teach.
π instructor.name, teaches.course_id (σ instructor.ID = teaches.ID (instructor ⨯ teaches))

The Join Operation (⋈) (2 marks)

  1. Display all instructors with all courses they teach.
--(Theta Join) instructor ⋈ instructor.ID = teaches.ID teaches --(Natural join) instructor ⋈ teaches

Set Operations - Union operation (υ) (2 marks)

  1. Find all the courses offered in either Fall 2009, Spring 2010, or both semesters.
π course_id (σ semester = 'Fall'year = 2009 (section)) ∪ π course_id (σ semester = 'Spring'year = 2010 (section))

Set Operations - Intersection (∩) (2 marks)

  1. Find all the courses offered in both the Fall 2009 and Spring 2010 semesters.
π course_id (σ semester = 'Fall'year = 2009 (section)) ∩ π course_id (σ semester = 'Spring'year = 2010 (section))

Set Operations - Difference (-) (2 marks)

  1. Find all the courses taught in the Fall 2009 semester but not in Spring 2010 semester.
π course_id (σ semester = 'Fall'year = 2009 (section)) – π course_id (σ semester = 'Spring'year = 2010 (section))

The Assignment Operation (←) (2 marks)

Note: The assignment operator in RelaX is ( = ) not an arrow.

  1. Find all the courses taught in Fall-2009 semester and assign it to “courses_fall_2009”
courses_fall_2009 = π course_id ( σ semester = 'Fall'year=2009 (section)) 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)

  1. Return the student relation and rename it to S.
ρ S student
  1. Find all students with credits greater than the credit of the student named “Levy”.
σ student.tot_cred > S.tot_cred (σ S.name = 'Levy' ( ρ S student ) ⨯ student)

Try some Equivalent Quires

  • Give two relational algebra expressions that find information about courses taught by all instructors in the ‘Physics’ department.
σ dept_name = 'Physics' (instructor ⨝ instructor.ID = teaches.ID teaches)

OR

(σ dept_name = 'Physics' (instructor)) ⨝ instructor.ID = teaches.ID teaches

Lab Task 2 (5 marks)

  1. Find the ID and name of each instructor in the ‘Physics’ department.
  2. Find the ID and name of each instructor in a department located in the building ‘Watson’.
  3. Find the Instructor who earns a salary greater than the budget of his department.

Lab Task 3 (5 marks)

  1. Find the ID and name of each student who has taken at least one course section in the year 2010.
  2. Find the ID and name of each student who has not taken any course section in the year 2010.

Lab Task 4 (10 marks)

  1. Find the ID and name of all students who have taken all the courses offered by the Comp. Sc. department.
  2. Find the ID and name of all students who have taken all the courses that are taken by student named 'Zhang'.
  3. Find the name and salary of all instructors who earn the highest salary.
tags: Database Systems IUG Computer Engineering
End Of Lab 1