Forem: Ramya

Mastering MongoDB CRUD with a College Students Collection

Ramya — Wed, 08 Oct 2025 17:45:14 +0000

MongoDB is a dynamic NoSQL database that stores data in flexible, JSON-like documents. In this guide, we’ll dive into CRUD operations — Create, Read, Update, and Delete — using a simple college students collection.
Step 1: Create (Insert)

Let’s start by adding student records to the students collection. Each document has this structure:

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

Insert five students:

db.students.insertMany([
{ "student_id": "S001", "name": "Soniya", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
{ "student_id": "S002", "name": "Isha", "age": 21, "department": "CSE", "year": 3, "cgpa": 8.5 },
{ "student_id": "S003", "name": "Sashmi", "age": 22, "department": "ECE", "year": 4, "cgpa": 7.2 },
{ "student_id": "S004", "name": "Priya", "age": 19, "department": "CSBS", "year": 1, "cgpa": 9.3 },
{ "student_id": "S005", "name": "Alice", "age": 20, "department": "Mechanical", "year": 2, "cgpa": 6.8 }
]);

🔍 Step 2: Read (Query)

View all student records:

db.students.find().pretty();

Find students with CGPA greater than 8:

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

Find students from the Computer Science department (CSBS):

db.students.find({ department: "CSBS" }).pretty();

✏ Step 3: Update

Update the CGPA of a specific student (e.g., student_id = "S002"):

db.students.updateOne(
{ student_id: "S002" },
{ $set: { cgpa: 8.8 } }
);

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

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

🗑 Step 4: Delete

Delete a student by student_id (e.g., "S005"):

db.students.deleteOne({ student_id: "S005" });

Delete all students with CGPA less than 7.5:

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

🧠 Key Takeaways

Create: Add documents using insertOne or insertMany.

Read: Query documents using find() with filters.

Update: Modify single or multiple documents with updateOne/updateMany.

Delete: Remove documents using deleteOne or deleteMany.

MongoDB makes managing JSON-like data intuitive and flexible, making it ideal for applications like student management systems.

Boosting SQL Performance: Indexing & Query Optimization Using a Students Table

Ramya — Wed, 08 Oct 2025 17:26:03 +0000

Indexes are one of the most effective ways to enhance SQL query performance. In this tutorial, we’ll dive into B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.

🧱 Step 1: Set Up the Students Table

Let’s begin by creating a Students table with columns for roll number, name, department, and CGPA:

CREATE TABLE Students (
ROLL_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
DEPT VARCHAR2(20),
CGPA NUMBER(3,2)
);

💡 Step 2: Add Sample Data

We’ll insert 20 sample student records across different departments with varying CGPAs:

INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.9);
INSERT INTO Students VALUES (103, 'Charlie', 'MECH', 8.2);
INSERT INTO Students VALUES (104, 'David', 'CIVIL', 7.0);
INSERT INTO Students VALUES (105, 'Eva', 'CSBS', 9.0);
INSERT INTO Students VALUES (106, 'Frank', 'EEE', 6.8);
INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.3);
INSERT INTO Students VALUES (108, 'Hank', 'MECH', 7.2);
INSERT INTO Students VALUES (109, 'Ivy', 'CIVIL', 8.1);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 9.0);
INSERT INTO Students VALUES (111, 'Kim', 'EEE', 7.5);
INSERT INTO Students VALUES (112, 'Leo', 'CSBS', 9.2);
INSERT INTO Students VALUES (113, 'Mia', 'MECH', 6.9);
INSERT INTO Students VALUES (114, 'Nina', 'ECE', 8.7);
INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.4);
INSERT INTO Students VALUES (116, 'Paul', 'EEE', 7.8);
INSERT INTO Students VALUES (117, 'Quinn', 'MECH', 8.0);
INSERT INTO Students VALUES (118, 'Rose', 'CIVIL', 7.3);
INSERT INTO Students VALUES (119, 'Sam', 'ECE', 8.8);
INSERT INTO Students VALUES (120, 'Tina', 'CSBS', 9.1);

⚡ Step 3: Implement a B-Tree Index

