Your FREE CompTIA DataSys+ Flashcards 2026 – 250+ Cards

Atomicity, Consistency, Isolation, Durability — the four guarantees of a reliable database transaction.

A transaction completes fully or not at all — partial changes are rolled back.

A transaction moves the database from one valid state to another, honoring all constraints.

Concurrent transactions don't interfere; each behaves as if it ran alone.

Once committed, changes survive crashes and power loss (persisted to non-volatile storage).

Stores data in tables (relations) of rows and columns with a defined schema; queried with SQL.

A column (or set) that uniquely identifies each row; cannot be null or duplicated.

A column that references another table's primary key, enforcing referential integrity.

A primary key made up of two or more columns combined.

Any column or set of columns that could serve as the primary key (uniquely identifies a row).

An artificial, system-generated key (e.g., auto-increment id) used instead of natural data.

A key made from real, meaningful data that already exists (e.g., email or SSN).

The rule that every foreign-key value must match an existing primary-key value (no orphans).

The rule that every table has a primary key and no primary-key value is null.

The structure/blueprint of a database: tables, columns, data types, keys, and relationships.

A single record in a table — one set of related values across all columns.

A named field in a table holding one type of value for every row.

The nature of a relationship between tables: one-to-one, one-to-many, or many-to-many.

Organizing tables to reduce redundancy and avoid update/insert/delete anomalies.

Each cell holds a single atomic value; no repeating groups; each row is unique.

1NF and every non-key column depends on the whole primary key (no partial dependencies).

2NF and no non-key column depends on another non-key column (no transitive dependencies).

Boyce-Codd Normal Form — a stricter 3NF where every determinant is a candidate key.

Deliberately adding redundancy to speed up read-heavy queries, trading off integrity.

Data Definition Language — CREATE, ALTER, DROP, TRUNCATE; defines and changes structure.

Data Manipulation Language — SELECT, INSERT, UPDATE, DELETE; reads and changes data.

Data Control Language — GRANT and REVOKE; controls permissions.

Transaction Control Language — COMMIT, ROLLBACK, SAVEPOINT; manages transactions.

DML statement that retrieves rows from one or more tables.

DML statement that adds a new row to a table.

DML statement that modifies existing rows that match a condition.

DML statement that removes rows matching a WHERE clause; can be rolled back.

DDL statement that removes all rows from a table quickly and resets it.

DDL statement that removes an entire table (structure and data) from the database.

Filters rows in a query to those that meet a condition.

Groups rows that share values so aggregate functions apply per group.

Filters groups after GROUP BY (WHERE filters rows before grouping).

Sorts the result set by one or more columns, ascending or descending.

Removes duplicate rows from a SELECT result set.

Returns only rows with matching values in both joined tables.

Returns all rows from the left table plus matches from the right (NULLs where none).

Returns all rows from the right table plus matches from the left (NULLs where none).

Returns all rows from both tables, with NULLs where either side has no match.

Returns the Cartesian product — every row of one table paired with every row of the other.

A table joined to itself, often to compare rows within the same table.

Computes a single value over a set of rows: COUNT, SUM, AVG, MIN, MAX.

Aggregate function returning the number of rows (or non-null values).

A saved, named query that acts like a virtual table; simplifies access and can restrict columns.

A structure that speeds row lookups by a column's value without scanning the whole table.

The same data stored in more than one place — a source of inconsistency normalization reduces.

A marker for a missing or unknown value — not zero and not an empty string.

A rule enforced on a column: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

Ensures all values in a column (or set) are different across rows.

Restricts column values to those that satisfy a boolean condition.

Defines the kind of value a column stores: INT, VARCHAR, DATE, DECIMAL, BOOLEAN, etc.

VARCHAR stores variable-length text; CHAR is fixed-length and padded.

SQL = structured, fixed-schema relational tables; NoSQL = flexible schema, horizontal scale.

NoSQL store of self-describing documents (e.g., JSON) — example: MongoDB.

NoSQL store mapping unique keys to values — example: Redis.

NoSQL store organizing data by column families for wide rows — example: Cassandra.

NoSQL store of nodes and relationships — example: Neo4j.

Entity-Relationship Diagram — a model of entities, attributes, and relationships in a design.

Database Management System — software that stores, retrieves, and manages databases.

A named, precompiled set of SQL statements executed by name, often with parameters.

Code that runs automatically in response to an INSERT, UPDATE, or DELETE event.

A routine that returns a value and can be used inside a query.

