The Relational Model — Tables, Keys, Normalisation

Runtime contract: The complete Python lab uses only an in-memory SQLite database. PostgreSQL-specific constraints and operational choices are checked against PostgreSQL 16 documentation, not a PostgreSQL server. Amounts are integer paise; decimal output is display-only.

Why this session exists

A database design starts with a question: what does one row assert, and what identifies that assertion? Normalisation helps avoid storing a mutable fact in several independently editable places. It does not automatically enforce every business rule, and it does not require one table per real-world noun.

By the end, you should be able to identify primary, candidate, composite and foreign keys; normalise an invoice through 1NF, 2NF and 3NF; distinguish current attributes from historical snapshots; choose natural versus surrogate identity; and enforce required, unique, positive and referential values in SQL.

Prerequisites

Read Python data structures and understand records, sets and tuples. A Python list of dictionaries is a convenient way to inspect query output, not the definition of a relation: lists are ordered and can contain duplicates.

Optional archived resources

These original links are retained for continuity, not used as technical evidence. Video titles, attribution, duration and playback are not reverified; a search link is not a particular lecture. Use the versioned primary sources below for contracts.

• What is a Relational Database?
• Lecture 01 · Relational Model & Relational Algebra
• Database Normalization — 1NF, 2NF, 3NF
• A Relational Model of Data for Large Shared Data Banks
• Data Definition — CREATE TABLE, constraints, keys
• Data Warehousing · Star & Snowflake Schemas

Historical context: relations are not storage layouts

Codd's 1970 relational proposal separated the logical description of data from physical navigation through storage. IBM's System R work subsequently explored a relational system and SQL. The durable lesson is data independence, not a claim that every OLTP store is relational or that all NoSQL systems eventually become SQL databases. IBM's historical account supports this lineage; it does not establish the old lesson's universal product or market-share claims.

One fact, one lifetime

Suppose an online shop records:

If email means current contact, Alice's rows disagree: an update anomaly. If it means address used for that purchase, the differing values may be correct. State the meaning before declaring duplication a defect.

A combined customer/order sheet also has insertion and deletion anomalies: a customer without an order has no natural row, and deleting their last order may remove their only contact record. Separate customer identity from purchase identity so those lifetimes do not depend on each other.

A historical unit_price_cents is not an exception to normalisation merely because it initially equals a catalogue price. They are different facts: purchase-line price versus current product price. The same reasoning applies to an immutable invoice address or legally required product-description snapshot.

From a spreadsheet to 3NF

A functional dependency X → Y means that in every valid instance, equal X values imply equal Y values. It is a domain rule, not a correlation inferred from four sample rows.

Take an invoice with columns order_id, customer_id, customer_email, order_date, line_no, product_id, product_name, qty, sale_price. Assume one customer and date per order, one current name per product, one current email per customer, and one product/quantity/price per order line.

1. 1NF: represent one product line per row rather than a CSV product list or product1/product2/product3 repeating columns. Attribute values are drawn from declared domains. “Atomic” is relative to the chosen model; storing a date as one value is fine even though it has year/month/day parts.
2. 2NF: a nonprime attribute must not depend on only a proper subset of a candidate key. Here the line candidate key is (order_id, line_no), but order_date and customer_id depend only on order_id. An intermediate 2NF split is orders(order_id, customer_id, customer_email, order_date) and order_lines(order_id, line_no, product_id, product_name, qty, sale_price): retain the dependent attributes until the next decomposition, rather than silently dropping them. Adding a surrogate line ID alone would not remove the partial dependency on the alternate composite candidate key.
3. 3NF: starting from a 1NF relation, for every nontrivial dependency X → A, X must be a superkey or A must be prime (belong to some candidate key). Move customer_id → customer_email into customers(customer_id, customer_email), and product_id → product_name into products(product_id, product_name). The final orders(order_id, customer_id, order_date) and order_lines(order_id, line_no, product_id, qty, sale_price) retain foreign keys to those facts. The usual “no non-key-to-non-key dependency” slogan is only a simplified special case.
4. BCNF: every determinant of a nontrivial dependency is a superkey. This strengthens 3NF; it does not mean literally no repeated values exist. Decomposition must also be lossless: joining on the shared key must reconstruct the original valid facts without spurious combinations. Dependency preservation determines whether all rules can still be checked locally.

For example, splitting an enrolment (student, course, grade) into (student, course) and (student, grade) loses which grade belongs to which course. For Alice's courses SQL and Python with grades A and B, their join creates four combinations instead of two. Splitting along the dependencies above avoids that mistake.

