Relational algebra

Relational algebra is a procedural language which specifies the operations to be performed on existing relations to get the desired relations. Relational algebra constitutes a basic set of operations for manipulating relational data. These operations enable the user to perform some basic retrieval operations. The result of the retrieval operation on a relation is another relation. The relational operations can be divided into two categories:

1)    Set oriented operations which include mathematical set operations like UNION, INTERSECTION, SET DIFFERENCE and CARTESIAN PRODUCT first these operations require union compatible operand relations. Two relations are union compatible when they have same arty and one to one corresponding attributes defined over the same domain.

2)    These operations are developed and specifically for relational database which include SELECT, PRODUCT, RENAME, JOIN and DIVION.

a.     Selection (σ): The select operation is used to select a subset of the tuples from a relation that satisfies a selection condition or search criteria. The resulting relation and the main (original) relation have the same attributes. It discards the words that do not match the selection condition. For example: to select that employee from EMPLOYEE relation who is having empno 1007, we will write

σ(empno =1007) (EMP)

b.     Projection (σ): whereas the select operations is used to choose a sunset a tuples of relation the PROJECT operation is used to choose only a subset of columns discarding others. More simply, project operations on relations are defined as the projection of all tuples over same set of attributes. The output of this operation is a new relation which will have the attributes only listed in attribute list from relation R. For example, if we have to take only the employee names and their salaries from EMPLOYEE relation then this can be represented as

σ(ename,sal)(EMP)

c.      Natural join: This operation takes only two source relations as inputs and produces a relation whose tuples are formed by concatenating tuples from each input source. It is basically a Cartesian product of the extension of each input source. However, not all possible combinations of tuples necessarily end up in the result. This is because it implicitly selects from among all possible tuple combinations only those that have identical values in attributes shared by both relations. If the degree of the input sources were m and n, and the number of shared attributes was s, then the degree of the resultant relation is m+n-s.

d.     Division: It involves dividing one relation by another. Division is in principle a partitioning operation. Thus, 6/2 can be paraphrased as partitioning a single group of 6 into a number of groups of 2, which in this case, 3 groups of 2. Relational division too can leave remainders but we ignore remainders and focus only on constructing whole groups of the divisor.

The manner in which a relational dividend is partitioned is a little more complex. A division of a relation R is performed over some attribute of R. The divisor is a subset of values from that attribute domain and the result is a relation comprising the remaining attributes of R. In relational algebra expressions, the divisor is in fact specified by another relation D. For this to be meaningful at all, D must have at least one attribute in common with the R. The division is over the common attribute and the set of values used as the actual divisor are the values found in D.  

e.     Set operations: relations are basically sets. A set operation on two or more sets is meaningful if the sets comprise values of the same type. This is so that comparison of values from different sets is meaningful. As a relation is a set of tuples, the values we are interested in are the tuples themselves. The structure of the tuples must be identical, the tuples must be of equal length and their corresponding elements must be of the same type. Only then can two tuples be equal. Thus, meaningful set operations on relations require that the source relations have identical schemas.

The following are the operations:

1.     Union: Union operator gives the information available in both the relations used union. For example, give the complete list of employees who are either working as full time or part time. Consider full_time and part_time two seperates tables which store the employees separately.

Ʌ(ename) (Full_time) U  Ʌ(ename)(Part_time)

2.     Intersection: it returns the common information from both the relations used in the intersection. The intersection operation takes the form

For example: display the name of the employees who are working as full time as well as part time.

Ʌ(ename) (Full_time)  Ʌ(ename) (part_time)

3.     Difference: it returns the information, which is available in first relation but not in second. The difference operation takes the form

Relation1-Relation2

Example: to display only those employee names who are working as full time, not as part time.

Ʌ(ename) (full_time)- Ʌ(ename)(part_time)