CompTIA DataSys+ (DS0-001) (DS0-001) (DS0-001) — Questions 76–150

ALTER TABLE modifies the structure of an existing table, so it adds the new column to Employees without dropping data. CREATE TABLE builds a new table and UPDATE changes row values, neither of which alters the schema.

Transparent Data Encryption encrypts tablespaces and credentials at rest, and integrating it with an external key management system via PKCS#11 moves master key custody to a separate hardware or software appliance. This satisfies both the encryption-at-rest requirement and the separation-of-duties control by keeping key management independent of the database server.

Disk I/O latency reveals whether storage can sustain the database's read and write workload; rising latency indicates the deployment is undersized for its transaction volume. It satisfies the stem's requirement for an indicator proving correct sizing for performance.

RPO defines the maximum tolerable data loss measured in time. Transaction log backups every 10 minutes capture changes more frequently than the 15-minute RPO, so at most 10 minutes of data is lost, meeting the requirement.

LIMIT restricts the result set to a specified number of rows after ORDER BY has sorted spend descending, satisfying the requirement to return only the first 10 customers. It applies after sorting, so the highest spenders are retained rather than an arbitrary subset.

A CTE names the pre-aggregated result set, separating it from the join logic so the query reads top-down and is easier to maintain. It also supports recursion, unlike a plain subquery, which satisfies the readability and recursive capability requirement.

Storing each migration script in a repository gives schema changes the same versioning as application code, so any environment can be rebuilt at a known revision. This directly satisfies the stem's benefit of traceable, repeatable schema evolution rather than ad-hoc manual alterations.

INNER JOIN returns only rows with matching keys in both tables, making it one of SQL's core join types. The stem asks for join types, and INNER JOIN is a distinct, standard category alongside LEFT, RIGHT and FULL OUTER joins.

Database bloat is wasted space within data pages left by deleted or updated rows that autovacuum has not yet reclaimed. It inflates table size beyond live row data, so capacity projections must account for it alongside transaction growth and row size.

Imposing a uniform access order means every transaction acquires locks in the same sequence, eliminating the circular-wait condition that produces deadlocks. This directly addresses the scenario's deadlock frequency by removing one of the four Coffman conditions rather than merely retrying after failure.

A lag feature shifts a variable's values backward by a defined number of time steps, so each observation carries the value from an earlier period. This exposes temporal dependence to the model, letting it learn autocorrelation and trends rather than treating each timestamp as independent.

TRUNCATE removes all rows by deallocating the table's data pages, a minimally logged operation that resets the table to empty while preserving its schema, columns, and indexes. This satisfies the stem's requirement to delete every row permanently yet keep the table definition intact, unlike DROP, which eliminates the structure itself.

Dynamic Data Masking applies masking rules at query execution, so the HR analytics team sees obfuscated values while the stored data remains unchanged on disk. This satisfies the stem's constraint of obscuring PII at the query level without altering underlying storage, unlike encryption or static masking, which modify data at rest.

Geographic separation determines whether a single regional outage can affect both sites simultaneously. Placing the DR replica far enough away avoids correlated failures from the same power grid, flood plain or network backbone, directly satisfying the DR requirement for site-independent resilience rather than merely duplicating data locally.

AVG is a standard SQL aggregate function that returns the arithmetic mean of a numeric column across rows, satisfying the stem's requirement. It collapses multiple rows into one value, distinguishing aggregates from scalar functions that operate per row.

A FOREIGN KEY constraint establishes a link between data in two tables, ensuring that the value in one table matches a value in another. It enforces referential integrity, preventing orphaned records and maintaining consistent relationships. This is a key mechanism for data integrity across related tables.

TLS provides secure, encrypted communication channels.

When the transaction log cannot grow to accommodate incoming transactions, SQL Server raises error 9002, the transaction log full error, and may suspend further writes. This symptom directly reflects the undersized log constraint in the stem, distinguishing it from data file growth issues.

UPDATE modifies existing rows in a table, setting new column values for records matched by the WHERE clause. This satisfies the stem's constraint of changing existing records, unlike INSERT, which adds new rows, or DELETE, which removes them.

Indexes on frequently filtered columns let the optimiser seek directly to matching rows instead of scanning the whole table, cutting I/O for WHERE, JOIN and ORDER BY predicates. This satisfies the stem's demand for a legitimate query-performance optimisation.

