Forem: SASHMITHA G 24CB054

Hands-On MongoDB CRUD Operations with a College Student Schema

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 10:19:01 +0000

MongoDB is a powerful NoSQL database that allows flexible storage of JSON-like documents. In this blog, we’ll explore CRUD operations — Create, Read, Update, and Delete — using a simple college students collection.

🧱 Step 1: Create (Insert)

We start by inserting student records into the students collection. Each document follows 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)

Display all student records:

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

Find students with CGPA > 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 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 < 7.5:

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

🧠 Key Takeaways

Create: Insert documents using insertOne or insertMany.

Read: Use find with filters to query documents.

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

Delete: Remove documents with deleteOne or deleteMany.

MongoDB makes CRUD operations intuitive and flexible, especially for JSON-like data structures, making it perfect for applications like student management systems.

Thank you @santhoshnc sir for guiding me..

MongoDB #NoSQL #CRUD #DatabaseLearning #DataEngineering #DevCommunity

SQL Indexing, Hashing & Query Optimization with a Students Table

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 09:48:16 +0000

Indexes are one of the most powerful tools in SQL databases for improving query performance. In this blog, we’ll explore B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.

🧱 Step 1: Create the Students Table

We start by creating a table Students with fields 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: Insert Sample Records

Let’s insert 20 sample students across various departments with different 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: Create a B-Tree Index

B-Tree indexes are efficient for 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
The B-Tree index ensures this query runs efficiently without scanning the entire table.

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

B+ Tree indexes are ideal for range queries.

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

This allows the database to quickly locate all students satisfying the CGPA condition.

⚡ Step 5: Create a Hash Index on Department

Hash indexes are perfect for exact match lookups.

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, leveraging the hash index.

🧠 Key Takeaways

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

B+ Tree Index: Optimized for range scans; all values stored at leaf nodes.

Hash Index: Excellent for equality comparisons (e.g., department = 'CSBS').

Proper indexing dramatically improves query performance, especially with large datasets.

💡 Final Thoughts

Understanding and using indexes effectively is crucial for query optimization. By combining B-Tree, B+ Tree, and Hash indexes, you can make your database queries faster and more efficient — a key skill for any data engineer or developer.

Thank you @santhoshnc sir for guiding and supporting me..

SQL #Database #Indexing #QueryOptimization #BTree #HashIndex #DataEngineering #DevCommunity

Exploring SQL Transactions, Deadlocks & Log-Based Recovery

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 09:26:29 +0000

Databases are designed to be reliable and consistent even in complex operations. Understanding transactions, deadlocks, and log-based recovery is key to mastering database management.

In this blog, we’ll explore these concepts using a simple Accounts table.

🧱 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 treated as a single unit — either fully executed or not executed at all.

Let’s simulate a money transfer from Alice to Bob and roll it back before committing.

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 rollback, balances remain unchanged, confirming no partial update occurs.

2️⃣ Deadlock Simulation

Deadlocks happen when two transactions wait indefinitely for each other’s locks.

Simulate using two sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
-- Try to update Bob
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2;

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
-- Try to update Alice
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1;

⏳ Both sessions wait on each other — a deadlock occurs.
Most DBMS detect this and abort one transaction automatically.

3️⃣ Log-Based Recovery

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

Example:

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 the database crashes, the system can undo uncommitted changes and restore consistency.

💡 Final Thoughts

Understanding transactions, deadlocks, and recovery mechanisms is crucial for building robust and reliable database applications.

Experimenting with these SQL operations gives hands-on insight into how real-world DBMS maintain data integrity and availability.

Thank you @santhoshnc sir for guiding me..

SQL #Database #Transactions #Deadlocks #Recovery #DevCommunity #LearningByDoing

Exploring ACID Properties in SQL with Practical Queries

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 09:03:43 +0000

Databases are designed to ensure that data remains accurate, reliable, and consistent even in the face of failures.
This reliability comes from the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In this blog, let’s understand each property using a simple example: a bank Accounts table.

🧱 Step 1: Create the Accounts Table

We’ll begin by creating a table with three columns — account number, name, and balance.

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

The CHECK constraint ensures that no record can have a negative balance — maintaining data consistency.

💡 Step 2: Insert 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 that a transaction is treated as a single unit — either all changes happen, or none do.

Let’s simulate a money transfer between two accounts, then roll it back to ensure no partial changes occur.

START TRANSACTION;

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

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After rollback, both balances return to their original state.
That’s Atomicity in action — preventing partial updates.

🧭 Step 4: Consistency

Now, let’s check if our table enforces consistency by rejecting invalid data.

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

❌ This statement will fail because of the CHECK (balance >= 0) constraint.

This demonstrates Consistency, ensuring that all data adheres to predefined rules.

⚔ Step 5: Isolation

Isolation ensures that concurrent transactions don’t interfere with each other.

Try this in two different sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
-- Keep this transaction open

Session 2:

SELECT * FROM Accounts WHERE acc_no = 3;

Depending on your isolation level (e.g., READ COMMITTED, REPEATABLE READ), Session 2 may or may not see the uncommitted change.
That’s how Isolation controls visibility between transactions.

🔒 Step 6: Durability

Once a transaction is committed, its changes are permanent — even if the system crashes.

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

SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 3;

✅ After restarting your database, the updated balance for Charlie remains.
That’s Durability — ensuring committed data is never lost.

🏁 Final Thoughts

ACID properties form the foundation of reliable database systems.
By experimenting with simple SQL transactions, you can clearly see how Atomicity, Consistency, Isolation, and Durability maintain data integrity — even in complex systems.
Thank you @santhoshnc sir for guiding me.

