Note that I use rows as another word for tuples or entries, and columns for fields, attributes or whatever you like to call it.
Select - ?
The select operator simply selects certain rows based on a criterion. The criteria is written as a comma separated list in subscript after the function symbol, such as ? age > 40, and can be any valid combination of expressions (using the logical AND and OR for combination, and the comparison operators <, <=, =, ?, >= or >). The equivalent in SQL is the SELECT statement, where the criteria are specified in the WHERE part.
If we have an instance of a relation S1, and want all entries with a higher rating than 5, function and SQL would be as follows:
- ? rating > 5(S1)
- SELECT * FROM S1 WHERE rating > 5;
Project - ?
The projection operator is used to filter out the columns that are not needed. The columns we want is specified as subscript, such as ? age, gender. The SQL equivalent would be to specify the desired fields in a SELECT statement.
To get all rows' ratings and playtime fields out of a relation instance S1, function and SQL would be:
- ? rating, playtime(S1)
- SELECT rating, playtime FROM S1;
Set operations
- Union: R ? S returns a relation instance containing all rows that is either in R or in S, or both. Note that R and S must be union-compatible or the union is not logical to perform. This means they must have the same number of fields, and corresponding fields must have the same domain.
- Intersection: R ? S returns a relation instance containing all rows that is present in both R and S. The union-compatibility is a requirement here too.
- Difference: R - S returns a relation instance containing all the rows that are present in R and not present in S. Again, union-compatibility...
- Cross-product: R × S returns a relation instance where all fields in R is concatenated with all fields in S, so that all combinations of tuples in R and S are present.
E.g.: {r, s} ? R × S if r ? R and s ? S.
Note that the preceding set operations can be applied to the relation instances generated by relational algebra operations - for instance, creation a union of a projection of S1 with a selection of S2.
Joins
Joins is probably the most useful and used way of combining information from two or more relations. Although the join operations can be accomplished by the means of a cross-product and selects and projections, using the joins is beneficial since the resulting data set usually is much smaller than that of a cross-product, which can have a huge impact on performance.
It is therefore important to be familiar with the various join operations available (there is a few) to be able to avoid materializing the cross-product and optimize queries.
Note that the SQL supports cross-products the following way:
SELECT * FROM R CROSS JOIN S;
Or implicitly:
SELECT * FROM R, S;
Condition Joins
Condition join is defined to be a cross-product followed by a selection with condition c.
Condition join: R ?c S = ?c (R × S)
Note that c can and usually does refer to a field in both R and S, for example R.f1 < S.f1.
In SQL, a condition join is called an INNER JOIN, and can be used as follows:
SELECT * FROM R INNER JOIN S ON R.f1 < S.f1;
Or implicitly:
SELECT * FROM R, S WHERE R.f1 < S.f1;
Not that the optimizing logic of the database system will probably replace this with an inner join, but to be certain the most effective method is used, use the explicit inner join notation.
Equijoin
This special case of a condition join, where the condition is a series of equalities of the form R.f1 = S.f1 (connected by ?). There is some redundancy in retaining both fields in the resulting set, so the S.f1 field is projected out. Thus, equijoin is a condition join followed by a projection.
In SQL, the equijoin should be automatically identified when all conditions of an INNER JOIN is equalities.
Natural Join
This is really an equijoin without the need to specify which fields should be compared, as fields with same names are implicitly given. Thus, no conditions are needed.
Natural join: R ? S
In SQL, the following statement facilitates natural join:
SELECT * FROM R NATURAL JOIN S;
Conclusion
This was only a scratch in the surface of relation algebra. To fully facilitate the possibilities of this, you need to develop a full understanding of the operators, how to combine them efficiently, and nested expressions. The latter is especially useful for advanced queries that cannot be completed with a single straight-forward function or SQL statement.
