1 / 6

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: The Relational Algebra and Relational Calculus. T1(SSN, NO_OF_DEPTS) ESSN COUNT DEPENDENT_NAME (DEPENDENT)

nixie
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: The Relational Algebra and Relational Calculus

  2. T1(SSN, NO_OF_DEPTS) ESSN COUNT DEPENDENT_NAME (DEPENDENT) T2  NO_OF_DEPS>1 (T1) RESULT pLNAME, FNAME (T2 * EMPLOYEE) • Examples of Queries in Relational Algebra • Query 5: list the names of all employees with two or more • dependents. Chapter 6: The Relational Algebra and Relational Calculus 42

  3. ALL_EMPS pSSN (EMPLOYEE) EMPS_WITH_DEPS(SSN) pESSN (DEPENDENT) EMPS_WITHOUT_DEPS (ALL_EMP - EMP_WITH_DEPS) RESULT pLNAME, FNAME (EMPS_WITHOUT_DEPS * EMPLOYEE) • Examples of Queries in Relational Algebra • Query 6: Retrieve the names of employees who have no dependents. Chapter 6: The Relational Algebra and Relational Calculus 43

  4. MGRS(SSN) pMGRSSN (DEPARTMENT) EMPS_WITH_DEPS(SSN) pESSN (DEPENDENT) MGRS_WITH_DEPS (MGRS EMPS_WITH_DEPS) RESULT pLNAME, FNAME (MGRS_WITH_DEPS * EMPLOYEE)  • Examples of Queries in Relational Algebra • Query 7: list the names of managers who have at least one • dependent.. Chapter 6: The Relational Algebra and Relational Calculus 44

  5. Example Queries Using Existential Quantifier • Query 3: find the name of each employee who works on some • project controlled by the department number 5. • Query 4: list the names of managers who have at least one • dependent. Q3: {e.LNAME, e.FNAME | EMPLOYEE(e) AND((x)(w) (PROJECT(x) AND WORKS_ON(w) AND x.DNUM=5 AND w.ESSN=e.SSN AND x.PNUMBER=w.PNO))} Q4: {e.LNAME, e.FNAME | EMPLOYEE(e) AND ((d) (p) (DEPARTMENT(d) AND DEPENDENT(p) AND e.SSN=d.MGRSSN AND p.ESSN=e.SSN))} Chapter 6: The Relational Algebra and Relational Calculus 49

  6. Example Queries Using Existential Quantifier • Query 5: find the names of employees who have no dependents. • Query 6: Make a list of project numbers for projects that involve an • employee whose last name is ‘Smith’, either as a worker or as a • manager of the controlling department for the project. Q5: {e.LNAME, e.FNAME | EMPLOYEE(e) AND (NOT (d) (DEPENDENT(d) AND e.SSN=d.ESSN))} Q6: {p.PNUMBER | PROJECT(p) AND (((e)(w) (EMPLOYEE(e) AND WORKS_ON(w) AND w.PNO=p.PNUMBER AND e.LNAME=‘Smith’ AND e.SSN=w.ESSN)) or ((m)(d)(EMPLOYEE(m) AND DEPARTMENT(d) AND p.DNUM=d.DNUMBER AND d.MGRSSN=m.SSN AND m.LNAME=‘Smith’)))} Chapter 6: The Relational Algebra and Relational Calculus 50

More Related