Database Management Systems Question 1. (16 points) Exercise 8.16 (a, c, e and i

Question

Database Management Systems

Question 1. (16 points) Exercise 8.16 (a, c, e and i) on pages 281 - 282. Specify the follow-ing queries on the COMPANY relational database schema shown in Figure 5.5 using the relational algebra operators. Aslo show the result of each query as it would apply to the database state of Figure 5.6.

a. Retrieve the names (rst and last names) of all employees in department 5 who work more than 10 hours per week (i.e., WORKS ON.HOURS > 10) on the ‘ProductX’ project. c. Find the names of all employees who are directly supervised by ‘Franklin Wong’.

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

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

Question 2. (12 points) Specify all above queries in Question 1 in tuple relational calculus.

Question 3. (6 points) Specify queries (a) and (c) in Question 1 in domain relational calcu-lus.

Explanation / Answer

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 s for

SELECT, P for PROJECT, J for EQUIJO

IN, * for NATURAL JOIN, and f for FUNCTION.

(a) EMP_W_X <-- ( s PNAME='ProductX' (PROJECT)) J (PNUMBER),(PNO) (WORKS_ON)EMP_WORK_10 <

-- (EMPLOYEE) J (SSN),(ESSN) ( s HOURS>10 (EMP_W_X))RESULT <-- P

LNAME,FNAME ( s DNO=5 (EMP_WORK_10))

Result:

LNAME FNAME

Smith John

English Joyce

(c) WONG_SSN <-- P SSN ( s FNAME='Franklin' ANDLNAME='Wong' (EMPLOYEE))

WONG_EMPS <-- (EMPLOYEE) J (SUPERSSN),(SSN) (WONG_SSN)

RESULT <-- P LNAME,FNAME (WONG_EMPS)

Result:

LNAME FNAME

Smith John

Narayan Ramesh

English Joyce

(e) PROJ_EMPS(PNO,SSN) <-- P PNO,ESSN (WORKS_ON)ALL_PROJS(PNO) <-- P PNUMBER(PROJECT)

EMPS_ALL_PROJS <-- PROJ_EMPS-:- ALLPROJS (* DIVISION operation *)RESULT <--P LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)

Result(empty):

LNAME FNAME

(i) E_P_HOU(SSN) <-- P ESSN (WORKS_ON J(PNO),(PNUMBER) ( s PLOCATION='Houston'(PROJECT)))D_NO_HOU <-- P DNUMBER (DEPARTMENT) -P DNUMBER ( sDLOCATION='Houston'(DEPARTMENT))E_D_NO_HOU <-- P SSN (EMPLOYEE J(PNO),(DNUMBER) (D_NO_HOU))

RESULT_EMPS <-- E_P_HOU- E_D_NO_HOU (* this is set DIFFERENCE *)RESULT <-- PLNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)

Result:

LNAME FNAME

ADDRESS

Wallace Jennifer 291 Berry, Bellaire, TX

Question 2

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

Answer:

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

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

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.