DEV Community: Rohivarshini Saravanan

Exploring MongoDB CRUD Operations

Rohivarshini Saravanan — Wed, 08 Oct 2025 18:01:10 +0000

Exploring MongoDB CRUD Operations: A Beginner’s Guide

MongoDB is one of the most popular NoSQL databases out there — and for good reason! It’s built for cloud-based, scalable apps that need flexibility and speed. Instead of storing data in strict tables like traditional databases, MongoDB uses JSON-like documents that feel much closer to real-world data. For example, representing students in a college database becomes super simple and natural.

At the heart of MongoDB are the four key CRUD operations — Create, Read, Update, and Delete. These let you add new data, view it, modify it, or remove it when needed.

In this blog, we’ll get our hands dirty performing CRUD operations on a student database. You’ll learn how to:

Insert multiple student records
Fetch and filter data using queries
Update details such as CGPA or academic year
Delete specific student records

We’ll use MongoDB Atlas, the cloud-based version of MongoDB, to make everything easy and accessible. By the end, you’ll have a solid, practical understanding of how MongoDB works — and why developers love using it for modern applications.

Schema – Collection: students
Each student record (document) follows this structure:

{
"student_id": "ST001",
"name": "Aarav",
"age": 20,
"department": "AI & DS",
"year": 2,
"cgpa": 8.9
}

CREATE (INSERT)
In MongoDB Atlas → Collections → Insert Document

{
"student_id": "ST001",
"name": "Aarav",
"age": 20,
"department": "AI & DS",
"year": 2,
"cgpa": 8.9
},
{
"student_id": "ST002",
"name": "Diya",
"age": 19,
"department": "CSE",
"year": 1,
"cgpa": 9.2
},
{
"student_id": "ST003",
"name": "Rahul",
"age": 21,
"department": "ECE",
"year": 3,
"cgpa": 7.8
},
{
"student_id": "ST004",
"name": "Meera",
"age": 20,
"department": "IT",
"year": 2,
"cgpa": 9.5
},
{
"student_id": "ST005",
"name": "Vikram",
"age": 22,
"department": "MECH",
"year": 3,
"cgpa": 6.9
}

Results:
Inserted 5 documents successfully.

READ (QUERY)
Run the following queries

(a) Display all student records
find()

(b) Find all students with CGPA > 8
({ cgpa: { $gt: 8 } })

This helps identify top performers or department-based groups easily.

UPDATE (MODIFY RECORDS)

(a) Update CGPA of a specific student
{ student_id: "ST002" },
{ $set: { cgpa: 9.6 } }

Matched 1 document, modified 1 document.

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

💡 $inc automatically increments numerical fields — perfect for promotions or increments.

DELETE (REMOVE RECORDS)

(a) Delete one student by ID
({ student_id: "ST005" })

(b) Delete all students with CGPA < 7.5
({ cgpa: { $lt: 7.5 } })

Cursor and Trigger

Rohivarshini Saravanan — Wed, 08 Oct 2025 15:53:07 +0000

CURSOR

A cursor in MySQL is a database object used to retrieve and process each row of a result set individually.
It is mainly used in stored procedures when we need to perform operations row by row.

To demonstrate a cursor with condition lets create and insert into the table.

Cursor example - Display Employee Names whose Salary > 50000

Call the Cursor Procedure

TRIGGER

A trigger in MySQL is a stored program that automatically executes (fires) when a specified event occurs on a table, such as INSERT, UPDATE, or DELETE.

To demonstrates an AFTER INSERT trigger (with audit logging) lets create and insert into the table.

Create AFTER INSERT Trigger

Insert Sample Students to See Trigger in Action

Check the Audit Table

Transactions, Deadlocks & Log Based Recovery

Rohivarshini Saravanan — Mon, 06 Oct 2025 14:12:43 +0000

Transaction

A transaction is a single logical unit of work that consists of one or more SQL operations.

It must follow the ACID properties:

A – Atomicity: Either all operations succeed or none.
C – Consistency: Database remains in a valid state before & after the transaction.
I – Isolation: Transactions don’t interfere with each other.
D – Durability: Once committed, changes are permanent.

Deadlock

A deadlock occurs when two or more transactions hold locks on resources and each waits for the other to release a lock, causing the system to freeze until one transaction is rolled back.

Example:

Transaction 1 locks Alice’s account and waits for Bob’s.
Transaction 2 locks Bob’s account and waits for Alice’s → Deadlock.

Log-Based Recovery

Databases maintain logs (Write-Ahead Logs / Binary Logs) to record every transaction.

If a crash occurs before commit → use logs to undo incomplete transactions.
If after commit → logs are used to redo committed transactions.

Schema
Use a single table Accounts:

`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);`

1. Transaction – Atomicity & Rollback

Balances remain unchanged (Alice: 1000, Bob: 1500) — proves Atomicity (no partial update).

2. Deadlock Simulation

Session 1

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

Session 2

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

Result: Deadlock occurs

