CompTIA DataSys+ (DS0-001) flashcards

A NoSQL database that uses graph structures (nodes, edges, and properties) to store and query highly interconnected data.

• Optimized for relationships and network structures.
• Excels at traversing complex, recursive connections.
• Used for social networks, recommendation engines, fraud detection.

Memory trick: Graph databases 'Graph' out 'Relationships' perfectly.

Different methods of copying and synchronizing data between database instances to ensure high availability, fault tolerance, and read scalability.

• Asynchronous: Primary commits first, then replicates. Fastest, potential data loss.
• Synchronous: Primary commits only after replica confirms receipt/commit. Zero data loss, higher latency.
• Semi-synchronous: Primary commits after replica acknowledges receipt. Balances performance and data safety.

Memory trick: Synchronize for safety, async for speed, semi for a blend.

A network security device that monitors network and/or system activities for malicious or unwanted behavior and can react in real-time to block or prevent those activities.

• Actively blocks detected threats.
• Can be signature-based, anomaly-based, or policy-based.
• Often deployed in-line to inspect and filter traffic.

Memory trick: Real-time response is like an alarm and a shield for immediate danger.

Metadata about the data stored in a table or index, used by the query optimizer to determine the most efficient execution plan for SQL queries.

• Includes information like data distribution, number of rows, and column cardinality.
• Outdated statistics lead to poor query plans and performance degradation.
• Should be updated regularly, especially after significant data changes.

Memory trick: Stats are the map for the query's journey.

The process of collecting and updating metadata about the data distribution and cardinality within database tables and indexes, used by the query optimizer.

• Crucial for the query optimizer to choose efficient execution plans.
• Should be updated regularly, especially after significant data changes.
• Can be updated manually or automatically.

Memory trick: Optimizer needs fresh 'stats' to plan the best route.

A SQL DDL command used to define a new table in a relational database, specifying its name, column names, data types, and constraints.

• Defines the table's structure.
• Includes column definitions with data types (e.g., INT, VARCHAR, DECIMAL).
• Allows specifying constraints like PRIMARY KEY, NOT NULL, DEFAULT.

Memory trick: CREATE a TABLE with COLUMNS and their TYPES, then ADD CONSTRAINTS!

An attack where multiple compromised computer systems attack a target, such as a server, website, or other network resource, and cause a denial of service for users of the targeted resource.

• Uses a 'botnet' of compromised machines.
• Aims to overwhelm the target's resources (bandwidth, CPU, memory).
• Results in legitimate users being unable to access the service.

Memory trick: Network attacks are like different ways to disrupt traffic or steal secrets.

A performance metric indicating the percentage of data requests that were satisfied from the database's in-memory cache rather than from slower disk storage.

• Higher ratio means fewer disk I/O operations.
• A low ratio indicates potential memory shortage or inefficient queries.
• Optimal ratios vary by database system and workload (often 90%+).

Memory trick: Buffer hit ratio: how often you 'hit' the fast memory.

Disk input/output operations performed by a database when memory allocated for sorting, hashing, or other intermediate query operations is insufficient, forcing data to be written to and read from temporary files.

• Indicates insufficient memory for query processing.
• Leads to significant performance degradation due to disk access.
• Often seen with complex queries like large sorts, joins, or aggregations.

Memory trick: Memory too small, disk takes the fall.

A database constraint that enforces domain integrity by ensuring that all values in a column meet a specified condition or range.

• Evaluates a boolean expression for each new or updated row.
• Prevents invalid data from being inserted or updated into a column.
• Can be applied to a single column or multiple columns within a table.

Memory trick: Keys for identity/relations, Check for values, Not Null for presence.

A database security feature that encrypts specific columns within a table, providing granular protection for sensitive data. Decryption typically occurs at the application layer or by authorized database users.

• Encrypts only designated columns, not the entire database.
• Offers fine-grained control over sensitive data.
• Requires application or user-level decryption keys for access.

Memory trick: Columns have their own locks, not the whole building.

