Forem: Arun Prasath

CRUD Operations in MongoDB Atlas – A Beginner’s Guide with Student Database Example

Arun Prasath — Mon, 06 Oct 2025 03:45:24 +0000

Introduction

MongoDB is one of the most popular NoSQL databases used by developers today for building modern, scalable applications. Unlike traditional relational databases, MongoDB stores data in flexible JSON-like documents, making it easier to work with real-world scenarios.

In this blog, I’ll walk you through CRUD operations (Create, Read, Update, Delete) in MongoDB using a simple example: a college student database.

We’ll:

Insert student details

Query them with filters

Update academic information

Delete records when needed

To make it more exciting, we’ll run these queries directly on MongoDB Atlas Cluster (cloud-based MongoDB). You can also include screenshots of the MongoDB Atlas dashboard and outputs so readers can follow visually.

Outcome

By the end of this blog, you’ll learn:

How to insert multiple documents into a collection

How to read and filter records using queries

How to update documents (single & multiple)

How to delete documents based on conditions

How CRUD fits into real-world development

Setup: Creating a Cluster

Create a free MongoDB Atlas account → MongoDB Atlas

Create a cluster (choose the free tier).

Inside the cluster, create a database called collegeDB.

Inside collegeDB, create a collection called students.

Create (Insert)

We’ll start by inserting student records into our students collection.

Each student is stored as a separate document:

{
"student_id": "01",
"name": "Arun",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 8
}

{
"student_id": "02",
"name": "Rohit",
"age": 19,
"department": "CSBS",
"year": 2,
"cgpa": 8.2
}

Insert multiple documents:

db.students.insertMany([
{
student_id: "01",
name: "Arun",
age: 20,
department: "CSBS",
year: 2,
cgpa: 8
},
{
student_id: "02",
name: "Rohit",
age: 19,
department: "CSBS",
year: 2,
cgpa: 8.2
}
])

Read (Query)

Fetch all students:

db.students.find({})

Find all students with CGPA > 8:

db.students.find({ cgpa: { $gt: 8 } })

Find students belonging to Computer Science department:

db.students.find({ department: "CSE" })

Update

Update CGPA of a specific student:

db.students.updateOne(
{ student_id: "05" },
{ $set: { cgpa: 9.2 } }
)

Increase year of study for all 3rd-year students:

db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
)

Delete

Delete one student record by ID:

db.students.deleteOne({ student_id: "04" })

Delete all students having CGPA < 7.5:

db.students.deleteMany({ cgpa: { $lt: 7.5 } })

Troubleshooting: JSON Error in Node.js

If you’re connecting MongoDB with a Node.js backend, you might run into this error:

Failed to execute 'json' on 'Response': Unexpected end of JSON input

app.post("/students", async (req, res) => {
try {
const result = await db.collection("students").insertOne(req.body);
res.json({ success: true, id: result.insertedId });
} catch (err) {
res.status(500).json({ error: err.message });
}
});

On the frontend, check if the response has content before parsing:

const response = await fetch("/students");
let data = {};

try {
data = await response.json();
} catch (e) {
console.warn("Empty or invalid JSON response");
}

Conclusion

In this blog, we explored how to perform CRUD operations in MongoDB using a real-world example of a student database.

We:

Inserted multiple records

Queried documents with conditions

Updated both single and multiple entries

Deleted documents selectively

CRUD operations form the building blocks of every application, whether you’re managing users in a website, products in an e-commerce app, or students in a college system.

This step-by-step approach not only gave us hands-on practice with MongoDB but also demonstrated how database schemas fit into real-world academic systems.

Thanks to @santhoshnc sir for guiding and motivating us.

Indexing, Hashing & Query Optimization in DBMS

Arun Prasath — Sun, 05 Oct 2025 07:51:17 +0000

Databases handle massive data efficiently using indexes and hashing. Instead of scanning entire tables, indexes act like the index of a book, making lookups faster.

In this blog, we’ll build a Students table, create B-Tree, B+Tree, and Hash indexes, and run queries to see their effect.