Pattern-matches text in a WHERE clause using wildcards % (any string) and _ (one char).

Tests whether a value matches any value in a list or subquery.

Tests whether a value falls within an inclusive range.

A query nested inside another query, used in SELECT, FROM, or WHERE.

Combines result sets of two queries and removes duplicates (UNION ALL keeps them).

A temporary name given to a column or table in a query for readability.

Returns the first non-null value from a list of expressions.

Adds if/then/else conditional logic inside a SQL query.

A unit of work treated as a single, all-or-nothing operation.

Permanently saves the changes made in the current transaction.

Undoes the changes made in the current transaction.

A marker within a transaction you can roll back to without undoing everything.

One row in a table relates to many rows in another (the most common relationship).

Rows on both sides relate to many — implemented with a junction (bridge) table.

A bridge table that resolves a many-to-many relationship using two foreign keys.

The set of valid values a column may hold, enforced by data type and constraints.

A table with no clustered index — rows stored in no particular order.

A column that automatically assigns the next sequential number to new rows.

Time-based data; some databases support system-versioned (temporal) tables for history.

A column storing JSON documents, blending relational and document storage.

Online Transaction Processing — many short real-time transactions; normalized for fast writes.

Online Analytical Processing — complex analytical queries over large historical data; often denormalized.

Extract, Transform, Load — moves and reshapes data from sources into a warehouse.

Extract, Load, Transform — loads raw data first, then transforms it in the target system.

A central repository of integrated historical data optimized for analysis and reporting.

A store of raw data in native format at scale, for varied later processing.

A subject-focused subset of a data warehouse for a specific team or use.

A warehouse design: one central fact table linked to denormalized dimension tables.

A star schema whose dimension tables are normalized into related sub-tables.

The central table in a warehouse holding measurable, numeric business events.

A table of descriptive attributes (who, what, where, when) joined to facts.

Installing, configuring, and making a database available in an environment.

A documented standard config used to deploy databases consistently and securely.

Managing infrastructure and config as version-controlled files (e.g., Terraform, Ansible).

Moving a database or its data/schema to a new version, server, or platform.

Applying versioned, repeatable changes to a database structure over time.

A database running on hardware the organization owns and manages itself.

A database hosted/managed in a cloud provider's environment (IaaS, PaaS, or DBaaS).

Database as a Service — a fully managed cloud database (provider handles patching, backups, HA).

Infrastructure as a Service — you manage the OS and DBMS on provider-supplied VMs.

Platform as a Service — the provider manages the platform; you deploy databases/apps on it.

Using shell/Python/PowerShell + SQL scripts to automate deployment and admin tasks.

A script that produces the same result no matter how many times it runs.

Parameters an app uses to connect: server, database, credentials, and options.

Standard APIs/drivers that let applications connect to and query databases.

A database packaged in a container (e.g., Docker) for portable, repeatable deployment.

A production-like environment for testing deployments before go-live.

An isolated environment for development and experimentation, separate from production.

Tracking schema and script changes in a repository (e.g., Git) for review and rollback.

Bulk loading or extracting data via tools like BCP, COPY, or import/export utilities.

Comma-Separated Values — a plain-text format commonly used to import/export tabular data.

A running copy of the DBMS engine managing one or more databases in memory and on disk.

Two identical environments; switch traffic to the new one for zero-downtime releases.

Reverting a deployment to the previous known-good version after a failure.

A quick post-deployment check that core functionality works.

Loading many rows at once efficiently, bypassing row-by-row overhead.

Cleaning, reshaping, and converting data during ETL/ELT.

Verifying imported data meets type, range, and integrity rules.

Requiring TLS for client-server connections during deployment hardening.

A configuration value (e.g., credentials, host) supplied to a deployment at runtime.

Storing credentials/keys securely (vaults) rather than hard-coded in scripts.

A configured connection that lets one database query another remote database.

Configuring primary and replica roles during deployment for HA or read scaling.

Estimating CPU, memory, and storage before deploying a database.

Defines the physical sort order of a table's rows; only one per table.

A separate structure pointing back to rows; many allowed per table.

An index on two or more columns, useful for multi-column lookups and sorting.

A balanced-tree structure that keeps lookups fast as data grows.

An index that contains all columns a query needs, so the table isn't read.

Disordered index pages that slow reads; fixed by rebuilding or reorganizing.

Recreates an index to remove fragmentation (heavier than a reorganize).

Defragments index pages in place with lower overhead than a rebuild.

