DEV Community: Lohita M

Amazon RDS (Relational Database Service)

Lohita M — Mon, 22 Dec 2025 05:52:30 +0000

Introduction

In today’s digital world, applications need reliable and scalable databases to store and manage data. Amazon Web Services (AWS) provides Amazon RDS (Relational Database Service) to make database management easier without worrying about infrastructure. RDS helps developers focus on application development instead of database maintenance.

What is Amazon RDS?

Amazon RDS is a managed relational database service offered by AWS. It allows users to create, operate, and scale relational databases in the cloud with just a few clicks. AWS takes care of tasks like backups, patching, and scaling.

Supported Database Engines

Amazon RDS supports multiple popular database engines:
-MySQL
-PostgreSQL
-MariaDB
-Oracle
-SQL Server
-Amazon Aurora (AWS-optimised database)

Key Features of Amazon RDS

-Fully Managed Service – No need to manage servers manually
-Automatic Backups – Data is backed up regularly
-High Availability – Multi-AZ deployment for fault tolerance
-Scalability – Easily increase storage or computing power
-Security – Encryption, IAM roles, and VPC support
-Monitoring – Performance insights using Amazon CloudWatch

How Amazon RDS Works

Choose a database engine
Select instance type and storage
Configure security and network settings
Launch the database instance
Connect your application to the database AWS manages maintenance tasks like updates, backups, and recovery.

Advantages of Amazon RDS

-Reduces operational overhead
-Easy to use and deploy
-Cost-effective (pay only for what you use)
-Highly reliable and scalable

Use Cases

-Web and mobile applications
-E-commerce platforms
-Content management systems
-Data-driven applications
-Startup and enterprise workloads

Conclusion

Amazon RDS simplifies database management by offering a secure, scalable, and managed solution. It is ideal for developers, startups, and enterprises who want reliable databases without handling complex infrastructure.

Liquibase in DevOps: Managing Databases the Right Way

Lohita M — Thu, 18 Dec 2025 17:35:36 +0000

🔍 1. Overview of the Tool (DevOps Periodic Table Context)

In the DevOps Periodic Table, Liquibase belongs to the Database Change Management category. It is a powerful tool used to track, version, and automate database schema changes, much like Git is used for source code. Liquibase ensures that database updates are consistent, reliable, and repeatable across all environments, such as development, testing, and production.

⚙️ 2. Key Features of Liquibase

Database Version Control – Tracks every database change with history and rollback support
Changelog-Based Approach – Uses XML, YAML, JSON, or SQL changelogs
Rollback Support – Safely undo database changes when needed
Multi-Database Support – Works with MySQL, PostgreSQL, Oracle, SQL Server, MongoDB, and more
CI/CD Integration – Easily integrates with Jenkins, GitHub Actions, GitLab CI, etc.
Environment Consistency – Prevents “works on my system” database issues

🔄 3. How Liquibase Fits into DevOps / DevSecOps

Liquibase plays a critical role in DevOps pipelines by automating database changes along with application deployments.

Enables Database as Code
Supports continuous integration & continuous delivery (CI/CD)
Reduces manual database errors
Helps implement DevSecOps by auditing database changes and maintaining compliance
Ensures faster, safer releases with controlled schema evolution

💻 4. Programming Language Used

Liquibase is primarily written in Java.
However, developers interact with it using:

SQL
XML
YAML
JSON

This makes it flexible and developer-friendly across teams.

*🏢 5. Parent Company
*
Liquibase is developed and maintained by Liquibase, Inc., a company focused on database DevOps and automation solutions.

🔓 6. Open Source or Paid?

✅ Liquibase Open Source – Free and open-source under Apache License 2.0

💼 Liquibase Pro – Paid enterprise version with advanced features like:

Advanced security
Performance optimizations
Enterprise support
Enhanced compliance features
🚀Conclusion

Liquibase is an essential DevOps tool that bridges the gap between application code and database changes. By treating database updates as version-controlled assets, Liquibase enables faster, safer, and more reliable software delivery.

CRUD (Create, Read, Update, Delete) operations in MongoDB using a simple college student schema.

