Forem: DEEPAK KUMAR B 24CB007

CRUD IN MONGODB

DEEPAK KUMAR B 24CB007 — Sun, 05 Oct 2025 15:56:40 +0000

Building a Student Database using MongoDB Atlas (NoSQL)
MongoDB Atlas is a cloud-based NoSQL database that makes it easy to store, query, and manage data without worrying about servers or infrastructure.
In this guide, we’ll walk through how to create a student database, insert documents, and perform CRUD operations all using MongoDB Atlas.

Create a Database
Insert Documents into the students Collection

Read Operations (Query Data)
🧾 Display all students

Students with CGPA > 8
{ cgpa: { $gt: 8 } }

Students from Computer Science Department
{ department: "CSE" }

Update Operations

Delete Operations

Cursor +Trigger

DEEPAK KUMAR B 24CB007 — Sat, 04 Oct 2025 17:18:29 +0000

In SQL, sometimes you need more than just basic queries.
What if you want to process each row individually?
Or automatically record every change happening in your table?

That’s where Cursors and Triggers come in.

Let’s walk through both with clear examples, outputs, and real-world use cases!

A Cursor in SQL works like a pointer that allows you to process rows one by one from a query result.
It’s useful when you need conditional logic or procedural control inside your SQL block.

A Trigger is like a background assistant that automatically executes when a specific event occurs. For example, when someone inserts, updates, or deletes data.

Cursor Example
Create a table

Insert the values

Output

Cursor Code (Employees with Salary > 50,000)

Trigger Example (AFTER INSERT Trigger)

Create table Student_Audit

Create AFTER INSERT Trigger

Insert Data into table and get output

SUMMARY
1.Cursors allow row-by-row processing of query results.
2.Triggers automate actions on data changes (here, logging inserts).
Both Cursors and Triggers bring life to SQL databases.
:Use Cursors when you need fine-grained control.
:Use Triggers to automate repetitive monitoring or logging.

Database Normalization: From 1NF to 3NF

DEEPAK KUMAR B 24CB007 — Sat, 04 Oct 2025 16:42:49 +0000

n this blog, we’ll understand how to normalize a database table step-by-step from 1NF → 2NF → 3NF using clear SQL examples and outputs.

We’ll start from a single unnormalized table containing student, course, and instructor data, and then progressively decompose it to remove redundancy and anomalies.

Identify Anomalies
Insertion Anomaly: We can’t add a new student without assigning a course.
Update Anomaly: If Dr. Kumar changes his phone number, multiple rows must be updated.
Deletion Anomaly: If Arjun drops all courses, his details will be deleted too.

First Normal Form (1NF)

Create a table

Insert data

Output

Second Normal Form (2NF)

Remove partial dependencies.
Create tables separately

Insert values into the respected table

Output

Third Normal Form (3NF)

Remove transitive dependencies — InstructorPhone depends on Instructor, not directly on Course.

In this case, the InstructorPhone depends on Instructor, not directly on Course.
So we already separated Instructors, which makes this 3NF compliant.

Tables in 3NF:

1.Students(StudentID, StudentName)
2.Instructors(Instructor, InstructorPhone)
3.Courses(CourseID, CourseName, Instructor)
4,StudentCourses(StudentID, CourseID)

Query Using JOINs

This structure ensures:
No redundant instructor or course info
Easy updates and clean data relationships
Faster JOIN queries in real databases

We achieved a clean, normalized schema that eliminates anomalies and improves consistency.

ACID Properties in DBMS

DEEPAK KUMAR B 24CB007 — Sat, 04 Oct 2025 16:12:08 +0000

Databases power almost every application we use daily—from banking systems to e-commerce platforms. To ensure data remains reliable, databases follow the ACID principles: Atomicity, Consistency, Isolation, and Durability.

Setup the Schema
We’ll create a Loans table that stores customer loan detail

Enter the values

Result

ATOMICITY
All operations in a transaction must succeed or none should.

Update and roll back

Consistency

Transactions must bring the database from one valid state to another. Invalid data should not enter the system.

Alter

Isolation

Concurrent transactions should not affect each other’s intermediate results.

Isolation prevents dirty reads between transactions.

Durability

