DEV Community: PRIAN S S 24CB042

💻 Mastering MongoDB CRUD Operations — A Hands-on Student Database Example 🎓

PRIAN S S 24CB042 — Wed, 08 Oct 2025 14:10:33 +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 super easy to handle real-world data without worrying about rigid table structures 📄.

In this blog, I’ll walk you through performing CRUD operations (Create, Read, Update, Delete) in MongoDB using a simple example — a college student database 🧑‍🎓

We’ll:
✅ Insert student details
✅ Query records
✅ Update academic information
✅ Delete records

All of this will be done directly on a MongoDB Atlas Cluster (cloud-based MongoDB) ☁️.

Along the way, I’ll include screenshots of my Atlas dashboard and query results so you can follow along visually 👀.

🎯 Outcome:

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

✨ Insert multiple documents into a collection
✨ Read and filter records using queries
✨ Update single and multiple documents
✨ Delete documents based on conditions
✨ Understand how CRUD fits into real-world applications

⚙️ Setup: Creating a Cluster

1️⃣ Create a free MongoDB Atlas account
2️⃣ Create a cluster and a database called collegeDB
3️⃣ Inside it, create a collection named students

🧩 CREATE (Insert):

Let’s begin by inserting student records into our collection.
Each student will be represented as a separate document in the students collection.

{
student_id: "S001",
name: "PRIAN",
age: 20,
department: "CSBS",
year: 2,
cgpa: 9
}

{
student_id: "S002",
name: "Santhosh",
age: 19,
department: "CSE",
year: 3,
cgpa: 8
}

{
student_id: "S003",
name: "SUGESH",
age: 20,
department: "EEE",
year: 4,
cgpa: 9
}

{
student_id: "S004",
name: "BOOPATHY",
age: 19,
department: "CSBS",
year: 1,
cgpa: 9.3
}

{
student_id: "S005",
name: "RAKSHANTH",
age: 20,
department: "CIVIL",
year: 2,
cgpa: 6.8
}

🔍 READ (Query):

Let’s now retrieve and filter student data.

📋 Display all student records:
db.students.find({})

📈 Find all students with CGPA > 8:
db.students.find({ cgpa: { $gt: 8 } })

💡 Find students belonging to the Computer Science department:

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

✏️ UPDATE:

Let’s modify existing records in our collection.

Update CGPA of a specific student:
db.students.updateOne(
{ student_id: "S005" },
{ $set: { cgpa:7.5}
)

Increase the year of study for all 3rd-year students by 1:
db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
)

❌ DELETE:

Time to clean up some records
🗑️ Delete one student by student_id:
db.students.deleteOne({ student_id: "S004" })

🗑️ Delete all students with CGPA < 7.5:
db.students.deleteMany({ cgpa: { $lt: 7.5 } })

🏁 Conclusion

In this blog, we explored CRUD operations in MongoDB using a practical student database example.

We learned how to:
🔸 Insert student documents(C)
🔸 Query data with filters(R)
🔸 Update specific or multiple records(U)
🔸 Delete unwanted entries(D)

CRUD forms the foundation of every real-world application, whether managing users, products, or students 👨‍💻.

This step-by-step guide helped me understand how MongoDB handles data dynamically and efficiently, especially in academic and enterprise systems.

A huge thanks 🙏 to @santhoshnc sir for his constant guidance and motivation throughout this learning journey. 🌟

💾 Transactions, Deadlocks & Log-Based Recovery in SQL

PRIAN S S 24CB042 — Sat, 04 Oct 2025 14:32:09 +0000

Database management systems ensure data consistency and reliability even during failures. Three major concepts that demonstrate this reliability are Transactions, Deadlocks, and Log-Based Recovery. Let’s explore each one with practical SQ

🧱 Schema Setup

We’ll start with a simple Accounts table:
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

This table will be used throughout the examples.

1️⃣ Transaction – Atomicity & Rollback

A transaction is a set of SQL operations that are executed as a single unit.
The key property here is Atomicity — meaning all operations must succeed or none at all.

Let’s simulate a money transfer between Alice and Bob.

START TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

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

SELECT * FROM Accounts;

2️⃣ Deadlock Simulation

A deadlock occurs when two transactions hold locks that the other needs.
Let’s simulate it using two sessions in MySQL.

🪜 Session 1

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

OUTPUT:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

✅ The DBMS automatically detects and resolves the deadlock by rolling back one transaction.

3️⃣ Log-Based Recovery

Databases maintain log files (Binary Logs or WAL) to record all changes.
Let’s see how rollback is logged internally.
START TRANSACTION;

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Charlie';

SELECT * FROM Accounts;

