Databases Study Portal

How it works

The relational model

• Table (relation) — a set of rows. Row (tuple) — one record. Column (attribute) — a typed field.
• Primary key (PK) — uniquely identifies a row; not null, unique. Often a surrogate id (auto-increment / bigint / uuid) rather than a natural key.
• Foreign key (FK) — a column that references another table's PK; the database enforces referential integrity (you can't reference a row that doesn't exist, and ON DELETE decides what happens to children).
• Constraints — NOT NULL, UNIQUE, CHECK, DEFAULT — push invariants into the database so bad data can't get in regardless of which app writes it.
• Schema — the set of table/column/constraint definitions (DDL). Changing it = a migration.

ACID — what a transaction guarantees

A transaction groups statements so they succeed or fail as a unit (BEGIN … COMMIT / ROLLBACK).

• Atomicity — all-or-nothing; a failure rolls the whole thing back.
• Consistency — constraints hold before and after; the DB never persists a state that violates them.
• Isolation — concurrent transactions don't step on each other (tunable, see isolation levels below).
• Durability — once committed, it survives a crash (write-ahead log / fsync).

The classic example: transferring money — debit one account and credit another must both happen or neither.

SQL in one breath

-- DDL (structure)
CREATE TABLE orders (
  id          bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total       numeric(10,2) NOT NULL CHECK (total >= 0),
  status      text NOT NULL DEFAULT 'pending',
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- DML (data)
SELECT c.name, COUNT(*) AS order_count, SUM(o.total) AS revenue
FROM   customers c
JOIN   orders o ON o.customer_id = c.id
WHERE  o.created_at >= '2026-01-01'
GROUP  BY c.name
HAVING SUM(o.total) > 1000
ORDER  BY revenue DESC
LIMIT  20;

Logical evaluation order is not the written order — it's roughly FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That's why you can't use a SELECT alias in WHERE (the alias doesn't exist yet) but you can in ORDER BY.

Joins

• INNER JOIN — only rows matching in both tables.
• LEFT (OUTER) JOIN — all left rows; right side is NULL when there's no match (use to find "customers with no orders": LEFT JOIN … WHERE o.id IS NULL).
• CROSS JOIN — Cartesian product. Usually accidental (a forgotten join condition) and a performance disaster — n × m rows.

Normalization vs denormalization

Normalization removes redundancy so each fact lives in exactly one place:

• 1NF — atomic columns, no repeating groups (no comma-separated lists in a cell).
• 2NF — no partial dependency on part of a composite key.
• 3NF — no transitive dependency (non-key columns depend only on the key, "the key, the whole key, and nothing but the key").

Normalized schemas avoid update anomalies (change a customer's name once, not in 10 000 order rows). Denormalization deliberately re-introduces redundancy (a cached order_count, a duplicated column) to avoid expensive joins on read-heavy paths — at the cost of having to keep the copies in sync. Normalize first; denormalize as a targeted optimization with evidence.