⚡ Rapid revision — the 20-minute version
| Spine | The mark |
|---|---|
| Keys | Primary: unique, never null; foreign: references a parent — junction tables solve M:N |
| Queries | SELECT-FROM-WHERE-ORDER BY — a missing WHERE on UPDATE changes EVERYTHING |
| Joins | INNER keeps matches; LEFT keeps ALL left rows — 'all customers' demands LEFT |
| Aggregation | WHERE filters rows, HAVING filters groups — aggregates live in HAVING |
| Normalisation | 1NF atomic, 2NF whole-key, 3NF no transitive chains — name the dependency, then split |
| ACID | Transfer = atomicity: both legs or neither; FKs guard referential integrity |
1. What a database is — and why not a spreadsheet
Database: A structured, PERSISTENT, SHARED collection of data managed by a DBMS. The DBMS enforces structure (schema), controls concurrent access (two users, no corruption), manages permissions, and backs up. FLAT FILE = one table, repeats data everywhere; RELATIONAL = many tables linked by keys, each fact stored ONCE. The move from flat file to relational is about removing redundancy — that is the whole story.
Worked: a customer's address typed on every invoice row (flat file) — change it 500 times or watch inconsistency. In a relational DB the address lives once in Customers; invoices point at the customer by key. One fact, one place — the definition of the relational idea.
2. The relational model — tables, keys, relationships
Keys: TABLE (relation) of ROWS (records) and COLUMNS (fields). PRIMARY KEY: the column that uniquely identifies each row (StudentID — never name, names collide). FOREIGN KEY: a column referencing another table's primary key — the link that joins tables. Relationships: 1:1, 1:M (one customer, many orders), M:N (students and subjects — resolved by a JUNCTION table holding both foreign keys).
Worked: Students(StudentID PK, name) and Subjects(SubjectID PK, title); M:N enrolment → Enrolments(StudentID FK, SubjectID FK). 'Why not store the subject name in Students?' — redundancy and update anomalies. Name the key, name the relationship type, name the junction — that trio is the mark.
3. SQL — defining the structure (DDL)
CREATE TABLE Students (StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, ClassID INT REFERENCES Classes(ClassID)). Constraints are the schema's law: PRIMARY KEY (unique, never null), FOREIGN KEY (must match a parent row), NOT NULL, UNIQUE, CHECK (a condition like marks BETWEEN 0 AND 100). The exam reads/writes short DDL — know that constraints live IN the table definition, not in the app.
4. SQL — querying and changing data (DML)
SELECT: SELECT columns FROM table WHERE condition ORDER BY column — the query spine. INSERT INTO adds rows, UPDATE ... SET ... WHERE changes matching rows, DELETE FROM ... WHERE removes them (omit WHERE and you change EVERYTHING — the classic disaster). Comparison: = ≠ less greater; BETWEEN, IN, LIKE for patterns; AND/OR/NOT combine conditions.
Worked: SELECT Name, Score FROM Results WHERE Score ≥ 50 ORDER BY Score DESC — names of everyone who passed, best first. UPDATE Results SET Score = 65 WHERE StudentID = 12 — the WHERE clause IS the safety catch: forget it and every row becomes 65.
5. Joins — combining tables
JOIN: INNER JOIN returns only rows that MATCH in both tables; LEFT JOIN returns all rows of the left table plus matches (NULL where none). The trap: an INNER JOIN silently drops customers with no orders — if the question says 'ALL customers, with orders if any', that is a LEFT JOIN. Join on the foreign key: FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID.
Worked: 100 customers, 80 have orders. INNER JOIN → 80 rows; LEFT JOIN → 100 rows (20 with NULL order columns). 'List all customers and any orders' — the ALL is the keyword that demands LEFT. Read the question's quantifier before choosing the join.
6. Aggregation — GROUP BY and the HAVING trap
Aggregation: COUNT, SUM, AVG, MIN, MAX collapse many rows into one answer. GROUP BY department produces one row per department; WHERE filters ROWS before grouping, HAVING filters GROUPS after aggregation. SELECT department, AVG(salary) FROM staff GROUP BY department HAVING AVG(salary) is greater than 50000 — HAVING because the condition uses the aggregate, WHERE cannot.
Worked: 'departments with more than 5 staff' — WHERE COUNT(*) fails (aggregates do not exist yet); GROUP BY department HAVING COUNT(*) is greater than 5 succeeds. The order of execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Say which clause and why — the HAVING rule is the exam's favourite SQL mark.
7. Normalisation — 1NF, 2NF, 3NF
Normal forms: 1NF: every cell ATOMIC (one value — no comma lists). 2NF: 1NF + no PARTIAL dependency — a non-key column must depend on the WHOLE composite key. 3NF: 2NF + no TRANSITIVE dependency — non-key columns must not depend on other non-key columns. Purpose: kill the UPDATE, INSERT and DELETE ANOMALIES — every fact stored once.
Worked: Orders(OrderID, ProductID, ProductName, Qty) — ProductName depends only on ProductID, not the whole key (partial) → 2NF violation: split Products(ProductID, ProductName). Then if Products also stored SupplierName with SupplierCity — City depends on Supplier (transitive) → 3NF violation: split Suppliers. Identify the DEPENDENCY first, split second — the dependency named is the mark.
8. Integrity and transactions — ACID
Integrity and ACID: ENTITY integrity: every row has a unique, non-null primary key. REFERENTIAL integrity: every foreign key points to an EXISTING parent row (no orphan orders). TRANSACTION: a unit of work that is ATOMIC (all or nothing), CONSISTENT (rules hold), ISOLATED (concurrent transactions do not trample), DURABLE (committed means survives crashes). The bank transfer is the canonical case: debit + credit must both happen or neither.
Worked: transfer 500 from A to B — debit A, credit B inside ONE transaction: a crash between the two steps rolls back both (atomicity). An order pointing at a deleted customer breaks referential integrity — the FK constraint blocks the delete (or cascades, by explicit choice). Name the property; the transfer story is the mark.
9. Indexing — the read/write trade-off
An INDEX is a lookup structure (like a book index) over one or more columns: searches on indexed columns jump from full-table scans O(n) to near O(log n). Cost: indexes take SPACE and SLOW every INSERT/UPDATE/DELETE (each must maintain the index). Index the columns you SEARCH and JOIN on; leave rarely-queried columns alone. 'Why not index everything?' — the write penalty is the answer.
10. Summary — the databases spine
| Question type | Method beat | Trap |
|---|---|---|
| Design | Entities, keys, relationship types | Names are not keys — uniqueness is |
| SQL query | SELECT-FROM-WHERE-ORDER BY | Missing WHERE on UPDATE/DELETE |
| Joins | INNER matches only; LEFT keeps all left rows | 'ALL customers' demands LEFT |
| Aggregation | WHERE before grouping, HAVING after | HAVING is for aggregates only |
| Normalisation | Name the dependency, then split | 1NF lists, 2NF partial, 3NF transitive |
| Integrity | PK unique/not null; FK matches a parent | Cascade deletes are a CHOICE, not a default |
Command discipline: 'write SQL' = correct clauses in order, keywords in caps, conditions explicit; 'explain' = the anomaly or the integrity rule being prevented; 'normalise to 3NF' = state the dependency that forces each split. Database papers pay for the DEPENDENCY and the CONSTRAINT — a table without keys earns nothing.