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.