CompTIA DataSys+ (DS0-001) flashcards

A NoSQL database that stores data in columns rather than rows, optimized for high write throughput, horizontal scalability, and efficient retrieval of specific columns across large datasets.

• Excellent for time-series and IoT data.
• Supports eventual consistency and high availability.
• Often used for Big Data analytics and real-time processing.

Memory trick: IoT streams need columns for speed, not rows or graphs indeed.

Transport Layer Security (TLS) is a cryptographic protocol designed to provide communication security over a computer network. It is widely used for encrypting communications between web applications and servers, mail servers, and database clients and servers.

• Successor to SSL.
• Provides encryption, authentication, and integrity.
• Operates at the transport layer (Layer 4) of the OSI model.

Memory trick: TLS secures the data journey.

The ability to restore a database to its exact state at any specific moment in time, typically achieved by combining full backups with a sequence of transaction log backups.

• Requires the database to be in full recovery mode.
• Achieved by restoring a full backup, then applying all subsequent transaction log backups up to the desired point.
• Frequent log backups minimize the potential for data loss (low RPO).

Memory trick: Full + Logs = Point in time, Diff = Faster, but less granular.

The `shared_buffers` parameter in PostgreSQL sets the amount of dedicated memory for shared buffer pool, used to cache data pages read from disk.

• Crucial for reducing disk I/O by keeping data in RAM.
• Typically set to 15-25% of total system RAM on a dedicated database server.
• Impacts overall query performance, especially for frequently accessed data.

Memory trick: Shared buffers 'S'ave 'S'torage I/O.

A PostgreSQL configuration parameter in `postgresql.conf` that determines which IP addresses the database server listens on for incoming client connections.

• Default is often `localhost` or `*` (all interfaces).
• `localhost` restricts connections to the local machine only.
• Requires a restart to take effect after modification.

Memory trick: Listen addresses control the database's open door.

Synchronous Database Mirroring is a high-availability solution that maintains two copies of a single database, typically on different servers. Transactions are committed on both the principal (primary) and mirror (secondary) databases before being acknowledged, ensuring zero data loss and enabling automatic failover.

• Maintains two identical copies of a database.
• Transactions committed on both primary and secondary before acknowledgment.
• Provides zero data loss (RPO=0) and automatic failover.

Memory trick: Mirror, Sync, Always On: Your data's twin, never gone.

The Recovery Time Objective (RTO) is the maximum acceptable duration of time that a computer system, application, or network can be down after a disaster or failure. It defines how quickly systems must be restored.

• Maximum acceptable downtime.
• Measures the speed of system restoration.
• A crucial metric in disaster recovery planning.

Memory trick: RTO: 'Time to Operate' again. RPO: 'Point of Data' lost.

A comprehensive document that describes the structure, relationships, and definitions of data within a database, serving as a blueprint for its implementation and maintenance.

• Includes schema diagrams, data dictionary, data models, and data lineage.
• Essential for communication between developers, analysts, and administrators.
• Ensures consistency and understanding of the database architecture.

Memory trick: The Design Document is the blueprint for the database house.

Performance bottleneck in SQL Server's `tempdb` database, often caused by many concurrent processes trying to allocate pages, leading to waits on allocation structures.

• Commonly occurs in highly concurrent OLTP systems.
• Symptoms include high PAGELATCH_EX and PAGELATCH_SH waits on `tempdb` pages.
• Mitigated by creating multiple `tempdb` data files and ensuring they are on fast storage.

Memory trick: Tempdb needs many files, fast disks, and good queries.

Queries that are primarily limited by the amount of available RAM, often leading to intermediate results spilling to disk, which significantly degrades performance.

• Common in analytical workloads (OLAP) with large joins, aggregations, and sorts.
• Symptoms include high `tempdb` I/O, page faults, and slow query execution.
• Increasing server RAM or optimizing queries to reduce memory footprint can help.

Memory trick: Aggregations love RAM, disk is a last resort.

A database optimized for storing and retrieving time-stamped data, often from sensors, IoT devices, or monitoring systems, designed for high ingest rates and time-based queries.

• Excellent for high-volume, append-only data.
• Optimized for time-based aggregations and range queries.
• Commonly used for monitoring, IoT, and financial data.

Memory trick: Streaming data with time stamps, time-series is the champ.

A technique used to obscure sensitive data with realistic, but not actual, data to protect privacy while maintaining data utility for non-production environments.

