DS0-001 Practice Test 12 — 15 Questions

Question 1

A database administrator is designing a schema for a financial application that must enforce that every account balance is never negative. The application inserts and updates balances frequently, and the DBA wants the rule enforced at the database level regardless of which application or script performs the change. Which database object should the DBA create to enforce this rule?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

A CHECK constraint on the Balance column that verifies Balance >= 0.. A CHECK constraint is the appropriate database object because it declaratively enforces a Boolean condition on column values during every INSERT and UPDATE. It cannot be bypassed by applications or ad-hoc scripts, and the database engine optimizes its evaluation. Stored procedures and triggers can enforce rules procedurally but are more complex and, in the case of stored procedures, easily bypassed. A foreign key addresses referential integrity, not numeric ranges.

Answer

A stored procedure that all applications must call to modify balances.

Answer

A foreign key constraint referencing a Balances lookup table that contains only non-negative values.

Answer

A trigger on the table that rolls back any transaction resulting in a negative balance.

Question 2

A data engineer is designing a table in a relational database to store product inventory records. The business requires that the combination of 'ProductCode' and 'WarehouseID' uniquely identifies each row, and that no duplicate pairs are allowed. Which constraint should be applied to enforce this requirement?

Accepted Answer

PRIMARY KEY on ProductCode and WarehouseID combined. A composite PRIMARY KEY on ProductCode and WarehouseID is the correct choice because it enforces uniqueness of the combination and disallows NULLs, exactly matching the requirement that each pair uniquely identifies an inventory row. Other constraints either enforce referential integrity, validate row-level conditions, or apply uniqueness too narrowly.

Answer

FOREIGN KEY referencing the Product and Warehouse tables

Answer

UNIQUE constraint on ProductCode only

Answer

CHECK constraint comparing ProductCode and WarehouseID

Question 3

A database administrator is deploying a new PostgreSQL 15 cluster on Linux and wants to protect against data loss if a single disk in the data volume fails. The server has four identical 1 TB SSDs available. Which RAID level should the administrator configure for the PostgreSQL data directory?

Accepted Answer

RAID 10. RAID 10 mirrors data across pairs of disks and then stripes across those pairs, delivering redundancy plus the high random I/O throughput a PostgreSQL data directory requires. It tolerates at least one disk failure and often more, depending on which disks fail, while avoiding the parity write penalty of RAID 5 and the total lack of protection in RAID 0.

Answer

RAID 0

Answer

RAID 5

Answer

RAID 1

Question 4

A financial analyst runs a query that joins a 500-million-row transactions table to a 200-row currency table on a non-indexed currency code column. The optimizer chooses a nested loop join and the query runs for hours. Which execution strategy should the analyst encourage to improve performance most effectively?

Accepted Answer

Hash join, building a hash table on the small currency table. When one input is very small and the join column on the large input is unindexed, a hash join avoids repeated scans by building an in-memory hash table from the small table and probing it once per large-table row. Merge joins need sorted inputs, nested loops repeat inner scans, and cross joins explode the result set, so the hash join is the most effective strategy here.

Answer

Cross join followed by a WHERE filter on matching currency codes

Answer

Merge join, requiring both inputs to be sorted on currency code

Answer

Nested loop join with a larger sort area allocated to the session

Question 5

A junior DBA runs the following statement against a production table that contains millions of rows with an active workload:

TRUNCATE TABLE Sales.Transactions;

What is the most important characteristic of this operation that the junior DBA should have understood before executing it?

Accepted Answer

It removes all rows without firing DELETE triggers or logging individual row deletions, and the removal cannot normally be rolled back row by row.. TRUNCATE TABLE removes all rows by deallocating data pages rather than deleting rows individually. This means it is minimally logged, does not fire DELETE triggers, and cannot be rolled back row by row. For a production table with millions of rows and concurrent activity, the DBA must understand that recovery depends on backups or the surrounding transaction, not on per-row undo. The other options describe behaviors that belong to DROP TABLE, foreign key enforcement, or backup automation, none of which apply to TRUNCATE.

Answer

It automatically creates a full backup of the table before removing the rows, allowing point-in-time recovery of the truncated data.

Answer

It releases the table's clustered index and all nonclustered indexes, so any index must be recreated afterward.

Answer

It enforces all FOREIGN KEY constraints referencing the table, so it will always fail if child rows exist.

Question 6

