1 / 21

603 Database Systems

603 Database Systems. Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E. Lecture 14 A First Course in Database Systems. Relational Calculus. Transforming the Universal (  ), and Existential(  )

powa
Download Presentation

603 Database Systems

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. 603 Database Systems Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E. Lecture 14 A First Course in Database Systems

  2. Relational Calculus Transforming the Universal ( ), and Existential( ) Quantifiers ( Well-Known transformations from Mathematical Logic) : (x) (P(x))  not ( x) (not(P(x))) ( x) (P(x))  not (x) (not(P(x))) (x) (P(x) and Q(x))  not ( x) (not(P(x)) or not (Q(x))) (x) (P(x) or Q(x)) not ( x) (not(P(x)) and not (Q(x))) (x) (P(x) or Q(x)) not (x) (not(P(x)) and not (Q(x))) (x) (P(x) and Q(x)) not (x) (not(P(x)) or not (Q(x)))

  3. Relational Calculus NOTICE: (x) (P(x))  ( x) (P(x)) not ( x) (P(x))  not ( x) (P(x))

  4. Relational Calculus The Existential( ) and Universal( ) Quantifiers: Truth Values for formulas with these quantifiers are described below: 1. If F is a formula, then so is ( t )(F), where t is a tuple variable. ( t )(F) is TRUE if the formula F evaluates to TRUE for some (at least one) tuple assigned to free occurrences of t in F; otherwise is ( t )(F) FALSE. 2. If F is a formula, then so is ( t )(F), where t is a tuple variable. ( t )(F) is TRUE if the formula F evaluates to TRUE for every tuple (in the universe) tuple assigned to free occurrences of t in F; otherwise is ( t )(F) FALSE.

  5. Relational Calculus Concept of free and bound tuple variables: Informally, a tuple variable is bound if it is quantified, meaning that it appears in an (t ) or ( t ) clause; otherwise, it is free.

  6. Relational Calculus Formally, a tuple variable is free or bound according to the following rules: * An occurrence of a tuple variable in a formula F that is an atom is free in F * An occurrence of a tuple variable t is free or bound in a formula made up of logical connectives- ( F1and F2), ( F1or F2), not (F1 ) and not ( F2 ) - depending on whether it is free or bound in F1 or F2 (if it occurs in either).

  7. Relational Calculus Formally, a tuple variable is free or bound according to the following rules(continued): * All free occurrences of a tuple variable t in F are bound in a formula F’ of the form F’ = ( t )(F) or ( t )(F) . The tuple variable is bound to the quantifier specified in F’.

  8. Relational Calculus The ( ) quantifier is called an existential quantifier because a formula ( t )(F) is TRUE if “there exists” some tuple that makes F TRUE. For the universal quantifier, ( t )(F) is TRUE if every possible tuple that can be assigned to free occurrences of t in F is substituted for t, and F is TRUE for every such substitution. It is called the universal (or for all) quantifier because every tuple in “the universe of” tuples must make F TRUE to make the quantified formula TRUE.

  9. Relational Calculus EXAMPLE: F1:d.DNAME = “Research” F2:(t)(d.DNUMBER = t.DNO) d is bound F3:(d) (d.MGRSSN = ‘33445555’) The tuple variable d is free in bothF1 and F2 , whereas it is bound to the universal quantifier in F3. Variable t is bound to the ( ) quantifier in F2 .

  10. Relational Calculus EXAMPLES USING THE EXISTENTIAL ( ) QUANTIFIER: Query 1 Retrieve the name and address of all employees who work for the ‘Research’ department. Q1: {t.FNAME, t.LNAME, t.ADDRESS  EMPLOYEE(t) and ( d) (DEPARTMENT (d) and d.DNAME = “Research’ and d.DNUMBER = t.DNO)} The only free tuple variables in a relational calculus expression should be those that appear on the left of the bar (  ).

  11. Relational Calculus EXAMPLES USING THE EXISTENTIAL ( ) QUANTIFIER: Query 1 continued: Q1: {t.FNAME, t.LNAME, t.ADDRESS  EMPLOYEE(t) and ( d) (DEPARTMENT (d) and d.DNAME = “Research’ and d.DNUMBER = t.DNO)} In Q1, t is the only free variable ; it is then bound successively to each tuple. If a tuple satisfies the conditions specified in Q1, the attributes FNAME, LNAME, and ADDRESS are retrieved for each such tuple

  12. Relational Calculus EXAMPLES USING THE EXISTENTIAL ( ) QUANTIFIER: Query 1 continued: Q1: {t.FNAME, t.LNAME, t.ADDRESS EMPLOYEE(t) and ( d) (DEPARTMENT (d)and d.DNAME = “Research’ and d.DNUMBER = t.DNO)} The conditions EMPLOYEE(t) and DEPARTMENT(d) specify the range relations for t and d.

  13. Relational Calculus EXAMPLES USING THE EXISTENTIAL ( ) QUANTIFIER: Query 1 continued: Q1: {t.FNAME, t.LNAME, t.ADDRESS EMPLOYEE(t) and ( d) (DEPARTMENT (d)and d.DNAME = “Research’ and d.DNUMBER = t.DNO)} The condition d.DNAME=‘Research’ is a selection condition and corresponds to a SELECT operation in the relational algebra,where as the condition d.DNUMBER = t.DNO is a join condition and serves a similar purpose to the JOIN operation

  14. Relational Calculus EXAMPLES USING THE EXISTENTIAL () QUANTIFIER: Query 2 For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, birthdate, and address. Q2: {p.PNUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS  PROJECT (p) and EMPLOYEE(m) and p.PLOCATION =‘Stafford’ and (( d) (DEPARTMENT(d) and p.DNUM = d.DNUMBER and d.MGRSSN = m.SSN))}

  15. Relational Calculus EXAMPLES USING THE EXISTENTIAL () QUANTIFIER: Q2: {p.PNUMBER, p.DNUM, m.LNAME, m.BDATE, m.ADDRESS  PROJECT (p) and EMPLOYEE(m) and p.PLOCATION =‘Stafford’ and ((d) (DEPARTMENT(d) and p.DNUM = d.DNUMBER and d.MGRSSN = m.SSN))} In Q2 there are two free tuple variables, p and m. Tuple variable d is bound to the existential quantifier.

  16. Relational Calculus EXAMPLES USING THE EXISTENTIAL () QUANTIFIER: Query 3: Make a list of project numbers for projects that involve and employee whose last name is ‘Smith’, either as a worker or a manager of the controlling department for the project. Q3: {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=p.DNUMBER and d.MGRSSN=m.SSN and m.LNAME=‘Smith’)))} UNION in Relational Algebra = or in TRC

  17. Relational Calculus EXAMPLES USING THE EXISTENTIAL () QUANTIFIER: Query: Find the name of each employee who works on some project controlled by department number 5. Q(some): {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))} Q(all): {e.LNAME, e.FNAME  EMPLOYEE(e) and ((x)(not (PROJECT(x)) or not x.DNUM=5 or ( (w) WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))))}

  18. Relational Calculus Using the Universal( ) Quantifier: NOTE: Whenever we use a universal ( ) quantifier, it is quite judicious to follow a few rules to ensure that our expression make sense. Q(all): {e.LNAME, e.FNAME  EMPLOYEE(e) and ((x)(not (PROJECT(x)) or not x.DNUM=5 or ( (w) WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))))} We can break up this query into its basic components.

  19. Relational Calculus Q(all): {e.LNAME, e.FNAME  EMPLOYEE(e) and ((x )(not (PROJECT(x)) ornot x.DNUM=5 or( ( w) WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))))} We can break up this query into its basic components as follows: Q(all): {e.LNAME, e.FNAME  EMPLOYEE(e) andF’} F’= ((x )(not (PROJECT(x)) orF1)) F1= not x.DNUM=5 orF2 F2= ( ( w) WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))

  20. Relational Calculus EMPLOYEE AiSSN . . . . . An PROJECT BiPNUMBER DNUM Bn WORK_ON CiESSN PNO Cn

  21. Relational Calculus Query: Find the name of each employee who works on some project controlled by department number 5. Q(some): {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))} {t   u  p(t[A] = u[A]  t[B] = u[B])}  Project Project => select certain columns (attributes) {t  xw p(t[A] = x[A]  t[B] = x[B] )  (t[A] = w[A]))} Employee e works on every project x in dept num 5!!!

More Related