DEV Community: Ilakkiya

MongoDB CRUD Operations

Ilakkiya — Sun, 05 Oct 2025 04:12:41 +0000

Databases don’t always have to be tables and rows — welcome to the world of MongoDB, where data lives as flexible documents!
In this post, we’ll explore how to perform the four basic operations — Create, Read, Update, and Delete (CRUD) — using a simple college student schema in MongoDB.

We’ll be using MongoDB Atlas, a free cloud-based platform that allows you to create and manage MongoDB databases online — no installation needed!

Steps to Set Up.

Head over to MongoDB Atlas and sign up for a free account.
Create a new Cluster (choose the free M0 shared tier).
Once your cluster is ready, go to Collections → Create Database.
Name your database collegeDB and add a collection named students.
Open the Atlas Data Explorer — this is where you’ll run all your MongoDB commands.

Schema: Student Collection

We’ll use a collection called students, where each document looks like this:

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

Create (Insert Documents)

We’ll begin by inserting five sample student records.

Read (Query Documents)

Once data is inserted, let’s retrieve it using different query conditions.
Display All Student Records

db.students.find();

Find Students with CGPA > 8

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

Find Students from the Computer Science Department

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

Update (Modify Documents)

Update a Specific Student’s CGPA

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

Increase the Year of Study for All 3rd-Year Students

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

Delete (Remove Documents)

Delete One Student by ID

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

Delete All Students with CGPA < 7.5

Summary

✔ Create → Add new student documents
✔ Read → Query and filter documents efficiently
✔ Update → Modify existing records dynamically
✔ Delete → Safely remove unwanted data

MongoDB makes database handling simple, fast, and flexible — perfect for modern applications that evolve with changing data needs.

mongodb #nosql #crud #database #learning #devcommunity #webdev

Indexing, Hashing & Query Optimization in SQL

Ilakkiya — Sun, 05 Oct 2025 02:29:14 +0000

Have you ever noticed how some database queries return results almost instantly — even when the table has thousands of rows?
That’s the magic of Indexing, Hashing, and Query Optimization.
In this post, we’ll see how these techniques make databases faster and smarter — using a simple Students table as our example.

Step 1 — Creating the Students Table

Let’s start by creating the table and inserting 20 sample records to work with.

Step 2 — B-Tree Index on roll_no

B-Tree indexing is the default and most common type of index in relational databases.
It helps in quickly locating rows based on range queries or sorted data.

CREATE INDEX idx_rollno_btree
ON Students(roll_no);

Now, let’s use that index to fetch a student’s details efficiently.

Result: The database uses the B-Tree index to find the record in milliseconds.

Step 3 — B+ Tree Index on cgpa

A B+ Tree index is an enhancement of the B-Tree — perfect for range-based queries, such as finding all students with CGPA above a threshold.

Result: The database quickly retrieves qualifying students without scanning the entire table.

Step 4 — Hash Index on dept

Hash indexing is great for exact matches, such as looking up a department by name.
It uses hash functions to map keys directly to data locations — extremely fast for equality checks.

HASH index using an in-memory table:

A MEMORY table is stored in RAM. If the database server restarts, the MEMORY table disappears. Use it only for temporary, very fast lookups.

Result: The database directly jumps to all CSBS records using the hash key — no full scan required.

Step 5 — Query Optimization in Action

Indexes and hashing dramatically improve performance by reducing search time and optimizing query execution plans.
To see the difference, you can run:

EXPLAIN SELECT * FROM Students WHERE cgpa > 8.0;

Result: The plan shows the use of indexes, confirming optimized access paths.

Summary

B-Tree Index – Ideal for range and sorted queries
B+ Tree Index – Efficient for range lookups with dense leaf nodes
Hash Index – Perfect for equality comparisons
Query Optimization – The key to high-speed, low-latency data retrieval

Indexes are like shortcuts for the database — they make searching faster, queries smarter, and performance smoother.

SQL #Indexing #Hashing #QueryOptimization #Database #Learning #DevCommunity

Transactions, Deadlocks & Log-Based Recovery in SQL

Ilakkiya — Sat, 04 Oct 2025 18:31:39 +0000