Database automatically detects and terminates one transaction:

ERROR 1213 (40001): Deadlock found; try restarting transaction

Log-Based Recovery

You’ll find entries for:

UPDATE (before rollback)
ROLLBACK (undo recorded in log)

Confirms Undo/Redo logging works for recovery.

Indexing, Hashing & Query Optimization

Rohivarshini Saravanan — Mon, 06 Oct 2025 13:36:26 +0000

Indexing

Indexing is a technique to speed up data retrieval in a database. It creates a separate data structure (index) that helps locate records quickly without scanning the entire table.

Common index types:

B-Tree Index – used for searching and sorting.
B+ Tree Index – efficient for range queries.
Hash Index – used for exact match lookups.

Hashing

Hashing uses a hash function to convert a key (like dept) into a hash value that points to the location of the record.

It provides fast access for equality searches but is not suitable for range-based queries.

Query Optimization

Query Optimization is the process of choosing the most efficient way to execute a query.

The optimizer analyzes indexes, joins, and conditions to minimize query execution time and improve performance.

1. Create a table Students with fields (roll_no, name, dept, cgpa)

2. Insert at least 20 sample records.

3. Create a B-Tree index on the roll_no column of the Students table.

4. Execute a query to fetch the details of a student with roll_no = 110.

5. Create a B+ Tree index on the cgpa column of the Students table.

6. Write a query to display all students with cgpa > 8.0.

7. Create a Hash index on the dept column of the Students table.

8. Run a query to retrieve all students from the 'CSBS' department.

ACID Properties

Rohivarshini Saravanan — Mon, 06 Oct 2025 13:09:28 +0000

Atomicity in Database Transactions

Atomicity is the “all or nothing” property of a database transaction.

It ensures that a transaction — which may include multiple SQL statements — is treated as a single indivisible unit of work.
That means either all operations of the transaction succeed, or none of them do.
If any part of the transaction fails (due to error, power loss, or system crash), the database rolls back to its previous stable state — ensuring that no partial updates occur.

1. Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

2. Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.
Atomicity ensures all operations in a transaction succeed or none do.

Example: Transfer ₹1000 from Ravi → Priya and rollback midway

Result: No partial transfer — both balances remain same (proving Atomicity).

3. Consistency: Try inserting a record with negative balance → should be rejected.
Consistency ensures that data integrity rules (constraints) are not violated.

Example: Try inserting a record with negative balance

This will fail because of the CHECK (balance >= 0) constraint.
Result: Database remains in a consistent state.

4. Isolation: Run two sessions at once – one updating, the other reading → observe isolation.
Isolation ensures that concurrent transactions do not interfere with each other.

Depending on isolation level:

READ UNCOMMITTED → Session 2 might see uncommitted data (dirty read)
READ COMMITTED (default) → Session 2 sees only committed data
REPEATABLE READ / SERIALIZABLE → Session 2 waits or reads the old value until Session 1 commits Result: Each session’s visibility depends on the isolation level — proving isolation behavior.

5. Durability: Commit a transaction → restart DB → ensure data persists.

Durability ensures that once a transaction is committed, changes are permanent, even after a crash or restart.

Result: The updated balance remains — showing durability.

Normalization

Rohivarshini Saravanan — Mon, 06 Oct 2025 12:42:52 +0000

What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy (repeated data) and improve data integrity (accuracy and consistency).

It divides a large, complex table into smaller, related tables and links them using foreign keys.

Why Normalization is Needed

To avoid data duplication
To ensure data consistency
To make data updates easier
To save storage space
To improve query performance

Types of Normal Forms

First Normal Form (1NF)

Each column contains atomic (single) values.
No repeating groups or arrays.
Each record should be unique (use a primary key).

Second Normal Form (2NF)

Table must be in 1NF.
No partial dependency: non-key columns must depend on the entire primary key, not part of it.

Third Normal Form (3NF)

Table must be in 2NF.
No transitive dependency: non-key attributes shouldn’t depend on other non-key attributes.

BCNF (Boyce-Codd Normal Form)
A stronger version of 3NF; every determinant must be a candidate key.

4NF (Fourth Normal Form)
Removes multi-valued dependencies.

5NF (Fifth Normal Form)
Deals with join dependencies — ensures data reconstruction without redundancy.

We shall use the following data as the starting point:

1. Identify anomalies (insertion, update, deletion) in this table.

🔸 Insertion Anomaly

We can’t add a new course until at least one student registers for it, because all course data is mixed with student data.

e.g., Can’t add a new course C104 – ML – Dr. Sharma without a student.

🔸 Update Anomaly

If an instructor’s phone number changes, it must be updated in multiple rows.

e.g., Dr. Kumar’s phone number appears twice.

🔸 Deletion Anomaly

If all students drop DBMS, deleting those rows also deletes Dr. Kumar and the course DBMS information.

2. Convert the table to 1NF and write the SQL CREATE TABLE statement for it.

✅ The table already satisfies 1NF because:

All values are atomic.
Each row is unique (based on StudentID + CourseID).

We’ll explicitly define a composite primary key (StudentID, CourseID).

3. Convert the table to 2NF and write SQL CREATE TABLE statements for the resulting tables, including primary keys.

In the current table:

StudentName depends only on StudentID
CourseName, Instructor, and InstructorPhone depend only on CourseID

So we separate Student and Course information.

4. Convert the table to 3NF and write SQL CREATE TABLE statements, including foreign keys.

Here, in Courses, InstructorPhone depends on Instructor, not on CourseID.

So we separate instructor details into a new table.

5. Insert the sample data into the normalized tables using INSERT INTO statements.

6. Write a query to list all students along with their courses and instructor names using JOINs.

SQL Queries : College Student & Course Management System

Rohivarshini Saravanan — Thu, 21 Aug 2025 08:15:10 +0000

Introduction

In today’s digital era, databases play a crucial role in managing and organizing information efficiently. SQL (Structured Query Language) is the backbone of relational databases, enabling us to create, modify, and retrieve data in a structured way.

This blog demonstrates a College Student & Course Management System using Oracle LiveSQL. Through a set of 10 SQL tasks — including DDL, DML, ALTER, constraints, functions, aggregate operations, JOINs, GROUP BY, views, and stored procedures — we will explore how to design and query relational databases effectively.Each query is explained with its purpose, making it easy for beginners to learn and practice SQL.

Use Case

Student table
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

Courses table
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Enrollments table (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

1. DDL – Create Faculty Table

CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

DDL (Data Definition Language) commands are used to define and manage database objects such as tables, views, and indexes.

Creates a new table Faculty with FacultyID as the primary key, FacultyName as NOT NULL, and Email as unique.

2. DML – Insert 3 Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Amit Sharma', 'Computer Science', TO_DATE('2002-05-12','YYYY-MM-DD'), 'amit@college.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Verma', 'Electronics', TO_DATE('2001-08-23','YYYY-MM-DD'), 'priya@college.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Mehta', 'Mechanical', TO_DATE('2000-11-05','YYYY-MM-DD'), 'rahul@college.edu');

DML (Data Manipulation Language) is used to insert, update, delete, and query records in a database.

Inserts 3 student records into the Students table with different departments.

3. ALTER TABLE – Add Phone Number

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

ALTER is used to modify an existing table structure, such as adding or deleting columns, or changing constraints.

Adds a new column PhoneNo to the Students table to store 10-digit phone numbers.

4. Defining Constraints – Limit Credits (1 to 5)

ALTER TABLE Courses
MODIFY (Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5));

Constraints ensure data integrity by restricting the values allowed in a column.

Ensures the Credits column in the Courses table only accepts values between 1 and 5.

5. SELECT with Functions – Uppercase & Email Length

SELECT Name,
UPPER(Name) AS UpperCase_Name,
LENGTH(Email) AS Email_Length
FROM Students;

SQL Functions help in manipulating and formatting data during queries.

Displays each student’s name, their name in uppercase, and the length of their email address.

6. Aggregate Functions – Avg Credits & Total Students

SELECT AVG(Credits) AS Avg_Credits
FROM Courses;

SELECT COUNT(*) AS Total_Students
FROM Students;

Aggregate functions perform calculations on multiple rows and return a single value.

Finds the average number of credits across all courses and the total number of students.

7. JOIN Operation – Student with Courses

SELECT s.Name AS Student_Name,
c.CourseName,
e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

JOIN is used to combine rows from multiple tables based on related columns.

Shows the list of students along with the courses they are enrolled in and their grades.

8. GROUP BY with HAVING – Students per Department

SELECT Dept, COUNT() AS Student_Count FROM Students
GROUP BY Dept HAVING COUNT() > 1;

GROUP BY groups rows with the same values, and HAVING filters groups based on conditions.

Groups students by department and displays only departments having more than 1 student.

9. View – StudentCoursesView

CREATE OR REPLACE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName,
c.CourseName,
e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

A View is a virtual table created from a SQL query that simplifies complex queries.

Creates a view that shows student names, their enrolled courses, and grades.

10. Stored Procedure – UpdateGrade

CREATE OR REPLACE PROCEDURE UpdateGrade(
p_StudentID IN NUMBER,
p_CourseID IN NUMBER,
p_NewGrade IN CHAR
) AS
BEGIN
UPDATE Enrollments
SET Grade = p_NewGrade
WHERE StudentID = p_StudentID AND CourseID = p_CourseID;
COMMIT;
END;

A Stored Procedure is a set of SQL statements stored in the database that can be executed when needed.

This procedure updates a student’s grade in the Enrollments table when given StudentID, CourseID, and the new grade.

Conclusion

This project shows how SQL commands like DDL, DML, ALTER, constraints, functions, joins, group by, views, and stored procedures work together to build and manage a College Student & Course Management System. By practicing these queries in Oracle LiveSQL, beginners can easily understand database design, data handling, and querying for real-world applications.