⚙️

Relational Model & Relational Algebra

The math beneath SQL — relations in, relations out. Select, Project, Union, Join and Divide, how they chain, and how query optimizers rewrite them.

σ Select · π Project ∪ Union ⋈ Join ÷ Divide

Press Next → or use ← → arrow keys

Section 01

The Story Behind the Relational Model

📖 A BIT OF HISTORY

1970 — Codd throws away the pointers

In 1970, E. F. Codd proposed storing data in plain tables instead of tangled pointer chains. That became the relational model — the foundation of every modern database. Its query engine is relational algebra: operations that take tables in and give tables out.

🔗

The Closure Property

A relation is a table; relational algebra is a set of operations where every input is a relation and every output is a relation — so results can feed into more operations, like links in a chain.

Section 02

Anatomy of a Relation

Formal Term	Everyday Term	Meaning
Relation	Table	A named set of rows with fixed columns
Tuple	Row / record	One entry in the relation
Attribute	Column / field	A named property
Domain	Data type / allowed values	The legal values for an attribute
Degree	Number of columns	STUDENT has degree 4
Cardinality	Number of rows	STUDENT has cardinality 4

🎲

A Relation Is a Set

Because it's a set, no two tuples are identical and order doesn't matter. This one fact explains why projection and union silently drop duplicates.

Section 03

Schema, Instance & Keys

📄

Schema — the design

The permanent structure: STUDENT(Roll, Name, Age, Dept). Changes rarely.

📋

Instance — the data

The actual tuples right now: {101 Raj 22 CSE, 102 Sara 19 ECE, …}. Changes with every insert/update/delete.

🔑

Keys

Super ⊇ Candidate ⊇ Primary. A foreign key references another relation's PK — ENROLLED.Roll → STUDENT.Roll.

Key Type	Definition	Example
Super Key	Any unique attribute set (extras allowed)	{Roll}, {Roll, Name}
Candidate Key	Minimal super key	{Roll}, {Email}
Primary Key	The chosen candidate key	Roll
Foreign Key	References another relation's PK	ENROLLED.Roll → STUDENT.Roll

Section 04

Relational Algebra — The Operators

It's procedural: you specify the operations and their order. Every operator consumes one or two relations and returns a relation.

Operation	Symbol	Type	Meaning
Selection	σ	Unary	Pick rows by a condition
Projection	π	Unary	Pick columns
Union	∪	Binary / set	All rows of either relation
Intersection	∩	Binary / set	Rows in both relations
Difference	−	Binary / set	Rows in one but not the other
Cartesian Product	×	Binary	All combinations of rows
Join	⋈	Binary	Combine related rows
Rename	ρ	Unary	Rename relation / attributes
Division	÷	Binary	Rows matching all of another set

Section 05

Selection (σ) — Pick the Rows

σ_{Age > 20} ( STUDENT ) → slices horizontally

SELECT * FROM STUDENT WHERE Age > 20;   -- SQL equivalent

Section 06

Projection (π) — Pick the Columns

π_Dept ( STUDENT ) → slices vertically, then drops duplicates

SELECT DISTINCT Dept FROM STUDENT;   -- DISTINCT mimics the set behaviour

Section 07

Union (∪), Intersection (∩) & Difference (−)

∪ Union

Raj, Sara, Amit — everyone in either.

∩ Intersection

Sara only — in both relations.

− Difference

Cricket − Chess = Raj — first but not second.

⚖️

Union-Compatibility Required

All three need the same number of attributes, in the same order, with matching domains — you can't union a 2-column relation with a 3-column one.

Section 08

Join (⋈) — Combine Related Rows

STUDENT ⋈ ENROLLED → match on the common attribute Roll

Natural Join

Matches all common attributes; keeps one copy of the shared column.

Equi Join

Joins on an equality condition (θ is =).

Theta Join

Joins on any condition (<, >, ≠, …).

Section 09

Division (÷) — The "For All" Operator

ENROLLED ÷ COURSE → "which students took every course?"

✅

The Logic

Roll 101 has {C1, C2} ✓ · Roll 102 has only {C1}, missing C2 ✗ · Roll 103 has {C1, C2} ✓. Division answers "for all / every" questions — the one operator SQL has no direct keyword for.

Section 10

Putting It Together — Nesting Operations

"Names of CSE students older than 20 who are enrolled in something."

π_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';

Section 11 · A & B

Precedence & Two Ways to Write a Query

Priority	Operators
Highest	σ π ρ
Then	× ⋈
Then	∩
Lowest	∪ −

Parentheses override precedence and clarify intent.

