Why normalization matters in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Identify the problem caused by lack of normalization

Consider a table storing customer orders where customer details are repeated in every order row. What issue does this cause?

SQL

CREATE TABLE Orders (
  OrderID INT,
  CustomerName VARCHAR(100),
  CustomerAddress VARCHAR(200),
  Product VARCHAR(100),
  Quantity INT
);

INSERT INTO Orders VALUES
(1, 'Alice', '123 Maple St', 'Book', 2),
(2, 'Alice', '123 Maple St', 'Pen', 5),
(3, 'Bob', '456 Oak St', 'Notebook', 1);

A Prevents data from being updated

B Faster queries because all data is in one table

C No issues; this is the best design

D Data redundancy leading to wasted storage and potential inconsistencies

💡 Hint

Think about what happens if Alice changes her address.

Repeating customer details in every order causes data redundancy. If the address changes, it must be updated in many rows, risking inconsistencies.

🧠 Conceptual

intermediate

Purpose of normalization in databases

What is the main goal of normalization in database design?

A To organize data to reduce redundancy and improve data integrity

B To make tables as large as possible for performance

C To encrypt data for security

D To store all data in a single table for simplicity

💡 Hint

Normalization helps keep data consistent and avoids repetition.

Normalization organizes data into tables to minimize duplication and ensure data accuracy.

📝 Syntax

advanced

Identify the correct SQL to create normalized tables

Which SQL option correctly splits customer and order data into two normalized tables?

A

CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(100), Address VARCHAR(200));
CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerID INT, Product VARCHAR(100), Quantity INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

B CREATE TABLE Orders (OrderID INT PRIMARY KEY, CustomerName VARCHAR(100), CustomerAddress VARCHAR(200), Product VARCHAR(100), Quantity INT);

C CREATE TABLE CustomersOrders (CustomerID INT, Name VARCHAR(100), Address VARCHAR(200), OrderID INT, Product VARCHAR(100), Quantity INT);

D

CREATE TABLE Customers (CustomerID INT, Name VARCHAR(100));
CREATE TABLE Orders (OrderID INT, Product VARCHAR(100), Quantity INT);

💡 Hint

Look for separate tables with a foreign key linking orders to customers.

Option A creates two tables with a foreign key, properly normalizing customer and order data.

❓ optimization

advanced

Effect of normalization on database updates

How does normalization affect the process of updating customer address information?

A It requires updating every order row with the new address

B It makes updates slower because data is spread across many tables

C It simplifies updates by storing the address in one place, reducing errors

D It prevents any updates to customer information

💡 Hint

Think about how many places you must change the address in a normalized design.

Normalization stores customer data once, so updating the address happens in one place, reducing errors and effort.

🔧 Debug

expert

Detect anomaly caused by denormalized design

Given this denormalized table, what anomaly might occur if a customer changes their phone number?

Orders
OrderID | CustomerName | PhoneNumber | Product
1       | John Doe     | 555-1234    | Laptop
2       | John Doe     | 555-1234    | Mouse
3       | Jane Smith   | 555-5678    | Keyboard

A Faster retrieval of phone numbers for orders

B Inconsistent phone numbers if some rows are not updated

C No anomalies; phone number is always correct

D Phone numbers cannot be stored in this table

💡 Hint

What happens if John Doe's phone number changes but only one order row is updated?

Because the phone number is repeated in multiple rows, updating only some rows causes inconsistent data.

Practice

1. Why is normalization important in a database?

easy

A. It automatically creates backups of the database.

B. It makes the database run faster by using more memory.

C. It allows storing data in one big table without keys.

D. It reduces data duplication and improves data integrity.

Solution

1. Step 1: Understand what normalization does

   Normalization organizes data to reduce repetition and avoid errors.

2. Step 2: Identify the main benefit

   Reducing duplication helps keep data accurate and easier to update.

3. Final Answer:

   It reduces data duplication and improves data integrity. -> Option D

