Relational Algebra in DBMS

Introduction and Basic Operations

1. What is Relational Algebra?

Relational Algebra is a formal query language used in Database Management Systems (DBMS) to perform operations on relational tables. It provides a set of operations that take one or more relations (tables) as input and produce a new relation as output.

It is a procedural language, which means it tells the system what operations to perform and in what order to get the desired result.

Relational Algebra forms the theoretical foundation of SQL. While SQL is used practically, relational algebra defines the logical operations behind it.

2. Why Do We Need Relational Algebra?

Relational Algebra is important because:

  • It provides a mathematical foundation for relational databases.
  • It helps in understanding how queries are executed internally.
  • It is used by the query optimizer in DBMS.
  • It ensures that every operation produces a valid relation.

All operations in relational algebra follow the concept of closure property — meaning the output of every operation is also a relation (table).

3. Basic Concepts Before Learning Operations

Before studying operations, you should understand:

  • Relation: A table in a database.
  • Tuple: A row in a table.
  • Attribute: A column in a table.
  • Degree: Number of attributes in a relation.
  • Cardinality: Number of tuples in a relation.

Example:

Student Table

RollNo

Name

Dept

1

Aditi

CSE

2

Rahul

ECE

Here:

  • Degree = 3 (RollNo, Name, Dept)
  • Cardinality = 2 (two rows)

4. Types of Operations in Relational Algebra

Relational Algebra operations are divided into two categories:

1. Unary Operations

These operate on a single relation.

  • Selection (σ)
  • Projection (π)
  • Rename (ρ)

2. Binary Operations

These operate on two relations.

  • Union (∪)
  • Set Difference (−)
  • Intersection (∩)
  • Cartesian Product (×)

In this article, we will focus on the basic unary operations and fundamental set operations.

5. Unary Operations

5.1 Selection (σ)

Selection is used to select specific rows from a relation based on a condition.

Notation:

σ condition (Relation)

Example:

σ Dept = "CSE" (Student)

This selects only those students who belong to the CSE department.

Important Points:

  • It filters rows.
  • It does not change the number of columns.
  • Degree remains the same.
  • Cardinality may decrease.

5.2 Projection (π)

Projection is used to select specific columns from a relation.

Notation:

π attribute1, attribute2 (Relation)

Example:

π Name, Dept (Student)

This selects only the Name and Dept columns.

Important Points:

  • It removes unwanted columns.
  • It may remove duplicate rows.
  • Degree decreases.
  • Cardinality may decrease (if duplicates are removed).

5.3 Rename (ρ)

The rename operation is used to rename a relation or its attributes.

Notation:

ρ NewRelationName (Relation)

Example:

ρ Stud (Student)

This renames the Student relation to Stud.

It is useful when:

  • Performing self-join
  • Avoiding ambiguity
  • Improving readability

6. Set Operations

Relational algebra supports set operations similar to mathematics.

For these operations, relations must be union-compatible:

  • Same number of attributes
  • Same domain of attributes
  • Same order of attributes

6.1 Union (∪)

Union combines tuples from two relations.

Notation:

R ∪ S

It includes all tuples present in either R or S.

Important:

  • Duplicate tuples are removed.
  • Relations must be union-compatible.

6.2 Set Difference (−)

Set Difference returns tuples that are in one relation but not in the other.

Notation:

R − S

It returns tuples that are in R but not in S.

6.3 Intersection (∩)

Intersection returns common tuples between two relations.

Notation:

R ∩ S

It includes only those tuples that are present in both R and S.

6.4 Cartesian Product (×)

Cartesian Product combines every tuple of one relation with every tuple of another relation.

Notation:

R × S

If:

  • R has m tuples
  • S has n tuples

Then R × S will have m × n tuples.

It is mainly used as a base operation for joins.

Summary

  • Relational Algebra is a procedural query language.
  • It forms the mathematical foundation of relational databases.
  • It follows the closure property.
  • Unary operations: Selection, Projection, Rename.
  • Binary operations: Union, Difference, Intersection, Cartesian Product.
  • Set operations require union compatibility.