Wgu D427 Data Management Applications Quick Review Exam 2026 Practice Questions And Verified Answers With Rationales| Instant Download

, Question 1
A database designer is creating a schema for a university. The registrar's office
needs to track students, courses, and the sections of each course. Each course
can have many sections, but each section belongs to exactly one course. Each
student can enroll in many sections, and each section can have many students.
Which schema correctly implements this requirement while satisfying third
normal form (3NF)?
A. CREATE TABLE Enrollment (student_id INT, section_id INT, grade
CHAR(1), PRIMARY KEY (student_id, section_id)); CREATE TABLE
Section (section_id INT PRIMARY KEY, course_id INT, semester
VARCHAR(10)); CREATE TABLE Course (course_id INT PRIMARY
KEY, title VARCHAR(100)); CREATE TABLE Student (student_id INT
PRIMARY KEY, name VARCHAR(100));
B. CREATE TABLE Enrollment (student_id INT, section_id INT,
course_id INT, grade CHAR(1), PRIMARY KEY (student_id,
section_id)); CREATE TABLE Section (section_id INT PRIMARY KEY,
semester VARCHAR(10)); CREATE TABLE Course (course_id INT
PRIMARY KEY, title VARCHAR(100)); CREATE TABLE Student
(student_id INT PRIMARY KEY, name VARCHAR(100));
C. CREATE TABLE Enrollment (student_id INT, section_id INT, grade
CHAR(1), PRIMARY KEY (student_id, section_id)); CREATE TABLE
Section (section_id INT PRIMARY KEY, course_id INT, semester
VARCHAR(10), course_title VARCHAR(100)); CREATE TABLE
Course (course_id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE Student (student_id INT PRIMARY KEY, name
VARCHAR(100));
D. CREATE TABLE Enrollment (student_id INT, section_id INT, grade
CHAR(1), PRIMARY KEY (student_id, section_id)); CREATE TABLE
Section (section_id INT PRIMARY KEY, course_id INT, semester
VARCHAR(10)); CREATE TABLE Course (course_id INT PRIMARY
KEY, title VARCHAR(100)); CREATE TABLE Student (student_id INT


Page 2

, PRIMARY KEY, name VARCHAR(100), section_id INT);


Correct Answer: A - CREATE TABLE Enrollment (student_id
INT, section_id INT, grade CHAR(1), PRIMARY KEY
(student_id, section_id)); CREATE TABLE Section (section_id
INT PRIMARY KEY, course_id INT, semester VARCHAR(10));
CREATE TABLE Course (course_id INT PRIMARY KEY, title
VARCHAR(100)); CREATE TABLE Student (student_id INT
PRIMARY KEY, name VARCHAR(100));


RATIONALE
Option A correctly separates entities into normalized tables:
Enrollment is an associative entity with a composite primary key,
Section links to Course via a foreign key, and no transitive
dependencies exist. Option B redundantly stores course_id in
Enrollment, violating 3NF because course_id depends on section_id,
not directly on the Enrollment key. Option C duplicates course_title in
Section, creating a transitive dependency, and Option D incorrectly
places section_id in Student, which would limit a student to one
section.

Question 2
Given the following table structures: Orders(order_id, customer_id, order_date,
total_amount) and Customers(customer_id, customer_name, city). Which SQL
query correctly returns each customer's name and the total amount of their
orders for the year 2025, including customers who placed no orders in 2025
(displaying 0 for their total)?
A. SELECT c.customer_name, COALESCE(SUM(o.total_amount), 0)
AS total FROM Customers c LEFT JOIN Orders o ON c.customer_id =
o.customer_id AND o.order_date >= '2025-01-01' AND o.order_date <
'2026-01-01' GROUP BY c.customer_name;
B. SELECT c.customer_name, SUM(o.total_amount) AS total FROM
Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id


Page 3

, WHERE o.order_date BETWEEN '2025-01-01' AND '2025-12-31'

GROUP BY c.customer_name;

C. SELECT c.customer_name, COALESCE(SUM(o.total_amount), 0)
AS total FROM Customers c INNER JOIN Orders o ON c.customer_id =
o.customer_id WHERE o.order_date BETWEEN '2025-01-01' AND
'2025-12-31' GROUP BY c.customer_name;
D. SELECT c.customer_name, COALESCE(SUM(o.total_amount), 0)
AS total FROM Customers c RIGHT JOIN Orders o ON c.customer_id =
o.customer_id AND o.order_date >= '2025-01-01' AND o.order_date <
'2026-01-01' GROUP BY c.customer_name;
Correct Answer: A - SELECT c.customer_name,
COALESCE(SUM(o.total_amount), 0) AS total FROM
Customers c LEFT JOIN Orders o ON c.customer_id =
o.customer_id AND o.order_date >= '2025-01-01' AND
o.order_date < '2026-01-01' GROUP BY c.customer_name;


RATIONALE
Option A uses a LEFT JOIN with the date condition in the ON clause,
ensuring all customers are included and those without 2025 orders get
NULL, which COALESCE converts to 0. Option B places the date
filter in WHERE, which filters out customers with no 2025 orders,
effectively turning the LEFT JOIN into an INNER JOIN. Option C
uses INNER JOIN, excluding customers with no orders. Option D
uses RIGHT JOIN, which returns all orders but only matching
customers, potentially missing customers with no orders.

Question 3
Consider a table with columns (A, B, C, D) where the functional dependencies
are: A -> B, B -> C, and C -> D. The table is currently in 1NF. To achieve 3NF,
which decomposition is correct?
A. R1(A, B), R2(B, C), R3(C, D)
B. R1(A, B, C), R2(C, D)



Page 4