Forem: srivishal

Transactions, Deadlocks & Log Based Recovery

srivishal — Sun, 05 Oct 2025 19:52:23 +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.

First, let’s create a sample 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);

*Transaction – Atomicity & Rollback
*
Suppose we want to transfer 500 from Alice to Bob. Using a transaction ensures that either both operations succeed or none.

START TRANSACTION;

UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';

ROLLBACK;

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

Deadlock Simulation

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

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Alice' FOR UPDATE;
UPDATE Accounts SET balance = balance + 100 WHERE name='Bob';

START TRANSACTION;
SELECT * FROM Accounts WHERE name='Bob' FOR UPDATE;
UPDATE Accounts SET balance = balance - 100 WHERE name='Alice';

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 SET balance = balance - 300 WHERE name='Alice';

SELECT * FROM Accounts;

Log-based recovery ensures that undo operations are possible. Even if the database crashes, the system can restore a consistent state.

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.

ACID PROPERTIES

srivishal — Sun, 05 Oct 2025 19:42:16 +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.

In this blog, we’ll demonstrate ACID properties with SQL transactions using a simple Loan Management example.

Step 1: Setup the Schema

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

Enter the values to be inserted in the table

Check the result

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

Update and roll back

Atomicity ensures no partial updates happen.

Consistency

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

Alter

Consistency ensures only valid data enters the database.

Isolation

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

Isolation prevents dirty reads between transactions.

Durability

Once a transaction is committed, the changes must persist—even if the database crashes.

Durability guarantees committed data survives restarts.

Key Takeaways

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.

Indexing, Hashing, and Query

srivishal — Sun, 05 Oct 2025 18:35:29 +0000

Indexing involves creating a data structure (an index) that stores a small, ordered subset of data from a table, along with pointers to the full data records. When a query is executed, the DBMS can use this index to quickly locate the relevant data without scanning the entire table.

Hashing uses a hash function to directly map data values to their physical storage locations on disk. Instead of traversing an index structure, the hash function calculates the address of the data based on its value.

Create a table

INSERT INTO Students VALUES
(101, 'Arun', 'CSBS', 8.5),
(102, 'Mathan', 'ECE', 8.8),
(103, 'Karthik', 'MECH', 6.9),
(104, 'Hareesh', 'CSE', 9.1),
(105, 'Ravi', 'EEE', 7.2),
(106, 'Srivishal', 'CSBS', 8.8),
(107, 'Vignesh', 'IT', 8.0),
(108, 'Harish', 'CSE', 9.3),
(109, 'Deepak', 'ECE', 7.5),
(110, 'Nidheesh', 'CSBS', 9.0),
(111, 'Pradeep', 'MECH', 6.8),
(112, 'Lokhitha', 'EEE', 7.6),
(113, 'Raj', 'CSBS', 8.7),
(114, 'Divya', 'IT', 8.4),
(115, 'Saravanan', 'CSE', 9.2),
(116, 'Monika', 'ECE', 7.9),
(117, 'Ganesh', 'MECH', 6.7),
(118, 'Kavya', 'CSBS', 9.1),
(119, 'Surya', 'EEE', 7.3),
(120, 'Anitha', 'IT', 8.2);

Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno ON Students(roll_no);

Query Using B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

Create a B+ Tree Index on cgpa

SELECT * FROM Students WHERE cgpa > 8.0;

Query Using Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';

Indexing is generally preferred for queries involving range searches, sorting, or when frequent data modifications occur, as it offers more flexibility.
Hashing is highly effective for exact-match queries (equality searches) and when the primary goal is fast, direct access to individual records.

DBMS ATLAS STUDENT SCHEMA

srivishal — Sun, 05 Oct 2025 18:25:45 +0000

In this blog, We’ll walk through CRUD operations (Create, Read, Update, Delete) in MongoDB using a simple example: a student database. We’ll insert student details, query them, update academic information, and even delete records.

To make it more exciting, we’ll run these queries directly on MongoDB Atlas Cluster (cloud-based MongoDB). Along the way, I’ll include screenshots of my MongoDB Atlas dashboard and outputs so you can follow along visually.
Outcome:-

How to insert multiple documents into a collection
How to read and filter records using queries
How to update documents (single & multiple)
How to delete documents based on conditions
How CRUD fits into real-world development
Create (Insert):-

Insert at least 5 student records into the students collection.

We cannot start by inserting 5 student records into our students collection together. We can create each student as separate document.

