Introduction to Relational Algebra

by K. Yue

1. Introduction

More theoretical query languages of the relational model:
1. relational algebra: procedural/operational/functional
2. relational calculus: declarative
Provide theoretical foundation for the relational model.
Relational algebra:
- Include a set of basic and derived set-theoretic operations.
- Procedural: specify a sequence of operations.
  - performance optimization is important.
- Operations can be unary or binary.
- The result is also a relation: closure property => chained operations.
- RA solutions are algorithmic.

Query Language Interpreter

You may use a relational algebra interpreter for practice:

Install the relational algebra (aql.jar) interpreter found in: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/. It uses Java so make sure that your PATH and CLASSPATH are set correctly.
Read the manual: http://tinman.cs.gsu.edu/~raj/elna-lab-2010/ch2/ch2.pdf, especially section 2.2 on the relational algebra interpreter.
Run the RA interpreter. E.g. in the parent directory command prompt, run "java edu.gsu.cs.ra.RA supply", assuming that the database "supply" is stored in the subdirectory supply. You will need to have . and ./* in CLASSPATH in order to do so. For example, CLASSPATH may be: ".;.\*;%JAVA_HOME%\lib".
Here is the supply.zip that contains the zipped supply folder you will need.
However, note that the RA interpreter has restrictions.

2. Basic Operations

Cartesian Product

Same as the usual definition of Cartesian Product of two sets.
- Remember that a relation is a set.
Merge all possible information from two relations.
Also called Cross Product or Cross Join.
Name ambiguity may be resolved by using full names.
The cardinality of a set S is |S|, the number of elements in the set.
|RxS|= |R| * |S|
Not very useful in practice as the result can be large and constructing the result can be time consuming.

Example:

R(A,B,C) has three tuples. S(A,D) has four tuples.

The result of R x S always has 12 tuples with the schema (R.A, B, C, S.A, D).

Example:

Try the following commands on the RA interpreter. To start the RA interpreter using the supply database:

java edu.gsu.cs.ra.RA supply

supplier;
supply;
supplier times supply;

supplier:
+------+-------+---------+--------+
| SNUM | SNAME | SCITY   | STATUS |
+------+-------+---------+--------+
| S1   | ABC   | Dallas |     10 |
| S2   | DEF   | Houston |     20 |
| S3   | Go go | Houston |     12 |
| S4   | P&G   | Dallas |      2 |
| S5   | Yap   | Phoenix |      5 |
| S6   | Yue   | Dallas |      1 |
+------+-------+---------+--------+

supply:
+------+------+----------+
| SNUM | PNUM | QUANTITY |
+------+------+----------+
| S1   | P1   |       10 |
| S1   | P2   |        3 |
| S2   | P1   |       11 |
| S2   | P2   |        1 |
| S2   | P4   |        6 |
| S3   | P4   |        1 |
| S3   | P5   |        2 |
| S3   | P6   |       12 |
| S3   | P7   |        5 |
| S4   | P2   |        1 |
| S4   | P5   |       10 |
| S4   | P7   |        4 |
| S4   | P8   |       10 |
| S5   | P1   |       11 |
| S5   | P3   |        5 |
| S5   | P4   |       10 |
| S5   | P5   |       14 |
+------+------+----------+

supplier * supply:
+------+-------+---------+--------+------+------+----------+
| SNUM | SNAME | SCITY   | STATUS | SNUM | PNUM | QUANTITY |
+------+-------+---------+--------+------+------+----------+
| S1   | ABC   | Dallas |     10 | S1   | P1   |       10 |
| S2   | DEF   | Houston |     20 | S1   | P1   |       10 |
| S3   | Go go | Houston |     12 | S1   | P1   |       10 |
| S4   | P&G   | Dallas |      2 | S1   | P1   |       10 |
| S5   | Yap   | Phoenix |      5 | S1   | P1   |       10 |
| S6   | Yue   | Dallas |      1 | S1   | P1   |       10 |
| S1   | ABC   | Dallas |     10 | S1   | P2   |        3 |
| S2   | DEF   | Houston |     20 | S1   | P2   |        3 |
| S3   | Go go | Houston |     12 | S1   | P2   |        3 |
| S4   | P&G   | Dallas |      2 | S1   | P2   |        3 |
| S5   | Yap   | Phoenix |      5 | S1   | P2   |        3 |
| S6   | Yue   | Dallas |      1 | S1   | P2   |        3 |
| S1   | ABC   | Dallas |     10 | S2   | P1   |       11 |
| S2   | DEF   | Houston |     20 | S2   | P1   |       11 |
| S3   | Go go | Houston |     12 | S2   | P1   |       11 |
| S4   | P&G   | Dallas |      2 | S2   | P1   |       11 |
| S5   | Yap   | Phoenix |      5 | S2   | P1   |       11 |
| S6   | Yue   | Dallas |      1 | S2   | P1   |       11 |
| S1   | ABC   | Dallas |     10 | S2   | P2   |        1 |
| S2   | DEF   | Houston |     20 | S2   | P2   |        1 |
| S3   | Go go | Houston |     12 | S2   | P2   |        1 |
| S4   | P&G   | Dallas |      2 | S2   | P2   |        1 |
| S5   | Yap   | Phoenix |      5 | S2   | P2   |        1 |
| S6   | Yue   | Dallas |      1 | S2   | P2   |        1 |
| S1   | ABC   | Dallas |     10 | S2   | P4   |        6 |
| S2   | DEF   | Houston |     20 | S2   | P4   |        6 |
| S3   | Go go | Houston |     12 | S2   | P4   |        6 |
| S4   | P&G   | Dallas |      2 | S2   | P4   |        6 |
| S5   | Yap   | Phoenix |      5 | S2   | P4   |        6 |
| S6   | Yue   | Dallas |      1 | S2   | P4   |        6 |
| S1   | ABC   | Dallas |     10 | S3   | P4   |        1 |
| S2   | DEF   | Houston |     20 | S3   | P4   |        1 |
| S3   | Go go | Houston |     12 | S3   | P4   |        1 |
| S4   | P&G   | Dallas |      2 | S3   | P4   |        1 |
| S5   | Yap   | Phoenix |      5 | S3   | P4   |        1 |
| S6   | Yue   | Dallas |      1 | S3   | P4   |        1 |
| S1   | ABC   | Dallas |     10 | S3   | P5   |        2 |
| S2   | DEF   | Houston |     20 | S3   | P5   |        2 |
| S3   | Go go | Houston |     12 | S3   | P5   |        2 |
| S4   | P&G   | Dallas |      2 | S3   | P5   |        2 |
| S5   | Yap   | Phoenix |      5 | S3   | P5   |        2 |
| S6   | Yue   | Dallas |      1 | S3   | P5   |        2 |
| S1   | ABC   | Dallas |     10 | S3   | P6   |       12 |
| S2   | DEF   | Houston |     20 | S3   | P6   |       12 |
| S3   | Go go | Houston |     12 | S3   | P6   |       12 |
| S4   | P&G   | Dallas |      2 | S3   | P6   |       12 |
| S5   | Yap   | Phoenix |      5 | S3   | P6   |       12 |
| S6   | Yue   | Dallas |      1 | S3   | P6   |       12 |
| S1   | ABC   | Dallas |     10 | S3   | P7   |        5 |
| S2   | DEF   | Houston |     20 | S3   | P7   |        5 |
| S3   | Go go | Houston |     12 | S3   | P7   |        5 |
| S4   | P&G   | Dallas |      2 | S3   | P7   |        5 |
| S5   | Yap   | Phoenix |      5 | S3   | P7   |        5 |
| S6   | Yue   | Dallas |      1 | S3   | P7   |        5 |
| S1   | ABC   | Dallas |     10 | S4   | P2   |        1 |
| S2   | DEF   | Houston |     20 | S4   | P2   |        1 |
| S3   | Go go | Houston |     12 | S4   | P2   |        1 |
| S4   | P&G   | Dallas |      2 | S4   | P2   |        1 |
| S5   | Yap   | Phoenix |      5 | S4   | P2   |        1 |
| S6   | Yue   | Dallas |      1 | S4   | P2   |        1 |
| S1   | ABC   | Dallas |     10 | S4   | P5   |       10 |
| S2   | DEF   | Houston |     20 | S4   | P5   |       10 |
| S3   | Go go | Houston |     12 | S4   | P5   |       10 |
| S4   | P&G   | Dallas |      2 | S4   | P5   |       10 |
| S5   | Yap   | Phoenix |      5 | S4   | P5   |       10 |
| S6   | Yue   | Dallas |      1 | S4   | P5   |       10 |
| S1   | ABC   | Dallas |     10 | S4   | P7   |        4 |
| S2   | DEF   | Houston |     20 | S4   | P7   |        4 |
| S3   | Go go | Houston |     12 | S4   | P7   |        4 |
| S4   | P&G   | Dallas |      2 | S4   | P7   |        4 |
| S5   | Yap   | Phoenix |      5 | S4   | P7   |        4 |
| S6   | Yue   | Dallas |      1 | S4   | P7   |        4 |
| S1   | ABC   | Dallas |     10 | S4   | P8   |       10 |
| S2   | DEF   | Houston |     20 | S4   | P8   |       10 |
| S3   | Go go | Houston |     12 | S4   | P8   |       10 |
| S4   | P&G   | Dallas |      2 | S4   | P8   |       10 |
| S5   | Yap   | Phoenix |      5 | S4   | P8   |       10 |
| S6   | Yue   | Dallas |      1 | S4   | P8   |       10 |
| S1   | ABC   | Dallas |     10 | S5   | P1   |       11 |
| S2   | DEF   | Houston |     20 | S5   | P1   |       11 |
| S3   | Go go | Houston |     12 | S5   | P1   |       11 |
| S4   | P&G   | Dallas |      2 | S5   | P1   |       11 |
| S5   | Yap   | Phoenix |      5 | S5   | P1   |       11 |
| S6   | Yue   | Dallas |      1 | S5   | P1   |       11 |
| S1   | ABC   | Dallas |     10 | S5   | P3   |        5 |
| S2   | DEF   | Houston |     20 | S5   | P3   |        5 |
| S3   | Go go | Houston |     12 | S5   | P3   |        5 |
| S4   | P&G   | Dallas |      2 | S5   | P3   |        5 |
| S5   | Yap   | Phoenix |      5 | S5   | P3   |        5 |
| S6   | Yue   | Dallas |      1 | S5   | P3   |        5 |
| S1   | ABC   | Dallas |     10 | S5   | P4   |       10 |
| S2   | DEF   | Houston |     20 | S5   | P4   |       10 |
| S3   | Go go | Houston |     12 | S5   | P4   |       10 |
| S4   | P&G   | Dallas |      2 | S5   | P4   |       10 |
| S5   | Yap   | Phoenix |      5 | S5   | P4   |       10 |
| S6   | Yue   | Dallas |      1 | S5   | P4   |       10 |
| S1   | ABC   | Dallas |     10 | S5   | P5   |       14 |
| S2   | DEF   | Houston |     20 | S5   | P5   |       14 |
| S3   | Go go | Houston |     12 | S5   | P5   |       14 |
| S4   | P&G   | Dallas |      2 | S5   | P5   |       14 |
| S5   | Yap   | Phoenix |      5 | S5   | P5   |       14 |
| S6   | Yue   | Dallas |      1 | S5   | P5   |       14 |
+------+-------+---------+--------+------+------+----------+

In SQL, the select statement is very powerful and can be used to simulate RA operations. For R * S:

SELECT R.*, S.*
FROM R, S; -- note that there is no join condition.

Select

Unary operation.
Select tuples (with the same schema) based on a Boolean condition.
Conditions may include attributes in the relational schema.
The Boolean expression of the condition can be a composite one.
'Horizontal' subset.
Not to be confused with the Select statement in SQL.

select

σ_cond(R) = {t | t ε R and cond}

Example: All information of suppliers in Houston

select [SCity <> 'Houston'] (supplier);
temp0(SNUM:VARCHAR,SNAME:VARCHAR,SCITY:VARCHAR,STATUS:INTEGER)

Number of tuples = 4
S1:ABC:Dallas:10:
S4:P&G:Dallas:2:
S5:Yap:Phoenix:5:
S6:Yue:Dallas:1:

or

σ_{SCity <> 'Houston'}(supplier)

+------+-------+---------+--------+
| SNUM | SNAME | SCITY   | STATUS |
+------+-------+---------+--------+
| S1   | ABC   | Dallas |     10 |
| S4   | P&G   | Dallas |      2 |
| S5   | Yap   | Phoenix |      5 |
| S6   | Yue   | Dallas |      1 |
+------+-------+---------+--------+

Project

Unary operation
Select attributes from tuples.
Duplicate results removed (because a relation is a set).
'Vertical' subset.

π_{c1, .., cm}(R) = {s | there exists t ε R (t(ci) = s(ci), for 1 <= i <= m)}

Example: Supplier names and status:

RA> project [SName, Status] (Supplier);
temp0(SNAME:VARCHAR,STATUS:INTEGER)

Number of tuples = 6
ABC:10:
DEF:20:
Go go:12:
P&G:2:
Yap:5:
Yue:1

π_{SNum, Status}(Supplier)

+------+--------+
| Snum | status |
+------+--------+
| S1   |     10 |
| S2   |     20 |
| S3   |     12 |
| S4   |      2 |
| S5   |      5 |
| S6   |      1 |
+------+--------+

Union

The set union operator.
Condition for R U S: R and S must be union compatible. Their relation schema must have compatible schema with the same structures.

R U S = {t | t ε R or t ε S}

Example:

Suppose StaffID and FacultyID are union compatible.

π_StaffID(Staff) U π_FacultyID(Faculty)

Example: All information of suppliers in Houston or Dallas.

σ_{(Scity='Houston')}(Supplier) U σ_{(Scity='Dallas')} (Supplier)

+------+-------+---------+--------+
| SNUM | SNAME | SCITY   | STATUS |
+------+-------+---------+--------+
| S1   | ABC   | Dallas |     10 |
| S2   | DEF   | Houston |     20 |
| S3   | Go go | Houston |     12 |
| S4   | P&G   | Dallas |      2 |
| S6   | Yue   | Dallas |      1 |
+------+-------+---------+--------+

Note that this is the same as:

σ_{(Scity='Houston' or Scity='Houston')} (Supplier).

Difference (Minus)

The set difference operator.
R - S: R and S must be union compatible.

R - S = {t | t ε R and not (t ε S)}

Example: Information of all suppliers in Dallas but not with a status of 1 or below.

σ_{(Scity='Dallas')} (Supplier) - σ_{(Status <=1)} (Supplier)

+------+-------+--------+--------+
| SNUM | SNAME | SCITY | STATUS |
+------+-------+--------+--------+
| S1 | ABC | Dallas | 10 |
| S4 | P&G | Dallas | 2 |
+------+-------+--------+--------+

Note that this is the same as:

σ_{(Scity='Dallas' and Status >1)} (Supplier)

Rename

Rename the names of selected attributes in a relation.
Maybe used to rename attributes before a set operation.
Notation in Elmarsi:

rename

A better notation includes the original name and the new name.

Example:

RA> rename [snum, name, city, status] (supplier);
temp0(SNUM:VARCHAR,NAME:VARCHAR,CITY:VARCHAR,STATUS:INTEGER)

Number of tuples = 6
S1:ABC:Dallas:10:
S2:DEF:Houston:20:
S3:Go go:Houston:12:
S4:P&G:Dallas:2:
S5:Yap:Phoenix:5:
S6:Yue:Dallas:1:

ρ_{(Name, City <- SNAME, SCITY)} (Supplier)

ρ_{(Name <- SNAME, CITY <- SCITY)}(Supplier)

+------+-------+---------+--------+
| SNUM | Name | City    | Status |
+------+-------+---------+--------+
| S1   | ABC   | Dallas |     10 |
| S2   | DEF   | Houston |     20 |
| S3   | Go go | Houston |     12 |
| S4   | P&G   | Dallas |      2 |
| S5   | Yap   | Phoenix |      5 |
| S6   | Yue   | Dallas |      1 |
+------+-------+---------+--------+

This basic set of RA operations is complete. Other relational algebra operations can be derived from them.

3. Common Derived Operations

Theta-join

Allow the application of condition on Cartesian product.
There are still redundant data on common attributes.
Allow the query engine to throw away tuples not in the result immediately.
Conceptually, a Cartesian Product followed by a selection.
Not usually used.

R1 |x|_Θ R2 = σ_Θ(R1 x R2)

Example:

Supplier |x|_{(quantity>11)} Supply

+------+-------+---------+--------+------+------+----------+
| SNUM | SNAME | SCITY   | STATUS | SNUM | PNUM | QUANTITY |
+------+-------+---------+--------+------+------+----------+
| S1   | ABC   | Dallas |     10 | S3   | P6   |       12 |
| S2   | DEF   | Houston |     20 | S3   | P6   |       12 |
| S3   | Go go | Houston |     12 | S3   | P6   |       12 |
| S4   | P&G   | Dallas |      2 | S3   | P6   |       12 |
| S5   | Yap   | Phoenix |      5 | S3   | P6   |       12 |
| S6   | Yue   | Dallas |      1 | S3   | P6   |       12 |
| S1   | ABC   | Dallas |     10 | S5   | P5   |       14 |
| S2   | DEF   | Houston |     20 | S5   | P5   |       14 |
| S3   | Go go | Houston |     12 | S5   | P5   |       14 |
| S4   | P&G   | Dallas |      2 | S5   | P5   |       14 |
| S5   | Yap   | Phoenix |      5 | S5   | P5   |       14 |
| S6   | Yue   | Dallas |      1 | S5   | P5   |       14 |
+------+-------+---------+--------+------+------+----------+

Equi-join

Theta-join where the condition involves only equality comparisons of common attributes (with the same names).
There are still redundant data on common attributes.
Not usually used.

Example:

Supplier |x| _{(Supplier.SNUM = Supply.SNUM)} Supply

+------+-------+---------+--------+------+------+----------+
| SNUM | SNAME | SCITY   | STATUS | SNUM | PNUM | QUANTITY |
+------+-------+---------+--------+------+------+----------+
| S1   | ABC   | Dallas |     10 | S1   | P1   |       10 |
| S1   | ABC   | Dallas |     10 | S1   | P2   |        3 |
| S2   | DEF   | Houston |     20 | S2   | P1   |       11 |
| S2   | DEF   | Houston |     20 | S2   | P2   |        1 |
| S2   | DEF   | Houston |     20 | S2   | P4   |        6 |
| S3   | Go go | Houston |     12 | S3   | P4   |        1 |
| S3   | Go go | Houston |     12 | S3   | P5   |        2 |
| S3   | Go go | Houston |     12 | S3   | P6   |       12 |
| S3   | Go go | Houston |     12 | S3   | P7   |        5 |
| S4   | P&G   | Dallas |      2 | S4   | P2   |        1 |
| S4   | P&G   | Dallas |      2 | S4   | P5   |       10 |
| S4   | P&G   | Dallas |      2 | S4   | P7   |        4 |
| S4   | P&G   | Dallas |      2 | S4   | P8   |       10 |
| S5   | Yap   | Phoenix |      5 | S5   | P1   |       11 |
| S5   | Yap   | Phoenix |      5 | S5   | P3   |        5 |
| S5   | Yap   | Phoenix |      5 | S5   | P4   |       10 |
| S5   | Yap   | Phoenix |      5 | S5   | P5   |       14 |
+------+-------+---------+--------+------+------+----------+

Example: equi-join by basic operations. Note the need of using the rename operation.

select [snum=snum1]
(supplier times
(rename[snum1, pnum, quantity](supply)));

temp2(SNUM:VARCHAR,SNAME:VARCHAR,SCITY:VARCHAR,STATUS:INTEGER,SNUM1:VARCHAR,PNUM:VARCHAR,QUANTITY:INTEGER)

Number of tuples = 17
S1:ABC:Dallas:10:S1:P1:10:
S1:ABC:Dallas:10:S1:P2:3:
S2:DEF:Houston:20:S2:P1:11:
S2:DEF:Houston:20:S2:P2:1:
S2:DEF:Houston:20:S2:P4:6:
S3:Go go:Houston:12:S3:P4:1:
S3:Go go:Houston:12:S3:P5:2:
S3:Go go:Houston:12:S3:P6:12:
S3:Go go:Houston:12:S3:P7:5:
S4:P&G:Dallas:2:S4:P2:1:
S4:P&G:Dallas:2:S4:P5:10:
S4:P&G:Dallas:2:S4:P7:4:
S4:P&G:Dallas:2:S4:P8:10:
S5:Yap:Phoenix:5:S5:P1:11:
S5:Yap:Phoenix:5:S5:P3:5:
S5:Yap:Phoenix:5:S5:P4:10:
S5:Yap:Phoenix:5:S5:P5:14:

Natural Join

Remove redundant common attributes.
- Equi-join on all common attributes.
- Projection to remove redundant common attributes.
Used very frequently to combine two tables.
If two relations do not share any common attributes, their natural join is the same as their Cartesian Product.

Let C1, C2, ... Cm be the common attributes of R and S.

R |x| S = π_{A1, A2, .. Al}(σ_{R.C1=S.C1,.., R.Cm=S.Cm}(RxS)

where A1, A2, ... Al is the list of components in RxS except S.C1, S.C2,.. S.Cm.

Example:

The schema of R(A,B) |x| S(A,C) is ABC. The schema of R(A,B) x S(A,C) is {R.A, B, S.A, C}.

Example:

Supplier |x| Supply:

+------+-------+---------+--------+------+----------+
| SNUM | SNAME | SCITY   | STATUS | PNUM | QUANTITY |
+------+-------+---------+--------+------+----------+
| S1   | ABC   | Dallas |     10 | P1   |       10 |
| S1   | ABC   | Dallas |     10 | P2   |        3 |
| S2   | DEF   | Houston |     20 | P1   |       11 |
| S2   | DEF   | Houston |     20 | P2   |        1 |
| S2   | DEF   | Houston |     20 | P4   |        6 |
| S3   | Go go | Houston |     12 | P4   |        1 |
| S3   | Go go | Houston |     12 | P5   |        2 |
| S3   | Go go | Houston |     12 | P6   |       12 |
| S3   | Go go | Houston |     12 | P7   |        5 |
| S4   | P&G   | Dallas |      2 | P2   |        1 |
| S4   | P&G   | Dallas |      2 | P5   |       10 |
| S4   | P&G   | Dallas |      2 | P7   |        4 |
| S4   | P&G   | Dallas |      2 | P8   |       10 |
| S5   | Yap   | Phoenix |      5 | P1   |       11 |
| S5   | Yap   | Phoenix |      5 | P3   |        5 |
| S5   | Yap   | Phoenix |      5 | P4   |       10 |
| S5   | Yap   | Phoenix |      5 | P5   |       14 |
+------+-------+---------+--------+------+----------+

supplier join supply;

Natural join by basic operations:

project [snum, sname, scity, status, pnum, quantity]
(select [snum=snum1]
(supplier times
(rename[snum1, pnum, quantity](supply))));

temp3(SNUM:VARCHAR,SNAME:VARCHAR,SCITY:VARCHAR,STATUS:INTEGER,PNUM:VARCHAR,QUANTITY:INTEGER)

Number of tuples = 17
S1:ABC:Dallas:10:P1:10:
S1:ABC:Dallas:10:P2:3:
S2:DEF:Houston:20:P1:11:
S2:DEF:Houston:20:P2:1:
S2:DEF:Houston:20:P4:6:
S3:Go go:Houston:12:P4:1:
S3:Go go:Houston:12:P5:2:
S3:Go go:Houston:12:P6:12:
S3:Go go:Houston:12:P7:5:
S4:P&G:Dallas:2:P2:1:
S4:P&G:Dallas:2:P5:10:
S4:P&G:Dallas:2:P7:4:
S4:P&G:Dallas:2:P8:10:
S5:Yap:Phoenix:5:P1:11:
S5:Yap:Phoenix:5:P3:5:
S5:Yap:Phoenix:5:P4:10:
S5:Yap:Phoenix:5:P5:14:

Exercise:

Let the cardinality of R(A,B) be 5 and the cardinality of S(A,C) be 6. What is the range of the possible cardinality of R(A,B) |x| S(A,C)?

0 to 30.

Example:

Consider the company database of Sunderraman:

EMPLOYEE(FNAME, MINIT,LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)
DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE)
DEPT_LOCATIONS(DNUMBER, DLOCATION)
PROJECTS(PNAME, PNUMBER, PLOCATION, DNUM)
WORKS_ON(ESSN, PNO, HOURS)
DEPENDENT(ESSN, DEPENDENT_NAME, SEX, BDATE, RELATIONSHIP)

Try:

EMPLOYEE;
DEPARTMENT;

Query: list all employees with their department information. Try:

EMPLOYEE JOIN DEPARTMENT;

You get:

temp0(FNAME:VARCHAR,MINIT:VARCHAR,LNAME:VARCHAR,SSN:VARCHAR,BDATE:VARCHAR,ADDRESS:VARCHAR,SEX:VARCHA
R,SALARY:DECIMAL,SUPERSSN:VARCHAR,DNO:INTEGER,DNAME:VARCHAR,DNUMBER:INTEGER,MGRSSN:VARCHAR,MGRSTARTD
ATE:VARCHAR)

Number of tuples = 240
James:E:Borg:888665555:10-NOV-27:450 Stone, Houston, TX:M:55000.0:null:1:Research:5:333445555:22-MAY
-1978:
James:E:Borg:888665555:10-NOV-27:450 Stone, Houston, TX:M:55000.0:null:1:Administration:4:987654321:
01-JAN-1985:
...

The result is incorrect and is the same as

EMPLOYEE TIMES DEPARTMENT;

There is no common attribute with the same name. Instead, DNO in Employee refers to DNUMBER. Instead, use:

RA> EMPLOYEE JOIN (RENAME [DNAME, DNO, MGRSSN, MGRSTARTDATE](DEPARTMENT));
temp1(FNAME:VARCHAR,MINIT:VARCHAR,LNAME:VARCHAR,SSN:VARCHAR,BDATE:VARCHAR,ADDRESS:VARCHAR,SEX:VARCHA
R,SALARY:DECIMAL,SUPERSSN:VARCHAR,DNO:INTEGER,DNAME:VARCHAR,MGRSSN:VARCHAR,MGRSTARTDATE:VARCHAR)

Number of tuples = 40
James:E:Borg:888665555:10-NOV-27:450 Stone, Houston, TX:M:55000.0:null:1:Headquarters:888665555:19-J
UN-1971:
Franklin:T:Wong:333445555:08-DEC-45:638 Voss, Houston, TX:M:40000.0:888665555:5:Research:333445555:2
2-MAY-1978:

Example:

java edu.gsu.cs.ra.RA company

Query: List all employees who are supervisors and have dependents.

RA> (PROJECT [SUPERSSN] (EMPLOYEE))
RA> INTERSECT
RA> (PROJECT [ESSN] (DEPENDENT));
temp2(SUPERSSN:VARCHAR)

Number of tuples = 3
333445555:
987654321:
444444400:

Note that the names of the attributes are different: SUPERSSN and ESSN. However, this is fine as they are union-compatible.

Query: List information of all employees who are supervisors and have dependents.

RA> EMPLOYEE
RA> JOIN
RA> (RENAME [SSN]
RA> ((PROJECT [SUPERSSN] (EMPLOYEE))
RA> INTERSECT
RA> (PROJECT [ESSN] (DEPENDENT))));
temp4(FNAME:VARCHAR,MINIT:VARCHAR,LNAME:VARCHAR,SSN:VARCHAR,BDATE:VARCHAR,ADDRESS:VARCHAR,SEX:VARCHA
R,SALARY:DECIMAL,SUPERSSN:VARCHAR,DNO:INTEGER)

Number of tuples = 3
Franklin:T:Wong:333445555:08-DEC-45:638 Voss, Houston, TX:M:40000.0:888665555:5:
Jennifer:S:Wallace:987654321:20-JUN-31:291 Berry, Bellaire, TX:F:43000.0:888665555:4:
Alex:D:Freed:444444400:09-OCT-1950:4333 Pillsbury, Milwaukee, WI:M:89000.0:null:7:

Other Joins

Some other joins are outer join, inner join and semi-join.
They can be defined through relational algebra expressions based on the basic operations.
Look them up when needs arise. For example: https://en.wikipedia.org/wiki/Relational_algebra

Division

R / S
Condition: the domain of S is a proper subset of R.
Let the schemes of R, S and T be dom(R), dom(S) and dom(T) = dom(R) - dom(S) respectively.
R / S = {t | t in dom(T), ∀s ε S(∃r ε R such that r = st)}.
In term of basic RA operations, R / S = π_R-S(R) - π_R-S((π_R-S(R) x S) - R))

Example:

Consider:

Supplier(SNum, SName, SCity, Status)
Part(PNum, PName, Color, Weight)
Supply(SNum, PNum, Quantity)

An example of relation instance:

Supplier:

SNum	SName	SCity	Status
S1	ABC	Dallas	10
S2	DEF	Houston	20
S3	Go go	Houston	12
S4	P&G	Dallas	2
S5	Yap	Phoenix	5
S6	Yue	Dallas	1

Part:

PNum	PName	Color	Weight
P1	Drum	Green	10
P2	Hammer	Green	20
P3	Minipod	Red	4
P4	Micropod	Red	4
P5	Blue Spur	Blue	3
P6	Musical Box	Blue	13
P7	Bear	Blue	9
P8	Panda	White	10

Supply:

SNum	PNum	Quantity
S1	P1	10
S1	P2	3
S2	P1	11
S2	P2	1
S2	P4	6
S3	P4	1
S3	P5	2
S3	P6	12
S3	P7	5
S4	P2	1
S4	P5	10
S4	P7	4
S4	P8	10
S5	P1	11
S5	P3	5
S5	P4	10
S5	P5	14

π _SNUM(SUPPLIER):

SNum

π_SNUM(SUPPLY):

SNum

σ _{STATUS >= 10} (SUPPLIER):

SNum	SName	SCity	Status
S1	ABC	Dallas	10
S2	DEF	Houston	20
S3	Go go	Houston	12

(π _SNUM (SUPPLIER)) * (σ_{STATUS >= 10} (SUPPLIER)):

L.SNum	R.SNum	SName	SCity	Status
S1	S1	ABC	Dallas	10
S1	S2	DEF	Houston	20
S1	S3	Go go	Houston	12
S2	S1	ABC	Dallas	10
S2	S2	DEF	Houston	20
S2	S3	Go go	Houston	12
S3	S1	ABC	Dallas	10
S3	S2	DEF	Houston	20
S3	S3	Go go	Houston	12
S4	S1	ABC	Dallas	10
S4	S2	DEF	Houston	20
S4	S3	Go go	Houston	12
S5	S1	ABC	Dallas	10
S5	S2	DEF	Houston	20
S5	S3	Go go	Houston	12
S6	S1	ABC	Dallas	10
S6	S2	DEF	Houston	20
S6	S3	Go go	Houston	12

(π _SNUM (SUPPLIER)) |X| (σ _{STATUS >= 10}(SUPPLIER)):

SNum	SName	SCity	Status
S1	ABC	Dallas	10
S2	DEF	Houston	20
S3	Go go	Houston	12

π _{SNUM, PNUM} (SUPPLY)):

SNum	PNum
S1	P1
S1	P2
S2	P1
S2	P2
S2	P4
S3	P4
S3	P5
S3	P6
S3	P7
S4	P2
S4	P5
S4	P7
S4	P8
S5	P1
S5	P3
S5	P4
S5	P5

π _PNUM (σ _{COLOR = ‘Green’}(PART)):

PNum

π _{SNUM, PNUM} (SUPPLY)) / π _PNUM (σ _{COLOR = ‘Green’} (PART)):

SNum

Division in basic operations:

(project [snum] (supply))
minus
(project [snum]
(((project [snum] (supply))
times
(project [pnum] (select [snum='S1'] (supply))))
minus
(project [snum, pnum] (supply))));

Exercise:

Work on some of the query questions listed in the Supply database example.

4. Epilog

Some shortcomings of Relational Algebra:

Cannot navigate tuples.
Cannot deal with recursion.
1. e.g. for the relation Employee(SSN, Supervisor_SSN, ...), find all supervisors (direct or indirect).
2. May extend to logical database, e.g. Datalog.
No group functions or other functions that take a set of multiple rows as an argument.
1. e.g. Show the available total quantities of all parts.
Operation too simple, resulting in long sequences.