Normalization vs Denormalization: SQL vs MongoDB Practice task Solution

✅ Solution: Normalization vs Denormalization Practice

---

This tutorial provides the complete solution to the earlier practice task comparing normalization in SQL and denormalization in MongoDB. If you haven't attempted the exercise, click here to try it first.

Here's the full solution to the Normalization vs Denormalization practice exercise using both SQL (normalized) and MongoDB (denormalized) approaches.

---

🔹 Task A: Normalized Design (SQL Style)

✅ 1. Table Structure

Authors Table

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  AuthorName VARCHAR(100),
  Email VARCHAR(100)
);

Books Table

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(150),
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

---

✅ 2. Sample Data Insertion

INSERT INTO Authors (AuthorID, AuthorName, Email) VALUES
(1, 'Jane Austen', 'jane.austen@example.com'),
(2, 'Mark Twain', 'mark.twain@example.com');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, 'Pride and Prejudice', 1),
(102, 'Emma', 1),
(103, 'Adventures of Tom Sawyer', 2);

---

✅ 3. SQL Query to Join Data

SELECT Books.Title, Authors.AuthorName
FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

🔍 Expected Output:

+----------------------------+-------------+
| Title                      | AuthorName  |
+----------------------------+-------------+
| Pride and Prejudice        | Jane Austen |
| Emma                       | Jane Austen |
| Adventures of Tom Sawyer   | Mark Twain  |
+----------------------------+-------------+

---

🔹 Task B: Denormalized Design (MongoDB Style)

✅ 1. Book Documents with Embedded Author Info

db.books.insertMany([
  {
    book_id: 101,
    title: "Pride and Prejudice",
    author: {
      author_id: 1,
      name: "Jane Austen",
      email: "jane.austen@example.com"
    }
  },
  {
    book_id: 102,
    title: "Emma",
    author: {
      author_id: 1,
      name: "Jane Austen",
      email: "jane.austen@example.com"
    }
  },
  {
    book_id: 103,
    title: "Adventures of Tom Sawyer",
    author: {
      author_id: 2,
      name: "Mark Twain",
      email: "mark.twain@example.com"
    }
  }
]);

---

✅ 2. MongoDB Query to Find Books by Author Name

db.books.find({ "author.name": "Jane Austen" }, { title: 1, _id: 0 });

🔍 Expected Output:

[
  { title: "Pride and Prejudice" },
  { title: "Emma" }
]

---

🔸 Bonus Challenge: Updating Author Email

🧠 In Normalized (SQL):

• You update once in the Authors table:

UPDATE Authors
SET Email = 'new.jane.austen@example.com'
WHERE AuthorID = 1;

✅ Change is reflected across all books automatically (since books reference the author ID).

---

🧠 In Denormalized (MongoDB):

• You must update all documents containing the embedded author data:

db.books.updateMany(
  { "author.author_id": 1 },
  { $set: { "author.email": "new.jane.austen@example.com" } }
);

❗ Requires multiple updates — more effort, but often better read performance.

🔗 Want a deeper look at MongoDB's schema design strategies? Check out our MongoDB Schema Design Guide.

---

✅ Summary: What You’ve Learned

Concept	SQL	MongoDB (NoSQL)
Data Structure	Normalized into separate tables	Denormalized with embedded docs
Redundancy	Low	High
Update Effort	One update in Authors table	Multiple document updates
Read Simplicity	Needs JOIN	Direct query, no joins needed

---

🏁 That’s it! You now understand the core differences between normalized and denormalized database design using SQL and MongoDB. Got questions or want more practice? Let us know in the comments or check out more tutorials in our Learning Series.

← Previous Next →