DEV Community: Mukesh B

Amazon RDS vs Amazon DYNAMODB

Mukesh B — Mon, 22 Dec 2025 04:35:57 +0000

1*) Amazon RDS (Relational Database Service)*
Service Overview
Amazon RDS is a managed relational database service that makes it easy to set up, operate, and scale relational databases in the cloud.

Key Features
1.Supports MySQL, PostgreSQL, MariaDB, Oracle, SQL Server
2.Automated backups & snapshots
3High availability with Multi-AZ deployment
4.Automatic patching & maintenance
5.Read replicas for performance scaling
6.Built-in security (encryption, IAM, VPC)

AWS Category / Cloud Domain
1.Database
2.Managed Relational Database

Where It Fits in Cloud / DevOps Lifecycle
1.Application data storage
2.Used in development, testing, and production
3.Supports CI/CD pipelines with stable backend databases
4.Part of Operate & Monitor phase

Programming Language / Access Methods
1.Accessed using SQL
2.Works with Java, Python, PHP, Node.js, .NET, etc.
4.Access via:
-AWS Console

AWS CLI
AWS SDKs Pricing Model 1.Pay-as-you-go 2.Charged based on:
Instance type
Storage used
I/O operations
Backup storage
2) Amazon DynamoDB
Service Overview
Amazon DynamoDB is a fully managed NoSQL key-value and document database designed for high performance and scalability.
Key Features
Serverless (no infrastructure management)
Extremely low latency (milliseconds)
Automatic scaling
Built-in high availability & replication
Supports event-driven architectures
Integrated security with IAM
AWS Category / Cloud Domain
Database
NoSQL Database
Serverless Database
Where It Fits in Cloud / DevOps Lifecycle
Used in cloud-native & microservices applications
Ideal for real-time apps, IoT, mobile backends
Fits into Build, Deploy & Operate stages
Works well with DevOps automation
Programming Language / Access Methods
No SQL (uses key-value / JSON documents)
Supported languages:
Python, Java, JavaScript, Go, .NET
Access via:
AWS Console
AWS CLI
AWS SDKs
API calls
Pricing Model
Pay-per-request or provisioned capacity
Charged for:
Read/write requests
Storage
Data transfer
Free tier available
Quick Comparison (For Exams)
Feature
RDS
DynamoDB
Database Type
Relational (SQL)
NoSQL
Schema
Fixed
Flexible
Scaling
Manual/Read replicas
Automatic
Use Case
Traditional apps
High-scale apps
Serverless
❌ No
✅ Yes

Mural

Mukesh B — Fri, 19 Dec 2025 04:12:57 +0000

Overview of the Tool
Mural is an online digital whiteboard and collaboration tool used by teams to brainstorm, plan, design, and visualize ideas in real time.
Mural Elements are the building blocks used on the board to create and organize content.

Key Features / Elements
1.Sticky Notes – Add ideas, comments, and feedback
2.Shapes – Flowcharts, diagrams, and structures
3.Text Boxes – Titles, explanations, and labels
4.Icons & Images – Visual representation of ideas
5.Connectors & Lines – Show relationships between elements
6.Templates – Ready-made layouts (Kanban, mind maps, SWOT, etc.)
7.Voting & Comments – Team decision-making and feedback
8.Real-time Collaboration – Multiple users work simultaneously

How It Fits into DevOps / DevSecOps
1.Used for sprint planning, retrospectives, and brainstorming
2.Helps visualize DevOps pipelines and security workflows
3.Supports collaboration between Dev, Sec, and Ops teams
4.Useful for threat modeling and process documentation

Programming Language
1.Not a programming tool
2.Built as a web-based application
3.Uses modern web technologies internally (not required for users)

Parent Company
1.Mural Inc.

Open Source or Paid
❌ Not open source
✅ Paid tool
Offers free trial and educational plans

Indexing, Hashing & Query Optimization in SQL — A Practical Guide