The Microsoft normalisation introduction supplies the anomaly/repeating-group motivation. The authors’ Database System Concepts, 7th edition, chapter 7 slides, especially slides 7.23 and 7.28, give the formal BCNF/3NF definitions; these clarify where beginner mnemonics need qualification.

For the invoice assumptions, each binary split has a shared determinant that is a key of one component. Joining orders to customers on customer_id retrieves exactly one current email; joining order lines to products retrieves one current name. The intended source instance is reconstructed without inventing email/product combinations. FK constraints enforce parent existence; they do not create the functional dependencies by inference from samples. No additional dependencies or NULL-valued mathematical keys are assumed in this normal-form derivation.

Worked courses and instructors

Start with courses(course_code, course_title, instructor_id, instructor_name, instructor_dept), with course_code the only candidate key. Assume each course has one instructor and each instructor one current department. The values are 1NF; there is no proper nonempty subset of the sole single-column candidate key, so there is no partial dependency violating 2NF. But instructor_id → instructor_name, instructor_dept violates 3NF because instructor_id is not a superkey and those attributes are not prime.

Decompose to courses(course_code PK, course_title, instructor_id FK) and instructors(instructor_id PK, name, dept). Updating instructor 7's department then updates one row. If instructors can teach jointly or belong to multiple departments, change the domain model and dependencies rather than pretending this design covers them. Likewise, a postal code does not universally determine one city and state across countries: do not invent that dependency.

Keys and referential integrity

• Primary key: the chosen unique, non-null row identifier; it may contain several columns. Immutability is a design preference, not what PRIMARY KEY syntax guarantees.
• Candidate key: a minimal attribute set sufficient to identify a tuple. A superkey may contain unnecessary attributes. An alternate candidate needs suitable uniqueness and requiredness constraints; nullable UNIQUE alone is not a mathematical candidate key.
• Foreign key: constrained columns whose non-null values must match an eligible referenced unique key. In PostgreSQL this need not be the primary key, and self-reference is allowed. With the usual MATCH SIMPLE, a composite FK containing a NULL escapes the match requirement; use an appropriate all-or-none policy when that is not intended.
• Natural key: a domain identifier such as an edition's ISBN or a membership number. Use it when its scope, uniqueness and lifecycle really match the entity. An email shared by a family cannot be made unique per patient just by replacing the patient primary key.
• Surrogate key: an independent integer or UUID. It decouples identity from editable contact data, but does not prevent duplicate real-world entities; preserve valid business uniqueness separately.
• Composite key: e.g. (user_id, role_id) or (order_id, line_no). For sensor readings, (sensor_id, timestamp) is unique only if the system actually forbids multiple readings at that time.

Sequential integers are compact and locality-friendly; random UUIDs enable independent allocation but change index locality and width. Time-ordered IDs have their own privacy, collision and allocation considerations. PostgreSQL 16 has a UUID type; do not infer a built-in UUIDv7 generator from later PostgreSQL documentation. None of these choices has a universal performance ratio.

PostgreSQL constraints define NOT NULL, UNIQUE, CHECK and REFERENCES separately. A CHECK(qty > 0) passes if its expression is TRUE or NULL. Add NOT NULL when absence is invalid. PostgreSQL unique constraints normally allow multiple NULLs; NULLS NOT DISTINCT deliberately changes that policy. SQLite has legacy nullable-primary-key exceptions outside INTEGER PRIMARY KEY, STRICT and WITHOUT ROWID tables, so write requiredness explicitly rather than generalising from this lab's integer IDs.

The normalised shop design

The small lab calls its single-product purchase rows orders. Each is effectively one order line. A multi-line order needs the header/line decomposition already worked above.

The keys make the joins many-to-one. The FKs establish parent existence; they do not establish that a purchase price matches an approved price list, that the date is well-formed, or that an account is authorised to place this order.

Sets, bags and NULL

A mathematical relation is an unordered set of tuples. SQL query results usually have bag semantics: projecting away a primary key can produce duplicate rows even if the source table has one. Column positions matter to some SQL operations, including set-operation matching; result-row order still requires ORDER BY.

SELECT 1 UNION SELECT 1;           -- one row
SELECT 1 UNION ALL SELECT 1;       -- two rows
SELECT NULL = NULL;               -- NULL / UNKNOWN
SELECT 1 WHERE 1 NOT IN (2, NULL); -- no row

