Practice Exercise on SQL Normalization vs NoSQL Denormalization (With Sample Data)

 

🧪 Practice Exercise: Normalization vs Denormalization

---

🎯 Objective:

Understand how to design data using normalization (SQL-style) and denormalization (NoSQL-style).

---

🔹 Scenario:

You are managing a simple Library Database with Books and Authors.

---

📌 Task A: Normalize the Data (SQL style)

1. Create tables for Books and Authors.

2. Each book should store a reference (AuthorID) to the author.

3. Insert sample data for 2 authors and 3 books (some books by the same author).

4. Write an SQL query to fetch each book’s title with its author’s name.

---

📌 Task B: Denormalize the Data (NoSQL style)

1. Create a MongoDB collection called books.

2. Store author details embedded inside each book document.

3. Insert the same sample data for 2 authors and 3 books as above.

4. Write a MongoDB query to find all books by a specific author’s name.

---

🔹 Bonus Challenge

• Consider the scenario when an author’s information (e.g., email) changes.

• Explain briefly how this update would differ in normalized vs denormalized models.

---

📝 Sample Data to Use

AuthorID	AuthorName	Email
1	Jane Austen	jane.austen@example.com
2	Mark Twain	mark.twain@example.com

BookID	Title	AuthorID
101	Pride and Prejudice	1
102	Emma	1
103	Adventures of Tom Sawyer	2

---

💡 Hint: Use a JOIN to connect books and authors in SQL.

---

✍️ Tried the exercise? Share your SQL or MongoDB approach in the comments below.

Next: solution for this exercise 

← previous Next →