Forem: Rajalakshmi

Crud Operation in MongoDB

Rajalakshmi — Wed, 08 Oct 2025 16:32:12 +0000

MongoDB is a leading NoSQL database widely used for cloud-native and scalable applications. Unlike relational databases with rigid schemas, it stores data in flexible JSON-like documents, making it easy to model real-world entities such as students in a college database.

At its core, MongoDB supports CRUD operations — Create, Read, Update, and Delete — to add, retrieve, modify, and remove data.

In this blog, we’ll perform CRUD operations on a student database schema by:

Inserting multiple student records
Querying and filtering data
Updating details like CGPA and year
Deleting records with conditions
We’ll use MongoDB Atlas, the cloud-hosted service, for hands-on practice. By the end, you’ll have practical experience managing data in MongoDB — an essential skill for modern development.

🧱 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
}

⚙️ 1️⃣ 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
}

✅ Result in Atlas:
Inserted 5 documents successfully.

🔍 2️⃣ 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.

✏️ 3️⃣ 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.

🗑️ 4️⃣ DELETE (REMOVE RECORDS)

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

OUTPUT:

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

OUTPUT:

🧹 Removes low-performing or outdated records, keeping your data clean.

🎓 Learning Outcomes
By performing these CRUD operations, you’ll learn to:

Work with MongoDB Atlas Cloud Interface
Write and execute basic MongoDB queries
Update and delete data safely
Export and visualize your collections

Normalisation

Rajalakshmi — Wed, 08 Oct 2025 15:41:45 +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.

Indexing, Hashing & Query Optimization

Rajalakshmi — Mon, 06 Oct 2025 17:28:40 +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.

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.

The Backbone of Database Reliability:

Rajalakshmi — Mon, 06 Oct 2025 15:50:41 +0000

When working with relational databases, ACID properties ensure data is handled reliably, consistently, and safely.

ACID stands for:

Atomicity – All-or-nothing transactions
Consistency – Database rules are preserved
Isolation – Transactions operate independently
Durability – Committed transactions persist permanently
We’ll use an Accounts table to demonstrate each property step by step, with SQL examples you can try yourself.

⭐Step 1: Create the Accounts Table and Insert Sample Data

🧑‍💻CODE:

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0));
INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Calindra', 5800),
(102, 'Thalorin', 4200),
(103, 'Veylith', 6900);
SELECT * FROM Accounts;

Explanation:

acc_no is the primary key, ensuring each account is unique 🔑
balance has a CHECK constraint to prevent negative values ❌
Sample data gives three accounts to work with for transactions ✅
⭐Step 2: Atomicity 🧩 – All-or-Nothing Transactions

Goal: Ensure that if part of a transaction fails, no partial updates occur.
Scenario: Transfer 1500 from Calindra to Thalorin, but simulate an error midway.

🧑‍💻CODE:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1500 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1500 WHERE acc_no = 102;
ROLLBACK;
SELECT * FROM Accounts;

Observation:

After ROLLBACK, all balances remain unchanged 🔄
Prevents incomplete transactions, critical in banking systems 💳
⭐Step 3: Consistency ⚖️ – Enforcing Data Rules

Goal: Ensure the database always remains in a valid state.
Scenario: Try to insert a record with a negative balance:

🧑‍💻CODE:

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Elarion', -2000);

Result:

Explanation:

Database constraints ❌ prevent invalid data
Consistency ensures all business rules and constraints are preserved
Tip 💡: Use constraints, triggers, and validations to maintain consistent data, especially in critical systems like finance, healthcare, or inventory.

⭐Step 4: Isolation 🚧 – Transactions Don’t Interfere

Goal: Ensure simultaneous transactions don’t cause conflicts.
Scenario: Simulate two sessions:

🧑‍💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 700 WHERE acc_no = 101;
SELECT balance FROM Accounts WHERE acc_no = 101;
COMMIT;

Session 1: Update an account but do not commit yet:

Session 2: Read the same account’s balance:

Observation:

Session 2 sees the original balance until Session 1 commits 🔒
Session 1 Commit:

After commit, Session 2 sees the updated balance ✅
Tip 💡: Different isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how strict transaction isolation is.

⭐Step 5: Durability 💪 – Committed Transactions Persist

Goal: Ensure committed transactions are permanent, even after a crash or restart.
Scenario: Update Veylith’s balance and commit:

🧑‍💻CODE:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2500 WHERE acc_no = 103;
COMMIT;
SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 103;

Observation:

After restart, Veylith’s balance retains the updated value 💾
Durability is typically ensured using write-ahead logs (WAL) and disk storage
💡 Closing Thoughts

In this guide, we explored ACID properties — Atomicity 🧩, Consistency ⚖️, Isolation 🚧, and Durability 💪 — using a simple Accounts table to illustrate real-world scenarios:

