Reference: DBMS Glossary

A

ACID

Atomicity, Consistency, Isolation, Durability — the four guarantees a transaction manager makes. Atomicity and durability come from the recovery manager; isolation from concurrency control; consistency is partly the system's, partly your schema design.

Assertion

A predicate that must hold on the entire database at all times, not just one table. Standardized in SQL but implemented almost nowhere — express it with triggers instead.

Attribute

A column of a relation; each draws its values from a domain. Avoid: field (file-system vocabulary).

Authorization

The privileges (select/insert/update/delete, optionally per-column) granted to users or roles via grant/revoke. Distinct from integrity: integrity guards against authorized users corrupting data, security against unauthorized access.

Atomicity

A transaction's writes are indivisible — all take effect or none do; a crash mid-transfer cannot leave one account debited and the other not credited.

B

Bag

A set that permits duplicate members. SQL tables are bags by default; the formal relational model is strictly set-based, which is why algebra projection eliminates duplicates and SQL needs distinct.

Base query (recursion)

The nonrecursive half of a recursive view definition; it seeds the fixed-point iteration, while the recursive step expands one level per iteration.

Buffer manager / buffer pool

The layer that caches disk pages in memory and decides which to evict, so upper layers read and write as if data were always resident.

C

Candidate key

A minimal superkey — uniquely identifies tuples and contains no redundant attribute. One candidate is chosen as primary; the rest are just alternates.

Check constraint

A row-level predicate (check(P)) that a table's tuples must satisfy; satisfied unless it evaluates to false, so null slips through.

Cross-tabulation

A table derived by turning one attribute's values into columns, aggregating cells where multiple rows contribute. Avoid: pivot-table, crosstab — same thing; SQL standardizes the keyword as pivot.

Cursor

Embedded-SQL construct for iterating a query result tuple at a time; can also update or delete its current row. JDBC's ResultSet plays the same role in dynamic SQL.

D

Data dictionary

The database's own metadata store — relations, types, constraints — consulted before most operations. Avoid: system catalog (common synonym).

DDL (data-definition language)

The SQL subset that declares schema and constraints (create, alter, drop); its output lands in the data dictionary.

DML (data-manipulation language)

The query/update subset. Declarative DML (SQL) states what to return and leaves how to the optimizer; procedural DML would make you spell out both.

Data independence

Applications survive changes to lower levels without rewrite. Physical independence (storage layout changes) is what every DBMS delivers; logical independence (conceptual schema changes) is rarer and harder.

Dense rank

Ranking function that numbers ties equally without gaps — after two rank-1 students comes rank 2, not rank 3 as plain rank() gives.

Domain

The permitted values for an attribute; atomic if its elements are treated as indivisible.

Durability

Once a commit is acknowledged, its effects survive any subsequent crash or power failure — enforced by the recovery manager.

E

Embedded database

A full database engine linked into the application process (SQLite, HSQLDB) rather than run behind a server. Not to be confused with embedded SQL, which is a way to reach a server.

Embedded SQL

SQL statements inside host-language source (EXEC SQL) translated by a preprocessor before compilation — compile-time checking, but dynamic SQL won the ecosystem.

F

Fixed point

The instance a recursive view converges to when an iteration adds no new tuples; the view is defined as exactly those tuples.

Foreign key

Attributes in one relation whose values must appear as a primary key (or unique set) in another; enforcing this is referential integrity, with on delete cascade/set null as repair options.

G

grouping()

Function returning 1 exactly for nulls generated by rollup/cube groupings — use it, not coalesce, when displaying 'all' subtotal rows, since coalesce also rewrites genuine nulls.

Grouping sets

group by grouping sets ((a,b),(c)) — lists exactly the desired groupings, unlike rollup (prefixes) and cube (all subsets).

I

Index

Physical-schema structure that speeds lookups on attributes; optional for correctness, vital for performance — including foreign-key checks.

Instance

The actual data in the database at a moment — constantly changing, like a value of a program variable. Avoid: "snapshot"; instance contrasts with schema, the stable design.

Integrity constraint

A predicate the data must always satisfy (keys, not-null, check, foreign keys, assertions); any update violating one is rejected. See Authorization for the contrast with security.

Isolation

Concurrent transactions behave as if executed serially — no transaction observes another's intermediate state; delivered by the concurrency-control subsystem.

J

JDBC

Java's standard API for dynamic SQL: connect, ship statements, iterate ResultSets. Defines the interface only; vendor drivers supply protocol specifics.

K

Key

Attribute set uniquely identifying a tuple. Superkey = unique (possibly redundant); candidate key = minimal superkey; primary key = the chosen candidate, conventionally listed first and underlined in diagrams. Avoid: "identifier" loosely — reserve ID-talk for primary keys.

