Tuple Relational Calculus

Tom Kelliher, CS 318

Feb. 22, 2002

Administrivia

Announcements

Homework due Wednesday. E/R designs due today @ 5:00 PM.

Assignment

Read 7.3 for Wednesday.

From Last Time

PHP/PostgreSQL lab.

Outline

1. Introduction to TRC.

2. Examples.

3. Practice.

Coming Up

Domain relational calculus.

Tuple Relational Calculus

1. SQL is based upon this.

2. Simple example TRC query:

   

   Corresponding SQL:

   SELECT *
   FROM Teaching T
   WHERE T.Semester = 'F1997';
   

3. General form of a TRC query:

   

   where:

   1. T is a tuple variable which ranges over all possible values of tuples.

   2. Condition is:
      1. Atomic:
         1. , asserting that T is in relation R.

         2. or .

      2. Two conditions combined using AND or OR, or one condition modified by NOT.

      3. A condition (C), Relation (R), and tuple variable (T) combined as

         

   3. T may be the only free (vs. bound) variable.

   4. This shorthand extension is acceptable:

      

      where T.A and S.B are the only free variables. Meaning? (Implied exists.)

4. Meaning of a TRC query:

   The result of a TRC query with respect to a given database is the set of all choices of values for the variable T that make the query condition a true statement about the database.

   Implies an exhaustive search over the tuple space.

Examples

1. All courses that have been taken by every student:

   

   Requires division in relational algebra!

2. Find all students who have ever taken a course from every professor who has ever taught a course. Why doesn't this express that?

   

   How do we fix this?

3. Retrieve IDs of students who did not take any courses in F2001:

   

4. Find potential student graders for this semester's courses:

   Hard way, similar to SQL query we developed earlier:

   

   Simplified version:

   

Practice

Write TRC queries to answer the following:

1. Retrieve name of student with largest ID number.

2. Names of all professors who have taught CS318.

3. The names of all sophomores who received A's during the F2001 semester.

4. The IDs of all students who took exactly one course during the F2001 semester.

5. The IDs of all students who have taken a course with me.

6. The IDs of all students who have taken every course I've taught.