Database glossary

217 Database terms, from Novice to Master. Start with the basics or jump to a level.

Updated September 28, 2026

Novice

ASC: An ORDER BY direction that sorts from lower values to higher values.
CHECK Constraint: A constraint requiring inserted or updated rows to satisfy a search condition.
Column: A named part of a table that holds values from a defined data type or domain.
Column Alias: A temporary output name assigned to a selected expression or column.
Comparison Predicate: A condition comparing values with operators such as =, <>, <, or >=.
Constraint: A rule the database enforces to protect valid and consistent data.
Data Type: A rule defining which values a column or expression may contain and which operations apply.
Database: An organized collection of data that software can store, retrieve, and update.
DEFAULT Clause: A clause providing a value when an INSERT omits the column.
DESC: An ORDER BY direction that sorts from higher values to lower values.
DISTINCT: A SELECT option that removes duplicate rows from a result.
Domain: A permitted set of values from which an attribute draws its data.
Foreign Key: Columns whose values must match a referenced unique or primary key, subject to NULL rules.
FROM: A query clause identifying the source tables, views, or derived tables.
Key: One or more attributes used to identify rows or connect related relations.
Logical Predicate: A condition that combines or reverses other conditions with AND, OR, or NOT.
Many-to-Many Relationship: A relationship where rows on both sides can relate to many rows on the other side.
NOT NULL Constraint: A constraint requiring a column to contain a non-NULL value.
NULL: A marker indicating that a value is missing or unknown, rather than zero or an empty string.
Numeric Literal: A number written directly in a SQL statement.
One-to-Many Relationship: A relationship where one parent row can relate to many child rows.
One-to-One Relationship: A relationship where each row can relate to at most one row on the other side.
ORDER BY: A clause that sorts a query result by one or more expressions.
Primary Key: The chosen candidate key that uniquely identifies every row and cannot contain NULL.
Query: A request for data or another result from a database.
Relation: A set of tuples sharing the same named attributes in the relational model.
Relational Database: A database that represents data as relations commonly presented as tables.
Relational Database Management System (RDBMS): Software that creates, protects, queries, and updates relational databases.
Relationship: A meaningful association between entities represented through keys and relations.
Result Set: The rows and columns produced by a query.
Row: One record in a table containing a value for each column.
Schema: The named structure defining database objects such as tables, views, and constraints.
SELECT: A statement or query clause that chooses expressions and columns for a result.
Snake Case: All letters are lower case, each word is separated by an underscore.
SQL Statement: A complete SQL instruction sent to a database management system.
String Literal: Text written between single quotation marks in SQL.
Structured Query Language (SQL): The standardized language for defining, querying, and changing relational data.
Table: The common SQL representation of a relation, arranged in rows and columns.
UNIQUE Constraint: A constraint preventing duplicate values or value combinations in specified columns.
Unique Key: One or more columns constrained so duplicate combinations are not allowed.
WHERE: A query clause keeping only rows for which its condition is true.

Apprentice