In an Active-Passive cluster, one server (active) handles all requests, while another server (passive) remains idle, constantly synchronized and ready to take over if the active server fails. This provides high availability with automatic failover.

• One node is active, others are passive (standby).
• Automatic failover to a passive node upon primary failure.
• Ensures high availability with minimal downtime.

Memory trick: Active-Passive: One works, one waits, ready to swap without debate.

A code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution.

• Exploits vulnerabilities in applications that construct SQL queries from unsanitized user input.
• Can lead to unauthorized data access, modification, or deletion, and even shell access.
• Mitigated by using parameterized queries, prepared statements, and input validation.

Memory trick: Bad input can break the database.

The process of artificially creating new data that statistically mirrors real-world data but does not contain any actual sensitive information.

• Maintains statistical properties and relationships of original data.
• Used for testing, development, and analytics without PII exposure.
• Requires sophisticated algorithms to ensure realism and utility.

Memory trick: Anonymization uses tricks to hide identities.

Synchronous replication ensures that a write operation is committed to both the primary and secondary databases before the primary acknowledges the write to the application. This guarantees zero data loss (RPO = 0) but can introduce latency.

• Guarantees zero data loss (RPO = 0).
• Transaction commits on primary and secondary before acknowledgment.
• Requires high-speed, low-latency network to minimize performance impact.

Memory trick: Sync for zero loss, Async for speed, Snapshot for moments, Transaction for need.

A high-availability configuration where one node (active) processes requests, and another (passive) remains online, receiving replicated data, ready to take over if the active node fails.

• One node is active, one is passive (standby).
• Passive node receives replicated data.
• Passive node takes over upon primary failure.

Memory trick: Passive node waits, like a 'standby' player.

The process of replacing sensitive data with a unique, non-sensitive identifier (token) that retains the data's format and original meaning without compromising its security.

• Primarily used for payment card industry (PCI) compliance.
• Tokens are typically randomly generated or cryptographically derived.
• The original sensitive data is stored securely in a separate token vault.

Memory trick: Advanced obfuscation is like using magic to hide the real treasure.

A method of constructing SQL queries where placeholders are used for user input, and the input values are passed separately to the database.

• Separates SQL code from data, preventing malicious input from being executed.
• Often implemented using 'prepared statements' in programming languages.
• The most effective defense against SQL injection vulnerabilities.

Memory trick: Parameterize your input, don't just concatenate!

In a database using the FULL recovery model, transaction log backups are crucial operations that capture all transactions since the last log backup and, upon successful completion, mark inactive portions of the log for truncation, freeing up space.

• Essential for point-in-time recovery.
• Truncates the inactive portion of the transaction log.
• Prevents the transaction log from growing indefinitely.

Memory trick: Log full? Backup the log to clear the path!

A logical operator in SQL that allows you to specify multiple values in a WHERE clause condition, checking if a column's value matches any value within a specified list.

• Provides a concise alternative to multiple OR conditions.
• Can be used with subqueries to dynamically generate the list of values.
• Often more efficient than a series of OR conditions for large lists.

Memory trick: WHERE clauses filter, IN is for 'among these choices'.

A database object that provides quick lookup of data in a database table, similar to an index in a book.

• Speeds up data retrieval operations (SELECT queries).
• Slows down data modification operations (INSERT, UPDATE, DELETE).
• Can be created on one or more columns.
• Primary keys automatically have a unique index.

Memory trick: Indexes are your query's fast lane.

The count of currently established and active connections to a database server, representing concurrent users or application processes interacting with the database.

• High numbers can indicate connection leaks or heavy load.
• Low numbers might suggest application issues or idle resources.
• Monitored to understand database concurrency and resource usage.

Memory trick: Active sessions are the 'pulse' of database usage.

A database object that contains the results of a query, physically stored in the database and periodically refreshed.

• Improves performance for complex queries, especially aggregations.
• Data is pre-computed and stored.
• Requires refresh mechanisms to keep data up-to-date.

Memory trick: Materialized views store the 'material' for quick reports.

The process of encoding data when it is stored on a physical medium (e.g., hard drives, backup tapes) to protect it from unauthorized access.