A database administrator is deploying a new database on a cloud platform. The organization requires that the database automatically scale storage capacity as data grows, without manual intervention, and that it maintain high availability across multiple availability zones. Which cloud database deployment option should the administrator choose?

Accepted Answer

A managed relational database service configured with a multi-AZ deployment and storage auto-scaling enabled.. A managed relational database service with multi-AZ deployment and storage auto-scaling automatically increases storage and maintains a standby replica in another availability zone for failover. This satisfies both automatic scaling and high availability. Self-managed, NoSQL with manual sharding, and data warehouse options do not meet these specific requirements.

Answer

A NoSQL database deployed on a cluster of VMs across multiple zones with manual sharding.

Answer

A data warehouse service with columnar storage and automatic scaling of compute nodes.

Answer

A single virtual machine running a self-managed database instance with attached SSD storage.

Question 7

A DBA is designing a backup strategy for a database that must meet a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is in the FULL recovery model. Which combination of backup types and scheduling best satisfies these objectives?

Accepted Answer

Weekly full backups, daily differential backups, and transaction log backups every 15 minutes.. The 15-minute RPO dictates that transaction log backups must be taken at least every 15 minutes so that at most 15 minutes of transactions are lost. Combining weekly full backups, daily differentials, and 15-minute log backups keeps the restore chain short enough to meet the 1-hour RTO while satisfying the RPO. Strategies without log backups or with hourly logs fail the RPO, and mirroring alone is not a backup strategy.

Answer

Weekly full backups and hourly transaction log backups.

Answer

Daily full backups and no transaction log backups.

Answer

Continuous mirroring with no scheduled backups.

Question 8

A database administrator is preparing to deploy a new Oracle Database 19c instance that will store regulated financial data. The compliance team requires that all data at rest be encrypted and that encryption keys be managed outside the database to satisfy separation-of-duties controls. Which deployment approach should the administrator use?

Accepted Answer

Enable Transparent Data Encryption with an external key management system using PKCS#11. Transparent Data Encryption with an external key management system encrypts data at rest while delegating master key custody to a separate appliance through PKCS#11. This satisfies both the encryption requirement and the separation-of-duties control, because the database server never holds the master key locally.

Answer

Enable Transparent Data Encryption with a local wallet stored on the database server

Answer

Enable Data Redaction policies on all columns containing regulated financial data

Answer

Enable database auditing and configure unified audit policies on all sensitive tables

Question 9

A multinational retailer stores customer loyalty data in a PostgreSQL cluster. Privacy counsel requires that EU customer records be purged automatically 30 days after account closure, while US records must be retained for seven years for tax purposes. Which data governance control best satisfies this requirement?

Accepted Answer

A scheduled job that deletes rows where region = 'EU' and closed_date is older than 30 days, with an audit log of each deletion.. Jurisdiction-specific retention requires an automated rule that deletes EU records past the 30-day window while preserving US records for the full tax period. A scheduled deletion scoped by region and closure date, plus an immutable audit trail, enforces the erasure obligation and produces the evidence auditors expect without disturbing the separate retention mandate.

Answer

Partition the table by region and detach the EU partition after 30 days, retaining the detached partition on cheaper storage.

Answer

A row-level security policy that hides EU records from analysts after 30 days while keeping them in the table for audit.

Answer

Encrypt EU records with a per-customer key and destroy the key after 30 days, rendering the ciphertext unreadable.

Question 10

A retail company's order-processing database runs on a single server with RAID 10 storage. The database is 800 GB, with a 20 GB transaction log. The recovery model is FULL, and log backups run every 15 minutes. During a power failure, the server shuts down unexpectedly. After restart, the database is marked as 'In Recovery' and takes 45 minutes to become available. The CIO wants to reduce this recovery time. Which action should the database administrator take first?

Accepted Answer

Enable indirect checkpoints with a low recovery interval.. Indirect checkpoints let you set a target recovery time per database, causing SQL Server to write dirty pages more frequently so that crash recovery completes within the specified interval. This preserves the FULL recovery model and point-in-time restore. Changing recovery models, shrinking the log, or altering RAID levels does not target the dirty-page backlog that determines recovery duration.

Answer

Change the recovery model to SIMPLE.

Answer

Shrink the transaction log file to 1 GB.

Answer

Move the database to a faster RAID 0 array.

Question 11

