**Tom Kelliher, CS 318**

**Feb. 22, 2002**

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

Read 7.3 for Wednesday.

PHP/PostgreSQL lab.

- Introduction to TRC.
- Examples.
- Practice.

Domain relational calculus.

- SQL is based upon this.
- Simple example TRC query:
Corresponding SQL:

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

- General form of a TRC query:
where:

- T is a
*tuple variable*which ranges over all possible values of tuples. - Condition is:
- Atomic:
- , asserting that T is in relation R.
- or .

- , asserting that T is in relation R.
- Two conditions combined using AND or OR, or one condition
modified by NOT.
- A condition (C), Relation (R), and tuple variable (T) combined as

- Atomic:
- T may be the only
*free*(vs. bound) variable. - This shorthand extension is acceptable:
where T.A and S.B are the only free variables. Meaning? (Implied exists.)

- T is a
- 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.*

- All courses that have been taken by every student:
Requires division in relational algebra!

- 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?

- Retrieve IDs of students who did not take any courses in F2001:
- Find potential student graders for this semester's courses:
Hard way, similar to SQL query we developed earlier:

Simplified version:

Write TRC queries to answer the following:

- Retrieve name of student with largest ID number.
- Names of all professors who have taught CS318.
- The names of all sophomores who received A's during the F2001
semester.
- The IDs of all students who took exactly one course during the F2001
semester.
- The IDs of all students who have taken a course with me.
- The IDs of all students who have taken every course I've taught.

Wed Feb 20 18:21:28 EST 2002