NOT IN is not universally “nothing whenever NULL exists”: it evaluates FALSE when an equal value is found, UNKNOWN when no equal value is found but a comparison is unknown, and TRUE for an empty right-hand set even with a NULL left operand. An ordinary equality-based NOT EXISTS expresses absence of a match without treating a right-hand NULL as evidence against every other key. Use null-safe equality deliberately when two missing markers should count as equivalent.

SQLite affinity is not a domain validator

In this lab, integer paise describe the intended input domain. Ordinary SQLite tables have type affinity, not strict static typing: INTEGER NOT NULL CHECK(qty > 0) can still accept a real such as 1.5 or nonnumeric text such as 'abc'. Under SQLite comparison rules, text compares above numbers, so the latter can pass that CHECK. A date stored as TEXT is likewise not a date validator. Do not promote successful negative/NULL tests into proof of all-domain validity.

For a strict integer domain, SQLite 3.37+ supports STRICT tables; alternatively require CHECK(typeof(qty) = 'integer' AND qty > 0) alongside NOT NULL. STRICT may losslessly convert numeric text such as '2', so it does not mean “the caller passed a Python int.” PostgreSQL 16 integer columns have a different typing contract. Here is a standalone SQLite-only extension, not a replacement for the retained shop lab:

CREATE TABLE strict_quantity (
    id INTEGER PRIMARY KEY,
    qty INTEGER NOT NULL CHECK(typeof(qty) = 'integer' AND qty > 0)
);
INSERT INTO strict_quantity VALUES (1,2); -- accepted
-- Run separately as negative tests: each must fail a constraint.
-- INSERT INTO strict_quantity VALUES (2,1.5);
-- INSERT INTO strict_quantity VALUES (3,'abc');
-- INSERT INTO strict_quantity VALUES (4,NULL);

Hands-on: constraints and independent lifetimes

Save this complete block as rel_lab.py and run python3 rel_lab.py. Enable SQLite FK enforcement before starting a transaction and verify it; a mid-transaction PRAGMA change is ineffective. These are real local constraint checks, not a crash-durability test.

"""rel_lab.py — feel the relational model in your fingers."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")
assert conn.execute("PRAGMA foreign_keys").fetchone() == (1,)
cur = conn.cursor()
 
# ---------- 1. Define a normalised schema with real constraints ----------
schema = """
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE
);
 
CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0)
);
 
CREATE TABLE orders (
    order_id           INTEGER PRIMARY KEY,
    customer_id        INTEGER NOT NULL REFERENCES customers(customer_id),
    product_id         INTEGER NOT NULL REFERENCES products(product_id),
    qty                INTEGER NOT NULL CHECK (qty > 0),
    unit_price_cents   INTEGER NOT NULL CHECK (unit_price_cents > 0),       -- snapshot at order time
    order_date         TEXT NOT NULL
);
 
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_product  ON orders(product_id);
"""
cur.executescript(schema)
 
# ---------- 2. Seed data ----------
cur.executemany("INSERT INTO customers VALUES (?,?,?)", [
    (1, "Alice", "a@x.com"),
    (2, "Bob",   "b@x.com"),
])
cur.executemany("INSERT INTO products VALUES (?,?,?)", [
    (10, "T-shirt", 50000),
    (20, "Mug",     25000),
    (30, "Cap",     30000),
])
cur.executemany("INSERT INTO orders VALUES (?,?,?,?,?,?)", [
    (1, 1, 10, 2, 50000, "2026-01-15"),
    (2, 1, 20, 1, 25000, "2026-01-20"),
    (3, 2, 10, 1, 50000, "2026-01-22"),
    (4, 1, 30, 1, 30000, "2026-02-05"),
])
 
# ---------- 3. Show Alice's orders — email lives in ONE place ----------
print("Alice's orders (single-source email):")
for row in cur.execute("""
    SELECT o.order_id, c.email, p.name, o.qty, o.unit_price_cents, o.order_date
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    WHERE c.name = 'Alice'
    ORDER BY o.order_id;
"""):
    print(" ", row)
 
# ---------- 4. Update Alice's email ONE row; all joins reflect it ----------
cur.execute("UPDATE customers SET email = 'alice@new.com' WHERE customer_id = 1")
 
# ---------- 5. Change product price; historical orders KEEP their snapshot ----------
cur.execute("UPDATE products SET price_cents = 60000 WHERE product_id = 10")
 
print("\nAfter email + price change:")
for row in cur.execute("""
    SELECT o.order_id, c.email, p.name AS product,
           p.price_cents AS current_price,
           o.unit_price_cents AS paid_price
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    WHERE c.name = 'Alice'
    ORDER BY o.order_id;
