CS 425 – Database Organization - 3 - Chapter 3 ：Formal Relational Query Language (2)

31、Tuple Relational Calculus

① A nonprocedural query language, where each query is of the form

{t | P (t ) }

② It is the set of all tuples t such that predicate P is true for t
③ is a tuple variable , t [A ] denotes the value of tuple t on attribute A
④ t ∈ r denotes that tuple t is in relation r
⑤ P is a formula similar to that of the predicate calculus

32、Predicate Calculus Formula

1. Set of attributes and constants
2. Set of comparison operators: (e.g., <, ≤, =,>, ≥)
3. Set of logical connectives: and (∧), or (v)‚ not (¬)
4. Implication (⇒): x ⇒ y, if x if true, then y is true
   x ⇒ y ≡ ¬x v y
5. Set of quantifiers:
   ∃t ∈r (Q (t )) ≡ ”there exists” a tuple in t in relation r
   such that predicate Q (t ) is true
   ∀t ∈r (Q (t )) ≡ Q is true “for all” tuples t in relation r

33、Example Queries

① Find the ID, name, dept_name, salary for instructors whose salary is greater than $80,000

{t | t ∈ instructor ∧ t [salary ] > 80000}

n As in the previous query, but output only the ID attribute value

{t | ∃ s ∈ instructor (t [ID ] = s [ID ] ∧ s [salary ] > 80000)}

Notice that a relation on schema (ID ) is implicitly defined by the query, because

1. t is not bound to any relation by the predicate
2. we implicitly state that t has an ID attribute (t[ID] =s[ID] )

② Find the names of all instructors whose department is in the Watson building

{t | ∃s ∈ instructor (t [name ] = s [name ]
∧∃u ∈ department (u [dept_name ] = s [dept_name ] “
∧ u [building ] = “Watson” ))}

<1> n Find the set of all courses taught in the Fall 2009 semester, or in the Spring 2010 semester, or both

{t | ∃s ∈ section (t [course_id ] = s [course_id ] ∧
s [semester ] = “Fall” ∧ s [year] = 2009 )
v ∃u ∈ section (t [course_id ] = u [course_id ] ∧
u [semester ] = “Spring” ∧ u [year] = 2010)}

<2>n Find the set of all courses taught in the Fall 2009 semester, and in the Spring 2010 semester

{t | ∃s ∈ section (t [course_id ] = s [course_id ] ∧
s [semester ] = “Fall” ∧ s [year] = 2009 )
∧ ∃u ∈ section (t [course_id ] = u [course_id ] ∧
u [semester ] = “Spring” ∧ u [year] = 2010)}

<3> n Find the set of all courses taught in the Fall 2009 semester, but not in the Spring 2010 semester

{t |  ∃s ∈ section (t [course_id ] = s [course_id ] ∧
s [semester ] = “Fall”  s [year] = 2009 )
∧ ¬ ∃u ∈ section (t [course_id ] = u [course_id ] ∧
u [semester ] = “Spring” ∧ u [year] = 2010)}

34、Safety of Expressions ⑤
① It is possible to write tuple calculus expressions that generate infinite relations.
② For example, { t | ¬ t ∈ r } results in an infinite relation if the domain of any attribute of relation r
is infinite
③ To guard against the problem, we restrict the set of allowable expressions to safe expressions.
④ An expression {t | P (t )} in the tuple relational calculus is safe if every component of t appears in
one of the relations, tuples, or constants that appear in P

• NOTE: this is more than just a syntax condition.
  • E.g. { t | t [A ] = 5 ∨ true } is not safe — it defines an infinite set with attribute values that do not appear in any relation or tuples or constants in P .

35、Universal Quantification

① Find all students who have taken all courses offered in the Biology department

{t | ∃ r ∈ student (t [ID ] = r [ID ]) ∧
  (∀ u ∈ course (u [dept_name ]=“Biology” ⇒
          ∃ s ∈ takes (t [ID ] = s [ID ] ∧
          s [course_id ] = u [course_id ]))}

• Note that without the existential quantification on student, the above query would be unsafe if the Biology department has not offered any courses.

36、Domain Relational Calculus
① A nonprocedural query language equivalent in power to the tuple relational calculus
② Each query is an expression of the form:

{ < x 1, x 2, …, xn > | P (x 1, x 2, …,xn )}

• x 1, x 2, …, xn represent domain variables
  • Variables that range of attribute values
• P represents a formula similar to that of the predicate calculus
• Tuples can be formed using <>
  • E.g., <‘Einstein’,’Physics’>

37、Example Queries
① Find the ID, name, dept_name, salary for instructors whose salary is greater than $80,000

{< i, n, d, s> | < i, n, d, s> ∈ instructor ∧ s > 80000}

② As in the previous query, but output only the ID attribute value

{< i> | < i, n, d, s> ∈ instructor ∧ s > 80000}

③ Find the names of all instructors whose department is in the Watson building

{< n > | ∃ i, d, s (< i, n, d, s > ∈ instructor
∧ ∃ b, a (< d, b, a> ∈ department ∧ b
= “Watson” ))}

④ Find the set of all courses taught in the Fall semester, or in the Spring semester, or both

{<c> | ∃ a, s, y, b, r, t ( <c, a, s, y, b, t > ∈ section ∧
s = “Fall”)
     v ∃ a, s, y, b, r, t ( <c, a, s, y, b, t > ∈ section ] ∧
s = “Spring”)}

This case can also be written as

{<c> | ∃ a, s, y, b, r, t ( <c, a, s, y, b, t > ∈ section ∧
           ( (s = “Fall”) v (s = “Spring))}

⑤ Find the set of all courses taught in the Fall semester, and in the Spring semester

{<c> | ∃ a, s, y, b, r, t ( <c, a, s, y, b, t > ∈ section ∧
             s = “Fall”)
       ∧ ∃ a, s, y, b, r, t ( <c, a, s, y, b, t > ∈ section ] ∧
            s = “Spring”)}

38、Safety of Expressions
The expression:
{ < x 1, x 2, …, xn > | P (x 1, x 2, …, xn )}
is safe if all of the following hold:

1. All values that appear in tuples of the expression are values from dom (P ) (that is, the values appear either as constants in P or in a tuple of a relation mentioned in P ).
2. For every “there exists” subformula of the form ∃ x (P 1(x )), the subformula is true if and only if there is a value of x in dom (P 1)such that P 1(x ) is true.
3. For every “for all” subformula of the form ∀x (P 1 (x )), the subformula is true if and only if P 1(x ) is true for all values x from dom (P 1).

39、Universal Qualification
① Find all students who have taken all courses offered in the Biology department

{< i > | ∃ n, d, tc ( < i, n, d, tc > ∈ student ∧
     (∀ ci, ti, dn, cr ( < ci, ti, dn, cr > ∈ course ∧ dn
=“Biology”
⇒ ∃ si, se, y, g ( <i, ci, si, se, y, g > ∈
takes ))}

② Note that without the existential quantification on student, the above query would be unsafe if the Biology department has not offered any courses.
* Above query fixes bug in page 246, last query

40、Relationship between Relational Algebra and Tuple （Domain）Calculus

① Codd’s theorem

• Relational algebra and tuple calculus are equivalent in terms of expressiveness

② That means that every query expressible in relational algebra can also be expressed in tuple
calculus and vice versa
③ Since domain calculus is as expressive as tuple calculus the same holds for the domain calculus
④ Note: Here relational algebra refers to the standard version (no aggregation and projection with functions)