📖 Key Definitions
🔹 Indexing

Indexing is a technique to speed up data retrieval from a database. Instead of scanning the whole table, the database uses an index (like a book index) to locate the rows quickly.

B-Tree Index

A B-Tree (Balanced Tree) index stores keys in a sorted order, allowing logarithmic time searches. It is efficient for:

B+ Tree Index

A B+ Tree is a variation of the B-Tree where all values are stored in the leaf nodes, and internal nodes only store keys for navigation.

Hash Index

A Hash Index uses a hashing function to map keys (like dept) into buckets.

Query Optimization

The process of minimising query execution time by leveraging indexes and writing efficient SQL statements.

Step 1: Create Students Table

CREATE TABLE Students1 (
roll_no INT PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);

Inserting Sample Records

INSERT INTO Students1 VALUES (101, 'Ana', 'CSBS', 8.5);
INSERT INTO Students1 VALUES (102, 'Paul', 'CSBS', 7.8);
INSERT INTO Students1 VALUES (103, 'Kevin', 'ECE', 9.0);
INSERT INTO Students1 VALUES (104, 'Angelin', 'ME', 8.2);
INSERT INTO Students1 VALUES (105, 'Vanessa', 'CSBS', 8.8);
INSERT INTO Students1 VALUES (106, 'Ria', 'ECE', 7.5);
INSERT INTO Students1 VALUES (107, 'Samuel', 'ME', 8.7);
INSERT INTO Students1 VALUES (108, 'Noah', 'CSBS', 6.9);
INSERT INTO Students1 VALUES (109, 'Marin', 'ECE', 8.0);
INSERT INTO Students1 VALUES (110, 'Joseph', 'CSBS', 9.2);
INSERT INTO Students1 VALUES (111, 'Trinita', 'ME', 7.9);
INSERT INTO Students1 VALUES (112, 'Ryan', 'CSBS', 8.3);
INSERT INTO Students1 VALUES (113, 'Daniel', 'ECE', 9.1);
INSERT INTO Students1 VALUES (114, 'Kane', 'ME', 7.7);
INSERT INTO Students1 VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students1 VALUES (116, 'Sarah', 'ECE', 8.4);
INSERT INTO Students1 VALUES (117, 'Merlin', 'ME', 8.0);
INSERT INTO Students1 VALUES (118, 'James', 'CSBS', 7.6);
INSERT INTO Students1 VALUES (119, 'Page', 'ECE', 8.9);
INSERT INTO Students1 VALUES (120, 'Reynolds', 'ME', 8.1);

B-Tree Index on roll_no
Most DBMSs use B-Trees to index numeric/ordered columns.

CREATE INDEX idx_roll_no ON Students1(roll_no);

Query with Index
This fetches details of roll_no = 110 in O(log n) instead of scanning all rows.

SELECT * FROM Students1 WHERE roll_no = 110;

B+ Tree Index on CGPA
B+ Trees are used for range queries, making them perfect for CGPA lookups.

CREATE INDEX idx_cgpa ON Students1(cgpa);

Query
Display all students with a CGPA> 8.0

SELECT * FROM Students1 WHERE cgpa > 8.0;

Hash Index on dept
Hashing is great for exact matches (not ranges).

CREATE INDEX idx_dept ON Students1(dept);

Query
Retrieve all students from the CSBS department

SELECT * FROM Students1 WHERE dept = 'CSBS';

⚡ Wrap Up

In this tutorial, we explored:

B-Tree Index → Fast lookup by roll_no

B+Tree Index → Efficient range queries (CGPA > 8.0)

Hash Index → Quick equality checks (dept = CSBS)

Indexes make queries 10x–100x faster, but they also consume storage & slow down inserts/updates. Use them wisely for query optimization!

Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database

DBMS - Transactions, Deadlocks & Log-Based Recovery

Arun Prasath — Sun, 05 Oct 2025 07:40:37 +0000

Working with databases is not just about storing data — it’s about ensuring reliability, atomicity, and consistency, especially when multiple users or processes are involved. In this post, we’ll explore three important concepts using a simple Accounts table:

