WGU D426 OBJECTIVE ASSESSMENT
EXAM Questions And Correct Answers With
Rationales
Practice Question Bank & Answer Rationale
THIS PACK CONTAINS
150 exam-style multiple-choice questions. Each question includes the correct answer and a complete
rationale for focused revision.
COVERAGE
This exam assesses foundational and applied competencies in data management, covering relational
modeling, SQL, normalization, transactions, indexing, data warehousing, distributed databases, and data
governance. Items require interpretation of schemas, query logic, and architectural trade-offs typical of a
rigorous US university database systems course.
STUDY GUIDE
Recommended duration: 2 hours 30 minutes. Passing target: 70% (Competent).
HIGHP - Page 1 of 50
,1. A library system tracks members and the books they borrow. A member may borrow
many books over time, and a book may be borrowed by many members (across different
loan periods). Which ER construct correctly models the relationship between Member and
Book for a single loan event?
[ ] A. A one-to-many relationship from Member to Book.
[ ] B. A many-to-many relationship resolved by an associative entity (Loan) with its own attributes.
[ ] C. A one-to-one relationship because each loan has one member and one book.
[ ] D. A unary relationship on Book because books reference themselves.
CORRECT: B. A many-to-many relationship resolved by an associative entity (Loan) with its own
attributes.
A single loan involves one member and one book, but over time members borrow many books and books are
borrowed by many members, so the conceptual relationship is many-to-many. This is resolved by an
associative entity (Loan) that can carry attributes such as loan date and due date. Options A and C
misrepresent the cardinality, and D incorrectly models a self-referencing relationship.
2. Given the relation R(A, B, C, D) with functional dependencies AB -> C, C -> D, and D ->
A, which normal form does R currently violate?
[ ] A. 1NF only, due to repeating groups.
[ ] B. 2NF, because C is partially dependent on AB.
[ ] C. 3NF, because of the transitive dependency C -> D where C is not a superkey.
[ ] D. BCNF, because every determinant is not a candidate key.
CORRECT: D. BCNF, because every determinant is not a candidate key.
The candidate keys are AB and BC (since C -> D -> A, so C determines A, making BC a key). The
dependency C -> D has determinant C, which is not a superkey, so R violates BCNF. It satisfies 3NF because
D is prime (part of key BC), but BCNF is stricter. Options A and B are incorrect because there are no
repeating groups or partial dependencies on a composite key.
3. A query must return each department name along with the count of employees,
including departments with zero employees. Which SQL construct is required?
[ ] A. INNER JOIN with GROUP BY department.
[ ] B. LEFT OUTER JOIN from department to employee with COUNT(employee_id).
[ ] C. RIGHT OUTER JOIN from employee to department with COUNT(*).
[ ] D. CROSS JOIN with a WHERE clause filtering null employee IDs.
CORRECT: B. LEFT OUTER JOIN from department to employee with COUNT(employee_id).
A LEFT OUTER JOIN from department to employee preserves all departments, and COUNT(employee_id)
ignores NULLs, yielding zero for empty departments. An INNER JOIN (A) drops departments with no
employees. A RIGHT OUTER JOIN from employee to department (C) would drop departments with no
employees because the preserved side is employee. A CROSS JOIN (D) produces a Cartesian product and
HIGHP - Page 2 of 50
, does not correctly aggregate counts.
4. In a transaction, T1 reads a row, T2 updates that row and commits, then T1 reads the
same row again and sees a different value. Which concurrency phenomenon has
occurred?
[ ] A. Dirty read
[ ] B. Phantom read
[ ] C. Non-repeatable read
[ ] D. Lost update
CORRECT: C. Non-repeatable read
A non-repeatable read occurs when a transaction reads the same row twice and gets different values
because another committed transaction modified it in between. A dirty read (A) involves reading uncommitted
data. A phantom read (B) involves new rows appearing in a range query. A lost update (D) occurs when two
transactions overwrite each other's changes without proper locking.
5. A table has a composite primary key (order_id, product_id) and frequent queries filter
on product_id alone. Which indexing strategy best improves those queries?
[ ] A. Add a clustered index on (order_id, product_id).
[ ] B. Add a nonclustered index on (product_id) including order_id.
[ ] C. Add a hash index on the composite key.
[ ] D. Add a bitmap index on order_id.
CORRECT: B. Add a nonclustered index on (product_id) including order_id.
A nonclustered index on product_id allows efficient lookups when filtering by product_id, and including
order_id can make it covering for common queries. The clustered index on (order_id, product_id) (A) is
ordered by order_id first, so it does not efficiently support product_id-only predicates. Hash indexes (C)
support equality only and not range scans, and bitmap indexes (D) are typically for low-cardinality columns
and not order_id.
6. In a star schema for retail sales, which table contains foreign keys to dimension tables
and additive numeric measures?
[ ] A. The dimension table
[ ] B. The fact table
[ ] C. The bridge table
[ ] D. The snowflake table
CORRECT: B. The fact table
The fact table in a star schema stores quantitative measures and foreign keys referencing dimension tables,
HIGHP - Page 3 of 50
, enabling aggregation. Dimension tables (A) hold descriptive attributes. Bridge tables (C) resolve
many-to-many relationships between dimensions. Snowflake tables (D) are normalized dimension tables, not
the central measure store.
7. A distributed database must guarantee that a read always returns the most recent write,
even during network partitions. According to the CAP theorem, which property must be
sacrificed?
[ ] A. Consistency
[ ] B. Availability
[ ] C. Partition tolerance
[ ] D. Durability
CORRECT: B. Availability
CAP states that under a network partition, a system must choose between consistency and availability. To
guarantee the latest write is always read (strong consistency) during a partition, the system must sacrifice
availability. Partition tolerance (C) is required in a distributed system, and durability (D) is not part of CAP.
8. Which statement correctly describes the role of a data steward in data governance?
[ ] A. They write all SQL queries for the organization.
[ ] B. They define and enforce data quality, privacy, and usage policies for a domain.
[ ] C. They are responsible for physical server maintenance.
[ ] D. They approve the annual IT budget.
CORRECT: B. They define and enforce data quality, privacy, and usage policies for a domain.
A data steward is accountable for data quality, policy enforcement, and stewardship within a business
domain, bridging IT and business. Writing SQL (A) is a technical task, not the steward's primary role. Server
maintenance (C) is an infrastructure responsibility, and budget approval (D) is a management function.
9. A company needs a database that supports flexible, schema-less documents and
horizontal scaling for a high-volume web application. Which database category is most
appropriate?
[ ] A. Relational OLTP database with strict normalization
[ ] B. Document-oriented NoSQL database
[ ] C. Data warehouse with star schema
[ ] D. Hierarchical mainframe database
CORRECT: B. Document-oriented NoSQL database
Document-oriented NoSQL databases store semi-structured documents, allow flexible schemas, and scale
horizontally, matching the requirement. A normalized relational OLTP database (A) imposes rigid schemas
HIGHP - Page 4 of 50
EXAM Questions And Correct Answers With
Rationales
Practice Question Bank & Answer Rationale
THIS PACK CONTAINS
150 exam-style multiple-choice questions. Each question includes the correct answer and a complete
rationale for focused revision.
COVERAGE
This exam assesses foundational and applied competencies in data management, covering relational
modeling, SQL, normalization, transactions, indexing, data warehousing, distributed databases, and data
governance. Items require interpretation of schemas, query logic, and architectural trade-offs typical of a
rigorous US university database systems course.
STUDY GUIDE
Recommended duration: 2 hours 30 minutes. Passing target: 70% (Competent).
HIGHP - Page 1 of 50
,1. A library system tracks members and the books they borrow. A member may borrow
many books over time, and a book may be borrowed by many members (across different
loan periods). Which ER construct correctly models the relationship between Member and
Book for a single loan event?
[ ] A. A one-to-many relationship from Member to Book.
[ ] B. A many-to-many relationship resolved by an associative entity (Loan) with its own attributes.
[ ] C. A one-to-one relationship because each loan has one member and one book.
[ ] D. A unary relationship on Book because books reference themselves.
CORRECT: B. A many-to-many relationship resolved by an associative entity (Loan) with its own
attributes.
A single loan involves one member and one book, but over time members borrow many books and books are
borrowed by many members, so the conceptual relationship is many-to-many. This is resolved by an
associative entity (Loan) that can carry attributes such as loan date and due date. Options A and C
misrepresent the cardinality, and D incorrectly models a self-referencing relationship.
2. Given the relation R(A, B, C, D) with functional dependencies AB -> C, C -> D, and D ->
A, which normal form does R currently violate?
[ ] A. 1NF only, due to repeating groups.
[ ] B. 2NF, because C is partially dependent on AB.
[ ] C. 3NF, because of the transitive dependency C -> D where C is not a superkey.
[ ] D. BCNF, because every determinant is not a candidate key.
CORRECT: D. BCNF, because every determinant is not a candidate key.
The candidate keys are AB and BC (since C -> D -> A, so C determines A, making BC a key). The
dependency C -> D has determinant C, which is not a superkey, so R violates BCNF. It satisfies 3NF because
D is prime (part of key BC), but BCNF is stricter. Options A and B are incorrect because there are no
repeating groups or partial dependencies on a composite key.
3. A query must return each department name along with the count of employees,
including departments with zero employees. Which SQL construct is required?
[ ] A. INNER JOIN with GROUP BY department.
[ ] B. LEFT OUTER JOIN from department to employee with COUNT(employee_id).
[ ] C. RIGHT OUTER JOIN from employee to department with COUNT(*).
[ ] D. CROSS JOIN with a WHERE clause filtering null employee IDs.
CORRECT: B. LEFT OUTER JOIN from department to employee with COUNT(employee_id).
A LEFT OUTER JOIN from department to employee preserves all departments, and COUNT(employee_id)
ignores NULLs, yielding zero for empty departments. An INNER JOIN (A) drops departments with no
employees. A RIGHT OUTER JOIN from employee to department (C) would drop departments with no
employees because the preserved side is employee. A CROSS JOIN (D) produces a Cartesian product and
HIGHP - Page 2 of 50
, does not correctly aggregate counts.
4. In a transaction, T1 reads a row, T2 updates that row and commits, then T1 reads the
same row again and sees a different value. Which concurrency phenomenon has
occurred?
[ ] A. Dirty read
[ ] B. Phantom read
[ ] C. Non-repeatable read
[ ] D. Lost update
CORRECT: C. Non-repeatable read
A non-repeatable read occurs when a transaction reads the same row twice and gets different values
because another committed transaction modified it in between. A dirty read (A) involves reading uncommitted
data. A phantom read (B) involves new rows appearing in a range query. A lost update (D) occurs when two
transactions overwrite each other's changes without proper locking.
5. A table has a composite primary key (order_id, product_id) and frequent queries filter
on product_id alone. Which indexing strategy best improves those queries?
[ ] A. Add a clustered index on (order_id, product_id).
[ ] B. Add a nonclustered index on (product_id) including order_id.
[ ] C. Add a hash index on the composite key.
[ ] D. Add a bitmap index on order_id.
CORRECT: B. Add a nonclustered index on (product_id) including order_id.
A nonclustered index on product_id allows efficient lookups when filtering by product_id, and including
order_id can make it covering for common queries. The clustered index on (order_id, product_id) (A) is
ordered by order_id first, so it does not efficiently support product_id-only predicates. Hash indexes (C)
support equality only and not range scans, and bitmap indexes (D) are typically for low-cardinality columns
and not order_id.
6. In a star schema for retail sales, which table contains foreign keys to dimension tables
and additive numeric measures?
[ ] A. The dimension table
[ ] B. The fact table
[ ] C. The bridge table
[ ] D. The snowflake table
CORRECT: B. The fact table
The fact table in a star schema stores quantitative measures and foreign keys referencing dimension tables,
HIGHP - Page 3 of 50
, enabling aggregation. Dimension tables (A) hold descriptive attributes. Bridge tables (C) resolve
many-to-many relationships between dimensions. Snowflake tables (D) are normalized dimension tables, not
the central measure store.
7. A distributed database must guarantee that a read always returns the most recent write,
even during network partitions. According to the CAP theorem, which property must be
sacrificed?
[ ] A. Consistency
[ ] B. Availability
[ ] C. Partition tolerance
[ ] D. Durability
CORRECT: B. Availability
CAP states that under a network partition, a system must choose between consistency and availability. To
guarantee the latest write is always read (strong consistency) during a partition, the system must sacrifice
availability. Partition tolerance (C) is required in a distributed system, and durability (D) is not part of CAP.
8. Which statement correctly describes the role of a data steward in data governance?
[ ] A. They write all SQL queries for the organization.
[ ] B. They define and enforce data quality, privacy, and usage policies for a domain.
[ ] C. They are responsible for physical server maintenance.
[ ] D. They approve the annual IT budget.
CORRECT: B. They define and enforce data quality, privacy, and usage policies for a domain.
A data steward is accountable for data quality, policy enforcement, and stewardship within a business
domain, bridging IT and business. Writing SQL (A) is a technical task, not the steward's primary role. Server
maintenance (C) is an infrastructure responsibility, and budget approval (D) is a management function.
9. A company needs a database that supports flexible, schema-less documents and
horizontal scaling for a high-volume web application. Which database category is most
appropriate?
[ ] A. Relational OLTP database with strict normalization
[ ] B. Document-oriented NoSQL database
[ ] C. Data warehouse with star schema
[ ] D. Hierarchical mainframe database
CORRECT: B. Document-oriented NoSQL database
Document-oriented NoSQL databases store semi-structured documents, allow flexible schemas, and scale
horizontally, matching the requirement. A normalized relational OLTP database (A) imposes rigid schemas
HIGHP - Page 4 of 50
