The Relational Algebra and SQL

Selection

The selection operation selects tuples from a relation that fit some criteria, creating a new relation with the selected tuples. We will use the notation

_C(R) = { t | C is true for t }

where is the selection operator, C is the selection condition, and R is a relation. The selection condition is a well-formed logical expression built from the following rules:

a comparison operation between attribute names or attribute values, and
the standard logical connectives: AND, OR, and NOT.

Some example conditions are given below for a relation with Name and Age attributes.

Name = 'Sue'

Name = 'Sue' AND Age >
  23

NOT (Name = 'Sue' AND Age
  > 23)

Let's look at some example of selection, and the meaning will become clear. Consider the relation Professions.

Professions
     Name |   Job
   ---------------------
     Joe   | Garbageman
     Sue   | Doctor
     Joe   | Surfer

Now consider the following selections and their results.

_{Name = Sue}(Professions) =

{(Sue, Doctor)}

_{Name = Sue OR Job = Surfer}(Professions) =

{(Sue, Doctor), (Joe, Surfer)}

_{Name = Sue AND Job = Surfer}(Professions) =

AND

{}

What does selection do in terms of the table metaphor? It merely selects those rows from the table that satisfy the selection condition, ignoring the rest. Note that the selected rows form a new table (possibly an empty table).

Projection

The projection operation projects out a list of attributes from a relation. For example, suppose we have a relation with the schema R(A₁, A₂, ..., A_N) and we want only the first M attributes

_{A₁, A₂,
..., A_M}

₁

₂

where is the projection operator, A₁, A₂, ..., A_M is a list of the first M attributes, and R is a relation. In general, we can project any of the attributes in a relation in any order. Let's look at some examples from the Professions relation depicted above.

_Job(Professions) would produce the following relation.
       Job
   --------------
     Garbageman
      Doctor
      Surfer
_Name(Professions) would produce the following relation (assuming we retain duplicates)
     Name
   --------
     Joe
     Sue
     Joe
or this table (assuming we eliminate duplicates)
     Name
   --------
     Joe
     Sue

Cartesian product of relations

The Cartesian product operation is similar to that for sets. Basically the Cartesian product produces a relation consisting of all possible pairings of tuples as follows. Assume we have relations R(A₁, A₂, ..., A_N) and S(B₁, B₂, ..., B_M) Then

₁

₂

₁

₂

₁

₂

AND

₁

₂

Note that R

S is not the same as S

R because the order of attributes differs.

Let's look at an example. Assume that in addition to the Professions relation, we have a Salaries relation.

Salaries
     Job       |   Pays
   ------------------------
   Garbageman | 50000
   Doctor      | 40000
   Surfer      |   6500

The result of Professions

Careers is depicted below.

     Name  |   Job       |  Job       |   Pays
   ---------------------------------------------
     Joe   |  Garbageman | Garbageman  |  50000
     Joe   |  Garbageman | Doctor      |  40000
     Joe   |  Garbageman | Surfer      |   6500
     Sue   |  Doctor     | Garbageman  |  50000
     Sue   |  Doctor     | Doctor      |  40000
     Sue   |  Doctor     | Surfer      |   6500
     Joe   |  Surfer     | Garbageman  |  50000
     Joe   |  Surfer     | Doctor      |  40000
     Joe   |  Surfer     | Surfer      |   6500

Note that we have two attributes now with the same name, Job, we will assume that one of the attributes is renamed appropriately.

Union, Intersection, Difference

Since a relation is just a set (or multiset), the set (or multiset) algebra operations, union, intersection, and difference, are also present in the relational algebra, with one constraint. These operations are only permitted between relations that are union compatible. Two relations are union compatible if they have the same number of attributes, and if the i^th attribute in each relation has the same domain. Basically, the two relations must have the same schemas, modulo renaming of the attributes, which makes a lot of sense since you really do not want two completely different kinds of tuples in the same relation.

A complete set of operations

We now have a complete set of relational algebra operations. Any other operator that we might introduce, such as a join, is merely for our notational convenience.

Joins

In general, a join is an operation that glues relations together. There are several kinds of joins.

Theta-join

The theta-join operation is the most general join operation. We can define theta-join in terms of the operations that we are familiar with already.

So the join of two relations results in a subset of the Cartesian product of those relations. Which subset is determined by the join condition:

. Let's look at an example. The result of

Professions

_{Job
= Job}

Careers

is shown below.

     Name  |   Job       |  Job       |   Pays
   ---------------------------------------------
     Joe   |  Garbageman | Garbageman  |  50000
     Sue   |  Doctor     | Doctor      |  40000
     Joe   |  Surfer     | Surfer      |   6500

Equi-join

The join condition,

, can be any well-formed logical expression, but usually it is just the conjunction of equality comparisions between pairs of attributes, one from each of the joined relations. This common case is called an equi-join. The example given above is an example of an equi-join.

