Lesson 1 — Why Databases Beat Spreadsheets

Database

An organised, persistent collection of related data.

DBMS

Software that stores, secures and queries a database (e.g. PostgreSQL).

Table / row / column

A record set, one record, and one field of every record.

Transaction

A group of operations that all succeed or all fail together.

ACID

Atomicity, Consistency, Isolation, Durability — the safety guarantees of a relational DB.

A club tracks 4,000 members. What changes when you move to a database?

1. 1Concurrency: Ten volunteers can update records at once; the DBMS keeps edits from overwriting each other.
2. 2Validation: A 'join date' column only accepts real dates, so bad data never gets in.
3. 3Speed: An index on surname turns a 4,000-row scan into an instant lookup.
4. 4Security: Volunteers can read members but only admins can delete them.
5. 5Recovery: Nightly backups plus a write-ahead log mean a crash loses seconds, not the file.

sql-- Spreadsheet thinking: one messy sheet
-- Name | Email | Class | Class Teacher | Teacher Email  (teacher repeated 30x)

-- Database thinking: two tables, linked
CREATE TABLE teacher (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  email  TEXT UNIQUE NOT NULL
);

CREATE TABLE student (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  year_group INTEGER NOT NULL,
  teacher_id INTEGER REFERENCES teacher(id)
);

The teacher's email is stored once. Change it in one place and every student record is instantly correct.