"""):
    print(" ", row)
 
# ---------- 6. Referential integrity: try to insert a bad order ----------
print("\nAttempt: insert order for non-existent customer_id=999")
try:
    cur.execute("INSERT INTO orders VALUES (99, 999, 10, 1, 50000, '2026-03-01')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by FK:", e)
 
# ---------- 7. UNIQUE constraint: duplicate email rejected ----------
print("\nAttempt: insert duplicate email")
try:
    cur.execute("INSERT INTO customers VALUES (3, 'Carol', 'alice@new.com')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by UNIQUE:", e)
 
# ---------- 8. CHECK constraint: negative qty rejected ----------
print("\nAttempt: insert order with qty = -5")
try:
    cur.execute("INSERT INTO orders VALUES (100, 1, 10, -5, 50000, '2026-03-01')")
except sqlite3.IntegrityError as e:
    print("  ✓ blocked by CHECK:", e)
 
# ---------- 9. Aggregate — revenue per customer ----------
print("\nRevenue per customer:")
for row in cur.execute("""
    SELECT c.name,
           SUM(o.qty * o.unit_price_cents) / 100.0 AS revenue_rupees
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.name
    ORDER BY revenue_rupees DESC;
"""):
    print(" ", row)
 
conn.close()

Worked results and category exercise

Alice's first output contains orders 1, 2 and 4. After changing her email, all three joins show alice@new.com without rewriting orders. The T-shirt's current price becomes 60,000 paise; order 1 still records 50,000. The final customer totals are Alice ₹1,550 and Bob ₹500. The orphan, duplicate-email and negative-quantity attempts each raise IntegrityError; the snapshot-price constraint also rejects nonpositive prices. Dates remain ISO-shaped TEXT by convention in this teaching fixture, not validated date types.

Extend the lab before conn.close():

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);
ALTER TABLE products ADD COLUMN category_id INTEGER REFERENCES categories(category_id);
INSERT INTO categories VALUES (1,'Apparel'),(2,'Drinkware'),(3,'Accessories');
UPDATE products SET category_id = 1 WHERE product_id = 10;
UPDATE products SET category_id = 2 WHERE product_id = 20;
UPDATE products SET category_id = 3 WHERE product_id = 30;
SELECT cat.name, SUM(o.qty * o.unit_price_cents) / 100.0 AS revenue_rupees
FROM orders o
JOIN products p ON p.product_id = o.product_id
JOIN categories cat ON cat.category_id = p.category_id
GROUP BY cat.category_id, cat.name
ORDER BY revenue_rupees DESC, cat.category_id;

Answer: Apparel 1500, Accessories 300, Drinkware 250. Each product maps to one category here; a many-category relationship would require an attribution rule or totals could multiply. This is a normalised lookup extension, not enough on its own to certify a dimensional star schema.

OLTP, OLAP and maintained copies

Normalised OLTP storage localises attribute updates and makes useful rules expressible as constraints. A star schema has a fact table at a declared grain and dimensions containing descriptive attributes; it still uses joins. A snowflake normalises some dimensions further. A wide reporting table prejoins more attributes and transfers maintenance work to the data pipeline. Columnar storage and partition pruning, not denormalisation alone, can make analytical scans efficient.

Choose using query patterns, correction frequency, historical requirements and freshness budgets. Analytics pipelines may update, merge and delete, not only append. Even one deterministic writer can crash between updates, receive late corrections or contain a bug: writer count is not a proof of anomaly freedom. A robust derived table needs versioned inputs, idempotent updates/rebuilds, reconciliation and atomic publication of a coherent result.

A materialised view can preserve a normalised source while serving a read-optimised shape. The cost is refresh work and staleness, and a full rebuild requires retained source data. “One fact, one place” is a design aid for authoritative facts, not a ban on caches, historical snapshots or replicated storage.

Production failure analysis

The original incident numbers and named-company performance stories were not established by their links. Use the following concrete scenarios instead of treating them as historical reports.

Orphan payments: find children with NOT EXISTS against parents; quarantine and reconcile against the authoritative ledger. Do not hard-delete financial evidence to make an FK migration pass. Correct missing identities, validate the relationship, then introduce the constraint using an engine-appropriate migration. Child-key indexes may make parent deletes/updates efficient; PostgreSQL does not automatically create them for FKs. Index benefit depends on the child workload, not an assumed five-microsecond cost.

Shared patient email: a surrogate patient_id fixes row identity, but keeping UNIQUE(email) still rejects twins using the same parent address. Model contact methods independently or permit repeated emails if the domain allows them. Use ON UPDATE/ON DELETE policies explicitly rather than assuming a natural-key edit must silently disconnect records.

Warehouse disagreement: two analysts can use different fact grains, attribution rules or freshness points even with a star schema. Publish a documented revenue definition, test reconciliation totals and distinguish current dimensions from historical snapshots. A canonical fact table helps, but no layout guarantees identical answers to different business questions.

Soft deletion does not “break” uniqueness; the stored row continues to satisfy the uniqueness policy. For unique active email in PostgreSQL or SQLite, use a partial unique index on email where deleted_at IS NULL. A nullable FK can represent optional association; a sentinel parent row represents a different business meaning. Enums can constrain a small domain; a lookup table is better when values need translated labels or additional metadata. Store currency in checked integer minor units or an appropriate exact decimal type with a declared rounding policy; binary floating point is approximate, but its error is not a guaranteed per-transaction cumulative loss.

Recall and stretch answers

1. PK versus FK? A PK identifies its row; an FK enforces eligible parent-key existence for required/non-null associations. Neither replaces authorisation.
2. Update anomaly? If email is current contact, editing just Alice's fourth purchase leaves contradictory copies. If it is a historical contact snapshot, the difference can be intentional.
3. 3NF? Apply the superkey-or-prime rule to every nontrivial dependency; decompose instructor attributes under instructor identity, not merely under an arbitrary surrogate.
4. When denormalise? For a justified reporting workload with maintained derived data and a freshness contract; star schemas are not join-free wide row stores.
5. Natural or surrogate? Prefer stable independent identity when domain identifiers change or are not actually unique; retain only domain-valid unique constraints.
6. CHECK versus app validation? Both are useful. The engine constrains every ordinary write path, but CHECK alone allows NULL and cannot express arbitrary cross-row rules in PostgreSQL.
7. Money? Use exact units/decimal arithmetic and explicit overflow/rounding rules. This lab's /100.0 is presentation, not accounting storage.
8. Soft-delete reuse? A partial unique index can enforce uniqueness among active rows; whether reuse is desirable remains a business decision.
9. Composite identity? (order_id, line_no) identifies a line. An extra surrogate does not eliminate that domain uniqueness rule.
10. Library model? Use books at the edition grain, copies for physical items, members, and loans(copy_id, member_id, borrowed_at, returned_at). Model multiple authors with a book_authors bridge, not one mandatory author_id. A partial unique index on loans(copy_id) WHERE returned_at IS NULL prevents two outstanding loans on a copy. Make copy_id and member_id non-null FKs. Shared member emails need not be unique.

Explain aloud: a relation models valid tuples independently of storage; keys identify facts and links; normalisation controls dependencies, while denormalisation creates maintained copies for a workload. For the invoice exercise, keep current product/customer attributes at their own lifetimes and preserve the transaction's price/address facts explicitly.

Explain-out-loud answer guide

1. What is the relational model? It describes data as relations of tuples over declared attribute domains, with keys and dependencies independently of physical storage; SQL bags and NULL markers require additional care.
2. What is a primary key versus a foreign key? A chosen PK is unique and required; an FK enforces an eligible parent key for non-null references. Candidate keys are minimal, and business identity is not automatically guaranteed by a surrogate.
3. Why normalise OLTP and denormalise OLAP? Normalisation localises authoritative updates; a justified analytical view trades maintained copies and freshness work for a workload-specific read shape. Neither workload label nor one deterministic writer proves correctness or speed.
4. How do you normalise the invoice? Declare the line grain and FDs, split partial order dependencies for 2NF, then move customer/product attributes to their determinants for 3NF; check losslessness and preserve purchase-time price facts.

Where this leads and sources

Continue to SQL basics, joins, indexes and transactions. Dimensional modelling and event-derived views build on grain and lifetime choices, not the obsolete session-number labels in the original navigation.

Primary documentation rechecked 10 October 2026: PostgreSQL 16 constraints, numeric types, SQLite foreign keys, SQLite type affinity and comparisons, SQLite SELECT and bag operations, and the historical/normalisation sources linked above. SQLite documentation is mutable and captured by access date. SQL results and negative cases are separately recorded in the batch evidence; no production measurements are asserted.