Atomicity → All or nothing.
Consistency→ Data always valid.
Isolation → Transactions don’t interfere.
Durability→ Data persists after commit.
By applying ACID principles, databases remain reliable, fault-tolerant, and consistent even in high-concurrency environments like banking systems.

Transactions, Deadlocks & Log Based Recovery

DEEPAK KUMAR B 24CB007 — Sat, 04 Oct 2025 15:45:47 +0000

Managing database transactions safely is crucial to ensure data integrity and prevent issues like partial updates or deadlocks. In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.

Let’s create a table

Insert values

Transaction – Atomicity & Rollback

ROLLBACK

UPDATE Accounts

Deadlock Simulation

Deadlocks occur when two transactions block each other waiting for resources.

RESULT
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Log-Based Recovery

Modern DBMS (MySQL/PostgreSQL) automatically maintain transaction logs. These logs help undo changes if a transaction fails.

UPDATE Accounts

SELECT * FROM Accounts;

Summary

Transactions guarantee atomicity; either all operations succeed or none.
Deadlocks occur when transactions block each other; they must be handled with care.
Log-based recovery ensures durability and recoverability.

INDEXING - HASHING - AND -QUERY

DEEPAK KUMAR B 24CB007 — Sat, 04 Oct 2025 15:14:39 +0000

Mastering Indexing in SQL: B-Tree, B+Tree, and Hash Index Examples

Indexing is one of the most powerful ways to boost the performance of your SQL queries. In this tutorial, we’ll explore B-Tree, B+Tree, and Hash indexes step-by-step using a Students table example.

We’ll:
✅ Create a Students table
✅ Insert sample data
✅ Build three types of indexes
✅ Run queries using each index
✅ Observe how indexing improves retrieval speed

Create the Table

Insert Sample Records

Create a B-Tree Index on roll_no

Query Using the B-Tree Index

Query Using the B+ Tree Index

Query Using the Hash Index

Conclusion

In this blog, we learned how to:
-Create B-Tree, B+Tree, and Hash indexes
-Execute optimized queries
-Understand which index type fits which use case

Indexes make retrieval blazing fast but also consume storage and slow down updates slightly. So, always index wisely!

STUDENT MANAGENT SYSTEM

DEEPAK KUMAR B 24CB007 — Fri, 22 Aug 2025 09:54:34 +0000

🚀 My SQL Learning Journey with Oracle Live SQL

Recently, I started practicing DBMS & SQL concepts using Oracle Live SQL. I wanted to share my progress so far — from creating tables to writing queries, altering schema, and even building views and stored procedures.

🏗️ Step 1: Creating a Table

The first thing I did was create a STUDENTS table with constraints like PRIMARY KEY, NOT NULL, and UNIQUE:

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

✅ Output: Table STUDENTS created successfully!

✏️ Step 2: Altering a Table

Later, I realized I also needed to store students’ phone numbers. Using ALTER TABLE, I added a new column:

ALTER TABLE Students
ADD PhoneNo VARCHAR(10);

✅ Output: Table STUDENTS altered.

👀 Step 3: Creating a View

To simplify data access, I created a view that joins Students, Enrollments, and Courses:

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

Now I can query StudentCoursesView directly without repeating the joins.

⚡ Step 4: Writing a Stored Procedure

I also experimented with stored procedures. For example, updating a student’s grade in a course:

CREATE PROCEDURE UpdateGrade (
    p_StudentID INT,
    p_CourseID INT,
    p_NewGrade VARCHAR2
)
AS
BEGIN
    UPDATE Enrollments
    SET Grade = p_NewGrade
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;
END;

This lets me update grades with a single procedure call.

📊 Step 5: Running Aggregate Queries

Finally, I tried aggregate queries like average credits or total students:

SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(*) AS TotalStudents FROM Students;

🎯 Key Learnings

DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE
DCL: GRANT, REVOKE
TCL: COMMIT, ROLLBACK, SAVEPOINT

Practicing these in Oracle Live SQL helped me understand not just syntax but real-world database management.

💡 Next, I plan to practice joins, subqueries, transactions, and indexing.

👉 Do you also use Oracle Live SQL or another tool for learning databases? Share your thoughts below!