Why normalization matters in SQL - See It in Action

Why Normalization Matters

📖 Scenario: You are working for a small bookstore that wants to organize its sales data efficiently. Currently, all information about books, authors, and sales is stored in one big table. This causes repeated data and makes it hard to update or find information.To fix this, you will learn how to split the data into smaller, related tables using normalization. This will reduce repetition and make the database easier to manage.

🎯 Goal: Create a simple database with three tables: Books, Authors, and Sales. Each table will store specific information to avoid repeating data. You will write SQL commands to create these tables and link them properly.

📋 What You'll Learn

Create a table called Authors with columns AuthorID (primary key) and Name.

Create a table called Books with columns BookID (primary key), Title, and AuthorID (foreign key).

Create a table called Sales with columns SaleID (primary key), BookID (foreign key), and Quantity.

Use proper SQL syntax to define primary keys and foreign keys.

Ensure the tables are normalized to avoid repeating author names or book titles in sales records.

💡 Why This Matters

🌍 Real World

Bookstores, libraries, and many businesses use normalized databases to keep their data clean and easy to update.

💼 Career

Database designers and developers must understand normalization to build efficient and reliable databases.

1

Create the Authors table

Write a SQL statement to create a table called Authors with two columns: AuthorID as an integer primary key, and Name as text.

SQL

-- Write the CREATE TABLE statement for Authors
-- Your code here

Need a hint?

Use CREATE TABLE Authors and define AuthorID as the primary key.

2

Create the Books table with foreign key

Write a SQL statement to create a table called Books with three columns: BookID as an integer primary key, Title as text, and AuthorID as an integer foreign key referencing Authors(AuthorID).

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

-- Write the CREATE TABLE statement for Books
-- Your code here

Need a hint?

Remember to add a foreign key constraint on AuthorID that links to Authors.

3

Create the Sales table with foreign key

Write a SQL statement to create a table called Sales with three columns: SaleID as an integer primary key, BookID as an integer foreign key referencing Books(BookID), and Quantity as an integer.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

-- Write the CREATE TABLE statement for Sales
-- Your code here

Need a hint?

Make sure to link BookID in Sales to the Books table.

4

Explain why normalization helps

Add a SQL comment at the end of your code explaining why splitting data into Authors, Books, and Sales tables helps avoid repeating data and makes the database easier to manage.

SQL

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    Name TEXT
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT,
    AuthorID INTEGER,
    FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY,
    BookID INTEGER,
    Quantity INTEGER,
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

-- Add a comment explaining why normalization matters
-- Your code here

Need a hint?

Write a comment starting with -- that explains how normalization reduces repeated data and improves management.