DEV Community: Kausi Tarun

AWS Analytics Services

Kausi Tarun — Thu, 18 Dec 2025 15:46:09 +0000

🔹 AWS Analytics Services

1️⃣ Amazon Redshift

🔸 Service Overview

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse service used for fast SQL-based analytics on large datasets.

🔸 Key Features

Columnar storage

1.Massively Parallel Processing (MPP)

2.High-performance SQL queries

3.Integration with S3, Glue, QuickSight

4.Redshift Spectrum for querying S3 data

5.Automated backups and scaling

🔸 AWS Category / Cloud Domain

1.Analytics

2.Data Warehousing

🔸 Where It Fits in Cloud / DevOps Lifecycle

1.Data analytics & business intelligence

2.Reporting and decision-making stage

3.Used after data ingestion and ETL processes

🔸 Programming Language / Access Methods

1.SQL

2.AWS Console

3.AWS CLI

4.JDBC / ODBC

5.SDKs (Python, Java, etc.)

🔸 Pricing Model

1.Pay for node type and number of nodes

2.On-demand or Reserved instances

3.Separate charges for storage and Spectrum queries

2️⃣ Amazon Athena

🔸 Service Overview

Amazon Athena is a serverless interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL.

🔸 Key Features

1.Serverless (no infrastructure management)

2.Query data directly from S3

3.Supports structured and semi-structured data

4.Integrates with AWS Glue Data Catalog

5.Fast ad-hoc querying

🔸 AWS Category / Cloud Domain

1.Analytics

2.Serverless Data Query

🔸 Where It Fits in Cloud / DevOps Lifecycle

1.Ad-hoc analysis

2.Log analysis and monitoring

3.Quick insights without ETL

🔸 Programming Language / Access Methods

1.SQL

2.AWS Console

3.AWS CLI

4.SDKs (Python, Java, etc.)

5.JDBC / ODBC

🔸 Pricing Model

1.Pay per query

2.Charged per TB of data scanned

3.No infrastructure or cluster cost

Conclusion

Redshift is best for large-scale analytics and BI workloads.

Athena is best for quick, serverless SQL queries on S3 data.

Google Kubernetes Engine

Kausi Tarun — Thu, 18 Dec 2025 15:34:56 +0000

🔹 Tool Name: Google Kubernetes Engine (GKE)

1️⃣ Overview of the Tool

Google Kubernetes Engine (GKE) is a managed Kubernetes service provided by Google Cloud. It helps you deploy, manage, and scale containerized applications using Kubernetes without managing the underlying infrastructure.

2️⃣ Key Features

1.Managed Kubernetes control plane

2.Automatic scaling (Horizontal & Vertical Pod Autoscaling)

3.Integrated security (IAM, Workload Identity)

4.Automated upgrades and patching

5.Monitoring and logging with Google Cloud Operations

6.Support for multi-cluster and hybrid deployments

3️⃣ How It Fits into DevOps / DevSecOps

DevOps:

1.Enables CI/CD pipelines for containerized applications

2.Automates deployment, scaling, and rollback

DevSecOps:

1.Built-in security policies and access control

2.Image vulnerability scanning

2.Network policies and secrets management

4.Compliance and audit logging

4️⃣ Programming Languages Used / Supported

GKE itself is built on Go (Golang), but it supports applications written in:

1.Java

2.Python

3.JavaScript (Node.js)

4.Ruby, PHP, etc.

5️⃣ Parent Company

1.Parent Company: Google

2.Platform: Google Cloud Platform (GCP)

6️⃣ Open Source or Paid

1.Kubernetes: Open Source

2.GKE: Paid (Managed Service)

You pay for cluster management and underlying cloud resources
Free tier available for experimentation(limited)

Conclusion

GKE is a managed Kubernetes service by Google that simplifies container orchestration and enhances DevOps and DevSecOps workflows through automation, scalability, and built-in security features.

MongoDB

Kausi Tarun — Wed, 08 Oct 2025 15:16:49 +0000

Introduction to MongoDB

MongoDB is a NoSQL (Non-Relational) database that stores data in a flexible, document-oriented format called BSON (Binary JSON). It is widely used for modern applications that require scalability, flexibility, and high performance.

🧩 Key Features of MongoDB

Document-Oriented Storage

Data is stored in documents (similar to JSON objects).

Each document contains key–value pairs, making it more flexible than traditional row-column structures.

