First Normal Form (1NF) in SQL - Mini Project: Build & Apply

Organizing a Customer Orders Table into First Normal Form (1NF)

📖 Scenario: You work at a small online store. The store keeps track of customer orders in a table. Currently, the orders table has a problem: some columns contain multiple values in one cell, which makes it hard to work with the data.Your job is to fix this by organizing the data into First Normal Form (1NF). This means each column should hold only one value per row, making the table easier to use and understand.

🎯 Goal: You will create a new table that follows First Normal Form (1NF) rules. This means no repeating groups or multiple values in a single column. Each order item will have its own row with one product per row.

📋 What You'll Learn

Create an initial table with customer orders where some columns have multiple values

Add a helper variable to identify the order to split

Write a query to split the multiple product values into separate rows

Create a new table that stores the orders in 1NF format with one product per row

💡 Why This Matters

🌍 Real World

Organizing data into First Normal Form helps businesses keep their databases clean and easy to use, especially when dealing with orders, customers, or inventory.

💼 Career

Database administrators and developers often need to normalize data to improve database design, performance, and maintainability.

1

Create the initial orders table with multiple products in one column

Write a SQL statement to create a table called orders with columns order_id (integer), customer_name (text), and products (text). Insert one row with order_id 1, customer_name 'Alice', and products 'apple, banana, orange' (all products in one string).

SQL

-- Create the orders table and insert one row with multiple products in the products column
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the row.

2

Add a variable to identify the order to split

Write a SQL variable or CTE named order_to_split that selects the row from orders where order_id is 1.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name TEXT,
    products TEXT
);

INSERT INTO orders (order_id, customer_name, products) VALUES (1, 'Alice', 'apple, banana, orange');

-- Create a CTE named order_to_split selecting order_id 1
-- Your code here

Need a hint?

Use a WITH clause to create a CTE named order_to_split.

3

Split the products into separate rows using a query

Write a SQL query that uses order_to_split and splits the products string into separate rows, one product per row. Use STRING_SPLIT(products, ',') or an equivalent function to split the string. Select order_id, customer_name, and each product as a separate row.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name TEXT,
    products TEXT
);

INSERT INTO orders (order_id, customer_name, products) VALUES (1, 'Alice', 'apple, banana, orange');

WITH order_to_split AS (
    SELECT * FROM orders WHERE order_id = 1
)
-- Write a query to split products into separate rows
-- Your code here

Need a hint?

Use CROSS APPLY STRING_SPLIT(products, ',') to split the products string into rows.

4

Create a new table with orders in First Normal Form (1NF)

Write a SQL statement to create a new table called orders_1nf with columns order_id (integer), customer_name (text), and product (text). Insert the split rows from the previous query into orders_1nf so each product has its own row.

SQL

CREATE TABLE orders (
    order_id INTEGER,
    customer_name TEXT,
    products TEXT
);

INSERT INTO orders (order_id, customer_name, products) VALUES (1, 'Alice', 'apple, banana, orange');

WITH order_to_split AS (
    SELECT * FROM orders WHERE order_id = 1
)
SELECT order_id, customer_name, TRIM(value) AS product
FROM order_to_split
CROSS APPLY STRING_SPLIT(products, ',');

-- Create orders_1nf table and insert split rows
-- Your code here

Need a hint?

Create the new table and insert the split rows using the same splitting query.