DEV Community: SRIRAM PG

MONGO-DB

SRIRAM PG — Tue, 07 Oct 2025 08:39:50 +0000

MongoDB CRUD Operations - Student Database

In this assignment, we explored MongoDB CRUD (Create, Read, Update, Delete) operations using a simple students collection.

Schema

Each student document follows this structure:

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

Tasks Performed

🔹 Create

Inserted 5 student records using insertMany().

🔹 Read

Display all student records
Find students with CGPA > 8
Find students from CSE / CSBS department

🔹 Update

Updated CGPA of a student (S002)
Increased year of all 3rd year students by 1

🔹 Delete

Deleted one student by student_id
Deleted all students with CGPA < 7.5

Output

Screenshots were taken from MongoDB Atlas for execution results.
Finally, the collection was exported in JSON/CSV format for submission.

✅ This assignment gave us hands-on practice with MongoDB’s document model and CRUD operations.

Indexing, Hashing & Query Optimization

SRIRAM PG — Mon, 06 Oct 2025 13:04:10 +0000

Step-1: 1. Setting Up Your Table
First, you'll create a simple table to store student information and fill it with some data.

Create a table Students: You need a table with columns for roll number (roll_no), name, department (dept), and cgpa.

Insert at least 20 sample records: You'll add 20 or more rows of fake student data into this table.

This gives you a dataset to work with. Without any indexes, if you search for a student, the database has to look through every single row one by one, which is slow.

Step-2: B-Tree Index (For Specific Lookups)
This part shows how to speed up searches for a single, specific record.

Create a B-Tree index on the roll_no column: A B-Tree index is like the index at the back of a textbook. If you want to find a specific topic (like roll_no = 110), you don't read the whole book; you look it up in the index, which tells you the exact page number (or in this case, the row's location). This is very fast for finding unique values.

Execute a query to fetch the details of a student with roll_no = 110: When you run this query, the database will use the B-Tree index you just created to instantly find the student with roll_no = 110 instead of scanning the whole table

Step-3: B+ Tree Index (For Range-Based Searches)
This part focuses on searches that look for a range of values.

Create a B+ Tree index on the cgpa column: A B+ Tree is a special type of B-Tree where all the final data pointers are stored at the bottom level (leaf nodes) and are linked to each other. This structure makes it extremely efficient to scan through a sequence of data.

Write a query to display all students with cgpa > 8.0: This is a range query (you're not looking for one CGPA, but all CGPAs above a certain value). The B+ Tree allows the database to quickly find the first student with a CGPA of 8.0 and then just follow the linked list to get all the other students with higher CGPAs.

Step-4: Hash Index (For Exact Matches)
This part demonstrates an index that is super fast for finding records based on an exact value.

Create a Hash index on the dept column: A Hash index works like a dictionary or hash map in programming. It takes a value (e.g., 'CSBS'), converts it into a unique code (a "hash"), and uses that code to find the data's location directly.

Run a query to retrieve all students from the 'CSBS' department: This is an equality query. The hash index will instantly find all students in the 'CSBS' department. However, a hash index is not good for range queries (e.g., dept > 'CSBS').

Transactions, Deadlocks & Log Based Recovery

SRIRAM PG — Sun, 05 Oct 2025 17:12:15 +0000

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;

Step-1: Atomicity & Rollback

Step-2: Deadlock Simulation
Session 1
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
Session 2
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

One of the sessions will show:

ORA-00060: deadlock detected while waiting for resource
Step-3: Log-Based Recovery

ACID property with SQL transactions

SRIRAM PG — Sun, 05 Oct 2025 16:22:36 +0000

ACID stands for:

A – Atomicity: Ensures that a transaction is “all or nothing.”

C – Consistency: Ensures data follows rules and constraints.

I – Isolation: Ensures transactions run independently without interfering.

D – Durability: Ensures committed changes are saved even after system failure

Step-1 : Create Table and Insert Records

Step-2: Atomicity – Transaction Rollback
Simulate a transfer where you rollback midway:

Step-3: Consistency – Reject Invalid Data
Try inserting invalid data
❌ You’ll get an error because of the CHECK(balance >= 0) constraint.

Step-4: solation – Two Sessions

In Oracle Live SQL, open two sessions:
Session 1: Start a transaction and update balance but don’t commit.
Session 2: Try reading the same row.

Step-5: Durability – Commit and Restart

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

INSERT INTO Accounts VALUES (101, 'Ravi', 5000);
INSERT INTO Accounts VALUES (102, 'Meena', 3000);
INSERT INTO Accounts VALUES (103, 'Arun', 7000);

SELECT * FROM Accounts;
-- Start a transaction
SAVEPOINT start_tx;

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

-- Oops! Suppose error or cancellation occurs
ROLLBACK TO start_tx;

SELECT * FROM Accounts;
INSERT INTO Accounts VALUES (104, 'Kiran', -2000);
-- Session 1
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Don’t commit yet

-- Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 103;
COMMIT;

CURSOR + TRIGGER

SRIRAM PG — Sun, 05 Oct 2025 03:43:45 +0000

Cursor example
Step 1: Create a sample table (Employee)

Step 2: Insert some records.

Step 3: Write Cursor Program.

TRIGGER EXAMPLE STEP BY STEP

Step 4: Create an audit table.

Step 5: Create a trigger.

Step 6: Test the trigger.

Now check
SELECT * FROM Employee_Audit;
Step 7:A cursor was used to fetch and display employee records satisfying a condition, while a trigger was created to automatically log insert operations into an audit table. This demonstrates how cursors help in handling query results row by row and how triggers ensure automatic enforcement of business rules. Together, they show the importance of procedural extensions in SQL for effective database management.

-- Step 1: Create Employee table
CREATE TABLE Employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);