Aggregate Function: A function that summarizes a set of rows into one value.
ALTER TABLE: A statement that changes the definition of an existing table.
Approximate Numeric Type: A floating-point numeric type that may store an approximation of a value.
AVG: An aggregate function returning the average of non-NULL numeric values.
BETWEEN Predicate: A predicate testing whether a value falls within an inclusive lower and upper bound.
CASE Expression: An expression that chooses a result by evaluating conditions in order.
Character String Type: A SQL type whose values are sequences of characters.
COUNT: An aggregate function returning the number of qualifying rows or non-NULL expression values.
CREATE TABLE: A statement that defines a new table, its columns, and its constraints.
CREATE VIEW: A statement that defines a view from a query.
CROSS JOIN: A join returning every possible pairing of rows from two table expressions.
Data Control Language (DCL): SQL statements that control database privileges and access.
Data Definition Language (DDL): SQL statements that define or change database structures.
Data Manipulation Language (DML): SQL statements that retrieve or change stored rows.
Date and Time Type: A SQL type representing a date, time, timestamp, or interval.
DELETE: A statement that removes selected rows from a table.
DROP TABLE: A statement that removes a table definition and its stored data.
Exact Numeric Type: A numeric type representing values with defined precision and scale without binary approximation.
EXCEPT: A set operation returning distinct rows in the first result but not the second.
EXISTS Predicate: A predicate that is true when its subquery returns at least one row.
FETCH FIRST: A standard clause limiting a query result to its first specified number of rows.
FULL OUTER JOIN: A join returning matched rows plus unmatched rows from both sides.
GROUP BY: A clause collecting rows with equal grouping values so each group produces one result row.
HAVING: A clause filtering groups after grouping and aggregation.
IN Predicate: A predicate testing whether a value equals any value in a list or subquery result.
INNER JOIN: A join returning only row combinations that satisfy its join condition.
INSERT: A statement that adds new rows to a table.
INTERSECT: A set operation returning distinct rows present in both query results.
IS NULL Predicate: A predicate testing whether a value is NULL.
JOIN: A query operation combining related rows from two table expressions.
Join Condition: The predicate deciding which rows from joined sources match.
LEFT OUTER JOIN: A join returning every left row and matching right rows, using NULLs when no right match exists.
LIKE Predicate: A predicate comparing text with a pattern containing wildcard characters.
MAX: An aggregate function returning the highest non-NULL value.
MIN: An aggregate function returning the lowest non-NULL value.
OFFSET Clause: A clause skipping a specified number of ordered result rows.
ON Clause: A join clause containing its match condition.
RIGHT OUTER JOIN: A join returning every right row and matching left rows, using NULLs when no left match exists.
Scalar Subquery: A subquery required to return one column and no more than one row.
Self Join: A query that joins a table to another reference to the same table.
SET Clause: The UPDATE clause assigning new values to columns.
Set Operation: An operation combining compatible query results according to set rules.
Subquery: A query nested inside another SQL statement.
SUM: An aggregate function adding the non-NULL numeric values in a group.
Table Subquery: A subquery producing rows and columns that another query can use as a table.
Transaction Control Language (TCL): SQL statements that complete, reverse, or mark points within transactions.
UNION: A set operation combining compatible query results and removing duplicate rows.
UNION ALL: A set operation combining compatible query results while retaining duplicate rows.
UPDATE: A statement that changes values in existing rows.
VALUES Clause: A clause that supplies rows of literal or calculated values.
View: A named query whose result can be used much like a table.
Wildcard Character: A pattern character representing unknown text, such as % or _ with LIKE.

Journeyman

