Denormalization tradeoffs in DBMS Theory - Mini Project: Build & Apply

Denormalization Tradeoffs

📖 Scenario: You are working with a database for an online bookstore. The database is currently normalized to reduce data duplication, but the team is considering denormalizing some tables to improve query speed for reporting.

🎯 Goal: Build a simple example showing the tradeoffs of denormalization by creating a normalized data structure, adding a denormalized field, and then discussing the pros and cons.

📋 What You'll Learn

Create a normalized data structure representing books and authors separately

Add a denormalized field to the books data to include author name directly

Explain the benefits of denormalization in this context

Explain the drawbacks of denormalization in this context

💡 Why This Matters

🌍 Real World

Denormalization is used in databases to speed up read-heavy operations like reports and dashboards by duplicating data.

💼 Career

Database administrators and developers must understand denormalization tradeoffs to design efficient and maintainable database schemas.

1

Create normalized tables for books and authors

Create two tables: authors with columns author_id and author_name, and books with columns book_id, title, and author_id. Insert these exact entries into authors: (1, 'Jane Austen'), (2, 'Mark Twain'). Insert these exact entries into books: (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2).

DBMS Theory

# Create authors and books tables with specified columns and insert data
# Your code here

Need a hint?

Define two tables with primary keys and foreign key relationship via author_id. Insert the exact rows given.

2

Add a denormalized column to books

Add a new column called author_name of type VARCHAR(100) to the books table. Then update this column with the corresponding author names from the authors table for each book.

DBMS Theory

CREATE TABLE authors (author_id INT PRIMARY KEY, author_name VARCHAR(100));
INSERT INTO authors VALUES (1, 'Jane Austen'), (2, 'Mark Twain');

CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT);
INSERT INTO books VALUES (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2);

-- Add author_name column to books and update it
# Your code here

Need a hint?

Use ALTER TABLE to add the column, then UPDATE with a subquery to fill author names.

3

Explain benefits of denormalization

Write a short explanation in a comment about one benefit of having the author_name directly in the books table, such as faster queries for reports that show book titles with author names.

DBMS Theory

CREATE TABLE authors (author_id INT PRIMARY KEY, author_name VARCHAR(100));
INSERT INTO authors VALUES (1, 'Jane Austen'), (2, 'Mark Twain');

CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT);
INSERT INTO books VALUES (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2);

ALTER TABLE books ADD COLUMN author_name VARCHAR(100);

UPDATE books SET author_name = (SELECT author_name FROM authors WHERE authors.author_id = books.author_id);

-- One benefit of denormalization:
# Your code here

Need a hint?

Think about how having author names in the books table helps when you want to list books with authors quickly.

4

Explain drawbacks of denormalization

Write a short explanation in a comment about one drawback of denormalization, such as the risk of data inconsistency if author names change and are not updated in all places.

DBMS Theory

CREATE TABLE authors (author_id INT PRIMARY KEY, author_name VARCHAR(100));
INSERT INTO authors VALUES (1, 'Jane Austen'), (2, 'Mark Twain');

CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT);
INSERT INTO books VALUES (101, 'Pride and Prejudice', 1), (102, 'Adventures of Huckleberry Finn', 2);

ALTER TABLE books ADD COLUMN author_name VARCHAR(100);

UPDATE books SET author_name = (SELECT author_name FROM authors WHERE authors.author_id = books.author_id);

-- One benefit of denormalization: It allows faster queries for reports because the author name is directly available in the books table without needing to join with the authors table.

-- One drawback of denormalization:
# Your code here

Need a hint?

Consider what happens if an author's name changes but the books table still has the old name.