Database systems are designed to handle multiple operations efficiently and reliably.
In this post, we’ll explore three key concepts that keep databases safe and consistent: Transactions, Deadlocks, and Log-Based Recovery.
We’ll use a simple Accounts table to demonstrate these concepts in action.

Creating the Accounts Table

We start with a simple table to represent bank accounts and insert sample data.

Transactions — Atomicity & Rollback

Definition:
A transaction is a sequence of operations that must be executed completely or not at all.

Example: Transfer Money with Rollback

Objective: Transfer 700 from Diana to Ethan, but rollback before committing.

Result: No partial update occurs; balances remain unchanged.

Deadlock Simulation

Definition:
A deadlock occurs when two transactions block each other, waiting for resources the other holds.

Scenario

Session 1: Lock Diana's account & try to update Ethan's account

Session 2:Lock Ethan's account & Try to update Diana's account

Both sessions wait indefinitely, creating a deadlock that the DBMS detects and resolves.

Log-Based Recovery

Definition:
Logging ensures that every database change is recorded, so the system can undo or redo transactions during failures.

Example: Transaction with Undo Logging

Result: The rollback operation is recorded in the log, ensuring data consistency.

Summary

Transactions: Ensure atomicity — all or nothing.
Deadlocks: Occur when transactions wait on each other; DBMS resolves them automatically.
Log-Based Recovery: Maintains a history of changes to recover from failures.

These mechanisms are fundamental to robust, reliable, and fault-tolerant database systems.

SQL #Database #Transactions #Deadlocks #Recovery #Learning #DevCommunity

ACID Properties in SQL Transactions

Ilakkiya — Sat, 04 Oct 2025 17:57:59 +0000

Managing data might seem straightforward, but behind every secure and reliable database are rules that ensure nothing breaks.
Let’s dive into ACID properties and see how SQL transactions guarantee that our operations are safe, consistent, and permanent — demonstrated with a simple Accounts table.

Creating the Accounts Table

We start by creating a table to hold account information and inserting some sample data.

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

INSERT INTO Accounts VALUES
(201, 'Arjun', 12000),
(202, 'Diya', 9500),
(203, 'Vikram', 7000);

Atomicity — All or Nothing

Definition:
A transaction must be atomic: either all operations succeed, or none do.

Example: Money Transfer with Rollback

Consistency — Maintain Data Rules

Definition:
Transactions must leave the database in a valid state, respecting all constraints.

Example: Prevent Negative Balance

Result: The database rejected the invalid insert, maintaining consistent data.

Isolation — Transactions Don’t Interfere

Definition:
Concurrent transactions should not affect each other’s operations.

Example: Simulate Two Sessions

Session 1 (User A)

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 201;
SELECT * FROM Accounts WHERE acc_no = 201;

Session 2 (User B)

SELECT * FROM Accounts WHERE acc_no = 201;

Result: Session 2 doesn’t see the uncommitted changes from Session1.
Now User A commits and checks again:

Change visible only after commit → Isolation works.

Durability — Permanent Once Committed

Definition:
Once a transaction is committed, its changes persist even if the database restarts.

Example: Commit and Verify

START TRANSACTION;
UPDATE Accounts SET balance = balance + 1500 WHERE acc_no = 203;  -- Vikram gains ₹1500
COMMIT;

Restart the database and run:

Summary

✅ Atomicity: No partial updates
✅ Consistency: Only valid data allowed
✅ Isolation: Transactions don’t interfere
✅ Durability: Changes survive system failures

ACID properties ensure databases are reliable, robust, and safe, making SQL transactions predictable and trustworthy.

SQL #Transactions #ACID #DatabaseDesign #Learning #DevCommunity

SQL Cursors and Triggers

Ilakkiya — Sat, 04 Oct 2025 17:18:27 +0000

This post explores Cursor and Trigger in SQL with practical examples in a way that’s easy to understand.
This post explores Cursor and Trigger in SQL with practical examples in a way that’s easy to understand.

Cursor — Processing Rows with a Condition

A cursor allows you to process query results row by row, especially when you need to apply logic or conditions that can’t be handled by a single SQL statement.