SQL #Database #ACID #Transactions #LearningByDoing #DevCommunity

SQL Cursor and Trigger Implementation

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 08:33:45 +0000

In this post, we’ll explore two key SQL programming concepts:
1️⃣ Cursor with condition and 2️⃣ AFTER INSERT Trigger.

Both examples are implemented in Oracle Live SQL.

🌀 1️⃣ Cursor: Display Employees with Salary Greater than 50,000

A cursor is used to process each record returned by a query, one row at a time.

✅ Step 1: Create a Cursor

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

🧠 Explanation:

CURSOR emp_cursor IS → Defines the SQL query.

emp_record emp_cursor%ROWTYPE → Declares a record to store fetched rows.

OPEN, FETCH, CLOSE → Manage cursor operations.

DBMS_OUTPUT.PUT_LINE → Displays each result.

🖥 Output:

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

⚡ 2️⃣ AFTER INSERT Trigger — Student Registration Audit

A trigger automatically performs an action when a specified database event occurs.

✅ Step 2: Create AFTER INSERT Trigger

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

🧠 Explanation:

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

:NEW → Refers to the new row being added.

Student_Audit Table → Logs inserted student records automatically.

✅ Step 3: Test the Trigger

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

🖥 Output:

Trigger TRG_STUDENT_INSERT compiled
1 row inserted

And a new log entry will appear in the Student_Audit table 🎯

🏁 Summary

Feature Description Example

Cursor Used for row-by-row processing Displays employees earning > ₹50,000
Trigger Executes automatically after table events Logs new student registrations

💡 Conclusion

Cursors and Triggers are essential for database automation and record handling.
By using them effectively, you can make your SQL programs more powerful, reliable, and dynamic! 🚀
Thank you @santhoshnc sir for guiding and supporting me..

Understanding 1NF, 2NF, and 3NF in DBMS with SQL Examples

SASHMITHA G 24CB054 — Sat, 04 Oct 2025 08:00:49 +0000

When designing databases, normalization is essential to remove redundancy and anomalies (insertion, update, deletion). In this post, we’ll take a sample base table, identify anomalies, and step-by-step normalize it into 1NF, 2NF, and 3NF using SQL CREATE TABLE statements.

📌 Base Table

Let’s assume a base table with student-course-instructor details:

🚨 Anomalies in this table

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

Update anomaly: If instructor’s email changes, must update multiple rows.

Deletion anomaly: Deleting the last student in a course removes course and instructor details too.

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

➡ Eliminate repeating groups and ensure 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)

➡ Remove partial dependency (attributes depending only on part of composite key).
We separate 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)

➡ Remove transitive dependency (InstructorEmail depends on Instructor, not CourseID).
So, 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

List all students 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;

🚀 Final Thoughts

Database normalization helps eliminate redundancy, prevent anomalies, and improve efficiency. By following 1NF → 2NF → 3NF, we created a robust schema ready for real-world applications.

SQL #DBMS #DatabaseDesign #Normalization #BackendDevelopment

Thank you @santhoshnc sir for guiding me...

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

SASHMITHA G 24CB054 — Thu, 21 Aug 2025 14:46:41 +0000

Building a Simple Student Management Schema in Oracle SQL
In this post, I will walk you through a straightforward example of creating a small database schema for managing students, courses, and enrollments using Oracle SQL. This example covers table creation, constraints, data insertion, and simple queries — perfect for beginners or those looking to refresh their SQL basics.

Step 1: Creating the Tables
The schema consists of three tables: Students, Courses, and Enrollments.
Students table stores information about each student such as their ID, name, department, date of birth, email, and later a phone number.
Courses table keeps details about different courses including their ID, name, and credits.
Enrollments bridges students and courses, showing which student is enrolled in which course and the grade they received.
Here is the SQL to create these tables with appropriate primary keys and foreign key relationships:

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

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

CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

Step 2: Inserting Sample Data
Next, we add some sample student records. Notice how the date format is handled using TO_DATE with the date format YYYY-MM-DD. Also, emails are kept unique as per table constraint:
sql
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Aruna', 'Electrical Engineering', TO_DATE('2005-11-22', 'YYYY-MM-DD'), 'aruna.ee@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Navya', 'Mechanical Engineering', TO_DATE('2008-07-09', 'YYYY-MM-DD'), 'navya.mech@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Preetha', 'Computer Science', TO_DATE('2007-03-15', 'YYYY-MM-DD'), 'preetha.cs@example.com');

Step 3: Altering Tables to Add New Features
We then extend the Students table to include a phone number column:
sql
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);
And add a constraint on the Courses table to ensure the credits are between 1 and 5:
sql
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);
This ensures data integrity by restricting invalid credit values.

Step 4: Adding Courses
Courses are inserted similarly, specifying their IDs, names, and credits. The credits must comply with our check constraint:
sql
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'DBMS', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'OS', 4);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'Data Structures', 5);
Step 5: Committing the Changes
Since Oracle requires explicit commits to make the transactions permanent, we run:
sql
COMMIT;
Step 6: Querying the Data
Finally, we run some queries to retrieve useful information:

Display student names in uppercase and the length of their emails:
sql
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;
List all available courses along with their credits:
sql
SELECT CourseID, CourseName, Credits
FROM Courses;
Show all student details including the newly added phone number:
sql
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Conclusion
This simple example covers core SQL concepts like table creation, constraints, relationships, data insertion, date formatting, table alteration, and querying in Oracle SQL. It’s a solid base for building more complex educational management systems or practicing SQL skills.
Thank you @santhoshnc sir for guiding me.