X

### Tuple Relational Calculus

• The tuple relational calculus, by contrast, is a non-procedural query language. A query in the tuple relational calculus is expressed as:

• {t | P(t)} so it is the set of all tuples 't' such that predicate 'P' is true for 't'.

• Implication : denoted by ⇒. Example: P ⇒ Q means “P implies Q”; that is,“if P is true, then Q must be true.” P ⇒ Q is logically equivalent to ¬P ∨ Q.

• For All : ∀ t ∈ r (Q(t)) means “Q is true for all tuples t in relation r.”

• A tuple-relational-calculus formula is built up out of atoms. An atom has one of the following forms:

1. s ∈ r, wheres is a tuple variable and r is a relation (we do not allow use of the ∉ operator)

2. s[x] Θ u[y], where s and u are tuple variables, x is an attribute on which s is deﬁned, y is an attribute on which u is deﬁned, and Θ is a comparison operator (<, ≤, =, ≠ , >, ≥); we require that attributes x and y have domains whose members can be compared by Θ.

• As we could for the relational algebra, we can write equivalent expressions that are not identical in appearance. In the tuple relational calculus, these equivalences include the following three rules:

1. P1 ∧ P2 is equivalent to ¬ (¬( P1) ∨ ¬( P2)).

2. ∀ t ∈ r ( P1(t)) is equivalent to ¬∃t ∈ r (¬P1 (t)).

3. P1 ⇒ P2 is equivalent to ¬( P1) ∨ P2.

Q. Find the ID, name, dept name, salary for instructors whose salary is greater than \$80,000.

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

Q. Find the instructor ID for each instructor with a salary greater than \$80,000 [ Hint : instructor ( ID, name, dept_name, salary ) ]

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

• Meaning of the preceding expression is : The set of all tuples 't' such that there exists a tuple 's' in relation instructor for which the values of 't' and 's' for the ID attribute are equal, and the value of 's' for the salary attribute is greater than \$80,000.

Q. Find the names of all instructors whose department is in the Watson building. [ Hint : instructor ( ID, name, dept_name, salary ) and department ( dept_name, building, budget)]

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

• Tuple variable 'u' is restricted to departments that are located in the Watson building, while tuple variable 's' is restricted to instructors whose dept_name matches that of tuple variable 'u'.

Q. To ﬁnd the set of all courses taught in the Fall 2009 semester, the Spring 2010 semester, or both,

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

• This expression gives us the set of all course id tuples for which at least one of the following holds :

1. The course_id appears in some tuple of the section relation with semester = Fall and year = 2009.

2. The course_id appears in some tuple of the section relation with semester = Spring and year = 2010.

• If the same course is offered in both the Fall 2009 and Spring 2010 semesters, its course_id appears only once in the result, because the mathematical definition of a set does not allow duplicate members.

Q. Select only those course_id values for courses that are offered in both the Fall 2009 and Spring 2010 semesters.

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

Q. Find all the courses taught in the Fall 2009 semester but not in Spring 2010 semester.

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

• This tuple-relational-calculus expression uses the ∃s ∈ section (...) clause to require that a particular course id is taught in the Fall 2009 semester, and it uses the ¬∃u ∈ section (...) clause to eliminate those course id values that appear in some tuple of the section relation as having been taught in the Spring 2010 semester.

Q. “Find all students who have taken all courses offered in the Biology department" [ Hint : course ( course_id, title, dept_name, credits ) , student ( ID, name, dept_name, tot_cred ) and takes ( ID, course_id, sec_id, semester, year, grade ) ]

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

 Previous Next