Relational Algebra – What is Relational Algebra?

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=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.

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

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

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

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

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.

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:

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.