Mukesh B — Wed, 08 Oct 2025 16:07:59 +0000

Introduction

When databases grow large, query performance becomes critical.
Indexes — like B-Trees, B+ Trees, and Hash indexes — help speed up data retrieval by reducing the number of rows scanned.

In this tutorial, we’ll:

Create a Students table.

Insert 20 records.

Create B-Tree, B+ Tree, and Hash indexes.

Run optimized queries and observe performance differences.

Let’s dive in! ⚙️

🏗️ Step 1: Create the Table

We’ll start by creating a simple Students table.

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

✅ Explanation:

roll_no uniquely identifies each student.

cgpa helps demonstrate range-based queries.

dept is great for hash indexing.

📥 Step 2: Insert 20 Sample Records

Let’s insert some demo data.

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Arun Kumar', 'CSE', 8.7),
(102, 'Meena Devi', 'ECE', 7.9),
(103, 'Karthik Raja', 'CSBS', 8.2),
(104, 'Priya Mohan', 'IT', 9.0),
(105, 'Vishal', 'CSBS', 8.1),
(106, 'Ananya', 'MECH', 7.5),
(107, 'Ravi', 'EEE', 6.9),
(108, 'Swetha', 'CSE', 9.1),
(109, 'Balaji', 'IT', 8.3),
(110, 'Hari', 'CSBS', 8.9),
(111, 'Nisha', 'ECE', 7.4),
(112, 'Sanjay', 'MECH', 8.0),
(113, 'Keerthi', 'CSE', 9.3),
(114, 'Deepak', 'CSBS', 7.8),
(115, 'Gayathri', 'EEE', 8.4),
(116, 'Suresh', 'IT', 7.6),
(117, 'Lavanya', 'CSE', 8.8),
(118, 'Kavin', 'CSBS', 9.2),
(119, 'Rohit', 'MECH', 8.1),
(120, 'Preethi', 'ECE', 8.0);

🌲 Step 3: Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno_btree ON Students (roll_no);

✅ Explanation:

B-Tree indexes are default in most SQL databases.

They help speed up exact match or range queries on numeric columns.

🔍 Query: Fetch student with roll_no = 110
SELECT * FROM Students WHERE roll_no = 110;

You’ll notice the query runs instantly, thanks to the index.

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

(In some databases, B+ Trees are the underlying structure of B-Tree indexes. You can simulate it using a normal index.)

CREATE INDEX idx_cgpa_bplustree ON Students (cgpa);

✅ Use Case:
Efficient for range queries, such as finding all students with cgpa > 8.0.

🧮 Query: List Students with cgpa > 8.0
SELECT * FROM Students WHERE cgpa > 8.0;

The B+ Tree helps in retrieving a range of values quickly.
You can check query performance with:

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

✅ Explanation:

Hash indexes are best for equality comparisons (e.g., dept = 'CSBS').

Not suitable for range or sorting operations.

🧭 Query: Retrieve All Students from the 'CSBS' Department
SELECT * FROM Students WHERE dept = 'CSBS';

You’ll see results appear very fast

🧩 Step 6: Query Optimization Tips

Use indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

Avoid indexing columns with low uniqueness (like gender or status).

Periodically run ANALYZE or VACUUM to update statistics.

Use EXPLAIN to see how your query executes.

🏁 Conclusion

By combining B-Tree, B+ Tree, and Hash Indexes, we optimized queries efficiently:

B-Tree: Fast for primary key lookups.

B+ Tree: Great for range queries.

Hash Index: Perfect for equality conditions.

With these techniques, your database queries can run 10x faster 🚀

Database Normalization — Step-by-Step from 1NF to 3NF

Mukesh B — Wed, 08 Oct 2025 16:07:42 +0000

🧠 Introduction

Database Normalization helps remove data redundancy and anomalies like insertion, update, and deletion problems.
In this tutorial, we’ll normalize a sample table step by step — from 1NF → 2NF → 3NF — and implement it in SQL.

