Relational Model

Every relational database organises data into tables — rows and columns. Each row is one entity. Each column is one attribute. The power comes from what you can do with that grid: relate tables to each other, enforce constraints, and query any shape of data without restructuring the store.

Analogy

Picture a doctor's office filing cabinet. Every patient has the same pre-printed intake form with the same labelled boxes: name, date of birth, blood type, allergies. One patient gets one form; the form itself never changes shape. A primary key is the patient ID stamped in the corner so two "John Smiths" don't get mixed up. A foreign key is a referral slip that reads "see prescription chart #482" — it points to another drawer, and the receptionist refuses to file a slip that points to a chart that doesn't exist. Normalization is the rule that stops you writing the patient's phone number on every visit's form when it belongs once, on the patient's intake sheet.

Tables, rows, columns

A table has a fixed schema. Every row conforms to it. Columns have a type — integer, text, timestamp, boolean. The type is enforced by the engine; you cannot store "hello" in an integer column.

id	name	email	age
1	alice	alice@example.com	30
2	bob	bob@example.com	25

Primary keys

A primary key uniquely identifies each row. No two rows in the same table may share a primary key. By convention, most tables use a synthetic integer that auto-increments (SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL). Natural keys — email, passport number — can work but change more often than you'd like.

Foreign keys

A foreign key ties a column in one table to the primary key of another, enforcing referential integrity.

CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title   TEXT   NOT NULL
);

user_id must exist in users.id. Delete the user and the cascade rule fires. The database enforces this — no application code required.

Normalization

Normalization is a process of decomposing tables to remove redundancy and prevent update anomalies.

1NF — First Normal Form

Every cell holds one atomic value. No comma-separated lists inside a column, no repeating groups.

Violates 1NF:

order_id	items
1	"widget, gadget, donut"

1NF-compliant:

order_id	item
1	widget
1	gadget
1	donut

2NF — Second Normal Form

1NF, plus every non-key column depends on the whole primary key — not just part of it. Partial dependency only arises with composite keys.

If (order_id, product_id) is your PK and customer_name depends only on order_id, split customer_name into the orders table.

3NF — Third Normal Form

2NF, plus no transitive dependencies — non-key columns must not depend on other non-key columns.

If dept_name depends on dept_id, and dept_id depends on employee_id, move dept_name into a departments table.

BCNF — Boyce-Codd Normal Form

A stricter 3NF. Every functional determinant must be a candidate key. Rare in practice, but covers edge cases where 3NF still allows anomalies.

Why denormalize

Normalization is the default. But reading from six joined tables is slower than reading one wide table. When read throughput matters more than write simplicity — reporting tables, data warehouses, caches — you may denormalize intentionally. Document it. Enforce consistency in code, or via a trigger.

The ER diagram

An Entity-Relationship diagram shows tables as boxes and relationships as lines. Cardinality marks tell you the ratio:

• 1 — N: one user, many posts
• M — N: many students, many courses (requires a join table)

The visualizer in this level draws the demo schema as a live ER diagram.