DS0-001 Database Planning And Deployment — Timed 40 Questions

Question 1

A database administrator is planning to normalize a flat-file database containing customer orders. The administrator identifies that customer contact information is repeated for every order record. Which normalization form should the administrator implement to move this contact information to a separate table?

40 minute time limit — choose an answer to begin.

Accepted Answer

Second Normal Form (2NF). Second Normal Form (2NF) requires that a table be in 1NF and that all non-key attributes be fully functionally dependent on the entire primary key, eliminating partial dependencies. In a flat-file order database where customer contact information repeats for every order, the customer details depend only on the customer identifier, not on the full composite key of order plus customer. Moving contact information to a separate customer table removes this partial dependency, which is exactly what 2NF addresses.

Answer

First Normal Form (1NF)

Answer

Boyce-Codd Normal Form (BCNF)

Answer

Third Normal Form (3NF)

Question 2

An organization is deploying a new SQL Server database. The storage administrator needs to ensure that the transaction logs are stored on a separate physical volume from the data files to improve performance and recoverability. Which configuration step accomplishes this?

Accepted Answer

Modifying the default file locations in SQL Server Management Studio (SSMS) Server Properties. Modifying the default file locations in SQL Server Management Studio (SSMS) Server Properties allows the DBA to specify separate default directories for data files and log files, so that new databases created on the instance automatically place their transaction logs on a different physical volume from the data files. This is the standard configuration step to achieve log/data separation for performance and recoverability.

Answer

Implementing a striped RAID 0 volume

Answer

Enabling FileStream on the target volume

Answer

Increasing the recovery model to Full

Question 3

A database administrator needs to implement Row-Level Security (RLS) to ensure that users can only view records belonging to their specific department. Which database component must be created to enforce this logic?

Accepted Answer

A security predicate function. A security predicate function is the dedicated database object that encapsulates the RLS filter logic (the WHERE clause predicate) and is bound to a table via a security policy. In SQL Server, CREATE SECURITY POLICY references an inline table-valued function that returns 1 for rows the user may see; in PostgreSQL, a row-level security policy uses a USING expression. Without this predicate function, the engine has no rule to evaluate per-row access, so RLS cannot be enforced.

Answer

A database trigger

Answer

A stored procedure

Answer

A database view

Question 4

You are reviewing a deployment plan for a multi-tenant database. To ensure security, you want to ensure that each tenant's data is logically isolated. Which strategy is most effective for this?

Accepted Answer

Using separate schemas for each tenant. Separate schemas per tenant provide logical isolation by giving each tenant its own namespace of tables, views, and permissions within the same database instance. This prevents cross-tenant data access at the schema level, simplifies per-tenant backup/restore, and allows tenant-specific schema customizations without affecting others. It is the most effective balance of isolation and operational efficiency for multi-tenant databases.

Answer

Using a shared table with a TenantID column

Answer

Creating separate database instances for every tenant

Answer

Implementing row-level security on all tables

Question 5

A database is experiencing high lock contention on a specific table during batch update processes. What is the most effective way to resolve this issue without changing the application code?

Accepted Answer

Enabling snapshot isolation. Snapshot isolation uses row versioning in tempdb so that readers do not block writers and writers do not block readers, eliminating the shared-lock contention that causes blocking during batch updates. Enabling it (via ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON and, for the read-committed variant, READ_COMMITTED_SNAPSHOT ON) resolves contention without touching application code. This is the standard DBA remedy when the application cannot be modified to use NOLOCK hints or shorter transactions.

Answer

Switching to an asynchronous commit model

Answer

Increasing the memory allocation for the buffer pool

Answer

Dropping all non-clustered indexes

Question 6

Which TWO of the following are essential components of a robust database backup plan?

Accepted Answer

Periodic full and incremental backups. Option C is correct because a robust backup plan must combine periodic full backups with incremental backups to capture changes between fulls, minimizing data loss (RPO) while controlling storage and time costs. Option E is correct because storing backup media off-site protects the backups from the same disaster (fire, flood, theft, ransomware) that could destroy the primary data center, ensuring recoverability. Options A, B, and D, while valuable operational practices, are not essential components of the backup plan itself: A is a validation activity that should accompany backups but is not a backup component per se, B concerns schema management rather than data protection, and D is general system hardening/maintenance unrelated to backup planning.