Lohita M — Wed, 08 Oct 2025 18:47:37 +0000

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

🧱 Schema (Collection: students)

Each document follows this structure:

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

⚙️ 1️⃣ CREATE (INSERT)
Insert at least 5 student records into the students collection:

db.students.insertMany([
{
"student_id": "S001",
"name": "Santhosh",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
},
{
"student_id": "S002",
"name": "Baviya",
"age": 19,
"department": "CSE",
"year": 1,
"cgpa": 8.7
},
{
"student_id": "S003",
"name": "Karthik",
"age": 21,
"department": "ECE",
"year": 3,
"cgpa": 7.2
},
{
"student_id": "S004",
"name": "Anu",
"age": 20,
"department": "CSE",
"year": 2,
"cgpa": 9.3
},
{
"student_id": "S005",
"name": "Ravi",
"age": 22,
"department": "MECH",
"year": 3,
"cgpa": 6.8
}
])

🔍 2️⃣ READ (QUERY)

Find all students with CGPA > 8:

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

Find students belonging to the Computer Science department:

db.students.find({ department: { $in: ["CSE", "CSBS"] } }).pretty()

✏️ 3️⃣ UPDATE
(a) Update the CGPA of a specific student

db.students.updateOne(
{ student_id: "S002" },
{ $set: { cgpa: 9.1 } }
)

(b) Increase the year of study for all 3rd-year students by 1

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

4️⃣ DELETE
(a) Delete one student record by student_id:

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

(b) Delete all students having CGPA < 7.5

db.students.deleteMany({ cgpa: { $lt: 7.5 } })

AFTER DELETION:

Database Normalization

Lohita M — Wed, 08 Oct 2025 18:22:19 +0000

Introduction:-

Database Normalization:

-Database Normalization is the process of organizing data in a relational database to reduce data redundancy (repeated data) and improve data integrity (accuracy and consistency).
-It involves dividing a large, unstructured table into smaller, well-structured tables and establishing relationships between them using keys.

Steps 1: Base Table

The initial unnormalized table includes details of students, their courses, instructors, and corresponding grades.

Step 2: Identifying Anomalies

Insertion anomaly: A new course cannot be added unless it is linked to a student.

Update anomaly: Modifying a course name requires updating it in several rows.

Deletion anomaly: Removing a student may also remove valuable course details if that student was the only enrollee.

Step 3: Converting to 1NF

First Normal Form (1NF) rules:

Every column should hold atomic (indivisible) values.

Each record must be unique.

Steps applied:

Divided multivalued attributes into individual rows.

Ensured that each column stores only one value.

SQL Table in 1 NF,

CREATE TABLE Students_1NF (
Student_ID INT,
Student_Name VARCHAR2(100),
Course_ID INT,
Course_Name VARCHAR2(100),
Instructor VARCHAR2(100),
Grade CHAR(2),
PRIMARY KEY (Student_ID, Course_ID)
);

Step 4: Conversion to 2NF

2NF Rule: Remove partial dependency, ensuring that non-key attributes rely on the entire composite primary key.

Observations:

Student_Name depends only on Student_ID.

Course_Name, Instructor, Instructor_Phone depend only on Course_ID.

Grade (if existed) would depend on both Student_ID + Course_ID.

Solution: Split into three tables:

Students → Student_ID, Student_Name

Courses → Course_ID, Course_Name, Instructor, Instructor_Phone

Enrollments → Student_ID, Course_ID

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

SQL Create Tables (2NF):

Step 5: Conversion to 3NF

3NF Rule: Remove transitive dependencies (non-prime attributes must depend only on primary key).

Instructor’s phone depends on Instructor, not Course_ID → we can split Courses into:

Courses → Course_ID, Course_Name, Instructor_ID

Instructors → Instructor_ID, Instructor_Name, Instructor_Phone

CREATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses3NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