• Can be static (one-time replacement) or dynamic (on-the-fly).
• Preserves data format and referential integrity.
• Crucial for compliance with privacy regulations like GDPR, HIPAA.

Memory trick: Obfuscation is like putting a mask on data so it can play a role.

A deadlock prevention strategy where all concurrent transactions acquire locks on shared resources (e.g., tables, rows) in a predefined, consistent sequence, thereby eliminating the possibility of a circular wait condition.

• Prevents deadlocks by eliminating circular dependencies.
• Requires careful design and adherence in application logic.
• Applies to resources that multiple transactions might access.

Memory trick: Always take the same path, so no one gets stuck in a loop.

The condition in PostgreSQL where tables and indexes consume more physical disk space than necessary due to the accumulation of 'dead tuples' from UPDATE/DELETE operations.

• Caused by PostgreSQL's MVCC (Multi-Version Concurrency Control) architecture.
• Requires VACUUM (or autovacuum) to reclaim space and update statistics.
• Can lead to increased I/O, larger backups, and slower query performance.

Memory trick: MVCC means dead rows bloat, VACUUM cleans.

A SQL Server and Azure SQL Database feature that allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the database engine.

• Protects data at rest, in motion, and in use.
• Decryption occurs only on the client side.
• Requires client-side application changes to handle encrypted columns.

Memory trick: To be truly secure, encrypt from client to disk, even in use.

A column property in SQL databases that automatically generates a unique, sequential integer number for each new row inserted into a table.

• Ensures unique values for primary keys.
• Simplifies data entry by eliminating manual ID assignment.
• Automatically increments the value for each new record.
• Syntax varies slightly across different database systems (e.g., `SERIAL` in PostgreSQL, `IDENTITY` in SQL Server).

Memory trick: AUTO_INCREMENT: Automatically number your records, easy and unique!

The process of monitoring and recording database activities to track user actions, detect suspicious behavior, and ensure compliance with security policies.

• Generates an audit trail of database events.
• Essential for accountability, forensics, and compliance.
• Can track logins, data access, schema changes, and DML operations.

Memory trick: Accountability is having a detailed ledger of every database action.

The process of improving query performance by creating and maintaining appropriate indexes on database tables.

• Indexes speed up data retrieval operations (SELECT).
• They are most effective on columns used in WHERE, JOIN, ORDER BY clauses.
• Indexes add overhead to data modification operations (INSERT, UPDATE, DELETE).

Memory trick: Index the joins, speed up the lines.

Database migration validation is the process of thoroughly testing the migrated database and application to ensure data integrity, functionality, and performance in the new environment.

• Crucial for ensuring custom code (PL/SQL, stored procedures) functions as expected.
• Verifies data consistency between source and target.
• Includes functional, performance, and regression testing.
• Helps identify and resolve issues before go-live.

Memory trick: Custom code 'C'hecks 'C'ome in 'V'alidation.

Documentation that describes the lifecycle of data, tracking its origin, transformations, and movement across various systems and processes within an organization.

• Essential for data governance, compliance, and auditing.
• Helps understand data quality issues and root causes.
• Often visualized using data flow diagrams.

Memory trick: Data Lineage is the data's journey, from birth to present.

A column or set of columns in a relational database table that provides a link between data in two tables. It refers to the primary key in another table.

• Establishes and enforces a link between two tables.
• Ensures referential integrity.
• Can contain NULL values if not also specified as NOT NULL.

Memory trick: Keys unlock and link your data.

A database architecture where one database (master) handles all write operations, and its data is asynchronously copied to one or more other databases (slaves/replicas) that handle read operations.

• Provides horizontal read scaling.
• Enhances data availability and can be used for backups.
• Slaves are eventually consistent with the master.

Memory trick: To scale reads wide, have a master and many slaves ride.

A disaster recovery strategy where a secondary database server is continuously updated with changes from the primary, often via log shipping, making it ready to take over with minimal delay and data loss.

• Secondary server is partially active (e.g., database instance running, but not applications).
• Data is frequently updated from the primary, typically via transaction logs.
• Offers a balance between RPO/RTO and cost/complexity compared to hot or cold standby.

Memory trick: Remember, RPO and RTO are like a compass guiding your DR choice.

Data sovereignty is the concept that digital data is subject to the laws of the country in which it is stored. This often implies that data must physically reside within the borders of its originating country or region.

• Data subject to local laws.
• Often requires in-country storage.
• Driven by national security, privacy, and economic concerns.

Memory trick: Sovereignty keeps data home.

