Appendices

Appendix L. Glossary of Database Terminology

The book's working vocabulary, alphabetical. Each term is defined in one line, with the chapter that teaches it in parentheses. Cross-references are to other glossary entries.

ACID — Atomicity, Consistency, Isolation, Durability: the transaction guarantees (18).

Aggregate function — a function collapsing many rows to one value (COUNT, SUM, AVG, MIN, MAX); skips NULLs except COUNT(*) (11).

Aggregation (EER) — treating a relationship as an entity for further relationships (5).

Alias (AS) — a rename for a table or expression column (10).

Anti-join — rows of A with no match in B, via NOT EXISTS (12).

ANY / ALL — quantified comparison over a set (12).

Anomaly (insert/update/delete) — the failure modes of badly shaped tables: cannot record a fact; must repeat a fact; deleting one fact destroys others (7).

Armstrong's axioms — reflexivity, augmentation, transitivity: the sound-and-complete FD rules (7).

Assertion (SQL) — a cross-table constraint object; dropped by modern platforms, replaced by triggers (7, 22).

Attribute — a named property of an entity or relation; a column (2, 5).

Attribute closure (X⁺) — everything X determines under an FD set; tests keys and dependencies (7).

Autocommit — each statement its own transaction (both platforms' default) (18, 23).

AUTO_INCREMENT — MySQL's per-table generated-key counter (9, 16).

Backup (logical/physical) — rows-as-SQL dumps vs data-directory copies (21).

BCNF — every non-trivial FD's determinant is a superkey (7).

Bridge (junction) table — the table realizing an M:N relationship (6).

Buffer pool — the in-memory page cache (4, 16).

Candidate key — a minimal superkey (2, 7).

CAP theorem — under partition, a system chooses consistency or availability (26).

Cardinality — tuple count of a relation (2); distinct value count of a column (19); the pairing rule of a relationship (5) — context disambiguates.

CASE — SQL's conditional expression (11).

Catalog (data dictionary) — the database's own metadata, stored as tables (4).

CDC (change data capture) — streaming changes from the log for ETL and replication (25).

CHECK constraint — a declarative domain rule; NULL passes (9).

Checkpoint — the flushed-pages point a recovery replay starts from (21).

Clustered index (InnoDB) — the primary key's B-tree is the table (16).

COALESCE — first non-NULL argument (11).

Composite key — a key over multiple columns (2, 6).

Concurrency control — making simultaneous transactions behave serially (18).

Connection pool — bounded reusable connections; small pools beat large (23).

Constraint — a rule the DBMS enforces (9).

Correlated subquery — an inner query referencing the outer row (12).

Covering index — an index answering the query without the table (19).

CTE (Common Table Expression) — a named query staged for one statement (12, 13).

Cursor — row-by-row iteration over a result set — the last resort (22).

Data dictionary — see catalog.

Data independence (logical/physical) — schema and storage evolution invisible to programs (4).

Data model — structure + manipulation + integrity (2).

Database — an organized, shared collection of logically related data (1, 4).

Database (SQL namespace) — an isolated object collection in a server (4).

DBMS — the software that stores, manages, and mediates access to data (1).

DDL / DML / DQL / DCL / TCL — the statement categories: define, change, query, control, transact (8).

Deadlock — an unresolvable wait cycle; a victim is aborted (40P01/40001) (18).

Default privileges — grants applied automatically to future objects (20).

Deferred constraint — a constraint checked at COMMIT (15, 22).

Degree (arity) — attribute count of a relation (2).

Denormalization — deliberate, documented redundancy for a workload (7, 25).

Derived table — a subquery in FROM with an alias (12).

Dirty read — reading another transaction's uncommitted data (18).

Dirty write — overwriting uncommitted data (forbidden everywhere) (18).

Discriminator (partial key) — the weak entity's within-owner identifier (5).

Division (÷) — "paired with every" — the all/every operation (3).

Domain — the set of values an attribute draws from; also PostgreSQL's named type+constraint (2, 15).

Domain constraint — a rule bounding a column's values (6, 9).

ETL / ELT — extract-transform-load; load-raw, transform-in-warehouse (25).

Entity / entity type / entity set — a thing; its description; its current instances (5).

Entity integrity — primary keys are unique and non-NULL (2).

Equi-join / theta join / natural join — equality join; any-predicate join; shared-names join (3, 12).

ERD — the entity-relationship diagram (5, F).

Event (MySQL) — calendar-scheduled stored SQL (16, 22).

Exclusion constraint — PostgreSQL's declarative non-overlap rule (29).

EXPLAIN / EXPLAIN ANALYZE — the plan; the plan with actual timings (19).

Expression index — an index on a function result (15, 19).

Fact table / dimension table — the warehouse's events and nouns (25).

FD (functional dependency) — X determines Y: a promise about every legal instance (7).

Foreign key — an attribute set referencing another table's key (2, 6).

Frame (window) — the rows a window function sees (13).

Fact grain — the declared meaning of one fact row (25).

FULL JOIN — both sides' unmatched rows preserved (12).

Functional dependency closure — see attribute closure.

Generalization / specialization — abstraction to a supertype / split into subtypes (5).

Generated column / identity column — computed stored column / standard generated key (9, 15).

GiST / GIN / BRIN / SP-GiST — PostgreSQL index access methods beyond B-tree (15, 19).

GRANT / REVOKE — bestow / remove a privilege (8, 20).

Grain (warehouse) — see fact grain.

GROUP BY / HAVING — partition for aggregation / filter the groups (11).

Hash index — equality-only index structure (15, 19).

Heap (table storage) — unordered row storage (4, 16).

Historical price (unit_price) — order-line redundancy justified by temporal correctness (6, 29).

Index — a sorted search structure; costs writes, buys lookups (19).

Index-only scan / Using index — covering-index verdicts (PG / MySQL) (19).

Inline vs. table-level constraint — on the column line vs. named on the table (9).

Insert ... SELECT — loading a table from a query (10).

Isolation level — the anomaly-prevention setting: RU, RC, RR, SERIALIZABLE (18).

Join — the data-combining operation: selection over a product (3).

Join dependency (5NF) — lossless reconstruction from three or more projections (7).

Key — an attribute set identifying tuples (2).

Keyset (seek) pagination — paging after the last key seen (10, 24).

Last_insert_id / RETURNING — generated-key retrieval (MySQL / PG) (16, 23).

Left join — every left row preserved, NULL-padded (12).

Least privilege — every account gets exactly the access its function needs (1, 20).

Lock (shared/exclusive) — concurrent readers permitted / one writer only (18).

Lock (gap/next-key) — InnoDB's phantom-preventing range locks (18).

Lossless decomposition — R = R1 ⋈ R2 exactly; tested by the shared superkey (7).

Lost update — the second writer's stale write erases the first (18).

Materialized view — a stored query result, refreshed on demand (13, 15).

Metadata — data describing data; held in the catalog (1, 4).

MVCC — multi-version storage: readers never block writers (18).

MVD (multivalued dependency) — X determines a set of Y values, independent of the rest (7).

N+1 problem — one query per row where one join would do (19, 23, 24).

Natural key / surrogate key — real-world identity / system-generated identifier (6).

Non-repeatable read — same row, different value within a transaction (18).

NOT IN trap — a NULL in the list makes every row UNKNOWN (10).

Normal forms (1NF–5NF) — the shape rules for tables; see Chapter 7.

NULL — the marker for value absent/unknown; three-valued logic applies (2, 10).

NULLIF — NULL when the two arguments are equal (11).

OLTP / OLAP — by-key transactional / scan-shaped analytical workloads (25).

Outer join — see LEFT/RIGHT/FULL JOIN.

OVER (PARTITION BY ... ORDER BY ...) — window function grammar (13).

Partial index — an index over a WHERE subset (15, 19).

Participation (total/partial) — whether every instance must join a relationship (5).

Partitioning (table/sharding) — storage slices pruned per query / rows across machines (25, 26).

pg_hba.conf — PostgreSQL's authentication policy file (14, 20).

Phantom read — same predicate, different row set within a transaction (18).

Plan (execution plan) — the operator tree the optimizer chose (19).

Pooler (PgBouncer/ProxySQL) — server-side connection multiplexers (23).

Predicate — a condition evaluating to TRUE/FALSE/UNKNOWN (10).

Prepared statement — structure and data separated; injection-proof (20, 23).

Primary key — the chosen candidate key; identity (2, 9).

Projection (π) — the column-selecting operation (3).

Query — a request to retrieve or manipulate data (1).

RBAC — role-based access control: roles hold privileges, members hold roles (20).

Read-your-writes — a user always sees their own committed changes (26).

Recursive CTE — seed + UNION ALL + step + stop (12).

Referential action — ON DELETE/UPDATE CASCADE, RESTRICT, SET NULL, SET DEFAULT (6, 9).

Referential integrity — foreign keys match existing keys or are NULL (2).

Relation / tuple / attribute / domain — table / row / column / value-set (2).

Relation schema / instance — intension (structure) / extension (content) (2).

Relational algebra / calculus — the procedural / declaratory formal languages; equivalent (3).

Relational completeness — able to express every algebra query (3).

Replication (sync/async, streaming/logical) — keeping second copies fed (21, 26).

REST / resource — URLs-as-nouns, methods-as-meanings over the service tier (24).

Retry loop — catch 40001/40P01, back off, re-execute the whole transaction (18, 23).

RLS (row-level security) — per-role row policies enforced in the engine (20).

ROLLBACK / SAVEPOINT — undo a transaction / a named rewind point (18).

Row estimate error — planner estimate vs. actual rows; the root of most bad plans (19).

Saga — local transactions chained by compensations (26).

Sargable — index-usable predicate: no function wrapped around the column (19).

Schedule — an interleaving of concurrent operations (18).

Schema (SQL namespace) — a named object collection inside a database (4).

Schema (database) — the declared structure and rules of a database (2).

SCD (slowly changing dimension) — Type 1 overwrite / Type 2 history rows / Type 3 previous-plus-current (25).

Selectivity / cardinality (planner) — matched fraction / distinct values driving cost (19).

Self join — a table paired with itself under aliases; needs a symmetry breaker (12, 3).

SERIALIZABLE — perfect isolation; conflicts surface as 40001 (18).

Set operations (UNION/INTERSECT/EXCEPT) — row-wise combination of compatible queries (3, 13).

Sharding — rows across machines by shard key (26).

Snapshot (MVCC) — the set of transactions visible to a statement (18).

SQL — Structured Query Language (8).

SQLSTATE / error classes — 23505 unique, 23503 FK, 40001 serialization, 40P01 deadlock, 45000 user (22, 23).

Statistics (planner) — the facts (row counts, n_distinct, histograms) plans are priced from (19).

Stored procedure / function / trigger — callable program / SQL-callable computation / event-attached program (22).

Superkey — any uniquely identifying attribute set (2).

Three-schema architecture — external / conceptual / internal levels (4).

Three-tier architecture — browser / application server / database server (4).

Transaction — the all-or-nothing unit of work (18).

Trigger (BEFORE/AFTER, FOR EACH ROW) — the always-on rule layer (22).

Truncate — fast, WHERE-less emptying; FK-guarded (9).

Union compatibility — same columns and types for set operations (3).

Unique constraint — natural identity enforced; an index in disguise (9).

Upsert — insert-or-update; ON CONFLICT / ON DUPLICATE KEY (10, 17).

User-defined function (UDF) — a SQL-callable stored computation (22).

View — a stored query presented as a table (9, 13).

VACUUM / autovacuum — dead-version reclamation in PostgreSQL (18, 21).

WAL / redo log / binary log — the change logs driving recovery, replication, PITR (4, 21).

Window function — aggregation across rows without collapsing them (13).

WITH CHECK OPTION — a view refusing writes that leave its scope (13).

Wraparound (transaction ID) — PostgreSQL's finite-counter maintenance horizon (21).

XID / timeline / GTID — transaction identities and replay guards (21, 26).

2PC (two-phase commit) — prepare-then-commit distributed atomicity (26).

3NF synthesis (Bernstein) — the lossless, dependency-preserving decomposition algorithm (7).