Relational Algebra in DBMS

Introduction

In Computer Science, every Programming language is based on some theoretical statements which are then written in the form of the logical syntax of the programming Language. Thus, the languages used for querying the data stored in the Database management System are also based on statements that are formed using some theoretical basis. Relational Algebra is the basis of database language which includes all the operations performed while querying the data in the database.

The database forms an important part of a Software Development Life Cycle. Therefore, the language used to manage the data in the database must be based on some reliable concepts. Relational Algebra in DBMS provides a robust concept for Relational Databases as a Formal Query Language.

Relational algebra is a procedural query language for databases, used to manipulate and query data stored in relational databases. This means that Relational Algebra includes two things, first is ‘what to do' and second is ‘how to do.’

The Queries of Relational Algebra in DBMS are executed using some operators. The operators that operate on the relations are called relational operators. Each of these operators takes one or more relations as input and produces a new relation as output. The diagram below shows the types of Relational Algebra Operations in DBMS.

The Various Operations of Relational Algebra

There are various operations of relational algebra which are as follows:

Selection

The selection operation is used to select certain tuples from a relation. The selection is based on a condition that is specified using a predicate. The condition can be any expression that evaluates to either true or false. It is denoted by sigma (σ).

Example Input:

Example Output:

Projection

The projection operation is used to select certain attributes from a relation. In other words, it is used to project the required column from the table. It is denoted by ∏.

Example Input:

Example Output:

Union

Union Operations of Relational Algebra in DBMS  returns all the tuples that are either in the first or second relation or in both relations. It is denoted by ∪. When union operation is performed, duplicate tuples are removed automatically. The condition for Union Operations is that the relations must have the same set of attributes.

Example Input:

Example Output:

Difference

The difference operation is used to select tuples that are present in one relation and not present in the other relation. It is denoted as R-S where R and S are relations (tables in the database).

Example Input:

Example Output:

Cross Product

This Operation is used to combine each row of one relation(table) with each row of another table. It is written as A X B, where A and B are relations.

Example Input:

Example Output:

Rename

This Operation is used to rename a relation in the database. It is denoted by rho(ρ) and written as ρ(Relation1← Relation) where Relation is the old name and ‘Relation1’ is the new name.

Example Input:

Example Output:

Join

The join operation is used to combine two relations or tables.  In Relational Algebra in DBMS,  Join Operations are of two types- Inner Join and Outer Join. Inner Join is further classified as Equi Join, Theta Join, and Natural Join. Outer Join is also divided into Left Outer Join, Right Outer Join, Full Outer Join, etc.

Example Input:

Example Output:

Intersection

The intersection operation is used to select tuples that are common to two relations. It is denoted by ∩.

Example Input:

Example Output:

The Importance of Relational Algebra in DBMS

Relational algebra is a fundamental concept in database management systems (DBMS). It is a language that helps us to express the operations that we can perform on the data stored in a database. In other words, relational algebra is a tool that provides us with a way to manipulate the data in relations in a systematic way.

Relational algebra in DBMS is important because it provides a precise and declarative way to express queries. In addition, Relational algebra can be used to optimize queries. For example, the order of the operators in a query can be changed to minimize the number of relations that need to be accessed. Therefore, it is important for both query optimization and precise query expression.