4. Quick Check:

   Normalization = less duplication [OK]

Hint: Normalization means less repeated data and fewer errors [OK]

Common Mistakes:

• Thinking normalization speeds up all queries
• Believing normalization stores everything in one table
• Confusing normalization with backup processes

2. Which of the following is the correct way to represent a normalized table relationship?

easy

A. Separate tables for customers and orders linked by a customer ID.

B. One table with repeated customer addresses for each order.

C. All data combined in a single table without keys.

D. Storing all orders in a text file outside the database.

Solution

1. Step 1: Recall normalization structure

   Normalization splits data into related tables connected by keys.

2. Step 2: Identify correct table design

   Separate tables for customers and orders linked by customer ID avoid duplication.

3. Final Answer:

   Separate tables for customers and orders linked by a customer ID. -> Option A

4. Quick Check:

   Normalized tables use keys to link data [OK]

Hint: Normalized tables use keys to connect related data [OK]

Common Mistakes:

• Repeating data in one big table
• Ignoring foreign keys between tables
• Storing data outside the database

3. Given two tables:
Customers(customer_id, name, address)
Orders(order_id, customer_id, product)
What happens if you update a customer's address in the Customers table?

medium

A. The address updates in all related orders automatically.

B. The address in Orders table also changes because it stores addresses.

C. Only the address in the Customers table changes; orders remain linked by customer_id.

D. The database throws an error because address is duplicated.

Solution

1. Step 1: Understand table roles

   Customers table stores addresses; Orders table links by customer_id only.

2. Step 2: Effect of updating address

   Updating address changes it only in Customers; Orders remain linked by customer_id without address duplication.

3. Final Answer:

   Only the address in the Customers table changes; orders remain linked by customer_id. -> Option C

4. Quick Check:

   Normalized data updates in one place [OK]

Hint: Update data in one table; linked tables stay consistent [OK]

Common Mistakes:

• Assuming address is stored in Orders table
• Thinking all related tables update automatically
• Believing address duplication causes errors

4. You have a table storing customer orders with repeated customer names and addresses. What is the main problem with this design?

medium

A. It improves query speed by avoiding joins.

B. It wastes space and risks inconsistent data.

C. It ensures data is always backed up.

D. It automatically normalizes data.

Solution

1. Step 1: Identify issue with repeated data

   Repeating customer info wastes space and can cause inconsistent updates.

2. Step 2: Understand normalization benefit

   Normalization avoids repetition, saving space and keeping data consistent.

3. Final Answer:

   It wastes space and risks inconsistent data. -> Option B

4. Quick Check:

   Repeated data = waste and errors [OK]

Hint: Repeated data wastes space and causes errors [OK]

Common Mistakes:

• Thinking repeated data speeds up queries
• Confusing normalization with backup
• Believing database auto-fixes repetition

5. You have a table with columns: OrderID, CustomerName, CustomerAddress, Product. To normalize it, which step should you take?

hard

A. Split into two tables: Customers (CustomerID, CustomerName, CustomerAddress) and Orders (OrderID, Product, CustomerID).

B. Add more columns to store multiple addresses per customer.

C. Combine all data into one table without keys.

D. Store customer addresses as a comma-separated list in Orders.

Solution

1. Step 1: Identify repeated data

   CustomerName and CustomerAddress repeat for each order, causing duplication.

2. Step 2: Apply normalization

   Split data into Customers table (with unique customer info) and Orders table linked by CustomerID.

3. Final Answer:

   Split into two tables: Customers (CustomerID, CustomerName, CustomerAddress) and Orders (OrderID, Product, CustomerID). -> Option A

4. Quick Check:

   Normalization splits repeated data into related tables [OK]

Hint: Split repeated data into separate tables linked by keys [OK]

Common Mistakes:

• Adding columns instead of splitting tables
• Storing multiple addresses in one field
• Ignoring keys to link tables