• Protects data even if the storage device is stolen or compromised.
• Requires a decryption key to access the original data.
• Commonly used for sensitive data on databases, file systems, and backups.

Memory trick: Resting data needs a lock, like a safe for a sleeping treasure.

The first level of database normalization, requiring that all columns in a table contain atomic (indivisible) values and that there are no repeating groups of columns.

• Each cell contains a single value.
• No repeating groups of columns.
• Eliminates multi-valued attributes.

Memory trick: 1NF: Atomic! 2NF: No partial! 3NF: No transitive!

A pre-compiled collection of SQL statements and procedural logic (e.g., IF-THEN-ELSE, loops) that is stored in the database and can be executed by name.

• Encapsulates complex business logic and data validation.
• Improves performance by reducing network traffic and pre-compilation.
• Enhances security by granting permissions to procedures rather than underlying tables.
• Promotes code reusability and consistency.

Memory trick: STORED PROCEDURES are like the database's BRAIN for complex rules!

Multi-Master replication (also known as Active-Active replication) is a database architecture where multiple database servers (masters) can accept read and write operations. Changes made on one master are replicated to all other masters, allowing for load balancing, high availability, and reduced latency for geographically dispersed users.

• Multiple nodes can accept read and write operations.
• Improves performance by allowing local writes for dispersed users.
• Requires robust conflict resolution mechanisms and tolerates slight replication delays.

Memory trick: Multi-Master: Everyone writes, everyone reads, globally distributed needs.

The process of controlling the size and content of a database's transaction log to ensure data recoverability and prevent disk space issues.

• Essential for point-in-time recovery.
• Unmanaged logs can consume all disk space.
• Backups are key to log truncation.

Memory trick: Log files grow like weeds, prune them with backups.

The maximum acceptable amount of data (measured in time) that can be lost from a database during a disaster.

• A lower RPO means less data loss is tolerated.
• RPO is typically achieved through frequent backups or real-time data synchronization.
• Synchronous solutions offer the lowest RPO.

Memory trick: RPO is data lost, RTO is time spent down.

The cumulative or average time that database processes spend waiting to acquire locks on database resources.

• High lock wait times indicate contention for shared resources.
• A key metric for diagnosing concurrency and blocking issues.
• Monitoring this helps identify queries or transactions causing excessive locking.

Memory trick: Each metric tells a specific story.

One of the ACID properties, ensuring that a transaction is treated as a single, indivisible unit of work. Either all of its operations succeed, or none of them do.

• Crucial for data consistency.
• Determines the scope of locks held during writes.
• Long-running transactions can lead to contention and blocking.

Memory trick: Contention is like traffic, reduce it with smaller trips.

A SQL Data Definition Language (DDL) command used to define a new table in a relational database.

• Specifies table name, column names, and data types.
• Allows definition of constraints like PRIMARY KEY, UNIQUE, NOT NULL.
• Foundation for structuring data storage.

Memory trick: DDL: Define, Design, Destroy, Develop.

A consistency model in distributed computing that guarantees that if no new updates are made to a given data item, eventually all accesses to that item will return the last updated value.

• Common in NoSQL databases for high availability and scalability.
• Data may not be immediately consistent across all nodes.
• Sacrifices immediate consistency for performance and availability (part of CAP theorem).

Memory trick: Eventually, all data will agree, but not always at once.

Schema validation ensures that the database structure (tables, columns, data types, constraints) aligns with application requirements and maintains data integrity.

• Prevents data corruption and inconsistencies.
• Includes verifying primary/foreign keys, unique constraints, and check constraints.
• Ensures data types in application code match database column types.
• Often performed in a staging environment before production deployment.

Memory trick: Validate 'R'elationships and 'D'ata types for 'A'ccuracy.

ACID is an acronym defining a set of properties that guarantee valid transactions in a database: Atomicity, Consistency, Isolation, Durability.

• Atomicity: All operations in a transaction either succeed or fail as a whole.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions execute without interfering with each other.
• Durability: Once a transaction is committed, its changes are permanent, even in case of system failure.