🏁 Step 1: Base Table

Let’s start with an unnormalized table.

📋 Base Table: Student_Course_Info

⚠️ Step 1A: Identify Anomalies
Type of Anomaly Description
Insertion Anomaly You can’t add a new course unless at least one student enrolls.
Update Anomaly If Dr. Ravi’s phone number changes, it must be updated in multiple rows.
Deletion Anomaly If all students drop “OS,” information about instructor Dr. Priya is lost.

🧮 Step 2: Convert to 1NF (First Normal Form)

Rule: Remove repeating groups and ensure atomic (single-valued) attributes.

✅ Each cell should contain a single value.
✅ Each record should be unique.

🧱 Table: Student_Course_1NF
CREATE TABLE Student_Course_1NF (
Student_ID INT,
Student_Name VARCHAR(50),
Course_ID VARCHAR(10),
Course_Name VARCHAR(50),
Instructor VARCHAR(50),
Instructor_Phone VARCHAR(15),
PRIMARY KEY (Student_ID, Course_ID)
);

Insert Sample Data
INSERT INTO Student_Course_1NF VALUES
(101, 'Arun Kumar', 'C01', 'DBMS', 'Dr. Ravi', '9876543210'),
(101, 'Arun Kumar', 'C02', 'OS', 'Dr. Priya', '9876500001'),
(102, 'Meena Devi', 'C01', 'DBMS', 'Dr. Ravi', '9876543210'),
(103, 'Vishal', 'C03', 'CN', 'Dr. Karthik', '9876522222');

✅ Data is atomic, but redundancy still exists (Instructor and Course details repeat).

🧩 Step 3: Convert to 2NF (Second Normal Form)

Rule: Remove partial dependency — all non-key attributes must depend on the whole composite key, not part of it.

Here:

Course_Name, Instructor, and Instructor_Phone depend only on Course_ID

Student_Name depends on Student_ID

👉 So we’ll split into three tables:

🧱 Table 1: Students
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50)
);

🧱 Table 2: Courses
CREATE TABLE Courses (
Course_ID VARCHAR(10) PRIMARY KEY,
Course_Name VARCHAR(50),
Instructor VARCHAR(50),
Instructor_Phone VARCHAR(15)
);

🧱 Table 3: Enrollments
CREATE TABLE Enrollments (
Student_ID INT,
Course_ID VARCHAR(10),
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);

Insert Data
INSERT INTO Students VALUES
(101, 'Arun Kumar'),
(102, 'Meena Devi'),
(103, 'Vishal');

INSERT INTO Courses VALUES
('C01', 'DBMS', 'Dr. Ravi', '9876543210'),
('C02', 'OS', 'Dr. Priya', '9876500001'),
('C03', 'CN', 'Dr. Karthik', '9876522222');

INSERT INTO Enrollments VALUES
(101, 'C01'),
(101, 'C02'),
(102, 'C01'),
(103, 'C03');

✅ Redundancy reduced.
❌ But instructor info still repeats (if multiple courses by the same instructor).

🧠 Step 4: Convert to 3NF (Third Normal Form)

Rule: Remove transitive dependencies — non-key attributes should not depend on other non-key attributes.

Here:

Instructor_Phone depends on Instructor, not directly on Course_ID.

👉 Split Instructor details into a new table.

🧱 Table 1: Students
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50)
);

🧱 Table 2: Instructors
CREATE TABLE Instructors (
Instructor_ID INT PRIMARY KEY AUTO_INCREMENT,
Instructor_Name VARCHAR(50),
Instructor_Phone VARCHAR(15)
);

🧱 Table 3: Courses
CREATE TABLE Courses (
Course_ID VARCHAR(10) PRIMARY KEY,
Course_Name VARCHAR(50),
Instructor_ID INT,
FOREIGN KEY (Instructor_ID) REFERENCES Instructors(Instructor_ID)
);