WORM storage policies lock objects so they cannot be altered or deleted until the retention period expires, satisfying the seven-year immutability requirement. Object Lock in compliance mode enforces this even against privileged users, supporting regulatory audit.

Asynchronous streaming replication allows the primary to commit transactions without waiting for standby acknowledgment, minimizing write latency. Hot standby servers can serve read-only queries, and a load balancer can distribute the read workload across them, improving read performance. This setup also provides high availability because a standby can be promoted if the primary fails. Given the 10:1 read-to-write ratio, this is the most efficient configuration.

A scatter plot places one numerical variable on each axis, so each point represents a paired observation. This reveals correlation, trends, and clusters between the two measures, which bar, line, and pie charts cannot show because they plot a single measure against categories or time.

LIKE performs pattern matching on string columns using wildcards such as % and _, satisfying the requirement to search for a pattern rather than an exact value. Equality operators match whole values only, so LIKE is the operator that filters rows by substring or pattern.

Updating statistics refreshes the optimizer's metadata about column value distribution and cardinality, letting it choose a more efficient plan. Stale or missing statistics cause poor cardinality estimates, so refreshing them directly addresses the poor query performance.

An index maintains a sorted structure on the queried column, letting the database locate matching rows without scanning the entire table. This directly satisfies the stem's read-performance constraint, trading additional storage and slower writes for faster lookups on that column.

LDAP/Active Directory Integration provides the centralised directory service the company requires, letting database administrators authenticate against one authoritative store rather than separate local accounts. It satisfies the stem's centralised identity management constraint by consolidating administrator credentials, group membership and authentication policy into a single directory that databases can query directly.

Extended Events captures live query execution plans with minimal overhead, satisfying the need to inspect currently running queries on the CPU-bound server. Unlike deprecated SQL Trace, its lightweight, event-based architecture lets you target plan-related events such as query_post_execution_showplan, revealing full table scans without materially worsening the existing CPU pressure.

UNION concatenates the row sets from two SELECT statements into one result set, eliminating duplicate rows by default. It satisfies the stem's requirement to combine results from two different queries, provided both have matching column counts and compatible data types in corresponding positions.

A hash join reads the smaller currency table once, builds an in-memory hash table on the currency code, then probes it for each transaction row. This converts the nested loop's repeated scans of the large table into a single pass, which is far more efficient when the join column is not indexed and the large table dominates the cost.

DISTINCT filters the result set so each unique combination of selected column values appears only once, directly satisfying the requirement to eliminate duplicate rows. It operates on the full SELECT list rather than a single column, unlike GROUP BY, which aggregates rows and is intended for summary calculations.

For an OLTP workload dominated by small random reads, the buffer pool is the primary mechanism for reducing physical I/O. With 512 GB of RAM, allocating a large portion to the buffer pool allows frequently accessed pages to remain in memory, dramatically lowering disk reads and improving response time. This is the standard tuning step for this scenario.

In FULL recovery model, restoring a differential backup requires the base full backup, then the differential, then all transaction log backups taken after the differential to reach the point of failure. This sequence preserves the log chain and allows point-in-time recovery to 10:07 AM.

S3 Lifecycle expiration rules automatically delete objects once a defined retention period elapses, satisfying GDPR erasure without manual intervention. This directly enforces the Right to be Forgotten at the object level, unlike replication or versioning settings, which retain data.

A nested loop join is often chosen when one input is small and can be efficiently probed, especially if an index exists on the join column of the inner table. Here, the Regions table is small, and if it has an index on the join column, the optimizer can quickly find matching rows for each row of the larger Sales table. This is a classic scenario for nested loop joins.

Physical destruction renders platters unrecoverable, while cryptographic erasure destroys the key protecting encrypted data, making it permanently inaccessible. Both defeat forensic recovery, unlike overwriting or degaussing, satisfying the requirement for the most secure handling of sensitive drives.

Asynchronous commit acknowledges transactions before the standby confirms receipt, so a primary failure can lose recently committed transactions not yet replicated. The risk is data loss, not corruption or split-brain, because the standby simply lacks those final writes.

Transformation applies business rules, cleansing and validation to extracted data before it is loaded, so checks such as age being positive are enforced here. Extraction only retrieves source data and loading writes it to the target, neither of which validates business rules.

