Run a mock round

A file system stores bytes in files and directories but knows nothing about their structure. A DBMS adds a layer that understands records, types, relationships and constraints, and coordinates concurrent access.

Key advantages of a DBMS:

• Structured queries through SQL, including joins and aggregation, without hand-written parsing code.
• Integrity: primary keys, foreign keys, checks and transactions keep data consistent.
• Concurrency control: many users and transactions read and write safely with isolation guarantees.
• Recovery: write-ahead logging and backups restore a consistent state after a crash.
• Security: fine-grained privileges, roles, auditing and views.
• Abstraction and independence: applications are less coupled to physical storage.

app -> SQL -> DBMS -> storage engine -> disk

A plain file approach can be simpler and faster for tiny or single-writer workloads, such as logs or configuration. But once you need multi-user concurrency, complex queries or crash safety, reimplementing those features in application code is far more error-prone.

ACID describes the guarantees of a transaction, a unit of work that either completes fully or not at all.

• Atomicity: all statements commit or all roll back. Implemented with logging and undo.
• Consistency: the database moves from one valid state to another, respecting constraints, keys and invariants.
• Isolation: concurrent transactions do not observe each other's uncommitted intermediate states; the degree is set by the isolation level.
• Durability: once committed, changes survive a crash, usually by writing to a write-ahead log and fsyncing before acknowledging.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK

The transfer is atomic and consistent, and isolation prevents another transaction from seeing only the debit. Durability is often relaxed slightly for performance, and distributed systems extend the idea with two-phase commit or consensus protocols.

Query optimisation starts by measuring. EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and reports actual rows and time.

Things to look for: sequential scans on large tables, nested loops over big inputs, misestimated row counts, sorts spilling to disk and repeated subplans.

Common improvements:

• Add the right index for filtering and join columns, and consider covering indexes with included columns.
• Keep statistics fresh so the planner estimates well, and avoid functions on indexed columns in predicates.
• Select only needed columns, and avoid SELECT *.
• Rewrite correlated subqueries as joins where appropriate, and reduce the number of joins per query.
• Verify the join order and join types suit the data distribution.

EXPLAIN ANALYZE
SELECT id FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

Indexes speed reads but slow writes and consume space, so balance them. Always verify with real data volumes, because plans on tiny test tables are misleading.

Both approaches handle concurrent updates to the same data.

Pessimistic locking assumes conflicts are likely, so it locks a row when read: SELECT ... FOR UPDATE or a shared lock. Other transactions block until the lock is released at commit or rollback. This prevents lost updates directly but reduces concurrency and risks deadlocks and long waits. It suits high-contention, short transactions.

Optimistic locking assumes conflicts are rare. You read a row with its version or timestamp, do your work, then update with a condition that the version is unchanged.

UPDATE items SET qty = 5, version = version + 1
WHERE id = 7 AND version = 3;
-- 0 rows affected means someone else changed it

If zero rows are affected, you detect the conflict and retry or report it. This avoids holding locks during user think time and scales well, but every write must check the result and handle retries. Many ORMs expose both, and hybrid strategies are common.

Two-phase commit, 2PC, is an atomic commit protocol for transactions spanning multiple databases or resource managers.

Phase one, prepare: the coordinator asks every participant to prepare. Each does the work, writes it to a durable log, acquires locks and votes yes or no.

Phase two, commit: if all voted yes, the coordinator logs the decision and tells everyone to commit; if any voted no, it tells everyone to roll back.

coordinator -> prepare -> all vote yes
            -> commit  -> all commit

Atomicity holds even across a crash, because a participant that voted yes can recover from its log and follow the coordinator's decision. The main problems are blocking: if the coordinator fails after prepare, participants hold locks and wait for it to recover. It is also synchronous and slow.

Consequently, modern distributed systems often prefer consensus-based replication or sagas, which trade isolation for availability and use compensating actions instead of a global atomic commit.

A primary key uniquely identifies each row in a table. It must be unique and not null, and a table has at most one. It may be a single column or a composite of several, and it is usually backed by a unique index. A natural key comes from the data, such as an email or ISBN; a surrogate key is generated, such as an auto-increment id or UUID.

A foreign key is a column, or set of columns, in one table that references the primary key or unique key of another. It enforces referential integrity: a child row cannot reference a parent that does not exist.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id)
);

Deletes and updates on the parent can be restricted, cascaded or set null depending on the rule. Index the foreign key column, because otherwise joins and cascade checks scan the child table. Primary keys define identity; foreign keys define relationships.

Normalisation organises columns into tables to reduce redundancy and update anomalies. The common normal forms:

• 1NF: atomic values, no repeating groups, each row unique.
• 2NF: 1NF plus no partial dependency on part of a composite key.
• 3NF: 2NF plus no transitive dependency, so non-key attributes depend only on the key.
• BCNF: every determinant is a candidate key, a stricter version of 3NF.

Unnormalised: order(id, customer_name, customer_city, product, qty)
3NF: customer(id, name, city)
     product(id, name, price)
     order(id, customer_id)
     order_item(order_id, product_id, qty)

Each fact is stored once, so updating a customer's city touches one row. Normalisation improves integrity and simplifies writes, and it can cost joins on read. Denormalisation deliberately reintroduces redundancy, for example storing a total or a copied name, to speed reads, and must be maintained carefully with triggers or application logic.

These three remove data at very different levels.

DELETE FROM t WHERE ... is DML. It removes selected rows, fires triggers, is fully logged, can be rolled back within a transaction and can cascade to child rows. Without a WHERE clause it removes everything, potentially slowly.

TRUNCATE TABLE t is DDL. It removes all rows quickly by deallocating pages rather than logging each row, resets identity counters in many systems, usually cannot be filtered and typically cannot be rolled back in the same way as DELETE. It is much faster for emptying a table.

DROP TABLE t removes the table definition itself plus its data, indexes, constraints and triggers. The table no longer exists until it is recreated.

DELETE FROM logs WHERE created_at < '2020-01-01';
TRUNCATE TABLE staging_events;
DROP TABLE obsolete_table;

Choose based on whether you need selective removal, speed, or removal of the object itself.

SQL defines four isolation levels that trade consistency against concurrency. They are usually described by which anomalies they permit.

• Read uncommitted: dirty reads allowed, sees uncommitted changes.
• Read committed: no dirty reads, but non-repeatable reads and phantoms can occur. The common default in PostgreSQL and Oracle.
• Repeatable read: rows read stay stable within the transaction, but phantoms may appear. MySQL InnoDB's default, implemented with MVCC.
• Serializable: transactions behave as if run one at a time, preventing all three anomalies, at the cost of blocking or aborts.

level              dirty  non-repeatable  phantom
read uncommitted    yes        yes           yes
read committed      no         yes           yes
repeatable read     no         no            yes
serializable        no         no            no

Implementations vary: MVCC snapshots give readers a consistent view without blocking writers, while lock-based systems block. Higher isolation reduces anomalies but increases contention, deadlocks and retries, so choose per transaction rather than globally.