DS0-001 Practice Test 10 — 10 Questions

Question 1

A query is slow due to excessive scanning of a large table. The execution plan shows a 'Table Scan'. What is the most likely missing element?

Accepted Answer

An index. A Table Scan in an execution plan means the database engine is reading every row of the table to satisfy the query, which is expensive on large tables. An index provides a sorted access path that lets the optimizer seek directly to matching rows instead of scanning the whole table, dramatically reducing I/O. Adding an appropriate index on the columns used in WHERE, JOIN, or ORDER BY clauses is the standard fix.

Answer

A view

Answer

A stored procedure

Answer

A trigger

Question 2

A database administrator is deploying a new Oracle 19c database and needs to ensure that if the production server's power fails, no committed transactions are lost. The storage array has a write-back cache with a battery-backed unit. Which configuration should the administrator verify?

Accepted Answer

Verify that the storage array's write-back cache is protected by the battery and that the operating system does not disable write caching flush.. Commit durability requires that redo records reach nonvolatile storage before the commit is acknowledged. A battery-backed write-back cache keeps cached writes safe across a power failure, and the operating system must pass flush commands to the array so commits are not stranded in volatile memory. Verifying both conditions directly addresses the requirement.

Answer

Increase the size of the redo log buffer to reduce the frequency of log writer flushes.

Answer

Set the database to ARCHIVELOG mode and enable automatic archival.

Answer

Enable FORCE LOGGING at the tablespace level to guarantee redo is generated for all changes.

Question 3

Which THREE of the following are standard SQL aggregate functions?

Accepted Answer

AVG. AVG (option A) is a standard SQL aggregate function that returns the average value of a numeric column, making it correct. SUM (option B) is also a standard aggregate function that totals the values in a numeric column, so it is correct. COUNT (option D) is a standard aggregate function that returns the number of rows or non-NULL values, so it is correct. SELECT (option C) is a data retrieval statement, not an aggregate function, and DELETE (option E) is a data manipulation statement used to remove rows, so neither belongs among aggregate functions.

Answer

SELECT

Answer

DELETE

Question 4

What does the 'P' in the acronym 'KPI' stand for?

Accepted Answer

Performance. KPI stands for Key Performance Indicator, where the 'P' represents Performance. A KPI is a quantifiable measure used to evaluate the success of an organization, project, or activity against strategic objectives. The 'Performance' element emphasizes that KPIs measure how well something is performing relative to a target.

Answer

Product

Answer

Process

Answer

Planning

Question 5

A database administrator needs to perform a schema-only export of a database. Which tool parameter is typically used for this purpose in mysqldump?

Accepted Answer

--no-data. The --no-data flag in mysqldump suppresses row data and exports only the table structures (CREATE TABLE statements), which is exactly a schema-only export. It is commonly used to replicate schema to a new environment without copying potentially sensitive or bulky data.

Answer

--quick

Answer

--add-drop-table

Answer

--all-databases

Question 6

Which document is essential to create during the database planning phase to define the specific requirements, constraints, and business rules of the proposed system?

Accepted Answer

Database Requirements Specification. The Database Requirements Specification is the planning-phase document that captures functional requirements, constraints, business rules, and assumptions for the proposed database. It is the authoritative input for later design, schema, and deployment decisions. Without it, designers lack a baseline to validate the schema against.

Answer

User Access Policy

Answer

Disaster Recovery Plan

Answer

Database Schema Diagram

Question 7

You are auditing data security controls. You find that raw sensitive data is being written to application logs by the ETL process. Which remediation step best aligns with the principle of 'Data Minimization'?

Accepted Answer

Configure the logging framework to redact sensitive fields at the source. Data minimization requires collecting, processing, and storing only the data necessary for the intended purpose. Redacting sensitive fields at the source before they are written to logs ensures that unnecessary sensitive data is never persisted, directly aligning with data minimization by preventing the data from being captured in the first place.

Answer

Implement a log retention policy of 30 days

Answer

Restrict access to log files to senior engineers only

Answer

Encrypt the log files at rest using AES-256

Question 8

Which protocol should be enforced for all data-in-transit between application servers and database instances?

Accepted Answer

TLS (Transport Layer Security). TLS is the standard cryptographic protocol for encrypting data in transit, providing confidentiality, integrity, and server authentication between application servers and database instances. It is the correct choice for protecting database traffic from eavesdropping and man-in-the-middle attacks.

Answer

HTTP

Answer

SNMP

Answer

FTP

Question 9

An organization must ensure that PII in a legacy SQL Server database is masked before being accessed by the HR analytics team. Which SQL Server feature should the DBA implement to ensure the data is obscured at the query level without changing the underlying storage?

Accepted Answer

Dynamic Data Masking (DDM). Dynamic Data Masking (DDM) in SQL Server is designed to obscure sensitive data at the query level without altering the underlying storage. It applies masking rules to specific columns, so users without UNMASK permission see masked values (e.g., partial credit card numbers or email addresses) while the actual data remains intact in the database. This is ideal for scenarios like HR analytics teams accessing PII, where the data must be usable but not fully visible.

Answer

Transparent Data Encryption (TDE)

Answer

Row-Level Security (RLS)

Answer

Always Encrypted

Question 10

Which TWO of the following are recognized components of a mature Data Governance framework? (Select TWO)

Accepted Answer

Data Quality Standards. Data Quality Standards (A) are a recognized component of a mature Data Governance framework because governance defines the accuracy, completeness, consistency, and validity rules that data must satisfy, along with the metrics and accountability for enforcing them. Data Cataloging (C) is also a core governance component, as a catalog inventories data assets, captures metadata, lineage, and ownership, and enables discoverability and stewardship across the organization. By contrast, Office Security Badge Access (B) is a physical security control, not a data governance artifact. Hardware Procurement Policy (D) concerns IT asset acquisition and does not address data stewardship, quality, or metadata management. Network Throughput Monitoring (E) is an infrastructure performance activity unrelated to governing data definitions, ownership, or lifecycle.

Answer

Office Security Badge Access

Answer

Hardware Procurement Policy

Answer

Network Throughput Monitoring