Answer

Testing of backup restoration procedures

Answer

Automatic database schema updates

Answer

Installation of the latest OS patches

Question 7

Which TWO database deployment configurations help mitigate the impact of a hardware failure on the primary storage controller?

Accepted Answer

Multi-path I/O (MPIO) configuration. Multi-path I/O (MPIO) configuration (B) is correct because it provides redundant physical paths between the server and the storage array, so if the primary storage controller or one path fails, I/O can fail over to an alternate path/controller and keep the database available. RAID 10 configuration for data volumes (C) is correct because mirroring (RAID 1+0) tolerates the loss of a disk or a controller-side disk group by serving data from the surviving mirror, reducing the impact of a hardware failure affecting primary storage. Full recovery model (A) only affects logging and point-in-time restore capability, not storage-controller fault tolerance. SQL Server Always On (D) provides database-level high availability across replicas but does not by itself mitigate a primary storage controller failure on the same shared storage. Database partitioning (E) is a scalability/manageability feature and does not provide hardware fault tolerance.

Answer

Full recovery model enabled

Answer

SQL Server Always On

Answer

Database partitioning

Question 8

A database architect is setting up an Always On Availability Group in SQL Server. To ensure that the secondary replica can be used for read-only workloads without impacting the primary node's performance, how should the 'Readable Secondary' property be configured?

Accepted Answer

Set to 'Read-intent only' to allow access based on application connection strings.. 'Read-intent only' allows read-only connections to the secondary replica only when the client explicitly specifies ApplicationIntent=ReadOnly in the connection string. This prevents normal read-write connections from accidentally hitting the secondary, ensuring that reporting workloads are deliberately routed there without affecting the primary's performance. It is the recommended setting when the secondary is dedicated to read-only workloads.

Answer

Set the synchronous commit mode to 'Asynchronous' on all nodes.

Answer

Set to 'Yes' and force all writes through the secondary replica.

Answer

Set to 'No' and route traffic via a secondary listener.

Question 9

A database administrator is deploying a new PostgreSQL 15 cluster for an OLTP application that performs a high volume of small, random writes. The storage vendor recommends configuring the filesystem to avoid partial-page writes during power loss. Which filesystem configuration should the administrator verify is enabled before initializing the cluster?

Accepted Answer

Filesystem block size matching the database page size. Aligning the filesystem block size with the PostgreSQL page size ensures each 8 KB page write is atomic, preventing torn pages during sudden power loss. This is a deployment-time filesystem decision that must be made before the cluster is initialized, since changing block size later requires rebuilding the filesystem.

Answer

Write barriers enabled on the filesystem mount

Answer

Data checksumming at the filesystem layer

Answer

Full-page writes enabled in the PostgreSQL cluster

Question 10

A database administrator is deploying a high-traffic OLTP database. The server has 64 GB of RAM, and the database size is 500 GB. The administrator wants to optimize performance by configuring the database to cache frequently accessed data in memory while ensuring that memory is not over-allocated to the database process. Which configuration parameter should the administrator adjust?

Accepted Answer

Set the buffer pool size to 50% of total RAM.. For a high-traffic OLTP database, the buffer pool should be sized to cache frequently accessed data while leaving enough memory for the OS and other processes. Allocating 50% of total RAM is a common best practice that balances performance and stability. Over-allocating can cause swapping, while under-allocating leads to excessive disk I/O.

Answer

Set the buffer pool size to 100% of total RAM.

Answer

Set the buffer pool size to 80% of total RAM.

Answer

Set the buffer pool size to 25% of total RAM.

Question 11

An organization is deploying a SQL Server 2022 Always On availability group across two data centers. The database administrator must ensure that if the primary replica becomes unavailable, a synchronous secondary replica in the remote data center can be promoted without data loss, but the remote secondary must not be allowed to delay commits on the primary. Which configuration should the administrator apply?

Accepted Answer

