Practice Problem Set #6
CSC 261/461 (Database Systems), Spring 2017,
University of Rochester
03/05/2017
Problem 1
Specify the following queries on the COMPANY relational database schema shown in Figure 1, using the rela-
tional operators.
Figure 1: Company Schema
1. Retrieve the names of employees in department 5 who work more than 10 hours per week on the ’Pro-
ductX’ project.
2. List the names of employees who have a dependent with the same first name as themselves.
3. Find the names of employees that are directly supervised by ’Franklin Wong’.
4. Retrieve the names of employees who work on every project.
5. Retrieve the names of employees who do not work on any project.
6. 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.
7. List the last names of department managers who have no dependents.
Solution:
In the relational algebra, as in other languages, it is possible to specify the same query in multiple ways.
We give one possible solution for each query. We use the symbol σ for SELECT, π for PROJECT, ./ for
EQUIJOIN, * for NATURAL JOIN
PS # 6 Page 1 / 3
1. EMP_W_X ← (σPname=’ProductX’ (PROJECT))./PNumber=PnO (WORKS_ON)
EMP_WORK_10 ← (EMPLOYEE)./Ssn=Essn (σHours>10 (EMP_W_X))
RESULT ← πLname,Fname (σDno=5 (EMP_WORK_10))
2. E ← (EMPLOYEE)./(Ssn,Fname) = (Essn,Dependent_name) (DEPENDENT)
R ← πLname,Fname (E)
3. WONG_SSN ← πSsn (σFname=’Franklin’ AND Lname=’Wong’ (EMPLOYEE))
WONG_EMPS ← (EMPLOYEE)./SUPERSSN=SSN (WONG_SSN)
RESULT ← πLname,Fname (WONG_EMPS)
4. PROJ_EMPS(PNO,SSN) ←σPNO,ESSN (WORKS_ON)
ALL_PROJS(PNO) ← πPnumber (PROJECT)
EMPS_ALL_PROJS ← PROJ_EMPS ÷ALLPROJS
RESULT ← π Lname,Fname (EMPLOYEE * EMP_ALL_PROJS)
5. ALL_EMPS ← πSSN (EMPLOYEE)
WORKING_EMPS(SSN) ← πEssn (WORKS_ON)
NON_WORKING_EMPS ← ALL_EMPS - WORKING_EMPS
RESULT ← πLname,Fname (EMPLOYEE * NON_WORKING_EMPS)
6. E_P_HOU(SSN) ← πEssn (WORKS_ON ./PNO =PNumber (σPlocation=’Houston’ (PROJECT)))
D_NO_HOU ← πDnumber (DEPARTMENT) - πDnumber ( σDlocation=’Houston’ (DEPARTMENT))
E_D_NO_HOU ← πSsn (EMPLOYEE ./Dno=Dnumber (D_NO_HOU))
RESULT_EMPS ← E_P_HOU - E_D_NO_HOU
RESULT ← πLname,Fname,Address (EMPLOYEE * RESULT_EMPS)
7. DEPT_MANAGERS(SSN)← πMgr_ssn (DEPARTMENT)
EMPS_WITH_DEPENDENTS(SSN) ← πEssn (DEPENDENT)
RESULT_EMPS ← DEPT_MANAGERS - EMPS_WITH_DEPENDENTS
RESULT ← πLname (EMPLOYEE * RESULT_EMPS)
Problem 2
Specify the same queries from Problem 1 in tuple relational calculus.
Solution:
1. [Link], [Link] | EMPLOYEE(e) AND [Link]=5 AND (∃ p) (∃ w)
( WORKS_ON(w) AND PROJECT(p) AND [Link]=[Link] AND
[Link]=[Link] AND [Link]=’ProductX’ AND [Link]>10 )
2. [Link], [Link] | EMPLOYEE(e) AND (∃ d) ( DEPENDENT(d) AND
[Link]=[Link] AND [Link]=d.Dependent_name)
3. [Link], [Link] | EMPLOYEE(e) AND (∃ s) ( EMPLOYEE(s) AND
[Link]=’Franklin’ AND [Link]=’Wong’ AND e.Super_ssn=[Link] )
PS # 6 Page 2 Page 2 / 3
4. [Link], [Link] | EMPLOYEE(e) AND (∀ p) ( NOT(PROJECT(p)) OR
(∃ w) ( WORKS_ON(w) AND [Link]=[Link] AND [Link]=[Link] ) )
5. [Link], [Link] | EMPLOYEE(e) AND NOT(∃ w) ( WORKS_ON(w)
AND [Link]=[Link] )
6. [Link], [Link], [Link] | EMPLOYEE(e) AND (∃ p) (∃ w)
( WORKS_ON(w) AND PROJECT(p) AND [Link]=[Link] AND
[Link]=[Link] AND
[Link]=’Houston’ AND NOT(∃ l) ( DEPT_LOCATIONS(l) AND
[Link]=[Link] AND [Link]=’Houston’ ) )
7. [Link] | EMPLOYEE(e) AND (∃ d) ( DEPARTMENT(d) AND
[Link]=[Link] AND NOT(∃ x) (DEPENDENT(x) AND [Link]=[Link]) )
PS # 6 Page 3 Page 3 / 3