DS0-001 Database Planning And Deployment Practice Test 2 — 15 Questions

Question 1

A database administrator is planning the deployment of a new PostgreSQL 15 cluster for a reporting application. The application will run complex analytical queries that scan large portions of tables and perform large sequential reads. Which storage configuration should the administrator choose to best support this workload?

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

Accepted Answer

Configure a RAID 0 array of solid-state drives to maximize sequential read throughput.. Analytical reporting queries that scan large table portions benefit most from high sequential read throughput. Striping across multiple solid-state drives in RAID 0 provides the highest sequential read performance among the options. The trade-off is lack of redundancy, but the scenario emphasizes performance for large sequential reads, making RAID 0 the best fit.

Answer

Configure a RAID 1 array of solid-state drives to provide redundancy and improve write performance.

Answer

Configure a single large solid-state drive to avoid RAID overhead and simplify management.

Answer

Configure a RAID 5 array of solid-state drives to balance read performance with parity protection.

Question 2

A database administrator is planning the deployment of a new PostgreSQL 15 cluster for a reporting application. The application will run complex analytical queries that scan large portions of tables and perform large sequential reads. Which storage configuration should the administrator choose to best support this workload?

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

Accepted Answer

Configure Oracle Real Application Clusters (RAC) with multiple instances across multiple nodes.. Oracle Real Application Clusters (RAC) with multiple instances across multiple nodes enables rolling upgrades and patching by allowing one instance to be taken offline while others remain available. This provides continuous availability for a 24/7 global application. Other options either require downtime or are more complex and less seamless for this requirement.

Answer

Configure Oracle Data Guard in maximum availability mode with a physical standby database.

Answer

Configure Oracle Real Application Clusters (RAC) with a single instance.

Answer

Configure Oracle Active Data Guard with a logical standby database for rolling upgrades.

Question 3

A database administrator is deploying a new MySQL 8.0 instance that will store sensitive customer data. The compliance team requires that data be encrypted at rest and in transit, and that encryption keys be managed externally. Which TWO configurations should the administrator implement to meet these requirements? (Choose two.)

Accepted Answer

Enable InnoDB tablespace encryption using a keyring plugin that supports external key management.. To meet the compliance requirements, the administrator must encrypt data at rest and in transit. InnoDB tablespace encryption with an external keyring plugin addresses data at rest with external key management. Configuring SSL/TLS for client connections addresses data in transit. Other options do not fulfill both requirements.

Answer

Use the mysql_native_password authentication plugin to ensure secure authentication.

Answer

Enable transparent data encryption (TDE) using the built-in MySQL Enterprise Encryption functions.

Answer

Enable binary log encryption using the keyring plugin.

Question 4

A data architect is designing a schema for a high-concurrency e-commerce system. They decide to use a surrogate key instead of a natural key for the Orders table to ensure join performance and data integrity. What is the primary advantage of this design choice?

Accepted Answer

It reduces the storage size of the index compared to a string-based natural key. A surrogate key (e.g., an auto-incrementing integer or GUID) is typically much smaller than a natural key such as a string-based order number or composite business key. Because the surrogate key is used in indexes and foreign key relationships, the smaller key size reduces index page size, allowing more index entries per page, which improves join performance and reduces I/O. This is the primary advantage in a high-concurrency e-commerce system where Orders is joined frequently.

Answer

It prevents the insertion of duplicate records

Answer

It ensures that the database complies with ACID requirements

Answer

It automatically creates a clustered index on the table

Question 5

A database developer is implementing a partitioning strategy for a large table containing historical financial records. The requirement is to allow for efficient archival of data older than five years. Which partitioning strategy should be chosen?

Accepted Answer

Range partitioning. Range partitioning divides data by contiguous value ranges of a column — for historical financial records, partitioning by date range (e.g., yearly or monthly) places each period's rows in its own partition. Archiving data older than five years then becomes a simple partition switch or drop, which is a metadata operation rather than a row-by-row DELETE. This makes range partitioning the natural fit for time-based archival.

Answer

Composite partitioning

Answer

List partitioning

Answer

Hash partitioning

Answer

Round-robin partitioning

Question 6

During the pre-deployment phase, a team needs to estimate the hardware resources required for a new Oracle Database installation. Which tool should be used to simulate database workload and forecast resource consumption?

Accepted Answer

Oracle Database Replay. Oracle Database Replay captures a production workload and replays it on a test system, allowing DBAs to measure resource consumption and forecast hardware requirements before deployment. It is specifically designed for workload simulation and capacity planning. This makes it the correct tool for pre-deployment resource estimation.

Answer

Automatic Workload Repository (AWR)

Answer

Oracle Enterprise Manager Performance Hub

