The Relational Model — Mathematical Foundations

Relational Model — গাণিতিক ভিত্তি

Read: ~35 min Medium 12 practice problems Math + SQL

1. Why Math? — The Calm Power of the Relational Model

Codd was a mathematician before he was a database researcher. His genius was to define a database in pure set theory: a database is a collection of relations, a relation is a set of tuples, and SQL operations correspond to operations on sets. This is not academic decoration — it is the reason the optimizer can rewrite your query, why duplicates can be removed safely, and why a query gives the same answer regardless of the order rows happen to be stored on disk.

In this module we tie every SQL feature back to its math. We will define domain, attribute, tuple, relation; meet four kinds of keys; and write our first CREATE TABLE statements that put these constraints to work in real, runnable SQLite.

Codd ছিলেন প্রথমে গণিতবিদ, পরে database researcher। তিনি database-কে define করেন বিশুদ্ধ set theory-তে — relation মানে tuple-এর set, SQL operation মানে set-এর operation। এই গণিতই কারণ — কেন optimizer query rewrite করতে পারে, কেন duplicate নিরাপদে সরানো যায়, কেন row কোন order-এ রাখা সেটা উত্তর বদলায় না।

2. Domain, Attribute, Tuple, Relation

Four definitions, in increasing order of complexity. Memorise them — every database book in the world uses this exact vocabulary.

roll name dept cgpa 101 Mahmuda Khatun CSE 3.78 102 Tanvir Ahmed EEE 3.20 103 Sumi Akter CSE 3.91 Attribute → Tuples (rows) Relation: student(roll, name, dept, cgpa) Domain of cgpa = [0.00, 4.00] INTEGER TEXT (≤50) {CSE,EEE,…} REAL ∈ [0,4] ↑ each column has a domain — the set of legal values Figure 5.1 — Relation = attribute এর tuple-set; প্রতিটি attribute-এর একটি domain।
Math termSQL termMeaningবাংলায়
DomainData type / CHECKThe set of legal values for an attribute (e.g. INTEGER, or CGPA between 0.0 and 4.0).একটি attribute-এর জন্য বৈধ মানের সেট।
AttributeColumnA named slot in each tuple; has a domain.Column — প্রতিটি tuple-এর একটি নামকৃত স্লট।
TupleRowAn ordered set of attribute values; one record.Row — attribute মানের একটি কপি।
RelationTableA set of tuples — therefore no duplicates and no inherent order.Tuple-এর set — duplicate নয়, কোনো নির্দিষ্ট order নয়।
Relation schemaCREATE TABLE …The header: name and attributes with domains.Table-এর design।
Relation instanceThe current rowsThe actual set of tuples right now.এই মুহূর্তের actual rows।
Domain = বৈধ মানের সেট। Attribute = column, যার একটি domain আছে। Tuple = row। Relation = tuple-এর set — তাই duplicate বা order বলে কিছু নেই।

Notice the most subtle point: a relation is a set. Sets do not have duplicates, and sets do not have order. This is why SELECT without ORDER BY may return rows in any order, and why pure relational theory disallows duplicate rows. SQL relaxes both rules in practice — but the math is the foundation.

3. Keys — Super, Candidate, Primary, Foreign

A key is the single most important idea in relational design. It is what lets a row be uniquely identified — without keys, you cannot meaningfully UPDATE, DELETE, or JOIN. There are four flavours, each a refinement of the previous.

