Relational Model & Relational Algebra: Select, Project, Join, Divide

The Story Behind the Relational Model

Structure of a Relational Database

A relation (table) is built from a few precise parts. Learn their formal names once and every textbook definition becomes clear.

Schema, Instance & Keys

Two more ideas complete the structure. The schema is the permanent design; the instance is the data in it right now.

A key guarantees each tuple can be uniquely identified. The hierarchy in brief:

Relational Algebra — The Query Engine

Relational algebra is a procedural query language: you specify the operations and their order. Every operator consumes one or two relations and produces a new relation — a property called closure, which lets you nest operations freely.

The example tables used throughout the rest of this tutorial:

Selection ( σ ) — Pick the Rows

Selection keeps only the tuples that satisfy a condition — it slices the table horizontally. Written as σ_condition(R). The structure of the table stays the same; only matching rows survive.

-- SQL equivalent of selection: the WHERE clause
SELECT * FROM STUDENT WHERE Age > 20;

Projection ( π ) — Pick the Columns

Projection keeps only the listed columns — it slices the table vertically. Written as π_columns(R). Crucially, because a relation is a set, projection removes duplicate rows from the result.

-- Projection = choosing columns; DISTINCT mirrors duplicate removal
SELECT DISTINCT Dept FROM STUDENT;
SELECT DISTINCT Name, Dept FROM STUDENT;   -- π Name, Dept (STUDENT)

Union ( ∪ ) — Combine the Rows

Union stacks all tuples of two relations into one, removing duplicates. It works only if the two relations are union-compatible: the same number of attributes, in the same order, with matching domains.

-- UNION removes duplicates; UNION ALL keeps them
SELECT Name FROM CRICKET
UNION
SELECT Name FROM CHESS;

Join ( ⋈ ) — Combine Related Rows

Join stitches together tuples from two relations that share a matching value. The natural join automatically matches on the common attribute (here, Roll) and merges the rows into one wider tuple.

-- Natural join matches on the common column automatically
SELECT * FROM STUDENT NATURAL JOIN ENROLLED;

-- Equivalent explicit (equi) join
SELECT * FROM STUDENT S JOIN ENROLLED E ON S.Roll = E.Roll;

Division ( ÷ ) — The "For All" Operator

Division takes ENROLLED(Roll, CID) ÷ COURSE(CID) and returns the Roll values that are paired with every CID present in COURSE.

-- Division via GROUP BY / HAVING: enrolled in as many courses as exist
SELECT Roll FROM ENROLLED
WHERE CID IN (SELECT CID FROM COURSE)
GROUP BY Roll
HAVING COUNT(DISTINCT CID) = (SELECT COUNT(*) FROM COURSE);

Putting It Together — Nesting Operations

Closure lets us chain operators into one expression. Suppose we want the names of CSE students older than 20 who are enrolled in something:

As a single relational-algebra expression and its SQL twin:

-- Relational algebra:
-- π Name ( σ Age>20 ∧ Dept='CSE' ( STUDENT ⋈ ENROLLED ) )

SELECT DISTINCT S.Name
FROM STUDENT S JOIN ENROLLED E ON S.Roll = E.Roll
WHERE S.Age > 20 AND S.Dept = 'CSE';

Combining Operators — Rules & a Worked Example

Because every operator returns a relation, you can chain them into one query. But chaining safely needs two kinds of rules: precedence (which operator binds first) and equivalence rules (legal ways to rearrange a combined expression).

A. Operator Precedence

When parentheses are absent, operators apply in this order. Unary operators bind tightest; union and difference are loosest.

B. Two Ways to Write a Combined Query

The same query can be one nested expression, or a sequence of steps using assignment (←) to name intermediate relations. Both compute identical results — pick whichever reads more clearly.

C. The Combine (Equivalence) Rules

These laws let you rewrite a combined expression without changing its result. They are the foundation of query optimization — the engine reshuffles operators to run faster.

D. A Worked Example Using Many Operators

Using ENROLLED = {(101,C1), (101,C2), (103,C1)}, here is the full combined expression:

-- Relational algebra (nested):
--   π Name ( σ Dept='ECE' (STUDENT) )
--   ∪
--   π Name ( σ Age>20 (STUDENT) ⋈ ENROLLED )

Step by step, with the example data:

-- The same combined query in SQL
SELECT Name FROM STUDENT WHERE Dept = 'ECE'
UNION
SELECT DISTINCT S.Name
FROM STUDENT S JOIN ENROLLED E ON S.Roll = E.Roll
WHERE S.Age > 20;

Relational Algebra ↔ SQL Cheat Sheet

Common Mistakes to Avoid

Golden Rules of Relational Algebra