WGU C170 PRE-ASSESSMENT FJO1 PFJO 2026/2027 | Data
Management Applications Complete Solution Guide |
Detailed Rationales & Concept Reinforcement | Pass
Guaranteed - A+ Graded
Domain 1: Data Architecture and Design (18 Questions)
Q1: A database designer is normalizing a table containing student enrollment data. The
table currently has the following attributes: StudentID, StudentName, CourseID,
CourseName, InstructorID, InstructorName, and Grade. Assuming StudentID and
CourseID together form the primary key, which normal form is violated if
InstructorName depends on InstructorID rather than the full primary key?
A. First Normal Form (1NF)
B. Second Normal Form (2NF) [CORRECT]
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Rationale: This scenario describes a violation of Second Normal Form (2NF). 2NF
requires that the table is in 1NF and that all non-key attributes are fully functionally
dependent on the entire primary key. Since the primary key is composite (StudentID,
CourseID), any attribute that depends on only part of the key (in this case,
InstructorName depends on InstructorID, which depends only on CourseID) creates a
partial dependency, violating 2NF. 1NF is already satisfied as the table has atomic
values. 3NF deals with transitive dependencies (non-key attributes depending on other
non-key attributes). BCNF is a stricter version of 3NF requiring that every determinant is
a candidate key. The correct solution would be to split this into multiple tables: Student
(StudentID, StudentName), Course (CourseID, CourseName, InstructorID), and Instructor
(InstructorID, InstructorName), with an Enrollment junction table connecting StudentID,
CourseID, and Grade.
,Q2: A retail company maintains a customer database where each customer has a
unique CustomerID. The database stores CustomerID, CustomerName, Email, Phone,
and Address. The Email column occasionally contains multiple email addresses
separated by semicolons for customers who provided multiple contact methods. Which
normal form is violated?
A. 1NF [CORRECT]
B. 2NF
C. 3NF
D. No normal form is violated
Rationale: First Normal Form (1NF) requires that all attributes contain only atomic
(indivisible) values and that there are no repeating groups. Storing multiple email
addresses in a single column separated by semicolons violates atomicity—this is a
classic 1NF violation. The correct design would create a separate Email table with a
foreign key to Customer, allowing one-to-many relationship between customers and
email addresses. 2NF and 3NF cannot be evaluated until 1NF is satisfied. This is a
common student error: attempting to apply higher normal forms while ignoring the
foundational requirement of atomic values.
Q3: A university database tracks student majors. The table contains: StudentID,
StudentName, MajorID, MajorName, DepartmentID, and DepartmentName. StudentID is
the primary key. MajorID determines MajorName, and DepartmentID determines
DepartmentName. MajorID determines DepartmentID. Which normal forms are
violated? (Select TWO)
A. 1NF
B. 2NF [CORRECT]
C. 3NF [CORRECT]
D. BCNF
,Rationale: This table violates both 2NF and 3NF. It violates 2NF because MajorName
depends on MajorID, but MajorID is not the primary key (StudentID is). Since MajorID is
not part of the primary key, this creates a partial dependency if we consider that Major
information should depend on the whole key. More directly, it violates 3NF because
DepartmentName is transitively dependent on StudentID: StudentID → MajorID →
DepartmentID → DepartmentName. A non-key attribute (DepartmentName) depends on
another non-key attribute (DepartmentID), which depends on another non-key attribute
(MajorID). The correct decomposition would be: Student (StudentID, StudentName,
MajorID), Major (MajorID, MajorName, DepartmentID), and Department (DepartmentID,
DepartmentName).
Q4: Which of the following scenarios BEST justifies intentional denormalization of a
database?
A. Reducing storage space requirements
B. Improving read performance for frequently accessed reports [CORRECT]
C. Ensuring data integrity through constraints
D. Simplifying the initial database design process
Rationale: Denormalization is the intentional introduction of redundancy to improve
performance, specifically read performance for reporting and analytical queries. While
denormalization increases storage space (eliminating A), its primary purpose is to
reduce the number of joins required for complex queries, thereby improving response
time. Option C is incorrect because denormalization actually complicates data
integrity—redundant data requires additional maintenance to ensure consistency.
Option D is incorrect because while denormalized schemas may appear simpler, they
introduce long-term maintenance challenges. The key WGU C170 concept:
denormalization is a performance optimization technique for read-heavy workloads, not
a design shortcut.
, Q5: A database designer is creating a schema for an e-commerce application. The
Orders table contains: OrderID (PK), CustomerID (FK), OrderDate, and TotalAmount. The
OrderDetails table contains: OrderID (FK), ProductID (FK), Quantity, and UnitPrice. Which
statement accurately describes the relationship between Orders and OrderDetails?
A. One-to-One relationship
B. One-to-Many relationship [CORRECT]
C. Many-to-Many relationship
D. Recursive relationship
Rationale: This is a classic One-to-Many relationship. One Order can have many
OrderDetails (line items), but each OrderDetail belongs to exactly one Order. The OrderID
foreign key in OrderDetails establishes this relationship. A One-to-One relationship
would require each order to have exactly one detail record. A Many-to-Many relationship
would require a junction table between two independent entities. A recursive
relationship would involve a table relating to itself. This parent-child pattern (Orders as
parent, OrderDetails as child) is fundamental in transactional database design and
appears frequently in WGU assessments.
Q6: In Crow's Foot notation, which symbol combination indicates that an entity
participation in a relationship is mandatory and can be associated with multiple
instances of the other entity?
A. Single vertical bar (|) and three-pronged crow's foot
B. Double vertical bar (||) and single line
C. Circle (O) and three-pronged crow's foot
D. Single vertical bar (|) near the entity and three-pronged crow's foot near the other
entity [CORRECT]
Rationale: In Crow's Foot notation: the symbol nearest the entity describes its cardinality
(maximum), while the symbol farthest describes its ordinality (minimum/participation).
Management Applications Complete Solution Guide |
Detailed Rationales & Concept Reinforcement | Pass
Guaranteed - A+ Graded
Domain 1: Data Architecture and Design (18 Questions)
Q1: A database designer is normalizing a table containing student enrollment data. The
table currently has the following attributes: StudentID, StudentName, CourseID,
CourseName, InstructorID, InstructorName, and Grade. Assuming StudentID and
CourseID together form the primary key, which normal form is violated if
InstructorName depends on InstructorID rather than the full primary key?
A. First Normal Form (1NF)
B. Second Normal Form (2NF) [CORRECT]
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Rationale: This scenario describes a violation of Second Normal Form (2NF). 2NF
requires that the table is in 1NF and that all non-key attributes are fully functionally
dependent on the entire primary key. Since the primary key is composite (StudentID,
CourseID), any attribute that depends on only part of the key (in this case,
InstructorName depends on InstructorID, which depends only on CourseID) creates a
partial dependency, violating 2NF. 1NF is already satisfied as the table has atomic
values. 3NF deals with transitive dependencies (non-key attributes depending on other
non-key attributes). BCNF is a stricter version of 3NF requiring that every determinant is
a candidate key. The correct solution would be to split this into multiple tables: Student
(StudentID, StudentName), Course (CourseID, CourseName, InstructorID), and Instructor
(InstructorID, InstructorName), with an Enrollment junction table connecting StudentID,
CourseID, and Grade.
,Q2: A retail company maintains a customer database where each customer has a
unique CustomerID. The database stores CustomerID, CustomerName, Email, Phone,
and Address. The Email column occasionally contains multiple email addresses
separated by semicolons for customers who provided multiple contact methods. Which
normal form is violated?
A. 1NF [CORRECT]
B. 2NF
C. 3NF
D. No normal form is violated
Rationale: First Normal Form (1NF) requires that all attributes contain only atomic
(indivisible) values and that there are no repeating groups. Storing multiple email
addresses in a single column separated by semicolons violates atomicity—this is a
classic 1NF violation. The correct design would create a separate Email table with a
foreign key to Customer, allowing one-to-many relationship between customers and
email addresses. 2NF and 3NF cannot be evaluated until 1NF is satisfied. This is a
common student error: attempting to apply higher normal forms while ignoring the
foundational requirement of atomic values.
Q3: A university database tracks student majors. The table contains: StudentID,
StudentName, MajorID, MajorName, DepartmentID, and DepartmentName. StudentID is
the primary key. MajorID determines MajorName, and DepartmentID determines
DepartmentName. MajorID determines DepartmentID. Which normal forms are
violated? (Select TWO)
A. 1NF
B. 2NF [CORRECT]
C. 3NF [CORRECT]
D. BCNF
,Rationale: This table violates both 2NF and 3NF. It violates 2NF because MajorName
depends on MajorID, but MajorID is not the primary key (StudentID is). Since MajorID is
not part of the primary key, this creates a partial dependency if we consider that Major
information should depend on the whole key. More directly, it violates 3NF because
DepartmentName is transitively dependent on StudentID: StudentID → MajorID →
DepartmentID → DepartmentName. A non-key attribute (DepartmentName) depends on
another non-key attribute (DepartmentID), which depends on another non-key attribute
(MajorID). The correct decomposition would be: Student (StudentID, StudentName,
MajorID), Major (MajorID, MajorName, DepartmentID), and Department (DepartmentID,
DepartmentName).
Q4: Which of the following scenarios BEST justifies intentional denormalization of a
database?
A. Reducing storage space requirements
B. Improving read performance for frequently accessed reports [CORRECT]
C. Ensuring data integrity through constraints
D. Simplifying the initial database design process
Rationale: Denormalization is the intentional introduction of redundancy to improve
performance, specifically read performance for reporting and analytical queries. While
denormalization increases storage space (eliminating A), its primary purpose is to
reduce the number of joins required for complex queries, thereby improving response
time. Option C is incorrect because denormalization actually complicates data
integrity—redundant data requires additional maintenance to ensure consistency.
Option D is incorrect because while denormalized schemas may appear simpler, they
introduce long-term maintenance challenges. The key WGU C170 concept:
denormalization is a performance optimization technique for read-heavy workloads, not
a design shortcut.
, Q5: A database designer is creating a schema for an e-commerce application. The
Orders table contains: OrderID (PK), CustomerID (FK), OrderDate, and TotalAmount. The
OrderDetails table contains: OrderID (FK), ProductID (FK), Quantity, and UnitPrice. Which
statement accurately describes the relationship between Orders and OrderDetails?
A. One-to-One relationship
B. One-to-Many relationship [CORRECT]
C. Many-to-Many relationship
D. Recursive relationship
Rationale: This is a classic One-to-Many relationship. One Order can have many
OrderDetails (line items), but each OrderDetail belongs to exactly one Order. The OrderID
foreign key in OrderDetails establishes this relationship. A One-to-One relationship
would require each order to have exactly one detail record. A Many-to-Many relationship
would require a junction table between two independent entities. A recursive
relationship would involve a table relating to itself. This parent-child pattern (Orders as
parent, OrderDetails as child) is fundamental in transactional database design and
appears frequently in WGU assessments.
Q6: In Crow's Foot notation, which symbol combination indicates that an entity
participation in a relationship is mandatory and can be associated with multiple
instances of the other entity?
A. Single vertical bar (|) and three-pronged crow's foot
B. Double vertical bar (||) and single line
C. Circle (O) and three-pronged crow's foot
D. Single vertical bar (|) near the entity and three-pronged crow's foot near the other
entity [CORRECT]
Rationale: In Crow's Foot notation: the symbol nearest the entity describes its cardinality
(maximum), while the symbol farthest describes its ordinality (minimum/participation).
