Riyadh Philanthropic Society For Science Prince Sultan College For Woman

1 / 6

# Riyadh Philanthropic Society For Science Prince Sultan College For Woman - PowerPoint PPT Presentation

Riyadh Philanthropic Society For Science Prince Sultan College For Woman Dept. of Computer &amp; 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)

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Riyadh Philanthropic Society For Science Prince Sultan College For Woman' - nixie

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Prince Sultan College For Woman

Dept. of Computer & Information Sciences

CS 340

Introduction to Database Systems

Chapter 6: The Relational Algebra and Relational Calculus

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

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

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

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

Chapter 6: The Relational Algebra and Relational Calculus

49

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