B-Tree indexes excel at point queries and range queries:

CREATE INDEX idx_rollno_btree ON Students(ROLL_NO);

-- Fetch a student with a specific roll number
SELECT * FROM Students WHERE ROLL_NO = 110;

✅ Output: Jack, CSBS, 9.0

This index allows the query to run efficiently without scanning the entire table.

⚡ Step 4: Add a B+ Tree Index on CGPA

B+ Tree indexes are perfect for range-based queries:

-- Example: fetch students with CGPA > 8.0
SELECT * FROM Students WHERE CGPA > 8.0;

The database can quickly locate all students that meet the CGPA condition.

⚡ Step 5: Create a Hash Index on Department

Hash indexes are ideal for exact match searches:

CREATE INDEX idx_dept_hash ON Students(DEPT);

-- Fetch all students from the CSBS department
SELECT * FROM Students WHERE DEPT = 'CSBS';

✅ Result: All CSBS students are returned efficiently using the hash index.

💡 Final Thoughts

Indexes are the unsung heroes of SQL performance. Understanding when and how to use B-Tree, B+ Tree, and Hash indexes can make your queries run significantly faster. Efficient indexing is not just a technical optimization—it’s a key skill that sets apart proficient database developers and data engineers. Start experimenting with different index types, and watch your database operations become more streamlined and powerful.

🧠 Key Insights

B-Tree Index: Fast for exact lookups and sorted range queries.

B+ Tree Index: Optimized for scanning ranges; all values are stored in leaf nodes.

Hash Index: Best suited for equality searches (e.g., DEPT = 'CSBS').

Proper indexing can dramatically boost query performance, especially when dealing with large datasets.

[Boost]

Ramya — Wed, 08 Oct 2025 17:16:09 +0000

SQL Essentials: Transactions, Deadlocks, and Recovery Explained

Ramya — Wed, 08 Oct 2025 17:14:59 +0000

Databases are designed to remain consistent and reliable, even during complex operations. Mastering concepts like transactions, deadlocks, and log-based recovery is essential for effective database management.

In this tutorial, we’ll use a simple Accounts table to demonstrate these concepts.

Step 1: Create the 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);

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is executed entirely or not at all.

Example: transferring money from Alice to Bob and rolling it back:

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After the rollback, balances remain unchanged, demonstrating no partial updates occur.

2️⃣ Deadlock Simulation

Deadlocks occur when two transactions wait indefinitely for each other’s resources.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2; -- Waits for Bob

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1; -- Waits for Alice

⏳ Both sessions wait on each other, causing a deadlock. Most DBMS automatically detect and abort one transaction to resolve it.

3️⃣ Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from failures.

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

✅ The rollback is recorded in the log. If a crash occurs, the system can undo uncommitted changes and restore consistency.

Step 1: Create the 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);

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

1️⃣ Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is executed entirely or not at all.

Example: transferring money from Alice to Bob and rolling it back:

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After the rollback, balances remain unchanged, demonstrating no partial updates occur.

2️⃣ Deadlock Simulation

Deadlocks occur when two transactions wait indefinitely for each other’s resources.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2; -- Waits for Bob

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1; -- Waits for Alice

⏳ Both sessions wait on each other, causing a deadlock. Most DBMS automatically detect and abort one transaction to resolve it.

3️⃣ Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from failures.

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

✅ The rollback is recorded in the log. If a crash occurs, the system can undo uncommitted changes and restore consistency.

Exploring ACID Properties in SQL with Practical Examples

Ramya — Wed, 08 Oct 2025 16:54:25 +0000

Ensuring that data remains accurate, reliable, and consistent is at the heart of database systems. This reliability comes from the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In this post, we’ll explore each property using a simple bank Accounts table.

Step 1: Creating the Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0) -- prevents negative balance
);

The CHECK constraint ensures no account can have a negative balance, maintaining data consistency.

Step 2: Inserting Sample Records

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

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

Step 3: Atomicity
Atomicity ensures a transaction is all or nothing.

START TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

ROLLBACK;

After rollback, balances return to their original state. ✅ No partial updates occur.