✅ Transactions & Rollback (Atomicity)

🔒 Deadlock Simulation

📝 Log-Based Recovery

Setup: The Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO CustomerAccounts VALUES (1, 'Emily', 1000);
INSERT INTO CustomerAccounts VALUES (2, 'Bobby', 1500);
INSERT INTO CustomerAccounts VALUES (3, 'Caleb', 2000);

SELECT * FROM CustomerAccounts;

Transaction – Atomicity & Rollback

Transactions ensure all-or-nothing execution. Let’s try transferring 500 from Emily to Bobby, but roll it back midway.

Deduct 500 from Emily

UPDATE CustomerAccounts
SET balance = balance - 500
WHERE name = 'Emily';

Add 500 to Bobby
UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

Rollback transaction

ROLLBACK;

** Check balances**

SELECT * FROM Accounts;

Deadlock Simulation

Deadlocks occur when two transactions wait on each other’s locks. Let’s simulate with two sessions:

Session 1:

-- Lock Emily
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Emily';
-- Do NOT commit

Session 2:
-- Lock Bobby
UPDATE CustomerAccounts SET balance = balance - 200 WHERE name = 'Bobby';
-- Do NOT commit

Continuing Session 1
-- Try updating Bobby (held by Session2)
UPDATE CustomerAccounts SET balance = balance + 100 WHERE name = 'Bobby';

Continuing Session 2
-- Try updating Emily (held by Session 1)
UPDATE CustomerAccounts SET balance = balance + 200 WHERE name = 'Emily';

Log-Based Recovery

Modern DBMSs use logs (MySQL → Binary Log, PostgreSQL → WAL) to ensure durability and rollback safety.

-- Update Caleb
UPDATE CustomerAccounts SET balance = balance + 300 WHERE name = 'Caleb';

-- Rollback
ROLLBACK;

-- Verify balances
SELECT * FROM CustomerAccounts;

Wrap Up

In this tutorial, we covered:

Transactions & Rollback → Ensures atomicity

Deadlock Simulation → Shows concurrency pitfalls

Log-Based Recovery → Demonstrates how databases ensure durability

These concepts form the backbone of ACID properties in relational databases.

Special thanks to @santhoshnc Sir for guidance throughout this assignment.

dbms #oracle #sql #transactions #deadlock #recovery #assignment

ACID Properties with SQL Transactions in DBMS

Arun Prasath — Sun, 05 Oct 2025 06:51:45 +0000

When working with relational databases, transactions are the building blocks that ensure reliability. They follow the ACID properties:

Atomicity → All or nothing

Consistency → Valid state before & after

Isolation → Transactions don’t interfere

Durability → Changes survive crashes

In this blog, we’ll explore ACID with SQL scripts using an Accounts table.

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;

Step 1: Setup the Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
) ENGINE=InnoDB;

Insert 3 sample rows.

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Sarah', 5000),
(2, 'Jessie', 3000),
(3, 'Benson', 7000);

Run it.
Output: 3 rows inserted

Check the table:

SELECT * FROM Accounts;

Atomicity

Definition: A transaction is atomic, meaning either all operations succeed or none do.

Example: Transfer 500 from Sarah to Jessie, then rollback

ROLLBACK:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM Accounts;

Output:
balances remain unchanged (Sarah=5000, Jessie=3000).
This proves atomicity: either all updates happen, or none.

COMMIT:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
COMMIT;
SELECT * FROM Accounts;

Output: Sarah=4500, Jessie=3500.
Committed → permanent update.

Consistency

Definition: A transaction must bring the database from one valid state to another. Rules like constraints must never be violated.

Example: Try inserting negative balance

INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);

Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.

Isolation

Definition: Transactions executing at the same time should not interfere with each other.

Example: Two sessions

Session 1 (updating):

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
-- Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;

Session 1 sees the reduced balance (2500).

Session 2 (reading at same time):

SELECT balance FROM Accounts WHERE acc_no = 1;

ROLLBACK;

Both sessions see Sarah back to 4500.
This shows isolation.

Durability

