1 / 18

Riyadh Philanthropic Society For Science Prince Sultan College For Woman

Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 6 Tutorial). Exercise 1 Given the Following database schema: Specify the following queries in: Relation algebra

ping
Download Presentation

Riyadh Philanthropic Society For Science Prince Sultan College For Woman

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer & Information Sciences CS 340 Introduction to Database Systems (Chapter 6 Tutorial)

  2. Exercise 1 • Given the Following database schema: • Specify the following queries in: • Relation algebra • Tuple relational calculus • Domain relational calculus Only for a, b, c, f, i, j Chapter 6 Tutorial 1

  3. Exercise 1 - a • Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘Product x’ project. Relational Algebra: PROJ_X  PNAME = ‘Product x’ (PROJECT) EMP_WORK_10  HOURS>10 (PROJ_X PNUMBER=PNO WORKS_ON) EMP_DEPT_5  DNO=5 (EMPLOYEE SSN=ESSN EMP_WORK_10) RESULT pFNAME, LNAME (EMP_DEPT_5) Chapter 6 Tutorial 2

  4. Exercise 1 - a • Tuple relational calculus: • {e.FNAME, e.LNAME | EMPLOYEE(e) AND e.DNO=5 AND • (p) (w) (PROJECT(p) AND WORKS_ON(w) AND p.PNAME=‘Product x’ AND w.HOURS>10 AND e.SSN=w.ESSN AND p.PNUMBER= w.PNO)} • Domain relational calculus: • {qs | (z) (a) (b) (e) (f) (g) (EMPLOYEE(qrstuvwxyz) AND PROJECT(abcd) AND WORKS_ON(efg) AND z=5 AND • a=‘Product x’ AND g>10 AND t=e AND b=f)} Chapter 6 Tutorial 3

  5. Exercise 1 - b • List the names of all employees who have a dependent with the same first name as themselves. Relational Algebra: EMP (EMPLOYEE SSN=ESSN AND FNAME=DEPENDENT_NAME DEPENDENT) RESULT pFNAME, LNAME (EMP) Chapter 6 Tutorial 4

  6. Exercise 1 - b • Tuple relational calculus: • {e.FNAME, e.LNAME | EMPLOYEE(e) AND (d) (DEPENDENT(d) AND e.SSN=d.ESSN AND • e.FNAME=d.DEPENDENT_NAME)} • Domain relational calculus: • {qs | (t) (a) (b) (EMPLOYEE(qrstuvwxyz) AND DEPENDENT(abcde) AND t=a AND q=b)} Chapter 6 Tutorial 5

  7. Exercise 1 - c • Find the names of all employees who are directly supervised by ‘Franklin Wong’. Relational Algebra: FW  FNAME=‘Franklin’ AND LNAME=‘Wong’ (EMPLOYEE) FW_SSN pSSN (FW) FW_EMP (EMPLOYEE SUPERSSN=SSN FW_SSN) RESULT pFNAME, LNAME (FW_EMP) Chapter 6 Tutorial 6

  8. Exercise 1 - c • Tuple relational calculus: • {e.FNAME, e.LNAME | EMPLOYEE(e) AND (s) (EMPLOYEE(s) AND s.FNAME=‘Franklin’ AND s.LNAME=‘Wong’ AND e.SUPERSSN=s.SSN)} • Domain relational calculus: • {qs | (y) (a) (c) (d) (EMPLOYEE(qrstuvwxyz) AND EMPLOYEE(abcdefghi) AND a=‘Franklin’ AND c=‘Wong’ AND y=d)} Chapter 6 Tutorial 7

  9. Exercise 1 - d • For each project, list the project name and the total hours per week (by all employees) spent on that project. Relational Algebra: PROJ_HOURS1(PNO, T_HOURS) PNO SUMHOURS (WORKS_ON) PROJ_HOURS2 (PROJ_HOURS1 PNO=PNUMBER PROJECT) RESULT pPNAME, T_HOURS (PROJ_HOURS2) Chapter 6 Tutorial 8

  10. . . • Exercise 1 - e • Retrieve the names of all employees who work on every project. Relational Algebra: PROJ_EMP pPNO, ESSN (WORKS_ON) ALL_PROJ(PNO) pPNUMBER (PROJECT) EMP_ALL_PROJ1 PROJ_EMP ALL_PROJ EMP_ALL_PROJ2 (EMPLOYEE SSN=ESSN EMP_ALL_PROJ1) RESULT pFNAME, LNAME (EMP_ALL_PROJ2) Chapter 6 Tutorial 9

  11. Exercise 1 - f • Retrieve the names of all employees who do not work on any project. Relational Algebra: ALL_EMP pSSN (EMPLOYEE) W_EMP(SSN) pESSN (WORKS_ON) N_W_EMP ALL_EMP - W_EMP RESULT pFNAME, LNAME (EMPLOYEE * N_W_EMP) Chapter 6 Tutorial 10

  12. Exercise 1 - f • Tuple relational calculus: • {e.FNAME, e.LNAME | EMPLOYEE(e) AND NOT(w) (WORKS_ON(w) AND e.SSN=w.ESSN)} • Domain relational calculus: • {qs | (t) (EMPLOYEE(qrstuvwxyz) AND NOT(a) (WORKS_ON(abc) AND t=a))} Chapter 6 Tutorial 11

  13. Exercise 1 - g • For each department, retrieve the department name and the average salary of all employees working in that department. Relational Algebra: D_AVG_S(DNUMBER, AVG) DNO AVERAGESALARY (EMPLOYEE) RESULT pDNAME, AVG (D_AVG_S * DEPARTMENT) Chapter 6 Tutorial 12

  14. Exercise 1 - h • Retrieve the average salary of all female employees. Relational Algebra: F_EMP  SEX=‘F’ (EMPLOYEE) RESULT(AVG_F_S) AVERAGESALARY (F_EMP) Chapter 6 Tutorial 13

  15. Exercise 1 - i • Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston. Relational Algebra: H_PROJ  PLOCATION=‘Houston’ (PROJECT) E_H_PROJ(SSN) pESSN (WORKS_ON PNO=PNUMBER H_PROJ) H_DEPT pDNUMBER ( DLOCATION=‘Houston’ (DEPT_LOCATION)) ALL_DEPT pDNUMBER (DEPARTMENT) N_H_DEPT ALL_DEPT - H_DEPT E_N_H_DEPT pSSN (EMPLOYEE DNO=DNUMBER N_H_DEPT) EMP E_H_PROJ E_N_H_DEPT RESULT pFNAME, LNAME, ADDRESS (EMPLOYEE * EMP)  Chapter 6 Tutorial 14

  16. Exercise 1 - i • Tuple relational calculus: • {e.FNAME, e.LNAME, e.ADDRESS | EMPLOYEE(e) AND (p) • (w) (PROJECT(p) AND WORKS_ON(W) AND • p.PLOCATION=‘Houston’ AND e.SSN=w.ESSN AND • w.PNO=p.PNUMBER AND NOT(l) (DEPT_LOCATION(l) AND • l.DLOCATION=‘Houston’ AND e.DNO=l.DNUMBER))} • Domain relational calculus: {qsv | (t) (z) (EMPLOYEE(qrstuvwxyz) AND (b) (c) (e) (f) (PROJECT(abcd) AND WORKS_ON(efg) AND c=‘Houston’ AND t=e AND b=f AND NOT(h) NOT(i) (DEPT_LOCATIONS(hi) AND i=’Houston’ AND z=h))} Chapter 6 Tutorial 15

  17. Exercise 1 - j • List the last names of all department managers who have no dependents. Relational Algebra: D_MGR(SSN) pMGRSSN (DEPARTMENT) E_W_DEPENDENT(SSN) pESSN (DEPENDENT) EMP D_MGR - E_W_DEPENDENT RESULT pLNAME (EMPLOYEE * EMP) Chapter 6 Tutorial 16

  18. Exercise 1 - j • Tuple relational calculus: • {e.LNAME | EMPLOYEE(e) AND (d) (DEPARTMENT(d) AND e.SSN=d.MGRSSN AND NOT(x) (DEPENDENT(x) AND e.SSN=x.ESSN))} • Domain relational calculus: • {s | (t) (EMPLOYEE(qrstuvwxyz) AND (c) (DEPARTMENT(abcd) AND t=c AND NOT(e) (DEPENDENT(efghi) AND t=e))} Chapter 6 Tutorial 17

More Related