DEV Community: Sugesh

CRUD OPERATIONS IN MONGODB

Sugesh — Wed, 08 Oct 2025 10:35:41 +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.

Outcome:-

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

Create (Insert):-

Insert at least 5 student records into the students collection.

We cannot start by inserting 5 student records into our students collection together. We can create each student as separate document.

Code:-

{
student_id: "S002",
name: "Jaswant",
age: 20,
department: "CSE",
year: 1,
cgpa: 9
}

{
student_id: "S001",
name: "jai surya",
age: 23,
department: "CSE",
year: 3,
cgpa: 9
}

{
student_id: "S001",
name: "sugesh",
age: 21,
department: "CSBS",
year: 2,
cgpa: 8
}

{
student_id: "S004",
name: "prian",
age: 24,
department: "CSE",
year: 3,
cgpa: 9
}

{
student_id: "S005",
name: "raksanth",
age: 23,
department: "CSBS",
year: 4,
cgpa: 9
}

Read (Query):-

Display all student records.

Fetch all students: {}

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:

Increase the year of study for all 3rd year students by 1.

{ "year": 3 }

Delete:-

Delete one student record by student_id.

{ "student_id": "S004" }

Conclusion:-

In this blog, we explored how to perform CRUD operations in MongoDB using a real-world example of a student database. Starting from inserting records, querying based on conditions, updating multiple documents, and finally deleting specific records – we’ve covered the foundation of working with MongoDB.

This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems

Thanks to @santhoshnc sir for guiding and motivating us.

DBMS – Transactions, Deadlocks & Log-Based Recovery

Sugesh — Sat, 04 Oct 2025 08:58:13 +0000

In DBMS – Transactions, Deadlocks & Log-Based Recovery, you’ll study how transactions ensure data consistency, how deadlocks occur when multiple transactions block each other, and how log-based recovery helps the system restore data after crashes or failures.

Step 1: Create Table and Insert Data

CREATE TABLE Bank_Account (
Acc_No INT PRIMARY KEY,
Acc_Name VARCHAR(50),
Balance DECIMAL(10,2)
);

INSERT INTO Bank_Account (Acc_No, Acc_Name, Balance) VALUES
(101, 'Alice', 5000.00),
(102, 'Bob', 3000.00),
(103, 'Charlie', 7000.00),
(104, 'Diana', 4500.00);

Step 2: Transaction – Atomicity & Rollback

Task: Start a transaction to transfer ₹1000 from Alice (Acc_No 101) to Bob (Acc_No 102).
Deduct ₹1000 from Alice’s account.

Deduct 1000 from Alice

UPDATE Bank_Account
SET Balance = Balance - 1000
WHERE Acc_No = 101;

Add 1000 to Bob

UPDATE Bank_Account
SET Balance = Balance + 1000
WHERE Acc_No = 102;

RollBack Trtansaction:

ROLLBACK;

Deadlock Simulation:

Session 1

Lock Alice's row (Acc_No = 101)

UPDATE Bank_Account
SET Balance = Balance - 500
WHERE Acc_No = 101;

Now try to update Bob (but Bob is already locked by Session 2)

UPDATE Bank_Account
SET Balance = Balance + 500
WHERE Acc_No = 102;

Session 2

Lock Bob's row (Acc_No = 102)

UPDATE Bank_Account
SET Balance = Balance - 300
WHERE Acc_No = 102;

Now try to update Alice (but Alice is already locked by Session 1)

UPDATE Bank_Account
SET Balance = Balance + 300
WHERE Acc_No = 101;

Log-Based Recovery

Start the transaction
START TRANSACTION;

Deduct from Alice
UPDATE Bank_Account
SET Balance = Balance - 6000
WHERE Acc_No = 101;

Add to Bob
UPDATE Bank_Account
SET Balance = Balance + 6000
WHERE Acc_No = 102;

Commit if success
COMMIT;

Rollback if failure
ROLLBACK;

Conclusion:

Transactions: ensure the ACID properties (Atomicity, Consistency, Isolation, Durability).

Atomicity & Rollback: guarantee that either all operations of a transaction happen, or none happen.

Deadlocks: occur when two or more transactions wait for each other’s locked resources → DBMS resolves it by aborting one transaction.

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

Cursor and Trigger in DBMS

Sugesh — Fri, 03 Oct 2025 17:26:00 +0000

cursor in dbms

In DBMS, a cursor is a pointer that allows row-by-row processing of the result set of a query, enabling traversal, retrieval, and manipulation of records one at a time.

