Answer each question given the following relational schema (solutions):

employee
person_name,* street, city
works
person_name,* company_name, salary
company
company_name,* city
manages
person_name,* manager_name

Questions

  1. Draw an entity-relationship (ER) diagram that corresponds to the relational schema.

    employees ER diagram

  2. Find the name of all managers.

    \[\pi_{\text{manager_name}}(\text{manages})\]

  3. Find the name of all employees who live in “Mumbai.”

    \[\pi_{\text{person_name}}(\sigma_{\text{city = Mumbai}}(\text{employee}))\]

  4. Find all employees who work directly for “Jones.”

    \[\pi_{\text{person_name}}( \sigma_{\text{manager_name = Jones}}(\text{manages}))\]

  5. Find all cities of residence of all employees who work directly for “Jones.”

    \[\pi_{\text{city}}( \sigma_{\text{manager_name = Jones}}( \text{manages} \bowtie \text{employee}) )\]

  6. Find the names of all employees in this database who do not work for “First Bank Corporation.”

    \[\pi_{\text{person_name}}( \sigma_{\text{company_name} \neq \text{First Bank Corporation}}( \text{works} ) )\]

    or, if some individuals work for more than one company,

    \[\pi_{\text{person_name}}(\text{works}) - \pi_{\text{person_name}}( \sigma_{\text{company_name = First Bank Corporation}}(\text{works}))\]

  7. Find the names of all employees who live in the same city and on the same street as their managers.

    \[\pi_{\text{person_name}}( \sigma_{\text{employee.street = manager.street} \wedge \text{employee.city = manager.city}}( \text{employee} \bowtie \text{manages} \bowtie \rho_{\text{manager (manager_name, street, city})}( \text{employee}) ) ) )\]

  8. Find the name of the manager of the manager of “Jones.”

    \[\pi_{\text{manager_name}}( \text{manages} \bowtie \rho_{\text{supervisee (person_name)}}( \pi_{\text{manager_name}}( \sigma_{\text{person_name = Jones}}( \text{manages} ) ) ) )\]

  9. Find the names of all employees who earn more than every employee of “Small Bank Corporation.”

    \[\pi_{\text{person_name}}(\text{works}) - \pi_{\text{works.person_name}}( \sigma_{\text{works.salary} \leq \text{sbc.salary}}( \text{works} \times \rho_{\text{sbc}}( \sigma_{\text{company_name = Small Bank Corporation}}( \text{works} ) ) ) )\]