DEV Community: Prabavathy Balagurusamy

🏦 Database Magic: Automating Tasks with Cursor and Trigger ✨

Prabavathy Balagurusamy — Fri, 10 Oct 2025 03:41:44 +0000

Managing a database isn’t just about keeping information — it’s about automating how data behaves and reacts.

In this article, we’ll explore two powerful SQL components that help add automation and intelligence to your database:

🎯 Cursor – processes query results one row at a time
⚙️ Trigger – executes specific actions automatically after data changes

Let’s understand how both work! 🔍

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

A cursor is a database mechanism that allows you to handle query results row by row — ideal when you need to process records individually or apply specific logic.

🧩 **Step 1: Create the Employee Table & Insert Sample Records

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 inserts some sample data.
A few employees have salaries greater than ₹50,000, which we’ll filter using a cursor.

⚙️ Step 2: Declare and Execute the Cursor

We’ll now define a stored procedure that uses a cursor to display the names of employees earning more than ₹50,000.

CODE:

DELIMITER $$

CREATE PROCEDURE DisplayHighSalaryEmployees()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE empName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

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:

The cursor selects employees whose salary exceeds ₹50,000.

The handler ensures the loop exits when there are no more rows left to fetch.

Commands like OPEN, FETCH, and CLOSE control the cursor’s operation.

For each fetched row, the employee name is displayed using a SELECT statement.

When executed, this stored procedure loops through all qualifying employees and prints their names one by one.

⚡ Part 2: AFTER INSERT Trigger – Auto Logging New Student Registrations

A trigger in SQL is a block of code that executes automatically when a specific event occurs in a table.
Here, we’ll create a trigger that logs every new student registration in a separate audit table.

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

✅ The Students table stores student information, and the Student_Audit table will maintain a log of each registration.

⚙️** Step 2: Create the AFTER INSERT Trigger**

This trigger fires automatically right after a new record is inserted into the Students table.

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 recently inserted record.
✅ NOW() stores the exact timestamp when the student was added.

🧪** 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

Both Cursors and Triggers help bring automation and logic directly into your SQL environment.

💡 Use Cursors when you need to process rows one at a time with specific conditions.
⚙️ Use Triggers when you want automatic actions to occur after table modifications.

🚀 Apply these concepts to your projects and make your databases more dynamic and responsive!

CRUD Operations in MongoDB — Student Management System

Prabavathy Balagurusamy — Thu, 09 Oct 2025 10:20:47 +0000

🎯 Objective

To gain hands-on experience performing CRUD (Create, Read, Update, Delete) operations in MongoDB Atlas using a simple college student schema.

MongoDB is a NoSQL document database that stores data in a flexible, JSON-like format — making it ideal for dynamic and structured data such as student records.

🧱 Schema Design

We’ll use a collection called students.
Each student document follows this structure:

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

⚙️Step 1: Create (Insert)

Insert at least 5 student records into the students collection.

MongoDB Query:
db.students.insertMany

{
"student_id": "S001",
"name": "Santhosh",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
},
{
"student_id": "S002",
"name": "Priya",
"age": 19,
"department": "CSE",
"year": 1,
"cgpa": 8.7
},
{
"student_id": "S003",
"name": "Karthik",
"age": 21,
"department": "IT",
"year": 3,
"cgpa": 7.4
},
{
"student_id": "S004",
"name": "Meena",
"age": 22,
"department": "ECE",
"year": 4,
"cgpa": 8.1
},
{
"student_id": "S005",
"name": "Arun",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9.2
}

✅ Result in Atlas:
Inserted 5 documents successfully.

🔍 Step 2: Read (Query)

a. Display all student records
db.students.find();

b. Find all students with CGPA > 8
db.students.find({ cgpa: { $gt: 8 } });

c. Find students belonging to the Computer Science department
db.students.find({ department: { $in: ["CSE", "CSBS"] } });

✅ Tip:
Use the MongoDB Atlas “Filter” option to test these queries easily.