Step 4: Consistency
Consistency ensures all data follows predefined rules.

INSERT INTO Accounts VALUES (104, 'David', -2000);

❌ This fails because negative balances are not allowed — maintaining data integrity.

Step 5: Isolation
Isolation prevents concurrent transactions from interfering with each other.

Session 1:

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

Session 2:

SELECT * FROM Accounts WHERE acc_no = 3;

Depending on your isolation level, uncommitted changes may or may not be visible.

Step 6: Durability
Durability ensures that once committed, changes persist, even after system failures.

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

✅ After restarting the database, Charlie’s updated balance remains intact.

ACID properties in action — keeping your data safe, reliable, and consistent.

🧩Summary :

Databases rely on ACID properties — Atomicity, Consistency, Isolation, and Durability — to ensure data remains accurate, reliable, and consistent.

Using a simple bank Accounts table, we can see these principles in action:

Atomicity: Transactions are all-or-nothing, preventing partial updates.

Consistency: Data always adheres to predefined rules.

Isolation: Concurrent transactions don’t interfere with each other.

Durability: Committed changes are permanent, even after system failures.

Understanding ACID is essential for building robust and reliable SQL databases.

Mastering SQL Cursors & Triggers in Oracle

Ramya — Wed, 08 Oct 2025 16:23:03 +0000

In this post, we’ll dive into two essential SQL concepts:
1️⃣ Using a Cursor with a condition
2️⃣ Implementing an AFTER INSERT Trigger

All examples are built in Oracle Live SQL.

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

A cursor allows you to process query results row by row, giving you precise control over each record.

Example:

DECLARE
CURSOR emp_cursor IS
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;

emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.EmpName ||
', Salary: ' || emp_record.Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

How it works:

CURSOR emp_cursor IS → Defines the query.

emp_record emp_cursor%ROWTYPE → Creates a record to store each fetched row.

OPEN, FETCH, CLOSE → Manage cursor operations.

DBMS_OUTPUT.PUT_LINE → Prints each result.

Output:

Employee: Arjun, Salary: 60000
Employee: Kiran, Salary: 80000

⚡ 2️⃣ AFTER INSERT Trigger: Student Registration Audit

A trigger automatically runs in response to specific table events.

Example:

CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;
/

How it works:

AFTER INSERT ON Students → Trigger fires after a new record is inserted.

:NEW → References the newly inserted row.

Student_Audit → Automatically logs new student entries.

Test it:

INSERT INTO Students (StudentID, StudentName)
VALUES ('S01', 'Arjun');

Output:

Trigger TRG_STUDENT_INSERT compiled
1 row inserted

✅ A new log entry is now in Student_Audit.

🏁 Summary

In this post, we explored SQL Cursors and AFTER INSERT Triggers in Oracle:

Cursor: Processes query results row by row, allowing precise control over each record. We used it to display employees earning more than ₹50,000.

Trigger: Automatically executes when a specified database event occurs. We implemented an AFTER INSERT trigger to log new student registrations in an audit table.

🎯 Key Takeaways

Feature Purpose Example

Cursor Row-by-row processing of query results List employees with Salary > ₹50,000
Trigger Automatic action after table events Log new student registrations into Student_Audit

[Boost]

Ramya — Wed, 08 Oct 2025 16:00:18 +0000

EXPLORING NORMALIZATION

Ramya — Wed, 08 Oct 2025 15:48:44 +0000

Database normalization is crucial for eliminating redundancy and preventing anomalies such as insertion, update, and deletion issues. In this guide, we’ll walk through a sample base table, identify its anomalies, and normalize it step by step into 1NF, 2NF, and 3NF using SQL CREATE TABLE statements.
📌 Base Table

Consider a base table that contains student-course-instructor information:

🚨 Anomalies in the Table

Insertion Anomaly: Cannot add a new course until a student enrolls.

Update Anomaly: Changing an instructor’s email requires updating multiple rows.

Deletion Anomaly: Deleting the last student enrolled in a course removes course and instructor information too.

✅ Step 1: Convert to 1NF (First Normal Form)