example: the employees with salary more than ₹50,000 are Bob, Charlie, and Ethan.

Step 1: Create Employee Table

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);

Step 2: Insert the Sample Data

INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 55000),
(4, 'Diana', 48000),
(5, 'Ethan', 75000);

Step 3: Declare and Process the Cursor

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

Explanation:

A cursor is used to process query results row by row, unlike normal SQL which works on the whole set at once.
The process involves declaring the cursor with a SELECT query, opening it, fetching rows one at a time, and finally closing and deallocating it.
Cursors are useful when you need to retrieve, display, or update individual records sequentially (e.g., showing employees with salary > ₹50,000).

Trigger in dbms

In DBMS, a trigger is a special type of stored procedure that is automatically executed (fired) in response to specific events on a table, such as INSERT, UPDATE, or DELETE.

Step 1: Create the Student Table

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
Age INT,
Gender VARCHAR(10),
Department VARCHAR(50)
);

Step 2: Create Student Audit Table

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

Step 3: Create AFTER INSERT Trigger

CREATE TRIGGER trg_After Insert_Student
ON Student
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName, ActionType, ActionTime)
SELECT
StudentID,
StudentName,
'INSERT',
GETDATE()
FROM Inserted; -- 'Inserted' holds newly added rows
END;

Step 4: Test Trigger

INSERT INTO Student (StudentID, StudentName, Age, Gender, Department)
VALUES (7, 'Grace', 20, 'Female', 'Computer Science');

Step 5: Verify Audit Table

SELECT * FROM Student_Audit;

Conclusion:

Triggers are automatic procedures that execute when an event (INSERT, UPDATE, DELETE) occurs on a table.
They are useful for maintaining logs, enforcing rules, and ensuring data integrity without manual intervention.
In our example, the AFTER INSERT trigger on the Student table successfully recorded every new student entry into the Student_Audit table, proving how triggers help in tracking changes automatically.

Thank You @santhoshnc sir for guiduing us and encouraging us by giving this as assignment.

Database normalization

Sugesh — Fri, 03 Oct 2025 16:25:34 +0000

Introduction:

Database normalization is the process of organizing data in a database to reduce duplication and improve consistency. It ensures each piece of information is stored only once and in the right place, making the database easier to manage and update without errors.

Step 1: Base Table

A base table is a table in a database that physically stores the data and is not derived from any other table or view.

Step 2: Identifying Anomalies

Insertion anomaly – You can’t add new data without also adding unnecessary or duplicate information.
Update anomaly – If data stored in multiple places changes, you must update all copies, or inconsistencies occur.
Deletion anomaly – Deleting one piece of data accidentally removes other important information.

Step 3: First Normal Form (1NF)

1NF (First Normal Form) means a table where all columns have atomic (indivisible) values and no repeating groups or multi-valued attributes.

CREATE TABLE Students_1NF (
StudentID INT,
Name VARCHAR(50),
Course VARCHAR(50),
Phone VARCHAR(20),
PRIMARY KEY (StudentID, Course, Phone)
);

Step 4: Second Normal Form (2NF)

No partial dependency. This usually applies when the primary key is composite (made of multiple columns).

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

Step 5: Third Normal Form (3NF)

No transitive dependencies exist → non-key attributes must depend only on the primary key, not on another non-key attribute.

DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Departments;

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50) NOT NULL
);

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Step 6: Isert Sample Data

-- Insert into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(10, 'Computer Science'),
(20, 'Mathematics'),
(30, 'Physics');

-- Insert into Students
INSERT INTO Students (StudentID, StudentName, DepartmentID) VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', 10),
(4, 'Diana', 30),
(5, 'Ethan', 20);

Step 7: Query with JOIN

SELECT
s.StudentID,
s.StudentName,
d.DepartmentName
FROM Students s
JOIN Departments d
ON s.DepartmentID = d.DepartmentID;

Conclusion:

Removes redundancy and duplicate data.

Ensures data consistency and accuracy.

Makes databases easier to update and maintain.

Improves efficiency by storing each fact only once.

Allows clean data retrieval through joins.

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

SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #DataModeling

ACID Properties with SQL Transactions in DBMS

Sugesh — Fri, 03 Oct 2025 14:54:02 +0000

The ACID properties are a set of principles that ensure reliable processing of database transactions in SQL. A transaction is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) executed as a single logical unit of work.

Create the Accounts Table and Insert the Data

CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountHolder VARCHAR(100) NOT NULL,
Balance DECIMAL(10,2) NOT NULL CHECK (Balance >= 0)
);
INSERT INTO Accounts (AccountID, AccountHolder, Balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00),
(3, 'Charlie', 2000.00);

Atomicity:

Atomicity means a transaction is an all-or-nothing operation — either all SQL statements succeed, or none take effect.

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

ROLLBACK;

Consistency:

Consistency ensures that a transaction brings the database from one valid state to another, maintaining all rules, constraints, and data integrity.

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

Isolation:

Isolation means each transaction executes as if it were the only one running, preventing interference from concurrent transactions.

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

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

Duriability:

Durability means once a transaction is committed, its changes are permanent and survive system failures.

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

Conclusion:

Atomicity → Ensures transactions are all-or-nothing, preventing partial updates.

Consistency → Guarantees the database remains in a valid state by enforcing rules and constraints.

Isolation → Keeps transactions independent, avoiding conflicts during concurrent execution.

Durability → Makes committed changes permanent, even in case of crashes or failures.

Thanks for @santhoshnc sir guiding me .

Indexing, Hashing & Query Optimization in DBMS

Sugesh — Fri, 03 Oct 2025 12:46:12 +0000

We will explore Transactions, Deadlocks, and Log-Based Recovery using SQL.
We use the Accounts table as our base schema and demonstrate ACID concepts with rollback, simulate a deadlock, and discuss log-based recovery.

definition:

Indexing: A database technique that improves data retrieval speed by creating auxiliary data structures on table columns.

Hash Indexing: An indexing method that uses a hash function to map keys to specific locations for fast equality searches.

B+ Index: A balanced tree index where all data records are stored in leaf nodes, and internal nodes hold only keys for efficient range queries.

B- Index: (B-Tree Index): A self-balancing tree index where keys and data can be stored in both internal and leaf nodes for balanced access.

Query Optimization: The process of determining the most efficient way to execute a database query using indexes, statistics, and execution plans.

Creating the Students Table:

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT,
Gender CHAR(1),
Department VARCHAR(50),
Email VARCHAR(100) UNIQUE
);

Inserting Sample Records:

INSERT INTO Student (StudentID, FirstName, LastName, Age, Gender, Department, Email) VALUES
(1, 'Alice', 'Johnson', 20, 'F', 'Computer Science', 'alice.johnson@example.com'),
(2, 'Bob', 'Smith', 22, 'M', 'Mechanical Engineering', 'bob.smith@example.com'),
(3, 'Clara', 'Williams', 19, 'F', 'Electrical Engineering', 'clara.williams@example.com'),
(4, 'David', 'Brown', 21, 'M', 'Mathematics', 'david.brown@example.com'),
(5, 'Eva', 'Davis', 23, 'F', 'Business Administration', 'eva.davis@example.com'),
(6, 'Frank', 'Miller', 20, 'M', 'Civil Engineering', 'frank.miller@example.com'),
(7, 'Grace', 'Wilson', 21, 'F', 'Computer Science', 'grace.wilson@example.com'),
(8, 'Henry', 'Moore', 22, 'M', 'Mechanical Engineering', 'henry.moore@example.com'),
(9, 'Ivy', 'Taylor', 19, 'F', 'Electrical Engineering', 'ivy.taylor@example.com'),
(10, 'Jack', 'Anderson', 24, 'M', 'Mathematics', 'jack.anderson@example.com'),
(11, 'Karen', 'Thomas', 20, 'F', 'Business Administration', 'karen.thomas@example.com'),
(12, 'Leo', 'Jackson', 21, 'M', 'Civil Engineering', 'leo.jackson@example.com'),
(13, 'Mia', 'White', 22, 'F', 'Computer Science', 'mia.white@example.com'),
(14, 'Noah', 'Harris', 23, 'M', 'Mechanical Engineering', 'noah.harris@example.com'),
(15, 'Olivia', 'Martin', 20, 'F', 'Electrical Engineering', 'olivia.martin@example.com'),
(16, 'Paul', 'Thompson', 21, 'M', 'Mathematics', 'paul.thompson@example.com'),
(17, 'Quinn', 'Garcia', 22, 'F', 'Business Administration', 'quinn.garcia@example.com'),
(18, 'Ryan', 'Martinez', 19, 'M', 'Civil Engineering', 'ryan.martinez@example.com'),
(19, 'Sophia', 'Robinson', 23, 'F', 'Computer Science', 'sophia.robinson@example.com'),
(20, 'Tom', 'Clark', 24, 'M', 'Mechanical Engineering', 'tom.clark@example.com');

