Consider the employee database of Figure 2.17. Give an expression in the rela- tional algebra to express each of the following queries:

Consider the employee database of Figure 2.17. Give an expression in the rela- tional algebra to express each of the following queries:

1. Find the ID and name of each employee who does not work for “BigBank”.
2. Find the ID and name of each employee who earns at least as much as every employee in the database.

EXPERT ANSWER

Please find below relational algebra expressions for 2 questions.

1.

πID,PERSON_NAME(σ(company_name!="BigBank")Employee)

π symbol is Projection – Removes duplicates and displays the mentioned column

take an example In a table they are 3 columns X, Y, Z. if we say π(YZ) displays the Y and Z columns without duplicates.

σ is for selection with condition σ(salary>5000)employee.

Here salary is the column and employee is the table name and display the all the rows who has salary >5000

for above answer

2. π employee.id,employee.person_name (σ(work.ID=employee.id and work.salary = (σ(salary)work))) (employeeXworks);

using cross product, projection adn selection to work with multiple tables.

and we represent cross product by X.

joining based on work.id=employee.id and our condiition is work.salary = at least salary of every employee in database