Relational algebra is procedural query language used to query the database in various ways. In other words, Relational Algebra is a formal language for the relational mode. A data model must also include a set of operations to manipulate, retrieve the data in the database, in addition to defining the database structure and constructs.

The relational algebra operation enables a user to specify basic retrieval requests for data from the database.

The results of retrieval are a new relation, which may have been formed from one or more relations. Sequences of relational algebra operators form a relational algebra expression, whose result is a new relation that represents the result of a database query (retrieval query).

Relational algebra provides the foundation of relational model operation and it’s used as the basic for implementing and optimising queries in RDBMS.

Fundamental Operations –

Fundamental operations on relational algebra are as below –

• Select operation
• Project operation
• Union operation
• Set difference operation
• Cartesian product operation
• Rename operation

The project, rename and select operations are called unary operations because they operate on one relation. Operations such as Union, Set Difference and Cartesian product operate on two relations. Therefore, they are called binary operations.

Note – We are going to use below relation tables to show different dbms relational algebra examples.

Player relation

Deposit relation

Borrower relation

R-Schema(id, name)

R – Relation

S-Schema(id, name)

S – Relation

(1) Select Operation (σ)

The select operation selects the tuples (rows) that satisfy the given predicate (condition). The lower case Greek letter Sigma (σ) is used to represent the select operation.

The predicate appears as a subscript to σ and argument relation is given in parenthesis following σ. Predicates can be defined using the operators =, !=, <=, <, >, >= etc. and they may be connected by using the connectives.

Notation of Selection Operation

Where,
σ is predicate,
r stands for relation (name of the table).
p is the prepositional logic.

Exercises –

Question A. Find all tuples from player relation for which country is India.

Question B. Select all the tuples for which runs are greater than or equal to 15000.

Question C. Select all the players whose runs are greater than or equal to 6000 and age is less than 25

(2) Project Operation (π)

Projection of a relation P (P-Schema) on the set of attributes Y is the projection of each tuple of the relation P on the set of attributes Y.

The projection operation is a unary operation and it returns its argument relation with certain attributes left out. It is denoted by a Greek letter pi (π). The attributes, which appear in the result, are listed as a subscript to π.

Notation of Project Operation

Where,
A1, A2, An are attribute name of the relation r.

Exercises –

a. List all the countries in Player relation.

b. List all the team ids and countries in Player Relation

(3) Union Operation ( ∪ )

Compatible relations: Two relations R and S are said to be compatible relations if they satisfy following two conditions –

1. The relations R and S are of same entity i.e. the number of attributes are same.
2. The domains of the ith attribute of R and ith attribute of S must be same for all i.

The union of R and S is set theoretic union of R and S, if R and S are compatible relations.

It is denoted by ∪, the resultant relation P(P=R ∪ S) has tuples drawn from R and S such that a tuple in P is either in R or S or in both of them.

Notation of Union Operation

Where,
R and S are relations.

Example – 1: P = R ∪ S is given by relation

(4) Set Difference Operation (-)

The set difference operation removes common tuples from the first relation. It is denoted by ‘-‘ sign. The expression R-S results in a relation containing those tuples in R but not in S. For set difference operation, relations must be compatible relations.

Notation of Set Difference Operation

Where,
R and S are relations.

Example –

A. P = R – S is given by

Exercises –

A. Find all the customers having an account but not the loan.

B. Find all the customers having a load but not the account.

(5) Cartesian Product Operation ( X )

Cartesian product of two relations is the concatenation of tuples belonging to the two relations. It is denoted by ‘x’ sign. If R and S are two relations, (R X S) results in a new relation P, which contains all possible combination of tuples in R and S. For Cartesian product operation, compatible relations are not required.

Notation of Cartesian Product Operation

Where,
P, R and S are relations.

X represents concatenations. The degree/arity of the resultant relation is given by

|P|=|R|=|S|

Example

Employee-Schema = { Emp-id, Name }

Project-Schema = { Proj-name }

Find R = Employee X Project

Solution –

R-Schema = {Emp-id, Name, Proj-name}

If the attribute name is same in both argument relations, then that is distinguished by attaching the name of the relation from which the attribute originally came.

Exercise

Given
Customer schema = {cust-id, name}
Customer

Employees Schema = {emp-id, name}
Employee

Find R = Customer X Employee

6.Rename Operation

This is a unary operation. Any relational algebra expression returns a new relation, but this relation is not having a name associated with it. Using Rename operation, we can rename such result relations or if we want to change the name of a given relation, it can be changed using rename operation.

It is denoted by rho (ρ)

Notation of Rename Operation

ρ(NewName, OldName)

Where,

NewName – New name of the relation.
OldName – Old name of the relation.

Example –

Question – Rename Player relation to PlayerList.

Solution – ρ(PlayerList, Player).

Exercise

Question 1. Rename Customer relation to CustomerList.

Thus, we have gone through different dbms relational algebra examples.