Atomicity: Transactions are all-or-nothing — rollback ensures no partial updates.
Consistency: Constraints like balance >= 0 keep data valid at all times.
Isolation: Concurrent transactions operate independently, preventing conflicts.
Durability: Committed changes survive database crashes and restarts.
By understanding and applying ACID principles, you can build robust, reliable, and secure database applications — whether in banking 💳, e-commerce 🛒, healthcare 🏥, or enterprise systems 🏢.

Mastering ACID not only ensures data integrity and safety but also gives you the confidence to handle real-world transactional challenges with SQL.

✅ Remember: ACID isn’t just a theory — it’s the backbone of trustworthy, dependable database systems

Database Reliability Explained: Transactions, Deadlocks & Log-Based Recovery

Rajalakshmi — Mon, 06 Oct 2025 13:09:20 +0000

Modern databases are designed to be reliable, consistent, and fault-tolerant — even during crashes or concurrent access.
Three critical concepts that make this possible are:
Transactions, Deadlocks, and Log-Based Recovery.

This blog demonstrates how they work using a simple Accounts table example in SQL.

🧱 Step 1: Create the Accounts Table
We’ll begin by creating a basic table to simulate real-world banking operations.

To verify the data:

✅ Output:

This table will be used to understand how transactions and recovery work internally.

🔹 1️⃣ Transaction – Atomicity & Rollback

A transaction is a sequence of SQL operations executed as a single logical unit.
It follows the ACID properties — Atomicity, Consistency, Isolation, and Durability.

Let’s focus on Atomicity, which ensures that either all operations in a transaction succeed or none do.

Example: Money Transfer
Suppose Alice sends ₹500 to Bob.
We’ll perform the transfer and then roll it back before committing.

✅ Result:
Balances remain unchanged after rollback.
This confirms no partial update took place — a real demonstration of atomicity.

💡 Why It Matters:
If a power failure or crash occurs mid-transfer, rollback ensures that incomplete operations are undone automatically.

🔹 2️⃣ Deadlock Simulation

A deadlock occurs when two transactions are waiting for each other’s locked resources.
This often happens in multi-user environments where concurrent access is common.

Real-World Analogy:
Imagine two people trying to withdraw money from two linked accounts at the same time — each holding a lock on one account and waiting for the other.

Session 1

Session 2

⏳ Both sessions wait indefinitely — creating a deadlock.

How Databases Handle It:
Most DBMSs (like MySQL, Oracle, and PostgreSQL) use deadlock detection algorithms to automatically abort one of the conflicting transactions.
This allows the other transaction to continue and keeps the database consistent.

💡 Tip to Avoid Deadlocks:

Access tables in a consistent order across transactions.
Keep transactions short and simple.
Avoid unnecessary locks.
🔹 3️⃣ Log-Based Recovery

Even with transactions and deadlock control, system failures can still occur.
That’s where log-based recovery ensures Durability — the “D” in ACID.

What It Means:
Databases maintain transaction logs (Binary Logs in MySQL, WAL in PostgreSQL) that record every change.
If a crash occurs, the DBMS uses these logs to redo committed transactions and undo uncommitted ones.

Database Magic: Automating Tasks with Cursor and Trigger

Rajalakshmi — Mon, 06 Oct 2025 12:46:31 +0000

Working with databases isn’t just about storing data — it’s about automating how that data behaves.
In this blog, we’ll explore two key SQL features:

🎯 Cursor – to process query results row by row
⚙️ Trigger – to automatically perform actions after data changes

Let’s dive in! 🔍

💼 Part 1: Cursor – Display Employees with Salary > 50,000

A cursor helps you loop through query results one row at a time, perfect for selective data processing.

🧩 Step 1: Create Employee Table & Insert Data

CODE:

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

INSERT INTO Employee VALUES (1, 'Sophia', 72000.00);
INSERT INTO Employee VALUES (2, 'Ryan', 48000.00);
INSERT INTO Employee VALUES (3, 'Olivia', 83000.00);
INSERT INTO Employee VALUES (4, 'Liam', 39000.00);
INSERT INTO Employee VALUES (5, 'Emma', 65000.00);

✅ This creates an Employee table and fills it with some data.
Notice that some employees have salaries above 50,000.

⚙️ Step 2: Declare and Process the Cursor

We’ll use a cursor to fetch and display names of employees whose salary is greater than 50,000.

CODE:

DELIMITER $$

CREATE PROCEDURE DisplayHighSalaryEmployees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO empName;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('Employee: ', empName) AS Employee_Name;
END LOOP;
CLOSE emp_cursor;
END $$

DELIMITER ;

CALL DisplayHighSalaryEmployees();

🧾 Output:

✨ Explanation:

