Relational operations

Relational operations

Every database management system must define a query language to allow users to access the data stored in the database. 

Relational Algebra is a procedural query language used to query the database tables to access data in different ways.

The primary operations that we can perform using relational algebra are:

1. Select
2. Project
3. Union
4. Set Different
5. Cartesian product
6. Rename

1. Select Operation (σ)

This is used to fetch rows(tuples) from table(relation) which satisfies a given condition.

Syntax: σ_p(r)

Where,

 σ represents the Select Predicate, 

r is the name of relation(table name in which you want to look for data), 

and p is the prepositional logic, where we specify the conditions that must be satisfied by the data.

example: σ_{age > 17} (Student), operation fetch data for students table with age more than 17.

σ_{age > 17 and gender = 'Male'} (Student), operation fetch data from table Student with information of male students, of age more than 17.

Example:

2. Project Operation (∏)

Project operation is used to project only a certain set of attributes of a relation. 

It will only project or show the columns or attributes asked for, and will also remove duplicate data from the columns.

Syntax: ∏_{A1, A2...}(r)

where A1, A2 etc are attribute names(column names).

For example, ∏_{Name, Age}(Student),  show only the Name and Age columns for all the rows of data in Student table.

Example:

3. Union Operation (∪)

This operation is used to fetch data from two relations(tables) or temporary relation(result of another operation). For this operation to work, the relations(tables) specified should be union compatible that is they should have same number of attributes(columns) and same attribute domain. The duplicate tuples are automatically eliminated from the result.

Syntax: A ∪ B 

where A and B are relations.

For example, 

∏_Student(RegularClass) ∪ ∏_Student(ExtraClass)

gives the name of Students who are attending both regular classes and extra classes, eliminating repetition.

Example:

4. Set Difference (-)

This operation is used to find data present in one relation and not present in the second relation. For this operation to work, the relations(tables) specified should be union compatible that is they should have same number of attributes(columns) and same attribute domain. 

Syntax: A - B

where A and B are relations.

For example, if we want to find name of students who attend the regular class but not the extra class, then, we can use the below operation:

∏_Student(RegularClass) - ∏_Student(ExtraClass)

Example:

5. Cartesian Product (X)

This operation is used to combine data from two different relations(tables) into one and fetch data from the combined relation.

Syntax: A X B

where A and B are relations.

For example, to find the information for Regular Class and Extra Class which are conducted during morning, the operation:

σ_{time = 'morning'} (RegularClass X ExtraClass)

note: both RegularClass and ExtraClass should have the attribute time.

Example:

6. Rename Operation (ρ)

This operation is used to rename the output relation for any query operation which returns result like Select, Project etc. Or to simply rename a relation(table)

Syntax: ρ(RelationNew, RelationOld)

example:  ρ(Student,ClassStudent)