🧱 Table 4: Enrollments
CREATE TABLE Enrollments (
Student_ID INT,
Course_ID VARCHAR(10),
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES Courses(Course_ID)
);

Insert Data
INSERT INTO Students VALUES
(101, 'Arun Kumar'),
(102, 'Meena Devi'),
(103, 'Vishal');

INSERT INTO Instructors (Instructor_Name, Instructor_Phone) VALUES
('Dr. Ravi', '9876543210'),
('Dr. Priya', '9876500001'),
('Dr. Karthik', '9876522222');

INSERT INTO Courses VALUES
('C01', 'DBMS', 1),
('C02', 'OS', 2),
('C03', 'CN', 3);

INSERT INTO Enrollments VALUES
(101, 'C01'),
(101, 'C02'),
(102, 'C01'),
(103, 'C03');

🔍 Step 5: JOIN Query — View Normalized Data Together

To list all students with their courses and instructors, we’ll join all tables.

SELECT
s.Student_Name,
c.Course_Name,
i.Instructor_Name
FROM Enrollments e
JOIN Students s ON e.Student_ID = s.Student_ID
JOIN Courses c ON e.Course_ID = c.Course_ID
JOIN Instructors i ON c.Instructor_ID = i.Instructor_ID;

✅ Result Example:

Student_Name Course_Name Instructor_Name
Arun Kumar DBMS Dr. Ravi
Arun Kumar OS Dr. Priya
Meena Devi DBMS Dr. Ravi
Vishal CN Dr. Karthik

✅ Conclusion

Through 1NF, 2NF, and 3NF, we’ve:

Eliminated redundancy

Avoided anomalies

Structured the database logically

This normalization not only improves data integrity but also simplifies updates and queries.

Understanding ACID Properties in SQL with Practical Examples

Mukesh B — Wed, 08 Oct 2025 16:07:25 +0000

🧠 Introduction

In relational databases, ACID stands for:

Atomicity

Consistency

Isolation

Durability

These four properties guarantee reliable transactions — ensuring that your data remains accurate even in case of errors or crashes.

In this tutorial, we’ll demonstrate each ACID property step by step using SQL commands.

🏗️ Step 1: Create a Sample Table

We’ll create an Accounts table and insert sample records.

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

📸 Screenshot: Accounts table created successfully.

🧮 Insert Sample Data
INSERT INTO Accounts VALUES
(101, 'Arun Kumar', 50000),
(102, 'Meena Devi', 30000),
(103, 'Vishal', 70000);

📸 Screenshot: Data inserted successfully.

✅ Current Data:

acc_no name balance
101 Arun Kumar 50000
102 Meena Devi 30000
103 Vishal 70000
⚙️ A → Atomicity

A transaction is all or nothing.
Either all steps succeed, or none are applied.

🎯 Example: Money Transfer (with Rollback)
BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 5000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 102;

-- Simulate an error
ROLLBACK;

SELECT * FROM Accounts;

📘 Explanation:

We start a transaction.

We try to transfer ₹5000 from Arun to Meena.

Then we issue a ROLLBACK, which undoes both updates.

✅ Expected Result:
Balances remain unchanged.

🧩 C → Consistency

The database must always move from one valid state to another.
Invalid data (e.g., negative balances) should not be allowed.

🎯 Example: Insert Invalid Record
INSERT INTO Accounts VALUES (104, 'Karthik', -2000);

✅ Expected Output:

📘 Explanation:
The CHECK (balance >= 0) constraint ensures consistency of data.
You can never have an account with a negative balance.

🔒 I → Isolation

Multiple transactions running at the same time should not interfere with each other.

🎯 Example: Two Sessions
🪄 Session 1:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 10000 WHERE acc_no = 103;
-- (Don’t commit yet)

🪄 Session 2:
SELECT * FROM Accounts WHERE acc_no = 103;

✅ Observation:

If your isolation level is READ COMMITTED,
Session 2 won’t see the updated balance until Session 1 commits.

