Unit 2: Relational Algebra and Relational Calculus

Relational algebra:

Relational Algebra is a procedural language that is performed on one or more existing relation to derive result relations without changing the original relation and can be used to tell the DBMS how to build a new relation from one or more relations in the database. So, it defines a complete scheme for each of  the result relations.

While using the relational algebra, user has to specify what is required and what are the procedures or steps to obtain the required output

The relational algebra is a theoretical language with operations that work on one or more relations to define another relation without changing the original relation(s). Thus, both the operands and the results are relations, and so the output from one operation can become the input to another operation. This allows expressions to be nested in the relational algebra, just as we can nest arithmetic operations. This property is called closure: relations are closed under the algebra, just as numbers are closed under arithmetic operations.

There are many variations of the operations that are included in relational algebra. Codd originally proposed eight operations, but several others have been developed. The five fundamental operations in relational algebra, Selection, Projection, Cartesian product, Union and Difference, perform most of the data retrieval operations, which can be expressed in terms of the five basic operations.

In relational algebra each operation takes one or more relations as its operand(s) and another relation as its result. Consider an example of mathematical algebra as shown below

                                                                  3+5=8

 Here, 3 and 5 are operands and + is an arithmetic operator which gives 8 as the result.

Similarly, in relational algebra, RI + R2 = R3

Here RI, R2 are relations (operands) and + is relational operator which gives R3 as a resultant relation.

We also can say that the relational algebra is a relation-at-a-time language in which all tuples from several relations are manipulated in one statement without looping . Relational operators are put under two categories.

1.       Common Set Operations

2.      Native Relational Operators

Let us tabulate common Set Operations

S.N.	Operations	Symbol	Keyboard Symbol	Example
1.	Union	U	UNION	R1 U R2
2.	Intersection	∩	INTERSECTION	R1 ∩ R2
3.	Difference	−	MINUS	R1 − R2
4.	Product	X	PRODUCT	R1 * R2

These all operations make use of the fact that tables are essentially sets of rows. Let us explain these operations with an example.

UNION:

Let us consider two relations(Table) R1 and R2 with the following tuples at some instance of time.

R1                                                                             

CName	CStatus
Ram	Good
Rahul	Excellent

R2

CName	CStatus
Kiran	Bad
Ram	Good

Then R1 U R2 yields

CName	CStatus
Ram	Good
Rahul	Excellent
Kiran	Bad

In order to perform UNION Operation on two relations, both operand and relations must be union-compatible which means that they must have same number of columns drawn from the same domain ( i.e. must be of same data type)

INTERSECTION:
An intersect operator is used to get common record(s) from the two relations.For example let us consider the two relations given above, i.e. R1 and R2. 
Then R1∩R2 yields

CName	CStatus
Ram	Good

This Operation is just like AND Function.