Answer

SQL Tuning Advisor

Question 7

You are deploying a database to a cloud environment and need to ensure that data at rest is encrypted according to organizational security policy. Which deployment configuration ensures transparent encryption of the database files?

Accepted Answer

Implementing Transparent Data Encryption (TDE). Transparent Data Encryption (TDE) encrypts the database files at rest — the data and log files on disk — without requiring changes to the application. It is transparent to the application because encryption and decryption happen at the storage engine level, and it satisfies organizational policies requiring encryption of data at rest. TDE is the standard mechanism for encrypting database files in SQL Server, Oracle, and Azure SQL.

Answer

Applying column-level encryption

Answer

Using a Virtual Private Cloud (VPC) endpoint

Answer

Enabling SSL/TLS on the connection string

Question 8

Before finalizing a database deployment, the team must ensure high availability. Which configuration allows for automatic failover to a standby server with zero data loss in a SQL Server environment?

Accepted Answer

Synchronous-commit Availability Group. A Synchronous-commit Availability Group in SQL Server ensures zero data loss by committing transactions on the primary replica only after they are hardened on at least one synchronous secondary replica. This provides automatic failover with no data loss, meeting the high availability requirement. It is the correct choice for zero data loss with automatic failover.

Answer

Database Mirroring in High Performance mode

Answer

Log Shipping

Answer

Asynchronous-commit Availability Group

Question 9

A database administrator is defining the data lifecycle policy for a new deployment. What is the final step in the data lifecycle for records that have exceeded their retention period and are no longer required for regulatory compliance?

Accepted Answer

Purging. Purging is the final step in the data lifecycle, where records that have exceeded retention and are no longer needed for compliance are permanently deleted from all storage systems. It ensures data is irrecoverably removed, satisfying regulatory and privacy requirements. Archiving, migration, and encryption occur earlier in the lifecycle.

Answer

Encryption

Answer

Migration

Answer

Archiving

Question 10

During database testing, a developer finds that a specific query is performing poorly due to a lack of statistics. What action should be taken to update the database optimizer's knowledge of the table data distribution?

Accepted Answer

Update statistics. When a query performs poorly due to outdated or missing statistics, the optimizer lacks accurate information about data distribution and may choose a suboptimal execution plan. Running UPDATE STATISTICS refreshes the optimizer's knowledge of column value distributions, enabling better cardinality estimates and plan selection.

Answer

Run a database consistency check (DBCC)

Answer

Clear the plan cache

Answer

Rebuild the table indexes

Question 11

A company is planning to migrate a legacy on-premises database to the cloud. Which assessment should be conducted to ensure the application compatibility with the target managed database service?

Accepted Answer

Database compatibility assessment. A database compatibility assessment evaluates whether the legacy database's schema, data types, stored procedures, triggers, and application dependencies will work on the target managed service. Managed cloud databases often lack certain features (e.g., SQL Server Agent, cross-database queries, CLR, or specific system stored procedures), so this assessment identifies incompatibilities before migration and informs remediation planning.

Answer

Network throughput analysis

Answer

Capacity planning report

Answer

Data integrity audit

Question 12

A database architect is tasked with choosing a database model for a system that requires highly variable, semi-structured data storage (e.g., JSON documents). Which model provides the best flexibility for this requirement?

Accepted Answer

Document-oriented. Document-oriented databases store data in flexible, semi-structured formats like JSON or XML, allowing for schema-less design and easy evolution of data structures. This makes them ideal for highly variable data such as JSON documents.

Answer

Key-Value

Answer

Relational (RDBMS)

Answer

Graph

Question 13

In the context of database deployment, what is the primary purpose of a staging environment?

Accepted Answer

To test the deployment and migration process. A staging environment is designed to mirror the production environment as closely as possible, allowing teams to test the deployment and migration process before it goes live. This includes validating scripts, configurations, and data migrations to catch issues that might not appear in development. The primary purpose is to ensure a smooth transition to production by rehearsing the actual deployment steps.

Answer

To develop new features in isolation

Answer

To handle production workload during maintenance

Answer

To perform unit testing on code

Question 14

An administrator is deploying a new database and needs to ensure that the backup strategy complies with the '3-2-1' backup rule. What does the '1' in this rule represent?

Accepted Answer

One off-site copy. The 3-2-1 backup rule states: keep at least 3 copies of data, on 2 different media types, with 1 copy stored off-site. The '1' specifically refers to the off-site copy, which protects against site-wide disasters such as fire, flood, or ransomware that could destroy all local copies simultaneously.

Answer

One recovery point objective (RPO) of zero

Answer

One local copy

Answer

One full backup per day

Question 15

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