ACID Compliance: Atomic, Consistent, Isolated, Durable
ACID Properties: Atomicity, consistency, isolation, and durability: four goals for reliable transactions.
Alternate Key: A candidate key not selected as the primary key.
Associative Entity: An entity representing a relationship and optionally storing attributes about that relationship.
Atomicity: The property that all work in a transaction succeeds together or none of it takes effect.
Boyce-Codd Normal Form (BCNF): A normal form requiring every determinant of a nontrivial dependency to be a superkey.
Candidate Key: A minimal set of attributes that uniquely identifies every tuple in a relation.
Clustered Index: The index that decides the order rows are stored in; one per table.
COMMIT: A statement that successfully ends a transaction and makes its changes durable.
Common Table Expression (CTE): A named query expression introduced by WITH for use within one statement.
Composite Key: A key made from two or more attributes.
Consistency: The property that a successful transaction preserves declared database rules.
Correlated Subquery: A subquery that refers to values from an outer query and is evaluated in that context.
Delete Anomaly: A design problem where deleting one fact unintentionally removes another fact.
Denormalization: Deliberately adding controlled redundancy to support a measured performance or reporting need.
DENSE_RANK: A window function assigning equal rank to peers without leaving gaps after ties.
Determinant: The attribute set on the left side of a functional dependency.
Durability: The property that committed changes survive a later failure.
Entity Integrity: The rule that every relation has a primary key whose values are unique and non-NULL.
First Normal Form (1NF): The normal form requiring each attribute value to be a single value from its domain.
Functional Dependency: A rule where one set of attributes determines another set of attributes.
Index: An auxiliary data structure that helps an RDBMS locate rows without scanning every row.
Insert Anomaly: A design problem where one fact cannot be stored without supplying an unrelated fact.
Isolation: The property controlling how concurrent transactions can observe one another's work.
Junction Table: A table that represents a many-to-many relationship through foreign keys.
LAG: A window function returning a value from an earlier row in the window partition.
LEAD: A window function returning a value from a later row in the window partition.
Natural Key: A key formed from meaningful values that already belong to the modeled domain.
Nonclustered Index: A separate sorted lookup structure that points back to the rows.
Normalization: Organizing relations to reduce redundancy and prevent modification anomalies.
OVER Clause: The clause defining the row window used by a window function.
Parameterized Query: A query keeping data values separate from SQL text through parameters.
PARTITION BY: An OVER subclause dividing query rows into independent window-function groups.
Prepared Statement: A parsed statement template executed later with supplied parameter values.
RANK: A window function assigning equal rank to peers and leaving gaps after ties.
READ COMMITTED: An isolation level preventing a statement from reading uncommitted changes.
READ UNCOMMITTED: The isolation level allowing the broadest concurrency and permitting dirty reads.
Recursive CTE: A CTE that repeatedly refers to its own accumulated result until no new rows are produced.
Referential Integrity: The rule that foreign-key values must reference an existing allowed key or be NULL when permitted.
REPEATABLE READ: An isolation level preventing reread rows from changing during the transaction.
ROLLBACK: A statement that reverses a transaction's uncommitted changes.
ROW_NUMBER: A window function assigning consecutive numbers to rows within each window partition.
SAVEPOINT: A named point inside a transaction to which later work can be rolled back.
Second Normal Form (2NF): First normal form with no non-key attribute dependent on only part of a candidate key.
SERIALIZABLE: The strongest standard isolation level, requiring results consistent with some serial order.
SQL Injection: A vulnerability where untrusted input changes the intended structure of a SQL statement.
Stored Procedure: A named, saved set of SQL statements with parameters.
Superkey: Any attribute set that uniquely identifies every tuple, including sets with unnecessary attributes.
Surrogate Key: A key introduced solely to identify rows and carrying no domain meaning.
Third Normal Form (3NF): Second normal form with non-key attributes depending on keys rather than other non-key attributes.
Transaction: A logical unit of database work completed or reversed as a whole.
Transaction Isolation Level: A setting defining which effects of concurrent transactions may be observed.
Trigger: Code that runs automatically when a table's rows change.
Unique Index: An index that also prevents duplicate indexed value combinations.
Update Anomaly: A design problem where repeated copies of one fact can become inconsistent during an update.
Window Frame: The subset of rows around the current row used by a window function.
Window Function: A function calculating across related rows without collapsing them into one row per group.

Freelancer

