Database Specialist Examination — knowledge map
The 40 core concepts of Database Specialist Examination and how they connect. Click a node in the map above to explore related terms and prerequisites; the list below indexes every concept with its definition and links to its prerequisites and related concepts.
Concepts (40)
Boyce-Codd normal form (BCNF)
A normal form stricter than 3NF: it requires that every nontrivial functional dependency's determinant be a candidate key, eliminating certain update anomalies (arising when multiple candidate keys exist) that 3NF alone can leave behind.
Prerequisites: Functional dependency、Candidate key and superkey、Normal forms (1NF to 3NF)
Candidate key and superkey
A superkey is any attribute set that uniquely identifies a tuple. A candidate key is a minimal superkey — removing any attribute from it would break uniqueness. The primary key is the candidate key chosen as the table's representative identifier; the others are called alternate keys.
Prerequisites: Tuple and attribute
Serializability
The property that the result of executing transactions concurrently matches the result of running them one at a time in some serial order. The SERIALIZABLE isolation level guarantees this property and is the strictest level, preventing every read anomaly — dirty reads, non-repeatable reads, and phantom reads.
Prerequisites: Transaction isolation level、Dirty read、Non-repeatable read、Phantom read
Tuple and attribute
In the relational model, a tuple is one row of a table (a single record), and an attribute is one column (a single field). Discussions of relational algebra and normalization theory operate at the level of tuples and attributes.
Prerequisites: Relational algebra
Related: Relational model
Functional dependency
A relationship in which the value of attribute A uniquely determines the value of attribute B (A→B). It is the theoretical basis of normalization: eliminating partial functional dependency (dependency on only part of the primary key) yields second normal form, and eliminating transitive functional dependency (indirect dependency via a non-key attribute) yields third normal form.
Related: Normal forms (1NF to 3NF)
Transaction isolation level
A setting that determines to what extent concurrently executing transactions can see each other's uncommitted changes. From lowest to highest: READ UNCOMMITTED (allows dirty reads), READ COMMITTED, REPEATABLE READ, and SERIALIZABLE (the strictest level, equivalent to serial execution). Raising the level improves consistency but reduces concurrency (throughput) — a fundamental trade-off.
Related: Dirty read
Degree and cardinality
In the relational model, degree is the number of attributes (columns) in a table, and cardinality is the number of tuples (rows). Degree is fixed at schema-design time and normally stable in operation, while cardinality changes as data is added or removed.
Prerequisites: Relational model、Tuple and attribute
Dirty read
A read anomaly in which a transaction reads changes made by another transaction that has not yet committed (and might still roll back). It can occur only under the READ UNCOMMITTED isolation level and is prevented at READ COMMITTED and above.
Related: Transaction isolation level
Query execution plan
The sequence of steps — order, algorithms (join method, whether to use an index, etc.) — the optimizer chooses to execute a SQL statement. It can be visualized with the DBMS's explain command and is the starting point for diagnosing slow queries, such as an unwanted full table scan.
Fourth normal form (4NF)
A normal form that, in addition to satisfying BCNF, requires the absence of nontrivial multivalued dependencies. It eliminates redundant insertion of unrelated combinations that arises when two independent one-to-many relationships are combined into a single table.
Prerequisites: Boyce-Codd normal form (BCNF)
Related: Multivalued dependency
Relational algebra
A mathematical query language defining operations on relations (tables): selection, projection, join, union, difference, Cartesian product, and division. SQL queries are internally translated into relational-algebra operations, which the optimizer rewrites into equivalent forms to build an execution plan.
Prerequisites: Query execution plan
Relational model
A data model that represents data as a set of tables (relations) made of rows (tuples) and columns (attributes). Proposed by E. F. Codd, it rests on the mathematical basis of relational algebra and relational calculus, giving queries a precise semantics.
Prerequisites: Relational algebra
Related: Tuple and attribute
Normal forms (1NF to 3NF)
A staged technique for reducing redundancy and update anomalies in relational database design. First normal form removes repeating groups, second normal form separates attributes that depend on only part of the primary key, and third normal form separates attributes that depend on non-key attributes (transitive dependency).
Related: Functional dependency
Two-phase locking
A locking protocol that guarantees serializability for concurrent transactions. A transaction proceeds through two phases: a growing phase, in which it only acquires locks, and a shrinking phase, in which it only releases them—once a lock is released, no new lock may be acquired. This discipline guarantees that the resulting schedule is equivalent to a serial execution.
Prerequisites: Serializability
Optimizer (cost-based optimization)
A mechanism that, using statistics (row counts, value distributions, etc.), estimates the cost (I/O, CPU) of each candidate execution plan that would return the same result, and picks the lowest-cost one. Stale or missing statistics can cause it to pick a poor plan, degrading performance.
Prerequisites: Query execution plan
Fifth normal form (5NF)
A normal form in which every join dependency is implied by the candidate keys, with all other join dependencies removed by lossless decomposition. It is treated as the practical endpoint of normalization, though higher forms such as 6NF exist in theory. A join dependency is the property that splitting a table and rejoining it reproduces the original content.
Prerequisites: Candidate key and superkey
Hash join
A join algorithm that builds an in-memory hash table from the smaller table's join column, then scans the larger table, probing the hash table for matches. It is limited to equality joins but is fast for joining large, unsorted tables.
Multivalued dependency
A relationship where fixing the value of one attribute (or attribute set) determines a set of values for another attribute, independent of yet other attributes. When multiple independent multivalued dependencies coexist in one table, redundant combinations result, which decomposition to 4NF resolves.
Related: Fourth normal form (4NF)
Multiversion concurrency control (MVCC)
A concurrency-control scheme that keeps multiple versions of a row across updates, letting each transaction read a snapshot from its start time so reads never block writes (and vice versa). It achieves higher concurrency than pure locking and underlies the default isolation implementation in many modern DBMSs.
Outer join
A join that includes rows from the non-matching side as well, filling the corresponding columns with NULL. It comes in left, right, and full variants, and is used for requirements like "list all customers, including those with no orders."
Phantom read
A read anomaly in which re-running the same range query twice within one transaction returns a different number of rows because another transaction inserted or deleted rows in between. Under the SQL standard it can occur up through REPEATABLE READ and is prevented at SERIALIZABLE, though some implementations (such as PostgreSQL MVCC or MySQL InnoDB gap locks) prevent it even at REPEATABLE READ.
Prerequisites: Multiversion concurrency control (MVCC)
Referential constraint (referential integrity)
A constraint requiring a foreign key's value to actually exist as a primary (or candidate) key value in the referenced table. It prevents orphaned references — child records without a parent — and preserves consistency across tables.
Prerequisites: Candidate key and superkey
Entity-relationship diagram
A conceptual modeling technique used in database design to diagram entities and the relationships between them. It explicitly shows the cardinality between entities (one-to-one, one-to-many, many-to-many) and is used as a precursor to logical database design and normalization.
Availability rate
The proportion of time a system operates correctly. Availability = MTBF ÷ (MTBF + MTTR); it rises as time-between-failures grows and repair time shrinks. A core availability metric, also used as an agreed SLA target (e.g., 99.9%).
Prerequisites: MTBF (mean time between failures)
B-tree index
The most common index type, keeping key values sorted in a balanced tree structure. It performs well for range searches, equality searches, and sorting alike, and is the default index type in most DBMSs. Every update carries a cost to rebalance the tree.
Non-repeatable read
A read anomaly in which reading the same row twice within one transaction returns different values because another transaction updated and committed it in between. It can occur up through READ COMMITTED and is prevented at REPEATABLE READ and above.
Sort-merge join
A join algorithm that first sorts both tables on the join column, then scans them in parallel, merging matching rows as it goes. It performs well on already-sorted (indexed) data and is used mainly for equality joins, providing stable performance on large sorted inputs (chosen versus hash join accordingly).
Prerequisites: Hash join
MTBF (mean time between failures)
For a repairable system, the average operating time from one failure to the next. A larger value means fewer failures and higher reliability. Computed as total operating time ÷ number of failures.
MTTR (mean time to repair)
The average time from a failure until recovery. A smaller value means faster recovery and better maintainability. Computed as total repair time ÷ number of failures; it is part of the denominator in availability = MTBF ÷ (MTBF + MTTR), assessed together with MTBF (mean time between failures) for reliability and maintainability.
Prerequisites: Availability rate、MTBF (mean time between failures)
Associative entity
An intermediate entity introduced in an E-R diagram to resolve a many-to-many relationship, holding both original entities' primary keys as foreign keys. It can also carry attributes of the relationship itself (e.g., order date), making it straightforward to implement as a relational table.
Prerequisites: Entity-relationship diagram
Cursor (SQL)
A mechanism, used within stored procedures or embedded SQL, for fetching and processing query-result rows one at a time. It is used for row-by-row procedural logic that set-based operations handle poorly, such as branching and updating on a per-row basis.
Prerequisites: Embedded SQL
Embedded SQL
A style of writing SQL statements directly inside host-language source code, such as C or COBOL; a precompiler translates them into host-language function calls at build time. Embedded SQL can include both static SQL (fixed at compile time) and dynamic SQL (assembled as a string at run time); the approach it contrasts with is the call-level interface (CLI/ODBC), which passes SQL through a library at run time.
Hash index
An index type that determines storage location from a hash of the key value. Equality lookups (=) are extremely fast, but the index cannot support range searches or sorting — the main contrast with a B-tree index.
Prerequisites: B-tree index
Logical data independence
The property that changes to the conceptual schema (e.g., table structure) do not affect the external schema that applications see. It is a goal of the three-schema architecture, letting tables be added or restructured without rewriting applications.
Prerequisites: Three-schema architecture (ANSI/SPARC)
Roll-forward recovery
A recovery method that applies the log records (redo log) generated after a backup, in order, to restore the database to its state just before a failure. It recovers committed updates made since the backup without losing them.
Prerequisites: UNDO/REDO logging recovery
Three-schema architecture (ANSI/SPARC)
An architecture that splits a database into an external schema (per-user view), a conceptual schema (overall logical structure), and an internal schema (physical storage). Separating the layers lets one change without disturbing the others.
UNDO/REDO logging recovery
The two operations used with the log during failure recovery: UNDO reverses changes made by transactions that never committed, and REDO reapplies changes that were committed but not yet flushed to disk. Recovery scans the log since the last checkpoint from both angles to restore a consistent state.
SQL JOIN
An SQL operation that combines rows from multiple tables based on a related column, producing a single result set. The main forms are inner join, which returns only matching rows from both tables, and outer join, which preserves all rows from one side.
Prerequisites: Outer join
Exclusive control (locking)
A mechanism that locks data to restrict simultaneous access by other processes, preventing the inconsistencies that can arise when multiple users or processes try to update the same data at the same time.