Practice & Assessment
Test your understanding of Normalization: Removing Redundancy
Multiple Choice Questions
6A table stores phone1, phone2, phone3 columns for each customer. Which normal form does this violate?
An order_items table has PK (order_id, product_id). The product_name column is present and depends only on product_id. Which normal form is violated?
A table has columns: emp_id (PK), dept_id, dept_name. dept_name depends on dept_id, not emp_id. Which normal form is violated?
When is denormalization a reasonable design choice?
Which action correctly brings a multi-value cell into 1NF?
Which statement about 3NF is accurate?
Coding Challenges
1Normalize a Flat Sales Table to 3NF
You are given this flat table DDL: CREATE TABLE sales (sale_id INT, customer_name VARCHAR(100), customer_email VARCHAR(255), product_id INT, product_name VARCHAR(100), category_name VARCHAR(100), quantity INT, sale_date DATE). Identify all 1NF, 2NF, and 3NF violations. Rewrite the schema as a set of normalized CREATE TABLE statements with appropriate PKs and FKs. Assume sale_id is the PK. There are no composite keys initially. Time estimate: 25 minutes.
Mini Project
Normalize a Hospital Flat File
A hospital exports data as one flat CSV with columns: patient_id, patient_name, dob, doctor_id, doctor_name, doctor_specialization, appointment_date, appointment_status, diagnosis_code, diagnosis_description. Identify all normalization violations, state which normal form each violates and why, then produce a fully normalized SQL schema (CREATE TABLE statements with PKs, FKs, and NOT NULL constraints) that reaches 3NF. Include at least five tables.