WGU C170 DATABASE APPLICATIONS LABS 2026/2027 |
Complete Solution Guide | SQL Labs | Pass Guaranteed - A+
Graded
Section 1: Entity-Relationship Diagram (ERD) Modeling &
Normalization (Q1-12)
Q1. A university database needs to track students and their dormitory assignments.
Each student lives in exactly one dorm room, and each dorm room houses exactly one
student. What type of relationship exists between Student and DormRoom?
A. One-to-Many (1:M)
B. Many-to-Many (M:N)
C. One-to-One (1:1)
D. Zero-to-Many (0:M)
C. One-to-One (1:1) [CORRECT]
Rationale: The scenario describes each student living in exactly one room and each
room housing exactly one student, which defines a 1:1 relationship. Option A (1:M)
would mean one room houses many students; Option B (M:N) would mean students can
live in multiple rooms and rooms can house multiple students; Option D is not a
standard cardinality notation.
Correct Answer: C
,Q2. In an ERD for an e-commerce system, which of the following correctly represents a
Many-to-Many (M:N) relationship between Customer and Product?
A. A single line connecting Customer and Product with "1" on both ends
B. A crow's foot on the Customer side and a single line on the Product side
C. A crow's foot on both ends of the relationship line
D. A dashed line with "0..1" on both ends
C. A crow's foot on both ends of the relationship line [CORRECT]
Rationale: In crow's foot notation, a crow's foot (three prongs) on both ends of a
relationship line indicates M:N cardinality. Option A represents 1:1; Option B represents
M:1; Option D represents an optional relationship, not M:N.
Correct Answer: C
Q3. Consider the following unnormalized table for a library system:
Table
BookI Title AuthorNam AuthorEmail Category CategoryLocatio
D e n
B001 Database Smith, J. Technolo Shelf A-12
Design m gy
, B002 SQL Basics Smith, J. Technolo Shelf A-12
m gy
B003 Data Science Jones, M. Science Shelf B-05
m
Which normal form is violated by this table design?
A. 1NF only
B. 2NF only
C. Both 1NF and 2NF
D. 3NF only
C. Both 1NF and 2NF [CORRECT]
Rationale: The table violates 1NF because AuthorName contains composite values (last
name, first name in one field). It violates 2NF because CategoryLocation is functionally
dependent on Category (a non-key attribute), creating a partial dependency. The table
must be in 1NF before 2NF can be assessed, and the repeating groups of author data
also indicate normalization issues.
Correct Answer: C
Q4. A database table contains the following attributes: OrderID, ProductID,
ProductName, ProductPrice, OrderDate, CustomerID, CustomerName. The primary key
is (OrderID, ProductID). Which normal form is violated?
A. 1NF
, B. 2NF
C. 3NF
D. BCNF
B. 2NF [CORRECT]
Rationale: ProductName and ProductPrice depend only on ProductID (part of the
composite key), and CustomerName depends only on CustomerID (a non-key attribute).
These are partial dependencies, which violate 2NF. 1NF is satisfied (atomic values); 3NF
cannot be violated if 2NF is already violated.
Correct Answer: B
Q5. Which of the following is a transitive dependency that would violate 3NF?
A. StudentID → StudentName (StudentID is the primary key)
B. StudentID → MajorID, MajorID → MajorName
C. (StudentID, CourseID) → Grade
D. OrderID → OrderDate, OrderID → CustomerID
B. StudentID → MajorID, MajorID → MajorName [CORRECT]
Rationale: A transitive dependency exists when A → B and B → C, therefore A → C
indirectly. Here, StudentID determines MajorID, and MajorID determines MajorName,
making MajorName transitively dependent on StudentID through MajorID. Options A, C,
and D show direct dependencies that do not violate 3NF.
Correct Answer: B
Complete Solution Guide | SQL Labs | Pass Guaranteed - A+
Graded
Section 1: Entity-Relationship Diagram (ERD) Modeling &
Normalization (Q1-12)
Q1. A university database needs to track students and their dormitory assignments.
Each student lives in exactly one dorm room, and each dorm room houses exactly one
student. What type of relationship exists between Student and DormRoom?
A. One-to-Many (1:M)
B. Many-to-Many (M:N)
C. One-to-One (1:1)
D. Zero-to-Many (0:M)
C. One-to-One (1:1) [CORRECT]
Rationale: The scenario describes each student living in exactly one room and each
room housing exactly one student, which defines a 1:1 relationship. Option A (1:M)
would mean one room houses many students; Option B (M:N) would mean students can
live in multiple rooms and rooms can house multiple students; Option D is not a
standard cardinality notation.
Correct Answer: C
,Q2. In an ERD for an e-commerce system, which of the following correctly represents a
Many-to-Many (M:N) relationship between Customer and Product?
A. A single line connecting Customer and Product with "1" on both ends
B. A crow's foot on the Customer side and a single line on the Product side
C. A crow's foot on both ends of the relationship line
D. A dashed line with "0..1" on both ends
C. A crow's foot on both ends of the relationship line [CORRECT]
Rationale: In crow's foot notation, a crow's foot (three prongs) on both ends of a
relationship line indicates M:N cardinality. Option A represents 1:1; Option B represents
M:1; Option D represents an optional relationship, not M:N.
Correct Answer: C
Q3. Consider the following unnormalized table for a library system:
Table
BookI Title AuthorNam AuthorEmail Category CategoryLocatio
D e n
B001 Database Smith, J. Technolo Shelf A-12
Design m gy
, B002 SQL Basics Smith, J. Technolo Shelf A-12
m gy
B003 Data Science Jones, M. Science Shelf B-05
m
Which normal form is violated by this table design?
A. 1NF only
B. 2NF only
C. Both 1NF and 2NF
D. 3NF only
C. Both 1NF and 2NF [CORRECT]
Rationale: The table violates 1NF because AuthorName contains composite values (last
name, first name in one field). It violates 2NF because CategoryLocation is functionally
dependent on Category (a non-key attribute), creating a partial dependency. The table
must be in 1NF before 2NF can be assessed, and the repeating groups of author data
also indicate normalization issues.
Correct Answer: C
Q4. A database table contains the following attributes: OrderID, ProductID,
ProductName, ProductPrice, OrderDate, CustomerID, CustomerName. The primary key
is (OrderID, ProductID). Which normal form is violated?
A. 1NF
, B. 2NF
C. 3NF
D. BCNF
B. 2NF [CORRECT]
Rationale: ProductName and ProductPrice depend only on ProductID (part of the
composite key), and CustomerName depends only on CustomerID (a non-key attribute).
These are partial dependencies, which violate 2NF. 1NF is satisfied (atomic values); 3NF
cannot be violated if 2NF is already violated.
Correct Answer: B
Q5. Which of the following is a transitive dependency that would violate 3NF?
A. StudentID → StudentName (StudentID is the primary key)
B. StudentID → MajorID, MajorID → MajorName
C. (StudentID, CourseID) → Grade
D. OrderID → OrderDate, OrderID → CustomerID
B. StudentID → MajorID, MajorID → MajorName [CORRECT]
Rationale: A transitive dependency exists when A → B and B → C, therefore A → C
indirectly. Here, StudentID determines MajorID, and MajorID determines MajorName,
making MajorName transitively dependent on StudentID through MajorID. Options A, C,
and D show direct dependencies that do not violate 3NF.
Correct Answer: B
