Full Practical Solution | SQL JOINs vs MongoDB Referencing

 Here’s the full solution to the Practice Assignment on SQL JOINs vs MongoDB Referencing, including expected outputs and explanations.

---

✅ Solution: SQL JOINs vs MongoDB Referencing

---

🔹 Part A: SQL Solution

✅ 1. Tables Created

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

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

---

✅ 2. Sample Data Inserted

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'George Orwell'),
(2, 'J.K. Rowling');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, '1984', 1),
(102, 'Animal Farm', 1),
(103, 'Harry Potter', 2);

---

✅ 3. Query Using JOIN

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

📤 Expected Output:

Title	Name
1984	George Orwell
Animal Farm	George Orwell
Harry Potter	J.K. Rowling

🔍 This result shows book titles along with the author's name using a JOIN between Books and Authors.

---

🔹 Part B: MongoDB Solution

Note: MongoDB doesn’t support traditional SQL-style JOINs. Instead, you can simulate them using referencing (multiple queries) or the $lookup operator in aggregation pipelines.

✅ 1. Insert Documents

Authors Collection:

db.authors.insertMany([
  { _id: 1, name: "George Orwell" },
  { _id: 2, name: "J.K. Rowling" }
]);

Books Collection:

db.books.insertMany([
  { title: "1984", author_id: 1 },
  { title: "Animal Farm", author_id: 1 },
  { title: "Harry Potter", author_id: 2 }
]);

---

✅ 2. Simulating a JOIN

Step 1: Find Author Document

const author = db.authors.findOne({ name: "George Orwell" });

🔁 Expected Output:

{
  "_id": 1,
  "name": "George Orwell"
}

---

Step 2: Find Books by Author ID

db.books.find({ author_id: author._id });

🔁 Expected Output:

[
  { "title": "1984", "author_id": 1 },
  { "title": "Animal Farm", "author_id": 1 }
]

📝 To match SQL output, you'd display:

Title	Author Name
1984	George Orwell
Animal Farm	George Orwell

---

🔸 Optional: Embedded Model Query

If books were stored like this:

db.books.insertOne({
  title: "1984",
  author: {
    id: 1,
    name: "George Orwell"
  }
});

Then your query:

db.books.find({ "author.name": "George Orwell" });

🔁 Would return documents with both book title and embedded author name directly, simulating a denormalized JOIN.

---

✅ Summary

Concept	SQL (JOIN)	MongoDB (Referencing)
How data is linked	Foreign key + JOIN	Manual field referencing (or embedding)
Queries	One SQL JOIN query	Two MongoDB queries (or use $lookup)
Output	Title + Author from joined tables	Title + Author via multiple reads or embed
Query Complexity	Moderate (JOIN syntax)	Simple (but more steps)

---

Note: MongoDB’s $lookup can perform JOIN-like operations in a single aggregation query, but it's more complex and typically used for reporting or complex analytics.

---

• Move to Part 10: Transactions and Consistency

← Previous Next →

🧪 Practice Assignment: SQL JOINs vs MongoDB Referencing

---

Here's a clear and practical assignment to help readers compare SQL JOINs with MongoDB referencing. This exercise is designed to reinforce how both systems manage relationships through hands-on tasks.

---

🎯 Objective:

Understand how to build and query related data using:

• SQL with JOINs

• MongoDB with manual referencing

---

📚 Scenario:

You’re building a simple Library System with:

• A list of Authors

• A list of Books written by those authors

Each book belongs to one author, and each author can write multiple books (One-to-Many relationship).

---

🔹 Part A: SQL – Using Foreign Keys & JOINs

✅ Step 1: Create Tables

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

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

---

✅ Step 2: Insert Data

INSERT INTO Authors (AuthorID, Name) VALUES
(1, 'George Orwell'),
(2, 'J.K. Rowling');

INSERT INTO Books (BookID, Title, AuthorID) VALUES
(101, '1984', 1),
(102, 'Animal Farm', 1),
(103, 'Harry Potter', 2);

---

✅ Step 3: Write a JOIN Query

Write a SQL query to display all book titles along with their author names.

Example:

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

---

🔹 Part B: MongoDB – Manual Referencing

✅ Step 1: Insert Documents

Authors Collection:

db.authors.insertMany([
  { _id: 1, name: "George Orwell" },
  { _id: 2, name: "J.K. Rowling" }
]);