READ(QUERY)

Find all students with CGPA > 8.

UPDATE

Update the CGPA of a specific student.

Delete:-

Delete one student record by student_id.

Conclusion:-

In this blog, we explored how to perform CRUD operations in MongoDB using a real-world example of a student database. Starting from inserting records, querying based on conditions, updating multiple documents, and finally deleting specific records – we’ve covered the foundation of working with MongoDB.

CRUD operations form the building blocks of every application, whether you’re managing users in a website, products in an e-commerce app, or students in a college system.

CURSOR AND TRIGGER

srivishal — Sun, 05 Oct 2025 17:58:54 +0000

A cursor in a Database Management System (DBMS) is a special control structure that enables traversal over the records in a result set obtained by executing a query. Unlike traditional SQL statements that operate on the entire set of rows simultaneously, a cursor allows row-by-row retrieval and processing of data. This makes it particularly useful when fine-grained, sequential manipulation of query results is required, such as performing conditional checks, applying calculations, or handling complex business logic on each individual row.

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employee VALUES
(1, 'Arjun', 45000),
(2, 'Priya', 60000),
(3, 'Kiran', 75000),
(4, 'Meera', 48000),
(5, 'Rahul', 90000);

OUTPUT

Cursor Code (Employees with Salary > 50,000)
DECLARE
CURSOR high_salary_cursor IS
SELECT EmpName FROM Employee WHERE Salary > 50000;
v_name Employee.EmpName%TYPE;
BEGIN
OPEN high_salary_cursor;
LOOP
FETCH high_salary_cursor INTO v_name;
EXIT WHEN high_salary_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE high_salary_cursor;
END;

Trigger Example (AFTER INSERT Trigger)
Create Student and Audit Tables
CREATE TABLE Studt (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Create AFTER INSERT Trigger
CREATE OR REPLACE TRIGGER student_insert_audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;

Insert Data into Studt
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

SELECT * FROM Student_Audit;

NORMALIZATION

srivishal — Sun, 05 Oct 2025 17:33:20 +0000

Guide to Database Normalization (1NF → 3NF)
Normalization is the art of organizing data efficiently — avoiding redundancy and ensuring consistency.
In this post, we’ll break down how to transform an unorganized student-course dataset into its 1NF, 2NF, and 3NF forms step by step with SQL code and outputs.

Identify Anomalies
Insertion anomaly:
Can’t insert a new course unless a student enrolls.

Update anomaly:
If Dr. Kumar’s phone number changes, we must update multiple rows.

Deletion anomaly:
If student S02 drops the course, we lose info about the DBMS course entirely.

The Raw Table — 1NF (First Normal Form)
In the beginning, everything lives in one big table — students, courses, instructors, and even phone numbers.
Creation of table

Insertion of data

Output

2NF (Second Normal Form)
To fix redundancy, we break our data into four tables:
Create separate tables for Students, Courses, Instructors, Studentcourses.

Insertion of values

Output

Convert to 3NF (Third Normal Form)
Remove transitive dependency (non-key attributes shouldn’t depend on other non-keys).

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

Final tables in 3NF:

Students(StudentID, StudentName)
Instructors(Instructor, InstructorPhone)
Courses(CourseID, CourseName, Instructor)
StudentCourses(StudentID, CourseID)

JOIN: Combine Everything

Why Normalization Matters
Prevents data redundancy
Improves data consistency
Makes updates easier and safer
Ensures relational integrity

Key Takeaway

Database normalization is not just a theory — it’s what keeps your database organized, clean, and scalable.
Every efficient application you use today, from Instagram to banking apps, relies on normalized relational databases underneath.

College Student & Course Management System

srivishal — Thu, 21 Aug 2025 05:20:22 +0000

As a beginner in software development, I wanted to explore how technology can simplify college life. In this blog, I’ll share my project on a College Student & Course Management System, where I learned how to manage student details, course enrollments, and department records using SQL and basic programming concepts. This project not only improved my coding skills but also helped me understand how real-world systems are designed to make administration easier.

Forem: srivishal

Transactions, Deadlocks & Log Based Recovery

ACID PROPERTIES

Indexing, Hashing, and Query

DBMS ATLAS STUDENT SCHEMA

CURSOR AND TRIGGER

NORMALIZATION

College Student & Course Management System

CREATE TABLE

ALTER TABLE

SELECT

INSERT TABLE

GROUP BY HAVING