1 / 14

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

uttara
Télécharger la présentation

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: Domain relational calculus: Tuple relational calculus: Chapter 6 Tutorial 6

  8. 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: Chapter 6 Tutorial 8

  9. Exercise 1 - e • Retrieve the names of all employees who work on every project. Relational Algebra: Chapter 6 Tutorial 9

  10. Exercise 1 - f • Retrieve the names of all employees who do not work on any project. Relational Algebra: Domain relational calculus: Tuple relational calculus: Chapter 6 Tutorial 10

  11. Exercise 1 - g • For each department, retrieve the department name and the average salary of all employees working in that department. Relational Algebra: Chapter 6 Tutorial 12

  12. Exercise 1 - h • Retrieve the average salary of all female employees. Relational Algebra: Chapter 6 Tutorial 13

  13. 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: Domain relational calculus: Tuple relational calculus: Chapter 6 Tutorial 14

  14. Exercise 1 - j • List the last names of all department managers who have no dependents. Relational Algebra: Domain relational calculus: Tuple relational calculus: Chapter 6 Tutorial 16

More Related