Once Session 1 executes COMMIT;, Session 2 will see the new balance.

📘 Explanation:
This demonstrates Isolation — one transaction’s changes remain invisible to others until committed.

💽 D → Durability

Once a transaction is committed, it remains permanent — even if the system crashes.

🎯 Example: Commit and Restart
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
COMMIT;

✅ Now restart your database service, then run:

SELECT * FROM Accounts WHERE acc_no = 101;

📘 Expected Output:
The updated balance remains in place even after the restart.

✅ This demonstrates Durability — committed data survives crashes or restarts.

🚀 Conclusion

You’ve just implemented and tested all four ACID properties in SQL:

✅ Atomicity → Rollback works perfectly
✅ Consistency → Data validation enforced
✅ Isolation → Parallel sessions protected
✅ Durability → Changes survive restarts

With this, you’ve mastered one of the most important foundations of Database Reliability! 🧠💾

💬 Next Step:
Try experimenting with different isolation levels using:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

and observe how concurrent transactions behave differently!

SQL Cursor & AFTER INSERT Trigger — Step-by-Step Guide with Example

Mukesh B — Wed, 08 Oct 2025 16:07:05 +0000

Introduction

In SQL, Cursors and Triggers are two powerful tools used for automation and data processing.

A Cursor allows you to process rows one-by-one.

A Trigger automatically executes in response to specific table events (like INSERT, UPDATE, or DELETE).

In this tutorial, you’ll learn:

How to create and process a cursor with a condition.

How to create an AFTER INSERT trigger to log student registrations.

Let’s get started! 🚀

🧩 Part 1: Process Cursor with Condition
🎯 Objective:

Display the names of employees whose salary > 50,000 using a cursor.

🏗️ Step 1: Create the Employee Table
CREATE TABLE Employee (
Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50),
Department VARCHAR(30),
Salary DECIMAL(10,2)
);

🧮 Step 2: Insert Sample Data
INSERT INTO Employee VALUES
(101, 'Arun Kumar', 'HR', 45000),
(102, 'Meena Devi', 'Finance', 55000),
(103, 'Vishal', 'IT', 75000),
(104, 'Karthik', 'Admin', 48000),
(105, 'Priya', 'IT', 92000);

🧠 Step 3: Create a Cursor to Display Employees with Salary > 50,000
DECLARE
emp_name VARCHAR(50);
emp_salary DECIMAL(10,2);

DECLARE emp_cursor CURSOR FOR
SELECT Emp_Name, Salary
FROM Employee
WHERE Salary > 50000;

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO emp_name, emp_salary;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + emp_name + ' | Salary: ' + CAST(emp_salary AS VARCHAR);
FETCH NEXT FROM emp_cursor INTO emp_name, emp_salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

📘 Explanation:

The CURSOR selects only employees with Salary > 50000.

The FETCH statement retrieves each record one by one.

Inside the loop, the result is printed (or displayed via SELECT in MySQL).

✅ Result Example:

Employee: Meena Devi | Salary: 55000.00
Employee: Vishal | Salary: 75000.00
Employee: Priya | Salary: 92000.00

🧩 Part 2: AFTER INSERT Trigger (Student Registration Audit)
🎯 Objective:

Whenever a new student is inserted into the Students table, automatically insert a log entry into the Student_Audit table.

🏗️ Step 1: Create the Students Table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50),
Department VARCHAR(30),
Register_Date DATE
);

🧱 Step 2: Create the Student_Audit Table
CREATE TABLE Student_Audit (
Audit_ID INT IDENTITY(1,1) PRIMARY KEY,
Student_ID INT,
Student_Name VARCHAR(50),
Action VARCHAR(20),
Action_Time DATETIME
);

⚡ Step 3: Create AFTER INSERT Trigger
CREATE TRIGGER trg_AfterInsert_Student
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (Student_ID, Student_Name, Action, Action_Time)
SELECT
i.Student_ID,
i.Student_Name,
'INSERT',
GETDATE()
FROM inserted i;

