## What is Relational Algebra?

Relational Algebra is a compilation of applications to manipulate or access relations. It is a procedural (or abstract) language with applications that is executed on additionally current relations to derive outcome (another) relations without modifying the initial relation(s). Furthermore, relational algebra represents the complete schema for each of the outcome relations.

Relational algebra includes a set of relational operators. Each operator has unnecessary relations as its input and generates a relation as its output. So, both the operands and the outcome are connections, and so the output from one operation can develop into the input to another operation.

A fundamental expression in relational algebra includes the following:

• A relation in the database
• A constant relation

The expression is composed of small sub-expressions. For instance, let E_1 and E_2be relational-algebra expressions. Then, the following are also expressions:

1. E1?E2
2. E1-E2
3. E1 X E2
4. sP (E1)where P is a predicate on the attribute in E1.
5. ?S (E1)where S is a series involving some of the attributes in E1.
6. ?x (E1)where x is the new name for the result of E1.

A query in relational algebra will include the following operations.

## Types of Operations in Relational Algebra

Following are the two types of operations in relational algebra

1. Basic/Fundamental Operations
2. Derived Operations

Let us explain these operations one by one with the support of the following examples.

## SELECT Operation (s)

The SELECT operation is used to choose a subset of the rows from a relationship that satisfies a selected condition.

## Syntax of SELECT Operation (s)

s< Selection Condition >(R)

Where s(sigma) is the symbol for SELECT operation.

< Selection Condition >is a Boolean expression described on the attributes of relation R.

In the above syntax, R is a relation or name of a table, and the condition is a propositional logic which helps the relational operators like <,>,=,=,=,?.

Example: Consider the following schema

Employee (Eno, Ename, ECity, EStreet, Salary, Dno)

Department (Dno, Dname, DCity, TotalSal)

Where the Total-Sal is the total salary of all employees in a department.

## Employee

Eno Ename ECity EStreet Salary Dno
001 Rohan Faridabad Sec-16 42,000 01
002 Sumit Noida Sec-15 35,000 02
003 Neha Delhi Sec-4 28,000 01
004 Gaurav Gurgaon Sec-11 50,000 03
005 Raman Faridabad Sec-8 46,000 02

## Department

Dno DName DCity TotalSal
01 Marketing Delhi 70,000
02 inance Noida 81,000

Query1: Get information on those employees who are drawing a salary of more than 40,000.

sSalary>40000 (Employee)

The resultant relation will be:

Eno Ename ECity EStreet Salary Dno
001 Rohan Faridabad Sec-16 42,000 01
004 Gaurav Gurgaon Sec-11 50,000 03
005 Raman Faridabad Sec-8 46,000 02

Query2: Get information on those employees who are living in Noida.

sEcity=”Noida” (Employee)

The resultant relation will be:

Eno Ename ECity EStreet Salary Dno
002 Sumit Noida Sec-15 35,000 02

## PROJECT Operation (?)

The PROJECT operation is used to select a subset of the attributes of relation by defining the name of the necessary attributes. Any matching tuples in the result are automatically deleted. It can also be used to modify the order of attributes in a relation.

## Syntax of PROJECT Operation (?)

?< attribute list >(R)

Where ? is the symbol for project operation, < attribute list > is a list of attributes from the relation R.

## Example

Consider the STUDENT relation

111 Mohit Maths A 15
112 Garima History B 16
113 Aayush Science A+ 14
114 Sheena English C 12

Query1: Get a list of all the student names and student subjects.

?NAME,SUBJECT(STUDENT)

The resultant relation will be:

NAME SUBJECT
Mohit Maths
Garima History
Aayush Science
Sheena English

## Query2: Get a list of all the student ID and student age.

?SID,AGE(STUDENT)

The resultant relation will be:

SID AGE
111 15
112 16
113 14
114 12

## Union Operation(?)

UNION operator is represented by ? symbol, and it is used to choose all the rows (tuples) from two tables (relations). Matching tuples are automatically deleted from the result.

Earlier taking it union between two relations, provides that both the union must be consistent, which involve that:

1. Both the relation must be of a similar degree, i.e., they must have the same number of
attributes.
2. For all k, the domain of the kth attribute of one relation must be similar to the domain of the kth attribute of another relation.

## Syntax of UNION Operation(?)

A1?A2

In the above syntax, A1 is the name of the first relation or table, and A2 is the name of the second relation or table.

Example: Consider the following schema

DEPOSIT (Cust_Name, Account_No)

LOAN (Cust_Name, Loan_No)

## DEPOSIT

Cust_Name Account_No
Mayank A-101
Hardik A-103
Himanshu A-105
Ashutosh A-106

## LOAN

Cust_Name Loan_No
Rahu L-101
Hardik L-103
Himanshu L-105

Query: Get the names of those customers, who have either an account or loan in a bank

?Cust-Name (Deposit)??Cust-Name (Loan)

Cust_Name
Mayank
Hardik
Himanshu
Ashutosh
Rahul

## SET DIFFERENCE Operation(-)

The SET DIFFERENCE operation is a-b, which is between two relations ‘a’ and ‘b’ that generates a relation with the tuples which are there in ‘a’ but not there in ‘b’.

## Syntax of SET DIFFERENCE Operation(-)

A-B

In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

Example: Consider the following schema

DEPOSIT (Cust_Name, Account_No)

LOAN (Cust_Name, Loan_No)

