Relational algebra problems

a.Retrieve the names of all employees in department 5 who work more than 10 hurs per week on the ‘Project x’ project

• This is just a select, project and join problem

• Get name and SSN of all employees in dept 5

• Find the PNO of ProjectX

• Join 2 with Works_On to get the ESSN for employees whose hours > 10

• Join 3 with 1 to get the required information

b. List the names of all employees who have a dependent with the same first name as themselves

• Use set intersection:

• Project the Fname, ssn from Employee

• Project Dependent_name, ESSN from Dependent

• Intersect 2 and 3 to get tuples in both

c.Find the names of all employees who are directly supervised by ‘Franklin Wong.

• This involves joining Employee with itself after some selects and projects

• Select the tuples with name=F Wong and project the SSN

• Join 1 to Employee with the join condition SSN=superssn

d. For each project, list the project name and the total hours per week spend on that project

• Must use group-by and a function to find the sum of groups

• On the Works_On relation, group by PNO and sum the hours

• Join 1 to Project to get the project name.

e. Retrieve the names of all employees who work on every project

• This is a division problem

• Project pnumber from Project

• Project ESSN and PNO from Work_On

• Divide 3 by 2

• Join that to Employee to get the names

f. Retrieve the names of all employees who do not work on any project

• Subtract problem

• Project SSN from Employee

• Project ESSN from Work_On

• Subtract 3 from 2

• Join r to Employee to get names

g. For each department, retrieve the department name and the average salary of all employees working in that department

• Group by, function problem

• On the Employee table group by DNO and average salary

• Join 2 with department to get the name

h. Retrieve the average salary of all female employees

• From the employee table group by sex and use the function to find the average salary

• Project from 1 sex=‘F’

i. Find the names and addresses of all employees who work on at least one project located in Houston, but whose department has no location in Houston.

• Break the query into two queries, then subtract the second part from the first

• First find the employees who work on a project in Houston

• Next, find the departments with no location in Houston

• Subtract 2 from 1

j. List the last names of all department managers who have no dependents

• Subtract problem

• Project MGRSSN from Department to get the SSN of all managers

• Project ESSN to get the SSN of all employees with dependents

• Subtract 2 from 1 to get managers without dependents

• Join 3 to Employee to get the names