A SQL command used to display the execution plan of a given SQL statement, detailing how the database will process the query.

• Shows join order, index usage, table scans, and estimated costs.
• Crucial tool for query performance tuning and optimization.
• Syntax varies slightly by database system (e.g., `EXPLAIN PLAN`, `EXPLAIN ANALYZE`).

Memory trick: EXPLAIN 'Explains' the query's 'Plan'.

A PostgreSQL command that rewrites the entire contents of a table and its associated indexes to disk, reclaiming all dead tuples and compacting the table.

• Reclaims all wasted disk space (bloat).
• Acquires an exclusive lock on the table, blocking all other operations.
• Should be used judiciously due to downtime implications.

Memory trick: VACUUM FULL: Sucks up all the bloat.

Strategies employed to ensure that deadlocks never occur in a database system, typically by breaking one of the four necessary conditions for a deadlock.

• One key strategy is consistent lock ordering.
• Can also involve using shorter transactions or avoiding certain lock types.
• Different from deadlock detection and resolution, which handle deadlocks after they occur.

Memory trick: Preventing deadlocks means planning lock acquisition like a dance.

Database sizing involves estimating the total storage space required based on data volume, record size, retention period, and overhead for indexes, logs, and system files.

• Estimate record size (bytes per row).
• Project data growth rate (records per period).
• Determine data retention policy (how long data is kept).
• Factor in overhead for indexes, logs, and system files (typically 10-50%).

Memory trick: Record 'R'etention 'O'verhead 'T'otal.

Software component in a high-availability cluster responsible for monitoring node health, managing resources, and orchestrating automatic failover.

• Detects node failures.
• Maintains cluster quorum.
• Initiates and manages failover of services/resources.

Memory trick: The Manager orchestrates the cluster dance.

A security measure that temporarily or permanently disables a user account after a predefined number of consecutive failed login attempts.

• Prevents brute-force and password guessing attacks.
• Configurable parameters: threshold, lockout duration, reset time.
• Can be implemented at the operating system, database, or application level.

Memory trick: Authentication needs guardians to protect the login gate.

A high-availability mechanism that ensures a failed or unresponsive cluster node is truly isolated from shared resources to prevent data corruption (split-brain).

• Prevents split-brain scenarios.
• Forcefully disconnects/powers off a problematic node.
• Often implemented via power switches, storage locks, or network switches.

Memory trick: Fencing 'Fences' off bad nodes.

Metadata maintained by the database system that describes the data distribution within columns and indexes, used by the query optimizer to estimate query costs and choose execution plans.

• Crucial for the query optimizer to select efficient execution plans.
• Become outdated after significant data modifications (inserts, updates, deletes).
• Need to be regularly updated, either manually or via auto-update mechanisms.

Memory trick: Optimizer needs fresh stats for good plans.

A non-clustered index that includes all columns referenced in a query (SELECT, WHERE, ORDER BY, GROUP BY clauses), allowing the database to satisfy the query entirely from the index without accessing the base table.

• Significantly speeds up specific queries.
• Reduces disk I/O by avoiding table lookups.
• Does not affect the physical storage order of the data.
• Can be larger than simple non-clustered indexes due to included columns.

Memory trick: COVERING indexes 'cover' all your query needs, no table trips!

A column attribute in SQL databases that automatically generates a unique, sequential number for new rows.

• Often used for primary keys.
• Ensures unique identifiers without manual input.
• Syntax varies slightly by database system (e.g., IDENTITY, SERIAL).

Memory trick: Auto-Increment: 'Auto'matically 'Increments' the ID.

The process of allocating an appropriate amount of RAM to the `innodb_buffer_pool_size` parameter in MySQL, crucial for caching data and indexes and reducing disk I/O.

• Most critical memory parameter for InnoDB.
• Typical recommendation is 50-70% of available RAM on dedicated servers.
• Insufficient size leads to poor performance; excessive size leads to OOM errors.

Memory trick: Buffer pool is the brain of InnoDB, give it half the RAM to sustain.

A SQL constraint that ensures all values in a specified column or set of columns are distinct.

• Allows NULL values (unless also `NOT NULL`).
• Can be applied to a single column or multiple columns (composite unique key).
• Ensures data integrity by preventing duplicate entries.

Memory trick: UNIQUE is 'U'nique, 'N'o 'I'dentical 'Q'uantities 'U'nder 'E'ntry.

To restore data from a differential backup strategy, you need the last full backup and the most recent differential backup.

