DS0-001 Practice Test 14 — 15 Questions

Question 1

An administrator needs to enforce password complexity on the database level. Where should this policy be configured?

Accepted Answer

Database Authentication Policy/IAM. Password complexity requirements are authentication controls, so they belong in the database's authentication policy or identity and access management (IAM) layer. This is where the DBMS enforces rules such as minimum length, character classes, and expiration at login time. Configuring it there ensures the policy applies uniformly to all database users before any session is established.

Answer

Database Trigger

Answer

Backup Schedule

Answer

Stored Procedure

Question 2

Which THREE elements are essential when configuring Database Activity Monitoring (DAM)?

Accepted Answer

Identifying sensitive data objects. Database Activity Monitoring (DAM) requires knowing what to protect, so option B (identifying sensitive data objects) is essential because audit policies and monitoring scope must target the specific tables, columns, and schemas that contain regulated or high-value data. Option D (configuring alert thresholds) is correct because DAM generates alerts on suspicious activity, and thresholds define the conditions, severity, and volume of events that trigger notifications, preventing alert fatigue while ensuring real threats surface. Option E (defining audit rules for privileged users) is correct because privileged accounts such as DBAs and sysadmins often bypass application controls, so explicit audit rules capturing their SELECT, DML, DDL, and privilege changes are a core DAM requirement. Option A (upgrading the database server CPU) is not a DAM configuration element; it is a hardware capacity change unrelated to monitoring policy. Option C (installing extra network routers) is also irrelevant, as DAM relies on database-native auditing, network sniffing, or agent-based collection rather than additional routing infrastructure.

Answer

Upgrading the database server CPU

Answer

Installing extra network routers

Question 3

Which of the following is a common symptom of a broken access control mechanism in a database?

Accepted Answer

Insecure Direct Object Reference (IDOR). Insecure Direct Object Reference (IDOR) is a classic symptom of broken access control, where an application exposes internal object identifiers (like user IDs or file names) and fails to verify that the requesting user is authorized to access them. It directly reflects a failure in the access control mechanism.

Answer

High CPU usage

Answer

Database fragmentation

Answer

Slow network speed

Question 4

Which THREE of the following are essential components of a robust disaster recovery plan for a mission-critical database?

Accepted Answer

Off-site or cloud storage of backups. Option C is correct because storing backups off-site or in the cloud protects against site-wide disasters such as fire, flood, or ransomware that could destroy locally held copies, ensuring a survivable copy exists in a separate failure domain. Option D is correct because backups are only trustworthy if restoration has been verified; regularly testing restores validates backup integrity, media readability, and RTO/RPO assumptions before an actual outage. Option E is correct because documented and tested failover procedures ensure that database replicas, clustering, or standby systems can be promoted quickly and consistently, minimizing downtime and human error during a real incident. Option A is not appropriate because RAID 0 offers striping with no redundancy or parity, so a single disk failure destroys the array and it is never a disaster recovery safeguard. Option B is not appropriate because keeping all data in memory is volatile, non-persistent, and lost on power failure or crash, providing no durable recovery capability.

Answer

Using only RAID 0 for storage

Answer

Keeping all data in memory

Question 5

A production database server is running out of memory. Which setting in SQL Server limits the amount of memory the instance can consume from the OS?

Accepted Answer

max server memory. The 'max server memory' setting in SQL Server configures the upper limit of memory that the SQL Server instance can allocate from the operating system. Setting this value prevents SQL Server from consuming all available memory and starving the OS or other applications, which is critical on a production server.

Answer

Query memory grant

Answer

Working set size

Answer

min server memory

Question 6

What is the primary purpose of a 'Normalization' process in database design?

Accepted Answer

Reduce redundancy. Normalization is the process of organizing database tables to minimize redundancy and dependency by dividing large tables into smaller, related ones and defining relationships. Its primary purpose is to reduce data duplication and prevent update, insert, and delete anomalies.

Answer

Convert SQL to NoSQL

Answer

Increase query speed

Answer

Enable horizontal scaling

Question 7

A company needs to audit all administrative actions taken on their database server. Which feature should the DBA enable?

Accepted Answer

Database Audit Logging. Database Audit Logging captures administrative and user actions — such as logins, privilege changes, schema modifications, and DDL/DML operations — providing the audit trail required to review who did what and when. It is the specific feature designed for compliance, forensic, and security auditing on a database server.

Answer

Data Partitioning

Answer

Query Optimization

Answer

Transaction Log Backup

Question 8

To comply with CCPA, an organization must track the 'Right to Know' requests. Which component of a Data Governance Framework is most relevant?

Accepted Answer

Data Cataloging. Data Cataloging is the component of a Data Governance Framework that inventories data assets, records their location, lineage, and classification, and enables organizations to locate and track personal information subject to CCPA 'Right to Know' requests. Without a catalog, an organization cannot reliably determine what personal data it holds or where it resides.

Answer

Data Ingestion Pipeline

Answer

Load Balancing

Answer

Data Compression

Question 9

As a Data Privacy Officer, you are reviewing technical controls for data security. Which TWO of the following are best practices to ensure 'Privacy by Design' in a new application? (Select TWO)

Accepted Answer

