Relational Algebra

Introduction

Relational Algebra was developed before the SQL. It is a procedural query language. Relational algebra received little attention outside of pure mathematics until the publication of E.F.Codd’s relational model of data in 1970. Codd proposed such an algebra as a basis for database query languages. 

There a five primitive operators in Codd’s algebra.

1. selection

2. projection

3. Cartesian product

4. set union

5. set difference

Relational algebra is divided into several groups:

Unary Relational Operation

1. SELECT,PROJECT,RENAME

Relational oparetions algebra form Set Theory

1. UNION,INTERSECTION,MINUS,CARTESIAN PRODUCT

Binary Relational Operation

1. JOIN,DEVISION
   

Unary Relational Operation

Select operation(σ)

This operation is used to select a subset of tuples in a relation which satisfies a selection condition. This produces horizontal subset of table.

Syntax:

   σ <condition> (R)

Example:

Select all the details of the students in class

σ ClassID=C003 (Student)

Project Operation(π)

This operation selects specified columns in the table and delete other columns. This produces vertical subsets of table and remove duplicate tuples

Syntax:

π <attribute list> (R)

Example:

Select ID and Name column from the Student table

σ ID,Name  (Student)

Rename Operation

This operation allows us to rename output relations

notation:

ρs(R)

Union Operation(∪)

This performs binary union between two given relations.

notation:

 R ∪ S 

Example:

Select ID and Name of all the students and ClassID and ClassName of the all classes.

Student ∪ Class

This is same as:

SELECT Student.*,Class.* FROM Student,Class;

Intersection Operation(⋂)

This defines a relation consisting of the set of all the tuples that are both in R and S .

notation:

 R ⋂ S 

Example:

Select all the student that have cars.

Student ⋂ CarOwn

This is same as:

SELECT Student.*,CarOwn.* FROM Student,CarOwn where Student.ID=CarOwn.ID;

Minus or Set Difference Operation

This defines as R – S. This is a relation that includes all the tuples that are in R but not in S .

notation:

 R - S 

Example:

Select all the student that don’t have cars.

Student – CarOwn

This is same as:

SELECT* FROM Student where Student.ID not in (CarOwn.ID);

Cartesian Product Operation

This defines a relation that is the concatenation of all the tuples of R and all the tuples of S.

notation:

 R × S