Schema-less

Unlike SQL databases, MongoDB does not require a fixed schema.

Documents in the same collection can have different fields and structures.

Collections and Databases

A database contains multiple collections.

A collection contains multiple documents.
(Equivalent of table → collection, and row → document in SQL.)

Scalability

Supports horizontal scaling using sharding, allowing data to be distributed across multiple servers.

Replication and High Availability

MongoDB uses replica sets for automatic failover and data redundancy.

Rich Query Language

Supports powerful queries, filtering, aggregation, and indexing for performance optimization.

⚙️ Basic MongoDB Structure

SQL Term MongoDB Equivalent

Database Database
Table Collection
Row Document
Column Field
Primary Key _id field (auto-generated)

🚀 Advantages of MongoDB

Flexible data model

High performance and scalability

Easy integration with programming languages (like Python, Java, Node.js, etc.)

Ideal for big data, real-time analytics, and cloud applications

Schema (Collection: students)
Each student document should follow the structure below:
{
"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.

2️⃣ Read (Query)
Display all student records.

Find all students with CGPA > 8.

Find students belonging to the Computer Science department.

3️⃣ Update
Update the CGPA of a specific student.

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

4️⃣ Delete
Delete one student record by student_id.

Delete all students having CGPA < 7.5.
Deliverables
MongoDB queries (insert, find, update, delete.

Conclusion

MongoDB is a powerful database solution designed for handling large volumes of unstructured or semi-structured data efficiently. Its flexibility, scalability, and ease of use make it a popular choice for modern web and mobile applications.

Acid Property with SQL Transactions

Kausi Tarun — Wed, 08 Oct 2025 14:48:11 +0000

🔷 Introduction to ACID Properties in SQL Transactions

In SQL databases, a transaction is a sequence of one or more SQL operations (like INSERT, UPDATE, or DELETE) executed as a single unit of work.
To ensure the reliability and consistency of data, transactions must follow the ACID properties.

1.Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

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

3.Consistency: Try inserting a record with negative balance → should be rejected.

4.Isolation: Run two sessions at once – one updating, the other reading → observe isolation.

Durability: Commit a transaction → restart DB → ensure data persists.

DBMS - Transactions,Deadlocks & Log-based Recovery

Kausi Tarun — Wed, 08 Oct 2025 14:36:04 +0000

Working with databases is not just about storing data — it’s about ensuring reliability, atomicity, and consistency, especially when multiple users or processes are involved. In this post, we’ll explore three important concepts using a simple Accounts table:

✅ Transactions & Rollback (Atomicity)

🔒 Deadlock Simulation

📝 Log-Based Recovery

Setup: The Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO CustomerAccounts VALUES (1, 'Emily', 1000);
INSERT INTO CustomerAccounts VALUES (2, 'Bobby', 1500);
INSERT INTO CustomerAccounts VALUES (3, 'Caleb', 2000);

SELECT * FROM CustomerAccounts;

Transaction – Atomicity & Rollback

Transactions ensure all-or-nothing execution. Let’s try transferring 500 from Emily to Bobby, but roll it back midway.

Deduct 500 from Emily

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

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

Rollback transaction

Deadlock Simulation

Deadlocks occur when two transactions wait on each other’s locks. Let’s simulate with two sessions:

Session 1:

-- Lock Emily
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Emily';
-- Do NOT commit

Session 2:
-- Lock Bobby
UPDATE CustomerAccounts SET balance = balance - 200 WHERE name = 'Bobby';
-- Do NOT commit

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

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

Log-Based Recovery

Modern DBMSs use logs (MySQL → Binary Log, PostgreSQL → WAL) to ensure durability and rollback safety.

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

-- Rollback
ROLLBACK;

-- Verify balances
SELECT * FROM CustomerAccounts;

In this tutorial, we covered:

Transactions & Rollback → Ensures atomicity

Deadlock Simulation → Shows concurrency pitfalls

Log-Based Recovery → Demonstrates how databases ensure durability

These concepts form the backbone of ACID properties in relational databases.

Indexing ,Hashing & Query Optimization in DBMS

Kausi Tarun — Wed, 08 Oct 2025 14:22:46 +0000

Databases handle massive data efficiently using indexes and hashing. Instead of scanning entire tables, indexes act like the index of a book, making lookups faster.

In this blog, we’ll build a Students table, create B-Tree, B+Tree, and Hash indexes, and run queries to see their effect.

📖 Key Definitions
🔹 Indexing

Indexing is a technique to speed up data retrieval from a database. Instead of scanning the whole table, the database uses an index (like a book index) to locate the rows quickly.

B-Tree Index

A B-Tree (Balanced Tree) index stores keys in a sorted order, allowing logarithmic time searches. It is efficient for:

B+ Tree Index

A B+ Tree is a variation of the B-Tree where all values are stored in the leaf nodes, and internal nodes only store keys for navigation.

Hash Index

A Hash Index uses a hashing function to map keys (like dept) into buckets.

Query Optimization

The process of minimising query execution time by leveraging indexes and writing efficient SQL statements.

Step 1: Create Students Table

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

Inserting Sample Records

INSERT INTO Students1 VALUES (101, 'Ana', 'CSBS', 8.5);
INSERT INTO Students1 VALUES (102, 'Paul', 'CSBS', 7.8);
INSERT INTO Students1 VALUES (103, 'Kevin', 'ECE', 9.0);
INSERT INTO Students1 VALUES (104, 'Angelin', 'ME', 8.2);
INSERT INTO Students1 VALUES (105, 'Vanessa', 'CSBS', 8.8);
INSERT INTO Students1 VALUES (106, 'Ria', 'ECE', 7.5);
INSERT INTO Students1 VALUES (107, 'Samuel', 'ME', 8.7);
INSERT INTO Students1 VALUES (108, 'Noah', 'CSBS', 6.9);
INSERT INTO Students1 VALUES (109, 'Marin', 'ECE', 8.0);
INSERT INTO Students1 VALUES (110, 'Joseph', 'CSBS', 9.2);
INSERT INTO Students1 VALUES (111, 'Trinita', 'ME', 7.9);
INSERT INTO Students1 VALUES (112, 'Ryan', 'CSBS', 8.3);
INSERT INTO Students1 VALUES (113, 'Daniel', 'ECE', 9.1);
INSERT INTO Students1 VALUES (114, 'Kane', 'ME', 7.7);
INSERT INTO Students1 VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students1 VALUES (116, 'Sarah', 'ECE', 8.4);
INSERT INTO Students1 VALUES (117, 'Merlin', 'ME', 8.0);
INSERT INTO Students1 VALUES (118, 'James', 'CSBS', 7.6);
INSERT INTO Students1 VALUES (119, 'Page', 'ECE', 8.9);
INSERT INTO Students1 VALUES (120, 'Reynolds', 'ME', 8.1);

B-Tree Index on roll_no
Most DBMSs use B-Trees to index numeric/ordered columns.

CREATE INDEX idx_roll_no ON Students1(roll_no);

Query with Index
This fetches details of roll_no = 110 in O(log n) instead of scanning all rows.

SELECT * FROM Students1 WHERE roll_no = 110;

B+ Tree Index on CGPA
B+ Trees are used for range queries, making them perfect for CGPA lookups.

CREATE INDEX idx_cgpa ON Students1(cgpa);

Query
Display all students with a CGPA> 8.0

SELECT * FROM Students1 WHERE cgpa > 8.0;

Hash Index on dept
Hashing is great for exact matches (not ranges).

CREATE INDEX idx_dept ON Students1(dept);

Query
Retrieve all students from the CSBS department

SELECT * FROM Students1 WHERE dept = 'CSBS';

In this tutorial, we explored:

B-Tree Index → Fast lookup by roll_no

B+Tree Index → Efficient range queries (CGPA > 8.0)

Hash Index → Quick equality checks (dept = CSBS)

Indexes make queries 10x–100x faster, but they also consume storage & slow down inserts/updates. Use them wisely for query optimization!

Cursor and Trigger in DBMS

Kausi Tarun — Wed, 08 Oct 2025 14:03:19 +0000

When working with databases, sometimes we need to process records row by row (using Cursors) or automatically respond to events (using Triggers).

In this tutorial, we’ll:

✅ Create a Cursor that fetches employees with a salary > 50,000

✅ Build an AFTER-INSERT Trigger to maintain a student audit log

🔹 Cursor

A cursor is a pointer that lets you process query results row by row instead of all at once. Useful when applying conditions or logic to each record.

Step 1: Employee Cursor Example

Let’s create a cursor to display employee names with salary > 50,000.

Step i: Create Employee Table

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

Step ii: Insert Sample Data

INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (1, 'Renner', 60000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (2, 'Samuel', 45000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (3, 'Ana',
75000);
INSERT INTO Employees (Emp_ID, Emp_Name, Salary) VALUES (4, 'Kylie', 50000);

Step iii: Cursor Implementation

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

Trigger

A trigger is a stored program that automatically runs when a specific event occurs (like INSERT, UPDATE, or DELETE).

Step 2:AFTER INSERT Trigger

We’ll now create a Students table and a Students_Audit table to keep track of new registrations.

Step i: Create Students Table

CREATE TABLE Students2 (
Student_ID NUMBER PRIMARY KEY,
Student_Name VARCHAR2(50),
Course VARCHAR2(50)
);
Step ii: Create Students_Audit Table

CREATE TABLE Students_Audit (
Audit_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Student_ID NUMBER,
Student_Name VARCHAR2(50),
Action VARCHAR2(50),
Action_Time TIMESTAMP
);
Step iii: Create AFTER INSERT Trigger(Trigger Implementation)

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students2
FOR EACH ROW
BEGIN
INSERT INTO Students_Audit (Student_ID, Student_Name, Action, Action_Time)
VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;
/
Test the Trigger
Step iv: Verify Audit Table

SELECT * FROM Students_Audit;

INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (1, 'Renner', 'Computer Science');
INSERT INTO Students2 (Student_ID, Student_Name, Course) VALUES (2, 'Martin', 'Mechanical Engineering');

In this tutorial, we learned:

Cursor → Process query results row by row

Trigger → Automatically log student registrations after insert

These features add power and automation to SQL programming!

Database Normalisation

Kausi Tarun — Wed, 08 Oct 2025 11:10:55 +0000

Database Normalisation

Database normalization is the process of structuring a relational database to reduce redundancy and improve data integrity.

In this blog, we’ll normalize a student-course-instructor dataset from Unnormalized Form → 1NF → 2NF → 3NF, and implement it in SQL.

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.

*First Normal Form (1NF)
*
Rule: Eliminate repeating groups, ensure atomic values.

So, we split multi-valued attributes into separate rows:

1.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)
);
2.Second Normal Form (2NF)

Rule: Remove partial dependency → non-key attributes should depend on the whole primary key.

Here, student_id depends on student info, course_id depends on course info, and instructor depends on the course.
So, we split into three tables:

SQL Create Tables (2NF):

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)
);
3.Third Normal Form (3NF)

