Relational Algebra – Core Concepts in the Relational Model

Relational Algebra is a formal system and a procedural query language used to retrieve data from relational databases. It takes relations (tables) as input and produces new relations as output. Each operation is applied to one or more relations to produce another relation without changing the original.

Let’s look at the most important operations:

1. SELECT (σ) – Filtering Rows

The SELECT operation is used to retrieve rows (tuples) that satisfy a certain condition.

Notation:
σ_condition(Relation)

Example:
σ_Age > 20(Student) retrieves all students older than 20.

Purpose: Acts like a “WHERE” clause in SQL.

2. PROJECT (π) – Selecting Columns

The PROJECT operation is used to choose specific attributes (columns) from a relation.

Notation:
π_Attribute1, Attribute2(Relation)

Example:
π_Name, Department(Student) returns only the Name and Department columns from the Student table.

Purpose: Eliminates unwanted columns and duplicates.

3. UNION ( ∪ ) – Combining Tuples

The UNION operation combines tuples from two relations and removes duplicates.

Notation:
Relation1 ∪ Relation2

Conditions:

• Both relations must have same number of attributes.
• Corresponding attributes must have compatible data types.

Example:
Student2023 ∪ Student2024 returns all unique students from both years.

4. SET DIFFERENCE ( − ) – Finding Non-Matching Tuples

The SET DIFFERENCE operation returns tuples that are in one relation but not in the other.

Notation:
Relation1 − Relation2

Example:
Enrolled − Graduated returns students who are enrolled but not yet graduated.

5. CARTESIAN PRODUCT ( × ) – Pairing All Tuples

The CARTESIAN PRODUCT operation combines each tuple of the first relation with every tuple of the second.

Notation:
Relation1 × Relation2

Example:
If Student has 3 tuples and Course has 2, then Student × Course gives 6 tuples.

Used in: Creating the base for JOIN operations.

6. JOIN (⨝) – Combining Related Tuples

The JOIN operation connects two relations based on a common attribute.

Notation:
Relation1 ⨝_Condition Relation2

Example:
Student ⨝_Student.DeptID = Department.DeptID Department joins both relations on DeptID.

Variants:

• Theta Join – uses any condition.
• Equi Join – uses equality condition.
• Natural Join – automatically joins using same-named attributes.

7. RENAME (ρ) – Renaming Relations or Attributes

The RENAME operation is used to rename the result of a relational algebra expression.

Notation:
ρ_NewName(Relation)
or
ρ_NewName(A1, A2, ...)(Relation)

Example:
ρ_StudentInfo(Student) renames the Student relation to StudentInfo.