[WGU D344 OBJECTIVE ASSESSMENT AND PRE
ASSESSMENT] EXAM with Questions and
Answers/Plus a Rationale Updated 2026 A+/Instant
Download PDF
Table of Contents
1. Principles of Data Management and Governance
2. Database Design and Normalization
3. SQL Querying and Optimization
4. Data Security, Privacy, and Compliance
5. Data Warehousing, ETL, and Integration
1. A data architect is tasked with integrating a legacy relational database into a modern data
warehouse environment. The legacy system frequently suffers from update anomalies due
to redundant data storage. Which normalization process should the architect prioritize to
eliminate these anomalies while maintaining referential integrity?
A. Denormalizing the schema to improve read performance for analytical queries.
B. Implementing Third Normal Form (3NF) to ensure that all non-key attributes are
dependent only on the primary key.
C. Migrating the data to a NoSQL document store to bypass schema constraints.
D. Applying First Normal Form (1NF) by removing all repeating groups and creating a flat file
structure.
CORRECT ANSWER : B
Rationale: 3NF is specifically designed to eliminate transitive dependencies and redundant data,
which are the primary causes of update anomalies. Denormalizing (A) would worsen the
,anomalies; migrating to NoSQL (C) does not address the relational structure issues; and 1NF
(D) is insufficient to resolve dependencies that lead to anomalies.
2. You are optimizing a complex SQL query that joins three large tables, each containing
millions of rows. The execution plan indicates a full table scan on the largest table,
resulting in high latency. What is the most effective strategy to improve performance?
A. Creating a composite index on the columns used in the JOIN and WHERE clauses.
B. Increasing the buffer pool size in the database configuration.
C. Replacing the JOINs with multiple subqueries to simplify the execution path.
D. Changing the database isolation level to READ UNCOMMITTED.
CORRECT ANSWER : A
Rationale: A composite index allows the query optimizer to perform an index seek instead of a
full table scan by narrowing the data set before the join. Increasing buffer pool size (B) is a
temporary fix for memory pressure, not a query optimization; subqueries (C) are generally less
efficient than joins; and changing the isolation level (D) risks data inconsistency.
3. A healthcare organization is implementing a new patient records system. The database
must comply with strict data privacy regulations that mandate auditing all access to
sensitive PII. Which security mechanism is most appropriate?
A. Implementing Column-Level Encryption on all fields containing patient names.
B. Enabling Database Auditing and implementing Row-Level Security (RLS) to restrict
access based on user role.
C. Moving all patient data to an encrypted cloud storage bucket.
D. Using stored procedures to encapsulate all data access, preventing direct table queries.
CORRECT ANSWER : B
Rationale: Auditing satisfies the regulatory requirement for tracking access, while RLS ensures
that users can only interact with data pertinent to their role. Encryption (A) protects data at rest
but does not manage access; cloud storage (C) does not provide granular access control; and
stored procedures (D) are a security layer but do not replace the need for formal auditing.
4. A data analyst observes that a specific report running against the production transactional
database is significantly slowing down user transactions. The report requires complex
aggregations over historical data. What is the optimal architectural solution?
,A. Implement a READ ONLY replica of the database and point the report to the replica.
B. Extract, Transform, and Load (ETL) the data into a dedicated data warehouse
optimized for analytical processing.
C. Increase the CPU resources of the current database server to handle the reporting load.
D. Convert the reporting queries into materialized views to be refreshed every 24 hours.
CORRECT ANSWER : B
Rationale: Analytical reporting should be separated from transactional processing (OLTP) to
prevent performance degradation; a data warehouse (OLAP) is built for this purpose. Using a
replica (A) or increasing resources (C) does not fundamentally solve the architecture mismatch
between OLTP and OLAP needs; materialized views (D) may still impact performance during
the refresh cycle.
5. When designing a database schema, you encounter a situation where an entity has a
many-to-many relationship with itself (e.g., employees who are managers of other
employees). How should this be modeled?
A. Add a 'ManagerID' column to the Employee table that references the 'EmployeeID' column in
the same table.
B. Create a separate 'Management' table with 'ManagerID' and 'SubordinateID' columns as a
recursive relationship.
C. Create a linking table (associative entity) with foreign keys referencing the
'EmployeeID' primary key of the Employee table.
D. Duplicate the Employee table data into a separate 'Managers' table to avoid circular
references.
CORRECT ANSWER : C
Rationale: A many-to-many relationship requires an associative entity (linking table) to properly
resolve the relationship while maintaining normalization. Adding a 'ManagerID' (A) only
supports one-to-many relationships; a separate management table (B) is redundant if a linking
table is used; duplicating data (D) violates normalization principles and risks data
inconsistency.
6. An organization is migrating to a cloud database service. They need to ensure that the
database remains available even if an entire data center fails. Which feature is required?
A. Multi-Instance read scaling.
, B. Multi-AZ (Availability Zone) deployment with synchronous replication.
C. Automated daily snapshots stored in cross-region storage.
D. Vertical scaling to increase server processing power.
CORRECT ANSWER : B
Rationale: Multi-AZ deployment with synchronous replication ensures that a standby instance is
ready to take over immediately if the primary instance fails, providing high availability. Read
scaling (A) addresses capacity, not availability; snapshots (C) are for recovery, not high
availability; and vertical scaling (D) addresses performance, not downtime.
7. A developer is designing a system where users can have multiple phone numbers, and
phone numbers can belong to multiple users (e.g., shared company devices). What is the
primary risk of not properly normalizing this schema?
A. Increased storage overhead for the linking table.
B. Anomalies during deletions, where removing a phone number might inadvertently
remove valid user associations.
C. Slower query performance due to the complexity of joins.
D. Incompatibility with standard SQL syntax for foreign key constraints.
CORRECT ANSWER : B
Rationale: The main danger of failing to normalize a many-to-many relationship is the risk of
anomalies where shared data is lost when one side of the relationship is deleted (deletion
anomaly). While storage (A) and performance (C) are factors, the integrity anomaly is the
critical design risk; SQL handles these joins easily (D).
8. Which SQL command is most appropriate for modifying the data type of an existing
column in a production database without losing the data contained within that column?
A. DROP COLUMN followed by ADD COLUMN.
B. TRUNCATE TABLE then ALTER TABLE.
C. ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type.
D. CREATE TABLE AS SELECT * INTO new_table.
CORRECT ANSWER : C
ASSESSMENT] EXAM with Questions and
Answers/Plus a Rationale Updated 2026 A+/Instant
Download PDF
Table of Contents
1. Principles of Data Management and Governance
2. Database Design and Normalization
3. SQL Querying and Optimization
4. Data Security, Privacy, and Compliance
5. Data Warehousing, ETL, and Integration
1. A data architect is tasked with integrating a legacy relational database into a modern data
warehouse environment. The legacy system frequently suffers from update anomalies due
to redundant data storage. Which normalization process should the architect prioritize to
eliminate these anomalies while maintaining referential integrity?
A. Denormalizing the schema to improve read performance for analytical queries.
B. Implementing Third Normal Form (3NF) to ensure that all non-key attributes are
dependent only on the primary key.
C. Migrating the data to a NoSQL document store to bypass schema constraints.
D. Applying First Normal Form (1NF) by removing all repeating groups and creating a flat file
structure.
CORRECT ANSWER : B
Rationale: 3NF is specifically designed to eliminate transitive dependencies and redundant data,
which are the primary causes of update anomalies. Denormalizing (A) would worsen the
,anomalies; migrating to NoSQL (C) does not address the relational structure issues; and 1NF
(D) is insufficient to resolve dependencies that lead to anomalies.
2. You are optimizing a complex SQL query that joins three large tables, each containing
millions of rows. The execution plan indicates a full table scan on the largest table,
resulting in high latency. What is the most effective strategy to improve performance?
A. Creating a composite index on the columns used in the JOIN and WHERE clauses.
B. Increasing the buffer pool size in the database configuration.
C. Replacing the JOINs with multiple subqueries to simplify the execution path.
D. Changing the database isolation level to READ UNCOMMITTED.
CORRECT ANSWER : A
Rationale: A composite index allows the query optimizer to perform an index seek instead of a
full table scan by narrowing the data set before the join. Increasing buffer pool size (B) is a
temporary fix for memory pressure, not a query optimization; subqueries (C) are generally less
efficient than joins; and changing the isolation level (D) risks data inconsistency.
3. A healthcare organization is implementing a new patient records system. The database
must comply with strict data privacy regulations that mandate auditing all access to
sensitive PII. Which security mechanism is most appropriate?
A. Implementing Column-Level Encryption on all fields containing patient names.
B. Enabling Database Auditing and implementing Row-Level Security (RLS) to restrict
access based on user role.
C. Moving all patient data to an encrypted cloud storage bucket.
D. Using stored procedures to encapsulate all data access, preventing direct table queries.
CORRECT ANSWER : B
Rationale: Auditing satisfies the regulatory requirement for tracking access, while RLS ensures
that users can only interact with data pertinent to their role. Encryption (A) protects data at rest
but does not manage access; cloud storage (C) does not provide granular access control; and
stored procedures (D) are a security layer but do not replace the need for formal auditing.
4. A data analyst observes that a specific report running against the production transactional
database is significantly slowing down user transactions. The report requires complex
aggregations over historical data. What is the optimal architectural solution?
,A. Implement a READ ONLY replica of the database and point the report to the replica.
B. Extract, Transform, and Load (ETL) the data into a dedicated data warehouse
optimized for analytical processing.
C. Increase the CPU resources of the current database server to handle the reporting load.
D. Convert the reporting queries into materialized views to be refreshed every 24 hours.
CORRECT ANSWER : B
Rationale: Analytical reporting should be separated from transactional processing (OLTP) to
prevent performance degradation; a data warehouse (OLAP) is built for this purpose. Using a
replica (A) or increasing resources (C) does not fundamentally solve the architecture mismatch
between OLTP and OLAP needs; materialized views (D) may still impact performance during
the refresh cycle.
5. When designing a database schema, you encounter a situation where an entity has a
many-to-many relationship with itself (e.g., employees who are managers of other
employees). How should this be modeled?
A. Add a 'ManagerID' column to the Employee table that references the 'EmployeeID' column in
the same table.
B. Create a separate 'Management' table with 'ManagerID' and 'SubordinateID' columns as a
recursive relationship.
C. Create a linking table (associative entity) with foreign keys referencing the
'EmployeeID' primary key of the Employee table.
D. Duplicate the Employee table data into a separate 'Managers' table to avoid circular
references.
CORRECT ANSWER : C
Rationale: A many-to-many relationship requires an associative entity (linking table) to properly
resolve the relationship while maintaining normalization. Adding a 'ManagerID' (A) only
supports one-to-many relationships; a separate management table (B) is redundant if a linking
table is used; duplicating data (D) violates normalization principles and risks data
inconsistency.
6. An organization is migrating to a cloud database service. They need to ensure that the
database remains available even if an entire data center fails. Which feature is required?
A. Multi-Instance read scaling.
, B. Multi-AZ (Availability Zone) deployment with synchronous replication.
C. Automated daily snapshots stored in cross-region storage.
D. Vertical scaling to increase server processing power.
CORRECT ANSWER : B
Rationale: Multi-AZ deployment with synchronous replication ensures that a standby instance is
ready to take over immediately if the primary instance fails, providing high availability. Read
scaling (A) addresses capacity, not availability; snapshots (C) are for recovery, not high
availability; and vertical scaling (D) addresses performance, not downtime.
7. A developer is designing a system where users can have multiple phone numbers, and
phone numbers can belong to multiple users (e.g., shared company devices). What is the
primary risk of not properly normalizing this schema?
A. Increased storage overhead for the linking table.
B. Anomalies during deletions, where removing a phone number might inadvertently
remove valid user associations.
C. Slower query performance due to the complexity of joins.
D. Incompatibility with standard SQL syntax for foreign key constraints.
CORRECT ANSWER : B
Rationale: The main danger of failing to normalize a many-to-many relationship is the risk of
anomalies where shared data is lost when one side of the relationship is deleted (deletion
anomaly). While storage (A) and performance (C) are factors, the integrity anomaly is the
critical design risk; SQL handles these joins easily (D).
8. Which SQL command is most appropriate for modifying the data type of an existing
column in a production database without losing the data contained within that column?
A. DROP COLUMN followed by ADD COLUMN.
B. TRUNCATE TABLE then ALTER TABLE.
C. ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type.
D. CREATE TABLE AS SELECT * INTO new_table.
CORRECT ANSWER : C