Rule: Remove transitive dependencies (non-key attributes depending on other non-key attributes).

Here, instructor_phone depends on instructor, not on course_id. So we separate Instructor data:

SQL Create Tables (3NF):

REATE 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)
);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 6: Insert Sample Data

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

-- Enrol

lment
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 6: Insert Sample Data

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

College Management System

Kausi Tarun — Thu, 21 Aug 2025 18:17:43 +0000

Introduction
A college management system uses a database management system as its core to store,manage,and retrieve vast amounts of information,such as studentand staff details,academic records,admissions,fees,and attendance,etc.it demonstrates key database concepts such as queries and conclusion of the college management system.

1.Primary Keys

StudentID in Students

CourseID in Courses

EnrollID in Enrollments

2.Unique constraints

Email in Students is unique.

3.Foreign keys

StudentID in Enrollments references Students(StudentID)

CourseID in Enrollments references Courses(CourseID)

4.Data types

DOB as DATE

Credits restricted to 2 digits (NUMBER(2))

1.Composite key in Enrollments
CREATE TABLE Enrollments (
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2),
PRIMARY KEY (StudentID, CourseID)
);
2.Cascade Delete/Update
StudentID NUMBER REFERENCES Students(StudentID) ON DELETE CASCADE,
CourseID NUMBER REFERENCES Courses(CourseID) ON DELETE CASCADE
3.Check constraints
Credits NUMBER(2) CHECK (Credits > 0),
Grade CHAR(2) CHECK (Grade IN ('A+', 'A', 'B+', 'B', 'C', 'D', 'F'))

Conclusion
1.DBMSs are fundamental for various applications, from simple data storage to complex data analysis and decision-making processes.
2.They serve as the backbone for many technologies, including data science, data modeling, and machine learning.
3.Large organizations like Google and Amazon rely heavily on DBMSs to manage their vast data stores.