Goal: Eliminate repeating groups and ensure all attributes have atomic values.

CREATE TABLE StudentCourses_1NF (
StudentID INT,
StudentName VARCHAR(100),
CourseID VARCHAR(10),
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);

✅ Step 2: Convert to 2NF (Second Normal Form)

Goal: Remove partial dependency (attributes depending only on part of a composite key).
Separate the table into Students, Courses, and Enrollments:

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

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);

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

✅ Step 3: Convert to 3NF (Third Normal Form)

Goal: Remove transitive dependency (e.g., InstructorEmail depends on Instructor, not CourseID).
We create a separate Instructors table:

CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(100),
InstructorEmail VARCHAR(100)
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
📝 Insert Sample Data

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

INSERT INTO Instructors VALUES
(1, 'Dr. Smith', 'smith@uni.edu'),
(2, 'Dr. Lee', 'lee@uni.edu'),
(3, 'Dr. Clark', 'clark@uni.edu');

INSERT INTO Courses VALUES
('C101', 'DBMS', 1),
('C102', 'Networks', 2),
('C103', 'AI', 3);

INSERT INTO Enrollments VALUES
(1, 'C101'),
(2, 'C102'),
(1, 'C103');
🔗 Query with JOINS

To list all students along with their courses and instructors:

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;

✅ Key Takeaways

Normalization reduces redundancy: Properly structured tables save storage and prevent duplicate data.
Avoid anomalies: Normalization prevents insertion, update, and deletion anomalies.
Step-wise approach:
1NF: Ensures atomic values and removes repeating groups.

2NF: Removes partial dependency by separating data into multiple tables.

3NF: Eliminates transitive dependency to ensure each attribute depends only on the primary key.

Improved data integrity: Normalized databases are easier to maintain and update.
Joins become powerful: Once normalized, SQL joins can easily combine data across tables for meaningful queries.

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM.

Ramya — Sat, 23 Aug 2025 05:10:04 +0000

In this blog we are going to Building a Simple Student-Course Management System with Oracle SQL
Managing student enrollments and course information is a common task for educational institutions and developers building academic applications. We'll create tables for students, courses, and enrollments, insert sample data, and explore some interesting queries.

Step 1: Creating the Tables
We start by creating three tables—Students, Courses, and Enrollments. Each table is designed with appropriate data types, constraints, and relationships.

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

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

-- Create Enrollments table
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);
The Students table contains the student's name, department, date of birth, email (unique), and an ID as a primary key.

The Courses table has course details including credits.

The Enrollments table records which student is enrolled in which course, along with their grades. It establishes foreign key relationships to ensure referential integrity.

Step 2: Adding Data Integrity Features
To improve data quality, let’s add a phone number column to Students and a check constraint on the Credits column in Courses:

sql
-- Add PhoneNo column to Students
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

-- Add check constraint for Credits in Courses (must be 1 to 5)
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);
Step 3: Inserting Sample Data
Insert some sample student and course data to work with:

sql
-- Insert students with different departments
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (2, 'TOM HOLLAND', 'ECE', TO_DATE('11/22/2005', 'MM/DD/YYYY'), 'tom@gmail.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (3, 'ANDREW', 'IT', TO_DATE('7/9/2008', 'MM/DD/YYYY'), 'andrew@gmail.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'EMMA', 'CSBS', TO_DATE('3/15/2007', 'MM/DD/YYYY'), 'emma@gmail.com');

-- Insert courses with credits
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (101, 'DE', 3);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (102, 'CE', 4);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (103, 'Data science', 5);

-- Commit changes
COMMIT;
Step 4: Querying Data with Useful Functions
Some simple queries to analyze the data:

sql
-- Display student names in uppercase and length of emails
SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;

-- Show all courses with their credits
SELECT CourseID, CourseName, Credits FROM Courses;

-- Display all student details, including the new PhoneNo column
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo FROM Students;

Conclusion
This simple database management system covers the core concepts like table creation with constraints, foreign keys for relationships, data insertion, and basic querying in Oracle SQL.

Thank you @santhoshnc sir for guiding me.