-- Step 2: Insert sample records
INSERT INTO Employee VALUES (1, 'Rahul', 60000);
INSERT INTO Employee VALUES (2, 'Priya', 45000);
INSERT INTO Employee VALUES (3, 'Kiran', 75000);
COMMIT;

-- Step 3: Cursor Program (fetch employees with salary > 50000)
DECLARE
v_id Employee.emp_id%TYPE;
v_name Employee.emp_name%TYPE;
v_sal Employee.salary%TYPE;

CURSOR c_emp IS
SELECT emp_id, emp_name, salary FROM Employee WHERE salary > 50000;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_name, v_sal;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' | ' || v_name || ' | ' || v_sal);
END LOOP;
CLOSE c_emp;
END;
/

-- Step 4: Create Employee_Audit table (for trigger)
CREATE TABLE Employee_Audit (
emp_id NUMBER,
action VARCHAR2(20),
action_time DATE
);

-- Step 5: Create Trigger to log inserts
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Audit VALUES (:NEW.emp_id, 'INSERT', SYSDATE);
END;
/

-- Step 6: Test the trigger
INSERT INTO Employee VALUES (4, 'Sneha', 55000);
COMMIT;

-- Step 7: Check audit log
SELECT * FROM Employee_Audit;

NORMALIZATION

SRIRAM PG — Sat, 04 Oct 2025 14:52:41 +0000

Step 1: Identify anomalies in the base table

Base table:

StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Arjun C101 DBMS Dr. Kumar 9876543210
S01 Arjun C102 Data Mining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655

Anomalies:

Insertion anomaly: Cannot insert a new instructor without assigning a student.

Update anomaly: Updating InstructorPhone requires multiple rows to be updated (e.g., Dr. Kumar’s phone).

Deletion anomaly: Deleting the last student in a course deletes course & instructor info.

Step 2: Convert to 1NF

1NF Rule: Remove repeating groups and make all attributes atomic.

Our table already has atomic values, so 1NF is essentially the same table. But for clarity, we define a primary key on StudentID + CourseID.
CREATE TABLE StudentCourse_1NF (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15),
PRIMARY KEY (StudentID, CourseID)
);

Step 3: Convert to 2NF

2NF Rule: Remove partial dependency. Here, StudentName depends only on StudentID and CourseName & Instructor info depends only on CourseID.
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID)
);

Step 4: Convert to 3NF

3NF Rule: Remove transitive dependency. InstructorPhone depends on Instructor, so create a separate Instructors table
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Step 5: Insert sample data into 3NF tables
-- Students
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

-- Instructors
INSERT INTO Instructors VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructors VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructors VALUES ('I03', 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Courses VALUES ('C101', 'DBMS', 'I01');
INSERT INTO Courses VALUES ('C102', 'Data Mining', 'I02');
INSERT INTO Courses VALUES ('C103', 'AI', 'I03');

-- Enrollments
INSERT INTO Enrollments VALUES ('S01', 'C101');
INSERT INTO Enrollments VALUES ('S01', 'C102');
INSERT INTO Enrollments VALUES ('S02', 'C101');
INSERT INTO Enrollments VALUES ('S03', 'C103');

Step 6: Query to list all students with courses and instructor names

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.Instruct

ORACLE-LIVE SQL

SRIRAM PG — Mon, 25 Aug 2025 17:50:32 +0000

-- Drop old tables if already exist
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Enrollments_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Courses_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Students_KPR';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- Create Students table
CREATE TABLE Students_KPR (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50),
Dept VARCHAR2(30),
Age NUMBER
);

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

-- Create Enrollments table
CREATE TABLE Enrollments_KPR (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER,
CourseID NUMBER,
Grade CHAR(1),
FOREIGN KEY (StudentID) REFERENCES Students_KPR(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses_KPR(CourseID)
);

-- Insert sample students
INSERT INTO Students_KPR VALUES (114, 'Ram', 'CSE', 19);
INSERT INTO Students_KPR VALUES (116, 'Sri', 'IT', 18);

-- Insert sample courses
INSERT INTO Courses_KPR VALUES (201, 'DBMS', 4);
INSERT INTO Courses_KPR VALUES (202, 'Networking', 3);
INSERT INTO Courses_KPR VALUES (203, 'Python', 5);

-- Insert enrollments
INSERT INTO Enrollments_KPR VALUES (301, 114, 201, 'A');
INSERT INTO Enrollments_KPR VALUES (302, 116, 202, 'B');
INSERT INTO Enrollments_KPR VALUES (303, 114, 203, 'A');