// nested (compact)
π_Name( σ_Age>20(STUDENT) ⋈ ENROLLED )

// sequential (readable)
T1 ← σ_Age>20(STUDENT)
T2 ← T1 ⋈ ENROLLED
Result ← π_Name(T2)

🟰

Same Result, Two Styles

Nest it for short queries; assign step-by-step temporaries for readable ones. Both compute identical relations.

Section 11 · C

Equivalence Rules — How Optimizers Rewrite Queries

Rule	Equivalence	Benefit
Cascade of σ	σ_c1∧c2(R) ≡ σ_c1(σ_c2(R))	Split / merge conditions
Commute σ	σ_c1(σ_c2(R)) ≡ σ_c2(σ_c1(R))	Apply the cheaper filter first
Cascade of π	π_L1(π_L2(R)) ≡ π_L1(R) if L1 ⊆ L2	Drop redundant projections
Product + σ = Join	σ_θ(R × S) ≡ R ⋈_θ S	Convert slow product to join
Selection push-down	σ_c(R ⋈ S) ≡ σ_c(R) ⋈ S	Filter before joining
Commute ⋈ / ∪	R ⋈ S ≡ S ⋈ R	Reorder for a better plan

⚡

The Golden Optimization

Selection push-down — filter early, shrinking the number of rows before an expensive join. This single idea drives much of real query optimization.

Section 11 · D

Worked Example — Combine Two Branches

"Names who are either in ECE, OR over 20 and enrolled."

π_Name(σ_Dept='ECE'(STUDENT)) ∪ π_Name(σ_Age>20(STUDENT) ⋈ ENROLLED)

Branch 1 — ECE students

σ_Dept='ECE' → {Sara, Neha} → π_Name → {Sara, Neha}

Branch 2 — older & enrolled

σ_Age>20 → {Raj, Amit}, join ENROLLED, π_Name → {Raj, Amit}

🔀

Final Union

{ Sara, Neha, Raj, Amit } — the union stacks both branches and drops any duplicate name automatically.

Section 12 · Cheat Sheet

Relational Algebra ↔ SQL

Operation	Algebra	SQL
Selection	σ_Age>20(R)	`WHERE Age > 20`
Projection	π_Name(R)	`SELECT DISTINCT Name`
Union	R ∪ S	`... UNION ...`
Intersection	R ∩ S	`INTERSECT`
Difference	R − S	`EXCEPT / MINUS`
Join	R ⋈ S	`JOIN ... ON / NATURAL JOIN`
Division	R ÷ S	`GROUP BY … HAVING COUNT` / double `NOT EXISTS`

🔁

The Irony

SQL's keyword SELECT actually performs projection (π), not selection (σ). The algebra's "selection" is SQL's WHERE.

Section 13

Three Common Mistakes to Avoid

↔️

Selection vs Projection

σ keeps rows (horizontal); π keeps columns (vertical). Ironically, SQL's SELECT does projection.

👥

Projection drops duplicates

Algebra treats relations as sets, so π silently removes repeats. In SQL you must add DISTINCT.

⚖️

Union without compatibility

Can't union a 2-column relation with a 3-column one, or mix domains. Same arity, same order, matching domains.

🧭

Remember the Shapes

σ is a horizontal slice, π is a vertical slice — and both outputs are sets, so duplicates never survive.

Section 14

Golden Rules of Relational Algebra

🏆 NON-NEGOTIABLE RULES

1

Every operator takes relations and returns a relation. This closure is what enables nesting.

2

σ works on rows, π works on columns. Selection is horizontal; projection is vertical.

3

Results are sets, so duplicates vanish. Projection and union eliminate repeated tuples automatically.

4

Set operations need union-compatibility. Union, intersection and difference require identical structure.

5

Join needs a common attribute; division answers "for all." Combine with ⋈; use ÷ for "every" questions.

6

Relational algebra is SQL's blueprint. Understanding it makes SQL easier to reason about and to optimize.

FINAL

The Math Beneath SQL

σ πRows & columns

∪ ∩ −Set operations

⋈Join

÷"For all"

1970Codd's model

🎯

The Foundation Is Set

Relations in, relations out — that closure lets you chain Select, Project, Union, Join and Divide into any query, and lets optimizers rewrite them with equivalence rules. Every SQL statement you'll ever write is this algebra in friendlier clothing. Next: SQL itself, and beyond it, normalization.

🧠

One Sentence to Remember

σ slices rows, π slices columns, ⋈ stitches tables on a shared value, ÷ answers "for all" — and every result is another relation you can feed back in.

⚙️ End of tutorial · Press ← to review, or click Restart