Memory trick: ACID is best friends with the 'R' in Relational.

A subset of SQL statements used for managing data within schema objects, such as retrieving, inserting, updating, and deleting data.

• Includes SELECT, INSERT, UPDATE, DELETE.
• Operates on the data content of tables.
• Distinct from DDL (Data Definition Language) which manages schema.

Memory trick: DML is about data: Select, Insert, Update, Delete.

A configuration setting in virtualization platforms that guarantees a minimum amount of CPU, memory, or I/O resources to a virtual machine, preventing resource contention.

• Ensures predictable performance for critical VMs.
• Prevents resource starvation caused by other VMs.
• Reduces hypervisor overhead in allocating resources.

Memory trick: To guarantee performance, reserve the resources you need.

The practice of encrypting specific, sensitive columns within a database table, rather than the entire database or table.

• Provides granular protection for highly sensitive data.
• Data remains encrypted on disk and in memory until explicitly decrypted by authorized processes/users.
• Requires key management and can impact query performance on encrypted columns.

Memory trick: TDE for files, Column for fields, RLS for rows, Masking for fakes.

A deployment strategy where application or database components are updated incrementally, instance by instance or in small batches, allowing the old and new versions to coexist and serve traffic during the transition.

• Minimizes downtime by avoiding a complete system shutdown.
• Allows for gradual introduction of changes.
• Requires backward compatibility between old and new versions during the transition.

Memory trick: Roll out the changes one step at a time, like a red carpet.

A SQL DDL command used to assign specific database privileges to users or roles.

• Controls access to database objects (tables, views, functions).
• Syntax: `GRANT privilege_type ON object_name TO user_or_role;`
• Common privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALL PRIVILEGES.

Memory trick: GRANT 'G'ives 'R'ights 'A'nd 'N'o 'T'rouble.

A NoSQL database type that stores data in flexible, semi-structured documents, typically JSON, BSON, or XML format.

• Schemaless or schema-flexible.
• High scalability and availability.
• Ideal for handling large volumes of unstructured or semi-structured data.

Memory trick: NoSQL is a DOC-KEY-GRAPH-COL family party!

An incremental backup copies only the data that has changed since the last full, differential, or incremental backup. It is efficient for storage and network bandwidth but requires restoring the full backup and all subsequent incremental backups for full recovery.

• Copies only data changed since the last backup (any type).
• Optimizes storage space and backup time.
• Recovery involves restoring the last full backup and all subsequent incremental backups in sequence.

Memory trick: Full, Diff, Inc: Each a step to recovery, choose wisely for your data's journey.

A type of relationship in a relational database where one record in Table A can be associated with multiple records in Table B, and one record in Table B can be associated with multiple records in Table A.

• Requires an intermediary (junction/associative) table to resolve.
• Each record in table A can have multiple matching records in table B.
• Each record in table B can have multiple matching records in table A.

Memory trick: Relationships describe how tables 'talk' to each other.

Achieving an RPO of 0 (zero data loss) typically requires real-time data synchronization solutions like synchronous replication or stretched clusters.

• RPO=0 means no data loss is acceptable.
• Backups alone cannot achieve RPO=0.
• Synchronous replication is a key technology for RPO=0.

Memory trick: To lose zero, you must replicate in 'Sync'.

A class of non-relational databases that provide a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.

• Handles unstructured, semi-structured, and polymorphic data.
• Offers flexible schemas (schema-less or schema-on-read).
• Designed for horizontal scalability and high availability.
• Includes document, key-value, column-family, and graph databases.

Memory trick: NoSQL is for when SQL just won't 'flex' enough.

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.

An index created on multiple columns of a table, allowing the database to efficiently search and retrieve data based on combinations of those columns.

• Useful for queries with WHERE clauses involving multiple columns.
• The order of columns in the index matters for query optimization.
• Can cover multiple columns in a single index lookup.

Memory trick: A composite index is like a multi-tabbed file folder.

