DEV Community: TERERA FAITH TANAKA 24CB070

ACID property with SQL transactions

TERERA FAITH TANAKA 24CB070 — Tue, 07 Oct 2025 13:57:35 +0000

Introduction

When working with databases, ensuring the reliability and correctness of transactions is essential. This is where the ACID properties come into play. ACID stands for Atomicity, Consistency, Isolation, and Durability which are the core principles that guarantee trustworthy database transactions.
In this blog we are to focus on these ACID properties:

we will start by creating a database called acid_test then create a table named Accounts with the following structure:

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

Insert 3 Sample Rows
INSERT INTO Accounts VALUES (1, 'Alice', 5000);
INSERT INTO Accounts VALUES (2, 'Bob', 3000);
INSERT INTO Accounts VALUES (3, 'Charlie', 7000);

Displaying the data

Exploring ACID Properties

1. Atomicity: All or Nothing Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
-- Suppose an error happens here or we decide to rollback
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

ROLLBACK; -- Cancel the transaction midway

Both updates are part of one transaction.
If any step fails or we explicitly roll back, no partial update remains.
Balances for Alice and Bob remain unchanged.

2. Consistency: Data Integrity Constraints

INSERT INTO Accounts VALUES (4, 'David', -500);

The database rejects the insert due to the CHECK (balance >= 0) constraint.
This enforces consistency, ensuring data adheres to business rules.

3. Isolation: Concurrent Transactions

Open two database sessions:

Session 1: Update Alice's balance.
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
-- Do NOT commit yet

Session 2: Read Alice's balance.
SELECT balance FROM Accounts WHERE acc_no = 1;

Session 2 may see the old balance (before update), depending on the isolation level.
This prevents dirty reads and shows how isolation controls concurrent transaction visibility.

4. Durability: Persisting Committed Data

After committing a transaction:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
COMMIT;

Now, restart the database server.
Query the Accounts table again:
SELECT * FROM Accounts WHERE acc_no = 3;

The data can still be viewed.

In conclusion ACID
By understanding and leveraging these principles, developers and database administrators can build applications that maintain data integrity even under complex and concurrent operations .Huge thanks to @santhoshnc for the guidance.

MongoDB

TERERA FAITH TANAKA 24CB070 — Sun, 05 Oct 2025 19:37:25 +0000

Introduction

In this blog, we will gain practical experience performing CRUD (Create, Read, Update, Delete) operations in MongoDB. We will use a simple college student schema in a students collection and run queries on MongoDB Atlas.
Schema (Collection: students)
Each student document should follow the structure below:
{
"student_id": "S001",
"name": "Santhosh",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
}

Create (Insert)
CREATE A DATABASE CALLED COLLEGE_STUDENTS AND A COLLECTION CALLED STUDENTS

Insert at least 5 student records into the students collection.

Read (Query)
Display all student records.

Find all students with CGPA > 8.

Find students belonging to the Computer Science department.

Update

Update the CGPA of a specific student.
modify cgpa of Faith to 8.3

Increase the year of study for all 3rd year students by 1
before

after

Delete

Delete one student record by student_id.

*Delete all students having CGPA < 7.5.
*

Conclusion

Mastering CRUD operations in MongoDB is fundamental for building efficient and scalable applications that rely on flexible, document-based data storage. By understanding how to Create, Read, Update, and Delete data effectively, developers can leverage MongoDB’s powerful querying capabilities and schema flexibility to manage data in real-time applications with ease. Whether you’re building a small project or a complex system, a solid grasp of these operations ensures that your data management is both robust and performant.Thanks to @santhoshnc for the guidance.

Cursor + Trigger

TERERA FAITH TANAKA 24CB070 — Sun, 05 Oct 2025 19:01:07 +0000

Introduction

In this blog we will explore two powerful SQL concepts, Cursors and Triggers. These tools help you handle row-by-row processing and automate actions in response to data changes, respectively.