Objective

Display employee names whose salary is greater than 50,000 using a cursor.

Trigger — Automating an Action After INSERT

A trigger automatically executes a set of SQL statements when a specific database event (like INSERT, UPDATE, or DELETE) occurs.
Here, we’ll use an AFTER INSERT trigger to maintain a student registration log.

Objective

Whenever a new record is added to the Students table, automatically insert an entry into the Student_Audit table.

**Testing the Trigger

**
When a new student record is inserted, the trigger automatically creates a log entry in the Student_Audit table.

Summary

✔ Cursor – Processes data row by row for conditional operations.
✔ Trigger – Automates database actions after an event.

Both help make SQL more dynamic, controlled, and responsive to real-time data changes.

sql #database #cursor #trigger #learning #devbeginners

Understanding 1NF, 2NF, and 3NF Using SQL — Step-by-Step Implementation

Ilakkiya — Sat, 04 Oct 2025 16:31:55 +0000

Database normalization is a process used to organize data in a database efficiently.It helps to remove redundancy, improve data integrity, and make data maintenance easier.
This post walks through the process of converting a database into 1NF, 2NF, and 3NF, showing each stage with practical SQL CREATE TABLE and INSERT examples.

Base Table

Let’s start with the following unnormalized Student Enrollment table:

This table stores student details, courses, and instructor information together.

Data Anomalies

This unnormalized table can cause three types of anomalies:

Insertion anomaly:
Cannot add a new course unless a student is enrolled in it.

Update anomaly:
If an instructor’s phone number changes, it must be updated in multiple rows.

Deletion anomaly:
Deleting a student record might also remove information about a course or instructor.

To overcome these issues, we will normalize the table step by step.

Step 1: First Normal Form (1NF)

Each column should contain atomic (single) values.
No repeating groups or arrays are allowed.

CREATE TABLE Student_1NF ( StudentID INT, StudentName VARCHAR(50), Course VARCHAR(50), Instructor VARCHAR(50), InstructorPhone VARCHAR(15) );

Step 2: Second Normal Form (2NF)

The table must be in 1NF.
All non-key attributes must depend on the entire primary key, not just a part of it.

CREATE TABLE Students ( StudentID VARCHAR(10) PRIMARY KEY, StudentName VARCHAR(100) NOT NULL );

CREATE TABLE Courses_2NF ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(100) NOT NULL, Instructor VARCHAR(100) NOT NULL, InstructorPhone VARCHAR(15) NOT NULL );

CREATE TABLE Enrollments_2NF ( StudentID VARCHAR(10) NOT NULL, CourseID VARCHAR(10) NOT NULL, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses_2NF(CourseID) );

INSERT INTO Students (StudentID, StudentName) VALUES ('S01','Arjun'), ('S02','Priya'), ('S03','Kiran');

INSERT INTO Courses_2NF (CourseID, CourseName, Instructor, InstructorPhone) VALUES ('C101','DBMS','Dr. Kumar','9876543210'), ('C102','Data Mining','Dr. Mehta','9123456780'), ('C103','AI','Dr. Rao','9988776655');

INSERT INTO Enrollments_2NF (StudentID, CourseID) VALUES ('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');

Step 3: Third Normal Form (3NF)

The table must be in 2NF.
There should be no transitive dependency (non-key attributes should not depend on other non-key attributes).

Here, InstructorPhone depends on Instructor, not on the key — so we separate instructor details into a new table.

CREATE TABLE StudentDetails ( StudentID VARCHAR(10) PRIMARY KEY, StudentName VARCHAR(100) NOT NULL ) ENGINE=InnoDB;

CREATE TABLE InstructorDetails ( InstructorID VARCHAR(10) PRIMARY KEY, InstructorName VARCHAR(100) NOT NULL, InstructorPhone VARCHAR(15) NOT NULL ) ENGINE=InnoDB;

CREATE TABLE CourseDetails ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(100) NOT NULL, InstructorID VARCHAR(10) NOT NULL, FOREIGN KEY (InstructorID) REFERENCES InstructorDetails(InstructorID) ) ENGINE=InnoDB;