A healthcare analytics team stores patient records in a PostgreSQL database. A new compliance mandate requires that the database administrator be prevented from reading the contents of the 'diagnosis' column in plaintext, while the application must still be able to query and filter on that column. Which mechanism should the team implement?

Accepted Answer

Column-level encryption using a key managed by an external key management service, with the application performing encryption and decryption.. The requirement is to hide column contents from a privileged database administrator while retaining application query capability. Encrypting that column with keys held outside the database, and letting the application perform cryptographic operations, achieves both goals because the DBA only ever sees ciphertext. Engine-level encryption and access policies still expose plaintext to administrators, and monitoring is detective rather than preventive.

Answer

Transparent Data Encryption (TDE) at the tablespace level using the database engine's built-in key store.

Answer

Row-level security policies that restrict SELECT on the table to application service accounts only.

Answer

Database activity monitoring that alerts when the administrator queries the 'diagnosis' column.

Question 12

A database administrator is deploying a new SQL Server instance that will host a data warehouse. The instance must store data and log files on separate physical volumes to maximize performance and simplify backup operations. The server has four drive letters available: C, D, E, and F. The administrator wants to ensure that tempdb is also isolated. Which configuration should the administrator use?

Accepted Answer

Place data files on C, log files on D, and tempdb on E.. The best practice is to isolate data, log, and tempdb files on separate physical volumes. This reduces I/O contention, improves write performance for the log, and prevents tempdb activity from affecting user databases. The configuration that places data on C, log on D, and tempdb on E satisfies all isolation requirements with the available drives.

Answer

Place all files on a single volume with RAID 5.

Answer

Place data files on C, log files on C, and tempdb on D.

Answer

Place data and log files on D, and tempdb on C.

Question 13

A database administrator is planning a disaster recovery strategy for a critical SQL Server database. The business requires a recovery point objective (RPO) of 15 minutes and a recovery time objective (RTO) of 1 hour. The database is currently using the simple recovery model. Which backup strategy should the administrator implement to meet these objectives?

Accepted Answer

Change the recovery model to full, perform a full backup weekly, differential backups daily, and transaction log backups every 15 minutes.. To achieve a 15-minute RPO, transaction log backups must be taken at least every 15 minutes, which requires the full recovery model. Weekly full and daily differential backups minimize restore time, supporting a 1-hour RTO. The other options either use recovery models that do not support log backups or have insufficient backup frequency, failing to meet the objectives.

Answer

Keep the simple recovery model, perform a full backup daily, and differential backups every 2 hours.

Answer

Keep the simple recovery model, perform a full backup every 4 hours, and use database snapshots every 15 minutes.

Answer

Change the recovery model to bulk-logged, perform a full backup daily, and transaction log backups every 30 minutes.

Question 14

A cloud-based SaaS provider stores customer data in a multi-tenant database. The provider wants to ensure that a customer's data is logically isolated from other customers' data, even though they share the same database schema. Which security control should be implemented to enforce this isolation?

Accepted Answer

Row-level security (RLS) policies that filter data based on the tenant identifier.. Row-level security policies enforce logical isolation by automatically filtering rows based on the tenant identifier. This ensures that each customer can only access their own data within the shared database. RLS is a proven approach for multi-tenant applications because it centralizes access control at the database layer, reducing the risk of application-level bugs that could expose cross-tenant data. It directly satisfies the requirement for logical isolation without altering the shared schema.

Answer

Network segmentation using virtual private clouds (VPCs) for each tenant.

Answer

Column-level encryption with separate keys for each tenant.

Answer

A separate database instance for each tenant.

Question 15

A database administrator is troubleshooting a query that runs slowly only when executed by a specific application login. The same query runs quickly when executed by the administrator's login. The administrator suspects parameter sniffing. Which action should the administrator take to resolve the issue while minimizing the impact on other queries?

Accepted Answer

Use the OPTION (RECOMPILE) query hint on the specific query.. The OPTION (RECOMPILE) query hint forces a new plan for each execution, using the current parameter values, which resolves parameter sniffing for that specific query without affecting other queries. Disabling parameter sniffing instance-wide is too broad, plan guides are complex, and updating statistics does not directly address the cached plan issue.

Answer

Create a plan guide for the query using sp_create_plan_guide.

Answer

Disable parameter sniffing at the instance level using trace flag 4136.

Answer

Update statistics on all tables involved in the query.