Convert a Table into First Normal Form (1NF)

Designing a Table in First Normal Form (1NF)

Write a SQL query to normalize a table into First Normal Form (1NF).

Solution:

-- Original table with repeating groups.
CREATE TABLE Orders (
    OrderID INT,
    CustomerName VARCHAR(100),
    ProductNames VARCHAR(MAX) -- Comma-separated list of products.
);

-- Normalize the table into 1NF by splitting repeating groups.
CREATE TABLE Orders_1NF (
    OrderID INT,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100)
);

Explanation:

• Purpose of the Query :
• The goal is to eliminate repeating groups and ensure atomic values in each column, achieving 1NF.
• Key Components :
• Orders_1NF: A normalized table where each product is stored in a separate row.
• Each column contains only one value per row.
• Why use 1NF? :
• 1NF ensures that data is stored in a structured format, making it easier to query and maintain.
• It eliminates redundancy caused by repeating groups.
• Real-World Application :
• In an e-commerce system, storing each product in a separate row simplifies order processing.

Notes:

• Repeating groups violate 1NF and can lead to data anomalies.
• Ensure that all columns contain atomic values after normalization.
• Important Considerations:
• Splitting tables may increase the number of rows but improves data integrity.

For more Practice: Solve these Related Problems:

• Write a SQL query to normalize a table containing customer orders where each order has multiple products stored as a comma-separated list into 1NF.
• Write a SQL query to split a table with a single column storing multiple phone numbers per user into 1NF by creating a separate row for each phone number.
• Write a SQL query to redesign a table that stores multiple email addresses for a user in a single column into 1NF.
• Write a SQL query to normalize a table containing student grades where each student has multiple subjects and grades stored in a single column into 1NF.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Database Design and Normalization Exercises Home
Next SQL Exercise: Resolving Partial Dependencies for Second Normal Form (2NF).