Database Specialist Examination key terms

A business-level picture of the entities, relationships, and rules that an organization's data must capture, drawn without reference to any particular DBMS or physical storage arrangement.

A distinguishable person, object, event, or concept that the business needs to keep data about; in a relational database each entity type usually becomes one table whose rows are instances.

The association between entities that carries business meaning, such as a customer placing an order; it is implemented by a foreign key or, for many-to-many cases, by a separate associative table.

How many instances of one entity can be linked to one instance of another - one-to-one, one-to-many, or many-to-many - which decides where the foreign key is placed.

Organization-wide agreement on names, formats, code values, and definitions of data items, so the same fact is recorded identically in every system and can be compared across them.

A catalog of data elements with names, types, lengths, allowed values, and relationships; designers use it as documentation, and the DBMS maintains a live version as its system catalog.

A modeling pattern where a general entity holds attributes common to several specialized entities, and each subtype adds attributes of its own; implemented as separate tables or one table with a type code.

The separation of a database into external schemas (user views), a conceptual schema (the whole logical structure), and an internal schema (physical storage), giving logical and physical data independence.

The step-by-step decomposition of relations so each fact is stored once, removing insert, update, and delete anomalies; it proceeds through first, second, and third normal form and beyond, trading joins for consistency.

A rule stating that whenever two rows agree on attribute set A they must also agree on attribute set B, written A→B; it is the basis for finding candidate keys and normal forms.

The column or combination of columns chosen to identify each row uniquely; it must be unique and never null, and other tables point to it through foreign keys.

A column or columns in a child table whose values must match a primary or unique key value in a parent table, enforcing referential integrity between the two tables.

The constraint that every foreign key value must match an existing parent row or be null where allowed, so inserts, updates, and deletes cannot leave orphaned child records.

Turning the conceptual model into normalized tables, keys, and constraints on a product-independent basis, deciding what each table represents and how each row is identified.

Mapping the logical design onto a specific DBMS: choosing data types, indexes, partitioning, and storage placement so that performance and capacity targets are met.

Deliberately adding redundancy to a normalized schema, for example duplicating columns or pre-joining tables, to avoid expensive joins in read-heavy workloads at the cost of extra update logic.

An auxiliary structure, usually a B-tree, that lets the DBMS locate rows without scanning the whole table; it speeds reads but adds storage and maintenance cost to every insert, update, and delete.

The part of SQL that creates and alters schema objects such as tables, views, indexes, and constraints, mainly through the CREATE, ALTER, and DROP statements.

The part of SQL that works on rows rather than definitions: SELECT to retrieve, and INSERT, UPDATE, DELETE, MERGE to change data, normally executed inside a transaction.

A named query stored in the database that behaves like a virtual table; it can hide columns, simplify complex joins, and, when updatable, pass changes through to the base tables.

A program stored and run inside the database server, grouping SQL statements with control logic so clients call a single unit and the logic is shared by all applications.

Procedural SQL that the DBMS fires automatically when an insert, update, or delete occurs on a specified table, often used for auditing, derived values, or complex business rules.

A SQL function that computes a value over a set of rows related to the current row, such as a running total, rank, or moving average, without collapsing rows the way GROUP BY does.

An operation that combines rows from two tables on a matching condition; inner joins keep only matches, while outer joins also keep unmatched rows from one or both sides.

Four guarantees for a transaction: atomicity (all or nothing), consistency (constraints preserved), isolation (concurrent work does not interfere), and durability (committed changes survive a failure).

A setting that trades consistency against concurrency by controlling which anomalies remain possible: read uncommitted, read committed, repeatable read, and serializable, ordered from weakest to strongest.

Mechanisms that coordinate concurrent transactions, typically by taking shared or exclusive locks on rows or pages, so conflicting reads and writes cannot interleave in a way that corrupts results.

A state where two or more transactions each hold a lock the others need and all wait indefinitely; the DBMS detects the cycle and aborts one victim so the others can finish.

A protocol for distributed transactions: a prepare phase in which every participant votes and reserves resources, then a commit or rollback phase driven by the coordinator's decision.

A concurrency method that keeps several versions of a row so readers see a consistent snapshot without blocking writers; the cost is storing and later cleaning up old versions.

Declarative rules the DBMS enforces on stored data - NOT NULL, UNIQUE, CHECK, primary and foreign keys - rejecting any statement that would leave the database violating them.

The access path the optimizer chooses for a SQL statement, showing join order, join methods, and index use; reading it is the first step when a query performs poorly.

Splitting a large table or index into smaller physical pieces by range, list, or hash, so maintenance and backup can work piece by piece and scans skip irrelevant pieces.

Continuously copying changes from a primary database to one or more replicas, used for read scale-out, availability, and disaster recovery; replication lag is the usual trade-off.

Encoding stored or transmitted data with a key so unauthorized parties cannot read it; applied to data at rest, data in transit, and specific columns holding sensitive values.

A record of who accessed or changed which data and when, kept apart from the transaction log so that investigations and compliance checks can reconstruct activity after the fact.

A consolidated store that gathers data from many operational systems, cleans and historizes it, and organizes it for analysis rather than for day-to-day transaction processing.

Extract, transform, load: the process that pulls data from source systems, cleanses and conforms it, and loads it into a warehouse or data mart, usually on a schedule.