Specify queries (a), (b), (c), (e), (f), (i), and (j) of Exercise 6.16 in both tuple and domain relational calculus.

Question

What will be an ideal response?

Answer 1

(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND e.DNO=5 AND (EXISTS p) (EXISTS w)
(
WORKS_ON(w) AND PROJECT(p) AND e.SSN=w.ESSN AND
w.PNO=p.PNUMBER AND
p.PNAME='ProductX' AND w.HOURS>10 ) }
Domain relational Calculus:
{ qs | EMPLOYEE(qrstuvwxyz) AND z=5 AND (EXISTS a) (EXISTS b) (EXISTS e)
(EXISTS f)
(EXISTS g) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b AND
a='ProductX' AND
g>10 ) }

(b) List the names of employees who have a dependent with the same first name as
themselves.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPENDENT(d) AND
e.SSN=d.ESSN
AND e.FNAME=d.DEPENDENT_NAME ) }
Domain relational Calculus:
{ qs | (EXISTS t) (EXISTS a) (EXISTS b) ( EMPLOYEE(qrstuvwxyz) AND
DEPENDENT(abcde)
AND a=t AND b=q ) }

(c) Find the names of employees that are directly supervised by 'Franklin Wong'.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (EXISTS s) ( EMPLOYEE(s) AND
s.FNAME='Franklin' AND s.LNAME='Wong' AND e.SUPERSSN=s.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS y) (EXISTS a) (EXISTS c) (EXISTS d) ( EMPLOYEE(qrstuvwxyz) AND
EMPLOYEE(abcdefghij) AND a='Franklin' AND c='Wong' AND y=d ) }

(e) Retrieve the names of employees who work on every project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND (FORALL p) ( NOT(PROJECT(p)) OR
(EXISTS w) (
WORKS_ON(w) AND p.PNUMBER=w.PNO AND w.ESSN=e.SSN ) ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND (FORALL b) (
NOT(PROJECT(abcd)) OR
(EXISTS e) (EXISTS f) (WORKS_ON(efg) AND e=t AND f=b) ) }

(f) Retrieve the names of employees who do not work on any project.
Tuple relational Calculus:
{ e.LNAME, e.FNAME | EMPLOYEE(e) AND NOT(EXISTS w) ( WORKS_ON(w)
AND
w.ESSN=e.SSN ) }
Domain relational Calculus:
{ qs | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND NOT(EXISTS a) (
WORKS_ON(abc) AND a=t )
) }

(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.
Tuple relational Calculus:
{ e.LNAME, e.FNAME, e.ADDRESS | EMPLOYEE(e) AND (EXISTS p) (EXISTS w) (
WORKS_ON(w) AND PROJECT(p) AND e.SSN=w.ESSN AND
w.PNO=p.PNUMBER AND
p.PLOCATION='Houston' AND NOT(EXISTS l) ( DEPT_LOCATIONS(l) AND
e.DNO=l.DNUMBER
AND l.DLOCATION='Houston' ) ) }
Domain relational Calculus:
{ qsv | (EXISTS t) (EXISTS z) ( EMPLOYEE(qrstuvwxyz) AND (EXISTS b) (EXISTS c)
(EXISTS e)
(EXISTS f) ( WORKS_ON(efg) AND PROJECT(abcd) AND t=e AND f=b AND
c='Houston' AND
NOT(EXISTS h) NOT(EXISTS i) ( DEPT_LOCATIONS(hi) AND z=h AND i='Houston'
) ) }

(j) List the last names of department managers who have no dependents.
Tuple relational Calculus:
{ e.LNAME | EMPLOYEE(e) AND (EXISTS d) ( DEPARTMENT(d) AND
e.SSN=d.MGRSSN AND
NOT(EXISTS x) (DEPENDENT(x) AND e.SSN=x.ESSN) ) }
Domain relational Calculus:
{ s | (EXISTS t) ( EMPLOYEE(qrstuvwxyz) AND (EXISTS c) ( DEPARTMENT(abcd)
AND t=c
AND NOT(EXISTS e) (DEPENDENT(efghi) AND e=t) ) }