DS0-001 Database Planning And Deployment Practice Test 1 — 15 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?

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

Accepted Answer

Second Normal Form (2NF). Moving repeating groups or redundant data into separate tables to eliminate partial dependencies is the goal of Second Normal Form (2NF).

Answer

First Normal Form (1NF)

Answer

Boyce-Codd Normal Form (BCNF)

Answer

Third Normal Form (3NF)

Question 2

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. Surrogate keys are immutable, integer-based identifiers that remain stable even if the underlying natural business data (like an order number) changes.

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 3

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 allows the database to group data by specific values such as dates, making it very simple to drop an entire partition when data reaches its archival age.

Answer

Composite partitioning

Answer

List partitioning

Answer

Hash partitioning

Answer

Round-robin partitioning

Question 4

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 Real Application Testing (RAT) and its sub-feature Database Replay are specifically designed to capture production workloads and replay them in a test environment to forecast hardware needs.

Answer

Automatic Workload Repository (AWR)

Answer

Oracle Enterprise Manager Performance Hub

Answer

SQL Tuning Advisor

Question 5

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) provides encryption for the entire database file at the storage level, which is a standard deployment requirement for cloud databases.

Answer

Applying column-level encryption

Answer

Using a Virtual Private Cloud (VPC) endpoint

Answer

Enabling SSL/TLS on the connection string

Question 6

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. SQL Server allows specifying separate file paths for the primary data file (.mdf) and the transaction log (.ldf) during the initial database creation process.

Answer

Implementing a striped RAID 0 volume

Answer

Enabling FileStream on the target volume

Answer

Increasing the recovery model to Full

Question 7

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. Synchronous-commit Availability Groups ensure that transactions are committed on the secondary replica before the primary acknowledges the transaction, preventing data loss.

Answer

Database Mirroring in High Performance mode

Answer

Log Shipping

Answer

Asynchronous-commit Availability Group

Question 8

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 or deletion is the standard final step in the data lifecycle after data has been archived and passed its retention period.

Answer

Encryption

Answer

Migration

Answer

Archiving

Question 9

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. Updating statistics (e.g., ANALYZE TABLE or UPDATE STATISTICS) provides the query optimizer with the necessary information to choose the most efficient execution plan.

Answer

Run a database consistency check (DBCC)

Answer

Clear the plan cache

Answer

Rebuild the table indexes

Question 10

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. RLS in SQL Server requires a security predicate function (a table-valued function) that returns a filter condition applied by a security policy.

Answer

A database trigger

Answer

A stored procedure

Answer

A database view

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 compatibility assessment tool evaluates features, syntax, and dependencies to identify what needs to be changed before moving to a managed service like Azure SQL or AWS RDS.

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 (NoSQL) are specifically designed to handle semi-structured data without the rigid schema requirements of relational databases.

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 a mirror of production designed to test deployment scripts, configurations, and data migration processes before the final production rollout.

Answer

To develop new features in isolation

Answer

To handle production workload during maintenance

Answer

To perform unit testing on code

Question 14

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. Implementing separate schemas for each tenant provides a logical separation that allows for efficient management while keeping data isolated within the same instance.

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 15

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' rule means 3 copies of data, 2 different media types, and 1 copy stored off-site to protect against local disasters.

Answer

One recovery point objective (RPO) of zero

Answer

One local copy

Answer

One full backup per day