PRINT 'New student added. Audit log recorded successfully!';

END;

📘 Explanation:

The trigger fires automatically after an insert on the Students table.

The pseudo-table inserted holds the new row being added.

The log entry is recorded in Student_Audit for tracking.

🧮 Step 4: Insert Sample Data
INSERT INTO Students VALUES
(101, 'Arun Kumar', 'CSE', '2025-10-08'),
(102, 'Meena Devi', 'ECE', '2025-10-08');

🧾 Step 5: Verify Audit Logs
SELECT * FROM Student_Audit;

Database System Internals — Transactions, Deadlocks, Recovery & Indexing Explained with SQL Examples

Mukesh B — Wed, 08 Oct 2025 16:06:44 +0000

Step 1: Create Table and Insert Sample Data
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);

📸 Screenshot: Accounts table with 3 rows.

✅ Current State:

acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000
⚡ Task 1️⃣: Transaction – Atomicity & Rollback

Goal: Transfer ₹500 from Alice → Bob, then rollback before committing.

🧩 Step 1: Start Transaction
BEGIN TRANSACTION;

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

📸 Screenshot: Transaction started and updates pending.

🧩 Step 2: Rollback
ROLLBACK;

Now verify balances:

SELECT * FROM Accounts;

✅ Expected Output (No Changes):

acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000

📘 Explanation:
Atomicity ensures either all operations happen or none do.
Rollback prevented a partial transfer.

📸 Screenshot: Balances unchanged after rollback.

⚔️ Task 2️⃣: Deadlock Simulation

Goal: Create a deadlock scenario using two sessions.

🪄 Session 1:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
-- Lock held on Alice
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob'; -- Will wait

🪄 Session 2:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 200 WHERE name = 'Bob';
-- Lock held on Bob
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Alice'; -- Causes Deadlock

✅ Observation:

Both sessions hold different locks and wait for each other.

Database detects deadlock → rolls back one transaction.

📸 Screenshot: SQL deadlock message (one transaction aborted).

📘 Explanation:
Deadlocks happen when two transactions hold resources the other needs.
The DBMS automatically detects and resolves it by aborting one.

🪵 Task 3️⃣: Log-Based Recovery

Most modern databases (MySQL, PostgreSQL, SQL Server) maintain transaction logs:

Binary Log / WAL (Write-Ahead Log) records every change before committing.

🧩 Step 1: Start and Rollback a Transaction
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';
ROLLBACK;

🧩 Step 2: Check Logs

If using MySQL:

SHOW BINARY LOGS;
SHOW BINLOG EVENTS;

If using PostgreSQL:

SELECT * FROM pg_stat_activity;

✅ You’ll see entries for the UPDATE followed by a ROLLBACK (UNDO) event.

📸 Screenshot: Log entries showing rollback.

📘 Explanation:
The transaction log helps recover or rollback uncommitted operations after crashes — ensuring data consistency.

📚 Part 2 — Indexing, Hashing & Query Optimization

Now let’s explore how indexes make your queries lightning fast ⚡

🧱 Step 1: Create Table and Insert Data
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(10),
cgpa DECIMAL(3,2)
);

🧩 Step 2: Insert 20 Sample Records
INSERT INTO Students VALUES
(101, 'Arun', 'CSE', 8.7),
(102, 'Meena', 'ECE', 7.9),
(103, 'Vishal', 'CSBS', 8.3),
(104, 'Priya', 'IT', 9.0),
(105, 'Ravi', 'CSBS', 8.5),
(106, 'Swetha', 'MECH', 7.8),
(107, 'Deepak', 'EEE', 6.9),
(108, 'Ananya', 'CSE', 9.1),
(109, 'Balaji', 'IT', 8.4),
(110, 'Hari', 'CSBS', 8.9),
(111, 'Nisha', 'ECE', 7.4),
(112, 'Sanjay', 'MECH', 8.0),
(113, 'Keerthi', 'CSE', 9.3),
(114, 'Suresh', 'CSBS', 8.0),
(115, 'Gayathri', 'EEE', 8.4),
(116, 'Kavin', 'IT', 7.6),
(117, 'Lavanya', 'CSE', 8.8),
(118, 'Karthik', 'CSBS', 9.2),
(119, 'Rohit', 'MECH', 8.1),
(120, 'Preethi', 'ECE', 8.0);