✏️ Step 3: Update

a. Update the CGPA of a specific student
Example: Update Santhosh’s CGPA to 9.5
db.students.updateOne(
{ student_id: "S001" },
{ $set: { cgpa: 9.5 } }
);

Modified 1 document.

b. Increase the year of study for all 3rd-year students by 1
db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
);

✅ Output:
Shows how many documents were modified.

🗑️ Step 4: Delete

a. Delete one student record by student_id
Example: Delete student S003
db.students.deleteOne({ student_id: "S003" });

b. Delete all students having CGPA < 7.5
db.students.deleteMany({ cgpa: { $lt: 7.5 } });

✅ Output:
Displays number of deleted documents.

📤Step 5: Export the Collection

After performing all CRUD operations, you can export your collection from MongoDB Atlas:

🪶 Steps:

Go to your cluster → Collections → Select students.
Click Export Collection.
Choose JSON or CSV format.
Download your file.

📸 Deliverables

✅ MongoDB Queries (as shown above)
✅ Screenshots of query results (from Atlas)
✅ Exported students.json or students.csv file

🌟** Conclusion**
This hands-on lab demonstrates how easily MongoDB handles CRUD operations using a flexible schema.
The students collection can be expanded further to include additional attributes like address, email, or course list — giving you real-world database experience for college or project work.

💾Ensuring Database Stability: Transactions, Deadlocks & Log-Based Recovery

Prabavathy Balagurusamy — Wed, 08 Oct 2025 20:13:47 +0000

🧱Modern databases are built to stay consistent, reliable, and resilient — even when systems crash or multiple users access data at once.
Three major mechanisms that make this possible are Transactions, Deadlocks, and Log-Based Recovery.

In this guide, we’ll explore how each of these works in practice using a simple DB_Assignment table as an example.

🧱 Step 1: Set Up the DB_Assignment Table

We’ll start by creating a basic table that mimics a real banking environment where multiple accounts interact.
Once inserted, verify your data using a simple SELECT * FROM DB_Assignment; query.

✅ Output:
This table will serve as the foundation for testing transactions and recovery concepts.

🔹 1️⃣Transaction – Atomicity & Rollback

A transaction is a collection of SQL commands treated as a single logical operation.
It upholds the ACID principles — Atomicity, Consistency, Isolation, and Durability.
Here, we’ll highlight Atomicity, which guarantees that either all steps in a transaction are applied or none are — there’s no halfway point.
💸 Example: Money Transfer
Suppose Alice transfers ₹500 to Bob.
We’ll begin the transfer but then roll it back before committing to observe atomicity.

START TRANSACTION;
UPDATE DB_Assignment SET balance = balance - 500 WHERE acc_no = 1;
UPDATE DB_Assignment SET balance = balance + 500 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM DB_Assignment;

✅ Result:
After rollback, both account balances stay the same — confirming that no partial update occurred.

💡 Why It’s Important:
If a power outage or software failure happens midway, the rollback mechanism prevents incomplete changes from corrupting the database.

🔹 2️⃣Deadlock Simulation:

A deadlock happens when two transactions each hold a lock that the other needs, causing both to wait forever.
This issue often surfaces when multiple users access and modify shared data simultaneously.

💼 Real-World Example:
Think of two cashiers trying to update two linked bank accounts — one has locked Account A and the other Account B.
Each is waiting for the other to release their lock — that’s a deadlock.

-- Session 1
BEGIN;
UPDATE DB_Assignment SET balance = balance - 500 WHERE acc_no = 1; -- Locks Alice
UPDATE DB_Assignment SET balance = balance + 500 WHERE acc_no = 2; -- Waiting on Bob

-- Session 2
BEGIN;
UPDATE DB_Assignment SET balance = balance - 300 WHERE acc_no = 2; -- Locks Bob
UPDATE DB_Assignment SET balance = balance + 300 WHERE acc_no = 1; -- Waiting on Alice

⏳ Both transactions are now stuck waiting — forming a deadlock.

