Relational Calculus & SQL Equivalence

Relational Calculus ও SQL-এর সাথে সম্পর্ক

Read: ~40 min Hard 10 practice problems Live SQL runner

1. Two Ways to Tell a Database What You Want

Relational algebra describes how to compute the answer: select these rows, project those columns, then join. It is procedural — a recipe.

Relational calculus describes what answer you want: “the set of all tuples t such that t belongs to Student and t.cgpa > 3.5.” It is declarative — a specification. And SQL? SQL is the world’s most successful descendant of relational calculus, with a few practical extensions tacked on.

Algebra বলে দেয় কীভাবে ফলাফল বের করতে হবে — অর্থাৎ recipe। Calculus বলে শুধু কী চাই — “ছাত্রদের সেট, যাদের CGPA 3.5-এর বেশি”। প্রথমটি procedural, দ্বিতীয়টি declarative। আজকের SQL মূলত relational calculus-এরই বাস্তবিক বংশধর — কিছু সংযোজন সহ।

Two flavours of calculus Codd defined two: Tuple Relational Calculus (TRC) — variables range over tuples (whole rows); and Domain Relational Calculus (DRC) — variables range over individual attribute values. They are equally expressive; SQL leans heavily toward TRC.

2. Tuple Relational Calculus (TRC)

A TRC expression has the shape:

{ t | P(t) }

“The set of tuples t such that predicate P(t) is true.” The predicate P uses:

Atoms: t ∈ R (t is in relation R), t.A op s.B (attribute compare), t.A op c (attribute vs constant).
Connectives: ∧ (and), ∨ (or), ¬ (not).
Quantifiers: ∀t (for all), ∃t (there exists).

TRC-তে variable একটি tuple (পুরো একটি row) প্রতিনিধিত্ব করে। অভিব্যক্তির রূপ: { t | P(t) } — অর্থাৎ এমন সব tuple t যাদের জন্য predicate P(t) সত্য। P-তে ∈, =, <, ∧, ∨, ¬, ∀, ∃ ব্যবহার করা যায়।

2.1 Worked example — “names of CSE students with CGPA > 3.5”

{ t.name | Student(t) ∧ t.dept = 'CSE' ∧ t.cgpa > 3.5 }

Equivalent algebra:

π_name(σ_{dept='CSE' ∧ cgpa > 3.5}(Student))

Equivalent SQL:

trc_to_sql.sql

SELECT name
FROM student
WHERE dept = 'CSE' AND cgpa > 3.5;

2.2 Worked example — TRC with ∃ quantifier

“Names of students who have taken at least one CSE course”:

{ t.name | Student(t) ∧ ∃e (Enroll(e) ∧ e.student_id = t.id ∧ e.course_id LIKE 'CSE%') }

trc_exists.sql

SELECT name
FROM student s
WHERE EXISTS (
    SELECT 1 FROM enroll e
    WHERE e.student_id = s.id
      AND e.course_id LIKE 'CSE%'
);

Note the parallel TRC’s ∃ becomes SQL’s EXISTS. TRC’s ∀ usually becomes the double-NOT EXISTS trick from Module 09 (¬∃¬). The mapping is mechanical.

3. Domain Relational Calculus (DRC)

DRC variables range over individual attribute values, not whole tuples. Form:

{ ⟨x₁, x₂, …, x_n⟩ | P(x₁, …, x_n) }

For our running example:

{ ⟨n⟩ | ∃ id, d, c (Student(id, n, d, c) ∧ d = 'CSE' ∧ c > 3.5) }

Notice: the variable n stands for a single value (the name); we existentially quantify over the other attributes. Same answer, different formal flavour.

DRC-তে variable একটি পুরো tuple না, একটি attribute-এর মান প্রতিনিধিত্ব করে। আকার: { ⟨x₁,…,xₙ⟩ | P(x₁,…,xₙ) }। উপরের উদাহরণে n মানে শুধু নাম, বাকি attribute-গুলো ∃ দিয়ে hide করা। ফলাফল TRC-র মতোই, কিন্তু নোটেশন আলাদা।

Aspect	TRC	DRC
Variable ranges over	Tuples (rows)	Attribute values
Predicate atoms	`t ∈ R`, `t.A op c`	`R(x₁,…,xₙ)`, `x op c`
Closer to SQL	✅ Very	❌ Less
Closer to QBE-style	—	✅ Microsoft Access’s “Query By Example” is essentially DRC.

4. Safe Expressions — Avoiding Infinite Answers

Calculus has a trap that algebra does not: it’s easy to write a perfectly legal expression whose answer is infinite. For example:

{ t | ¬Student(t) }

“All tuples that are not students.” But the universe of all possible tuples is infinite — every imaginable integer, every imaginable string. A real DBMS can’t enumerate them.