📸 Screenshot: 20 records inserted.

🌲 Step 3: Create a B-Tree Index on roll_no
CREATE INDEX idx_rollno_btree ON Students (roll_no);

Now query:

SELECT * FROM Students WHERE roll_no = 110;

📘 Explanation:
B-Tree index improves performance of equality and range lookups on numeric fields.

📸 Screenshot: Query output for roll_no = 110.

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

(In most SQL databases, normal indexes are internally implemented as B+ Trees)

CREATE INDEX idx_cgpa_bplustree ON Students (cgpa);

Query:

SELECT * FROM Students WHERE cgpa > 8.0;

✅ Retrieves high-scoring students efficiently.

📸 Screenshot: Output for cgpa > 8.0.

⚡ Step 5: Create a Hash Index on dept
CREATE INDEX idx_dept_hash ON Students USING HASH (dept);

Now run:

SELECT * FROM Students WHERE dept = 'CSBS';

✅ Fast equality search on department names.

#8 Latest news in DBMS 2025

Mukesh B — Wed, 08 Oct 2025 15:49:34 +0000

https://www.linkedin.com/posts/mukesh-balasubramaniam-4a9593302_dbms-cloudcomputing-datasecurity-activity-7381717069710565376-AbXI?utm_source=share&utm_medium=member_android&rcm=ACoAAE1UDAEBOQCw6atAWeJcbj1Jn4KnyB8leNI

MongoDB Basics for Students + LinkedIn Post

Mukesh B — Wed, 08 Oct 2025 15:25:54 +0000

https://www.linkedin.com/posts/mukesh-balasubramaniam-4a9593302_mongodb-basics-for-student-activity-7381711229091753985-L1yg?utm_source=share&utm_medium=member_android&rcm=ACoAAE1UDAEBOQCw6atAWeJcbj1Jn4KnyB8leNI

MongoDB CRUD Operations using a College Student Schema

Mukesh B — Wed, 08 Oct 2025 12:22:57 +0000

🎯 Objective

The goal of this lab is to gain hands-on experience with performing basic CRUD (Create, Read, Update, Delete) operations in MongoDB Atlas using a simple college student schema.

We’ll use a students collection with fields like student_id, name, age, department, year, and cgpa.

🧱 Schema Structure

Each document in the students collection follows this structure:

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

⚙️ Step 1: Connect to MongoDB Atlas

🪄 Step 2: Create the Database and Collection
use college;

db.createCollection("students");

✅ This creates a new database named college and a collection named students.

📸 Screenshot: Collection created in Atlas.

🧩 Step 3: CREATE — Insert Student Records

We’ll insert at least 5 documents into the collection.

db.students.insertMany([
{ "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
{ "student_id": "S002", "name": "Meena", "age": 19, "department": "CSE", "year": 1, "cgpa": 8.5 },
{ "student_id": "S003", "name": "Vishal", "age": 21, "department": "ECE", "year": 3, "cgpa": 7.2 },
{ "student_id": "S004", "name": "Priya", "age": 22, "department": "CSBS", "year": 3, "cgpa": 9.1 },
{ "student_id": "S005", "name": "Ravi", "age": 20, "department": "MECH", "year": 2, "cgpa": 6.8 }
]);

✅ Inserted 5 student documents successfully.

🔍 Step 4: READ — Query Student Records
📗 4.1 Display All Student Records
db.students.find();

📘 4.2 Find Students with CGPA > 8
db.students.find({ cgpa: { $gt: 8 } });

✅ Displays all high-performing students.

📙 4.3 Find Students from Computer Science Department
db.students.find({ department: "CSE" });

or for CSBS:

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

📘 Description:
Filters documents where department equals CSE or CSBS.

✏️ Step 5: UPDATE — Modify Data
🧾 5.1 Update CGPA of a Specific Student
db.students.updateOne(
{ student_id: "S002" },
{ $set: { cgpa: 8.9 } }
);

✅ Updates Meena’s CGPA to 8.9.

📈 5.2 Increase Year of Study for All 3rd-Year Students by 1
db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
);