🧠 How Databases Handle It:

Modern DBMS (like MySQL, PostgreSQL, Oracle) automatically detect deadlocks and abort one transaction, allowing the other to complete successfully.
This maintains database consistency without manual intervention.

💡Tips to Prevent Deadlocks:

Always access tables and rows in a consistent order.
Keep transactions short and efficient.
Avoid unnecessary or long-held locks.

🔹 3️⃣Log-Based Recovery:

Even with transactions and deadlock control, unexpected failures can still occur.
This is where log-based recovery ensures the Durability part of ACID — guaranteeing that committed data survives system crashes.

⚙️ What It Does:

Databases maintain transaction logs — for instance, Binary Logs in MySQL or Write-Ahead Logs (WAL) in PostgreSQL — that track every change made to the database.
If a crash happens, the DBMS replays the log to redo committed transactions and undo incomplete ones.

START TRANSACTION;
UPDATE DB_Assignment SET balance = balance + 100 WHERE acc_no = 3;
ROLLBACK;

✅ Result:
The rollback entry gets recorded in the log.
If the system crashes, the recovery process reads this log and restores the database to the last consistent state.

💡Why It Matters:
Without logging, recovering from crashes or incomplete updates would be nearly impossible.
That’s why mission-critical systems like banking and e-commerce rely heavily on log-based recovery.

💡 Final Thoughts

Database reliability isn’t just about storing data — it’s about maintaining integrity during every possible failure or concurrency issue.

Transactions ensure atomic and consistent operations.

Deadlocks teach us how to manage concurrent processes safely.

Log-Based Recovery guarantees data durability even after unexpected crashes.

By practicing these concepts hands-on, you’ll understand how professional-grade databases achieve high fault tolerance and data safety in real-world scenarios. ⚡

The Backbone of Database Reliability: Exploring ACID in Action with SQL Transactions

Prabavathy Balagurusamy — Wed, 08 Oct 2025 19:28:42 +0000

When it comes to relational databases, the ACID principles form the bedrock of data reliability. They guarantee that every transaction behaves predictably, maintaining stability and trust in the system even when errors occur.

ACID stands for:

Atomicity 🧩 – Either all operations occur or none do
Consistency ⚖️ – Rules and constraints always stay valid
Isolation 🚧 – Parallel transactions don’t interfere
Durability 💪 – Once committed, data stays safe forever

We’ll demonstrate these concepts step by step using an Accounts table and simple SQL transactions.

⭐Step 1: Create the Accounts Table and Add Data

To begin, we’ll create a table named Accounts with a primary key and a check constraint to ensure valid balances.

🧑‍💻 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 serves as the unique identifier for each account 🔑
The CHECK constraint blocks any record with a negative balance ❌
The three sample entries give us a foundation to explore ACID behavior ✅

⭐Step 2: Atomicity 🧩 – All or Nothing

Objective: Ensure that no partial updates occur if a transaction fails midway.
Scenario: Transfer ₹1500 from Calindra to Thalorin, but simulate an interruption before completion.

🧑‍💻 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 executing ROLLBACK, all account balances remain unchanged 🔄.
This proves Atomicity, where incomplete or failed operations are completely undone — a must for financial systems💳.

⭐Step 3: Consistency ⚖️ – Data Integrity Always

Goal: Maintain valid data according to defined rules and constraints.
Scenario: Attempt to insert a record with a negative balance.

🧑‍💻 CODE:

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

Result:The system rejects the insertion immediately ❌

Explanation:

The CHECK constraint enforces that balances cannot be negative, ensuring the database remains valid.
Consistency ensures that all business logic and schema rules remain intact — no matter what the transaction tries to do.

💡 Tip: Use constraints, validations, and triggers to ensure that the database always holds meaningful, error-free data.

⭐Step 4: Isolation 🚧 – Transactions Stay Independent

Goal: Ensure that one transaction doesn’t affect another running at the same time.
Scenario: Run two sessions — one updates an account, the other tries to read it before the update commits.