A cursor allows you to fetch and process rows returned by a query one at a time, which is useful for row-level operations that cannot be done in a single SQL statement.

CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
salary INT
);

INSERT INTO Employee VALUES(1, 'John Doe', 60000);
INSERT INTO Employee VALUES(2, 'Jane Brown', 45000);
INSERT INTO Employee VALUES(3, 'Jim Brown', 52000);
INSERT INTO Employee VALUES(4, 'Lisa White', 48000);
INSERT INTO Employee VALUES(5, 'Mike Black', 75000);

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

DECLARE emp_cursor CURSOR FOR
SELECT emp_name FROM Employee WHERE salary > 50000;

DECLARE @emp_name VARCHAR(50);

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @emp_name;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @emp_name; -- or use SELECT in some systems
FETCH NEXT FROM emp_cursor INTO @emp_name;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Trigger
AFTER INSERT Trigger (Student Table)
Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table to keep track of the registration.

summary
Cursors help process data row-by-row when set-based operations don’t suffice.

Triggers automate actions in response to table changes, great for audit logging or enforcing business rules.Thanks to @santhoshnc for the guidance

Transactions, Deadlocks & Log Based Recovery Schema

TERERA FAITH TANAKA 24CB070 — Sun, 05 Oct 2025 18:29:05 +0000

Introduction

Managing data integrity and concurrency is critical in any database system. In this blog, we are going to explore key database concepts ie transactions with atomicity and rollback, deadlock simulation, and log-based recovery

Schema used
Accounts 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);

Transactions: Atomicity & Rollback
A transaction is a sequence of operations performed as a single logical unit of work. It ensures atomicity, meaning all operations succeed or none at all.

BEGIN;
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK;

After applying rollback
The balances remain unchanged because the rollback undid the updates, demonstrating atomicity ie no partial updates happened.
SELECT * FROM Accounts;

Deadlock Simulation
A deadlock happens when two sessions wait for each other’s locked resources indefinitely.
Open two separate sessions (e.g., two terminal windows connected to the database).
Session 1
BEGIN;
UPDATE Accounts SET balance = balance + 0 WHERE name = 'Alice';

--(will wait if locked by Session 2)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Session 2
BEGIN;
UPDATE Accounts SET balance = balance + 0 WHERE name = 'Bob';

-- (will wait if locked by Session 1)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

Both sessions wait for each other indefinitely ie a deadlock

Log-Based Recovery

[mysqld]
log_bin = mysql-bin
BEGIN;

UPDATE Accounts SET balance = balance - 300 WHERE name = 'Charlie';

ROLLBACK;

The update and rollback operations are recorded in the log.

During recovery, the database uses the log to undo changes from uncommitted transactions ensuring data consistency.

Conclusion

Understanding these concepts helps build reliable, concurrent database applications that maintain data integrity even under failure or heavy multi-user access.I extend my gratitude to @santhoshnc for the guidance through deadlocks.

Indexing, Hashing & Query Optimization

TERERA FAITH TANAKA 24CB070 — Sun, 05 Oct 2025 17:49:07 +0000

Introduction

To start with the definations,well indexing is a data structure technique used to improve the speed of data retrieval operations in a database,hashing is used to map data to a fixed-size value (ie hash) using a hash function and Query Optimization is the process of improving the efficiency of SQL queries so that they run faster and consume fewer resources (CPU, memory, disk I/O).
When working with large datasets, performance becomes critical.
In this blog post, we will explore different types of indexes in SQL using a simple Students table:

1.B-Tree Index
2.B+ Tree Index
3.Hash Index

We will walk through creating indexes and using queries to demonstrate their impact.
Firstly we will create a table called students as shown below:
CREATE TABLE Students ( roll_no INT PRIMARY KEY, name VARCHAR(50), dept VARCHAR(10), cgpa DECIMAL(3,2) );

Inserting at least 20 sample records

`INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(102, 'Bob', 'MECH', 7.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(103, 'Charlie', 'EE', 9.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(104, 'David', 'CSBS', 6.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(105, 'Eva', 'MECH', 8.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(106, 'Frank', 'EE', 7.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(107, 'Grace', 'CSBS', 9.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(108, 'Helen', 'MECH', 8.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(109, 'Ian', 'EE', 7.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(110, 'Jack', 'CSBS', 8.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(111, 'Karen', 'MECH', 7.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(112, 'Leo', 'EE', 8.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(113, 'Mona', 'CSBS', 9.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(114, 'Nate', 'MECH', 6.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(115, 'Olivia', 'EE', 8.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(116, 'Paul', 'CSBS', 8.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(117, 'Quinn', 'MECH', 7.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(118, 'Rita', 'EE', 9.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(119, 'Sam', 'CSBS', 8.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(120, 'Tina', 'MECH', 7.8);

Create a B-Tree index on the roll_no column of the Students table and Execute a query to fetch the details of a student with roll_no = 116.

CREATE INDEX idx_roll_no ON Students (roll_no);

SELECT * FROM Students WHERE roll_no = 110;

Create a B+ Tree index on the cgpa column of the Students table.Write a query to display all students with cgpa > 8.0.

CREATE INDEX idx_cgpa ON Students (cgpa);

SELECT * FROM Students WHERE cgpa > 8.0;

Create a Hash index on the dept column of the Students table and Run a query to retrieve all students from the 'CSBS' department.

CREATE INDEX idx_dept_hash ON Students USING HASH (dept);
SELECT * FROM Students WHERE dept = 'CSBS';

CONCLUSION

Indexing is a cornerstone of database performance optimization. By choosing the right type of index you tend to speed up data retrieval and improve your application's responsiveness.
Mastering indexing not only enhances performance but also helps maintain scalable and efficient databases as your data grows.Thanks to @santhoshnc for the guidance.

DATABASE NORMALIZATION

TERERA FAITH TANAKA 24CB070 — Sun, 05 Oct 2025 17:03:14 +0000

Introduction

Database normalization is an essential part of designing efficient, scalable, and maintainable databases. In this post, we will focus mainly on 1NF ,2NF and 3NF using a simple student-course example shown below and implement each stage using SQL CREATE TABLE statements.

Anomalies in table above

Insertion anomaly: You can’t insert a new course unless a student enrolls in it.
Update anomaly: If Prof. Smith changes name, you need to update multiple rows.

3.Deletion anomaly: If Charlie drops all courses, info about Chemistry and Prof. Lee might be lost.

First Normal Form (1NF)

Eliminate repeating groups and ensure atomicity.
In our case, the base table is already atomic but we will enforce a primary key.

CREATE TABLE StudentCourse_1NF ( StudentID INT, StudentName VARCHAR(50), CourseID VARCHAR(10), CourseName VARCHAR(50), InstructorName VARCHAR(50), PRIMARY KEY (StudentID, CourseID) );

Second Normal Form (2NF)

Eliminate partial dependencies.
Only non-key attributes should depend on the full primary key.

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

COURSE TABLE
CREATE TABLE Course ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(50), InstructorName VARCHAR(50) );

RELATIONSHIP BETWEEN THE TWO
CREATE TABLE StudentCourse ( StudentID INT, CourseID VARCHAR(10), PRIMARY KEY (StudentID, CourseID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN KEY (CourseID) REFERENCES Course(CourseID) );

Third Normal Form (3NF)

Eliminate transitive dependencies.
Attributes should only depend on the primary key, not on other non-key attributes.
Instructor table
CREATE TABLE Instructor ( InstructorID INT PRIMARY KEY, InstructorName VARCHAR(50) );

Updated course table
CREATE TABLE Course ( CourseID VARCHAR(10) PRIMARY KEY, CourseName VARCHAR(50), InstructorID INT, FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID) );

Student table

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

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

Inserting values into the tables

Inserting into student table
INSERT INTO Student (StudentID, StudentName) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

Inserting into instructors table
INSERT INTO Instructor (InstructorID, InstructorName) VALUES (1, 'Prof. Smith'), (2, 'Prof. Johnson'), (3, 'Prof. Lee');

Inserting into course table
INSERT INTO Course (CourseID, CourseName, InstructorID) VALUES ('C101', 'Math', 1), ('C102', 'Physics', 2), ('C103', 'Chemistry', 3);

Enrolment
INSERT INTO StudentCourse (StudentID, CourseID) VALUES (1, 'C101'), (1, 'C102'), (2, 'C101'), (3, 'C103');

JOINS QUERY

SELECT s.StudentName, c.CourseName, i.InstructorName FROM StudentCourse sc JOIN Student s ON sc.StudentID = s.StudentID JOIN Course c ON sc.CourseID = c.CourseID JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion

By applying the principles of 1NF, 2NF, and 3NF, we transform a flat, repetitive table into a set of well-structured, interrelated tables that ensure data integrity and ease of maintenance.Through normalization, we not only improve the consistency and accuracy of data but also make our database scalable and easier to query. The process may seem complex at first, but breaking it down step-by-step, as we did with the student-course example, makes it straightforward and practical.
Mastering normalization empowers you to design robust databases that stand the test of time, reduce errors, and improve performance.

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

TERERA FAITH TANAKA 24CB070 — Mon, 25 Aug 2025 17:00:17 +0000

Introduction

Databases are the backbone of any college or university management system, for tracking students, courses and grades, SQL makes it possible to store and retrieve data efficiently. In this blog,I implemented such a system using Oracle LiveSQL. It includes concepts like create tables, insert data, enforce constraints, run queries with functions, aggregates, joins, and grouping, and even write a stored procedure.

BASE SCHEMA

As part of my assignment i made this database which
Comprises of 4 tables ie Students, Courses, Enrollments and Faculty.

TABLE CREATION

Student 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) );

Enrollment
CREATE TABLE Enrollments ( EnrollID NUMBER PRIMARY KEY, StudentID NUMBER REFERENCES Students(StudentID), CourseID NUMBER REFERENCES Courses(CourseID), Grade CHAR(2) );

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

INSERTING SAMPLE DATA

I inserted sample student data into student table as shown below:

`INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Sarah', 'Biomedical', DATE '2000-05-12', 'alice@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'James', 'Mechanical', DATE '2001-07-20', 'james@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Charlie', 'Electrical', DATE '2002-02-14', 'charlie@example.com');
`

ALTER TABLE

This adds a column called gender to the student table
ALTER TABLE Students ADD (Gender CHAR(10));

ADDING CONSTRAINTS

ALTER TABLE Courses ADD CONSTRAINT chk_Credits CHECK (Credits BETWEEN 1 AND 5);

Using Functions in SELECT

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

Aggregate Functions

Average course credits
`SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(*) AS TotalStudents FROM Students;
`

JOIN Operation

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

This query lists all students with their courses and grades.

GROUP BY with HAVING

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

The above code shows departments with more than 2 students.

Views

`CREATE OR REPLACE VIEW StudentCoursesView AS
SELECT s.Name AS Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
Now you can query the view directly:

SELECT * FROM StudentCoursesView;

Stored Procedure

A stored procedure to update grades
`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;
/

Call it in Oracle LiveSQL:

BEGIN
UpdateGrade(1, 101, 'A');
END;
/

Conclusion

In this blog, we explored the essential SQL operations that form the from creating and modifying tables using DDL and ALTER, inserting and managing records with DML, enforcing constraints to maintain data integrity, and applying functions and aggregates for insights, to performing joins, grouping with HAVING, building reusable views, and even automating tasks with stored procedures—each step plays a vital role in efficient database management.

By practicing these queries

you not only learn the syntax but also gain a deeper understanding of how databases ensure accuracy, consistency, and reliability