A calculus expression is called safe if its result is guaranteed to be finite. The standard rule: every variable in the formula must be bounded by membership in some finite relation appearing in the expression. SQL’s designers solved this by simply requiring a FROM clause — every column must come from a known table.

Calculus-এ একটি বিপদ আছে: এমন expression লিখে ফেলা সম্ভব যার answer অসীম — যেমন “সব tuple যা Student না”। বাস্তব DBMS এমন কিছু compute করতে পারবে না। নিরাপদ (safe) expression মানে যেগুলোর answer অবশ্যই finite। SQL-এ এই সমস্যা সমাধান হয়েছে FROM clause বাধ্যতামূলক করে — প্রতিটি column অবশ্যই কোনো known table থেকে আসতে হবে।

Why this matters in practice Whenever you see a SQL query without a FROM (e.g. SELECT 1+1), it’s producing constants, not querying data. Real queries always have a FROM — that’s the safety guarantee in disguise.

5. Codd’s Theorem — They’re All Equally Powerful

Codd’s theorem (1972) states that the following three formalisms have exactly the same expressive power over relations:

Figure 10.1 — Algebra ⇔ TRC ⇔ DRC: any query expressible in one is expressible in the others.

Practically, every safe TRC or DRC expression can be mechanically translated to algebra, and every algebra expression to TRC. SQL is built on top of tuple calculus, but its query optimizer translates every query back into algebra to plan execution. So when you write SQL, you’re using all three at once — calculus to express, algebra to execute.

Codd-এর theorem (১৯৭২) বলে: relational algebra, TRC এবং DRC — এই তিনটির প্রকাশক্ষমতা ঠিক একই। যেকোনো safe TRC বা DRC expression কে algebra-তে রূপান্তর করা যায় এবং উল্টোটিও সত্য। আজকের SQL গড়ে উঠেছে tuple calculus-এর ওপর, কিন্তু optimizer প্রতিটি query কে আগে algebra-তে অনুবাদ করে — তারপর execute করে।

6. The Calculus → Algebra → SQL Pipeline

Take any plain-English requirement and walk it through the three stages. This is exactly what database engineering interviews ask you to demonstrate.

Three-stage drill
(1) Write the requirement as TRC.
(2) Rewrite as algebra.
(3) Translate to SQL.

6.1 Side-by-side example — bKash

“Names of bKash customers who have made at least one transaction over BDT 5000.”

Form	Expression
TRC	`{ t.name \| Customer(t) ∧ ∃x (Txn(x) ∧ x.customer_id = t.id ∧ x.amount > 5000) }`
Algebra	`π_name(Customer ⋈_{id = customer_id} σ_{amount > 5000}(Txn))`
SQL	see below

pipeline.sql

-- Translation of the TRC at the top of §6.1
SELECT DISTINCT c.name
FROM customer c
WHERE EXISTS (
    SELECT 1 FROM txn x
    WHERE x.customer_id = c.id
      AND x.amount > 5000
);

6.2 Universal example — “customers who have made transactions to all branches”

TRC: { t.name | Customer(t) ∧ ∀b (Branch(b) → ∃x (Txn(x) ∧ x.customer_id = t.id ∧ x.branch_id = b.id)) }

The ∀ becomes double NOT EXISTS in SQL:

universal.sql

SELECT c.name
FROM customer c
WHERE NOT EXISTS (
    SELECT 1 FROM branch b
    WHERE NOT EXISTS (
        SELECT 1 FROM txn x
        WHERE x.customer_id = c.id
          AND x.branch_id   = b.id
    )
);

7. Where SQL Drifts from Pure Calculus

SQL is descended from calculus, but it is not pure calculus. Three big departures:

✅ What SQL adds (যা যোগ হয়েছে)

Aggregates: SUM, COUNT, AVG, MIN, MAX.
GROUP BY / HAVING.
ORDER BY (presentation).
Bag (multiset) semantics — duplicates allowed.
Three-valued logic (TRUE / FALSE / NULL).

⚠️ What changes from pure theory

Pure calculus: sets only, no duplicates.
Pure calculus: no NULL.
Pure calculus: no ordering.
SQL’s NULL breaks some classic equivalences (e.g. NOT IN with NULL ≠ EXCEPT).

SQL মূলত calculus, কিন্তু পুরোপুরি বিশুদ্ধ নয়। SQL যোগ করেছে aggregate (SUM, COUNT, AVG…), GROUP BY, ORDER BY, এবং bag semantics (duplicate allowed)। আবার SQL-এ NULL যোগ হয়েছে, যা তিন-value-এর logic তৈরি করেছে — এতে কিছু classical equivalence ভেঙে যায় (যেমন NOT IN বনাম EXCEPT)।

The classic NULL trap WHERE x NOT IN (SELECT y FROM S) returns no rows if S contains a single NULL. EXCEPT would return correctly. This is one of the most common bugs in production SQL.

8. Practice Problems