Attribute: A named characteristic describing an entity or relation.
Attribute Closure: The attributes functionally determined by an attribute set under known dependencies.
Cardinality: The number of rows in a relation or the allowed counts in a relationship, depending on context.
Cost-Based Optimization: Choosing a plan by comparing estimated resource costs of alternatives.
Cursor: Steps through query results one row at a time; usually a last resort.
Deadlock: A cycle where transactions wait indefinitely for resources held by one another.
Deferrable Constraint: A constraint whose checking may be postponed until transaction completion.
Dependency Preservation: A decomposition property allowing original dependencies to be enforced without joining relations.
Dirty Read: Reading a change made by another transaction before that transaction commits.
Entity: A distinguishable thing or concept about which data is stored.
Entity-Relationship Model: A conceptual model describing entities, attributes, and relationships.
Exclusive Lock: A lock preventing other transactions from taking conflicting read or write locks.
Execution Plan: The physical operators and access paths used to execute a statement.
Fifth Normal Form (5NF): A normal form where every nontrivial join dependency follows from candidate keys.
Fourth Normal Form (4NF): BCNF with no nontrivial multivalued dependency unless its determinant is a superkey.
Generalization: Combining similar entity types into a broader supertype.
Hash Join: A join algorithm that builds a hash table for one input and probes it with the other.
Identifying Relationship: A relationship where a parent's key contributes to a dependent entity's identity.
Index Scan: Reading multiple entries from an index, often across a range or the full index.
Index Seek: Using index keys to navigate directly to a matching value or range.
Initially Deferred: A constraint mode starting each transaction with checking postponed until commit.
Initially Immediate: A constraint mode starting each transaction with checking performed after each statement.
Join Dependency: A constraint stating that a relation can be reconstructed by joining specified projections.
Lock: A concurrency-control claim restricting conflicting access to a database resource.
Lock Granularity: The size of the protected resource, such as a row, page, or table.
Lossless Decomposition: Splitting a relation so joining the parts recreates exactly the original relation.
Lost Update: A concurrency problem where one write unintentionally overwrites another transaction's write.
Merge Join: A join algorithm that advances through inputs ordered by their join keys.
Minimal Cover: An equivalent, simplified set of functional dependencies with no unnecessary parts.
Multivalued Dependency: A dependency where one attribute set determines independent sets of values for another.
Nested Loop Join: A join algorithm that searches the inner input for each qualifying outer row.
Non-Repeatable Read: Getting a different value when rereading a row changed and committed by another transaction.
Optionality: Whether participation in a relationship is required or optional.
Phantom Read: A repeated predicate query returning added or removed rows due to another committed transaction.
Query Optimizer: The RDBMS component that chooses a physical strategy for executing a query.
Query Plan: A representation of the operations selected or considered for a query.
Sargable Predicate: A predicate shaped so an optimizer can use an index search boundary.
Selectivity: The fraction of rows expected to satisfy a predicate.
Shared Lock: A lock allowing compatible readers while blocking conflicting writers.
Specialization: Dividing a broad entity type into narrower subtypes.
Statistics: Stored summaries of data distribution used to estimate query costs.
Table Scan: Reading a table broadly rather than locating rows through a selective index path.
Weak Entity: An entity that depends on another entity for part of its identity.

Master

Antijoin: A relational operation returning rows from one side that have no match on the other side.
Cartesian Product: The relation containing every possible concatenation of a tuple from each input relation.
Conflict Serializability: Serializability established by reordering nonconflicting operations into a serial schedule.
Domain Relational Calculus: A declarative formal query language using variables that range over domain values.
Equijoin: A theta join whose condition uses equality.
Natural Join: A join matching equally named columns automatically and returning one copy of each matched column.
Precedence Graph: A directed graph of transaction conflicts used to test conflict serializability.
Projection (Relational Algebra): The relational operation that keeps selected attributes and removes duplicate tuples.
Relational Algebra: A formal collection of operations that take relations as input and produce relations.
Relational Closure: The property that relational operations return relations.
Relational Completeness: The ability of a query language to express every operation of relational algebra.
Relational Difference: The tuples present in the first compatible relation but absent from the second.
Relational Model: A data model representing information through relations, domains, keys, and integrity rules.
Selection (Relational Algebra): The relational operation that keeps tuples satisfying a predicate.
Semijoin: A relational operation returning rows from one side that have a match on the other side.
Serial Schedule: A transaction schedule that completes each transaction before starting the next.
Serializable Schedule: A concurrent schedule with the same effect as some serial schedule.
Theta Join: A join defined by an arbitrary comparison predicate between its inputs.
Three-Valued Logic: SQL logic in which a condition can be true, false, or unknown.
Tuple Relational Calculus: A declarative formal query language describing desired tuples with logical predicates.
Two-Phase Locking: A protocol where a transaction acquires locks before entering a phase that only releases them.
Unknown Truth Value: The third SQL truth value produced when NULL prevents a condition from being known true or false.
View Serializability: Serializability based on transactions reading the same values and producing the same final writes.
Write-Ahead Logging: A recovery rule requiring log records to reach durable storage before related data changes.

More subjects in Databases