CREATE TABLE Students3NF (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Enrollments3NF (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course3NF(CourseID)
);

SQL Create Tables (3NF):

Step 6: Insert Sample Data

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

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

-- Students
INSERT INTO Student3NF VALUES ('S01', 'Arjun');
INSERT INTO Student3NF VALUES ('S02', 'Priya');
INSERT INTO Student3NF VALUES ('S03', 'Kiran');

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

Step 7: Query with JOINs

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollment3NF e
JOIN Student3NF s ON e.StudentID = s.StudentID
JOIN Course3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion:-

Normalization helps achieve:

Reduced data redundancy

Improved data integrity

Simplified maintenance and updates

Following the progression from 1NF → 2NF → 3NF in Oracle SQL enables the creation of scalable and efficient database designs.

Transactions, Deadlocks & Log-Based Recovery in Oracle SQL

Lohita M — Wed, 08 Oct 2025 17:15:05 +0000

We will explore key DBMS concepts: Transactions, Deadlocks, and Log-Based Recovery using an Accounts table in Oracle SQL.

1️⃣ Schema Setup

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Accounts';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/

CREATE TABLE Accounts (
acc_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
balance NUMBER
);

INSERT INTO Accounts VALUES (1, 'Alice', 1000);
INSERT INTO Accounts VALUES (2, 'Bob', 1500);
INSERT INTO Accounts VALUES (3, 'Charlie', 2000);

COMMIT;

Explanation:

NUMBER is used for numeric columns in Oracle. -VARCHAR2 is used for text columns. -COMMIT finalizes the inserts so other sessions can see the data. -After this, the Accounts table has three accounts with initial balances.

2️⃣ Transaction – Atomicity & Rollback

UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK;
SELECT * FROM Accounts;

Explanation

-Oracle automatically starts a transaction with any DML statement.
-Atomicity ensures that either all updates succeed or none do.
-ROLLBACK cancels changes.
-After rollback, balances of Alice and Bob remain unchanged, ensuring data consistency.

3️⃣ Deadlock Simulation

Session 1:

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

Session 2:

UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

Explanation

-Session 1 locks Alice’s account.
-Session 2 locks Bob’s account.
-When each session tries to update the other’s locked row, Oracle detects a deadlock.
-Oracle automatically rolls back one transaction to resolve the deadlock.

4️⃣ Log-Based Recovery

UPDATE Accounts SET balance = 2500 WHERE name = 'Charlie';
ROLLBACK;
SELECT * FROM Accounts;

Explanation

-Oracle writes all changes to redo logs.
-When we ROLLBACK, Oracle uses the logs to undo changes.
-This ensures the database stays consistent even if a failure occurs.

ACID PROPERTIES WITH SQL TRNSACTIONS

Lohita M — Wed, 08 Oct 2025 16:48:00 +0000

Step 1: Create the Accounts Table & Insert Sample Data

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, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);

Step 2: Atomicity

Goal: Transfer money from Alice → Bob, but rollback midway to prevent partial update.

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
ROLLBACK;
SELECT * FROM Accounts;

Expected Result: No changes happen. Alice still has 5000, Bob 3000.
This shows atomicity — either all updates succeed, or none do.

Step 3: Consistency
Goal: Reject invalid state (negative balance).

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

Expected Result: Error due to CHECK (balance >= 0).
This preserves data consistency — database rules are enforced.

Step 4: Isolation
Goal: Observe isolation between concurrent transactions.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 103;

Session 2 (another terminal/session):

SELECT * FROM Accounts WHERE acc_no = 103;

Expected Result:

Depending on isolation level (REPEATABLE READ default in MySQL), Session 2 may not see uncommitted changes.
Session 1 commits → changes become visible.

COMMIT;

This shows isolation — transactions do not interfere unexpectedly.

Step 5: Durability
Goal: Data persists after commit.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;

Restart the database.
Run:

SELECT * FROM Accounts WHERE acc_no = 102;

Expected Result: Bob’s balance is updated permanently. ✅
This demonstrates durability — committed transactions survive crashes.

Indexing, Hashing & Query Optimization

Lohita M — Wed, 08 Oct 2025 15:38:29 +0000

🎯** Objective**
To understand how to improve query performance using B-Tree, B+ Tree, and Hash indexing in MySQL.

🧩 Step 1: Create the Students Table

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