Key typeDefinitionExample (student)বাংলায়
Super keyAny set of attributes that uniquely identifies a tuple — possibly with extras.{roll}, {roll, name}, {roll, name, cgpa}যেকোনো attribute-set যা row uniquely identify করে।
Candidate keyA minimal super key — remove any attribute and uniqueness breaks.{roll}, {nid}সবচেয়ে ছোট super key — যেকোনো একটিও বাদ দিলে uniqueness ভাঙে।
Primary keyThe candidate key the designer chose; cannot be NULL.rollDesigner যে candidate key বেছে নিলেন; NULL হতে পারে না।
Alternate keyA candidate key that was not chosen as primary.nidযে candidate key primary হয়নি।
Foreign keyAn attribute in one table whose values must match a primary key in another (or be NULL).student.did → department.didএকটি table-এর attribute যা অন্য table-এর primary key-কে রেফার করে।
Key চারটি স্তর: Super key — যেকোনো unique-করা set; Candidate key — সবচেয়ে ছোট super key; Primary key — designer যেটি বেছে নেন (NULL নিষেধ); Foreign key — অন্য table-এর primary key-কে যেটি রেফার করে।

Below we declare every key type in one schema and run a query that walks the foreign-key chain. Notice the UNIQUE on nid — it is an alternate candidate key, even though we chose roll as primary.

keys_demo.sql 
-- Walk foreign keys: each student → department
SELECT s.roll, s.name, s.nid,
       d.dname AS dept, s.cgpa
FROM student s
JOIN department d ON d.did = s.did
ORDER BY s.cgpa DESC;

4. Why a Relation Cannot Have Duplicate Tuples

A pure relation is a set. Sets, by definition, do not contain duplicates: {a, a, b} and {a, b} are the same set. Therefore in the relational model, two identical rows cannot both exist.

Why this is enforced in practice
By declaring a primary key, you make duplicates of the key impossible. The DBMS rejects them at insert time. This is how the set property of a relation gets enforced concretely. Without a primary key, SQL does permit duplicates (a "bag," not a set) — which is one of the small ways SQL deviates from pure theory.

Primary key declare করলেই duplicate insert আটকে যায় — এভাবেই বিমূর্ত set-এর গুণ বাস্তবে চাপানো হয়।
no_duplicates.sql 
-- The DBMS rejects this — set semantics in action
INSERT INTO student VALUES (101, 'Mahmuda');

SELECT * FROM student;
Relation একটি set, আর set-এ duplicate থাকতে পারে না। Primary key declare করার মুহূর্তে DBMS এই বিমূর্ত নিয়মটিকে concretely চাপিয়ে দেয়।

5. Integrity Constraints — Three Shields That Protect the Data

An integrity constraint is a rule that the DBMS enforces on every INSERT, UPDATE, or DELETE. There are three classical kinds, and every modern SQL dialect implements all three.

TypeWhat it protectsSQL keywordবাংলায়
Entity integrityEvery tuple must be uniquely identifiable; primary key cannot be NULL.PRIMARY KEYপ্রতিটি row uniquely চেনা যাবে; PK NULL হবে না।
Referential integrityA foreign-key value must match an existing primary key (or be NULL).FOREIGN KEY … REFERENCESFK-এর মান অন্য table-এর PK-তে থাকতে হবে।
Domain integrityEvery attribute value must be inside its declared domain.CHECK, types, NOT NULLপ্রতিটি মান তার declared domain-এর মধ্যে থাকবে।

Below is a single schema demonstrating all three. Try to break any rule — the DBMS will reject your attempt:

integrity_all_three.sql 
-- 1. Entity integrity — primary key must exist and be unique
INSERT INTO account VALUES ('A1001', 'Rahim Uddin', 15000, 1);

-- 2. Domain integrity — CHECK rejects negative balance
--    The next line will fail.
INSERT INTO account VALUES ('A1002', 'Karim', -100, 2);

SELECT * FROM account;
referential_integrity.sql 
-- 3. Referential integrity — branch 99 does not exist, so FK fails
INSERT INTO account
VALUES ('A2001', 'Salma', 50000, 99);

SELECT * FROM account;
Entity integrity: PK NULL/duplicate নিষেধ। Referential integrity: FK-এর মান অন্য table-এর PK-তে থাকতে হবে। Domain integrity: CHECK ও type-এর সীমার বাইরে মান যাবে না। তিনটি একসাথে data-কে অসম্ভব অবস্থা থেকে রক্ষা করে।

6. Relation Schema vs Relation Instance — Same Idea, Stricter Words

