Transcript Retrieve the names of employees who work on every project

Exercise 1
Relational Algebra
Database System-dww
6 Relations of Company
Database System-dww
Exercises
(a) Retrieve the names of employees in
department 5 who work more than 10 hours
per week on the 'ProductX' project.
(b) List the names of employees who have a
dependent with the same first name as
themselves.
(c) Find the names of employees that are
directly supervised by 'Franklin Wong'.
(d) For each project, list the project name and
the total hours per week (by all employees)
spent on that project.
(e) Retrieve the names of employees who
work on every project.
Database System-dww
Exercises
(f) Retrieve the names of employees who do
not work on any project.
(g) For each department, retrieve the
department name, and the average salary of
employees working in that department.
(h) Retrieve the average salary of all female
employees.
(i) Find the names and addresses of
employees who work on at least one project
located in Houston but whose department
has no location in Houston.
List the last names of department managers
who have no dependents.
Database System-dww
(a) Retrieve the names of employees in department 5 who work more
than 10 hours per week on the 'ProductX' project
Graphical approach can be used to help the logical and prosedural
approach
Example of above query, the graphical approach: clue from query the
relations that (may) work on the query are in the red text
P
Pnumber=Pno
Pname=‘ProductX’
ProductX
W
ESSN=SSN
Hours > 10
Fname, Lname (?)
Dno = 5
> 10
WORKS_ON_ProductX (Rel.A)
(PNAME=‘ProductX’(PROJECT)
E
5
PNUMBER=PNO(WORKS_ON))
EMPLOYEE_DEPT5_WORKS_MORE_10_onProductX (Rel.B)
((EMPLOYEE) SSN=ESSN( HOURS>10(Rel.A)))
RESULT  FNAME,LNAME(DNO=‘5’ (Rel.B))
Database System-dww
List the names of employees who have a
dependent with the same first name as
themselves.
Graphical approach
Dp
Depend_name=Fname
ESSN=SSN
E
Fname, Lname (?)
RESULT  FNAME,LNAME(DEPENDENT
(ESSN,DEPEND_NAME)=(SSN,FNAME)(EMPLOYEE))
Database System-dww
(c) Find the names of employees that
are directly supervised by 'Franklin
Wong'.
Graphical for next questions (try by
Urself)
E_SSN_FRANKLIN_WONG(Rel.A)
( SSN( FNAME=‘FRANKLIN’ LNAME=‘WONG’(EMPLOYEE)))
E_SUPERVISED_BY_FRANKLIN_WONG (Result)
( FNAME, LNAMES(EMPLOYEE) SUPERSSN=SSN(Rel.A))
Database System-dww
(d) For each project, list the project
name and the total hours per week (by
all employees) spent on that project
Using function
TOTAL_HOURS_PROJECT (Rel.A)
(PNO
ℱSUM HOURS(WORKS_ON))
RESULT
( PNAME, TOT_HOURS(Rel.A)
PNO=PNUMBER(PROJECT))
Database System-dww
(e) Retrieve the names of employees
who work on every project
ALL_EMPLOYEE(SSN, PNO)(Rel.A) ( SSN, PNO(WORKS_ON))
ALL_PROJECT(PNO)(Rel.B) ( PNO(PROJECT))
E_ALL_PROJECT ( Rel.A÷Rel.B)
RESULT ( FNAME, LNAME (E_ALL_PROJECT * EMPLOYEE))
Database System-dww
(f) Retrieve the names of employees
who do not work on any project
ALL_EMPLOYEE(SSN)(Rel.A) ( SSN(EMPLOYEE))
ALL_EMPLOYEE_WORKING(SSN)(Rel.B) ( ESSN(WORKS_ON))
E_NOT_WORKING ( Rel.A - Rel.B)
RESULT ( FNAME, LNAME (
(EMPLOYEE) SSN=ESSN(E_NOT_WORKING))
Database System-dww
(g) For each department, retrieve the
department name, and the average salary of
employees working in that department
AVERAGE_SALARY_DEPARTMENT(Rel.A)
(DNO
ℱAVG SALARY(EMPLOYEE))
RESULT
( DNO, AVG_SALARY(Rel.A * EMPLOYEE))
(h) Retrieve the average salary of all female
employees
RESULT
ℱAVG SALARY( SEX=‘F’(EMPLOYEE))
Database System-dww
(i) Find the names and addresses of
employees who work on at least one
project located in Houston but whose
department has no location in Houston
E_WORK_PROJ_IN_HOUSTON(Rel.A)
( ESSN(WORKS_ON)
PNO=PNUMBER
( PLOCATION=‘HOUSTON’(PROJECT)))
E_D_NOT_IN_HOUSTON(Rel.B)
( SSN(EMPLOYEE)
DNO=DNUMBER
( DLOCATION≠‘HOUSTON’(DEPARTMENT_LOCATIONS)))
RESULT
( FNAME, LNAME, ADDRESS((EMPLOYEE)
Database System-dww
(Rel.A-Rel.B))
(j)List the last names of department
managers who have no dependents
D_MNG_SSN(Rel.A)( SSN(DEPARTMENT)
E_WITH_DEPENDENT(Rel.A)( SSN(DPENDENT)
MNGR_NO_DEPENDENT(Rel.A – Rel.B)
RESULT( LNAME(EMPLOYEE * MNGR_NO_DEPENDENT))
Database System-dww