A database architecture where a primary 'master' database handles all write operations, and one or more 'replica' databases asynchronously receive copies of the data, primarily serving read operations.

• Enhances read scalability and performance by distributing read load.
• Provides high availability and disaster recovery capabilities.
• Replicas can be used for reporting or analytical queries without impacting the master.

Memory trick: Scale reads, replicate writes, stay agile.

Different types of storage media (e.g., SSDs, HDDs) with varying performance, cost, and capacity characteristics, selected based on database workload requirements.

• SSDs (SATA, NVMe) offer high IOPS and low latency, ideal for transactional workloads.
• HDDs offer high capacity at a lower cost, suitable for archival or large-volume analytical data.
• Choosing the right tier balances performance needs with budgetary constraints.

Memory trick: Cost-performance-capacity triangle: balance wisely.

Transparent Data Encryption (TDE) is a technology that encrypts and decrypts database data files, log files, and backups in real-time at the I/O layer, without requiring changes to existing applications. It protects data at rest.

• Encrypts entire database files.
• Transparent to applications.
• Protects data at rest (storage media).
• Encrypts data, log, and backup files.

Memory trick: TDE hides the whole database.

A security principle where users and systems are granted only the minimum necessary permissions to perform their authorized functions.

• Limits the potential damage from accidental errors or malicious actions.
• Reduces the attack surface.
• Applies to users, processes, and applications.

Memory trick: Principles are like rules for who gets the key to which door.

A simulated execution of database schema or data changes against a target environment (typically a clone of production) to identify potential issues without making actual modifications.

• Generates SQL scripts without executing them.
• Helps identify syntax errors, conflicts, and unexpected behaviors.
• Crucial for reducing risk in production deployments.

Memory trick: Before building, always rehearse the schema's journey.

A type of NoSQL database that stores data as a collection of key-value pairs, where each key is unique and maps to a specific value.

• Offers extreme scalability and high availability.
• Simple data model, ideal for fast reads and writes.
• Often used for caching, session management, and real-time data.

Memory trick: Key-Value stores are 'Key' for 'Value' in speed and scale.

Tables are the primary storage structures in a relational database, organizing data into rows and columns with defined data types and constraints.

• Fundamental building block of a relational database.
• Composed of rows (records) and columns (fields).
• Each column has a specific data type (e.g., INT, VARCHAR, DATE).

Memory trick: Tables are the 'T' in 'The foundation' of any database.

Asynchronous replication allows the primary database to commit transactions and respond to the application before the data is fully replicated to the secondary database. This offers better performance and lower network requirements but introduces a risk of data loss (non-zero RPO) if the primary fails before replication completes.

• Primary commits independently of secondary.
• Lower latency and network requirements than synchronous.
• Acceptable for non-zero RPO (potential data loss).

Memory trick: Async: cheaper, faster writes, but data loss bites.

A Failover Cluster Instance (FCI) is a single instance of a database server that is installed across multiple Windows Server Failover Cluster (WSFC) nodes and utilizes shared storage. If one node fails, the database instance automatically fails over to another node in the cluster, which takes ownership of the shared storage and brings the instance online.

• Uses shared storage (e.g., SAN).
• Database instance fails over between nodes.
• No data replication needed between nodes; all access same files.

Memory trick: FCI: Shared disk, instant switch, no data replication glitch.

The lowest SQL standard isolation level, allowing transactions to read uncommitted changes made by other concurrent transactions, leading to 'dirty reads' but maximizing concurrency.

• Permits dirty reads, non-repeatable reads, and phantom reads.
• Offers the highest concurrency but the least data consistency.
• Suitable for scenarios where approximate data is acceptable and blocking must be avoided.

Memory trick: Isolation levels are like privacy settings for your data reads.

A virtual table based on the result-set of a SQL query. It does not store data itself but rather displays data stored in other tables.

• Simplifies complex queries by abstracting underlying table structures.
• Can be used for security, restricting user access to specific rows/columns.
• Data is dynamic, always reflecting the current state of the base tables.

Memory trick: Views are windows, procedures are scripts.

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.