Problems 1 Specify the following queries on the database sch

Problems: 1. Specify the following queries on the database schema shown in Figure 3.6 using both the tuple relational calculus and the domain relational calculus. You don’t need to show the result of each query if applied to the database of Figure 3.6.

(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\'.

(e) Retrieve the names of employees who work on every project. 2 FIGURE 3.6 One possible database state for the COMPANY relational database schema.

Solution

(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 ) }

Result:
LNAME FNAME
Smith John
English Joyce

(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 )}

Result (empty):
LNAME FNAME

(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 ) }

Result:
LNAME FNAME
Smith John
Narayan Ramesh
English Joyce

(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) (NO
T(PROJECT(abcd)) OR
(EXISTS e) (EXISTS f) (WORKS_ON(efg) AND e=t AND f=b) ) }

Result (empty):
LNAME FNAME

Explanation:
In tuple relation calculus, we select the tuples in a relation which can vary from range of values or certain attribute values.
In the first example, as we need first and last name of employee, we have wriiten e.LNAME,e.FNAME and as these belong to Employee table we have queried on that and then followed the atributes value mathcing
In domain relational calculus,it\'s completely different. We use few attributes to be selected from relation based on few conditions. So,here instead of selecting the whle tuple,we focus on selecting the attributes.
So, take first problem, as we need first and last name of employee table, we denoted each column of employee with variables (q,r,....z) and then fetched qs as they point to first and last name and then did needed comparisions