`
🧾** Step 2: Insert Sample Records**

`
INSERT INTO Students VALUES
(101, 'Aarav', 'CSBS', 8.9),
(102, 'Diya', 'ECE', 7.8),
(103, 'Karthik', 'IT', 9.1),
(104, 'Meena', 'CSBS', 8.2),
(105, 'Rohit', 'EEE', 7.6),
(106, 'Isha', 'MECH', 8.0),
(107, 'Ravi', 'CIVIL', 7.4),
(108, 'Sneha', 'IT', 9.3),
(109, 'Vikram', 'CSBS', 8.7),
(110, 'Priya', 'CSE', 9.0),
(111, 'Hari', 'ECE', 8.4),
(112, 'Ananya', 'EEE', 7.9),
(113, 'Kavin', 'MECH', 8.1),
(114, 'Swathi', 'IT', 9.5),
(115, 'Teja', 'CSBS', 8.8),
(116, 'Vishwa', 'CSE', 9.2),
(117, 'Divya', 'ECE', 7.7),
(118, 'Suresh', 'CIVIL', 8.3),
(119, 'Kiran', 'IT', 8.6),
(120, 'Pooja', 'CSBS', 9.4);

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

CREATE INDEX idx_roll_no ON Students(roll_no);
✅ Query:

SELECT * FROM Students WHERE roll_no = 110;
💡 Explanation:

-B-Tree indexes help in range-based and sorted searches efficiently.
-The lookup time reduces from O(n) to O(log n).

🌿Step 4: Create a B+ Tree Index on cgpa

`
CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

✅** Query:**

`
SELECT * FROM Students WHERE cgpa > 8.0;

⚙️ Step 5: Create a Hash Index on dept

`
CREATE INDEX idx_dept_hash ON Students USING HASH(dept);

`
✅** Query:**

`
SELECT * FROM Students WHERE dept = 'CSBS';

`
💡** Explanation:**

-Hash Index is best for exact match lookups (e.g., =).
-It is not used for range queries like > or <.

Working with Cursors and Triggers in Oracle Live SQL

Lohita M — Sun, 05 Oct 2025 14:46:19 +0000

In this post, we’ll learn how to use Cursors and Triggers in Oracle SQL with simple examples.
Let’s dive in! ⚡

📌 1. Cursor with Condition

A cursor in Oracle is used when you want to process query results row by row.
👉Example: Display employee names whose salary > 50,000 from the Employee table.

✅ Steps:

1.Declare a cursor with the condition.
2.Open the cursor.
3.Fetch each row into a variable.
4.Process inside a loop.
5.Close the cursor.

👉Example (Oracle PL/SQL)

DECLARE
emp_name Employee.Emp_Name%TYPE;

CURSOR emp_cursor IS
SELECT Emp_Name FROM Employee WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_name);
END LOOP;
CLOSE emp_cursor;
END;
/

📌 2. AFTER INSERT Trigger

A trigger in Oracle is a stored PL/SQL block that automatically executes when a specific event occurs on a table.
👉 Example: Whenever a new student is inserted into the Students table, add a log entry in Student_Audit table.

✅** Steps:**

Create an audit table.
Write an AFTER INSERT trigger.
Insert log details inside the trigger.

👉 Example (Oracle PL/SQL):

CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Course VARCHAR(30)
);

CREATE TABLE Student_Audit (
Audit_ID INT PRIMARY KEY,
Student_ID INT,
Action VARCHAR(20),
Log_Time TIMESTAMP
);

CREATE SEQUENCE STUDENT_AUDIT_SEQ
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT
ON STUDENTS
FOR EACH ROW
BEGIN
INSERT INTO STUDENT_AUDIT (AUDIT_ID, STUDENT_ID, ACTION, LOG_TIME)
VALUES (STUDENT_AUDIT_SEQ.NEXTVAL, :NEW.STUDENT_ID, 'INSERT', SYSTIMESTAMP);
END;
/

INSERT INTO STUDENTS VALUES (1, 'Sowmya', 'Biology');

SELECT * FROM STUDENT_AUDIT;

🎯 Conclusion

1.Cursors are useful when you need row-by-row processing.
2.Triggers automate tasks like auditing changes.