Now rollback:

ROLLBACK;

SELECT * FROM Accounts;

✅ The rollback is recorded in MySQL’s binary log or PostgreSQL’s WAL, ensuring that even after a crash, the database can undo incomplete transactions and redo committed ones.

🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)

PRIAN S S 24CB042 — Sat, 04 Oct 2025 05:37:14 +0000

When working with databases, query performance is everything. Indexing and hashing help speed up data retrieval dramatically, especially when working with large tables.
In this blog, we’ll explore:
B-Tree Indexing
B+ Tree Indexing (conceptually, in practice it’s B-Tree)
Hash Indexing
Query optimization examples
We’ll use a sample Students table with 20+ records for demonstration.

📌 Step 1: Create the Students Table

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);

📌 Step 2: Insert Sample Records

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Aarav', 'CSE', 8.5),
(102, 'Diya', 'ECE', 7.9),
(103, 'Rahul', 'MECH', 6.8),
(104, 'Sneha', 'CSE', 8.9),
(105, 'Karthik', 'EEE', 7.2),
(106, 'Meera', 'CSBS', 9.1),
(107, 'Arjun', 'CSE', 8.0),
(108, 'Nisha', 'IT', 7.5),
(109, 'Vikram', 'CSBS', 8.7),
(110, 'Priya', 'ECE', 9.2),
(111, 'Varun', 'MECH', 7.0),
(112, 'Pooja', 'CSE', 8.6),
(113, 'Rohit', 'EEE', 6.9),
(114, 'Aditi', 'CSBS', 8.3),
(115, 'Suresh', 'IT', 7.8),
(116, 'Ishita', 'CSE', 9.0),
(117, 'Manoj', 'ECE', 6.7),
(118, 'Ananya', 'CSBS', 8.4),
(119, 'Deepak', 'MECH', 7.1),
(120, 'Krishna', 'EEE', 8.2);

✅ We now have enough data to work with indexing.

📌 Step 3: B-Tree Index on roll_no

B-Trees are the default index type in most RDBMS (MySQL, PostgreSQL). They are great for exact lookups on primary key columns.

CREATE INDEX idx_rollno ON Students (roll_no);

Query with Index:
SELECT *
FROM Students
WHERE roll_no = 110;

👉 This efficiently returns all students with CGPA greater than 8.0.

📌 Step 4: B+ Tree Index on cgpa

In practice, B+ Trees are also implemented as B-Trees in MySQL/PostgreSQL. They are excellent for range queries.
CREATE INDEX idx_cgpa ON Students (cgpa);
Query with Index:
SELECT *
FROM Students
WHERE cgpa > 8.0;

📌 Step 5: Hash Index on dept

Hash indexes are best for equality-based queries (e.g., dept = 'CSBS').
PostgreSQL
CREATE INDEX idx_dept ON Students USING HASH (dept);
Query with Index:
SELECT *
FROM Students
WHERE dept = 'CSBS';
👉 This fetches all students in the CSBS department (Meera, Vikram, Aditi, Ananya).

📊 Final Thoughts

B-Tree Index → Best for unique lookups (like roll numbers).
B+ Tree Index → Great for range queries (cgpa > 8).
Hash Index → Ideal for equality checks (dept = 'CSBS').

By applying the right indexing strategy, you can optimize query performance significantly.

🔥ACID Properties with SQL Transactions

PRIAN S S 24CB042 — Fri, 03 Oct 2025 08:53:14 +0000

When working with databases, ACID properties are the foundation that ensures data reliability and consistency.
In this blog, we’ll explore each property with SQL examples using a simple Accounts table.

🏦 Step 1: Create Table and Insert Sample Data

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

INSERT INTO Accounts VALUES (101, 'Alice', 5000);
INSERT INTO Accounts VALUES (102, 'Bob', 3000);
INSERT INTO Accounts VALUES (103, 'Charlie', 7000);

SELECT * FROM Accounts;

🅰 Atomicity

Definition: Atomicity ensures a transaction is all or nothing. If one part fails, the whole transaction is rolled back.
Example: Transfer money from Alice → Bob, but rollback before completion.
BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;

ROLLBACK;

SELECT * FROM Accounts;

🅲 Consistency

Definition: Consistency ensures that data follows defined rules and constraints.
Example: Prevent negative balance using a CHECK constraint.

ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
INSERT INTO Accounts VALUES (104, 'David', -500);

🅸 Isolation

Definition: Isolation ensures that concurrent transactions do not interfere with each other.

Example: Run two sessions at once.

SESSION 1 :
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;

SESSION 2:
SELECT * FROM Accounts WHERE acc_no = 103;

