Relational Databases¶

A relational database represents data as relations constrained by a schema. SQL operates on sets or multisets of rows; result order is unspecified without an ORDER BY clause.

Keys and constraints¶

a primary key identifies each row;
a foreign key enforces a relationship to a candidate key;
unique, check, and not-null constraints protect invariants near the data;
application validation improves feedback but does not replace database constraints under concurrency.

Normalization¶

Normalization decomposes relations to reduce redundancy and update anomalies. Functional dependencies determine whether a decomposition satisfies forms such as 3NF or BCNF. Denormalization is an evidence-based performance trade-off that adds synchronization obligations; it is not a substitute for schema design.

Indexes¶

An index trades storage and write maintenance for access paths. A composite B-tree index can support searches using a useful prefix of its key order, plus some range and ordering patterns. Whether an index is selected depends on statistics, selectivity, predicates, joins, ordering, and the optimizer.

Use EXPLAIN or the database's plan facility with representative data. “The column is indexed” does not prove the query is efficient. The query-optimization guide connects plans and indexes to ORM fetching and regression tests.

Query design¶

select only required columns;
make pagination order deterministic;
prefer set-based operations over per-row application round trips;
understand how NULL affects three-valued logic;
parameterize values rather than constructing SQL text;
inspect actual cardinalities and plans for important queries.

Exercises¶

Identify an update anomaly in a denormalized relation.
Design an index for equality on one column and range on another.
Explain why offset pagination slows and shifts under concurrent inserts.