✅ Increments the year field for all 3rd-year students.

❌ Step 6: DELETE — Remove Data
🗑️ 6.1 Delete One Student by student_id
db.students.deleteOne({ student_id: "S005" });

✅ Removes the student named Ravi (S005).

🚫 6.2 Delete All Students Having CGPA < 7.5
db.students.deleteMany({ cgpa: { $lt: 7.5 } });

✅ Deletes all underperforming students.

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

Mukesh B — Thu, 21 Aug 2025 18:35:49 +0000

1. Create Faculty Table (DDL)
This command creates a table named Faculty to store faculty details. It contains four attributes: FacultyID, FacultyName, Dept, and Email. FacultyID is defined as the primary key to uniquely identify each faculty, and Email is set as unique to prevent duplicate entries.
Command:
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

2. Insert Data into Students Table (DML)
This command inserts sample student records into the Students table. Each record includes StudentID, Name, Department, Date of Birth, and Email. These entries are used to populate the database with initial data for testing queries.
Command:
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'Ravi Kumar', 'CSE', DATE '2002-05-14', 'ravi@college.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (2, 'Anita Sharma', 'ECE', DATE '2001-11-22', 'anita@college.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (3, 'Suresh Mehta', 'IT', DATE '2003-01-09', 'suresh@college.com');

3. Alter Table – Add Column
This command modifies the existing Students table by adding a new column named PhoneNo. This column is used to store the phone number of students and allows a maximum of 10 digits.
Command:
ALTER TABLE Students ADD PhoneNo NUMBER(10);

4. Add Constraints to Courses Table
This command adds a CHECK constraint to the Courses table. The constraint ensures that the Credits value of any course must be between 1 and 5. This prevents invalid data entry and maintains data integrity.
Command:
ALTER TABLE Courses ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

5. Select with Functions
This query uses SQL functions to manipulate and analyze data. It converts student names into uppercase and calculates the length of each student’s email ID. Such functions help in formatting and analyzing textual data.
Command:
SELECT UPPER(Name) AS StudentName_Upper, LENGTH(Email) AS Email_Length FROM Students;

6. Aggregate Functions
These queries use aggregate functions to summarize data. The first query calculates the average credits from the Courses table, while the second counts the total number of unique students enrolled in courses using COUNT(DISTINCT ...).
Command:
SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(DISTINCT StudentID) AS TotalStudents FROM Enrollments;

7. Join Operation
This query demonstrates the use of JOINs across three tables: Students, Enrollments, and Courses. It retrieves a list of students along with the courses they are enrolled in and their corresponding grades. This helps in combining data from multiple tables.
Command:
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;

8. Group By with Having
This query uses GROUP BY to count the number of students in each department. The HAVING clause is applied to filter results, displaying only those departments that have more than 2 students. It helps in generating summarized reports with conditions.
Command:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

9. Create View
This command creates a database view named StudentCoursesView. A view is a virtual table that simplifies complex queries. This view displays each student along with the courses they are enrolled in and their grades, making future queries easier.
Command:
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;

10. Stored Procedure – UpdateGrade
This command creates a stored procedure called UpdateGrade. A stored procedure is a reusable set of SQL statements. This one updates a student’s grade in the Enrollments table for a specific course. It improves database efficiency and reusability.
Command:
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;