Configure the remote secondary with asynchronous commit and manual failover. Asynchronous commit on the remote secondary prevents cross-site latency from slowing primary transactions, while manual failover lets the administrator confirm data synchronization before promotion. This combination balances the no-data-loss requirement with the no-delay requirement, which synchronous automatic failover cannot achieve across high-latency links.

Answer

Configure the remote secondary with asynchronous commit and automatic failover

Answer

Configure the remote secondary with synchronous commit and automatic failover

Answer

Configure the remote secondary with synchronous commit and manual failover

Question 12

A database administrator is planning the deployment of a new MongoDB 6.0 replica set to support a customer-facing application. The administrator must ensure that reads can be served from secondary members to distribute load, and that the deployment can survive the loss of a single data center. Which TWO deployment configurations should the administrator implement? (Choose two.)

Accepted Answer

Distribute replica set members across two data centers with a majority in one. Routing reads to secondaries with secondaryPreferred distributes read load away from the primary, and spreading replica set members across two data centers with a majority in one ensures the set retains quorum if a data center is lost. Together these meet the read-scaling and survivability requirements without over-engineering the deployment.

Answer

Enable sharding on the primary collection before deployment

Answer

Configure a majority write concern on all application writes

Answer

Deploy an odd number of voting members across at least three data centers

Question 13

A database administrator is deploying a new database that will be used for both OLTP and reporting workloads. To optimize performance, the administrator plans to implement a caching layer. Which TWO caching strategies are most appropriate for this mixed workload? (Choose two.)

Accepted Answer

Implement a read-through cache for frequently read tables.. For a mixed OLTP and reporting workload, write-through caching ensures data consistency for updates, while read-through caching optimizes read performance for frequently accessed data. Together, they balance the need for immediate consistency in OLTP with fast reads for reporting. Other options like write-behind or eviction policies do not address both requirements effectively.

Answer

Implement a write-behind cache for frequently updated tables.

Answer

Implement a cache eviction policy based on Least Recently Used (LRU).

Answer

Implement a distributed cache across multiple nodes.

Question 14

A database administrator is deploying a MySQL 8.0 InnoDB cluster for a high-traffic web application. The application performs many short transactions and the administrator observes that disk I/O is the bottleneck during peak hours. The storage team confirms the disks are not saturated at the hardware level. Which InnoDB configuration change should the administrator make to reduce disk I/O by allowing transactions to commit without forcing a log flush to disk on every commit?

Accepted Answer

Set innodb_flush_log_at_trx_commit to 2. Setting innodb_flush_log_at_trx_commit to 2 writes each commit to the operating system cache but flushes to disk only once per second, eliminating the per-commit disk flush that drives the observed I/O bottleneck. It preserves durability across a database crash while trading only OS-level or power-loss durability, which matches the administrator's goal.

Answer

Enable innodb_dedicated_server to auto-tune buffer pool and log sizes

Answer

Set innodb_flush_log_at_trx_commit to 0

Answer

Increase innodb_log_file_size to reduce checkpoint frequency

Question 15

A team is deploying a MongoDB 7.0 replica set for a customer-facing application that must remain writable if a single data center becomes unavailable. The primary and two secondaries currently reside in one data center, with two additional secondaries planned for a second data center. Which write concern and read concern combination best balances durability with availability during a data center outage?

Accepted Answer

Write concern w:majority with read concern majority, so acknowledged writes survive failover and reads see only replicated data.. With five members split across two data centers, three in one and two in the other, a majority write concern requires acknowledgment from three members, which can be satisfied within a single surviving site. Read concern majority ensures reads reflect only data that has been replicated to a majority and therefore will not be rolled back after a failover.

Answer

Write concern w:0 with read concern available, so the application never waits for a server response.

Answer

Write concern w:1 with read concern local, so writes acknowledge as soon as the primary applies them.

Answer

Write concern w:5 with read concern linearizable, so every write reaches all members before acknowledging.

Question 16

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?

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 17

A database administrator is planning the deployment of a new Oracle Database 19c instance that will support a global e-commerce application. The application must be available 24/7 and must support rolling upgrades with minimal downtime. The administrator needs to choose a high-availability architecture that allows patching and upgrades with no downtime. Which Oracle feature should the administrator implement?

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 18

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 19

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 20

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