## DEPOSIT

Cust_Name Account_No
Mayank A-101
Hardik A-103
Himanshu A-105
Ashutosh A-106

## LOAN

Cust_Name Loan_No
Rahu L-101
Hardik L-103
Himanshu L-105

Query: Get the names of those customers, who have an account in a bank, but do not have a loan.

?Cust-Name (Deposit) -?Cust-Name (Loan)

The resultant relation will be:

Cust_Name
Mayank
Ashutosh
Rahul

## CARTESIAN PRODUCT Operation (X)

CARTESIAN PRODUCT is represented by (X) symbol. The Cartesian product of two tables merges each row in one table with each row in the other table.

The Cartesian product of two relations a and b is expressed as a x b. The resultant relation will be an on the schema that will be a concatenation of the two schemas A and B, expressed as (A, B). For each tuple ta?a and each tuple tb?b,there will be a tuple t in axb, such that t[A]=tA and t[B]=tB.

Let say, relation a has p tuples, and the relation b has q tuples, then the resultant relation will have p*q tuples.

## Syntax of CARTESIAN PRODUCT (X)

A x B

Example: Consider the following schema

EMPLOYEE (Eno, Ename, Salary, Dno)

DEPARTMENT (Dno, Dname)

## EMPLOYEE

Eno Ename Salary Dno
101 Rahul 15,000 1
102 Rohit 20,000 4
103 Rohan 18,000 5

## DEPARTMENT

Dno Dname
1 Marketing
3 Sales
5 Legal

Query: Let’s find the Cartesian product of the table EMPLOYEE and DEPARTMENT.

EMPLOYEE X DEPARTMENT

The resultant relation will be:

Eno Ename Salary Dno Dno Dname
101 Rahul 15,000 1 1 Marketing
101 Rahul 15,000 1 3 Sales
101 Rahul 15,000 1 5 Legal
102 Rohit 20,000 4 1 Marketing
102 Rohit 20,000 4 3 sales
102 Rohit 20,000 4 5 Legal
103 Rohan 18,000 5 1 Marketing
103 Rohan 18,000 5 3 sales
103 Rohan 18,000 5 15 Legal

## RENAME Operation (?)

A RENAME is a unary operation written as ?_(a/b)(R) where the result is exact to R, except that the b field in all tuples is renamed to a field.

## Syntax of RENAME Operation (?)

? (new_relation_name, old_relation_name)

## Example

Consider the STUDENT relation

111 Mohit Maths A 15
112 Garima History B 16
113 Aayush Science A+ 14
114 Sheena English C 12

Query: Get a list of all the student names.

?NAME, ?NAME(STUDENT)

The resultant relation will be:

NAME
Mohit
Garima
Aayush
Sheena

## SET INTERSECTION Operation (n)

The intersection operator is denoted by the (n) symbol, which is used to show all the rows of frequent attributes from two relations.

## Syntax of SET INTERSECTION Operation (n)

AnB

In the above syntax, A is the name of the first relation or table, and B is the name of the second relation or table.

Example: Consider the following schema

DEPOSIT (Cust_Name, Account_No)

LOAN (Cust_Name, Loan_No)

## DEPOSIT

Cust_Name Account_No
Mayank A-101
Hardik A-103
Himanshu A-105
Ashutosh A-106

## LOAN

Cust_Name Loan_No
Rahu L-101
Hardik L-103
Himanshu L-105

Query: Get the names of those customers, who have an account as well as loan in a bank

?Cust-Name (Deposit) n ?Cust-Name (Loan)

The resultant relation will be:

Cust_Name
Hardik
Himanshu

## DIVISION Operation (/)

The DIVISION is a binary operation that is composed of A/B. The outcome includes the constraints of rows in A to the attribute names exclusive to A, i.e., in the header of A, but not in the header of B, for which it influences that all their mixtures with rows in B are demonstration in A.

## Syntax of DIVISION Operation (/)

A/B

Example: Consider the following Banking Schema

## Branch

Branch-Name Branch-City
Sec-18, HDFC Noida
South Delhi Delhi
West Delhi Delhi

## Account

Account-No Amount Branch-Name
101 20,000 Sec-18, HDFC
102 25,000 South Delhi
103 30,000 West Delhi

## Depositor

Customer-Name Account-No
Vikram 101
Rohit 102
Rohit 103

Query: Find the names of all customers who have accounts in all branches of Delhi.

A1?? Branch-Name(s_(Branch-City=”Delhi” (Branch))
A2??Customer-Name,Branch-Name (Depositor C Account)

Result:A2/A1

Branch-Name
South Delhi
West Delhi

## A2

Customer-Name Branch-Name
Vikram Sec-62, HDFC
Rohit South Delhi
Rohit West Delhi

The result relation will be:

 Rohit

## ASSIGNMENT Operation (?)

Sometimes, it is useful to write a relational algebra expression by split it into sub-expressions and authorize the intermediate outcomes to temporary relation-variables. The ASSIGNMENT operation is represented by (?) designate the outcome of a relational-algebra sub-expression to a provisional relation-variable. The relation-variable can be used in subsequent expressions, equal to any permanent relation. Assignment to a permanent relation cause affects its modification.

### Joins Operation(?)

A join operation is denoted by ? (Bowtie) Symbol. Joins are the merger of related rows from two different relations or tables into an individual type. It is associated with a cartesian product.

Apply now for Advanced DBMS Course