CREATE TABLE StudentCourseEnrollments ( StudentID VARCHAR(10) NOT NULL, CourseID VARCHAR(10) NOT NULL, PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES StudentDetails(StudentID), FOREIGN KEY (CourseID) REFERENCES CourseDetails(CourseID) ) ENGINE=InnoDB;

INSERT INTO StudentDetails (StudentID, StudentName) VALUES ('S11','Rohan'), ('S12','Meera'), ('S13','Vikram'), ('S14','Sneha');

INSERT INTO InstructorDetails (InstructorID, InstructorName, InstructorPhone) VALUES ('I11','Dr. Sharma','9871112233'), ('I12','Dr. Nair','9122223344'), ('I13','Dr. Kapoor','9988771122'), ('I14','Dr. Joshi','9899988776');

INSERT INTO CourseDetails (CourseID, CourseName, InstructorID) VALUES ('C201','Operating Systems','I11'), ('C202','Computer Networks','I12'), ('C203','Machine Learning','I13'), ('C204','Cloud Computing','I14');

INSERT INTO StudentCourseEnrollments (StudentID, CourseID) VALUES ('S11','C201'), ('S11','C202'), ('S12','C201'), ('S13','C203'), ('S14','C204');

At this stage, the database is fully normalized into Third Normal Form.

Displaying All Students with Their Courses and Instructors

Conclusion

Through these steps, we successfully:

Identified anomalies in the base table
Converted it to 1NF, 2NF, and 3NF
Used SQL commands to design normalized tables
Joined the data to display meaningful results

Database normalization not only removes redundancy but also ensures that data remains consistent and easy to maintain.

sql #database #normalization #learning #assignments

College Database Management System – Oracle LiveSQL

Ilakkiya — Wed, 20 Aug 2025 16:28:20 +0000

This project demonstrates how to design and query a mini College Database Management System (DBMS) using Oracle LiveSQL. The aim is to understand how real-world college data like students, faculty, courses, and enrollments can be efficiently stored, managed, and retrieved using SQL.

🔹 Schema Design

We create four core tables with proper constraints to maintain data integrity:

Faculty – stores teacher details such as FacultyID, FacultyName, Department, and Email.

Students – contains student details including StudentID, Name, Department, Date of Birth, Email, and Phone number.

Courses – holds subject details like CourseID, CourseName, and Credits (restricted between 1–5).

Enrollments – acts as a bridge (many-to-many relationship) between Students and Courses, mapping students to their registered courses along with their grades.

This schema ensures relational integrity by applying primary keys, unique keys, foreign keys, and constraints.

🔹 Sample Queries

Once the schema is created, we run different types of queries to explore SQL operations:

String Functions & Aggregates

SELECT UPPER(Name), LENGTH(Email) FROM Students;
SELECT AVG(Credits) AS AvgCredits, COUNT(*) AS TotalStudents
FROM Courses, Students;

These queries display student names in uppercase, measure email lengths, and calculate the average course credits.

Joins
To display the mapping between students, courses, and their grades:

SELECT s.Name, c.CourseName, e.Grade

FROM Students s

JOIN Enrollments e ON s.StudentID = e.StudentID

JOIN Courses c ON e.CourseID = c.CourseID;

Group By + Having
To analyze the student distribution across departments:

SELECT Dept, COUNT() AS StudentCount

FROM Students

GROUP BY Dept

HAVING COUNT() > 2;

This ensures only departments with more than two students are displayed.

🔹** Views & Procedures**

Views
A view named StudentCoursesView is created to simplify repeated queries:

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;

This allows users to easily fetch student-course-grade data without rewriting complex joins.

Stored Procedures
To update student grades efficiently, we write a procedure:

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;

END;

** Conclusion**

This project demonstrates a complete database workflow:

Designing relational schemas with constraints

Inserting and modifying data

Executing SQL queries with functions, joins, and grouping

Creating views for simplicity

Automating updates with stored procedures

By working on this mini-project in Oracle LiveSQL, we gain practical insights into how a College DBMS operates. It not only strengthens SQL knowledge but also builds a strong foundation for handling larger real-world databases.