2.14

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 works for “BigBank”.
b. Find the ID, name, and city of residence of each employee who works for “BigBank”.
c. Find the ID, name, street address, and city of residence of each employee who works for “BigBank” and earns more than $10000.
d. Find the ID and name of each employee in this database who lives in the same city as the company for which she or he works.

We assume that there is an ID attribute on the employee relation.

We also assume that the attributes person_name and company_name are UNIQUE in the relations employee and company respectively.

  1. \[\Pi_{ID, person\_name}(employee \bowtie_{employee.person\_name = works.person\_name} \sigma_{company\_name="BigBank"}(works))\]

  2. \[\Pi_{ID, person\_name, city}(employee \bowtie_{employee.person\_name = works.person\_name} \sigma_{company\_name="BigBank"}(works))\]

  3. \[\Pi_{ID, person\_name, street, city}(employee \bowtie_{employee.person\_name = works.person\_name} \sigma_{company\_name="BigBank" \wedge salary > 10000 }(works))\]

Method 1:

\[S1 \leftarrow (company \bowtie_{company.company\_name = works.company\_name} works)\]

\[\Pi_{ID, person\_name} (S1 \bowtie_{S1.person\_name = employee.person\_name \wedge S1.city = employee.city} employee))\]

Method 2:

\[\Pi_{ID, person\_name}(employee \bowtie_{employee.person\_name = works.person\_name} works \bowtie_{works.company\_name = company.company\_name \wedge employee.city = company.city} company)\]