DBMS Relational Algebra Examples With Solutions

Greetings!
We have recently published 100+ articles on android tutorials with kotlin and java. If you need, you may visit Android Tutorial for beginners page. You can also check Kotlin Tutorial for beginners. Also, if you are interested in content writing, you can mail us at tutorialwing@gmail.com.
In this tutorial, we will learn about dbms relational algebra examples. We will go through fundamental operations such as – Select operation, Project operation, Union operation, Set difference operation, Cartesian product operation and Rename operation. Also, we will see different dbms relational algebra examples on such operation.

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

Player Id Team Id Country Age Runs Wickets
1001 101 India 25 10000 300
1004 101 India 28 20000 200
1006 101 India 22 15000 150
1005 101 India 21 12000 400
1008 101 India 22 15000 150
1009 103 England 24 6000 90
1010 104 Australia 35 1300 0
1011 104 Australia 29 3530 10
1012 105 Pakistan 28 1421 166
1014 105 Pakistan 21 3599 205

Deposit relation

Acc. No. Cust-name
A 231 Rahul
A 432 Omkar
R 321 Sachin
S 231 Raj
T 239 Sumit

Borrower relation

Loan No. Cust-name
P-3261 Sachin
Q-6934 Raj
S-4321 Ramesh
T-6281 Anil

Id Name
101 Raj
102 Rahul
103 Sachin
104 Anil

Id Name
101 Raj
104 Anil
106 Kapil
107 Sumit

(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
σp(r)

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
πA1, A2, An(r)

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
P = R ∪ S

Where,
R and S are relations.

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

Id Name
101 Raj
102 Rahul
103 Sachin
104 Anil
106 Kapil
107 Sumit

(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
P = R – S

Where,
R and S are relations.

Example –

A. P = R – S is given by

Id Name
106 Kapil
107 Sumit

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
P = R X S

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 }

Emp-Id Name
101 Sachin
103 Rahul
104 Omkar
106 Sumit
107 Ashish

Project-Schema = { Proj-name }

Proj-name
DBMS 1
DBMS 2

Find R = Employee X Project

Solution –

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

Emp-Id Name Proj-name
101 Sachin DBMS 1
101 Sachin DBMS 2
103 Rahul DBMS 1
103 Rahul DBMS 2
104 Omkar DBMS 1
104 Omkar DBMS 2
106 Sumit DBMS 1
106 Sumit DBMS 2
107 Amit DBMS 1
107 Amit DBMS 2

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

Cust-Id Name
101 Sachin
102 Rahul
103 Ramesh

Employees Schema = {emp-id, name}
Employee

Emp-Id Name
201 Omkar
202 Sumit
203 Ashish

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.