Relational Algebra

Tom Kelliher, CS 318

Feb. 11, 2002

Administrivia

Announcements

Assignment

Read 6.2--3.

From Last Time

E/R design example.

Outline

1. Relational algebra's role.

2. Primitive RA operators.

3. Derived RA operators.

Coming Up

Queries and updates in SQL.

Relational Algebra's Role

Query processing:

Primitive Relational Algebra Operators

Generally, each operator takes:

1. One or two relations.

2. A conditional expression.

Select

1. Used to filter out tuples from a relation.

2. Example:

   

3. General form:

   

4. Selection-condition can be extended with the usual boolean operators.

5. Only real restriction: Attributes named in the selection-condition must belong to the relation.

Project

1. Used to filter out attributes from a relation.

2. Example:

   

3. Combination example:

   

Union

1. Relations must be union-compatible:
   1. Same number of attributes.

   2. Attribute names are the same in both relations.

   3. Attribute domains match.

2. Example:

   

Set Difference

1. Union-compatability required.

2. : Everything in A and not in B.

   Let and . and .

Cartesian Product

1. Used to combine two relations. Expensive to compute.

2. is the set of all tuples which can be formed by combining a tuple of A with a tuple of B.

   Let A be:

   

   and B be:

   

   is:

   

   Whoops. To rename the attributes, we could write:

   

Examples

Assume we have the schemas Student and Transcript:

Write relational algebra expressions to determine the following:

1. The names of all seniors.

2. The name and address of the student whose Id is 123456789.

3. The names of all juniors and those students who Id is between 111111111 and 222222222, inclusive.

4. The names of all students who have taken CS318.

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

6. The names and Ids of any students who have never taken a course.

Derived Relational Algebra Operators

Join

1. Theta join:

   

   Example:

   

   (Ids of all those who earn more than their supervisors.)

2. Equijoin: only comparison operator is =.

   Important because they ``reconstruct'' the database: Get names of all students who have taken CS318.

3. Natural join: equijoin on intersection of attributes where the redundant attributes in each tuple are projected out.

Division

1. Let <s> and <t> be sets of attributes and let relation and relation . Then:

   

2. Example. Let A be:

   

   Let B be:

   

   .

3. Derivation of division :