🧑‍💻 CODE:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 700 WHERE acc_no = 101;
SELECT balance FROM Accounts WHERE acc_no = 101;
COMMIT;

Explanation:

Session 1: Begins the update but doesn’t commit immediately.
Session 2: Reads the balance — it still sees the old value until the first session commits.

Once Session 1 commits, the new balance becomes visible ✅.

💡 Note: Isolation levels like READ COMMITTED, REPEATABLE READ, and SERIALIZABLE determine how strictly this behavior is enforced in concurrent environments.

⭐Step 5: Durability 💪 – Committed Data Stays

Goal: Make sure that once a transaction is committed, the data remains safe even after system failures or restarts.
Scenario: Increase Veylith’s balance by ₹2500 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 restarting the database, the committed change persists 💾.
That’s Durability — once the system acknowledges a commit, it’s written permanently, usually ensured by write-ahead logs (WAL) and disk persistence mechanisms.

💡 Final Thoughts

Through this hands-on walkthrough, we demonstrated how ACID properties safeguard the integrity and reliability of databases:

Atomicity: Transactions fully succeed or completely roll back
Consistency: Data always follows the defined rules
Isolation: Transactions run without disturbing one another
Durability: Committed data survives any crash or restart

By mastering these core principles, developers can design systems that are robust, predictable, and secure, whether in banking 💳, healthcare 🏥, or e-commerce 🛒.

✅ Remember: ACID isn’t just a theory — it’s what makes databases trustworthy and dependable at scale.

🧠 Database Normalization in SQL (1NF 2NF 3NF)

Prabavathy Balagurusamy — Wed, 08 Oct 2025 17:11:05 +0000

🎯 Objective

Database normalization is one of the most important concepts in database design. It ensures that data is stored efficiently, redundancy is minimized, and data integrity is maintained.

To understand 1NF, 2NF, and 3NF, identify data anomalies, and implement normalization using SQL with proper CREATE TABLE, INSERT, and JOIN queries

By the end, you’ll understand not only how to normalize a table, but why it matters.

🧱 Base Table — The Starting Point

Let’s begin with a simple (but flawed) table design that stores students, their enrolled courses, and instructor details:

At first glance, this table might look fine — it gives us all the details in one place. But let’s look deeper.

⚠️ Data Anomalies

This table has the following anomalies:

Insertion anomaly: A new course can’t be added unless at least one student is enrolled.
Update anomaly: If the instructor’s phone number changes, it must be updated in multiple rows.
Deletion anomaly: If all students of a course are removed, information about that course and instructor is lost.

We apply Normalization — a step-by-step process of structuring the database.

🧱 Step 1: Transforming to 1NF (First Normal Form)

✅ Rule: Every column should contain indivisible (atomic) values — no groups or lists within a single field.

Our original table already meets this rule since each column stores one value per cell.
However, let’s formally define this table in SQL to establish a proper structure.

