WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
WGU C170 PROJECT
Data Management Applications - Comprehensive Performance Assessment
With Complete Solution - 100 Verified Questions and Answers
This comprehensive exam aligns with the WGU C170 Data Management Applications Performance
Assessment for the 2026/2027 academic year. The 100 questions cover all project requirements and rubric
competencies: project scenario analysis, ERD design, normalization to 3NF, DDL implementation (CREATE
TABLE, ALTER TABLE, constraints), DML and data population, query development (SELECT, JOINs,
subqueries, aggregates, GROUP BY, HAVING), views, indexes, stored procedures, triggers, and
documentation/testing/submission requirements. Each rationale provides complete solution guidance with SQL
syntax rules, relational theory, normalization principles, and rubric alignment. 75% scenario-based application
questions and 25% direct recall mirror the actual C170 project workflow from requirements analysis to final
submission.
WGU C170 Project - Western Governors University Page 1
,WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Section 1: Project Requirements and Scenario Analysis (Business Rules, Data
Requirements, & Deliverable Expectations)
Questions 1-10
Q1: The WGU C170 performance assessment requires the student to design a database for a specific
scenario. Which deliverable serves as the FOUNDATION document that defines business rules, data
requirements, and report specifications before any SQL coding begins?
A. The scenario description and requirements document provided in the course materials [CORRECT]
B. The CREATE TABLE script directly
C. The final submission packet only
D. The SQL query output screenshots
Correct Answer: A
Rationale: WGU C170 expects students to FIRST analyze the provided scenario to extract entities, attributes, relationships,
and required reports before designing the database. The scenario document defines the business rules (e.g., 'a customer may
place many orders' implies a 1:M relationship) and data requirements (e.g., 'track customer name, address, phone' implies
specific attributes). Skipping this analysis leads to incorrect schemas that fail the rubric's 'Requirements' competency. SQL
coding follows ERD design and normalization.
Q2: In the C170 project scenario, the requirement states: 'Each order must belong to exactly one
customer, but a customer may place many orders over time.' Which relationship cardinality does this
business rule define?
A. Customer (1) to Order (M) — a one-to-many relationship with mandatory participation on the Order side
[CORRECT]
B. Customer (M) to Order (M) — many-to-many
C. Customer (1) to Order (1) — one-to-one
D. Customer (0) to Order (M) — optional on Customer side
Correct Answer: A
Rationale: The phrase 'each order must belong to exactly one customer' establishes mandatory participation on the Order side
(no Order without a Customer), and 'a customer may place many orders' creates a one-to-many (1:M) cardinality. In Crow's
Foot notation, this is drawn with a mandatory-one (||) symbol at the Customer end and a crow's foot (<) at the Order end. The
foreign key customer_id goes in the Order table, enforcing referential integrity.
Q3: A C170 student is analyzing the scenario and notices the requirement: 'A product can belong to
multiple categories, and each category can contain multiple products.' How should this M:N relationship
be modeled in the database design?
A. Create an associative (junction) entity such as Product_Category with its own primary key combining
product_id and category_id [CORRECT]
B. Place category_id in the Product table only
C. Place product_id in the Category table only
D. Add a categories column as a comma-separated string in Product
Correct Answer: A
Rationale: Many-to-many (M:N) relationships cannot be directly implemented in a relational database; they require an
associative (also called junction, linking, or composite) entity. The Product_Category table has a composite primary key
(product_id, category_id), each being a foreign key to its respective parent table. This resolves the M:N into two 1:M
relationships. Storing a comma-separated list violates 1NF and prevents efficient joins and constraint enforcement.
WGU C170 Project - Western Governors University Page 2
,WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Q4: The C170 project rubric requires the final schema to demonstrate 'data normalization to 3NF.' Which
statement best describes why the rubric emphasizes 3NF over 2NF or BCNF?
A. 3NF eliminates transitive dependencies (non-key attributes depending on other non-key attributes) in addition
to the partial dependencies removed by 2NF [CORRECT]
B. 3NF allows duplicate data across tables for faster querying
C. 3NF only requires atomic values in cells, which is the same as 1NF
D. 3NF requires every foreign key to be NOT NULL
Correct Answer: A
Rationale: Third Normal Form (3NF) builds on 1NF (atomic values, no repeating groups) and 2NF (no partial dependencies —
every non-key attribute depends on the entire primary key, relevant for composite keys). 3NF additionally eliminates transitive
dependencies, where a non-key attribute depends on another non-key attribute (e.g., zip_code → city → state in a Customer
table). The C170 rubric requires 3NF because it eliminates insertion, update, and deletion anomalies while preserving data
integrity.
Q5: A C170 student reads the requirement: 'The system must generate a monthly sales report showing
total revenue per product category, sorted by revenue descending.' Which deliverables must this report
generate in the final submission?
A. A SQL query using JOIN, SUM, GROUP BY, and ORDER BY; plus a screenshot of the output matching the
expected results [CORRECT]
B. Only the ERD showing the Product_Category relationship
C. An INSERT statement adding sample sales data
D. A CREATE VIEW statement alone without verification
Correct Answer: A
Rationale: The C170 rubric's 'Reporting' competency requires both the SQL query AND a screenshot of the executed output.
This specific report requires: JOIN between Product, Product_Category, Category, and Order_Item tables; SUM(quantity *
unit_price) AS revenue; GROUP BY category_name; ORDER BY revenue DESC. The rubric expects students to demonstrate
the query works against their sample data, producing verifiable results. Skipping the screenshot or using incorrect joins fails
the competency.
Q6: The C170 scenario specifies that 'the database must store employee information including name,
address, hire date, department, and manager.' When analyzing this requirement, which entity attributes
are derived (calculated) rather than stored directly?
A. Employee tenure (years of service) calculated from hire_date and CURRENT_DATE [CORRECT]
B. Employee name stored as a single VARCHAR column
C. Department stored as a foreign key to Department table
D. Address stored as a composite attribute
Correct Answer: A
Rationale: Derived attributes are calculated from other stored attributes rather than stored directly, to avoid redundancy and
update anomalies. Employee tenure (years of service) is derived from hire_date and the current date using functions like
YEAR(CURRENT_DATE) - YEAR(hire_date) or DATEDIFF. Storing derived values risks inconsistency. In an ERD, derived
attributes are indicated with a leading slash or asterisk (e.g., /years_of_service). However, in some BI scenarios, derived
columns may be precomputed for performance — but C170 expects normalized design.
WGU C170 Project - Western Governors University Page 3
, WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Q7: When extracting business rules for the C170 project, the scenario states: 'A customer cannot place an
order if their account status is not active.' How should this business rule be implemented in the database
design?
A. Implement as a CHECK constraint or application-layer validation that the customer's status is 'active' before
inserting an order [CORRECT]
B. Store the rule as a comment in the SQL file
C. Use a view to filter inactive customers
D. Ignore the rule because it complicates the design
Correct Answer: A
Rationale: Business rules can be enforced at three levels: (1) Database level — CHECK constraints, FOREIGN KEY
constraints, triggers; (2) Application level — code that validates before database operations; (3) Stored procedure level —
encapsulating logic. For this rule, a CHECK constraint (account_status IN ('active','suspended','closed')) plus an INSERT
trigger or stored procedure verifying the customer is active before order insertion. C170 expects students to identify business
rules and choose the appropriate enforcement mechanism.
Q8: The C170 project scenario describes a multi-product, multi-order, multi-customer e-commerce
system. The student identifies the following potential entities: Customer, Order, Order_Line, Product,
Category, Employee. Which of these is correctly classified as a WEAK entity (cannot exist without its
parent)?
A. Order_Line (depends on Order for its existence and identification) [CORRECT]
B. Customer (independent, has its own primary key)
C. Product (independent, has its own SKU)
D. Employee (independent, has its own employee_id)
Correct Answer: A
Rationale: A weak entity cannot exist without its identifying parent (strong) entity and typically has a partial key (discriminator)
that becomes unique only when combined with the parent's primary key. Order_Line (or Order_Detail) is a classic weak entity:
an order line item has no meaning without the Order it belongs to. In the ERD, weak entities are drawn with double-bordered
rectangles and identified by composite primary keys (order_id, line_number). The line_number is the partial key discriminator
within each order.
Q9: A C170 student must produce a 'data dictionary' as part of the deliverables. Which component MUST
be included for every column in the dictionary to satisfy the rubric?
A. Column name, data type, length/precision, constraint (PK/FK/NOT NULL/UNIQUE/CHECK/DEFAULT), and
description [CORRECT]
B. Column name only
C. Sample data values only
D. The CREATE TABLE statement only
Correct Answer: A
Rationale: A data dictionary (or metadata catalog) documents the schema for stakeholders and future maintainers. WGU
C170 rubric expects each column entry to include: name, data type with size/precision, key/constraint designation (PK, FK,
NOT NULL, UNIQUE, CHECK, DEFAULT), and a business description explaining the column's purpose. This supports the
'Documentation' competency. Missing any element (especially the description or constraints) loses rubric points. Sample data
values are useful but not required for every column.
WGU C170 Project - Western Governors University Page 4
WGU C170 PROJECT
Data Management Applications - Comprehensive Performance Assessment
With Complete Solution - 100 Verified Questions and Answers
This comprehensive exam aligns with the WGU C170 Data Management Applications Performance
Assessment for the 2026/2027 academic year. The 100 questions cover all project requirements and rubric
competencies: project scenario analysis, ERD design, normalization to 3NF, DDL implementation (CREATE
TABLE, ALTER TABLE, constraints), DML and data population, query development (SELECT, JOINs,
subqueries, aggregates, GROUP BY, HAVING), views, indexes, stored procedures, triggers, and
documentation/testing/submission requirements. Each rationale provides complete solution guidance with SQL
syntax rules, relational theory, normalization principles, and rubric alignment. 75% scenario-based application
questions and 25% direct recall mirror the actual C170 project workflow from requirements analysis to final
submission.
WGU C170 Project - Western Governors University Page 1
,WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Section 1: Project Requirements and Scenario Analysis (Business Rules, Data
Requirements, & Deliverable Expectations)
Questions 1-10
Q1: The WGU C170 performance assessment requires the student to design a database for a specific
scenario. Which deliverable serves as the FOUNDATION document that defines business rules, data
requirements, and report specifications before any SQL coding begins?
A. The scenario description and requirements document provided in the course materials [CORRECT]
B. The CREATE TABLE script directly
C. The final submission packet only
D. The SQL query output screenshots
Correct Answer: A
Rationale: WGU C170 expects students to FIRST analyze the provided scenario to extract entities, attributes, relationships,
and required reports before designing the database. The scenario document defines the business rules (e.g., 'a customer may
place many orders' implies a 1:M relationship) and data requirements (e.g., 'track customer name, address, phone' implies
specific attributes). Skipping this analysis leads to incorrect schemas that fail the rubric's 'Requirements' competency. SQL
coding follows ERD design and normalization.
Q2: In the C170 project scenario, the requirement states: 'Each order must belong to exactly one
customer, but a customer may place many orders over time.' Which relationship cardinality does this
business rule define?
A. Customer (1) to Order (M) — a one-to-many relationship with mandatory participation on the Order side
[CORRECT]
B. Customer (M) to Order (M) — many-to-many
C. Customer (1) to Order (1) — one-to-one
D. Customer (0) to Order (M) — optional on Customer side
Correct Answer: A
Rationale: The phrase 'each order must belong to exactly one customer' establishes mandatory participation on the Order side
(no Order without a Customer), and 'a customer may place many orders' creates a one-to-many (1:M) cardinality. In Crow's
Foot notation, this is drawn with a mandatory-one (||) symbol at the Customer end and a crow's foot (<) at the Order end. The
foreign key customer_id goes in the Order table, enforcing referential integrity.
Q3: A C170 student is analyzing the scenario and notices the requirement: 'A product can belong to
multiple categories, and each category can contain multiple products.' How should this M:N relationship
be modeled in the database design?
A. Create an associative (junction) entity such as Product_Category with its own primary key combining
product_id and category_id [CORRECT]
B. Place category_id in the Product table only
C. Place product_id in the Category table only
D. Add a categories column as a comma-separated string in Product
Correct Answer: A
Rationale: Many-to-many (M:N) relationships cannot be directly implemented in a relational database; they require an
associative (also called junction, linking, or composite) entity. The Product_Category table has a composite primary key
(product_id, category_id), each being a foreign key to its respective parent table. This resolves the M:N into two 1:M
relationships. Storing a comma-separated list violates 1NF and prevents efficient joins and constraint enforcement.
WGU C170 Project - Western Governors University Page 2
,WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Q4: The C170 project rubric requires the final schema to demonstrate 'data normalization to 3NF.' Which
statement best describes why the rubric emphasizes 3NF over 2NF or BCNF?
A. 3NF eliminates transitive dependencies (non-key attributes depending on other non-key attributes) in addition
to the partial dependencies removed by 2NF [CORRECT]
B. 3NF allows duplicate data across tables for faster querying
C. 3NF only requires atomic values in cells, which is the same as 1NF
D. 3NF requires every foreign key to be NOT NULL
Correct Answer: A
Rationale: Third Normal Form (3NF) builds on 1NF (atomic values, no repeating groups) and 2NF (no partial dependencies —
every non-key attribute depends on the entire primary key, relevant for composite keys). 3NF additionally eliminates transitive
dependencies, where a non-key attribute depends on another non-key attribute (e.g., zip_code → city → state in a Customer
table). The C170 rubric requires 3NF because it eliminates insertion, update, and deletion anomalies while preserving data
integrity.
Q5: A C170 student reads the requirement: 'The system must generate a monthly sales report showing
total revenue per product category, sorted by revenue descending.' Which deliverables must this report
generate in the final submission?
A. A SQL query using JOIN, SUM, GROUP BY, and ORDER BY; plus a screenshot of the output matching the
expected results [CORRECT]
B. Only the ERD showing the Product_Category relationship
C. An INSERT statement adding sample sales data
D. A CREATE VIEW statement alone without verification
Correct Answer: A
Rationale: The C170 rubric's 'Reporting' competency requires both the SQL query AND a screenshot of the executed output.
This specific report requires: JOIN between Product, Product_Category, Category, and Order_Item tables; SUM(quantity *
unit_price) AS revenue; GROUP BY category_name; ORDER BY revenue DESC. The rubric expects students to demonstrate
the query works against their sample data, producing verifiable results. Skipping the screenshot or using incorrect joins fails
the competency.
Q6: The C170 scenario specifies that 'the database must store employee information including name,
address, hire date, department, and manager.' When analyzing this requirement, which entity attributes
are derived (calculated) rather than stored directly?
A. Employee tenure (years of service) calculated from hire_date and CURRENT_DATE [CORRECT]
B. Employee name stored as a single VARCHAR column
C. Department stored as a foreign key to Department table
D. Address stored as a composite attribute
Correct Answer: A
Rationale: Derived attributes are calculated from other stored attributes rather than stored directly, to avoid redundancy and
update anomalies. Employee tenure (years of service) is derived from hire_date and the current date using functions like
YEAR(CURRENT_DATE) - YEAR(hire_date) or DATEDIFF. Storing derived values risks inconsistency. In an ERD, derived
attributes are indicated with a leading slash or asterisk (e.g., /years_of_service). However, in some BI scenarios, derived
columns may be precomputed for performance — but C170 expects normalized design.
WGU C170 Project - Western Governors University Page 3
, WGU C170 Data Management Applications - PROJECT | 100 Questions 2026/2027 | With Complete Solution
Q7: When extracting business rules for the C170 project, the scenario states: 'A customer cannot place an
order if their account status is not active.' How should this business rule be implemented in the database
design?
A. Implement as a CHECK constraint or application-layer validation that the customer's status is 'active' before
inserting an order [CORRECT]
B. Store the rule as a comment in the SQL file
C. Use a view to filter inactive customers
D. Ignore the rule because it complicates the design
Correct Answer: A
Rationale: Business rules can be enforced at three levels: (1) Database level — CHECK constraints, FOREIGN KEY
constraints, triggers; (2) Application level — code that validates before database operations; (3) Stored procedure level —
encapsulating logic. For this rule, a CHECK constraint (account_status IN ('active','suspended','closed')) plus an INSERT
trigger or stored procedure verifying the customer is active before order insertion. C170 expects students to identify business
rules and choose the appropriate enforcement mechanism.
Q8: The C170 project scenario describes a multi-product, multi-order, multi-customer e-commerce
system. The student identifies the following potential entities: Customer, Order, Order_Line, Product,
Category, Employee. Which of these is correctly classified as a WEAK entity (cannot exist without its
parent)?
A. Order_Line (depends on Order for its existence and identification) [CORRECT]
B. Customer (independent, has its own primary key)
C. Product (independent, has its own SKU)
D. Employee (independent, has its own employee_id)
Correct Answer: A
Rationale: A weak entity cannot exist without its identifying parent (strong) entity and typically has a partial key (discriminator)
that becomes unique only when combined with the parent's primary key. Order_Line (or Order_Detail) is a classic weak entity:
an order line item has no meaning without the Order it belongs to. In the ERD, weak entities are drawn with double-bordered
rectangles and identified by composite primary keys (order_id, line_number). The line_number is the partial key discriminator
within each order.
Q9: A C170 student must produce a 'data dictionary' as part of the deliverables. Which component MUST
be included for every column in the dictionary to satisfy the rubric?
A. Column name, data type, length/precision, constraint (PK/FK/NOT NULL/UNIQUE/CHECK/DEFAULT), and
description [CORRECT]
B. Column name only
C. Sample data values only
D. The CREATE TABLE statement only
Correct Answer: A
Rationale: A data dictionary (or metadata catalog) documents the schema for stakeholders and future maintainers. WGU
C170 rubric expects each column entry to include: name, data type with size/precision, key/constraint designation (PK, FK,
NOT NULL, UNIQUE, CHECK, DEFAULT), and a business description explaining the column's purpose. This supports the
'Documentation' competency. Missing any element (especially the description or constraints) loses rubric points. Sample data
values are useful but not required for every column.
WGU C170 Project - Western Governors University Page 4
