A-Level · Database Design

Database Design

Beyond writing queries: how a well-designed database actually protects the data inside it. Normalisation, the ACID guarantees behind every transaction, and referential integrity, all demonstrated with a real SQLite engine running the exact rules a database enforces, not just described.

Loading the SQLite engine for the live demonstrations below...

Section 2

Normalisation: 1NF to 3NF

One messy table, genuinely decomposed step by step. Watch each functional dependency get identified and split out, and watch the final check that proves nothing was lost along the way.

Functional dependencies

Exam tips

  • 1NF: every cell holds one atomic value, no repeating groups. 2NF: no non-key attribute depends on only PART of a composite key. 3NF: no non-key attribute depends on another non-key attribute (a transitive dependency).
  • Normalisation never loses data, it only reorganises it. The final reconstruction check above proves this directly: joining every table back together recovers the exact original rows.
Section 3

ACID: what a transaction actually guarantees

A bank transfer between two real accounts, in a real SQLite database. Try a transfer that would overdraw the sender, and watch what genuinely happens, not what's supposed to happen.

Accounts

Transfer from Alex to Sam

Try transferring 30 (succeeds), then try transferring 200 (would take Alex below zero, genuinely rejected by a real CHECK constraint and rolled back as a whole transaction).

The four ACID properties

  • Atomicity: a transaction happens completely or not at all. Demonstrated directly above, an invalid transfer never partially applies, both balances stay exactly as they were.
  • Consistency: a transaction can only move the database from one valid state to another. The CHECK constraint (balance can never go negative) is what enforces this here.
  • Isolation: transactions running at the same time don't see each other's half-finished work. If two transfers from the same account ran simultaneously without isolation, both could read the same starting balance and overdraw the account together, isolation prevents that.
  • Durability: once a transaction commits, it survives even a crash immediately afterward, because it's been written to permanent storage, not just held in memory.
Section 4

Referential integrity: RESTRICT, CASCADE, and SET NULL

Deleting a customer who still has orders. Three real, genuinely different behaviours, each enforced by the database engine depending on how the foreign key was defined, not by convention or guesswork.

RESTRICT (the default)

CASCADE

SET NULL

Exam tips

  • RESTRICT protects against orphaned foreign keys by refusing the delete outright, the order would otherwise point at a customer that no longer exists.
  • CASCADE takes the opposite approach: it deliberately removes the dependent rows too, appropriate when the child data genuinely has no meaning without its parent (an order without a customer makes no sense), but genuinely dangerous if used somewhere the dependent data should have survived.
  • SET NULL is a middle ground: the order itself survives, but its link to the now-deleted customer is broken (CustomerID becomes NULL). Appropriate when the child record is still meaningful on its own, an order's line items and total still matter for accounting even if we no longer know which customer placed it.

Cascading isn't only about deletes

A different scenario: renaming an author's code. ON UPDATE CASCADE propagates a primary key change to every book that references it, automatically and correctly, not just ON DELETE.

Author

Book (both reference the same AuthorCode)

Both books currently reference AuthorCode 'AUTH01'. Click rename and watch both foreign keys update automatically, in a single statement, with no separate UPDATE needed on the Book table.

Exam tips

  • ON DELETE and ON UPDATE are two separate clauses, a foreign key can genuinely have different behaviour for each, CASCADE on delete and RESTRICT on update, for instance, are both perfectly valid together on the same key.
  • Without ON UPDATE CASCADE, changing a referenced primary key would either be rejected outright (the default) or would silently orphan every row that still held the old value, verified directly above: both books update together, correctly, because the clause was explicitly defined.
Section 5

Secondary keys

Not every useful search field is a primary key.

StudentID (Primary Key)Surname (Secondary Key)Name
S1001ReyesMaya Reyes
S1002FinchOliver Finch
S1003ReyesDiego Reyes
StudentID uniquely identifies each row, that's the primary key. Surname isn't unique (two students share "Reyes"), so it can't be a primary key, but it's still genuinely useful to search and index on, that's a secondary key.

Exam tips

  • A secondary key speeds up searching and sorting on a field, without needing to guarantee uniqueness the way a primary key must. A database can hold many secondary (indexed) keys per table, but only one primary key.
Section 6

Client-server databases

Where does the actual query execute?

This SQL Sandbox (embedded)

  • The whole database and the whole SQLite engine run inside your browser
  • No network round-trip needed for any query
  • Only one user, you, can access this data, and it disappears when you close the tab

A genuine client-server database

  • The data and the database engine live on a central server
  • The client sends a query over the network, the server executes it and sends back only the result
  • Many users can query the same live data at once, and it persists long after any one client disconnects

Exam tips

  • The genuine trade-off: a client-server model allows shared, persistent, centrally-secured data at the cost of needing a network connection and a server to maintain, an embedded database like this sandbox needs neither, at the cost of being single-user and non-persistent.
Section 7

Check your understanding