W GU C170 OB JECT IVE ASSESSMENT
W E S T E R N GO V E R N O R S UN IV E R S IT Y · L AT E S T 2 0 2 0 2 7
WGU C170
Objective Assessment
Complete Solution
105-Question Database Applications Competency Exam
— With Complete Solution
A 105-question objective assessment aligned with the WGU C170 Data
Management Applications competency blueprint for the 2026–2027
academic cycle. Coverage spans the relational model, ER modeling,
normalization (1NF–BCNF), DDL/DML, SQL querying, JOINs, subqueries,
aggregates, views, indexes, security, transactions, ACID, concurrency, and
data warehousing — with complete worked solutions, SQL syntax reasoning,
and step-by-step query analysis.
T O TA L Q U E S T I O N S SE CTIO N S F O R M AT
105 11 MCQ · A–D
30% recall · 50% application · 20% analysis
11 competency domains · SQL · ERD · Normalization · Transactions
· OLAP
Z .A I · I N F O R M AT I O N T E C H N O L O G Y E D U C AT I O N C OMP L ETE
SERIES S OL U TION
,WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
WGU C170 Objective Assessment
Data Management Applications · With Complete Solution · Latest
Total Questions 105
Total Sections 11
Format Multiple Choice (A–D), one correct answer per question
Cognitive Mix ~30% recall, ~50% application, ~20% analysis
Question Style ~70% application/scenario-based (SQL writing, design), ~30% direct recall
Coverage Relational model; ERD; normalization (1NF–BCNF); DDL/DML; SELECT; JOINs; subqueries;
aggregates; views/indexes; transactions/ACID; OLAP/NoSQL
Answer Format Correct answer marked with [CORRECT]; rationale with SQL syntax and complete solution guidance
Complete Solution Step-by-step reasoning reflecting WGU C170 objective assessment blueprint for 2026–2027
Section 1: Database Fundamentals and the Relational Model
Database Concepts, RDBMS, Tables, Keys, & Relationships (Q1–Q12)
Q1: A retail company currently stores customer data in a flat spreadsheet with columns for CustomerID,
Name, Orders (comma-separated list of order IDs), and OrderTotals (comma-separated list of dollar
amounts). When a customer asks 'how much did CustomerID 1001 spend in total?', the analyst must
manually split the comma-separated lists and sum the totals. Which fundamental problem does this design
exhibit, and what is the relational model solution?
A. The design exhibits the 'repeating group' problem (non-atomic values in a single column); the relational
model requires each cell to hold a single atomic value, and the proper solution is to decompose into two
tables: Customers(CustomerID, Name) and Orders(OrderID, CustomerID, OrderTotal) linked by a foreign
key [CORRECT]
B. The design exhibits slow file I/O; the solution is to compress the spreadsheet
C. The design lacks an index; the solution is to add an index on CustomerID
D. The design is correct because spreadsheets are valid databases
Correct Answer: A
Rationale: First Normal Form (1NF) requires that every column in a row hold a single atomic value — a cell cannot contain a
list, set, or repeating group. The 'repeating group' (comma-separated order IDs and totals) violates 1NF and forces procedural
manipulation to query. The relational model solution is decomposition: each customer's orders become rows in a separate
Orders table linked by CustomerID (foreign key). Complete solution: the Customers table holds one row per customer; the
Orders table holds one row per order, with CustomerID as a foreign key reference to Customers(CustomerID). This enables
standard SQL aggregation (SUM(OrderTotal) GROUP BY CustomerID).
Q2: Which of the following statements accurately distinguishes a database from a database management
system (DBMS)?
A. A database is the logical container of related data; a DBMS is the software system that creates, manages,
secures, and provides programmatic and interactive access to that database (e.g., MySQL, PostgreSQL,
Z.ai · Information Technology Education Series Page 1
,WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
Oracle, SQL Server) [CORRECT]
B. A database is the software; a DBMS is the data
C. A database and a DBMS are interchangeable terms for the same thing
D. A DBMS is a hardware device; a database is software
Correct Answer: A
Rationale: A database is a structured collection of related data (logically independent of any software). A DBMS (Database
Management System) is the software application that creates, manages, secures, and provides access to databases — it
implements the relational model, enforces constraints, processes SQL, manages transactions and concurrency, and provides
backup/recovery. Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite. The data is the 'what'; the
DBMS is the 'how'. This distinction is foundational WGU C170 competency material.
Q3: In the relational model, what is the difference between a 'relation' and a 'tuple'?
A. A relation is the mathematical term for a table (an unordered set of tuples sharing the same attributes);
a tuple is the mathematical term for a single row (one set of attribute values for one entity instance)
[CORRECT]
B. A relation is a column; a tuple is a row
C. A relation is the schema; a tuple is the table
D. A relation is a query result; a tuple is the query
Correct Answer: A
Rationale: In relational theory (Codd, 1970), a relation is a set of tuples with the same heading (set of attributes) — the
abstract mathematical concept corresponding to a SQL 'table'. A tuple is an ordered set of attribute values — corresponding to
a SQL 'row'. An attribute is a named column with an associated domain (set of allowed values). Tables, rows, and columns
are SQL implementation terms; relations, tuples, and attributes are the theoretical terms. The distinction matters for
understanding relational algebra, normalization theory, and the mathematical foundations of SQL.
Q4: A primary key constraint uniquely identifies each row in a table and must satisfy two properties.
Which of the following correctly identifies both properties?
A. Uniqueness (no two rows can have the same primary key value) and minimality (no subset of the primary
key columns can be removed while still preserving uniqueness — i.e., no proper subset is itself unique)
[CORRECT]
B. Uniqueness and non-nullability of every column in the table
C. Indexability and foreign-key referenceability
D. Immutability (the value never changes) and confidentiality
Correct Answer: A
Rationale: A primary key must satisfy two properties: (1) uniqueness — no two rows may have the same primary key value(s);
(2) minimality — no proper subset of the primary key columns can be removed while still preserving uniqueness (i.e., the key is
the smallest set of columns that uniquely identifies a row). For a single-column primary key, minimality is automatic. For a
composite key (e.g., (OrderID, ProductID) in an order-line-items table), minimality requires that neither OrderID alone nor
ProductID alone uniquely identifies the row. A primary key also implies NOT NULL and UNIQUE constraints.
Q5: A table Employee(EmployeeID, DepartmentID, Name, Salary) has a foreign key DepartmentID
referencing Department(DepartmentID). An attempt to insert a row with DepartmentID = 999 fails with a
constraint violation because DepartmentID 999 does not exist in the Department table. Which integrity rule
has been enforced?
A. Referential integrity — the foreign key value in the child table (Employee.DepartmentID) must either
match an existing primary key value in the parent table (Department.DepartmentID) or be NULL (if the
Z.ai · Information Technology Education Series Page 2
, WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
foreign key column allows NULLs) [CORRECT]
B. Domain integrity — the value must fall within the allowed data type
C. Entity integrity — the primary key must be unique and non-null
D. Transaction integrity — the insert must be atomic
Correct Answer: A
Rationale: Referential integrity requires that every foreign key value in a child table either matches an existing primary key
value in the parent (referenced) table or is NULL (if the FK column allows NULLs). Inserting Employee with DepartmentID =
999 (which does not exist in Department) violates referential integrity. Domain integrity (column data type and constraints),
entity integrity (primary key uniqueness and non-nullability), and transaction integrity (ACID) are separate concepts.
Referential integrity is enforced by foreign key constraints and is essential for maintaining the validity of relationships across
tables.
Q6: A table named Students has columns StudentID (the primary key), Email, and Phone. The database
designer wants to ensure that no two students can have the same Email value, while still allowing NULL
emails. Which constraint should be applied?
A. UNIQUE constraint on the Email column — UNIQUE allows at most one NULL value (in standard SQL)
and prevents duplicate non-NULL values; this is the correct choice for 'no duplicates but NULLs are
allowed' [CORRECT]
B. PRIMARY KEY constraint on the Email column
C. NOT NULL constraint on the Email column
D. CHECK constraint requiring Email to be non-empty
Correct Answer: A
Rationale: A UNIQUE constraint enforces uniqueness of column values but allows NULL values (standard SQL allows
multiple NULLs in a UNIQUE column, though some databases like SQL Server and Oracle allow only one NULL). PRIMARY
KEY implies both UNIQUE and NOT NULL — so a column that may legitimately be NULL cannot be a primary key. NOT
NULL alone does not prevent duplicates. A CHECK constraint cannot enforce uniqueness (it only checks per-row conditions).
For an email column where duplicates are forbidden but NULL is permitted (student hasn't provided email yet), UNIQUE is
the correct choice. Complete solution: `ALTER TABLE Students ADD CONSTRAINT uc_email UNIQUE (Email);`
Q7: Which of the following is a 'candidate key' in a database table?
A. Any minimal set of one or more columns that can uniquely identify a row — a table may have multiple
candidate keys, and one is chosen as the primary key; the others become alternate keys [CORRECT]
B. A key used only in foreign tables
C. A key generated by the database automatically (surrogate key)
D. A key that is a candidate for deletion
Correct Answer: A
Rationale: A candidate key is any minimal set of attributes that uniquely identifies each row in a relation (satisfies uniqueness
and minimality). A table can have multiple candidate keys; one is selected as the primary key, and the others become 'alternate
keys' (typically enforced with UNIQUE constraints). Example: In an Employees table, both EmployeeID (a surrogate) and
(GovernmentIDNumber, Country) might be candidate keys; EmployeeID is typically chosen as the primary key, and
GovernmentIDNumber+Country would be enforced with a UNIQUE constraint. Surrogate keys (auto-generated) are a special
case of candidate keys.
Q8: A composite primary key is required when:
A. No single column uniquely identifies a row, but a combination of two or more columns does (common in
junction/associative tables representing many-to-many relationships, such as OrderLineItems(OrderID,
Z.ai · Information Technology Education Series Page 3
W E S T E R N GO V E R N O R S UN IV E R S IT Y · L AT E S T 2 0 2 0 2 7
WGU C170
Objective Assessment
Complete Solution
105-Question Database Applications Competency Exam
— With Complete Solution
A 105-question objective assessment aligned with the WGU C170 Data
Management Applications competency blueprint for the 2026–2027
academic cycle. Coverage spans the relational model, ER modeling,
normalization (1NF–BCNF), DDL/DML, SQL querying, JOINs, subqueries,
aggregates, views, indexes, security, transactions, ACID, concurrency, and
data warehousing — with complete worked solutions, SQL syntax reasoning,
and step-by-step query analysis.
T O TA L Q U E S T I O N S SE CTIO N S F O R M AT
105 11 MCQ · A–D
30% recall · 50% application · 20% analysis
11 competency domains · SQL · ERD · Normalization · Transactions
· OLAP
Z .A I · I N F O R M AT I O N T E C H N O L O G Y E D U C AT I O N C OMP L ETE
SERIES S OL U TION
,WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
WGU C170 Objective Assessment
Data Management Applications · With Complete Solution · Latest
Total Questions 105
Total Sections 11
Format Multiple Choice (A–D), one correct answer per question
Cognitive Mix ~30% recall, ~50% application, ~20% analysis
Question Style ~70% application/scenario-based (SQL writing, design), ~30% direct recall
Coverage Relational model; ERD; normalization (1NF–BCNF); DDL/DML; SELECT; JOINs; subqueries;
aggregates; views/indexes; transactions/ACID; OLAP/NoSQL
Answer Format Correct answer marked with [CORRECT]; rationale with SQL syntax and complete solution guidance
Complete Solution Step-by-step reasoning reflecting WGU C170 objective assessment blueprint for 2026–2027
Section 1: Database Fundamentals and the Relational Model
Database Concepts, RDBMS, Tables, Keys, & Relationships (Q1–Q12)
Q1: A retail company currently stores customer data in a flat spreadsheet with columns for CustomerID,
Name, Orders (comma-separated list of order IDs), and OrderTotals (comma-separated list of dollar
amounts). When a customer asks 'how much did CustomerID 1001 spend in total?', the analyst must
manually split the comma-separated lists and sum the totals. Which fundamental problem does this design
exhibit, and what is the relational model solution?
A. The design exhibits the 'repeating group' problem (non-atomic values in a single column); the relational
model requires each cell to hold a single atomic value, and the proper solution is to decompose into two
tables: Customers(CustomerID, Name) and Orders(OrderID, CustomerID, OrderTotal) linked by a foreign
key [CORRECT]
B. The design exhibits slow file I/O; the solution is to compress the spreadsheet
C. The design lacks an index; the solution is to add an index on CustomerID
D. The design is correct because spreadsheets are valid databases
Correct Answer: A
Rationale: First Normal Form (1NF) requires that every column in a row hold a single atomic value — a cell cannot contain a
list, set, or repeating group. The 'repeating group' (comma-separated order IDs and totals) violates 1NF and forces procedural
manipulation to query. The relational model solution is decomposition: each customer's orders become rows in a separate
Orders table linked by CustomerID (foreign key). Complete solution: the Customers table holds one row per customer; the
Orders table holds one row per order, with CustomerID as a foreign key reference to Customers(CustomerID). This enables
standard SQL aggregation (SUM(OrderTotal) GROUP BY CustomerID).
Q2: Which of the following statements accurately distinguishes a database from a database management
system (DBMS)?
A. A database is the logical container of related data; a DBMS is the software system that creates, manages,
secures, and provides programmatic and interactive access to that database (e.g., MySQL, PostgreSQL,
Z.ai · Information Technology Education Series Page 1
,WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
Oracle, SQL Server) [CORRECT]
B. A database is the software; a DBMS is the data
C. A database and a DBMS are interchangeable terms for the same thing
D. A DBMS is a hardware device; a database is software
Correct Answer: A
Rationale: A database is a structured collection of related data (logically independent of any software). A DBMS (Database
Management System) is the software application that creates, manages, secures, and provides access to databases — it
implements the relational model, enforces constraints, processes SQL, manages transactions and concurrency, and provides
backup/recovery. Examples: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, SQLite. The data is the 'what'; the
DBMS is the 'how'. This distinction is foundational WGU C170 competency material.
Q3: In the relational model, what is the difference between a 'relation' and a 'tuple'?
A. A relation is the mathematical term for a table (an unordered set of tuples sharing the same attributes);
a tuple is the mathematical term for a single row (one set of attribute values for one entity instance)
[CORRECT]
B. A relation is a column; a tuple is a row
C. A relation is the schema; a tuple is the table
D. A relation is a query result; a tuple is the query
Correct Answer: A
Rationale: In relational theory (Codd, 1970), a relation is a set of tuples with the same heading (set of attributes) — the
abstract mathematical concept corresponding to a SQL 'table'. A tuple is an ordered set of attribute values — corresponding to
a SQL 'row'. An attribute is a named column with an associated domain (set of allowed values). Tables, rows, and columns
are SQL implementation terms; relations, tuples, and attributes are the theoretical terms. The distinction matters for
understanding relational algebra, normalization theory, and the mathematical foundations of SQL.
Q4: A primary key constraint uniquely identifies each row in a table and must satisfy two properties.
Which of the following correctly identifies both properties?
A. Uniqueness (no two rows can have the same primary key value) and minimality (no subset of the primary
key columns can be removed while still preserving uniqueness — i.e., no proper subset is itself unique)
[CORRECT]
B. Uniqueness and non-nullability of every column in the table
C. Indexability and foreign-key referenceability
D. Immutability (the value never changes) and confidentiality
Correct Answer: A
Rationale: A primary key must satisfy two properties: (1) uniqueness — no two rows may have the same primary key value(s);
(2) minimality — no proper subset of the primary key columns can be removed while still preserving uniqueness (i.e., the key is
the smallest set of columns that uniquely identifies a row). For a single-column primary key, minimality is automatic. For a
composite key (e.g., (OrderID, ProductID) in an order-line-items table), minimality requires that neither OrderID alone nor
ProductID alone uniquely identifies the row. A primary key also implies NOT NULL and UNIQUE constraints.
Q5: A table Employee(EmployeeID, DepartmentID, Name, Salary) has a foreign key DepartmentID
referencing Department(DepartmentID). An attempt to insert a row with DepartmentID = 999 fails with a
constraint violation because DepartmentID 999 does not exist in the Department table. Which integrity rule
has been enforced?
A. Referential integrity — the foreign key value in the child table (Employee.DepartmentID) must either
match an existing primary key value in the parent table (Department.DepartmentID) or be NULL (if the
Z.ai · Information Technology Education Series Page 2
, WGU C170 Objective Assessment · Latest · With Complete Solution Western Governors University · Data Management Applications
foreign key column allows NULLs) [CORRECT]
B. Domain integrity — the value must fall within the allowed data type
C. Entity integrity — the primary key must be unique and non-null
D. Transaction integrity — the insert must be atomic
Correct Answer: A
Rationale: Referential integrity requires that every foreign key value in a child table either matches an existing primary key
value in the parent (referenced) table or is NULL (if the FK column allows NULLs). Inserting Employee with DepartmentID =
999 (which does not exist in Department) violates referential integrity. Domain integrity (column data type and constraints),
entity integrity (primary key uniqueness and non-nullability), and transaction integrity (ACID) are separate concepts.
Referential integrity is enforced by foreign key constraints and is essential for maintaining the validity of relationships across
tables.
Q6: A table named Students has columns StudentID (the primary key), Email, and Phone. The database
designer wants to ensure that no two students can have the same Email value, while still allowing NULL
emails. Which constraint should be applied?
A. UNIQUE constraint on the Email column — UNIQUE allows at most one NULL value (in standard SQL)
and prevents duplicate non-NULL values; this is the correct choice for 'no duplicates but NULLs are
allowed' [CORRECT]
B. PRIMARY KEY constraint on the Email column
C. NOT NULL constraint on the Email column
D. CHECK constraint requiring Email to be non-empty
Correct Answer: A
Rationale: A UNIQUE constraint enforces uniqueness of column values but allows NULL values (standard SQL allows
multiple NULLs in a UNIQUE column, though some databases like SQL Server and Oracle allow only one NULL). PRIMARY
KEY implies both UNIQUE and NOT NULL — so a column that may legitimately be NULL cannot be a primary key. NOT
NULL alone does not prevent duplicates. A CHECK constraint cannot enforce uniqueness (it only checks per-row conditions).
For an email column where duplicates are forbidden but NULL is permitted (student hasn't provided email yet), UNIQUE is
the correct choice. Complete solution: `ALTER TABLE Students ADD CONSTRAINT uc_email UNIQUE (Email);`
Q7: Which of the following is a 'candidate key' in a database table?
A. Any minimal set of one or more columns that can uniquely identify a row — a table may have multiple
candidate keys, and one is chosen as the primary key; the others become alternate keys [CORRECT]
B. A key used only in foreign tables
C. A key generated by the database automatically (surrogate key)
D. A key that is a candidate for deletion
Correct Answer: A
Rationale: A candidate key is any minimal set of attributes that uniquely identifies each row in a relation (satisfies uniqueness
and minimality). A table can have multiple candidate keys; one is selected as the primary key, and the others become 'alternate
keys' (typically enforced with UNIQUE constraints). Example: In an Employees table, both EmployeeID (a surrogate) and
(GovernmentIDNumber, Country) might be candidate keys; EmployeeID is typically chosen as the primary key, and
GovernmentIDNumber+Country would be enforced with a UNIQUE constraint. Surrogate keys (auto-generated) are a special
case of candidate keys.
Q8: A composite primary key is required when:
A. No single column uniquely identifies a row, but a combination of two or more columns does (common in
junction/associative tables representing many-to-many relationships, such as OrderLineItems(OrderID,
Z.ai · Information Technology Education Series Page 3