We met schema vs instance in Module 03. In the relational model these terms get a little more precise.

📐 Relation schema R(A₁, A₂, …, Aₙ)

The name of the relation plus its ordered list of attributes with domains. Mathematically: a header.

student(roll: INT, name: TEXT, dept: TEXT, cgpa: REAL ∈ [0,4])

🧾 Relation instance r(R)

The set of tuples currently in the table — each tuple has values from the matching domains.

{(101, 'Mahmuda', 'CSE', 3.78), (102, 'Tanvir', 'EEE', 3.20)}

Relation schema = relation-এর নাম + attribute তালিকা + প্রতিটি attribute-এর domain। Relation instance = এই মুহূর্তে যে tuple-set actually আছে। Schema পাল্টায় কালেভদ্রে; instance প্রতি সেকেন্ডে।

7. SQL — How the Math Becomes a Language

Every line of SQL is a relational-model operation in disguise. Let us read three SQL constructs through the relational lens, so the rest of this course feels less like memorising syntax and more like applying theory.

  • CREATE TABLE student (roll INT PRIMARY KEY, …) — "Define a relation schema with these attributes and this entity-integrity constraint."
  • SELECT name FROM student WHERE cgpa > 3.5 — "From the relation student, project the name attribute, restricted to tuples whose cgpa > 3.5." (Pure relational algebra.)
  • FROM student JOIN department ON student.did = department.did — "The natural join of two relations on equal did values."

Run a one-shot example that ties it all together — model a simple e-commerce order line in pure relational terms. Notice every constraint and key:

ecommerce_relations.sql 
-- π_(district, total) σ_(year=2025) (orders ⋈ customer ⋈ product)
SELECT c.district,
       SUM(o.qty * p.price) AS total_revenue
FROM orders o
JOIN customer c ON c.cid = o.cid
JOIN product  p ON p.pid = o.pid
GROUP BY c.district
ORDER BY total_revenue DESC;
Reading SQL as algebra
Every SELECT … FROM … WHERE … can be re-written as a chain of relational-algebra operators: σ (select rows), π (project columns), ⋈ (join), ∪ (union), − (difference). The optimizer literally does this rewriting under the hood to choose a fast plan.

প্রতিটি SELECT-WHERE-JOIN আসলে σ-π-⋈-এর শৃঙ্খল। Optimizer এই শৃঙ্খলটিই rewrite করে দ্রুততম plan বেছে নেয়।
SQL আসলে relational algebra-র উপরে একটি English-like wrapper। যত গভীরে algebra বুঝবেন, তত কম মনে রাখা লাগবে।

8. Glossary (শব্দকোষ)

TermMeaningবাংলায়
DomainSet of legal values for an attribute.একটি attribute-এর বৈধ মানের সেট।
AttributeA named column.নামকৃত column।
TupleOne row.এক row।
RelationA set of tuples — a table without duplicates.Tuple-এর set।
Super keyAny uniquely-identifying attribute set.যেকোনো unique-করা attribute-set।
Candidate keyMinimal super key.সবচেয়ে ছোট super key।
Primary keyThe chosen candidate key — never NULL.বেছে নেওয়া candidate key — NULL নয়।
Foreign keyAn attribute referencing another relation's primary key.অন্য relation-এর PK-কে রেফার করা attribute।
Entity integrityPK exists & is unique.PK থাকতে হবে ও unique হবে।
Referential integrityFK matches an existing PK.FK অন্য PK-তে থাকতে হবে।
Domain integrityValues stay inside declared domain.মান domain-এর বাইরে যাবে না।

9. Practice Problems

Twelve problems — from definitions to runnable SQL. Try each before peeking.

