Operators

  • $\sigma$ Selection, e.g. $\sigma_{firstname=John}(students)$
  • $\pi$ Projection, e.g. $\pi_{name, age}(students)$, it only returns distinct rows.
  • $\times$ Cross Product
  • $-$ Set Difference
  • $\cup$ Set Union
  • $\rho$ Renaming, e.g. $\rho_{sid \rightarrow studentId}(students)$
  • $\cap$ Set Intersection
  • $\Join$ Natural Join
  • $\Join_{\theta}$ Conditional/Theta Join, $R\Join_{\theta}S = \sigma_\theta(R\times S)$
  • $/$ Division, $A/B = \left\lbrace \langle x \rangle \mid \forall \langle y \rangle \in B, \exists \langle x, y \rangle \in A \right\rbrace$

Exercises

Given the following schema, primary keys are in bold, write relational-algebra queries to answer the following problems.

  • Employees(id, name, salary)
  • Aircraft(id, name, range)
  • Flights(flno, origin, dest, dist, departs, arrives)
  • IsCertified(employeeid, aircraftid), foreign keys: employee_id from Employees(id), aircraft_id from Aircraft(id)
  1. Find the id’s of pilots who are certified for some Boeing aircraft. Make sure that a pilot’s id does not appear more than once.
  2. Find the id’s and names of all pilots who are not certified for any Boeing aircraft.

Solutions

  1. $\pi_{employeeid}(\sigma_{name\,\, \tt{LIKE}\,\, \%Boeing\%}(Aircraft \Join_{id = aircraftid} IsCertified))$
  2. First, let’s create a temp relation , and then the result is