Basic and additional operations of Relational Algebra

In this article, we are going to learn about relational algebra and its basic and additional operations.
Submitted by Mahak Jain, on November 06, 2018

Definition

Every DBMS must define a query language to enable users to access the data which is stored in the database. Relational Algebra is a procedural query language. It is used to query the database tables in order to access data in several ways. In this algebra, the input is a relation and output is also a relation. Operators are used to performing queries. It can be either unary or binary operator.

Basic Operations

SELECT (unary)

PROJECT (unary)

UNION (binary)

SET DIFFERENCE (binary)

CARTESIAN PRODUCT (binary)

RENAME (unary)

Other Operations

SET INTERSECTION

NATURAL JOIN

DIVISION

ASSIGNMENT

1) Select Operation (σ)

This is used to get rows (tuples) from the table (relation) which fulfill a given condition.

Syntax: σp(r)

Where σ is used for the Select Predicate, r is the name of relation or table, and p is used as the propositional logic, where we give the conditions that must be fulfilled by the data. In this, unary and binary operators like =, etc., to mention the condition can be used.

Example: Student table and get data for students with age more than 20.

σage> 20 (Student)

2) Project Operation (∏)

Project operation is used to project or show only a desired set of attributes of a relation. In other words, if we wish to see only the names all of the students in the Student table, then the project operation can be used. It will only project the columns or attributes chosen and will delete duplicate data from the columns.

Syntax: ∏A1, A2...(r)

Here A1, A2 etc. are attribute names (name of columns).

Example:

∏Name, Age(Student)

3) Union Operation (∪)

This is used to fetch data from two (relations)tables or temporary relations (the result of other operation).

For union operation to work, the condition is that the relations(tables) taken should have an equal number of attributes(columns) and equal attribute domain. Even the duplicate tuples are automatically removed from the result.

Syntax: P ∪ Q

Where P and Q are relations.

Example: if we have two tables Regular class and Extra class, both have an attribute student to save the name of the student, then,

∏Student(Regular class) ∪ ∏Student(Extra class)

4) Set Difference (-)

This operation is used to find data which present in one relation but not present in the other relation. This operation can be applied to two relations, just like the Union operation.

Syntax: P - Q

Where P and Q are relations.

Example: if we want to find the name of students who take the regular class but miss the extra class, then, the below operation can be used as:

∏Student(Regularclass) - ∏Student(Extraclass)

5) Cartesian Product (X)

This operation is used to combine data of two separate relations into single and fetch data from the combined result relation.

Syntax: A X B

Example: to find the information for Regular Class and Extra Class which are conducted in the morning, then, the following operation can be used:

σtime = 'morning' (Regularclass X Extraclass)

6) Rename Operation (ρ)

Rename operation is used to change the name or rename the output relation for any query operation which returns or gives the result like Select, Project etc. Or in simple words to rename a relation(table).