s to manipulate relations
using the relational database model
. Since instances of a relation
, or relation states
, consist of sets of n-tuples
, relational algebra
consists of two kinds of operations: set operations
and relational operations
. Set operations used in relational algebra are the same as those found in set theory
, I will detail only relational operations here.
Select -- indicated by a lower-case sigma.
This operation selects rows (instances of a relation) based on a predicate (condition). The format of the select operator is as follows:
σ <predicate> (<relation>)
Select returns a list of tuples that have the same attribute as the relation on which the select was performed.
Project -- indicated by pi.
This operation selects columns of a table (attributes) with no condition to be met. The format for the project operator is as follows:
π <attribute1, attribute2, attribute n> (<relation>)
Join -- indicated by the bowtie symbol. Why a bowtie? We may never know...
This operation combines tables related through a foreign key. This operation is equivalent to performing a select on the cross-product of tables but it's now combined into one handy-dandy operation for your querying pleasure. As for format:
<relation> (bowtie)<predicate> <relation>
A special kind of join is known as Equijoin, or a natural join. This operation is indicated by an asterisk, an joins tables over the equality of a foreign key of one tuple with the primary key of another. Format:
Division - Indicated by the division symbol. Since I'm still not sure myself what this thing actually does, I only know that it's necessary in some cases, I'll use the formal definition from my textbook here and let you figure things out (see the bottom of the writeup for the source):
In general, the division operation is applied to two relations, R(Z) / S(X), where X ⊂ Z. (X is a subset of Z, if this html character doesn't work on your browser!) Let Y = Z - X...that is, let Y be the set of attributes of R that are not attributes of S. The result of the division is a relation T(Y) that includes a tuple t if tuples tR appear in R with tR[Y] = t, and with tR[X] = tS for every tuple tS in S. This means that, for a tuple t to appear in the result T of the division, the values in t must appear in R in combination with every tuple in S.
Function : This operations performs some function on a relation (or joined relations for that matter) such as count:
<grouping attribute>(<function> <argument>) <relation>
The grouping attribute here is optional. If used, the function will return a set of relations: R(<grouping attribute>, <result>), otherwise R will contain only the result(s).
These operations in combination
with standard set operations allow us to implement complex database queries
and make life all the more interesting.
Source: Fundamentals of Database Systems by Elmasri and Navathe (there is a nice node on this text, you should check it out), and the skills and abilities I am acquiring as a result of Introduction to Database Systems at my University.