বারোটি প্রশ্ন — সংজ্ঞা থেকে runnable SQL পর্যন্ত। আগে নিজে চেষ্টা করুন।
  1. Define domain, attribute, tuple, and relation in one sentence each.
    এক বাক্যে বলুন: domain, attribute, tuple, relation।
    ✨ Show Answer

    Answer: A domain is the set of legal values for one attribute. An attribute is a named column whose values come from a domain. A tuple is one ordered set of attribute values — a row. A relation is a set of tuples sharing one schema — a table.

    Domain — attribute-এর বৈধ মানের সেট; attribute — column; tuple — row; relation — tuple-এর set।

  2. For an e-commerce order(oid, cid, pid, qty, order_date) table, list one super key, one candidate key, one primary key, and any foreign keys.
    order(oid, cid, pid, qty, order_date) — একটি super key, একটি candidate key, একটি primary key এবং foreign key চিহ্নিত করুন।
    ✨ Show Answer

    Answer: Super key example: {oid, cid}. Candidate key: {oid}. Primary key: oid. Foreign keys: cid → customer.cid, pid → product.pid.

    Super key: {oid, cid}; Candidate key: {oid}; Primary key: oid; FK: cid → customer.cid, pid → product.pid।

  3. Write CREATE TABLE for an NID-style table with a 13-character national ID as primary key, a non-null name, and a year-of-birth domain check (1900–2025).
    NID-এর জন্য table বানান: ১৩-অক্ষরের NID primary key, name NOT NULL, year-of-birth domain check (1900–2025)।
    ✨ Show Answer
    ans3.sql
    CREATE TABLE citizen (
  nid   TEXT PRIMARY KEY CHECK (length(nid) = 13),
  name  TEXT NOT NULL,
  yob   INTEGER NOT NULL CHECK (yob BETWEEN 1900 AND 2025)
);

INSERT INTO citizen VALUES ('1990123456789', 'Mahmuda Khatun', 1990);
SELECT * FROM citizen;
  4. Show that domain integrity stops a 14-character NID from being inserted into the table above.
    দেখান, ১৪-অক্ষরের NID উপরের table-এ ঢুকতে পারবে না।
    ✨ Show Answer
    ans4.sql
    -- 14-character NID — domain check fails
INSERT INTO citizen VALUES ('19901234567890', 'Bad Row', 1990);

SELECT * FROM citizen;
  5. Define entity integrity, referential integrity, and domain integrity, with one bKash example each.
    প্রতিটি integrity-র সংজ্ঞা ও bKash থেকে একটি উদাহরণ দিন।
    ✨ Show Answer

    Answer:

    • Entity: Every wallet has a unique non-null msisdn.
    • Referential: A transaction's sender_msisdn must exist in the wallet table.
    • Domain: amount > 0 — no negative-amount transfers.

    Entity — প্রতিটি wallet-এর unique msisdn। Referential — txn-এর sender_msisdn আগে wallet-এ থাকতে হবে। Domain — amount > 0।

  6. Try to insert a duplicate primary key and observe the error.
    Duplicate primary key insert করার চেষ্টা করুন।
    ✨ Show Answer
    ans6.sql
    INSERT INTO wallet VALUES ('01711000001', 'Different Owner');
SELECT * FROM wallet;
  7. Why does a "relation" not have a built-in row order? Give one practical consequence.
    Relation-এর কোনো built-in order কেন নেই? এর একটি practical ফলাফল দিন।
    ✨ Show Answer

    Answer: Because a relation is a set, and sets are unordered. Practical consequence: a SELECT without ORDER BY may return rows in any order, and the optimizer is free to read them in whatever sequence is fastest.

    Relation একটি set; set-এ order নেই। ফলাফল: ORDER BY ছাড়া SELECT যেকোনো order-এ row দিতে পারে — optimizer যা দ্রুত মনে করে সেটাই বেছে নেয়।

  8. Explain the difference between a candidate key and a primary key. Can a relation have more than one candidate key?
    Candidate key ও primary key-এর পার্থক্য কী? একটি relation-এ একাধিক candidate key থাকতে পারে?
    ✨ Show Answer

    Answer: A candidate key is any minimal super key. A relation may have many — for example a student relation could have both {roll} and {nid}. The designer picks exactly one to be the primary key; the others become alternate keys, often enforced with UNIQUE.

    Candidate key — যেকোনো minimal super key। একটি relation-এ একাধিক থাকতে পারে। Designer একটিকে primary বাছেন, বাকিগুলো alternate, সাধারণত UNIQUE-এ enforce করা হয়।

  9. Build course and enrollment tables (a student can enrol in many courses) and list the courses each student is enrolled in.
    course ও enrollment table বানান (এক student অনেক course-এ ভর্তি হতে পারে) এবং প্রতিটি student-এর course list করুন।
    ✨ Show Answer
    ans9.sql
    SELECT s.name, c.title, e.grade