DECLARE emp_cursor defines a cursor to select employees with salary > 50,000.
The handler ensures the loop stops when all rows are fetched.
OPEN, FETCH, and CLOSE control the cursor’s lifecycle. Each employee’s name is displayed using a SELECT statement.
When you run the procedure, the cursor loops through all eligible records and prints employee names who earn more than ₹50,000.
⚡ 2️⃣ AFTER INSERT Trigger: Logging Student Registrations

A trigger automatically executes code when a certain event occurs in the database.
Here, we’ll create a trigger that logs every new student registration.

🧩 Step 1: Create Students and Audit Tables

CODE:

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR2(50),
Department VARCHAR2(50) );

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY,
StudentID INT,
Action VARCHAR2(50),
ActionTime TIMESTAMP );

⚙️ Step 2: Create the AFTER INSERT Trigger

This trigger runs automatically after a new student record is inserted.

CODE:

DELIMITER $$

CREATE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action, ActionTime)
VALUES (NEW.StudentID, 'Registered', NOW());
END $$

DELIMITER ;

✅ The :NEW keyword refers to the newly inserted row in the Students table.
✅ SYSTIMESTAMP records the exact time of registration.

🧪 Step 3: Test the Trigger

CODE:

INSERT INTO Students VALUES (201, 'Aarav', 'Computer Science');
INSERT INTO Students VALUES (202, 'Priya', 'Mechanical Engineering');
INSERT INTO Students VALUES (203, 'Karan', 'Electronics');
SELECT * FROM Student_Audit;

🧾 Output:

✅ Conclusion

Cursors and Triggers are powerful features that help automate and control database workflows.

Use cursors for row-level processing.
Use triggers for event-based automation.
🚀 Experiment with these and watch your database become smarter and more interactive!

College Student & Course Management System

Rajalakshmi — Thu, 21 Aug 2025 18:12:03 +0000

This blog shows how to create a simple College Management System using SQL on Oracle LiveSQL. It covers basic database tasks like:

Creating tables

Adding data

Setting rules with constraints

Writing queries using functions and totals

Linking t****ables with joins

Creating views

Using stored procedures

The system helps manage information about students, courses, enrollments, and faculty members.

Database Schema
The database contains four main tables: Students, Courses, Enrollments, and Faculty.

Students 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
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

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

Creates a new table Faculty to store faculty details with constraints on primary key, non-null name, and unique email.

DML
INSERT INTO Students VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-04-15', 'YYYY-MM-DD'), 'alice.johnson@uni.edu');
INSERT INTO Students VALUES (2, 'Bob Smith', 'Mathematics', TO_DATE('2001-11-23', 'YYYY-MM-DD'), 'bob.smith@uni.edu');
INSERT INTO Students VALUES (3, 'Clara Lee', 'Physics', TO_DATE('2003-07-07', 'YYYY-MM-DD'), 'clara.lee@uni.edu');

This command inserts three rows of data into the student table.

Alter Students Table — Add PhoneNo
ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

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

Add Constraint on Courses Credits
ALTER TABLE Courses
MODIFY Credits CHECK (Credits BETWEEN 1 AND 5);

Enforces that the Credits column in Courses can only have values between 1 and 5, ensuring data integrity.

SELECT with Functions — Names Uppercase & Email Length
SELECT
UPPER(Name) AS StudentName_Upper,
LENGTH(Email) AS Email_Length
FROM Students;

Demonstrates use of string functions by displaying student names in uppercase and counting the length of their email addresses.

Aggregate Functions — Average Credits & Total Enrollments
SELECT
AVG(Credits) AS Average_Credits,
(SELECT COUNT(*) FROM Enrollments) AS Total_Enrollments
FROM Courses;

Calculates the average number of credits for all courses and counts total student enrollments using aggregate functions.

JOIN — List Students with Enrolled Courses & Grades
SELECT
s.Name AS Student_Name,
c.CourseName AS Course_Name,
e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

Shows how to join multiple tables to display which students are enrolled in which courses along with their grades.

GROUP BY with HAVING — Departments with More Than 2 Students
SELECT
Dept,
COUNT() AS Student_Count
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

Groups students by department and filters to show only departments having more than two students.

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

Creates a virtual table (view) combining student names, their courses, and grades for easy repeated querying.

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;

END;
/

A stored procedure to update a student’s grade for a specific course, showing basic procedural SQL and parameter usage.

Optional: Run Stored Procedure Example
BEGIN
UpdateGrade(1, 101, 'A+');
END;
/

Summary

Insert data first to create meaningful relationships.

Use the above queries to test key SQL concepts: DDL, DML, Alter, Constraints, SELECT functions, Aggregates, JOINs, GROUP BY, Views, and Procedures.

Run queries on Oracle LiveSQL
, take screenshots of both query and output.