L

LOB locator

Handle returned for blob/clob values that streams the large object from storage on demand instead of materializing it in memory.

M

Materialized view

A view whose result is physically stored and must be kept current by the system (view maintenance). Trades freshness-management cost for fast reads.

Metadata features

API facilities (JDBC's ResultSetMetaData/DatabaseMetaData, ODBC catalog functions) for discovering schemas and result shapes at runtime — the basis of generic browsers and tools.

Monotonic (query)

Property that adding input tuples never shrinks the output; required of recursive-query bodies, banning aggregation, not-exists, and except over the recursive view.

N

Natural join

Join equating ALL shared attribute names. Convenient but fragile: a newly added shared column silently changes matching semantics — prefer using/explicit on.

Null

Placeholder for unknown or absent value; comparisons yield unknown (the third truth value), aggregates ignore it, and ranking treats it as highest unless told otherwise. Eliminate it where you can.

O

ODBC

The C-origin standard API for dynamic SQL (now wrapped by most languages); defines conformance levels, with the SQL CLI standard closely mirroring it.

Optimizer (query)

Component rewriting a declarative query into an efficient evaluation plan; exists because relational algebra offers many equivalent expressions with wildly different costs.

P

Prepared statement

Statement compiled once with ? parameter placeholders bound later. Preferred always for user input: binding escapes values automatically, closing the SQL-injection hole.

Primary key

The candidate key the designer designates as the relation's identifier; implicitly not null and unique. Choose stable attributes (ID), not mutable ones (name, address).

PSM (Persistent Storage Module)

The SQL standard's procedural extension — variables, compound statements, loops, condition handlers — behind stored functions and procedures. Vendor dialects (PL/SQL, T-SQL, PL/pgSQL) diverge heavily from it.

Q

Query processing

Pipeline of parser, translator, optimizer, and evaluation engine turning SQL text into results; the DDL interpreter and DML compiler feed it.

R

Recursive view

View defined in terms of itself as base-query union recursive-step; evaluates to a fixed point. Expresses transitive closure (prerequisites, bill-of-materials, reachability).

Referential integrity

Guarantee that every foreign-key value matches some referenced key; violations reject the change or trigger cascade/set-null repairs.

Relation

A set of tuples over named attributes — the formal model's table, unordered, duplicate-free. Avoid: "table" for the formal concept; SQL tables are bags, not sets.

Relational algebra

The functional query calculus (select σ, project π, join ⋈, union/except/intersect) under SQL; equivalence between expressions is what query optimization exploits.

Rollup / cube

Generalized group by: rollup generates one grouping per attribute prefix plus the empty grouping; cube generates every subset. Absent attributes surface as generated nulls.

S

Schema

The database's structural design — declarations of relations, types, constraints — stable like a variable's type declaration; rarely changed compared to instances.

SQL injection

Attack where crafted user input alters query semantics (' or 'Y'='Y returns whole tables). Prevented by prepared statements; string concatenation of user input is never acceptable.

Stored procedure

Named procedure stored and executed inside the database (call), centralizing business logic so every application shares one copy and one point of change.

Superkey

Any attribute set that uniquely identifies tuples; may contain redundant attributes, unlike a candidate key.

T

Table function

Function returning a whole relation, usable in from; effectively a parameterized view.

Temporary table

Table created within a transaction, visible only to it (concurrent callers get private copies) and dropped when the transaction ends; workhorse of iterative transitive-closure code.

Transaction

Unit of work executing with ACID guarantees, delimited by commit/ rollback; under autocommit each statement is its own transaction.

Transition variable / transition table

Trigger-side references to affected rows: referencing new/old row as for row triggers, new/old table as for statement triggers (after-only).

Trigger

Event-condition-action rule the system fires automatically on modifications. Use for rules SQL constraints can't express; avoid where built-ins exist (cascade, materialized views, replication), and mind chain reactions.

U

Updatable view

View accepting inserts/updates — only when defined from one base relation over bare attributes with nullable omissions and no grouping; with check option rejects rows that wouldn't satisfy the view.

V

View

Virtual relation defined by a stored query, recomputed at each use — not a stored copy. Doubles as a security boundary: grant access to the view, hide the base tables.

W

Window function

Aggregate/ranking computed over a sliding frame of ordered tuples (

over (order by ...
        rows N preceding)

) without collapsing rows; overlapping frames are exactly what moving averages need. Related: partition by ranks within groups, ntile(n) buckets ordered tuples.

with clause

Defines a temporary named relation scoped to a single query — a local view, not a persistent one; add recursive to let the definition reference itself.