## 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 $\rho_{CertifiedPilots}(\pi_{employeeid}(\sigma_{name\ LIKE\ \%Boeing\%}(Aircraft \Join_{id = aircraftid} IsCertified)))$, and then the result is $\pi_{name}(Employees\Join_{id = employeeid}(\pi_{employeeid}(IsCertified) - CertifiedPilots))$