What's changed: Initial version
3.3Databases (Relational Model, Normalization, SQL, Transactions, Concurrency Control, Failure Recovery)
Learn the relational model, which handles data as tables; normalization (first through third normal form), which eliminates redundancy and update anomalies; SQL (SELECT, JOIN, GROUP BY) for manipulating tables; the primary key, which uniquely identifies a row, and the foreign key, which references another table; the transaction, which groups multiple operations as a unit (the ACID properties); concurrency control (locking, deadlock) for managing simultaneous access; rollback and roll-forward for recovering from failures; and where NoSQL fits in.
Databases are a consistently tested, important area in FE Subject A. The four pillars—why and how normalization works, basic SQL syntax, maintaining consistency under simultaneous access, and failure-recovery procedures—are best understood not as rote terminology but as design decisions with reasons behind them; that is the fastest path to exam points.
3.3.1The relational model and normalization
- The relational model treats data as tables (relations) consisting of rows and columns; each row represents one data item, each column an attribute. Tables are related to each other through common columns (keys).
- Normalization is a table-splitting design technique that prevents data duplication and update-time inconsistency (update anomalies). First normal form (1NF) eliminates repeating groups (multiple values in one cell), making every cell hold a single value. Second normal form (2NF) additionally separates out columns that depend on only part of the primary key (partial functional dependency) into another table—an issue that arises with composite primary keys.
- Third normal form (3NF) additionally separates out columns that depend on a non-key column (transitive functional dependency)—e.g., if an orders table holds not just "customer ID" but also "customer name," the customer name depends transitively on the customer ID rather than directly on the primary key, and should be split out. The further normalization goes, the fewer update anomalies remain, but there is a tradeoff: more JOINs are needed, which can reduce query performance.
3.3.2SQL basics and primary/foreign keys
- The SELECT statement retrieves data from a table (
SELECT name FROM employees WHERE dept = 'sales'). JOIN combines multiple tables (an inner join keeps only matching rows; an outer join also keeps unmatched rows, filling with NULL). GROUP BY groups rows by the value of specified columns, used together with aggregate functions (count, sum, etc.). - The primary key is the column that uniquely identifies a row in a table (no duplicates, no NULLs allowed). The foreign key is a column that references another table's primary key, guaranteeing referential integrity by preventing registration of a value that does not exist in the referenced table (e.g., an order table's "customer ID" may only hold values that actually exist in the customer table).
3.3.3Transactions, concurrency control, and failure recovery
- A transaction is a sequence of operations handled as a unit under the rule "either all of it executes, or none of it does" (e.g., a bank transfer: the withdrawal and the deposit either both succeed or both fail). The ACID properties: Atomicity—an all-or-nothing outcome; Consistency—data integrity is preserved after execution; Isolation—unaffected by interference from other transactions running concurrently; Durability—the result of a successfully completed transaction survives even a subsequent failure.
- Concurrency control is the mechanism that prevents inconsistency when multiple transactions access the same data simultaneously. A lock secures temporary exclusive rights to data (a shared lock for reading, an exclusive lock for updating). Deadlock—a state where two transactions each wait forever for the other to release a lock it holds, and neither can proceed (resolved by forcibly rolling back one of them).
- Recovery from a failure is performed using logs (before-images and after-images). Rollback undoes the updates of an abnormally terminated transaction using before-images, restoring the state before it began (used for transaction failures). Roll-forward reapplies after-images recorded since the last backup, restoring the state up to just before the failure (used when recovering from a backup after something like a disk failure). The direction—rollback undoes, roll-forward reapplies—is the most frequently tested distinction.
- NoSQL broadly covers databases not bound to the relational (tabular) model: key-value stores (simple key-value pairs), document stores (semi-structured data such as JSON), graph databases (representing nodes and relationships), and more. They are chosen as an alternative to, or alongside, relational databases when large-scale distributed processing or a flexible schema is needed.
The staples: normalization prevents update anomalies but going further increases JOINs and can hurt performance; a foreign key guarantees referential integrity; ACID = atomicity/consistency/isolation/durability; deadlock is a standstill from each side waiting on the other's lock; rollback undoes using before-images, roll-forward reapplies using after-images. Questions asking you to judge whether rollback or roll-forward applies to a transaction failure versus a disk failure are also classic.
Consider designing an order-management system and trace the flow from normalization through transactions. Suppose the initial "orders" table crams order ID, customer ID, customer name, product name, quantity, and unit price all into one table. Every time the same customer places another order, the customer name gets recorded again, redundantly—and if the customer wants to change their name, failing to update every single order row consistently causes an inconsistency (an update anomaly). Since the customer name depends on the customer ID rather than the primary key (order ID), this violates third normal form; splitting off a customers table (customer ID, customer name) and giving the orders table a customer ID as a foreign key resolves it. Likewise, if one order can contain multiple products, cramming product name, quantity, and unit price into one order row as repeating items violates first normal form and requires splitting into an order-details table. After normalization, retrieving an order requires combining JOIN with GROUP BY, as in SELECT o.id, c.name, sum(d.quantity * d.unit_price) FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_details d ON o.id = d.order_id GROUP BY o.id, c.name;, to reconstruct the same information the single-table design once held directly. Next, consider "confirm order" processing: decrementing stock and inserting the order record must both succeed together, since if only one of the two succeeds, stock and orders fall out of consistency—this must be guaranteed atomic as one transaction. If two staff members try to update the same product's stock row simultaneously, one acquires the concurrency-control lock while the other waits for it to release. If two transactions end up each waiting on a lock the other holds, a deadlock results, and the DBMS resolves it by forcibly rolling back one of them. Finally, if the system terminates abnormally mid-operation, the interrupted transaction's updates are undone via rollback using before-images; whereas if the hard disk itself fails and recovery must proceed from a backup, after-images recorded since the backup was taken are reapplied via roll-forward to restore the state up to just before the failure.
| Term | Direction/role | When used |
|---|---|---|
| Rollback | Undoes using before-images | Abnormal transaction termination |
| Roll-forward | Reapplies after-images to move forward | Disk-failure recovery from a backup |
| Primary key | Uniquely identifies a row | No duplicates, no NULLs |
| Foreign key | References another table's primary key | Guarantees referential integrity |
Trap: "More normalization is always better" is wrong—normalizing further reduces update anomalies but trades off against more tables, more JOINs, and potentially lower query performance; real-world systems sometimes denormalize intentionally. Also, "rollback and roll-forward mean the same thing" is wrong: rollback undoes using before-images, while roll-forward reapplies using after-images—opposite directions. The idea that "deadlocks would not happen if locks were never used" is also wrong: abandoning concurrency control entirely would instead cause data inconsistency, so deadlocks are instead handled through detection-and-resolution mechanisms (timeouts, forced rollback).
3.3.4Section summary
- Normalization (1NF -> 2NF -> 3NF) reduces duplication/update anomalies but trades off against more JOINs. Primary key = unique identification; foreign key = referential integrity
- ACID (atomicity/consistency/isolation/durability) guarantees transactions. A standoff between concurrency-control locks causes deadlock
- Rollback = undo via before-images (transaction failure); roll-forward = reapply via after-images (backup recovery). NoSQL is an alternative not bound to the tabular model
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. An orders table recorded customer ID, customer name, and product name all in one row, so the customer name was duplicated every time the same customer ordered again, and forgetting to update some rows on a name change caused inconsistency. Which design change resolves this?
Q2. Transaction A and transaction B each keep waiting for the other to release a lock it holds, and neither can proceed. What is this state called, and what is the DBMS's typical response?
Q3. The disk device storing the database itself has failed, and recovery must proceed from the most recent backup. Which operation reflects the updates made since the backup was taken?