2.8

Consider the employee database of Figure 2.17. Give an expression in the relational algebra to express each of the following queries: a. Find the ID and name of each employee who does not work for “BigBank.
b. Find the ID and name of each employee who earns at least as much as every employee in the database.

a. To find employees who do not work for BigBank, we first find all those who do work for BigBank. Those are exactly the employees not part of the desired result. We then use set difference to find the set of all employees minus those employees that should not be in the result.

${\Pi }_{ID,person\_name}(employee)-{\Pi }_{ID,person\_name}(employee{\bowtie }_{employee.ID=works.ID}({\sigma }_{company\_name="BigBank"}(works)))$

b. We use the same approach as in part a by first finding those employees who do not earn the highest salary, or, said differently, for whom some other employees earns more. Since this involves comparing two employee salary values, we need to reference the employee relation twice and therefore use renaming.

${\Pi }_{ID,person\_name}(employee)-{\Pi }_{A.ID,A.person\_name}({\rho }_A(employee){\bowtie }_{A.salary<B.salary}{\rho }_B(employee))$

Comment: This is the answer that is provided here. But if you look at Figure 2.17 closely the employee relation doesn’t have an attribute called salary.