Referential integrity constraints ensure foreign keys in fact and dimension tables always match a valid primary key, preventing orphaned records. This blocks inconsistent relationships entering the warehouse, directly satisfying the integrity control the question demands.

When the parameter's data type differs from the column's, the optimiser applies an implicit conversion to the column side, making the predicate non-sargable and forcing a scan instead of an index seek. This satisfies the execution plan's implicit conversion warning.

This design places a foreign key in the Orders table pointing to the Customers table. Each order references exactly one customer, and a customer can be referenced by many orders. This precisely implements the one-to-many relationship required by the scenario, with no unnecessary constraints.

Key-value stores are a core NoSQL category, pairing each key with an opaque value and offering simple lookups without a fixed relational schema. This satisfies the question's demand for a common NoSQL database type, alongside document, column-family and graph stores.

An index maintains a separate ordered structure pointing to table rows, letting the database locate matching records without scanning the whole table. This directly reduces the I/O needed for retrieval, which is its primary function.

SUM totals numeric values across rows, satisfying the stem's requirement for a widely supported aggregate. It is part of the SQL standard and implemented across virtually all relational databases, including SQL Server, Oracle, PostgreSQL and MySQL. This ubiquity confirms it as one of the three commonly supported functions the question demands.

A read-only replica receiving log-shipped changes provides a separate copy of the data that can be queried without interfering with the primary. If the replica is consistent at a point in time, the report sees a stable dataset even while the primary continues to accept inserts, which supports consistent reporting without blocking writers.

Interactive filters let users narrow the displayed dataset to the dimensions and ranges they need, so the dashboard presents only relevant subsets rather than every record at once. This directly reduces cognitive overload while preserving access to the full underlying data.

Analysing access logs reveals which accounts, IP addresses and queries touched the affected data, establishing breach scope and root cause. This forensic step is essential during PII incident response to determine exposure extent and guide containment.

Generalizing fields such as postal code and age into broader ranges, and suppressing rare combinations, ensures each record is indistinguishable from at least k-1 others. This reduces re-identification risk from quasi-identifier combinations while leaving counts and distributions usable for aggregate statistics, matching the policy's dual requirement for the published permit dataset.

A clustered columnstore index stores data in a columnar format, which is highly efficient for aggregation queries that scan large amounts of data. It provides high compression and batch-mode execution, significantly speeding up queries that group by product_category and sum sales. It eliminates the need for full table scans by reading only relevant columns.

Biometric data is a special category under GDPR and protected personal information under CCPA because it uniquely identifies a person through physical or behavioural characteristics. This satisfies the scenario's requirement to name a data category covered by privacy legislation.

Quarantining records with NULL or malformed emails enforces the business rule by preventing invalid customer records from entering the trusted dataset while preserving them for correction. This approach handles both missing and invalid values and supports data quality remediation rather than silent data loss, which aligns with the requirement that every customer record must have a valid email.

VPC endpoints let workloads reach storage services over private addresses inside the VPC, bypassing the public internet entirely. This satisfies the securing-storage requirement by removing internet exposure, and endpoint policies can further restrict which buckets or resources are reachable.

An Always On availability group can host a secondary replica configured to allow read-only connections, offloading reporting queries from the primary. Long-running scans then execute on the secondary, preserving primary CPU and I/O for OLTP transactions. This directly satisfies the requirement to isolate reporting from the transactional workload without additional licensing for a separate reporting server.

ALTER TABLE ... SHRINK SPACE reclaims fragmented space in-place, moving rows downward and resetting the high-water mark without dropping the table. It satisfies the stem's requirement to defragment while preserving the table structure, unlike MOVE (which relocates segments) or export/import. Row movement must be enabled first.

SHOW ENGINE INNODB STATUS returns the LATEST DETECTED DEADLOCK section, listing each transaction's held and waiting InnoDB row and table locks plus the SQL involved. This gives the internal lock detail needed to identify the conflicting access pattern.

A unique index enforces uniqueness but does not necessarily prohibit NULLs. In many database systems, such as SQL Server and PostgreSQL, multiple NULLs are allowed in a unique index because NULL is considered distinct from other NULLs. This allows flexibility while still preventing duplicate non-NULL values. However, behavior varies, so it's important to check the specific DBMS documentation.