Definition: Once a transaction is committed, its changes persist even if the system crashes.

Example: Commit, restart DB, check again

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
COMMIT;

Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Benson’s balance increases (7500).

Now restart MySQL server
Reconnect, run again:

USE acid_demo;
SELECT * FROM Accounts WHERE acc_no = 3;

Balance is still 7500.
This proves durability: committed changes survive restarts.

🚀 Wrap Up

We demonstrated the ACID properties using SQL:

🔹 Atomicity → Rollback prevents partial updates

🔹 Consistency → Constraints keep data valid

🔹 Isolation → Transactions run independently

🔹 Durability → Committed changes survive crashes

These principles ensure that databases remain reliable, safe, and trustworthy, even under concurrent workloads or unexpected failures.

Thanks @santhoshnc Sir for his guidance and support and for Everything

dbms #MySql #oracle #transactions #acid #database #learning

Cursor and Trigger in DBMS

Arun Prasath — Sun, 05 Oct 2025 06:35:43 +0000

When working with databases, sometimes we need to process records row by row (using Cursors) or automatically respond to events (using Triggers).

In this tutorial, we’ll:

✅ Create a Cursor that fetches employees with a salary > 50,000

✅ Build an AFTER-INSERT Trigger to maintain a student audit log

🔹 Cursor

A cursor is a pointer that lets you process query results row by row instead of all at once. Useful when applying conditions or logic to each record.

Step 1: Employee Cursor Example

Let’s create a cursor to display employee names with salary > 50,000.

Step i: Create Employee Table

CREATE TABLE Employees (
Emp_ID NUMBER PRIMARY KEY,
Emp_Name VARCHAR2(50),
Salary NUMBER
);

Step ii: Insert Sample Data

INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (1, 'Renner', 60000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (2, 'Samuel', 45000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (3, 'Ana',
75000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (4, 'Kylie', 50000);

Step iii: Cursor Implementation

DECLARE
CURSOR emp_cursor IS
SELECT Emp_Name, Salary FROM Employees WHERE Salary > 50000;
v_EmpName Employees.Emp_Name%TYPE;
v_Salary Employees.Salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_EmpName, v_Salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees: ' || v_EmpName || ', Salary: ₹' || v_Salary);
END LOOP;
CLOSE emp_cursor;
END;

🔹 Trigger

A trigger is a stored program that automatically runs when a specific event occurs (like INSERT, UPDATE, or DELETE).

Step 2:AFTER INSERT Trigger

We’ll now create a Students table and a Students_Audit table to keep track of new registrations.

Step i: Create Students Table

CREATE TABLE Students2 (
Student_ID NUMBER PRIMARY KEY,
Student_Name VARCHAR2(50),
Course VARCHAR2(50)
);

Step ii: Create Students_Audit Table

CREATE TABLE Students_Audit (
Audit_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Student_ID NUMBER,
Student_Name VARCHAR2(50),
Action VARCHAR2(50),
Action_Time TIMESTAMP
);

Step iii: Create AFTER INSERT Trigger(Trigger Implementation)

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students2
FOR EACH ROW
BEGIN
INSERT INTO Students_Audit (Student_ID, Student_Name, Action, Action_Time)
VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;

Test the Trigger

INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (1, 'Renner', 'Computer Science');
INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (2, 'Martin', 'Mechanical Engineering');

Step iv: Verify Audit Table

SELECT * FROM Students_Audit;

🚀 Wrap Up

In this tutorial, we learned:

Cursor → Process query results row by row

Trigger → Automatically log student registrations after insert

These features add power and automation to SQL programming!

Thank @santhoshnc Sir for his valuable guidance and continuous support in successfully completing this DBMS assignment.

dbms #sql #oracle #plsql #database #cursors #triggers #programming #assignment #learning

Database Normalization

Arun Prasath — Sun, 05 Oct 2025 06:19:01 +0000

Database Normalization:

Database normalization is the process of structuring a relational database to reduce redundancy and improve data integrity.

In this blog, we’ll normalize a student-course-instructor dataset from Unnormalized Form → 1NF → 2NF → 3NF, and implement it in SQL.

Step 1: Base Table

The initial unnormalized table includes details of students, their courses, instructors, and corresponding grades.

Step 2: Identifying Anomalies

Insertion anomaly: A new course cannot be added unless it is linked to a student.

Update anomaly: Modifying a course name requires updating it in several rows.

Deletion anomaly: Removing a student may also remove valuable course details if that student was the only enrollee.

1️⃣ First Normal Form (1NF)

Rule: Eliminate repeating groups, ensure atomic values.

So, we split multi-valued attributes into separate rows:

SQL Table in 1 NF,

CREATE TABLE Students_1NF (
Student_ID INT,
Student_Name VARCHAR2(100),
Course_ID INT,
Course_Name VARCHAR2(100),
Instructor VARCHAR2(100),
Grade CHAR(2),
PRIMARY KEY (Student_ID, Course_ID)
);

2️⃣ Second Normal Form (2NF)

Rule: Remove partial dependency → non-key attributes should depend on the whole primary key.

Here, student_id depends on student info, course_id depends on course info, and instructor depends on the course.
So, we split into three tables:

SQL Create Tables (2NF):

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

3️⃣ Third Normal Form (3NF)

Rule: Remove transitive dependencies (non-key attributes depending on other non-key attributes).

Here, instructor_phone depends on instructor, not on course_id. So we separate Instructor data:

SQL Create Tables (3NF):

REATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses3NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

CREATE TABLE Students3NF (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Enrollments3NF (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course3NF(CourseID)
);

Step 6: Insert Sample Data

-- Instructors
INSERT INTO Instructor VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Course3NF VALUES ('C101', 'DBMS', 'I01');
INSERT INTO Course3NF VALUES ('C102', 'Data Mining', 'I02');
INSERT INTO Course3NF VALUES ('C103', 'AI', 'I03');

-- Students
INSERT INTO Student3NF VALUES ('S01', 'Arjun');
INSERT INTO Student3NF VALUES ('S02', 'Priya');
INSERT INTO Student3NF VALUES ('S03', 'Kiran');

-- Enrollment
INSERT INTO Enrollment3NF VALUES ('S01', 'C101');
INSERT INTO Enrollment3NF VALUES ('S01', 'C102');
INSERT INTO Enrollment3NF VALUES ('S02', 'C101');
INSERT INTO Enrollment3NF VALUES ('S03', 'C103');

Step 7: Query with JOINs

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollment3NF e
JOIN Student3NF s ON e.StudentID = s.StudentID
JOIN Course3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

🚀** Wrap Up**

We started with an unnormalized table and step-by-step applied:

1NF → Removed repeating groups

2NF → Removed partial dependencies

3NF → Removed transitive dependencies

Result → A clean, normalized database with reduced redundancy, better integrity, and easier queries

Special thanks to @santhoshnc for mentoring me on database normalization concepts!

SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #BCNF #4NF #5NF #DataModeling

Student Managment System

Arun Prasath — Mon, 01 Sep 2025 08:14:12 +0000

🚀 Step-by-Step SQL Assignment Execution (Oracle Live SQL)

In this post, I am sharing my DBMS SQL Assignment execution using Oracle Live SQL.
I have included explanations and screenshots for each step to make the process clear and easy to follow.

1️⃣Create Faculty Table (DDL)

The first task was to create a Faculty table with the following fields:

FacultyID – Primary Key

FacultyName – Not Null

Dept – Department name

Email – Unique constraint

📸

2️⃣Insert Students Data (DML)

Next, I inserted three students into the Students table, each belonging to different departments.

📸

3️⃣Alter Table – Add Phone Number

I modified the Students table to add a new column PhoneNo.
This column ensures that phone numbers are 10 digits long.

📸

4️⃣Define Constraints on Courses Table

The Courses table was updated with a constraint to make sure that Credits cannot be less than 1 or more than 5.

📸

5️⃣SELECT with Functions

I displayed the student names in uppercase and also calculated the length of their email IDs.

📸