Ensuring that privacy settings are set to the most restrictive level by default. Option C is correct because Privacy by Design requires that the default state of any system protect the user, so privacy settings must default to the most restrictive level and require deliberate opt-in to share more data. Option D is correct because pseudonymization replaces direct identifiers with artificial keys, allowing realistic testing and analytics while reducing re-identification risk, which aligns with GDPR Article 25 data protection by design and by default. Option A is wrong because indexing improves query performance but does nothing to protect privacy, and trading encryption for speed directly undermines data security. Option B is wrong because maximizing metadata collection by default violates data minimization and purpose limitation principles. Option E is wrong because granting all developers root access to production databases breaks least-privilege and separation-of-duties controls, increasing breach and insider-threat risk.

Answer

Prioritizing high-performance database indexing over data encryption

Answer

Setting default configuration to capture maximum user metadata

Answer

Requiring all developers to have root access to the production database

Question 10

What is the primary purpose of a Data Protection Impact Assessment (DPIA) under GDPR?

Accepted Answer

Identify and mitigate data processing risks. A DPIA under GDPR is a structured assessment required when processing is likely to result in a high risk to the rights and freedoms of natural persons. Its primary purpose is to identify those risks and determine the measures needed to mitigate them before processing begins. It is a proactive risk-management and accountability tool, not an operational IT task.

Answer

Backup the production database

Answer

Configure firewall rules

Answer

Accelerate database performance

Question 11

When normalizing a database, what is the primary goal of moving data into a third normal form (3NF)?

Accepted Answer

Remove transitive dependencies. Third normal form (3NF) builds on 2NF by eliminating transitive dependencies — non-key attributes that depend on other non-key attributes rather than directly on the primary key. The rule is that every non-key column must depend on 'the key, the whole key, and nothing but the key.' Removing transitive dependencies prevents update anomalies where changing one fact forces changes in unrelated rows, ensuring each fact is stored in exactly one place.

Answer

Enable multi-dimensional analysis

Answer

Increase data redundancy

Answer

Increase read speed

Answer

Combine multiple tables

Question 12

A company is conducting a data governance audit. Which TWO of the following items should be verified to ensure proper data security?

Accepted Answer

User access rights are reviewed periodically. Option D is correct because periodic review of user access rights enforces the principle of least privilege and ensures that permissions are revoked when roles change or employees leave, which is a core access-control requirement in any data governance audit. Option E is correct because encryption-at-rest on all databases protects stored data from unauthorized disclosure if physical media or backups are compromised, directly satisfying data security and confidentiality controls. The remaining options do not belong: A concerns appliance energy efficiency, B concerns a secondary display for the database server, and C concerns power protection for a printer—none of these relate to protecting data confidentiality, integrity, or access control.

Answer

The breakroom refrigerator is energy efficient

Answer

The database server has a secondary monitor

Answer

The office printer is connected to a UPS

Question 13

Which TWO settings should be configured in a database deployment to improve performance for heavy read-intensive reporting workloads?

Accepted Answer

Columnstore indexes. B is correct because columnstore indexes store data in a column-oriented, compressed format with batch-mode execution, which dramatically speeds up the large scans and aggregations typical of read-intensive reporting workloads. E is correct because read-only secondary replicas offload reporting queries from the primary, letting the primary handle transactional traffic while secondaries serve reads, improving overall read throughput and scalability. A is wrong because adding transaction logs does not improve read performance and mainly affects write durability/recovery. C is wrong because dropping primary key constraints harms data integrity and removes useful indexes rather than helping reads. D is wrong because asynchronous commit mode is a write-side durability trade-off and does not accelerate read-intensive reporting.

Answer

Increasing the number of transaction logs

Answer

Dropping primary key constraints

Answer

Asynchronous commit mode

Question 14

You are deploying a MySQL database on Linux and want to ensure that the database files are isolated from system logs for performance reasons. Which MySQL configuration variable should you modify to specify the data directory location?

Accepted Answer

datadir. The datadir variable in MySQL specifies the directory where the database files (such as .frm, .ibd, and ibdata files) are stored. By modifying datadir, you can isolate these files from system logs, improving I/O performance by placing them on separate storage. This is the correct variable for changing the data directory location.

Answer

tmpdir

Answer

log_error

Answer

basedir

Question 15

A database administrator is planning a new SQL Server deployment and needs to ensure that the tempdb system database does not become a bottleneck during high-concurrency operations. Which configuration step should be performed during the initial setup?

Accepted Answer

Create multiple tempdb data files of equal size to distribute I/O contention across multiple threads.. Creating multiple tempdb data files of equal size is the standard SQL Server best practice for high-concurrency workloads. SQL Server uses proportional fill across data files in a filegroup, and multiple tempdb files allow allocation pages (PFS, GAM, SGAM) and I/O to be spread across files, reducing latch contention on allocation structures and distributing I/O across CPUs and storage queues. Equal sizing is essential so proportional fill distributes writes evenly rather than favouring the largest file.

Answer

Move the tempdb database to a network attached storage (NAS) share for high availability.

Answer

Set the recovery model of tempdb to Full to ensure point-in-time recovery.

Answer

Configure the tempdb database to use a single large file on a compressed volume.