Metadata about data distribution the optimizer uses to choose a good plan.

The DBMS component that chooses the execution strategy for a SQL statement.

The optimizer's step-by-step plan for running a query (scans, seeks, joins, sorts).

Reading every row of a table — slow on large tables; a sign a useful index is missing.

Using an index to jump directly to matching rows — efficient lookup.

Improving speed via indexing, query rewriting, statistics, and configuration.

Measuring a query's resource use and timing to find bottlenecks.

Preventing conflicting concurrent access to data to preserve isolation.

Two transactions each hold a lock the other needs; the DBMS kills one as the victim.

The DBMS converting many fine-grained locks into a coarser one to save resources.

Multiple transactions executing at the same time without corrupting data.

Sets how visible one transaction's changes are to others (read committed, serializable, etc.).

Reading uncommitted changes that may later be rolled back.

Rows appearing/disappearing between reads in a transaction due to other inserts/deletes.

Automated, scheduled task: backups, index maintenance, statistics updates, cleanup.

Applying vendor updates to the DBMS to fix bugs and close security holes.

A scheduled set of upkeep tasks: backups, integrity checks, index/statistics maintenance.

A consistency check (e.g., DBCC CHECKDB) that detects corruption in a database.

Forecasting storage, memory, and CPU needs to keep performance acceptable as data grows.

Continuously tracking health metrics (CPU, memory, I/O, waits, blocking, errors).

A record of normal performance used to spot abnormal drift.

Automated notifications when a metric crosses a threshold (e.g., low disk, high latency).

Records all changes so transactions can be committed, rolled back, and recovered.

Reclaiming space in the transaction log after backups, preventing it from filling.

Splitting a large table across segments to improve manageability and performance.

Horizontally splitting data across multiple servers to scale out.

Moving old, rarely accessed data to cheaper storage to keep active tables lean.

Reclaiming space from dead rows and updating statistics (e.g., PostgreSQL VACUUM).

Reusing a set of database connections to reduce overhead and improve throughput.

A feature that caps CPU/memory per workload to prevent one query starving others.

Metrics showing what the engine is waiting on (I/O, locks, CPU) to find bottlenecks.

One transaction holding a lock that stalls another (not yet a deadlock).

An instruction that overrides the optimizer's default choice for a query.

The optimizer caching a plan based on the first parameter, hurting other values.

Stored execution plans reused to avoid recompiling frequent queries.

Generating a fresh execution plan when data or statistics change significantly.

The overall accuracy and consistency of data, protected by constraints and checks.

A database user whose matching server login is missing — a common post-restore fix.

Workspace the engine uses for sorts, joins, and temporary objects.

A setting that lets a data/log file expand automatically when it fills.

Reducing storage and I/O by compressing data or backups.

A routine review of performance, errors, security, and backups.

Updating data-distribution stats so the optimizer makes good choices.

A scheduled period for disruptive upkeep (patching, rebuilds) with low user impact.

Confidentiality, Integrity, Availability — the three core security goals.

Keeping data accessible only to authorized parties (encryption, access control).

Ensuring data is accurate and unaltered except by authorized changes.

Ensuring data and the database are accessible when needed.

Verifying who a user or service is (passwords, keys, MFA, certificates).

Determining what an authenticated user is allowed to do.

Role-Based Access Control — assign permissions to roles, then users to roles.

Granting only the minimum access a role needs to do its job.

Splitting critical tasks among people so no one has unchecked control.

SQL (DCL) command that gives a user or role a permission.

SQL (DCL) command that removes a previously granted permission.

Inserting malicious SQL via unsanitized input to read or alter data.

A prepared statement that separates SQL code from input — the main SQL-injection defense.

Checking and sanitizing user input to block malicious or malformed data.

Encrypting stored data on disk so files/backups are unreadable if stolen.

Encrypting data moving over the network (TLS) so it can't be intercepted.

Transparent Data Encryption — encrypts database files at rest automatically.

Transport Layer Security — the protocol that encrypts data in transit between client and server.

Advanced Encryption Standard — a strong symmetric cipher widely used for data encryption.

A one-way function producing a fixed-length digest; used to store passwords (with salt).

Random data added to a password before hashing to defeat precomputed (rainbow) attacks.

Securely generating, storing, rotating, and revoking encryption keys.

Hiding sensitive values (e.g., showing only last 4 digits) for non-privileged users.

Replacing sensitive data with a non-sensitive token mapped in a secure vault.