This query joins Sales and Products, groups by product_name, and sums the quantity. The HAVING clause filters groups where the sum exceeds 100. It correctly returns only products meeting the sales threshold, with the required columns. This is the proper use of GROUP BY and HAVING.

This control directly enforces the jurisdiction-specific retention rule by scoping deletion to EU records past the 30-day threshold, and the audit log provides evidence of compliance. It leaves US records untouched for the seven-year tax retention period, satisfying both obligations with one deterministic mechanism.

INSERT appends new rows to an existing table, specifying target columns and values. UPDATE modifies existing rows, DELETE removes them, and CREATE TABLE defines structure, so INSERT is the only command that adds records as the question requires.

A data dictionary stores metadata — table names, column definitions, data types, constraints and relationships — describing the database structure. This metadata repository lets DBAs and applications understand schema layout, supporting consistent design, documentation and change management across the DBMS.

Load testing subjects the deployed database to concurrent user volumes and transaction rates, revealing throughput, latency and resource bottlenecks. It directly verifies whether the system meets its stated performance targets, which is the Testing phase objective named in the stem.

The `shared_buffers` parameter in `postgresql.conf` explicitly sets the memory PostgreSQL dedicates to caching data pages, directly satisfying the stem's requirement for the shared buffer allocation. Other parameters govern different memory areas, such as per-connection work memory or the OS page cache, so none controls this specific allocation.

GROUP BY region collapses rows into one per region, and AVG(order_total) computes the average order value for each group. This returns exactly one row per region with the average, which is the required output. It is the standard and most efficient way to produce per-group aggregates in SQL.

Window functions compute aggregates across a set of rows while retaining each individual row, so `SUM(sales) OVER (ORDER BY date)` returns a running total without collapsing detail. This directly satisfies the developer's requirement for cumulative sales, unlike GROUP BY, which reduces rows and cannot produce per-row running values.

With FULL recovery, transaction log backups every 15 minutes limit data loss to at most 15 minutes, satisfying the RPO. Weekly full backups plus daily differentials keep restore time manageable, and the log chain provides point-in-time recovery. The RTO of 1 hour is realistic because restoring one full, one differential, and a handful of log backups is far faster than replaying a week of logs from a single full backup.

Normalizing casing ensures consistency for joins.

Combining daily full backups with transaction log backups every 5 minutes allows point-in-time recovery to within 5 minutes of a failure. This strategy meets the RPO by minimizing data loss, as the log backups capture all transactions since the last backup. It is a standard approach for databases requiring low RPO.

Separate schemas give each tenant its own tables and namespace within a shared database instance, so queries and permissions are scoped per tenant. This provides stronger logical isolation than shared tables with tenant-ID columns, satisfying the requirement for effective data separation in a multi-tenant architecture.

Updating statistics is a standard maintenance task that refreshes the query optimiser's cardinality estimates, satisfying the stem's requirement. It runs alongside index rebuilds and backups, preventing poor execution plans caused by stale distribution data after significant data modification.

The full recovery model preserves the transaction log and supports point-in-time recovery, which is essential for financial data. RAID 10 provides both mirroring and striping without parity overhead, giving low write latency and redundancy. Separating data and log files on distinct RAID 10 arrays isolates I/O and improves performance. This combination satisfies durability and low write latency requirements.

A ROPA documents each processing activity with its purpose, legal basis, categories of data subjects and personal data, recipients, retention periods, and security measures. It is precisely the artifact an auditor examines to verify that lawful basis and retention are defined per purpose, making it the correct evidence for the bank's compliance demonstration.

Oracle Database Replay captures a production workload and replays it against a test system, reproducing realistic concurrency and transaction mix. This satisfies the pre-deployment requirement to simulate workload and forecast hardware consumption, unlike capacity-planning spreadsheets or static benchmarks that cannot replicate actual Oracle session behaviour.

Mirroring requires the log chain.

Transaction log backups capture every committed transaction since the last log backup, so combining them with periodic full backups lets you replay logs to a precise point in time. Full backups alone restore only to the last backup, and differential backups restore only to the differential's creation, so neither meets the point-in-time requirement.

Multiple tempdb data files of equal size let SQL Server spread allocation and I/O across several files, each served by separate threads. This reduces allocation latch and page contention, preventing tempdb becoming a bottleneck under high concurrency.