• Differential backups store changes since the last full backup.
• Faster to restore than incremental backups (fewer files).
• Requires more storage than incremental backups (cumulative changes).

Memory trick: Differential: Full + one Diff. Incremental: Full + many Inc.

A condition where data and index blocks are scattered non-contiguously across disk, requiring more I/O operations to retrieve complete data sets, thus degrading performance.

• Caused by frequent INSERTs, UPDATEs, and DELETEs.
• Leads to increased disk I/O and slower query execution.
• Resolved by rebuilding or reorganizing indexes and tables.

Memory trick: Fragmentation is like a messy bookshelf, reorganization tidies it up.

A cold standby system is a duplicate system that is powered off and not actively running. In a disaster, it must be powered on, configured, and data restored to it, leading to a longer Recovery Time Objective (RTO) but lower operational cost.

• System is powered off or minimally configured.
• Requires manual intervention to bring online.
• Lowest cost among standby options, but highest RTO.

Memory trick: RPO/RTO: The budget dance, cold is cheap, hot's a chance.

A measure of how unique the values in an indexed column are. High selectivity (many unique values) makes an index very effective; low selectivity (many duplicates) reduces its effectiveness.

• High selectivity means the index quickly narrows down results.
• Low selectivity means the index may still lead to scanning many rows.
• Indexes on columns with many duplicates (low selectivity) are less beneficial for performance.

Memory trick: INDEX speed depends on SELECTIVITY, not just existence!

A subset of SQL used to define and manage the database structure and schema, including creating, modifying, and deleting database objects.

• Includes CREATE, ALTER, DROP, TRUNCATE, RENAME.
• Also includes GRANT and REVOKE for permission management.
• Operates on the structure of the database, not the data content.

Memory trick: DDL defines your database's look and feel.

A database normalization form that requires a table to be in 1NF and all non-key attributes to be fully functionally dependent on the entire primary key.

• Addresses partial dependencies where a non-key attribute depends on only part of a composite primary key.
• Typically involves moving partially dependent attributes to a new table.
• Reduces data redundancy and improves data integrity.

Memory trick: 1NF is 'First' for 'Flat', 2NF 'Depends All', 3NF 'No Transitive'.

A backup type focused on long-term data retention for compliance or historical purposes, often to low-cost, high-latency storage.

• For infrequently accessed historical data.
• Optimized for low cost per GB.
• Accepts longer retrieval times.

Memory trick: Archive is for the 'Ancient' data.

A patching or upgrade strategy for clustered systems where components are updated sequentially, one at a time, allowing the overall system to remain operational throughout the process.

• Minimizes downtime for high-availability systems.
• Requires a clustered or replicated environment.
• Ensures continuous service availability during updates.

Memory trick: Rolling upgrades are like changing tires on a moving car.

A command-line utility provided with MySQL that guides users through essential initial security steps for a new MySQL server installation.

• Sets a strong password for the 'root' user.
• Removes anonymous user accounts.
• Disallows remote 'root' login.
• Removes the 'test' database and its privileges.

Memory trick: Secure 'S'tart 'S'erver 'S'trong.

A column or set of columns in a table that uniquely identifies each row in that table. It enforces entity integrity and is typically indexed for efficient data retrieval.

• Must contain unique values.
• Cannot contain NULL values (NOT NULL constraint).
• There can be only one primary key per table.
• Often used for efficient data retrieval and establishing relationships.

Memory trick: PRIMARY keys are the UNIQUE and essential ID for each record!

The principle of least privilege dictates that users, programs, or processes should be granted only the minimum level of access or permissions necessary to perform their legitimate functions, and no more.

• Reduces attack surface.
• Limits damage from compromised accounts.
• Fundamental security best practice.

Memory trick: Least Privilege means only 'just enough' keys.

A legal or regulatory requirement that dictates where an organization's data must be stored and processed, typically within specific geographic boundaries.

• Mandated by various international and national laws (e.g., GDPR, CCPA).
• Requires careful selection of cloud providers and data center regions.
• Impacts data replication, backup strategies, and disaster recovery planning.

Memory trick: Cloud compliance needs a map for data and rules.

Disk encryption is a technology that encrypts data on a hard drive or other storage device at the hardware or operating system level, protecting it from unauthorized access if the device is lost or stolen.

• Protects data at rest.
• Encrypts the entire volume or partition.
• Requires a key or passphrase to decrypt upon access.

Memory trick: Resting data needs a strong lock.

