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

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

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

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

Hint

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

Practice

1. What is the main purpose of denormalization in a database?

easy

A. To enforce strict data integrity through normalization

B. To speed up data retrieval by adding redundant data

C. To eliminate all duplicate data for storage efficiency

D. To reduce the size of the database by removing tables

Solution

1. Step 1: Understand denormalization goal

   Denormalization intentionally adds repeated data to reduce complex joins and speed up reading.

2. Step 2: Compare options with definition

   Only To speed up data retrieval by adding redundant data matches this purpose; others describe normalization or unrelated goals.

3. Final Answer:

   To speed up data retrieval by adding redundant data -> Option B

4. Quick Check:

   Denormalization = Adding redundant data to speed reads [OK]

Hint: Denormalization means adding repeated data to read faster [OK]

Common Mistakes:

• Confusing denormalization with normalization
• Thinking denormalization removes duplicates
• Assuming denormalization reduces database size

2. Which of the following is a correct statement about denormalization?

easy

A. It can cause data inconsistencies if updates are not handled carefully

B. It always eliminates the need for any data updates

C. It removes redundant data to save storage space

D. It requires more complex joins in queries

Solution

1. Step 1: Analyze denormalization effects on data updates

   Denormalization adds redundant data, so updates must be carefully managed to avoid inconsistencies.

2. Step 2: Evaluate each option

   It can cause data inconsistencies if updates are not handled carefully correctly states the risk of data inconsistency; others are incorrect or opposite.

3. Final Answer:

   It can cause data inconsistencies if updates are not handled carefully -> Option A

4. Quick Check:

   Denormalization risk = data inconsistency if updates fail [OK]

Hint: Denormalization risks data errors if updates are sloppy [OK]

Common Mistakes:

• Believing denormalization removes need for updates
• Thinking it reduces redundant data
• Assuming it makes queries more complex

3. Consider a denormalized table where customer addresses are repeated in multiple rows. What is a likely consequence when updating a customer's address?

medium

A. The database prevents any address updates to avoid errors

B. All address entries update automatically without extra effort

C. Only one address entry updates, causing inconsistent data

D. The address is stored in a separate table to avoid repetition

Solution

1. Step 1: Understand denormalized data repetition

   In denormalization, the same address appears multiple times, so updating one row doesn't update others automatically.

2. Step 2: Identify update behavior

   Without special handling, only one entry updates, causing inconsistent addresses across rows.

3. Final Answer:

   Only one address entry updates, causing inconsistent data -> Option C

4. Quick Check:

   Denormalized repeated data update = risk of inconsistency [OK]

Hint: Repeated data updates need multiple changes or cause inconsistency [OK]

Common Mistakes:

• Assuming all repeated data updates automatically
• Thinking database blocks updates in denormalized tables
• Confusing denormalization with normalization structure

4. A database designer denormalized a table to improve read speed but notices data inconsistencies after updates. What is the best way to fix this issue?

medium

A. Remove denormalization and normalize the database fully

B. Delete duplicate rows to avoid repeated data

C. Ignore inconsistencies since denormalization is for speed

D. Add triggers or application logic to update all copies of data

Solution

1. Step 1: Identify cause of inconsistencies

   Denormalization causes repeated data; updates must be applied to all copies to keep data consistent.

2. Step 2: Choose solution to maintain consistency

   Adding triggers or logic to update all copies ensures consistency without losing denormalization benefits.

3. Final Answer:

   Add triggers or application logic to update all copies of data -> Option D

4. Quick Check:

   Fix denormalization inconsistency = update all copies [OK]

Hint: Use triggers or logic to sync repeated data updates [OK]

Common Mistakes:

• Removing denormalization without considering performance
• Ignoring data inconsistencies
• Deleting duplicates breaks denormalization purpose

5. A company wants to speed up report generation by denormalizing their sales database. Which tradeoff should they carefully consider before proceeding?

hard

A. Denormalization may cause data update anomalies if not managed

B. Denormalization guarantees 100% data accuracy

C. Denormalization eliminates the need for backups

D. Denormalization will reduce storage space significantly

Solution

1. Step 1: Understand denormalization tradeoffs

   Denormalization speeds up reads but adds redundant data, risking update anomalies if not handled properly.

2. Step 2: Evaluate options for tradeoffs

   Denormalization may cause data update anomalies if not managed correctly states the risk; others are false or unrelated.

3. Final Answer:

   Denormalization may cause data update anomalies if not managed -> Option A

4. Quick Check:

   Denormalization tradeoff = update anomalies risk [OK]

Hint: Denormalization speeds reads but risks update errors [OK]

Common Mistakes:

• Assuming denormalization saves storage space
• Believing it removes need for backups
• Thinking it guarantees perfect accuracy