Download
1 / 25
250 likes | 456 Views
The Relational Calculus. The Relational Calculus. The Relational Calculus High-level, non-procedural language Relational calculus expression declares the requirements tuples in the resulting relation must satisfy. Provides foundation for standard query language (SQL) Expressive power
E N D
The Relational Calculus • The Relational Calculus • High-level, non-procedural language • Relational calculus expression declares the requirements tuples in the resulting relation must satisfy. • Provides foundation for standard query language (SQL) • Expressive power • Any expression in relational algebra can be expressed in relational calculus. • The tuple relational calculus • The domain relational calculus
The Tuple Relational Calculus • A tuple variable • Ranges over a database relation, denoted R(t) • Reference to an attribute of a tuple is denoted using the ‘.’ notation • Tuple relational expression • Form: {t | COND(t)}, where COND(t) is a conditional expression involving t
The Tuple Relational Calculus Example: {t.NAME | EMPLOYEE(t) AND t.SALARY>50000} • The range relation of t is EMPLOYEE • The select condition that must evaluate to true: SALARY>50000 • The requested attribute: NAME
Expressions and Formulas • A general expression {t1.Aj, t2.Ak,…, tn.Ap | COND(t1, t2,…, tn, tn+1,…, tn+m)} • t1, t2,…, tn, tn+1,…, tn+m: tuple variables • ti.Aj: attribute Aj of the relation on which ti ranges • COND is a condition or formula made of predicate calculus atoms
Expressions and Formulae • Atom • R(t): R is a relation name and t is a tuple variable • ti.A op tj.B: A and B are attributes of the relations on which ti and tj range, respectively; op {=, <, , >, , } • ti.A op c: c is a constant value in the domain of A • Formula • An atom is a formula. • If F is a formula, then so is NOT(F). • If F1 and F2 are formulas, then so are (F1AND F2), (F1OR F2), and (IF F1THEN F2).
Quantifiers • Existential quantifier • If F is a formula, then so is (t)(F), where t is a tuple variable. • (t)(F) is true if there exists some tuple that makes F true. • Universal quantifier • If F is a formula, then so is (t)(F), where t is a tuple variable. • (t)(F) is true if F is true for every substitution for t.
Other Notations R(t) tR NOT F F F1AND F2 F1 F2 F1OR F2 F1 F2 IF F1THEN F2 F1 F2
Equivalences and Implications (F1 F2) F1 F2 (F1 F2) F1 F2 F1 F2 F1 F2 (x)(P(x)) (x)(P(x)) (x)(P(x)) (x)(P(x)) (x)(P(x)) (x)(P(x)) (x)(P(x)) (x)(P(x))
Free and Bound Variables • Informally • A tuple variable is bound if it is quantified, otherwise it is free. Example: F1: d.DNAME=‘RESEARCH’ F2: (t)(d.NUMBER=t.DNO) F3: (d)(d.MGRSSN=‘333445555’)
Examples Q1: “Retrieve the name and address of all employees who work for the ‘Research’ department.” {t.FNAME, t.LNAME, t.ADDRESS | EMPLOYEE(t) AND (d)(DEPARTMENT(d) AND d.NAME=‘Research’ AND d.NUMBER=t.DNO)} t is the only free tuple variable, bound successively to each tuple, d.NAME=‘Research’ corresponds to a SELECT operation, and d.NUMBER=t.DNO serves as a join condition in relation algebra.
Examples Q2: “For every project in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name, and address.” {p.PNUMBER, p.DNUM, m.LNAME, m.ADDRESS | EMPLOYEE(m) AND PROJECT(p) AND p.LOCATION=‘Stafford’ AND ((d)(DEPARTMENT(d) AND p.DNUM=d.DNUMBER AND d.MGRSSN=m.SSN))}
Examples Q3: “Find the name of each employee who works on some project controlled by department number 5.” {e.LNAME, e.FNAME | EMPLOYEE(e) AND ((p)(w) (PROJECT(p) AND WORKS_ON(w) AND p.DNUM=5 AND w.ESSN=e.SSN AND p.PNUMBER=w.PNO))}
Examples Q4: “Make a list of project numbers for projects that involve an employee whose last name is “Smith”, either as a worker or as manager of the controlling department for the project.” {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’)))}
Examples Q5: “Find the names of employees who work on all projects controlled by department number 5.” {e.LNAME, e.FNAME | EMPLOYEE(e) AND ( (x) ((PROJECT(x) AND (x.DNUM=5)) ((w)(WORKS_ON(w) AND w.ESSN=e.SSN AND x.PNUMBER=w.PNO)) )}
Examples Q6: “Find the names of employees who have no dependents.” {e.LNAME, e.FNAME | EMPLOYEE(e) AND ( NOT(d) (DEPENDENT(d) AND e.SSN=d.ESSN))}
Examples Q7: “List the names of managers who have at least one dependent.” {e.LNAME, e.FNAME | EMPLOYEE(e) AND ( (d)(p) (DEPARTMENT(d) AND DEPENDENT(p) AND e.SSN=d.MGRSSN AND e.SSN=p.ESSN))}
Safe Expressions • Safe expression • Yields a result with a finite number of tuples • All values in its result are from the domain of the expression, which is the set of all values referenced by the expression or exist in any tuple in the relations referenced in the expression. Examples: • dom(EMPLOYEE(t) AND t.SALARY>25000): 25000 and all values appear in EMPLOYEE} • {t | NOT(EMPLOYEE(t))} is not safe.
The Domain Relational Calculus • Variables • Range over single values from domains of attributes • Domain relational expression • {x1, x2,…,xn | COND(x1, x2,…,xn, xn+1, xn+2,…,xn+m)} • x1, x2,…,xn, xn+1, xn+2,…,xn+m are domain variables • COND is a condition or formula of the domain relational calculus,
The Domain Relational Calculus • Atom • R(x1, x2,…,xj): R is a relation of degree j and xi is a domain variable. • xiop xj: xi and xj are domain variables and op {=, <, , >, , } • xiop c: c is a constant value in the domain of xi • Formula • Made of atoms, variables, and quantifiers
Examples Q1: “Retrieve the birth date and address of the employee whose name is ‘John B. Smith’.” {uv | (q)(r)(s)(t)(w)(x)(y)(z) (EMPLOYEE(qrstuvwxyz) AND q=‘John’ AND r=‘B’ AND s=‘Smith’}
Examples Q2: “Retrieve the name and address of the employees who work for the ‘Research’ department.” {qsv | (z)(l)(m)(EMPLOYEE(qrstuvwxyz) AND DEPARTMENT(lmno) AND l=‘Research’ AND m=z}
Examples Q3: “For every project located in ‘Stafford’, list the project number, the controlling department number, and the department manager’s last name and address.” {iksv | (j)(m)(n)(t)(PROJECT(hijk) AND EMPLOYEE(qrstuvwxyz) AND DEPARTMENT(lmno) AND k=m AND n=t AND j=‘STAFFFORD’}
Examples Q4: “Retrieve the names of employees who have no dependents.” {qs | (t)(EMPLOYEE(qrstuvwxyz) AND (NOT(l) (DEPENDENT(lmnop) AND t=l)))}
Examples Q4: “List the names of managers who have at least one dependents.” {sq | (t)(j)(l)(EMPLOYEE(qrstuvwxyz) AND DEPARTMENT(hijk) AND DEPENDENT(lmnop) AND t=j AND l=t}
