Query for a Relational Banking Enterprise

Relational entity sets for a Banking Enterprise:

employee (employee-name, street, city)
works (employee-name, company-name,salary)
company (company-name, city)
manages (employee-name, manager-name)

Here: Underline is representing a strong entity set.


      1. Consider the relational database for the above entity sets. Give an expression in the Relational   algebra for each request:

a. Modify the database so that Jones now lives in Newtown.
b. Give all employees of First Bank Corporation a 10 percent salary raise.
c. Give all managers in this database a 10 percent salary raise.
d. Give all managers in this database a 10 percent salary raise, unless the salary
would be greater than $100,000. In such cases, give only a 3 percent raise.
e. Delete all tuples in the works relation for employees of Small Bank Corporation.

     2. Consider the relational database of above entity sets. Give a relational-algebra expression for   each of the following queries:
a. Find the company with the most employees.
b. Find the company with the smallest payroll.
c. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.

3.  Using the bank example, write relational-algebra queries to find the accounts held by more than two customers in the following ways:
a. Using an aggregate function.
b. Without using any aggregate functions.


No comments:

Post a Comment

Popular Posts