Relational Algebra and Six Basic Operation in DBMS

 What is Relational Algebra in DBMS?

Relational Algebra is a procedural query language. Relational algebra mainly provides a theoretical foundation for relational databases and SQL. The main purpose of using Relational Algebra is to define operators that transform one or more input relations into an output relation. Given that these operators accept relations as input and produce relations as output, they can be combined and used to express potentially complex queries that transform potentially many input relations (whose data are stored in the database) into a single output relation (the query results). As it is pure mathematics, there is no use of English Keywords in Relational Algebra and operators are represented using symbols.

Relational algebra refers to a procedural query language that takes relation instances as input and returns relation instances as output. It performs queries with the help of operators. A binary or unary operator can be used. They take in relations as input and produce relations as output. Recursive relational algebra is applied to a relationship, and intermediate outcomes are also considered relations.

Six Relational Algebra Operations

1. Select Operation (or σ):

It is used to select required tuples of the relations.

The notation is − σp(r)

Here σ stands for the selection predicate while r stands for the relation. p refers to the prepositional logic formula that may use connectors such as or, and, and not. Also, these terms may make use of relational operators such as − =, ≠, ≥, < , >, ≤.

Example:

σsubject = “information”(Novels)

The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’.

σsubject = “information” and cost = “150”(Novels)

The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’ and the ‘price’ is 150.

σsubject = “information” and cost = “150” or year > “2024”(Novels)

The output would be − Selecting tuples from the novels wherever the subject happens to be ‘information’ and the ‘price’ is 150 or those novels have been published after 2024.

2. Project Operation (or ∏):

It projects those column(s) that satisfy any given predicate.

Here B1, B2, and An refer to the attribute names of the relation r.

The notation is − ∏B1, B2, Bn (r)

Remember that duplicate rows are eliminated automatically since the relation is a set.

Example:

∏subject, writer (Novels)

The output would be − Selecting and projecting columns named as writer as well as the subject from the relation Novels.

3. Union Operation (or ∪):

It would perform a binary union between two relations.

The notation is − r U s

It is defined as follows:

r ∪ s = { t | t ∈ r or t ∈ s}

Here r and s either refer to DB relations or the relation result set (or temporary relation).

Example:

∏ writer (Novels) ∪ ∏ writer (Articles)

The output would be − Projecting the names of those writers who might have written either an article or a novel or both.

4. Set Different Operation (or −)

Tuples refers to the result of the set difference operation. These are present in just one of the relations but not at all in the second one.

The notation is − r − s

Finding all the tuples present in r and not present in s.

Example:

∏ writer (Novels) − ∏ writer (Articles)

The output would be − Providing the writer names who might have written novels but have not written articles.

5. Cartesian Product Operation (or Χ):

It helps in combining data and info of two differing relations into a single one.

The notation is − r Χ s

Where s and r refer to the relations. Their outputs would be defined as follows:

s Χ r = { t ∈ s and q t | q ∈ r}

Example:

σwriter = ‘mahesh'(Novels Χ Articles)

The output would be − Yielding a relation that shows all the articles and novels written by mahesh.

6. Rename Operation (or ρ):

Rename is a unary operation used for renaming attributes of a relation. Relations are the results of relational algebra but without any name. Thus, the rename operation would allow us to rename the relation output. The ‘rename’ operation is basically denoted by the small Greek letter ρ or rho.

The notation is − ρ x (E)

Here the result of the E expression is saved with the name of x.

Example:

ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.