প্রথমে নিজে চেষ্টা করুন, তারপর Show Answer-এ ক্লিক করুন। কয়েকটি উত্তর runnable SQL সহ দেওয়া আছে।

In one sentence each, distinguish algebra from calculus.

এক বাক্যে algebra ও calculus-এর পার্থক্য।

✨ Show Answer

Algebra is procedural — it tells how to compute. Calculus is declarative — it specifies what answer we want.
Write the TRC for: “names of customers who have made any bKash transaction in May 2026”.

TRC-তে লিখুন।

✨ Show Answer

{ t.name | Customer(t) ∧ ∃x (Txn(x) ∧ x.customer_id = t.id ∧ x.ts BETWEEN '2026-05-01' AND '2026-05-31') }
What does it mean for a calculus expression to be safe?

Safe expression কাকে বলে?

✨ Show Answer

An expression is safe if its result is guaranteed to be finite — every variable is bounded by membership in some finite relation that appears in the formula.
Write DRC and SQL for: “all student-name, dept pairs”.

DRC ও SQL-এ লিখুন।
✨ Show Answer
DRC: { ⟨n, d⟩ | ∃ id, c (Student(id, n, d, c)) }.
p4.sql
```
SELECT name, dept FROM student;
```
Translate ∀ to SQL — what is the standard idiom?

∀ কে SQL-এ কীভাবে লেখা হয়?

✨ Show Answer

Double NOT EXISTS (because ∀x P(x) ≡ ¬∃x ¬P(x)). Alternatively, GROUP BY … HAVING COUNT(DISTINCT …) = (SELECT COUNT(*) …).
What is Codd’s theorem?

Codd-এর theorem কী বলে?

✨ Show Answer

Relational algebra, tuple relational calculus (TRC), and domain relational calculus (DRC) all have exactly the same expressive power. Anything one can express, the other two can.
SQL is closer to TRC or DRC? Why?

SQL TRC-র কাছাকাছি না DRC-র?

✨ Show Answer

TRC. SQL’s row variables (FROM table AS t + t.column) directly mirror TRC’s tuple variables. Microsoft Access’s QBE is the rare commercial DRC-style interface.
Translate this English to TRC, then SQL: “names of products that no order has ever included.”

যেসব পণ্য কোনো order-এ আসেনি — TRC ও SQL।
✨ Show Answer
TRC: { p.name | Product(p) ∧ ¬∃o (OrderItem(o) ∧ o.product_id = p.id) }
p8.sql
```
SELECT p.name
FROM product p
WHERE NOT EXISTS (
    SELECT 1 FROM order_item o
    WHERE o.product_id = p.id
);
```
Why is SELECT ... NOT IN (subquery) dangerous when the subquery may return NULL?

NOT IN-এ NULL থাকলে কী সমস্যা হয়?

✨ Show Answer

SQL uses three-valued logic. x NOT IN (a, NULL) evaluates to NOT (x = a OR x = NULL) = NOT (TRUE OR UNKNOWN) = UNKNOWN — and rows with UNKNOWN are filtered out. Result: zero rows. Use EXCEPT or NOT EXISTS instead.
For each, mark as Algebra / TRC / DRC / SQL: (a) π_name(σ_{cgpa > 3.5}(student)); (b) { t.name | Student(t) ∧ t.cgpa > 3.5 }; (c) SELECT name FROM student WHERE cgpa > 3.5; (d) { ⟨n⟩ | ∃ id, d, c (Student(id, n, d, c) ∧ c > 3.5) }.

প্রতিটি অভিব্যক্তিকে Algebra / TRC / DRC / SQL-তে শ্রেণিবদ্ধ করুন।

✨ Show Answer

(a) Algebra. (b) TRC. (c) SQL. (d) DRC. — All four answer the same question: names of students with CGPA > 3.5.

Summary — Module 10

Relational calculus is the declarative twin of algebra: TRC ranges over tuples, DRC over attribute values. Both are equally powerful, and Codd’s theorem proves all three formalisms (algebra, TRC, DRC) are interchangeable. SQL grew from tuple calculus and added aggregates, GROUP BY, ordering, bag semantics, and NULL — useful in practice, but breaking some classical equivalences. The mental pipeline requirement → calculus → algebra → SQL is the secret skill that separates good database engineers from great ones.

Relational calculus হলো algebra-র declarative যমজ। TRC tuple-এর ওপর, DRC attribute-value-এর ওপর কাজ করে। Codd-এর theorem বলে — algebra, TRC, DRC সবাই সমান শক্তিশালী। SQL মূলত tuple calculus থেকে এসেছে, তবে এতে যোগ হয়েছে aggregate, GROUP BY, ORDER BY, bag semantics, এবং NULL। মানসিক pipeline — requirement → calculus → algebra → SQL — যেকোনো ভালো database engineer-এর গোপন দক্ষতা।

Next Module → Functional Dependencies & Normalization Foundations.

Previous Next: Functional Dependencies