CREATE TABLE StudentCourse_1NF (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

➕ Insert Sample Data:

INSERT INTO StudentCourse_1NF VALUES
('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');

💡 Observation:
Although the data follows 1NF, there’s still repeated information — the same course and instructor details appear multiple times.

🧩 Step 2: Converting to 2NF (Second Normal Form)

✅ Rule: Remove partial dependencies — every non-key attribute must depend on the entire primary key.

In our current structure, a composite key could be (StudentID, CourseID).
But attributes like CourseName, Instructor, and InstructorPhone depend only on CourseID.
This means the table breaks the rule for 2NF.

To correct this, we’ll divide the data into three distinct tables: Student, Course, and Enrollment.

🧮 Student Table

Holds all student-specific details.

CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

INSERT INTO Student VALUES
('S01', 'Arjun'),
('S02', 'Priya'),
('S03', 'Kiran');

📘 Course Table

Contains information about each course and its instructor.

CREATE TABLE Course (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO Course VALUES
('C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('C102', 'Data Mining', 'Dr. Mehta', '9123456780'),
('C103', 'AI', 'Dr. Rao', '9988776655');

🧾 Enrollment Table

Links students to the courses they have enrolled in.

CREATE TABLE Enrollment (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

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

✅ Result:
Now, each table only includes data that depends on its own key.
We’ve removed redundant information between students and courses — though one more dependency still exists between Instructor and InstructorPhone.

🧩 Step 3: Moving to 3NF (Third Normal Form)

✅ Rule: Eliminate transitive dependencies — non-key fields shouldn’t depend on other non-key fields.

Here, InstructorPhone depends on Instructor, not on CourseID.
That’s a transitive dependency, and it violates 3NF.

To fix this, we’ll create a new table just for instructor details.

👨‍🏫 Instructor Table

CREATE TABLE Instructor (
InstructorID VARCHAR(10) PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(15)
);

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

📘** Updated Course Table (After 3NF)**

CREATE TABLE Course_3NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID VARCHAR(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

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

✅ Now:
Every column in every table depends directly on its primary key — there’s no indirect dependency left.
The schema is now fully normalized and easy to maintain.

🧮 Step 4: Retrieve Data Using JOINs

Let’s merge all the tables to display complete student-course-instructor details.

SELECT s.StudentName, c.CourseName, i.InstructorName, i.InstructorPhone
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course_3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

✅ This result neatly connects students, their courses, and instructors — all using efficient, normalized tables.

💡 Final Thoughts

Normalization isn’t just a database theory — it’s the foundation for creating efficient, accurate, and scalable database systems.

By systematically applying 1NF → 2NF → 3NF,
Removed unnecessary duplication
Solved insertion, update, and deletion problems
Organized data for easier queries and maintenance

This Student–Course example clearly demonstrates how normalization transforms messy data into a clean, reliable, and well-structured database.

Optimizing SQL Performance with Indexing, Hashing, and B+ Trees

Prabavathy Balagurusamy — Mon, 06 Oct 2025 14:53:43 +0000

🎯 Overview

In this blog, we’ll explore Indexing, Hashing, and Query Optimization concepts practically using SQL.
We’ll:

Create a Students table
Insert sample records
Create B-Tree, B+ Tree, and Hash indexes
Run queries and see how indexing improves performance

🧩 Step 1: Create the Students Table

Let’s start by creating a simple table with columns — roll_no, name, dept, and cgpa.

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa FLOAT
);

🧾 Step 2: Insert 20 Sample Records

Here’s some sample data to work with:

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Ananya', 'CSBS', 9.1),
(102, 'Rahul', 'CSE', 8.3),
(103, 'Divya', 'ECE', 7.9),
(104, 'Karthik', 'MECH', 8.5),
(105, 'Sanjay', 'EEE', 7.4),
(106, 'Priya', 'CSBS', 9.3),
(107, 'Deepak', 'CSE', 8.0),
(108, 'Meena', 'IT', 8.7),
(109, 'Vishal', 'CSE', 9.0),
(110, 'Kavya', 'CSBS', 8.8),
(111, 'Harini', 'ECE', 7.2),
(112, 'Rakesh', 'MECH', 8.1),
(113, 'Aishwarya', 'EEE', 9.2),
(114, 'Manoj', 'IT', 8.4),
(115, 'Siva', 'CSBS', 9.5),
(116, 'Sneha', 'CSE', 7.8),
(117, 'Nithin', 'MECH', 8.9),
(118, 'Gayathri', 'CSBS', 8.2),
(119, 'Arun', 'ECE', 9.0),
(120, 'Monika', 'IT', 7.5);

🌳 Step 3: Create a B-Tree Index on roll_no

Most RDBMSs (like MySQL and PostgreSQL) use B-Trees by default for indexing primary keys and unique columns.

CREATE INDEX idx_rollno_btree ON Students(roll_no);

✅ Why B-Tree?
It helps quickly locate records in sorted order. The database doesn’t need to scan every row — it navigates the index like a tree structure.

🔍 Step 4: Query with B-Tree Index

Let’s fetch details of the student with roll_no = 110:

SELECT * FROM Students WHERE roll_no = 110;

The query optimizer will automatically use the B-Tree index to quickly find the matching record.

Output:

🌲 Step 5: Create a B+ Tree Index on cgpa

In databases, B+ Trees are often used for range queries, especially in systems like PostgreSQL.

CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

✅ Why B+ Tree?
It stores all values in leaf nodes in sorted order, perfect for range queries like >, <, BETWEEN, etc.

📈 Step 6: Query Using B+ Tree

Now, display all students with CGPA greater than 8.0:

SELECT * FROM Students WHERE cgpa > 8.0;

Output:

💡 Query Optimization:
The database engine uses the B+ Tree index to skip lower CGPA values, scanning only relevant entries.

🧮 Step 7: Create a Hash Index on dept

Hash indexes are ideal for equality comparisons (=), such as finding all students from a specific department.

CREATE INDEX idx_dept_hash ON Students USING HASH (dept);

✅ Why Hash Index?
Hashing maps keys directly to fixed locations — it’s extremely fast for equality lookups.

🧠 Step 8: Query Using Hash Index

Retrieve all students from the CSBS department:

SELECT * FROM Students WHERE dept = 'CSBS';

Output:

Step 9: Test Query Performance

output:

🧠 Final Thoughts

Indexing and hashing are powerful optimization tools that can drastically improve database performance.
Choosing the right index type based on the query type — equality, range, or sorting — ensures your database runs efficiently even with millions of records.

💬 Conclusion

Optimized queries = Faster apps! ⚡
Index smartly, hash wisely, and watch your queries fly 🚀

Building a College Student & Course Management System with SQL

Prabavathy Balagurusamy — Fri, 22 Aug 2025 04:06:27 +0000

In every college, managing students, courses, and enrollments can become a real challenge. From storing student details to tracking who has enrolled in which course, a well-structured database makes life a lot easier. That’s where SQL comes in.

In this blog, let’s explore how we can design a College Student & Course Management System step by step—with both the concepts and the queries.

🏗 Designing the Foundation

We begin by creating the core tables:

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

This setup handles the many-to-many relationship between students and courses.

🔑 Creating the Faculty Table (DDL)

We can also add a faculty table to store professors’ details.

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

✍️ Inserting Data (DML)

Now, let’s add a few student records.

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2002-05-10', 'arun@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', DATE '2001-11-23', 'priya@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Singh', 'MECH', DATE '2003-02-15', 'rahul@example.com');

📞 Altering Tables

If we need to add phone numbers for students:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

✅ Defining Constraints

We restrict course credits to only 1–5:

ALTER TABLE Courses
MODIFY Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

🔍 Using Functions

Let’s see student names in uppercase and find email lengths:

SELECT UPPER(Name) AS StudentName_Upper,
LENGTH(Email) AS EmailLength
FROM Students;

📊 Aggregate Functions

Some useful statistics:

-- Average credits of courses
SELECT AVG(Credits) AS AvgCredits FROM Courses;

-- Total number of students enrolled
SELECT COUNT(DISTINCT StudentID) AS TotalEnrolled
FROM Enrollments;

🔗 JOIN Operation

List students with their courses and grades:

SELECT s.Name AS StudentName,
c.CourseName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

📑 Grouping Data

Count students in each department, showing only those with more than 2:

SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

👀 Creating a View

Simplify repeated queries with a view:

CREATE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName,
c.CourseName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

⚡ Stored Procedure

Automating grade updates:

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

🎯 Conclusion

By building this system, we learned how to:

✅ Create relational tables (Students, Courses, Enrollments, Faculty)

✅ Insert, alter, and update records

✅ Enforce constraints for data integrity

✅ Use functions and aggregates for insights

✅ Apply joins and grouping for reports

✅ Simplify with views

✅ Automate with stored procedures

This is a mini real-world academic database project—great practice for students and professionals learning SQL.