Recording who accessed or changed what and when, for accountability and compliance.

An immutable record of security-relevant database events.

Labeling data by sensitivity (public, internal, confidential, restricted) to drive controls.

Personally Identifiable Information — data that can identify a person; requires protection.

EU regulation governing personal-data privacy, consent, and breach notification.

U.S. law protecting the privacy and security of health information (PHI).

Security standard for organizations that handle payment-card data.

An attacker gaining higher permissions than they were granted.

Layering multiple security controls so no single failure exposes the data.

A control that monitors and filters SQL traffic to block malicious queries.

Encrypting backup files so stolen backups don't expose data.

Disabling an account after repeated failed logins to slow brute-force attacks.

A non-human account an application uses; should run with least privilege.

Multi-Factor Authentication — requiring two or more proofs of identity.

Using digital certificates to verify identity instead of passwords.

Restricting which rows a user can see based on their identity or role.

Restricting access to specific sensitive columns within a table.

Masking column values at query time for unauthorized users without changing stored data.

Scanning a database for misconfigurations and known weaknesses.

An authorized simulated attack to find exploitable security gaps.

A fix that closes a known vulnerability in the DBMS.

Trying many passwords/keys until one works; mitigated by lockout and strong policies.

Users accumulating permissions over time beyond what they currently need.

Unauthorized access to or disclosure of protected data.

The plan to detect, contain, eradicate, and recover from a security incident.

A formal review confirming controls meet a regulation or standard.

The principle that data is subject to the laws of the country where it is stored.

A hardened default configuration applied to every database.

Recovery Point Objective — the maximum tolerable data loss; drives backup frequency.

Recovery Time Objective — the maximum tolerable downtime; drives recovery design.

A complete copy of all data; fastest restore, most storage and time.

Copies all changes since the last FULL backup; restore = full + latest differential.

Copies changes since the last backup of ANY type; smallest, but slowest restore.

Keep 3 copies of data, on 2 media types, with 1 copy off-site.

Testing that backups actually restore — an untested backup is unproven.

A backup taken while the database is shut down (offline).

A backup taken while the database is running and online.

Restoring a database to a specific moment using backups plus transaction logs.

A read-only, point-in-time image of data used for fast recovery or cloning.

Striping for speed; no redundancy — one disk failure loses all data.

Mirroring — an identical copy on a second disk; survives one disk failure.

Striping with distributed parity; survives one disk failure; needs 3+ disks.

Double parity; survives two simultaneous disk failures; needs 4+ disks.

Mirrored pairs that are striped; high performance and redundancy.

Designing systems to minimize downtime through redundancy and failover.

Automatically switching to a standby system when the primary fails.

Multiple nodes sharing storage so a standby can take over the database service.

Keeping synchronized copies of a database on multiple servers.

Commits on the primary and replica together — zero data loss, higher latency.

Replica lags slightly behind — lower latency, small risk of data loss.

Periodically copying and restoring transaction logs to a standby server.

Maintaining a hot standby copy of a database for failover.

The plan and process to restore service after a major outage or disaster.

A secondary location (hot, warm, or cold) used to resume operations after a disaster.

A fully equipped, running DR site that can take over almost immediately.

A partially ready DR site needing some setup/data load before takeover.

A facility with infrastructure but no live systems/data — slowest to bring up.

An organization-wide plan to keep critical functions running during disruption.

Mean Time To Repair — average time to fix a failed component.

Mean Time Between Failures — average operating time between failures (reliability).

Storing copies in geographically separate regions to survive a regional outage.

Rules for how long backups/data are kept before deletion or archive.

A secondary copy kept ready to take over (warm/hot) if the primary fails.

Both nodes serve traffic simultaneously, sharing load and providing redundancy.

One node serves traffic while a standby waits to take over on failure.

The minimum number of cluster nodes that must agree for the cluster to stay online.

A cluster fault where two nodes both think they're primary — prevented by quorum/witness.

An extra vote that breaks ties and prevents split-brain in a cluster.

The time period available to run backups without harming performance.

How long backup copies are kept before deletion (driven by policy/compliance).

A backup stored in a separate location to survive a site-level disaster.

A walkthrough drill that tests a DR/BCP plan without real failover.

Periodically restoring backups to prove they work and meet the RTO.

Restoring a full system, including OS, from backup onto new hardware.

Capturing every change so you can restore to nearly any point in time.

Service Level Agreement — the committed uptime/recovery targets for a service.