🅳 Durability

Definition: Durability ensures once a transaction is committed, it remains even after a system crash.

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;

🚀 Conclusion

*Through this demo, we saw how ACID properties guarantee:
*Atomicity → no partial updates.
*Consistency → rules are enforced.
*Isolation → transactions don’t clash.
*Durability → committed changes survive crashes.

Thanks for @santhoshnc sir guiding me !!!

🖥️ Using Cursors & Triggers in SQL: Step-by-Step Guide 🚀

PRIAN S S 24CB042 — Wed, 01 Oct 2025 15:55:23 +0000

we’ll learn how to use cursors and triggers in SQL. These are powerful tools for:

🔹 Processing data row by row

🔹 Automating actions on database events

1️⃣ Cursor: Employees with Salary > 50,000 💰

STEP 1:Create Employee Table & Insert Data
`CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES (1, 'Alice', 60000);
INSERT INTO Employee VALUES (2, 'Bob', 45000);
INSERT INTO Employee VALUES (3, 'Charlie', 75000);`

Step 2: Declare & Process the Cursor
`DECLARE @EmpName VARCHAR(50);

DECLARE employee_cursor CURSOR FOR
SELECT EmployeeName
FROM Employee
WHERE Salary > 50000;

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @EmpName;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @EmpName; -- Display employee name
FETCH NEXT FROM employee_cursor INTO @EmpName;
END

CLOSE employee_cursor;
DEALLOCATE employee_cursor;`

2️⃣ Trigger: AFTER INSERT on Students Table 📝

A cursor allows you to iterate over query results one row at a time.

Step 1: Create Employee Table & Insert Data
`CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES (1, 'Alice', 60000);
INSERT INTO Employee VALUES (2, 'Bob', 45000);
INSERT INTO Employee VALUES (3, 'Charlie', 75000);
`

Step 2: Create AFTER INSERT Trigger
CREATE TRIGGER trg_AfterStudentInsert
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit(StudentID, Action, ActionTime)
SELECT StudentID, 'Registered', GETDATE()
FROM inserted;
END;
Step 3: Insert Sample Data & Test Trigger

INSERT INTO Students VALUES (1, 'David', 'Computer Science');
INSERT INTO Students VALUES (2, 'Emma', 'Mathematics');

SELECT * FROM Student_Audit;

✅ Conclusion

Cursors 👉 Process query results row by row

Triggers 👉 Automatically perform actions on data events

💡 Real-world Use:

Use cursors for complex row-level operations

Use triggers for audit logging, notifications, and data integrity

THANKY YOU @santhoshnc sir for guiduing us and encouraging us by giving this as assignment !!!!

🚀 Understanding Database Normalization : 1F,2F,3F With SQL Examples !!!

PRIAN S S 24CB042 — Wed, 01 Oct 2025 13:17:01 +0000

📌 Introduction:

When working with databases, it’s common to face problems like data redundancy, inconsistencies, and loss of information. This happens when data is not structured properly.

That’s where Database Normalization comes in. It’s a process of organizing data in relational databases to minimize redundancy and improve integrity.

In this blog, we’ll walk through 1NF, 2NF, and 3NF, step by step, with SQL examples.

🏁 Process 1: Base Table

we start with the following table:

🔎 Process 2: Anomalies in Base Table

1.Insertion anomaly – Can’t add a new course unless a student enrolls.
2.Update anomaly – If an instructor’s name changes, we must update multiple rows.
3.Deletion anomaly – If the last student in a course leaves, info about that course is lost.

🧱 Process 3: First Normal Form (1NF)

POINT: Remove repeating groups, ensure atomic values.

CREATE TABLE StudentCourse ( StudentID INT, StudentName VARCHAR(50), CourseID VARCHAR(10), CourseName VARCHAR(50), InstructorName VARCHAR(50), PRIMARY KEY (StudentID, CourseID) );

This ensures no multi-valued attributes.

🧱 Process 4: Second Normal Form (2NF)

POINT: Remove partial dependencies (non-key attributes depending on part of the key).

`CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorName VARCHAR(50)
);

CREATE TABLE StudentCourse (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);`

🧱 Process 5: Third Normal Form (3NF)

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

`CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50)
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
`

💾 Process 6: Insert Sample Data

`INSERT INTO Students VALUES (1, 'John'), (2, 'Alice'), (3, 'Bob');

INSERT INTO Instructors VALUES (101, 'Dr. Smith'), (102, 'Dr. Miller'), (103, 'Dr. Brown');

INSERT INTO Courses VALUES ('C101', 'DBMS', 101),
('C102', 'OS', 102),
('C103', 'Networks', 103);

INSERT INTO StudentCourse VALUES (1, 'C101'), (1, 'C102'),
(2, 'C101'), (3, 'C103');`

