# selected_soln1 - Chapter 6 The Relational Model and...

This preview shows pages 1–3. Sign up to view the full content.

Chapter 6 The Relational Model and Relational Algebra - 65 - 6.19 Specify the following queries on the database schema shown in Figure 6.5 using the relational operators discussed in this chapter. Also show the result of each query if applied to the database of Figure 6.6. Answers: 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, and f for FUNCTION. (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the 'ProductX' project. 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)) Result : LNAME FNAME Smith John English Joyce (b) List the names of employees who have a dependent with the same first name as themselves. E <-- (EMPLOYEE) (SSN,FNAME),(ESSN,DEPENDENT_NAME) (DEPENDENT) R <-- LNAME,FNAME (E) (empty): - - - - - - - - - - (c) Find the names of employees that are directly supervised by 'Franklin Wong'. WONG_SSN <-- SSN ( FNAME='Franklin' AND LNAME='Wong' (EMPLOYEE)) WONG_EMPS <-- (EMPLOYEE) (SUPERSSN),(SSN) (WONG_SSN) RESULT <-- LNAME,FNAME (WONG_EMPS) : Smith John Narayan Ramesh English Joyce (d) For each project, list the project name and the total hours per week (by all employees) spent on that project.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Chapter 6 The Relational Model and Relational Algebra - 66 - PROJ_HOURS(PNO,TOT_HRS) <-- PNO f SUM HOURS (WORKS_ON) RESULT <-- Π PNAME,TOT_HRS ( (PROJ_HOURS) ϑ (PNO),(PNUMBER) (PROJECT) ) Result : PNAME TOT_HRS ProductX 52.5 ProductY 37.5 ProductZ 50.0 Computerization 55.0 Reorganization 25.0 Newbenefits 55.0 (e) Retrieve the names of employees who work on every project. PROJ_EMPS(PNO,SSN) <-- PNO,ESSN (WORKS_ON) ALL_PROJS(PNO) <-- PNUMBER (PROJECT) EMPS_ALL_PROJS <-- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *) RESULT <-- LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS) (empty): LNAME FNAME - - - - - - - - - - (f) Retrieve the names of employees who do not work on any project.
This is the end of the preview. Sign up to access the rest of the document.

## selected_soln1 - Chapter 6 The Relational Model and...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online