FROM enrollment e
JOIN student s ON s.roll = e.roll
JOIN course  c ON c.cid  = e.cid
ORDER BY s.name;
  10. In the schema above, why is the primary key of enrollment a composite of (roll, cid)?
    উপরের schema-এ enrollment-এর primary key (roll, cid) composite কেন?
    ✨ Show Answer

    Answer: Neither roll alone nor cid alone identifies an enrolment uniquely — a student takes many courses and a course has many students. Their combination is the smallest set that uniquely identifies one enrolment row, so the candidate key is {roll, cid}.

    শুধু roll বা শুধু cid দিয়ে এক row চেনা যায় না; দুটি মিলিয়েই unique। তাই composite primary key।

  11. Demonstrate referential integrity: try to enrol a non-existent student. The DBMS should reject it.
    Referential integrity দেখান — অনুপস্থিত roll দিয়ে enroll করার চেষ্টা করুন।
    ✨ Show Answer
    ans11.sql
    -- Roll 999 does not exist — FK should fail
INSERT INTO enrollment VALUES (999, 'CSE101');

SELECT * FROM enrollment;
  12. In one paragraph, explain why "the relational model is built on set theory" actually matters to a working developer.
    এক অনুচ্ছেদে ব্যাখ্যা করুন — "relational model set theory-র উপর বানানো" — এটি কেন সত্যিকার developer-এর জন্য গুরুত্বপূর্ণ।
    ✨ Show Answer

    Answer: Because set theory makes queries composable and rewritable. The optimizer can re-arrange joins, push filters down, eliminate duplicates, and parallelise — all because every operation has clean mathematical meaning. It is also why SQL is portable: any DBMS that respects the math will compute the same answer for the same query, regardless of which indexes or storage tricks it uses internally.

    Set theory-র কারণেই query compose ও rewrite করা যায়। Optimizer JOIN order বদলায়, filter আগে চালায়, duplicate সরায়, parallelise করে — কারণ প্রতিটি operation-এর গণিতগত অর্থ পরিষ্কার। এই কারণেই একই SQL ভিন্ন DBMS-এ একই উত্তর দেয়।

Summary — Module 05

The relational model is just four tidy ideas: a domain is a set of legal values, an attribute is a named column drawing from a domain, a tuple is one row, and a relation is a set of tuples. From these come super, candidate, primary, and foreign keys, and the three classical integrity constraints — entity, referential, and domain — that the DBMS enforces on every write. SQL is the English-like language we use to describe operations on these sets; behind the scenes the optimizer rewrites every query as relational algebra. The rest of this course is, in a sense, an extended workshop on this single mathematical foundation.

Relational মডেল চারটি পরিচ্ছন্ন ধারণা: domain (বৈধ মানের সেট), attribute (column), tuple (row), relation (tuple-এর set)। এ থেকে আসে super, candidate, primary, foreign key, এবং তিনটি integrity (entity, referential, domain) যা DBMS প্রতিটি write-এ enforce করে। SQL-এর প্রতিটি query আসলে relational algebra-র rewriting — পুরো কোর্সটি এই গণিতের উপর-ই দাঁড়িয়ে আছে।

Next Module → Phase 2 শুরু — Relational Algebra ও Calculus, যেখানে আজকের গণিত আরও গভীরভাবে আসবে।

Previous Next: Relational Algebra