🔗 Process 7: Query with JOIN

Now let’s get a list of all students along with their courses and instructors:
SELECT s.StudentName, c.CourseName, i.InstructorName FROM StudentCourse sc JOIN Students s ON sc.StudentID = s.StudentID JOIN Courses c ON sc.CourseID = c.CourseID JOIN Instructors i ON c.InstructorID = i.InstructorID;

🎯 Conclusion:

Through normalization, we transformed our messy base table into well-structured, relational tables.

1NF removed repeating groups.
2NF eliminated partial dependencies.
3NF removed transitive dependencies.
Now our database is clean, scalable, and reliable.

*Thanks @santhoshnc sir for guiding us and encouraging us!!!!
*

College Student & Course Management System

PRIAN S S 24CB042 — Thu, 21 Aug 2025 07:02:08 +0000

Introduction

Databases play a vital role in organizing and managing information in today’s digital world. SQL (Structured Query Language) is the standard language used to interact with relational databases. With SQL, we can create tables, insert and update records, enforce rules, and extract meaningful insights from data.

In this blog, we explore how SQL is applied using Students and Courses tables. We will learn how to:

Create and modify tables

Insert and manage records

Use constraints to maintain data integrity

Transform data with built-in functions

Summarize data using GROUP BY

Filter groups using the HAVING clause

By the end, you’ll understand not only the SQL commands but also the theory behind them — giving you both practical skills and conceptual clarity.

Working with Students and Courses Tables in Oracle SQL

In this post, let’s walk through some common SQL operations using two tables: Students and Courses. We’ll cover how to insert values, alter tables, add constraints, and run useful queries to analyze data.

1.Creating the Students Table

We start by creating a table to store student details. Each student has an ID, name, department, date of birth, and email.
For example, our Students table keeps details about learners such as their ID, name, department, date of birth, and email.
_CREATE TABLE Students ( StudentID NUMBER PRIMARY KEY, Name VARCHAR2(50) NOT NULL, Dept VARCHAR2(30), DOB DATE, Email VARCHAR2(50) UNIQUE
);_

2. Inserting Sample Student Records

Next, we’ll insert a few students, each belonging to a different department.

`INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'Computer Science', TO_DATE('2005-03-15', 'YYYY-MM-DD'), 'arun.cs@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Meena R', 'Electrical Engineering', TO_DATE('2004-11-22', 'YYYY-MM-DD'), 'meena.ee@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Vignesh S', 'Mechanical Engineering', TO_DATE('2005-07-09', 'YYYY-MM-DD'), 'vignesh.mech@example.com');`

3.Altering the Students Table

Suppose we also want to store phone numbers. We can add a new column PhoneNo with 10 characters:

_ALTER TABLE Students ADD PhoneNo VARCHAR2(10);
_
*4. Creating the Courses Table and Adding Constraints
*
Let’s say we also have a Courses table with credits. To make sure credit values are always between 1 and 5, we add a CHECK constraint.

_ALTER TABLE Courses ADD CONSTRAINT chk_credits_limit CHECK (Credits BETWEEN 1 AND 5);_

5. Displaying Student Names in Uppercase with Email Length

We can use SQL functions to transform and analyze data. For example:

_SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;_
6.Working with Course Credits

To see all the courses with their credit values, we run:

_SELECT CourseID, CourseName, Credits FROM Courses;
_
And if we want to calculate useful insights like the average credits or the total number of students, we can use:

_SELECT (SELECT AVG(Credits) FROM Courses) AS Avg_Credits, (SELECT COUNT(StudentID) FROM Students) AS Total_Students FROM dual;_

GROUP BY with HAVING in SQL

GROUP BY → Groups rows that have the same values in a column.

Aggregate functions like COUNT, SUM, AVG, MAX, MIN are applied to each group.

HAVING → Works like WHERE, but it filters groups instead of individual rows.
1. Students Count by Department

_SELECT Dept, COUNT(StudentID) AS Total_Students FROM Students GROUP BY Dept;_

Conclusion

Working with SQL is not just about writing queries — it is about understanding how data can be structured, managed, and analyzed effectively. Through our Students and Courses example, we saw how to:

Create and modify tables to match real-world needs

Insert meaningful records into the database

Enforce rules with constraints to protect data integrity

Transform and analyze data using built-in SQL functions

Summarize information with the GROUP BY clause

Apply conditions on aggregated results using the HAVING clause

THANKYOU @santhoshnc FOR GUIDING AND SUPPORTING US !!!!