2.18

Write the following queries in relational algebra, using the university schema.

  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 ID and name of each student who has taken at least one course in the “Comp. Sci.” department.
  4. Find the ID and name of each student who has taken at least one course section in the year 2018.
  5. Find the ID and name of each student who has not taken any course section in the year 2018.

  1. \(\Pi_{ID, name}(\sigma_{dept\_name = "Physics"}(instructor))\)
  2. \(\Pi_{ID, name}(instructor \bowtie_{instructor.dept\_name = department.dept\_name} (\sigma_{building = "Watson"}(department)))\)
  3. \(\Pi_{student.ID, student.name}(\sigma_{dept\_name = "Comp. Sci"}(student \bowtie_{student.ID = takes.ID} takes \bowtie_{takes.course\_id = course.course\_id} course))\)
  4. \(\Pi_{student.ID, student.name}(\sigma_{year = 2018}(student \bowtie_{student.ID = takes.ID} takes)\)
  5. \(\Pi_{ID, name}(student) - \Pi_{student.ID, student.name}(\sigma_{year = 2018}(student \bowtie_{student.ID = takes.ID} takes)\)