Relational Algebra

A set of operators is used to manipulate relations.

1. Operators act on whole relations i.e. a set of tuples not single tuples. Relational algebra expressions define what has to be done and not how.
2. Relational algebra has closure property to guarantee that the result of the application of relational algebra to any relation will have as its result a relation. A resulting relation can be used as an operand in future expressions.
3. Relational algebra is not a language for serious use in implementing a DBMS.
4. Operators of relational theory exploit the logical structure of relations.

The relational operators looked at here are:

• select
• project
• join
• divide
• union
• intersection
• difference

Select

The select expression follows this format:

select <relation> where <selection condition>

Selection can be through one simple condition or a boolean combination of simple conditions:

select Student where Registered > 1996

select Student where Registered > 1996 and StudentId <> s09

The results will form a horizontal slice to produce the complete tuples from those instances of Student that satisfy the conditions.

Simple selections are comparisons of an attribute with a constant. These constants need not necessarily be of the same attributes. However, the values must be defined in the same domain.

Project

This operator has the effect of taking a vertical slice in producing, from the tuples, only those attributes selected. It follows the form:

project Student over Name

Where there are a number of attributes that are the same, only one of each of those attributes will be returned due to the closure property reducing the results. This will avoid duplication.

A single expression can be used to extract a relation with more than one attribute using an attribute list:

project Student over Name, Region

Combining expressions

Relational expressions can be combined in a way that allows the result of one expression can be used as the operand for a second expression. The term used is giving.

select Student where Region = 3 giving A

The relation produced is referred to by the label A, and this can be used as an operand.

select Student where Region = 3 giving A
project A over Name giving B

Nested expressions can be used, where the nested expression is evaluated first, and the resulting relation becomes the operand.

project(select Student where Region = 3 ) over Name giving B

Join

The join operator pastes relations together using shared attribute values.

join Student and Staff where CounsellorNo = StaffNo

Divide

Divide also operates on two relations using shared attribute values. The result is a smaller relation containing only tuples for which the second relation is a factor.

divide Studies by AllCourses over CourseCode giving C

Union

The result of a union operator is a relation containimng all the tuples belonging to either or both <relation 1> <relation 2>. The union expression is:

<relation 1> union <relation 2>

Two relations are union-compatible if:

• they are of the same degree
• there exists one to one mapping with a corresponding relation
• the corresponding attributes are defined in the same domain

Where attributes do not correspond, a project can be used to form a temporary relation with attributes written in the required order.

For the closure property to be enforced the relations used as operands in union, intersection and difference must be union-compatible.

Union operators are commutative. The operands can be reversed with no effect on the result (Unless attribute names in the relations differ. In this case the result heading will reflect the different names).

Intersection

<relation 1> intersection <relation 2>

Similar to union but results in a relation containing tuples belonging to both relations. Relations must be union-compatible.

Difference

<relation 1> difference <relation 2>

This operator is not commutative. the result will be a relation of tuples belonging to <relation 1> but do not belong to <relation 2>.

Updating

Updating is achieved by using the relational assignment operator, :=. The relational assignment operator allows any relation to be assigned to any other union-compatible relation. the tuples of one relation will be replaced by copies from another relation.

UpdatedRelation := RelationalExpression

More operators

Theta-join

theta-join <relation 1> and <relation 2>
where <attribute 1> theta <attribute 2>

The theta expression in the condition is a comparison e.g. greater than, less than etc.

Cartesian product (aka the times operator)

<relation 1> times <relation 2>

The result has a heading of all the attributes of the headings in both relations and tuples made up of all tuples of <relation 2> appended to every tuple of <relation 1>.

Relational calculus

Putting into a formal language the English language specification of a desired relation.

Range of E, EX is Enrolment
Produce E.TutorNo wher FORALL EX
(IF E.TutorNo = EX.TutorNo THEN EX. CourseCode = c2)

Comments, suggestions, ideas to
Stuart Banner