Natural join

Note that in the result of an equi-join, the join attributes are duplicated. A natural join is an equi-join that projects away duplicated attributes. If

is omitted from a

we will assume that the operation is a natural join. Let

₁

and

₁

Then

_{A₁,...,A_n,X₁,...,X_m,B₁,...,B_k}

_{X₁ = X₁
AND ... AND X_m = X₁}

(We assume that the join attributes have been made distinct via renaming appropriately.)

Let's look at an example. The result of

Professions

Careers

is shown below.

     Name  |   Job       |   Pays
   ---------------------------------
     Joe   |  Garbageman |  50000
     Sue   |  Doctor     |  40000
     Joe   |  Surfer     |   6500

Reordering columns in a table

How do I go about swapping columns in a relation? I use projection? Assume I have relation

S = (A₁, A₂, A₃)

I want a relation that is just like S but with exactly the opposite order of attributes. Then I would do

_{A₃, A₂,
A₁}(S)

the result is S with the columns swapped.

Examples of Relational Algebra

Consider the following relations (depicted as tables).

  STUDENTS
    name | subject
   ----------------
    joe  |  CP1500
    joe  |  CP1200
    sue  |  CP3020

The Cartesian product of these relations,

PARENTOF

STUDENTS

would result in the following relation.

    parent | name | name | subject
 ----------------------------------
     pam   |  joe |  joe | CP1500
     pam   |  joe |  joe | CP1200
     pam   |  joe |  sue | CP3020
     pam   |  sue |  joe | CP1500
     pam   |  sue |  joe | CP1200
     pam   |  sue |  sue | CP3020
     ann   |  pam |  joe | CP1500
     ann   |  pam |  joe | CP1200
     ann   |  pam |  sue | CP3020
     eric  |  ann |  joe | CP1500
     eric  |  ann |  joe | CP1200
     eric  |  ann |  sue | CP3020

The equi-join (on the name attribute),

PARENTOF

_{name
= name}

STUDENTS

would result in the following relation.

    parent | name | name | subject
 ----------------------------------
     pam   |  joe |  joe | CP1500
     pam   |  joe |  joe | CP1200
     pam   |  sue |  sue | CP3020

Finally, the natural join,

PARENTOF

STUDENTS

would yield the following.

    parent | name | subject
 ----------------------------------
     pam   |  joe | CP1500
     pam   |  joe | CP1200
     pam   |  sue | CP3020

Now let's consider several examples using these relations.

Reordering columns example

Suppose I want a relation like STUDENTS, but with the subject first, then the name. I would do

_{subject, name}

STUDENTS

What are the names of the students?:
STUDENTNAMES = _name(STUDENTS)

Who is taking CP1500?:
CP1500 = _name(_{subject
= CP1500}(STUDENTS))

Who is the parent of joe?:
JOES_PARENTS = _parent(_{name
= joe}(PARENTOF))

The above three examples were all operations on a single table. We must use a join to combine information from two or more tables.

Who is the parent of a student taking CP1500?: In this example, we make use of the result of a previous query, the CP1500 relation is computed above.
CP1500_PARENTS = _name(PARENTOF CP1500)

Who is the grandparent of of a student taking CP1500?:
CP1500_GRANDPARENTS = _name(PARENTOF CP1500_PARENTS)

Exercise 1 Assume the following relations:

BOOKS(DocId, Title, Publisher, Year)
STUDENTS(StId, StName, Major, Age)
AUTHORS(AName, Address)
borrows(DocId, StId, Date)
has-written(DocId, AName)
describes(DocId, Keyword)

Here it is the entity relationship diagram. The key fields of the relations are underlined in the entity relationship diagram.

Write the following queries in relational algebra.

List the year and title of each book.
List all information about students whose major is CS.
List all students with books they can borrow.
List all books published by McGraw-Hill before 1990.
List the name of those authors who are living in Davis
List the name of students who are older than 30 and who are not studying CS.
Rename AName in the relation AUTHORS to Name.
List the names of all students who have borrowed a book and who are CS majors.
List the title of books written by the author ’Jones’.
As previous, but not books that have the keyword ’database’.
Find the name of the youngest student.
Find the title of the oldest book.

Exercise2 Consider the following schema:

Suppliers( sid: integer, sname: string, address: string );

Parts( pid: integer, pname: string. color: string );

Catalog( sid: integer, pid: integer, cost: real );

Execute the script to creating tables (The script contains all tables used in Chapter 5 in the textbook - not only these used in this exercise ). Load data into the tables.

Find the pid of parts with cost lower than 10$
Find the name of parts with cost lower than 10$
Find the address of the suppliers who supply "Fire Hydrant Cap"
Find the name of the suppliers who supply green parts

Here are the script and the data files