Creating a B-Tree Index on roll_no:

CREATE INDEX idx_student_id
ON Student (StudentID);

Query: Fetch student with Student_ID = 10;

SELECT * FROM StudentWHERE StudentID = 10;

Creating a B+ Tree Index on age:

CREATE INDEX idx_student_age ON Student (Age);

Query: Display all students with age = 21;

SELECT * FROM Student WHERE Age = 21;

Creating an Index on dept for Fast Equality Search

CREATE INDEX idx_student_dept ON Student (Department);

Query: Retrieve all students from the Computer Science department

SELECT * FROM Students WHERE dept = 'Computer Science';

Steps Summary:-

1. Create the Student table → Defined columns like StudentID, FirstName, Age, Department, etc.

2. Insert sample records → Added 20 student records for testing queries and indexing.

3. Create a B-Tree index on StudentID → Fast lookups and range queries by student ID (already exists as PK).

4. Query using the StudentID index → Equality (=), range (BETWEEN), and sorting (ORDER BY).

5. Create a B+ Tree index on Age → Efficient for searching students by specific age or within an age range.

6. Query using the Age index → Equality search, range queries, and ordered retrieval by age.

7. Create an index on Department (Hash / B+ Tree) → Optimized for fast equality search on departments.

8. Query using the Department index → Retrieve all students in a particular department quickly.

9. Note on index choice:

B+ Tree = supports equality + range queries.
Hash Index = best for equality only.

Conclusion:

Indexes (B-Tree, B+ Tree, and Hash) significantly improve database performance by enabling faster equality, range, and ordered queries depending on the use case.

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

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

COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM

Sugesh — Thu, 21 Aug 2025 16:08:58 +0000

Managing students, courses, and enrollments is one of the most common use cases in college management systems.
In this blog, we’ll design a Student & Course Management System in SQL and walk through 10 key operations: creating tables, inserting data, altering structure, defining constraints, using functions, joins, group by, views, and stored procedures.

Step 1 – Create the Students Table

We start by creating a Students table with basic details.
`CREATE TABLE Students ( StudentID NUMBER PRIMARY KEY, StudentName VARCHAR2(50) NOT NULL, Dept VARCHAR2(30), DOB DATE, Email VARCHAR2(50) UNIQUE );

Step 2 – Insert Data into Students

Let’s insert some student records:
INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (101, 'Arun Kumar', 'CSE', DATE '2004-05-10', 'arun.kumar@college.com');

INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (102, 'Priya Sharma', 'ECE', DATE '2003-11-15', 'priya.sharma@college.com');

INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (103, 'Vikram Raj', 'Mechanical', DATE '2004-01-20', 'vikram.raj@college.com');

Step 3 – Alter Table to Add Phone Number

We can add new columns later. Here we add a 10-digit PhoneNo.
`ALTER TABLE Students ADD PhoneNo VARCHAR2(10); ![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hsdm7wyoe8lim0ohjpkf.png)

Step 4 – Create Courses Table

We’ll also need a Courses table to store course information.
CREATE TABLE Courses ( CourseID NUMBER PRIMARY KEY, CourseName VARCHAR2(50) NOT NULL, Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5) );

Step 5 – Enrollments Relationship (Many-to-Many)

A student can enroll in many courses, and a course can have many students.
So, we create an Enrollments table:
CREATE TABLE Enrollments ( EnrollID NUMBER PRIMARY KEY, StudentID NUMBER REFERENCES Students(StudentID), CourseID NUMBER REFERENCES Courses(CourseID), Grade CHAR(2) );

step 6 – Using SQL Functions

Example: display student names in uppercase and email lengths.
`SELECT UPPER(StudentName) AS StudentName_Upper,
LENGTH(Email) AS Email_Length
FROM Students;

Step 7 – Aggregate Functions

Find the average course credits and total number of students enrolled.
SELECT AVG(Credits) AS AvgCourseCredits FROM Courses SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled FROM Enrollments;
`

✅ Final Thoughts

With these steps, we have built a Student & Course Management System in SQL that includes:

Students table with constraints

Courses table

Enrollments table (many-to-many relationship)

Insert, alter, and constraints

Functions and aggregates

Joins and group by queries

Views for simplified reporting

Stored procedures for automation
Thankyou @santhoshnc sir for teaching about this and giving it as our assignment .This assignment is helpfull for me sir!!

This system forms the backbone of a college database project, and can easily be extended with Attendance, Results, or Faculty management.🚀