Relational Calculus � More “declarative” than relational algebra – Foundation for query languages (such as SQL) Relational Calculus – Relational algebra used more for physical operators � Comes in two flavours: Tuple relational calculus (TRC) and Domain relational calculus (DRC) – TRC : Variables range over (i.e., get bound to) tuples – DRC : Variables range over domain elements (= field values) – Both TRC and DRC are simple subsets of first-order logic � We will study DRC Database Management Systems, R. Ramakrishnan 1 Database Management Systems, R. Ramakrishnan 2 Domain Relational Calculus DRC Formulas � Query has the form: � Atomic formula: x x 1 2 , ,..., xn ∈ Rname � � � � , or X op Y, or X op constant – x x 1 2 , ,..., xn p x x | 1 2 , ,..., xn � � � � � � � � � � � � – op is one of < > = ≤ ≥ ≠ , , , , , � � � � � Formula: x x 1 2 , ,..., xn � Answer includes all tuples that – an atomic formula, or … (more later) � � make the formula p x x 1 2 , ,..., xn be true . � � � � � � � Example of query with atomic formula � � � Formula is recursively defined { < I , N , T , A > | < I , N , T , A >∈ Sailors } – Starting with simple atomic formulas – Equivalent relational algebra query? – Logical connectives – Quantification Database Management Systems, R. Ramakrishnan 3 Database Management Systems, R. Ramakrishnan 4 DRC Formulas DRC Formulas � Formula: � Formula: – an atomic formula, or – an atomic formula, or ¬ p p , ∧ q p q , ∨ ¬ p p , ∧ q p q , ∨ , where p and q are formulas, or – , where p and q are formulas, or – – (more later) ∃ X p X ( ( )) , where variable X is free in p(X), or – � Query using logical connectives ∀ X p X ( ( )) , where variable X is free in p(X) – � The use of quantifiers ∃ X and is said to bind X. ∀ X { < I , N , T , A > | < I , N , T , A >∈ Sailors ∧ T > 7 } – A variable that is not bound is free – Equivalent relational algebra query? � � � � � For query: x 1 , x 2 ,..., xn | p x 1 , x 2 ,..., xn � � � � � � � � – Find sailors who are older than 18 or have a rating under 9, and � � � � � � � � are called ‘Joe’ – The variables x1, ..., xn that appear to the left of `|’ must be the only free variables in the formula p(...). Database Management Systems, R. Ramakrishnan 5 Database Management Systems, R. Ramakrishnan 6
Find sailors rated > 7 who’ve reserved boat #103 Find sailors rated > 7 who’ve reserved a red boat � � I N T A , , , | I N T A , , , ∈ Sailors ∧ T > 7 ∧ � � I N T A , , , | I N T A , , , ∈ Sailors ∧ T > 7 ∧ � � � � � � � � � � ∃ Ir Br D Ir Br D , , , , ∈ Re serves ∧ Ir = ∧ I ∃ Ir Br D , , Ir Br D , , ∈ Re serves ∧ Ir = ∧ I Br = 103 � � � � � � � � � � � � � � � � � ∃ B BN C B BN C , , , , ∈ Boats ∧ B = Br ∧ C = ' red ' � � � � � � � � � We have used as a shorthand ∃ Ir Br D , , ( ... ) � � � � � � � � ( ) for ( ) ( ) ∃ Ir ∃ Br ∃ D . .. � Observe how the parentheses control the scope of each quantifier’s binding. � Note the use of to find a tuple in Reserves that ∃ � This may look cumbersome, but with a good user `joins with’ the Sailors tuple under consideration. interface, it is very intuitive. Database Management Systems, R. Ramakrishnan 7 Database Management Systems, R. Ramakrishnan 8 Find sailors who’ve reserved all boats Find sailors who’ve reserved all boats � � I N T A , , , | I N T A , , , ∈ Sailors ∧ I N T A , , , | I N T A , , , ∈ Sailors ∧ � � � � � � � � � � � ∀ B , BN , C � B , BN , C ∈ Boats ∀ B , BN , C � B , BN , C ∈ Boats ∧ � � � � � � � � � � � � � � � � � � ∃ Ir Br D , , Ir Br D , , ∈ Re serves ∧ = I Ir ∧ Br = B � ∃ Ir Br D , , Ir Br D , , ∈ Re serves ∧ = I Ir ∧ Br = B � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � B BN C , , � Find all sailors I such that for each 3-tuple � What is wrong with this? there is a tuple in Reserves showing that sailor I has reserved it. Database Management Systems, R. Ramakrishnan 9 Database Management Systems, R. Ramakrishnan 10 Find sailors who’ve reserved all boats Find sailors who’ve reserved all boats � � I N T A , , , | I N T A , , , ∈ Sailors ∧ I N T A , , , | I N T A , , , ∈ Sailors ∧ � � � � � � � � � � � ∀ B BN C , , � ¬ B BN C , , ∈ Boats ∨ � � � ¬∃ B , BN , C � B , BN , C ∈ Boats ∧ � � � � � � � � � � � � � � � � � � ∃ Ir Br D , , Ir Br D , , ∈ Re serves ∧ = I Ir ∧ Br = B � � � � � � � � � � � � � ¬ ∃ Ir , Br , D � Ir , Br , D ∈ Re serves ∧ I = Ir ∧ Br = B � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � � B BN C , , � Find all sailors I such that for each 3-tuple � Find all sailors I such that there does not exist a 3- B BN C , , either it is not a tuple in Boats or there is a tuple in tuple in Boats for which there does not exist Reserves showing that sailor I has reserved it. a tuple in Reserves showing that sailor I has reserved it. Database Management Systems, R. Ramakrishnan 11 Database Management Systems, R. Ramakrishnan 12
Find sailors who’ve reserved all red boats Unsafe Queries, Expressive Power � Syntactically correct calculus queries that have an � I N T A , , , | I N T A , , , ∈ Sailors ∧ � � infinite number of answers � � – Such queries are called unsafe . � � ∀ B , BN , C � B , BN , C ∈ Boats ∧ C = ' Re d ' � � � � � – e.g., I , N , T , A | ¬ I , N , T , A ∈ Sailors � � � � � � � � � � � � � � � � � � � � ∃ Ir Br D , , Ir Br D , , ∈ Re serves ∧ = I Ir ∧ Br = B � � � � � � � � � � � � � � � � � � � � � � � � � � Every relational algebra query can be expressed as a safe query in DRC/TRC B BN C , , � Find all sailors I such that for each 3-tuple – And vice versa! there is a tuple in Reserves showing that sailor I has � Relational Completeness : Query language (e.g., reserved it. SQL) can express every query that is expressible in relational algebra/calculus. Database Management Systems, R. Ramakrishnan 13 Database Management Systems, R. Ramakrishnan 14 Summary � Relational calculus is “declarative” or non- operational – Users define queries in terms of what they want, not in terms of how to compute it � Algebra and safe calculus have same expressive power – Relational “completeness” Database Management Systems, R. Ramakrishnan 15
Recommend
More recommend
