WGU D426: Data Management Foundations
Actual Comprehensive Final Exam (2026/2027) |
Grade A Benchmark & OA Mastery Guide
REFERENCE DATABASE SCHEMA: "TechSolutions Inc."
Use this schema for all questions unless otherwise specified.
TableCopy
Table Columns Constraints
EmployeeID INT, Name VARCHAR(100),
PK: EmployeeID; FK: DepartmentID →
EMPLO DepartmentID INT, Salary
DEPARTMENTS, ManagerID →
YEES DECIMAL(10,2), HireDate DATE,
EMPLOYEES (self-referencing)
ManagerID INT
DEPAR DepartmentID INT, DeptName
PK: DepartmentID; FK: LocationID →
TMENT VARCHAR(50), LocationID INT, Budget
LOCATIONS
S DECIMAL(12,2)
LOCATI LocationID INT, City VARCHAR(50), State
PK: LocationID
ONS CHAR(2), Country VARCHAR(50)
, ProjectID INT, ProjectName
VARCHAR(100), ClientID INT, Budget
PROJE
DECIMAL(12,2), StartDate DATE, PK: ProjectID; FK: ClientID → CLIENTS
CTS
EndDate DATE NULL, Status
VARCHAR(20)
ClientID INT, ClientName
CLIENT VARCHAR(100), Industry VARCHAR(50), PK: ClientID; CHECK: CreditRating IN
S ContactEmail VARCHAR(100), ('A','B','C','D')
CreditRating CHAR(1)
AssignmentID INT, EmployeeID INT,
PK: AssignmentID; FK: EmployeeID →
ASSIGN ProjectID INT, Role VARCHAR(50),
EMPLOYEES, ProjectID → PROJECTS;
MENTS HoursAssigned INT, HourlyRate
UNIQUE: (EmployeeID, ProjectID)
DECIMAL(8,2)
InvoiceID INT, ProjectID INT, Amount
INVOIC DECIMAL(12,2), IssuedDate DATE, PK: InvoiceID; FK: ProjectID →
ES PaidDate DATE NULL, PaymentMethod PROJECTS
VARCHAR(20)
Additional Notes: All foreign keys enforce ON DELETE RESTRICT and ON UPDATE
CASCADE. Status values in PROJECTS: 'Planning', 'Active', 'Completed', 'Cancelled'.
DOMAIN A: FOUNDATIONAL THEORY & DATA MODELING (Q1-20)
Q1 (Multiple Select) - Analysis & Evaluation:
Analyzing the TechSolutions schema, which business rules are logically enforced by the
defined foreign keys? (Select all that apply.)
, 1. An employee must be assigned to a valid department
2. A department must have at least one employee
3. A project must be associated with a valid client
4. An invoice can only be created for an existing project
5. An assignment must have both a valid employee and a valid project
6. A location must have at least one department assigned to it
Correct Answers: 1, 3, 4, 5
Verified Rationale: Foreign key constraints enforce referential integrity by ensuring that
values in the child table match existing primary key values in the parent table. This
guarantees that: employees reference valid departments (1); projects reference valid
clients (3); invoices reference valid projects (4); and assignments reference both valid
employees and projects (5). However, FK constraints do NOT enforce existence in the
reverse direction—departments may exist without employees (2), and locations may
exist without departments (6). These would require procedural constraints or triggers.
Q2 (Multiple Choice) - Complex Application:
The schema includes a self-referencing foreign key (ManagerID → EmployeeID) in
EMPLOYEES. What does this design pattern model?
A. A recursive relationship where an employee manages multiple projects
B. A hierarchical relationship where employees report to other employees
C. A many-to-many relationship between employees and departments
D. A temporal relationship tracking employee promotion history
Correct Answer: B
Verified Rationale: A self-referencing foreign key creates a recursive relationship within
a single entity. In this case, ManagerID references EmployeeID in the same table,
, establishing a hierarchy where each employee (except top-level) reports to a manager
who is also an employee. This models organizational reporting structures. The pattern
enables queries like "find all employees who report directly or indirectly to a specific
manager" using recursive CTEs. It does not model project management (A),
departmental relationships (C), or historical tracking (D).
Q3 (Multiple Choice) - Analysis & Evaluation:
The ASSIGNMENTS table has a composite unique constraint on (EmployeeID,
ProjectID). What is the primary purpose of this constraint?
A. To ensure an employee can only work on one project at a time
B. To prevent duplicate assignment records for the same employee-project combination
C. To create a composite primary key for the table
D. To enforce that every employee must have at least one assignment
Correct Answer: B
Verified Rationale: A UNIQUE constraint prevents duplicate values in specified columns,
ensuring data integrity by disallowing multiple rows with the same
EmployeeID-ProjectID pair. This prevents erroneous double-booking of an employee on
the same project with different roles or rates. It does not limit employees to single
projects (A—an employee can have multiple assignments to different projects). The
table already has a surrogate primary key (AssignmentID), so this is not the PK (C). It
does not enforce minimum participation (D—UNIQUE allows NULLs and doesn't require
existence).
Q4 (Multiple Select) - Synthesis & Creation:
Actual Comprehensive Final Exam (2026/2027) |
Grade A Benchmark & OA Mastery Guide
REFERENCE DATABASE SCHEMA: "TechSolutions Inc."
Use this schema for all questions unless otherwise specified.
TableCopy
Table Columns Constraints
EmployeeID INT, Name VARCHAR(100),
PK: EmployeeID; FK: DepartmentID →
EMPLO DepartmentID INT, Salary
DEPARTMENTS, ManagerID →
YEES DECIMAL(10,2), HireDate DATE,
EMPLOYEES (self-referencing)
ManagerID INT
DEPAR DepartmentID INT, DeptName
PK: DepartmentID; FK: LocationID →
TMENT VARCHAR(50), LocationID INT, Budget
LOCATIONS
S DECIMAL(12,2)
LOCATI LocationID INT, City VARCHAR(50), State
PK: LocationID
ONS CHAR(2), Country VARCHAR(50)
, ProjectID INT, ProjectName
VARCHAR(100), ClientID INT, Budget
PROJE
DECIMAL(12,2), StartDate DATE, PK: ProjectID; FK: ClientID → CLIENTS
CTS
EndDate DATE NULL, Status
VARCHAR(20)
ClientID INT, ClientName
CLIENT VARCHAR(100), Industry VARCHAR(50), PK: ClientID; CHECK: CreditRating IN
S ContactEmail VARCHAR(100), ('A','B','C','D')
CreditRating CHAR(1)
AssignmentID INT, EmployeeID INT,
PK: AssignmentID; FK: EmployeeID →
ASSIGN ProjectID INT, Role VARCHAR(50),
EMPLOYEES, ProjectID → PROJECTS;
MENTS HoursAssigned INT, HourlyRate
UNIQUE: (EmployeeID, ProjectID)
DECIMAL(8,2)
InvoiceID INT, ProjectID INT, Amount
INVOIC DECIMAL(12,2), IssuedDate DATE, PK: InvoiceID; FK: ProjectID →
ES PaidDate DATE NULL, PaymentMethod PROJECTS
VARCHAR(20)
Additional Notes: All foreign keys enforce ON DELETE RESTRICT and ON UPDATE
CASCADE. Status values in PROJECTS: 'Planning', 'Active', 'Completed', 'Cancelled'.
DOMAIN A: FOUNDATIONAL THEORY & DATA MODELING (Q1-20)
Q1 (Multiple Select) - Analysis & Evaluation:
Analyzing the TechSolutions schema, which business rules are logically enforced by the
defined foreign keys? (Select all that apply.)
, 1. An employee must be assigned to a valid department
2. A department must have at least one employee
3. A project must be associated with a valid client
4. An invoice can only be created for an existing project
5. An assignment must have both a valid employee and a valid project
6. A location must have at least one department assigned to it
Correct Answers: 1, 3, 4, 5
Verified Rationale: Foreign key constraints enforce referential integrity by ensuring that
values in the child table match existing primary key values in the parent table. This
guarantees that: employees reference valid departments (1); projects reference valid
clients (3); invoices reference valid projects (4); and assignments reference both valid
employees and projects (5). However, FK constraints do NOT enforce existence in the
reverse direction—departments may exist without employees (2), and locations may
exist without departments (6). These would require procedural constraints or triggers.
Q2 (Multiple Choice) - Complex Application:
The schema includes a self-referencing foreign key (ManagerID → EmployeeID) in
EMPLOYEES. What does this design pattern model?
A. A recursive relationship where an employee manages multiple projects
B. A hierarchical relationship where employees report to other employees
C. A many-to-many relationship between employees and departments
D. A temporal relationship tracking employee promotion history
Correct Answer: B
Verified Rationale: A self-referencing foreign key creates a recursive relationship within
a single entity. In this case, ManagerID references EmployeeID in the same table,
, establishing a hierarchy where each employee (except top-level) reports to a manager
who is also an employee. This models organizational reporting structures. The pattern
enables queries like "find all employees who report directly or indirectly to a specific
manager" using recursive CTEs. It does not model project management (A),
departmental relationships (C), or historical tracking (D).
Q3 (Multiple Choice) - Analysis & Evaluation:
The ASSIGNMENTS table has a composite unique constraint on (EmployeeID,
ProjectID). What is the primary purpose of this constraint?
A. To ensure an employee can only work on one project at a time
B. To prevent duplicate assignment records for the same employee-project combination
C. To create a composite primary key for the table
D. To enforce that every employee must have at least one assignment
Correct Answer: B
Verified Rationale: A UNIQUE constraint prevents duplicate values in specified columns,
ensuring data integrity by disallowing multiple rows with the same
EmployeeID-ProjectID pair. This prevents erroneous double-booking of an employee on
the same project with different roles or rates. It does not limit employees to single
projects (A—an employee can have multiple assignments to different projects). The
table already has a surrogate primary key (AssignmentID), so this is not the PK (C). It
does not enforce minimum participation (D—UNIQUE allows NULLs and doesn't require
existence).
Q4 (Multiple Select) - Synthesis & Creation:
