Forem: Jaswant Karun

🔧 AWS IoT Jobs: Automating Secure Device Management at Scale

Jaswant Karun — Thu, 18 Dec 2025 16:29:45 +0000

In the modern cloud ecosystem, managing thousands—or even millions—of IoT devices manually is impossible. AWS IoT Jobs solves this challenge by enabling secure, scalable, and automated remote operations on IoT devices.
This article explores AWS IoT Jobs, its features, role in the cloud and DevOps lifecycle, and why it is a critical service in large-scale IoT deployments.

📌 Service Overview:-

AWS IoT Jobs is a managed service under AWS IoT Core that allows you to define, schedule, and track remote operations—called jobs—on IoT devices or groups of devices.

A job can perform tasks such as:

Firmware updates
Software installations
Configuration changes
Security patches
Diagnostics and maintenance tasks
Jobs are executed securely and reliably, even when devices are intermittently connected.

⭐ Key Features:-

🔹 1. Scalable Device Management

Execute jobs on single devices, groups, or entire fleets
Supports millions of devices simultaneously

🔹 2. Secure Execution

Uses AWS IoT Core authentication and authorization
Jobs are delivered using TLS-encrypted MQTT or HTTPS

🔹 3. Job Scheduling & Targeting

Schedule jobs immediately or at a later time
Target devices using Thing Groups

🔹 4. Job Monitoring & Status Tracking

Track job states such as:

QUEUED
IN_PROGRESS
SUCCEEDED
FAILED
Provides real-time visibility

🔹 5. Retry & Rollback Support

Automatically retries failed jobs
Helps maintain device stability

☁️ AWS Category / Cloud Domain

Category Domain
AWS IoT Core Internet of Things (IoT)
Device Management Cloud Operations
Security-Aware Automation DevSecOps

🔄 Where It Fits in Cloud / DevOps Lifecycle

AWS IoT Jobs plays a vital role in the Operate & Maintain phase of the cloud lifecycle.

🔁 Cloud Lifecycle Mapping

Plan → Build → Deploy → Operate → Optimize
↑
AWS IoT Jobs

🔧 DevOps Perspective

Automates post-deployment operations
Enables continuous device updates
Reduces manual intervention

🛡️ DevSecOps Perspective

Ensures security patches are applied consistently
Supports compliance and secure updates
Enables “shift-left security” for IoT systems

💻 Programming Language / Access Methods

AWS IoT Jobs can be accessed using multiple methods:

🔹** Programming Languages**

Python (Boto3 SDK)
Java
JavaScript (Node.js)
C / C++ (for device-side execution)

🔹** Access Methods**

AWS Management Console
AWS CLI
AWS SDKs
MQTT topics
REST APIs

Example (AWS CLI):

aws iot create-job \
--job-id firmware-update-001 \
--targets arn:aws:iot:region:account-id:thinggroup/ProductionDevices \
--document file://job.json

💰 Pricing Model:-

AWS IoT Jobs follows a pay-as-you-go pricing model.

💵 Pricing Factors

Number of job executions
Messaging usage via AWS IoT Core
No upfront cost or minimum fee

You pay only for what you use
Cost-efficient for large-scale deployments

🌍 Real-World Use Cases:-

OTA (Over-The-Air) firmware updates
Remote security patch deployment
Configuration changes for smart devices
Device diagnostics and health checks
Industrial IoT fleet management

✨ Innovative Insight: AWS IoT Jobs as “CI/CD for Devices”

Think of AWS IoT Jobs as CI/CD for physical devices:

Code → Application servers

Jobs → IoT devices

Just like Jenkins automates software delivery, AWS IoT Jobs automates device lifecycle management.

📝 Conclusion:-

AWS IoT Jobs is a powerful and scalable solution for managing IoT devices securely in the cloud. By integrating automation, monitoring, and security, it becomes a critical component in cloud-native IoT architectures and DevSecOps pipelines.
For organizations managing large IoT fleets, AWS IoT Jobs is not optional—it’s essential.
Thanks to @santhoshnc Sir for Assigning this Assignment

🔧Jenkins: The Heart of Continuous Integration in DevSecOps

Jaswant Karun — Thu, 18 Dec 2025 15:45:11 +0000

In the DevOps & DevSecOps Periodic Table, Jenkins stands out as one of the most powerful and widely adopted tools for Continuous Integration and Continuous Delivery (CI/CD).
It automates repetitive development tasks, ensures faster releases, and improves software quality by integrating security early in the pipeline.
This blog explores Jenkins—its features, role in DevSecOps, and why it continues to be a backbone tool for modern software teams.

📌 Overview of Jenkins:-

Jenkins is an open-source automation server used to automate building, testing, and deploying software projects.
It enables developers to continuously integrate code changes from multiple contributors and detect issues early in the development lifecycle.

Originally developed as Hudson in 2004, Jenkins evolved into a community-driven project and became one of the most essential tools in DevOps.

⭐ Key Features of Jenkins:-
🚀 1. Continuous Integration & Continuous Delivery

1. Automatically builds and tests code after every commit
1. Reduces integration errors
1. Speeds up release cycles

🔌 2. Plugin Ecosystem (1800+ Plugins)

Supports Git, GitHub, Docker, Kubernetes, AWS, Azure, SonarQube, OWASP, and more
Highly customizable for any DevOps workflow

🧩 3. Pipeline as Code

Jenkins pipelines are defined using Jenkinsfile
Enables version-controlled CI/CD pipelines,

pipeline {
agent any
stages {
stage('Build') {
steps {
echo 'Building the application'
}
}
stage('Test') {
steps {
echo 'Running tests'
}
}
stage('Deploy') {
steps {
echo 'Deploying application'
}
}
}
}

🔐 4. DevSecOps Integration:-

Integrates security tools like:

SonarQube
OWASP Dependency Check
Trivy
Snyk
Enables shift-left security

⚙️ 5. Distributed Builds

Supports master-agent architecture
Scales easily across multiple machines

How Jenkins Fits into DevOps & DevSecOps
Jenkins in DevOps

Jenkins acts as the automation backbone:

Pulls code from Git
Builds applications
Runs automated tests
Deploys to servers or cloud platforms

🛡️ Jenkins in DevSecOps

Jenkins integrates security checks directly into pipelines:

Static code analysis
Vulnerability scanning
Compliance checks
Security testing before deployment

➡️ This ensures security is not an afterthought, but part of the pipeline.

💻 Programming Languages Used in Jenkins

Java – Core Jenkins engine
**
Groovy** – Jenkins Pipeline scripts

Shell / Bash – Automation scripts

Python – Testing and security tools integration

🏢 Parent Company of Jenkins

Original Creator: Kohsuke Kawaguchi

Maintained by: Jenkins Community

Previously supported by: Sun Microsystems & Oracle

👉 Jenkins is community-driven, not owned by a single company.

💰 Open Source or Paid?

Feature Jenkins
Open Source Yes
License MIT License
Cost Free
Enterprise Support Available via CloudBees
**
🧪 Jenkins in the DevSecOps Periodic Table:-**

In the DevSecOps Periodic Table, Jenkins belongs to:

Category: CI/CD & Automation

Role: Orchestrator

Impact: Faster, safer, and reliable software delivery

It acts as the central atom connecting:

Source control
Build tools
Security scanners
Deployment platforms

🌍 Real-World Use Cases:-

Automating cloud deployments (AWS, Azure, GCP)
Microservices CI/CD pipelines
Security scanning before production
Containerized deployments with Docker & Kubernetes

🚀 Why Jenkins is Still Relevant in 2025

Despite the rise of GitHub Actions, GitLab CI, and Azure DevOps, Jenkins remains relevant because:

Extreme flexibility
Huge plugin ecosystem
Strong community support
Cloud & container readiness

✨ Innovative Insight: Jenkins as the “Control Tower” of DevSecOps

Think of Jenkins as a control tower:

Every code change reports here
Every test, scan, and deployment is monitored
Every failure is instantly visible

In DevSecOps, Jenkins ensures:

“No insecure code reaches production unnoticed.”

📝 Conclusion:-

Jenkins is more than a CI tool—it is a DevOps and DevSecOps enabler.
Its flexibility, open-source nature, and massive ecosystem make it a must-know tool for engineers entering the DevOps world.
If you want automation, speed, security, and reliability, Jenkins is still a champion.

Special thanks to @santhoshnc Sir for guidance throughout this assignment.

DBMS – Transactions, Deadlocks & Log-Based Recovery

Jaswant Karun — Thu, 02 Oct 2025 11:52:38 +0000

We will explore Transactions, Deadlocks, and Log-Based Recovery using SQL.
We use the Accounts table as our base schema and demonstrate ACID concepts with rollback, simulate a deadlock, and discuss log-based recovery.

Step 1: Create Table & Insert Data

CREATE TABLE CustomerAccounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

SELECT * FROM CustomerAccounts;

Transaction – Atomicity & Rollback

Task: Transfer 500 from Alice to Bob, but rollback before committing.

Deduct 500 from Alice

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

-- Add 500 to Bob
UPDATE CustomerAccounts
SET balance = balance + 500
WHERE name = 'Bob';

Rollback transaction

ROLLBACK;

Check balances

SELECT * FROM CustomerAccounts;

Deadlock Simulation (Conceptual)

Session 1 (conceptual)

-- Lock Alice
UPDATE CustomerAccounts SET balance = balance - 100 WHERE name = 'Alice';
-- Do NOT commit

Session 2 (conceptual)
-- Lock Bob
UPDATE CustomerAccounts SET balance = balance - 200 WHERE name = 'Bob';
-- Do NOT commit

Continuing Session 1
-- Try updating Bob (held by Session 2)
UPDATE CustomerAccounts SET balance = balance + 100 WHERE name = 'Bob';

Continuing Session 2
-- Try updating Alice (held by Session 1)
UPDATE CustomerAccounts SET balance = balance + 200 WHERE name = 'Alice';

Expected in a real multi-session DB:

ORA-00060: deadlock detected while waiting for resource

Log-Based Recovery

Task: Show rollback and explain internal logs (undo/redo).

-- Update Charlie
UPDATE CustomerAccounts SET balance = balance + 300 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;

-- Verify balances
SELECT * FROM CustomerAccounts;

Conclusion:-

Atomicity: Rollback prevents partial updates.

Deadlock: Occurs when two sessions wait for each other (conceptual in LiveSQL).

Log-Based Recovery: Oracle maintains undo/redo logs for safe recovery.

Special thanks to @santhoshnc Sir for guidance throughout this assignment.

dbms #oracle #sql #transactions #deadlock #recovery #assignment

Indexing, Hashing & Query Optimization in DBMS

Jaswant Karun — Thu, 02 Oct 2025 11:07:13 +0000

We will focus on enhancing query performance in Oracle SQL by using indexes. We’ll create a Students table, insert sample records, and run optimized queries utilizing B-Tree and B+ Tree indexes.

Definition:

Indexing: A database index is a data structure that enhances the speed of data retrieval operations on a table, though it comes with additional costs in terms of writes and storage space.

Hash Index: Utilizes a hash function to enable rapid equality searches. (Note: In Oracle, true hash indexes are not directly supported; hash-like performance can be achieved through specific functions or clustering techniques.)

B+ Tree Index: An extension of the B-Tree where all values are stored at the leaf nodes, making it more efficient for range queries.

B-Tree Index: A balanced tree-based index commonly used for equality and range queries.

Query Optimization: The process of minimizing query execution time by leveraging indexes and writing efficient SQL statements.

Creating the Students Table

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);

Inserting Sample Records

INSERT INTO Students VALUES (101, 'Arjun', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Priya', 'CSBS', 7.8);
INSERT INTO Students VALUES (103, 'Kiran', 'ECE', 9.0);
INSERT INTO Students VALUES (104, 'Anita', 'ME', 8.2);
INSERT INTO Students VALUES (105, 'Vikram', 'CSBS', 8.8);
INSERT INTO Students VALUES (106, 'Ravi', 'ECE', 7.5);
INSERT INTO Students VALUES (107, 'Sneha', 'ME', 8.7);
INSERT INTO Students VALUES (108, 'Nikhil', 'CSBS', 6.9);
INSERT INTO Students VALUES (109, 'Maya', 'ECE', 8.0);
INSERT INTO Students VALUES (110, 'Aditya', 'CSBS', 9.2);
INSERT INTO Students VALUES (111, 'Tanya', 'ME', 7.9);
INSERT INTO Students VALUES (112, 'Rohan', 'CSBS', 8.3);
INSERT INTO Students VALUES (113, 'Divya', 'ECE', 9.1);
INSERT INTO Students VALUES (114, 'Karthik', 'ME', 7.7);
INSERT INTO Students VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students VALUES (116, 'Suresh', 'ECE', 8.4);
INSERT INTO Students VALUES (117, 'Meena', 'ME', 8.0);
INSERT INTO Students VALUES (118, 'Aravind', 'CSBS', 7.6);
INSERT INTO Students VALUES (119, 'Pooja', 'ECE', 8.9);
INSERT INTO Students VALUES (120, 'Rahul', 'ME', 8.1);

Creating a B-Tree Index on roll_no

CREATE INDEX idx_roll_no ON Students(roll_no);

Query: Fetch student with roll_no = 110

SELECT * FROM Students WHERE roll_no = 110;

Creating a B+ Tree Index on cgpa

Oracle automatically uses B+ Tree for numeric indexes.

CREATE INDEX idx_cgpa ON Students(cgpa);

Query: Display all students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0;

Creating an Index on dept for Fast Equality Search

Oracle does not support direct hash indexes for normal tables.
Instead, we use a regular index:

CREATE INDEX idx_dept ON Students(dept);

Query: Retrieve all students from the CSBS department

SELECT * FROM Students WHERE dept = 'CSBS';

Steps Summary:-

Defined the Students table with columns: roll_no, name, dept, and cgpa.
Added 20 sample records into the table.
Built a B-Tree index on roll_no to enable faster lookups.
Created a B+ Tree index on cgpa to improve range query performance.
Indexed the dept column to accelerate equality-based searches.
Executed queries to test and confirm indexing benefits in performance.

Conclusion:-

By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS.

Thanks to @santhoshnc Sir for guiding me through indexing and query optimization concepts.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database

Cursor and Trigger in DBMS

Jaswant Karun — Thu, 02 Oct 2025 09:50:08 +0000

Definition of Cursor in DBMS:-

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.

Cursor Example – Retrieving and Displaying Employee Records Where the Salary is Greater Than ₹50,000

Step 1: Create Employee Table

CREATE TABLE Employee (
Emp_ID NUMBER PRIMARY KEY,
Emp_Name VARCHAR2(50),
Salary NUMBER
);

Step 2: Insert Sample Data

INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (1, 'Ramesh', 60000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (2, 'Suresh', 45000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (3, 'Anita', 75000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (4, 'Kavya', 50000);

Step 3: Create and Process Cursor

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

Explanation:

The cursor emp_cursor selects employees earning more than 50,000.

DBMS_OUTPUT.PUT_LINE prints each employee’s name and salary.

Definition of Trigger in DBMS:-

A trigger in a Database Management System (DBMS) is a special type of stored procedure that is automatically invoked or executed by the system whenever a specific event, such as an insertion, update, or deletion, occurs on a particular table or view. Triggers are mainly used to maintain data integrity, enforce business rules, and keep audit logs without requiring explicit calls from the user.

Trigger Example – Executing an AFTER INSERT Operation on the Students Table

Step 1: Create Students Table

CREATE TABLE Students (
Student_ID NUMBER PRIMARY KEY,
Student_Name VARCHAR2(50),
Course VARCHAR2(50)
);

Step 2: Create Student_Audit Table

CREATE TABLE Student_Audit (
Audit_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Student_ID NUMBER,
Student_Name VARCHAR2(50),
Action VARCHAR2(50),
Action_Time TIMESTAMP
);

Step 3: Create AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (Student_ID, Student_Name, Action, Action_Time)
VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;
/

Explanation:

Trigger automatically logs new students into Student_Audit.
NEW references the inserted row, and SYSTIMESTAMP captures insertion time.

Step 4: Test Trigger

INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (1, 'Ravi', 'Computer Science');
INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (2, 'Meena', 'Electrical Engineering');

Step 5: Verify Audit Table

SELECT * FROM Student_Audit;

Conclusion:-

Cursors provide the capability to process query results one row at a time based on specific conditions, enabling detailed and controlled data handling.
Triggers, on the other hand, help in automating actions such as maintaining logs or updating related tables, ensuring that certain tasks are carried out automatically without the need for manual execution.
Together, these features play a vital role in advanced database management by improving efficiency, consistency, and reliability.

Thank @santhoshnc Sir for his valuable guidance and continuous support in successfully completing this DBMS assignment.

dbms #sql #oracle #plsql #database #cursors #triggers #programming #assignment #learning

Database Normalization

Jaswant Karun — Thu, 02 Oct 2025 08:00:01 +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.

Step 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.

Special thanks to @santhoshnc for mentoring me on database normalization concepts!

SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #BCNF #4NF #5NF #DataModeling

ACID Properties with SQL Transactions in DBMS

Jaswant Karun — Thu, 02 Oct 2025 06:28:49 +0000

Demonstrating ACID Properties with SQL Transactions:-

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;

Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT).

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

Insert 3 sample rows.

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 7000);

Run it.
👉 Output: 3 rows inserted.

Check the table:

SELECT * FROM Accounts;

Output:
acc_no | name | balance
1 | Alice | 5000
2 | Bob | 3000
3 | Charlie | 7000

Atomicity (Rollback vs Commit)

Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

ROLLBACK:

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

Output:
balances remain unchanged (Alice=5000, Bob=3000).
This proves atomicity: either all updates happen, or none.

COMMIT:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
COMMIT;
SELECT * FROM Accounts;

Output: Alice=4500, Bob=3500.
Committed → permanent update.

Consistency

Try inserting a wrong record:

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

Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.

Isolation (needs 2 query tabs)

Open two query tabs in Workbench:

Tab A = Session 1

Tab B = Session 2

In Tab A,

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
-- Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;

Tab A sees the reduced balance (2500).

In Tab B,

SELECT balance FROM Accounts WHERE acc_no = 1;

If using default isolation (REPEATABLE READ), Tab B still sees old committed value (4500), not Tab A’s uncommitted update.

Now, back in Tab A:

ROLLBACK;

Both sessions see Alice back to 4500.
This shows isolation.

Durability

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

Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Charlie’s balance increases (7500).

Now restart MySQL server
Reconnect, run again:

USE acid_demo;
SELECT * FROM Accounts WHERE acc_no = 3;

Balance is still 7500.
This proves durability: committed changes survive restarts.

Conclusion:

Atomicity: Transactions are “All or Nothing”.

Consistency: Database enforces valid data.

Isolation: Concurrent transactions do not interfere.

Durability: Committed changes are permanent.

Thanks @santhoshnc Sir for his guidance and support and for Everything

dbms #MySql #oracle #transactions #acid #database #learning

Simple College Student Schema

Jaswant Karun — Tue, 30 Sep 2025 17:49:56 +0000

Introduction:-

MongoDB is one of the most popular NoSQL databases used by developers today for building modern, scalable applications. Unlike traditional relational databases, MongoDB stores data in flexible JSON-like documents, making it easier to work with real-world scenarios.

In this blog, I’ll walk you through CRUD operations (Create, Read, Update, Delete) in MongoDB using a simple example: a college 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

Setup: Creating a Cluster

Create a free MongoDB Atlas account

Create a cluster and a database called collegeDB

Inside it, create a collection called students

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.

Code:-

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

{
student_id: "S002",
name: "Santhosh",
age: 21,
department: "CSE",
year: 3,
cgpa: 8.5
}

{
student_id: "S003",
name: "Gangeswara",
age: 22,
department: "ECE",
year: 4,
cgpa: 7.2
}

{
student_id: "S004",
name: "Rakshanth",
age: 19,
department: "CSBS",
year: 1,
cgpa: 9.3
}

{
student_id: "S005",
name: "Naveens",
age: 20,
department: "Mechanical",
year: 2,
cgpa: 6.8
}

Read (Query):-

Display all student records.

Fetch all students: {}

Find all students with CGPA > 8.

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

Find students belonging to the Computer Science department.

db.students.find({ department: "CSE" })

Update:-

Update the CGPA of a specific student.

{ "student_id": "S005" }

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

{ "year": 3 }

Delete:-

Delete one student record by student_id.

{ "student_id": "S004" }

Result:

Delete all students having CGPA < 7.5

{ "cgpa": { "$lt": 7.5 } }

Result:

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.

This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems

Thanks to @santhoshnc sir for guiding and motivating us.

College Student & Course Management System

Jaswant Karun — Thu, 21 Aug 2025 12:03:20 +0000

Introduction

In this post, I am sharing a Database Management System (DBMS) small project using SQL (Oracle).
Managing students, courses, and enrollments is a common use case in universities and colleges.
To implement this, we can design a simple database system using SQL.
This post walks through DDL, DML, Joins, Views, Procedures, and Functions step by step with examples.

Database Schema

Students table → stores student details

Faculty table → stores faculty details

Courses table → stores courses offered

Enrollments table → connects students and courses

StudentID

StudentID → NUMBER, Primary Key

Name → VARCHAR2(50), NOT NULL

Dept → VARCHAR2(30)

DOB → DATE

Email → VARCHAR2(50), UNIQUE

PhoneNo → CHAR(10), must be 10 digits

Courses Table

CourseID → NUMBER, Primary Key

CourseName → VARCHAR2(50), NOT NULL

Credits → NUMBER(2), must be between 1 and 5

Enrollments Table

EnrollID → NUMBER, Primary Key

StudentID → NUMBER, Foreign Key → Students(StudentID)

CourseID → NUMBER, Foreign Key → Courses(CourseID)

Grade → CHAR(2)

Faculty Table

FacultyID → NUMBER, Primary Key

FacultyName → VARCHAR2(50), NOT NULL

Dept → VARCHAR2(30)

Email → VARCHAR2(50), UNIQUE

Relationships

One Student can have many Enrollments

One Course can have many Enrollments

Enrollments table = Junction table (Many-to-Many between Students and Courses)

Faculty can later be linked to Courses

Relationships

One Student → Many Enrollments

One Course → Many Enrollments

Enrollments acts as a junction table (Many-to-Many) between Students and Courses.

Faculty can be linked later to Courses (One-to-Many).

Create Tables (DDL)

CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

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

Insert Sample Data (DML)

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-14', 'arun.kumar@example.com');

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'Database Systems', 4);

Alter & Constraints

ALTER TABLE Students ADD PhoneNo CHAR(10);

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

Functions & Aggregates

SELECT UPPER(Name), LENGTH(Email) FROM Students;
SELECT AVG(Credits) FROM Courses;
SELECT COUNT(DISTINCT StudentID) FROM Enrollments;

Joins & Views

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;

CREATE VIEW StudentCoursesView AS
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;

Stored 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;

COMMIT;

END;
/

Conclusion

This simple College Student & Course Management System demonstrates how SQL can be used to handle real-world scenarios like student registrations, course management, enrollments, grading, and reporting.
By using DDL, DML, Constraints, Aggregates, Joins, Views, and Stored Procedures, we built a robust and structured system.
This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems

Thanks to @santhoshnc sir for guiding and motivating us.