Cryptographic algorithms designed to be secure against attacks by future quantum computers, ensuring long-term confidentiality and integrity.

• Also known as Post-Quantum Cryptography (PQC).
• Aims to replace current algorithms (RSA, ECC, AES) vulnerable to quantum attacks.
• Crucial for protecting data with long-term security requirements.

Memory trick: Long-term secrets need a lock that future tech can't pick.

The practice of placing different types of database files (data, log, tempdb) on separate physical storage devices to reduce I/O contention and improve performance.

• Transaction logs benefit most from dedicated, fast storage due to sequential write patterns.
• Separating data and log files prevents read operations from competing with write operations.
• `tempdb` files also benefit from dedicated, fast storage to avoid contention.

Memory trick: Logs are fast, data is balanced, backups are slow.

A comprehensive test of a disaster recovery plan that simulates a complete outage and attempts to restore all critical systems and data to full operation.

• Simulates a major disaster (e.g., data center outage).
• Verifies RTO and RPO objectives.
• Involves restoring all critical applications and data.

Memory trick: Full test means full disaster simulation.

A high-performance storage solution combining a Storage Area Network (SAN) utilizing Fibre Channel protocol for connectivity and all-flash arrays (AFAs) for storage.

• Fibre Channel (FC) provides dedicated, low-latency, high-bandwidth block storage connectivity.
• All-Flash Arrays (AFAs) use SSDs exclusively, offering maximum IOPS and minimal latency.
• Ideal for mission-critical, I/O-intensive workloads like financial transaction processing and high-performance databases.
• Offers superior performance and scalability compared to direct-attached storage or NAS.

Memory trick: Fast 'F'lash 'F'ibre 'F'inancial 'F'ixes.

A non-clustered index that includes all the columns required by a query, allowing the query to be satisfied entirely from the index without visiting the base table.

• Optimizes query performance by avoiding table lookups.
• Must contain all columns in the SELECT list, WHERE clause, and ORDER BY clause.
• Can increase index size and write overhead.

Memory trick: Indexes are shortcuts, covering indexes are express lanes.

A method of executing SQL queries where the SQL command and the data values are sent separately to the database. Placeholders are used in the SQL command for where data values will be inserted.

• Prevents SQL injection vulnerabilities.
• Improves performance by allowing the database to pre-compile the query plan.
• Handles data type conversion and special character escaping automatically.
• Standard practice for secure database interaction.

Memory trick: PARAMETERIZE your queries to BLOCK SQL INJECTION!

A database recovery model (specific to SQL Server, but conceptually similar in others) that provides a balance between full logging and minimal logging, allowing certain bulk operations to be minimally logged for performance.

• Minimizes transaction log space for bulk operations.
• Still supports point-in-time recovery for non-bulk operations.
• Requires log backups to truncate the log.

Memory trick: Bulk-Logged: A 'bulk' deal for speed, but logs enough for recovery.

Achieving low RPO (minimal data loss) and low RTO (minimal downtime) typically requires continuous data replication combined with automated failover mechanisms.

• RPO < 5 min, RTO < 1 hour are demanding.
• Backups alone are insufficient for these targets.
• Asynchronous replication with automation is a common solution.

Memory trick: Low RPO/RTO needs Active replication and Auto-failover.

The process of adjusting database configuration parameters that control the memory allocated for sorting, hashing, and temporary tables during query execution.

• Crucial for performance of complex queries with JOINs, GROUP BY, ORDER BY.
• Insufficient memory leads to 'spilling to disk', causing I/O bottlenecks.
• Parameters often include `work_mem` (PostgreSQL), `sort_buffer_size` (MySQL), `temp_buffers`.

Memory trick: Temp buffers: Keep your 'work' in memory, not on slow disk.

A database concept that ensures relationships between tables remain consistent. It dictates that foreign key values must either match a primary key value in a related table or be NULL.

• Prevents orphan records.
• Enforced using foreign key constraints.
• Actions like CASCADE, SET NULL, or RESTRICT can be defined for ON DELETE/ON UPDATE.

Memory trick: Referential integrity is about linked records, like a chain.

Fine-Grained Access Control (FGAC) is an advanced access control mechanism that allows database administrators to define highly specific authorization policies, often down to the individual row and column level within a table, based on user context or attributes.

• Provides row-level security (RLS) and column-level security (CLS).
• Restricts data visibility based on user attributes or policies.
• More granular than traditional RBAC.

Memory trick: FGAC: Every cell has its own key.