by Dinesh Thakur Category: DBMS & RDBMS

**Relation Algebra** is a procedural query language for RDBMS (Relational Database Management System). An Algebra based on the set of operators (like Arithmetic operator, union, intersection relational operator, etc.) and operand. Edgar F. Codd created it for a relational database. Relational Algebra provides a fundamental query for retrieving data from databases. The retrieved result from algebra expression is a relation which formed from one or more relations. Relational Algebra specifies many operations to manipulate relations.

Procedural Query Language is language in which user tells the system to perform a specific operation to obtain the desired result.

SQL Query language used by users in these days based on the concept of relational algebra and relational Calculus. In these days, most Commercial RDBMSs are not providing us the platform or user interface for using Relational algebra query language. Relational algebra considered as an integral part of the relational query. SQL query is parsed into Relational algebra expression through parser than into executable code.

Algebra provides us a great range of operations to perform on relations. Relational Algebra function can divide into two parts, Basic set operations, and Special Relational Operations. Relational algebra used operand, operators, rules in algebraic expressions.

**Basic set operations**: Union, Intersection, Difference and Cartesian product.

**Special Set Operations**: Join, Selection, Projection, and Division

**Let’s us discuss all the operation one by one**

**Selection Operation**: Selection command is used to fetch some tuples from relation after satisfying the condition. Selection command only used to fetch tuples; we can’t fetch attribute using this Selection command. Command gives use two set of tuples as input, first who satisfy the condition and second who not satisfy the condition. Selection command of relational algebra and Select command of SQL are considered to be same as their functioning. ’**σ**’ Sigma symbol is used to represent selection command in Relational Algebra.

**Syntax**: σ(Condition) (Relation Name)

Let us consider a table Employee, having four columns.

Eid | Emp_Name | City | Salary |

1 | Pooja Somani | Abohar | 30000 |

2 | Rahul Verma | Fazlika | 35000 |

3 | Sonali | Delhi | 50000 |

4 | Ashman | Chandigarh | 35000 |

σ EID=1001 (EMPLOYEE)

Command will return the information of employee id of 1001 from the relation Employee.

σ (Salary=35000 AND City=’Delhi’) (EMPLOYEE)

Command will return employee name whose salary is equal to 35000 and city = Delhi.

AND, OR and NOT are used in condition to check one or more condition at the same time. They are the logical operator. We can also use relation operator to compare values.

**For instance**:

σ ((Condition1) AND (Conditions 2)) (Relation) This command will return output if both conditions are true at the same time.

σ ((Condition1) OR (Conditions2)) (Relation) This command will return output either one of the conditions is true or both conditions are true at the same time.

σ (Not (Condition1)) (Relation)

**For example:**

σ (NotSalary = 35000) (EMPLOYEE)

Command will show those tuples as a result whose Salary not equal to 35000.

**Relational Operators**

σ (Not Salary > 35000 OR Salary < 50000) (EMPLOYEE)

**Projection**: Projection command is used to access no of attributes from the relation. It access number of attributes according to the condition. Fetching columns and tuples is always less than or equal to the number of columns and tuples in a relation.

Syntax: π (Column_name1, Column_name2,……………………., Column_nameN ) (Relation)

In SQL, We write the same syntax like -

Select Column_Name1,Column_Name2,………………………..,Column_NameN from Relation;

Π (Eid, Emp_Name, Salary) (Employee)

Command fetch column Eid, Emp_Name, Salary from relation Employee.

π (Eid,Emp_Name,Salary) ( σ (Salary >= 35000) (Employee))

**The above command show this attributes and tuples in the new relation.**

Eid | Emp_Name | Salary |

2 | Rahul Verma | 35000 |

3 | Sonali | 50000 |

4 | Ashman | 35000 |

**Basic Set Operations**: Relational Algebra has five primitive operators. These are the selection, the projection, the Cartesian product, the set union, and the Set differences. Set Union, Set Difference, and Cartesian product operators taken from mathematical set theory. There are some unary and binary operators in relational algebra.

For performing set union and set difference operations, the two relations involved must be union-compatible. The two relations must have the same set of attributes.

**Student Table**

Roll No | Student_Name | City | Fees |

1 | Pooja Somani | Abohar | 30000 |

5 | Rohit | Barnala | 35000 |

6 | Sonal | Delhi | 50000 |

7 | Kamal Rajput | Jaipur | 35000 |

**Union Operations**: Union operation is standard mathematical OPERATIONS on set. The union is binary operation because to apply union operation we must require minimum two relations. Union operation combines the tuples of two relations or more relations. To combine the tuples of relations, set of attributes and their data type must be same. Duplicate values eliminated during union process of relation. For more understanding of this point, suppose both relations Employee and Student having the same record as 1, Pooja Somani, Abohar, 30000 then it will use for one time.

**Syntax**: Relation1 υ Relaion2 υ Relation3 ………..

For Instance, In Relational Algebra

Denoted by Employee υ Student (Employee and Student relation both have the same Number of attributes and same data type)

**In SQL**

Select * from Employee union Select * From Student;

**Intersection**: Intersection is also a binary operation. It is used to find the common record (tuples) from two or more relations. For intersection compatible, both relations must have the same set of attributes and have same data type according to attribute. ‘∩’ is used to denote intersection operation in relational algebra.

For Instance, In Relational Algebra

Denoted by- R ∩ S

