WGU C170 DATABASE APPLICATIONS LABS COMPLETE SOLUTION GUIDE 2026/2027 | MySQL Workbench | Verified Lab Solutions | Pass Guaranteed - A+ Graded

WGU C170 DATABASE APPLICATIONS LABS COMPLETE
SOLUTION GUIDE 2026/2027 | MySQL Workbench | Verified
Lab Solutions | Pass Guaranteed - A+ Graded
Course Alignment: Western Governors University C170 - Data Management Applications

Last Updated: March 2026

MySQL Version: 8.0+ Compatible

Tools: MySQL Workbench 8.0 CE



Lab 1: Database Design & Normalization (1NF-3NF)

Task: Normalize an unorganized dataset to Third Normal Form (3NF)

Scenario: You have been given a flat file containing pizza order data with the following
attributes: OrderID, CustomerName, CustomerPhone, OrderDate, Item1, Item1Price,
Item1Qty, Item2, Item2Price, Item2Qty, TotalAmount, EmployeeName, EmployeeID.

Step 1: Identify 1NF Violations

●​ Repeating Groups: Item1/Item2 indicate multiple items per order (violates
atomicity)
●​ Multi-valued Attributes: CustomerPhone may contain multiple numbers
●​ No Primary Key: OrderID alone cannot uniquely identify rows due to repeating
items

Step 2: Apply 1NF (Atomic Values & No Repeating Groups)

plainCopy

Original Table Structure (UNF):
Orders(OrderID, CustomerName, CustomerPhone, OrderDate,
Item1, Item1Price, Item1Qty, Item2, Item2Price, Item2Qty,
TotalAmount, EmployeeName, EmployeeID)

,1NF Solution:
Separate into two tables to eliminate repeating groups:


Step 3: Apply 2NF (Remove Partial Dependencies)

Analysis:

●​ OrderID determines OrderDate, CustomerName, CustomerPhone, TotalAmount,
EmployeeID, EmployeeName
●​ ItemID determines ItemName, ItemPrice (if stored separately)
●​ Quantity depends on BOTH OrderID AND Item (composite key needed)

Step 4: Apply 3NF (Remove Transitive Dependencies)

Transitive Dependencies Found:

●​ OrderID → EmployeeID → EmployeeName (EmployeeName depends on
EmployeeID, not directly on OrderID)
●​ OrderID → CustomerName, CustomerPhone (assuming CustomerID exists
conceptually)

[COMPLETE SOLUTION]

sqlCopy

-- ============================================
-- LAB 1: FULLY NORMALIZED SCHEMA (3NF)
-- ============================================

-- Table: Customers (3NF: No transitive dependencies)
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY AUTO_INCREMENT,
CustomerName VARCHAR(100) NOT NULL,
CustomerPhone VARCHAR(20),
CreatedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: Employees (3NF: Isolated employee attributes)
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
EmployeeName VARCHAR(100) NOT NULL,

, EmployeeRole VARCHAR(50),
HireDate DATE
);

-- Table: Orders (3NF: Only direct dependencies on OrderID)
CREATE TABLE Orders (
OrderID INT PRIMARY KEY AUTO_INCREMENT,
CustomerID INT NOT NULL,
EmployeeID INT NOT NULL,
OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
TotalAmount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

-- Table: MenuItems (3NF: Item attributes depend only on ItemID)
CREATE TABLE MenuItems (
ItemID INT PRIMARY KEY AUTO_INCREMENT,
ItemName VARCHAR(100) NOT NULL,
ItemPrice DECIMAL(8,2) NOT NULL,
Category VARCHAR(50)
);

-- Table: OrderDetails (3NF: Composite key, no partial dependencies)
CREATE TABLE OrderDetails (
OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
OrderID INT NOT NULL,
ItemID INT NOT NULL,
Quantity INT NOT NULL CHECK (Quantity > 0),
UnitPrice DECIMAL(8,2) NOT NULL, -- Snapshot price at time of order
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
FOREIGN KEY (ItemID) REFERENCES MenuItems(ItemID),
UNIQUE KEY UK_OrderItem (OrderID, ItemID) -- Prevent duplicate items
per order
);


Rationale:
This normalization eliminates all redundancy and anomalies. The OrderDetails table

uses a composite relationship to handle the many-to-many relationship between Orders

and Items. By storing UnitPrice in OrderDetails, we maintain historical pricing

integrity (price at time of order vs. current menu price). This follows WGU C170's

, emphasis on functional dependency analysis and satisfies the Boyce-Codd Normal
Form (BCNF) requirements commonly tested in assessments.



Lab 2: Entity-Relationship Diagram (ERD) Creation

Task: Create an ERD for PizzaOrderingDB showing entities, attributes, relationships,
cardinality, and modality

Step 1: Identify Entities & Business Rules

●​ Customers: Place orders (1:M relationship with Orders)
●​ Employees: Take orders (1:M relationship with Orders)
●​ Orders: Contain multiple items (M:N relationship with MenuItems, resolved
through OrderDetails)
●​ MenuItems: Can appear in many orders
●​ OrderDetails: Associative entity resolving M:N relationship

Step 2: Define Relationships with Cardinality & Modality

Table


Cardin Modalit
Relationship Business Rule
ality y



Customers to (1,1) to A customer may have zero or many orders; every
1:M
Orders (0,N) order must have exactly one customer



Employees to (1,1) to An employee may take zero or many orders; every
1:M
Orders (0,N) order must have exactly one employee