Books Collection:

db.books.insertMany([
  { title: "1984", author_id: 1 },
  { title: "Animal Farm", author_id: 1 },
  { title: "Harry Potter", author_id: 2 }
]);

---

✅ Step 2: Query to Simulate a JOIN

Write MongoDB queries to show book titles and their author names:

Step 1: Get author document

const author = db.authors.findOne({ name: "George Orwell" });

Step 2: Find all books by that author

db.books.find({ author_id: author._id });

---

🔸 Optional Challenge: Try Embedding in MongoDB

Instead of referencing, restructure books with embedded author info:

db.books.insertOne({
  title: "1984",
  author: {
    id: 1,
    name: "George Orwell"
  }
});

Then query:

db.books.find({ "author.name": "George Orwell" });

---

🧠 Reflection Questions

1. Which method (JOIN or referencing) felt more intuitive to you?

2. How does MongoDB referencing differ from SQL JOINs?

3. What trade-offs do you notice in terms of query performance or complexity?

---

• Click Next to view solution with expected outputs

---

Feel free to share your thoughts in the comments!

← Previous Next →

SQL Joins vs NoSQL Referencing: Understanding Data Relationships Easily

🔷 Part 9: Relationships in SQL vs Referencing in NoSQL

---

This is a key concept that helps readers understand how to link data across tables (SQL) or documents (NoSQL), which is essential for real-world applications like e-commerce, school systems, or social networks.

---

📍 Introduction

Most databases store related data: customers and orders, students and classes, or products and reviews.

• In SQL, we use foreign keys and JOINs to link tables.

• In NoSQL (like MongoDB), we use manual referencing or embedding to connect documents.

Understanding these relationship methods is essential for building scalable and maintainable database designs.

---

🔸 1. Relationships in SQL (Using Joins)

SQL supports:

• One-to-One

• One-to-Many

• Many-to-Many
  …through foreign keys and JOINs.

---

✅ Example: Students and Classes (One-to-Many)

📁 Tables:

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Classes (
  ClassID INT PRIMARY KEY,
  StudentID INT,
  Subject VARCHAR(50),
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

🔍 Fetching Related Data:

SELECT Students.Name, Classes.Subject
FROM Students
JOIN Classes ON Students.StudentID = Classes.StudentID;

➡️ This JOIN connects each student to their class records.

---

🔹 2. Relationships in MongoDB (Referencing)

In NoSQL, there are two ways to handle related data:

✅ A. Manual Referencing (Normalized approach)

// students collection
{
  _id: ObjectId("stu101"),
  name: "Aisha Khan"
}

// classes collection
{
  student_id: ObjectId("stu101"),
  subject: "Math"
}

To retrieve, you need two queries:

const student = db.students.findOne({ name: "Aisha Khan" });
const classes = db.classes.find({ student_id: student._id });

✅ B. Embedding (Denormalized approach)

{
  name: "Aisha Khan",
  classes: [
    { subject: "Math" },
    { subject: "Science" }
  ]
}

➡️ This is faster for reads, but harder to update if the same class is shared across students.

---

🔄 Comparison Table: SQL Joins vs NoSQL Referencing

Feature	SQL (Joins)	MongoDB (Referencing/Embedding)
Relationship type	Foreign keys & JOINs	Manual references or embedded documents
Querying	Single JOIN query	Multiple queries or nested documents
Read performance	May be slower due to joins	Fast if embedded
Update complexity	Centralized & simple	More complex with embedded structures
Data duplication	Minimal	Possible with embedding

---

🧠 Best Practices

✅ Use SQL when:

• Data relationships are complex and frequently queried together

• You want strong referential integrity

✅ Use NoSQL referencing when:

• You need flexibility or horizontal scaling

• Data isn’t always accessed together

---

📌 Quick Summary

• SQL uses foreign keys and JOINs for linking tables.
• NoSQL uses manual referencing or embedding to relate documents.
• Joins are powerful but may affect performance in large datasets.
• Embedding is fast but risks data duplication and update complexity.

---

✅ What’s Next?

In Part 10, we’ll dive into Transactions and Consistency — how SQL ensures ACID compliance, and how NoSQL handles consistency across distributed systems.

---

Click Next for:

•  practice assignment comparing SQL JOINs and MongoDB referencing

← Previous Next →