Practice & Assessment
Test your understanding of Normalization and Database Design
Multiple Choice Questions
5A table has a composite primary key (order_id, product_id). The column product_name depends only on product_id. Which normal form does this violate?
Which of the following is a transitive dependency?
What anomaly makes it impossible to add data for a new entity without having related data for another entity?
When should you consider intentionally denormalizing a database?
What is the primary goal of database normalization?
Coding Challenges
1Normalize an Unnormalized Schema
You are given the following single flat table representing a school system: school_records(student_id, student_name, student_email, course_id, course_name, instructor_id, instructor_name, instructor_email, department, grade, enrollment_date). (1) Identify all functional dependencies. (2) Show which normal forms are violated and why. (3) Decompose the table into a 3NF schema by writing CREATE TABLE statements for all resulting tables with appropriate primary keys, foreign keys, and constraints. (4) Write a query on the normalized schema that retrieves all students, their courses, instructor names, and grades for a specific department.
Mini Project
Database Refactoring: Normalize a Legacy Schema
You inherit a legacy e-commerce database with a single denormalized table: all_orders(order_id, order_date, customer_name, customer_email, customer_phone, customer_city, customer_country, product_code, product_name, product_brand, brand_country, category_name, parent_category, quantity, unit_price, discount_percent, shipping_address, shipping_city, payment_method, payment_status). Task: (1) List all functional dependencies you can identify. (2) Identify every 1NF, 2NF, and 3NF violation. (3) Design the fully normalized 3NF schema with CREATE TABLE statements. (4) Write a migration script that inserts data from the denormalized table into each normalized table using INSERT INTO ... SELECT. (5) Verify the migration by writing a query on the normalized schema that produces identical output to SELECT * FROM all_orders for any given order_id.