Consider the schemas of following relational database for a company. The company
ID: 3567105 • Letter: C
Question
Consider the schemas of following relational database for a company. The company has different departments at different cities in a state:
employee(employee-id, dept-id, name, street, city, salary)
department(dept-id, dept-name, city)
manage(employee-id, manager-id)
NOTES:
manager-id and employee-id in the manage relation are foreign keys to the employee relation.
dept-id in the employee relation is a foreign key to the department relation.
An employee belongs to a single manager only. That is, an employee only works for one manager. Some employees do not have a manager.
1.Give an expression in Relational Algebra for each of the following queries.
2. .Find the names of all employees who work for the Accounting Department (dept-name= Accounting) and who earn more than $10,000 per annum.
3. Find all employees in the database who live in the same city as the department for which they work.
4.Find all employees in the database who live in the same cities and on the same streets as do their managers.
5.Find all employees in the database who do not work for the Accounting Department.
6.Consider the two tables T1 and T2 shown in Figure 6.15 (see next page). Show the results of the following operations:
NOTE: |X| is the JOIN operation.
Explanation / Answer
I have solved this question earlier with different figures. Please workout using yours figures. If you need any further help just PM me. If I have helped you please rate me 5 stars first (before you rate anyone else)
Consider the schemas of following relational database for a company. The company has different departments at different cities in a state:
employee(employee-id, dept-id, name, street, city, salary)
department(dept-id, dept-name, city)
manage(employee-id, manager-id)
NOTES:
manager-id and employee-id in the manage relation are foreign keys to the employee relation.
dept-id in the employee relation is a foreign key to the department relation.
An employee belongs to a single manager only. That is, an employee only works for one manager. Some employees do not have a manager.
Give an expression in SQL for each of the following queries.
1. Create the employee and the manage schemas, use appropriate data types. employee-id is a 9-digit integer.
2. Find the names of all employees who work for the Accounting Department (dept-name =