Company Relational Database for Exercise
employee (person_name, street, city)
works (person_name, company_name, salary)
company (company_name, city)
manages (person_name, manager_name)
- Consider the Company Relational Database, where the primary keys are underlined. Give an expression in the relational algebra to express each the following queries:
- Find the names of all employees who live in the same city and on the same street as do their manager
- Find the names of all employees in this database who do not work for “First Bank Corporation”
- Find the names of all employees who earn more than every employee of “Small Bank Corporation”
- Consider the Company Relational Database, where the primary keys are underlined. Give an expression in the relational algebra to express each the following queries:
- Find all employees who work directly for “Jones.”
- Find all cities of residence of all employees who work directly for “Jones.”
- Find the name of the manager of the manager of “Jones.”
- Find the employees who earn more than all employees living in the city “Mumbai.”
3.
- Find the names of all employees who work for “Small Bank Corporation”.
- Find the names and cities of residence of all employees who work for “Small Bank Corporation”.
- Find the names, street addresses, and cities of residence of all employees who work for “Small Bank Corporation” and earn more than $5,000.
- Find the names of all employees in this database who live in the same city as the company for which they work.
- Assume the companies maybe located several cities. Find all companies located in every city in which “First Bank Corporation” is located
- Choose 2 relational algebra (RA) expressions (from the answer of previous exercise). For each RA, create a Query Evaluation Plan (QEP) by selecting the appropriate algorithm.
- Let relations r1(A,B,C) and r2(C,D,E) have the following properties :
r1 has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one block, and 30 tuples of r2 fit on one block.
Estimate the number of block transfers and seeks require, using each of the following join strategies for r1|x|r2:
- Nested-loop join.
- Block nested-loop join.
Jawab
1.
A) Π person_name ((employee ⋈ manages) ⋈ (manager_name = employee2.person_name ^ employee.street = employee2.street ^ employee.city = employee2.city)(ρ employeew(employee)))
B) Π person_name (σ company_name ≠ “First Bank Corporation” (works))
C) Π person_name (works) – (Π works.person_name (works ⋈ (works.salary ≤ works2.salary ^ works2.company_name = “Small Bank Corporation”) ρ works2 (works)))
2.
A) Π person_name, street, city (σ employee.person_name = manager.person_name ^manager_name = ‘Jones'(employee x manages))
B) Π city (σ employee.person_name = manager.person_name ^ manager_name = ‘Jones'(employee x manages))
C) Π manager.person_name ∩ Πmanager.person_name = ‘Jones’ (manages))
D) Π person_name (σ salary > employee.person_name ^ Mumbai (employee x salary x company ))
3.
A) Π person_name (σ company_name = ‘Small Bank Corporation’ (works |x| employee))
B) Π person_name, city (σ company_name = ‘Small Bank Corporation’ (works |x| employee))
C) Π person_name, street, city (σ company_name = ‘Small Bank Corporation’ ^ salary > 5000 (works |x| employee))