Relational Algebra & It's Operations

Tuesday, April 1, 2008 by Ankit Goyal

RELATIONAL ALGEBRA

Relational algebra is a collection of operations used to manipulate relations (tables). These operations enable the users to specify the retrieval requests which results in a new relation built from one or more relations.

Relational Algebra is a Procedural language, which specifies, the operations to be performed on the existing relations to derive result relations. It is a procedural language which means that user has to specify what is required and what is the sequence of steps performed on the database to obtain the required output. Whenever the operations are performed on the existing relations to produce new relations then the original relations(s) are not effected i.e. they remain the same, and the resultant relation obtained can act as an input to some other operation, so relational algebra operations can be composed together into a relational algebra expression. Composing relational algebra operation into relational expression is similar to composing arithmetic operations (+, -, *) into arithmetic expressions. R1+R2 is a relational expression where R1 and R2 are relations.

It is important that the results of use of relational algebric operations on Relations (Tables) must themselves be a Relation (Tables). This is because these operators can be used sequentially in various combinations to obtain desired results. Thus each operation on completion must leave data as a relation (table) for the next operator to use. So, this property which all the above operators must have is referred to as Relational Closure.

Relational Algebra is a formal and non-user friendly language. It illustrates the basic operations required for any Data Manipulation languages but it is very less commonly used in the commercial languages because it lacks the syntactic details, although it acts as a fundamental technique for extracting data from the database.


Relational Algebric Operations

The Relational Algebric Operations can be divided into two groups.

1. Basic Set Oriented Operations or Traditional set operations – These are derived from Mathematical Set theory. They are applicable because each relation is defined to be set of Tuples. These include Union, Intersection, Difference, Cartesian Product. All of these operations are binary operations which means that operation applies to pair of Relations.

2. Special Relational operations - These include join, selection, projection and division. These operations were designed specifically for relational databases. These operations don't add only power to the algebra but simply for common queries that are lengthy to express using basic set oriented operations.

These operations were introduced by Dr. Codd. But these could not meet all the requirements, so some additional operations were introduced. These included aggregate functions like SUM, AVERAGE, COUNT, OUTER JOIN etc.