Denormalization and when to use it in SQL - Mini Project: Build & Apply

Denormalization and When to Use It

📖 Scenario: You work for a small online bookstore. The database currently has separate tables for Books and Authors. To speed up some common queries, you want to practice denormalization by combining some data into one table.

🎯 Goal: Build a denormalized table that combines book titles and author names into one table for faster reading queries.

📋 What You'll Learn

Create a Books table with columns BookID, Title, and AuthorID.

Create an Authors table with columns AuthorID and Name.

Create a denormalized table BooksWithAuthors that includes BookID, Title, and AuthorName.

Insert sample data into Books and Authors.

Use a SQL query to populate BooksWithAuthors by joining Books and Authors.

Understand when denormalization is helpful for faster reads.

💡 Why This Matters

🌍 Real World

Denormalization is used in real-world databases to speed up queries that read data frequently, such as reporting or dashboards.

💼 Career

Database developers and administrators often decide when to denormalize data to improve performance while balancing data consistency.

1

Create the Books and Authors tables

Write SQL statements to create a table called Books with columns BookID (integer primary key), Title (text), and AuthorID (integer). Then create a table called Authors with columns AuthorID (integer primary key) and Name (text).

SQL

-- Create Books table
-- Create Authors table

Need a hint?

Use CREATE TABLE statements with the exact column names and types.

2

Insert sample data into Books and Authors

Write SQL INSERT statements to add these entries: In Authors, add (1, 'Jane Austen') and (2, 'Mark Twain'). In Books, add (101, 'Pride and Prejudice', 1) and (102, 'Adventures of Huckleberry Finn', 2).

SQL

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

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

-- Insert authors
-- Insert books

Need a hint?

Use INSERT INTO with exact values for authors and books.

3

Create the denormalized BooksWithAuthors table

Write a SQL statement to create a new table called BooksWithAuthors with columns BookID (integer primary key), Title (text), and AuthorName (text).

SQL

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

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

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Jane Austen');
INSERT INTO Authors (AuthorID, Name) VALUES (2, 'Mark Twain');

INSERT INTO Books (BookID, Title, AuthorID) VALUES (101, 'Pride and Prejudice', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (102, 'Adventures of Huckleberry Finn', 2);

-- Create BooksWithAuthors table

Need a hint?

Use CREATE TABLE with the specified columns for the denormalized table.

4

Populate BooksWithAuthors by joining Books and Authors

Write a SQL INSERT INTO BooksWithAuthors statement that selects BookID, Title, and Name as AuthorName by joining Books and Authors on AuthorID.

SQL

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

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

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Jane Austen');
INSERT INTO Authors (AuthorID, Name) VALUES (2, 'Mark Twain');

INSERT INTO Books (BookID, Title, AuthorID) VALUES (101, 'Pride and Prejudice', 1);
INSERT INTO Books (BookID, Title, AuthorID) VALUES (102, 'Adventures of Huckleberry Finn', 2);

CREATE TABLE BooksWithAuthors (
  BookID INTEGER PRIMARY KEY,
  Title TEXT,
  AuthorName TEXT
);

-- Insert data into BooksWithAuthors by joining Books and Authors

Need a hint?

Use INSERT INTO ... SELECT ... JOIN to combine data from both tables.