**In SQL**

Select * from Employee intersect Select * From Student;

Suppose both relations Employee and Student having the same record as

1 | Pooja Somani | Abohar | 30000 |

then the command will return this tuple as output.

Set Difference-The result of this operation is a relation that includes all tuples that are in R but not in S.

R-S denotes it.

i.e., common tuples from both table eliminated and only content of relation R will write about output.

**In SQL **

Select * from Employee minus Select * from Student;

**For instance**: Employee – Student

It will return the relation

Eid | Emp_Name | Salary |

2 | Rahul Verma | 35000 |

3 | Sonali | 50000 |

4 | Ashman | 35000 |

If I write the same command like Student – Employee, then the output is

Roll No | Student_Name | City | Fees |

5 | Rohit | Barnala | 35000 |

6 | Sonal | Delhi | 50000 |

7 | Kamal Rajput | Jaipur | 35000 |

Both UNION and INTERSECTION held both *commutative* and *associative property*; that is

Commutative Property

R υ S = S υ R and R ∩ S = S ∩ R

Associative Property

*R *υ (*S **υ T*) = (*R *υ*S*) υ *T *and (*R *∩*S*) ∩ *T *= *R *∩ (*S *∩* T*)

Set Difference (MINUS) operation is *not commutative*

R-S=/S-R (Sir, I tried to put the sign here, but it's not going on .So please check it)

, Which we proved in above example.

**Cartesian product Operation**: Cartesian product is also the binary operator. It is also known as a cross product or cross join. But Cartesian product not required compatibility with other table means not required the same number of columns and same data type. Resulted table is large because each tuple of one relation gets to merge with each tuple of other relation. Suppose Relation R has N tuples and Relation S has M tuples then resulted table will contain N * M tuples.

**Syntax**: It is denoted by

R x S

In SQL, Cartesian product written as

Select * from Employee, Student;

**Join Operation**: Where Cartesian product resulted into N*M relation. Join is used combine tuples from two relations after processing condition is known Theta Join.

Syntax-R?_{Condition }S

In Relational Algebra

R ? _{Condition }S= σ _{Condition(R x S)}

In SQL,

Select * from Employee, Student where Employee.Eid > Student.RollNo;

**Resulted output**

First Cartesian product formed after that condition is applied. According to above Condition, First Cartesian product operator applied, then a match for the condition whose Employee Eid >Student Roll no.

Eid | Emp_Name | City | Salary | RollNo | Student_Name | City | Fees |

2 | Rahul Verma | Fazlika | 35000 | 1 | Pooja Somani | Abohar | 30000 |

3 | Sonali | Delhi | 50000 | 1 | Pooja Somani | Abohar | 30000 |

4 | Ashman | Chandigarh | 35000 | 1 | Pooja Somani | Abohar | 30000 |

**Equi-Join Operator**: In Equi-join operator, only ‘= ‘operator used in the condition. The resultant relation always has all tuples from both relations that satisfy the condition.

**In Relational Algebra**

R ? (Eid = RollNo) S= σ(Eid = RollNo)(R x S)

**In SQL**,

Select * from Employee, Student where Employee.Eid =Student.RollNo;

**Resulted output**:

Eid | Emp_Name | City | Salary | RollNo | Student_Name | City | Fees |

1 | Pooja Somani | Abohar | 30000 | 1 | Pooja Somani | Abohar | 30000 |

**Natural Join Operator**: It is same as equijoin. In a natural join, column name should be same in both relations so that duplicacy of columns can remove. Natural join can be an inner join, left outer join or right outer join. By default, join is an inner join.

About Dinesh Thakur

Dinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.

Search Content

Popular Article

#### What is DBMS? Advantages and Disadvantages of DBMS.

#### What are Strong and Weak Entity Sets in DBMS

#### Database Normalization

#### What are the Components of DBMS?

#### What is ER-Model?Advantages and Disadvantages of E-R Model.

#### What is Data Independence of DBMS?

#### Traditional File Processing System

#### What are Data Models? Type of Data Models.

#### Advantages of Database

#### Type of Functional Dependence (FD)

#### What are the Difference Between DDL, DML and DCL Commands?

#### What is a Database Architecture

#### What is a Database Instance

#### What are INTANCES, SCHEMAS AND SUBSCHEMA in DBMS?

#### Database Languages

#### Database Approach

#### Relational Model

#### Differentiate between DBMS and RDBMS.Types of DBMS.

#### Entity Relationship Diagram

#### What are the Functions and Service of DBMS

#### Network Model

#### What is Difference Between Relation and Relational Schema?

#### What is the role of DBA, Data Manager, File Manager, and Disk Manager?

#### Database Model

#### What is Metadata OR Data Dictionary?

#### Type of Database System

#### What is a Database Server

#### what is a Database Concurrency Control

#### What is DBA?

#### What is a Database Users

#### What are the Problems with E-R Model?

#### Database System Structure

#### E-R NOTATION

#### Database Management System

#### What is a Database Schema

#### What is the Basic Construction of E-R Modeling?

#### What is a Database View

#### What is the Procedure for Database Access?

#### What is a Database Object

#### What is attributes?

#### What is Database?

#### What is Data Recovery?

#### What is dBase?

#### What is Database Engine?

#### What is relational database? - Definition

#### Relational Algebra - What is Relational Algebra?

Basic Courses

Advance Courses