603 database systems
DESCRIPTION
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( ) - PowerPoint PPT PresentationTRANSCRIPT
603 Database Systems
Senior Lecturer: Laurie Webster II, M.S.S.E.,M.S.E.E., M.S.BME, Ph.D., P.E.
Lecture 14Lecture 14
A First Course in Database Systems
Relational CalculusTransforming 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)))
Relational Calculus
NOTICE:
(x) (P(x)) ( x) (P(x))
not ( x) (P(x)) not ( x) (P(x))
Relational CalculusThe 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.
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.
Relational CalculusFormally, 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- ( F1 and F2), ( F1 or F2), not (F1 )
and not ( F2 ) - depending on whether it is free
or bound in F1 or F2 (if it occurs in either).
Relational CalculusFormally, 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’.
Relational CalculusThe ( ) 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.
Relational CalculusEXAMPLE:
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 both F1 and F2 , whereas it is bound to the universal quantifier in F3. Variable t is bound to the ( ) quantifier in F2 .
Relational CalculusEXAMPLES 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 ( ).
Relational CalculusEXAMPLES 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
Relational CalculusEXAMPLES 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.
Relational CalculusEXAMPLES 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
Relational CalculusEXAMPLES 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))}
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.
Relational CalculusEXAMPLES 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
Relational CalculusEXAMPLES 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))))}
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.
Relational CalculusQ(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 as follows:
Q(all): {e.LNAME, e.FNAME EMPLOYEE(e) and F’}
F’= ((x )(not (PROJECT(x)) or F1))
F1= not x.DNUM=5 or F2
F2 = ( ( w) WORKS_ON(w) and w.ESSN=e.SSN and x.PNUMBER=w.PNO))
Relational CalculusEMPLOYEE Ai SSN . . . . . An
PROJECT Bi PNUMBER DNUM Bn
WORK_ON Ci ESSN PNO Cn
Relational CalculusQuery: 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!!!