WGU D830 / D334 DATA
SYSTEMS MANAGEMENT FINAL
OBJECTIVE ASSESSMENT
MASTER PREP 150 REAL Q&AS
D830 / D334 Objective Assessment Practice Exam
(Questions 1–150)
1. A data architect is designing an enterprise relational
database management system (RDBMS) and must enforce
a business rule where an employee's salary cannot be less
than the state's minimum wage. Which of the following
constraint mechanisms should be explicitly implemented
within the DDL schema definition to validate this
structural restriction natively at the table level without
relying on application-tier logic?
A) A unique constraint applied directly to the employee
identity attribute.
B) A foreign key constraint linking to a static master
payroll registry table.
C) A CHECK constraint defined with a logical
conditional expression on the salary column.
(Correct Answer: )
D) A primary key identity constraint applied sequentially
across the record attributes.
Rationale: A CHECK constraint is a declarative data
integrity constraint used in SQL to specify that the values
in a given column must satisfy a specific logical condition
, or boolean expression before a row insertion or
modification can be committed.
2. During a logical schema migration, an administrator
discovers a table containing a composite primary key
consisting of ProjectID and EmployeeID. The table also
contains an attribute named EmployeeEmergencyContact
which is fully dependent on only the EmployeeID portion
of the key. Which normal form is currently being violated
by this design, and what anomaly does it introduce?
A) First Normal Form (1NF); it introduces a cross-product
valuation anomaly.
B) Second Normal Form (2NF); it introduces a
partial functional dependency anomaly where
non-key attributes depend on a subset of the
composite primary key. (Correct Answer: )
C) Third Normal Form (3NF); it introduces a transitive
data redundancy anomaly.
D) Boyce-Codd Normal Form (BCNF); it introduces a
multi-valued join dependency conflict.
Rationale: Second Normal Form (2NF) requires that
the table is in 1NF and that all non-key attributes are
fully functionally dependent on the entire primary key. A
partial dependency exists when a non-key attribute
depends on only part of a composite primary key,
requiring decomposition to reach 2NF.
3. An application developer executes an unstructured SQL
transaction that modifies several rows in an inventory
management table. Concurrently, a financial auditing
process reads the exact same table to compile an
automated report. If the RDBMS isolation level is set to
READ UNCOMMITTED, which data concurrency
phenomenon is the auditing process exposed to?
A) Dirty Reads, where the auditing process can
read uncommitted modifications that might be
, rolled back later by the developer. (Correct
Answer: )
B) Non-repeatable Reads, where a single transaction reads
different values for the same row upon sequential reads.
C) Phantom Reads, where new rows inserted by another
concurrent transaction suddenly appear in a subsequent
query.
D) Lost Updates, where two concurrent processes
overwrite each other's data streams sequentially.
Rationale: The READ UNCOMMITTED isolation level
allows a transaction to read data that is currently being
modified by another transaction but has not yet been
committed. This exposes the reading transaction to "dirty
reads" if the modifying transaction fails or rolls back.
4. A database engineer needs to extract data from two
distinct tables: CorporateCustomers and
OnlineSubscribers. The objective is to produce a single
unified result set containing all unique email addresses
present in either table, removing any duplicate
occurrences across both datasets. Which SQL set operator
must be utilized to achieve this outcome?
A) UNION ALL
B) UNION (Correct Answer: )
C) INTERSECT
D) EXCEPT
Rationale: The UNION operator combines the result
sets of two or more SELECT queries into a single result
set and automatically filters out duplicate rows. In
contrast, UNION ALL retains all duplicate rows, while
INTERSECT finds only matching items.
5. While reviewing a performance bottleneck in an e-
commerce transactional database, a systems administrator
notices that a query filtering orders by ShippingDate
, requires a full table scan over millions of records. To
optimize retrieval speeds for this specific column without
altering the underlying physical storage sequence of the
table, what structural modification should be executed?
A) Convert the table into a clustered multi-dimensional
partition array.
B) Create a non-clustered index on the
ShippingDate attribute. (Correct Answer: )
C) Redefine the ShippingDate column as the table’s
primary key descriptor.
D) Implement a surrogate vertical view index across the
master database catalog.
Rationale: A non-clustered index creates a separate,
sorted pointer structure that maps to the physical rows of
the table without rearranging the actual underlying data
rows. This allows the query engine to rapidly locate data
points without executing time-consuming full table scans.
6. In a conceptual Entity-Relationship (ER) model for a
university database, a business rule states that a Student
may register for multiple Courses, and a single Course can
contain many registered Students. How should this
relationship type be represented during the transition
from a conceptual model to a logical relational schema
mapping?
A) By adding a foreign key directly inside the Student table
pointing to the Course table.
B) By creating a recursive relationship loop on the Course
master table structure.
C) By decomposing the relationship into a new
associative (junction) table containing foreign
keys referencing both primary keys. (Correct
Answer: )
D) By implementing a multi-valued array column inside
both the Student and Course entities.
SYSTEMS MANAGEMENT FINAL
OBJECTIVE ASSESSMENT
MASTER PREP 150 REAL Q&AS
D830 / D334 Objective Assessment Practice Exam
(Questions 1–150)
1. A data architect is designing an enterprise relational
database management system (RDBMS) and must enforce
a business rule where an employee's salary cannot be less
than the state's minimum wage. Which of the following
constraint mechanisms should be explicitly implemented
within the DDL schema definition to validate this
structural restriction natively at the table level without
relying on application-tier logic?
A) A unique constraint applied directly to the employee
identity attribute.
B) A foreign key constraint linking to a static master
payroll registry table.
C) A CHECK constraint defined with a logical
conditional expression on the salary column.
(Correct Answer: )
D) A primary key identity constraint applied sequentially
across the record attributes.
Rationale: A CHECK constraint is a declarative data
integrity constraint used in SQL to specify that the values
in a given column must satisfy a specific logical condition
, or boolean expression before a row insertion or
modification can be committed.
2. During a logical schema migration, an administrator
discovers a table containing a composite primary key
consisting of ProjectID and EmployeeID. The table also
contains an attribute named EmployeeEmergencyContact
which is fully dependent on only the EmployeeID portion
of the key. Which normal form is currently being violated
by this design, and what anomaly does it introduce?
A) First Normal Form (1NF); it introduces a cross-product
valuation anomaly.
B) Second Normal Form (2NF); it introduces a
partial functional dependency anomaly where
non-key attributes depend on a subset of the
composite primary key. (Correct Answer: )
C) Third Normal Form (3NF); it introduces a transitive
data redundancy anomaly.
D) Boyce-Codd Normal Form (BCNF); it introduces a
multi-valued join dependency conflict.
Rationale: Second Normal Form (2NF) requires that
the table is in 1NF and that all non-key attributes are
fully functionally dependent on the entire primary key. A
partial dependency exists when a non-key attribute
depends on only part of a composite primary key,
requiring decomposition to reach 2NF.
3. An application developer executes an unstructured SQL
transaction that modifies several rows in an inventory
management table. Concurrently, a financial auditing
process reads the exact same table to compile an
automated report. If the RDBMS isolation level is set to
READ UNCOMMITTED, which data concurrency
phenomenon is the auditing process exposed to?
A) Dirty Reads, where the auditing process can
read uncommitted modifications that might be
, rolled back later by the developer. (Correct
Answer: )
B) Non-repeatable Reads, where a single transaction reads
different values for the same row upon sequential reads.
C) Phantom Reads, where new rows inserted by another
concurrent transaction suddenly appear in a subsequent
query.
D) Lost Updates, where two concurrent processes
overwrite each other's data streams sequentially.
Rationale: The READ UNCOMMITTED isolation level
allows a transaction to read data that is currently being
modified by another transaction but has not yet been
committed. This exposes the reading transaction to "dirty
reads" if the modifying transaction fails or rolls back.
4. A database engineer needs to extract data from two
distinct tables: CorporateCustomers and
OnlineSubscribers. The objective is to produce a single
unified result set containing all unique email addresses
present in either table, removing any duplicate
occurrences across both datasets. Which SQL set operator
must be utilized to achieve this outcome?
A) UNION ALL
B) UNION (Correct Answer: )
C) INTERSECT
D) EXCEPT
Rationale: The UNION operator combines the result
sets of two or more SELECT queries into a single result
set and automatically filters out duplicate rows. In
contrast, UNION ALL retains all duplicate rows, while
INTERSECT finds only matching items.
5. While reviewing a performance bottleneck in an e-
commerce transactional database, a systems administrator
notices that a query filtering orders by ShippingDate
, requires a full table scan over millions of records. To
optimize retrieval speeds for this specific column without
altering the underlying physical storage sequence of the
table, what structural modification should be executed?
A) Convert the table into a clustered multi-dimensional
partition array.
B) Create a non-clustered index on the
ShippingDate attribute. (Correct Answer: )
C) Redefine the ShippingDate column as the table’s
primary key descriptor.
D) Implement a surrogate vertical view index across the
master database catalog.
Rationale: A non-clustered index creates a separate,
sorted pointer structure that maps to the physical rows of
the table without rearranging the actual underlying data
rows. This allows the query engine to rapidly locate data
points without executing time-consuming full table scans.
6. In a conceptual Entity-Relationship (ER) model for a
university database, a business rule states that a Student
may register for multiple Courses, and a single Course can
contain many registered Students. How should this
relationship type be represented during the transition
from a conceptual model to a logical relational schema
mapping?
A) By adding a foreign key directly inside the Student table
pointing to the Course table.
B) By creating a recursive relationship loop on the Course
master table structure.
C) By decomposing the relationship into a new
associative (junction) table containing foreign
keys referencing both primary keys. (Correct
Answer: )
D) By implementing a multi-valued array column inside
both the Student and Course entities.
