Hands-On SQL & MongoDB Transaction Practice Task

🧪 Practice Task: Managing a Simple Fund Transfer System

---

Practical hands-on practice task for transactions and consistency, designed for both SQL and MongoDB. It reinforces the concepts from Part 10 and helps learners experience the role of transactions in maintaining data integrity.

---

This hands-on task builds on the concepts introduced in Part 10: SQL ACID Transactions vs NoSQL Transactions. We recommend reading it first if you haven’t already.

---

🎯 Objective:

Implement a basic fund transfer between two users and ensure data consistency using transactions in both SQL and MongoDB.

---

⚠️ Prerequisites & Environment Notes:

• Ensure your SQL database (e.g., MySQL with InnoDB or PostgreSQL) supports transactions.
• MongoDB version must be 4.0 or higher. Transactions require running in a replica set, even if it’s a single-node development setup.
• Ensure your accounts collection exists before starting the MongoDB transaction.

---

🔹 Part A: SQL Transaction Practice

🧱 Step 1: Create Tables

CREATE TABLE Accounts (
  AccountID INT PRIMARY KEY,
  Name VARCHAR(100),
  Balance DECIMAL(10, 2)
);

---

🧾 Step 2: Insert Initial Data

INSERT INTO Accounts (AccountID, Name, Balance) VALUES
(1, 'Aisha', 1000.00),
(2, 'Ravi', 500.00);

---

⚙️ Step 3: Write a Transaction

Simulate transferring ₹200 from Aisha to Ravi.

Your task:

• Deduct 200 from Aisha’s account

• Add 200 to Ravi’s account

• Use START TRANSACTION, COMMIT, and ROLLBACK

🔧 Expected Query Structure:

START TRANSACTION;

-- Step 1: Deduct from Aisha
UPDATE Accounts
SET Balance = Balance - 200
WHERE AccountID = 1;

-- Step 2: Add to Ravi
UPDATE Accounts
SET Balance = Balance + 200
WHERE AccountID = 2;

-- Commit if successful
COMMIT;

-- If something fails:
-- ROLLBACK;

---

📤 Expected Output:

Aisha: ₹800.00  
Ravi: ₹700.00

(₹ = Indian Rupees. You can substitute your local currency as needed like $ =Dollar or any other.)

If any part fails (e.g., insufficient balance), rollback should leave both balances unchanged.

---

🔹 Part B: MongoDB Transaction Practice

🧱 Step 1: Insert Accounts

db.accounts.insertMany([
  { _id: 1, name: "Aisha", balance: 1000 },
  { _id: 2, name: "Ravi", balance: 500 }
]);

---

⚙️ Step 2: Simulate a Transaction (Multi-Document)

Use a MongoDB session to transfer ₹200 from Aisha to Ravi.

🔧 Your task:

• Start a session

• Perform two updates inside a transaction

• Handle commit or abort

✅ Code Hint:

const session = db.getMongo().startSession();
session.startTransaction();

try {
  db.accounts.updateOne(
    { _id: 1 },
    { $inc: { balance: -200 } },
    { session }
  );

  db.accounts.updateOne(
    { _id: 2 },
    { $inc: { balance: 200 } },
    { session }
  );

  session.commitTransaction();
  print("Transaction committed.");
} catch (e) {
  session.abortTransaction();
  print("Transaction aborted:", e);
} finally {
  session.endSession();
}

---

📤 Expected Output:

Aisha: 800
Ravi: 700

If anything fails, neither balance should change.

---

💡 Bonus Challenge (Optional)

Enhance your transaction logic by adding a check to ensure the sender (Aisha) has sufficient balance before transferring funds.

• SQL: Add a condition or query to verify Balance >= 200 before proceeding with the deduction.
• MongoDB: Read Aisha’s balance within the session and only proceed if it’s sufficient.

This simulates a real-world scenario where overdrafts are not allowed.

---

🧠 Reflection Questions

1. What happens if you skip the commit in a SQL or MongoDB transaction?

2. Why is it important to use transactions for operations like fund transfers?

3. In MongoDB, what advantages and trade-offs do you notice with session-based transactions?

---

complete solutions to this task will be shared in the next post. Stay tuned! 

← Previous Next →

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 →