Forem: Jai Surya

Amazon Athena in the AWS Periodic Table

Jai Surya — Thu, 18 Dec 2025 16:07:49 +0000

Amazon Athena in the AWS Periodic Table ☁️

In the AWS Periodic Table, Amazon Athena stands out as a powerful, serverless analytics service. It enables users to analyze large volumes of data stored in Amazon S3 using standard SQL—without managing any infrastructure.

Service Overview

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using SQL. It is completely serverless, meaning there is no need to provision, configure, or manage servers. Athena is commonly used for ad-hoc querying, log analysis, and data exploration.

⭐ ##Key Features

*Serverless – no infrastructure to manage

*SQL-based querying (Presto / Trino engine)

*Directly queries data in Amazon S3

*Supports CSV, JSON, Parquet, ORC, Avro formats

*Fast query execution with parallel processing

*Integrates with AWS Glue Data Catalog

*Built-in security and access control using IAM

AWS Category / Cloud Domain

*Category: Analytics

*Cloud Domain: Big Data, Data Analytics, Data Warehousing

Where It Fits in Cloud / DevOps Lifecycle

Amazon Athena fits into the data analysis and monitoring phase of the Cloud and DevOps lifecycle.
It is widely used for:

*Log analysis and troubleshooting

*Security auditing and compliance reporting

*Data validation in CI/CD pipelines

*Ad-hoc analysis during development and operations

In DevOps and DevSecOps workflows, Athena helps teams quickly gain insights from operational and security data without deploying additional tools.

Programming Language / Access Methods

Query Language: SQL

Access Methods:

*AWS Management Console

*AWS CLI

*AWS SDKs (Java, Python, JavaScript, etc.)

*JDBC / ODBC drivers

*Integration with BI tools like Amazon QuickSight

##Pricing Model

Amazon Athena follows a pay-per-query pricing model.

*You are charged per amount of data scanned by each query

*No upfront costs or minimum fees

*Costs can be optimized by using compressed and columnar data formats like Parquet and ORC

Conclusion

Amazon Athena is a simple yet powerful analytics service that allows teams to query massive datasets with ease. Its serverless nature, SQL support, and seamless integration with other AWS services make it an essential component in modern cloud-based data analytics and DevOps environments.

I would like to sincerely thank my professor, @santhoshnc, for his guidance and encouragement in learning cloud technologies and AWS services like Amazon Athena.

Jenkins in DevSecOps Periodic Table

Jai Surya — Thu, 18 Dec 2025 13:51:08 +0000

Jenkins in the DevSecOps Periodic Table

In the DevSecOps Periodic Table, Jenkins holds a vital position as one of the most widely adopted automation tools. It plays a key role in enabling Continuous Integration and Continuous Delivery (CI/CD), making software development faster, reliable, and more secure.

##Overview of the Tool:

Jenkins is an open-source automation server that helps developers build, test, and deploy applications automatically. It acts as a central hub where code changes are continuously integrated, tested, and prepared for release. By automating repetitive tasks, Jenkins reduces human error and accelerates the development lifecycle.

⭐ ##Key Features:

*Continuous Integration & Continuous Delivery (CI/CD) automation

*Extensive plugin ecosystem (1800+ plugins)

*Easy integration with Git, Docker, Kubernetes, Maven, and more

*Pipeline-as-Code using Jenkinsfile

*Distributed builds using master-agent architecture

*Security integrations for DevSecOps workflows

🔄 ##How It Fits into DevOps / DevSecOps:

In DevOps, Jenkins automates the build and deployment process, ensuring faster feedback and continuous delivery.

In DevSecOps, Jenkins integrates security tools such as:

*SAST (Static Application Security Testing)

*DAST (Dynamic Application Security Testing)

*Dependency scanning

*Container image scanning

By embedding security checks directly into the CI/CD pipeline, Jenkins helps shift security left, making applications more secure from the early stages of development.

💻 ##Programming Language:

Jenkins is primarily written in Java.
Pipeline scripts are commonly written using Groovy, along with support for shell scripts, Python, and other languages depending on the project needs.

🏢 ##Parent Company:

Jenkins was originally developed by Kohsuke Kawaguchi at Sun Microsystems.
Today, Jenkins is maintained by the Jenkins open-source community under the Continuous Delivery Foundation (CDF), which is part of the Linux Foundation.

Open Source or Paid?:

✅ Jenkins is completely open source and free to use.
However, enterprises can opt for paid support and managed services from third-party vendors if required.

🧾 ##Conclusion:

Jenkins remains a cornerstone of the DevSecOps ecosystem due to its flexibility, scalability, and strong community support. Whether you are automating builds, deployments, or security checks, Jenkins continues to be a powerful and reliable choice in modern software development pipelines.

I would like to sincerely thank my professor, @santhoshnc, for guiding and inspiring me to explore DevOps and DevSecOps tools like Jenkins and helping me grow my technical knowledge.

Simple College Student Schema

Jai Surya — Wed, 08 Oct 2025 10:15:57 +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 (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: "Santhosh",
age: 20,
department: "CSE",
year: 2,
cgpa: 9
}

{
student_id: "S002",
name: "Jai surya",
age: 21,
department: "CSE",
year: 3,
cgpa: 8
}

{
student_id: "S003",
name: "Dinesh",
age: 22,
department: "ECE",
year: 4,
cgpa: 9
}

{
student_id: "S004",
name: "siva",
age: 19,
department: "AD",
year: 1,
cgpa: 9
}

{
student_id: "S005",
name: "Sandy",
age: 20,
department: "CSBS",
year: 2,
cgpa: 7.5
}

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": "S001" }

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.

Transactions, Deadlocks & Log Based Recovery in SQL

Jai Surya — Tue, 07 Oct 2025 14:18:23 +0000

In this assignment, 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.

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:

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

ACID Properties with SQL Transactions in DBMS

Jai Surya — Tue, 07 Oct 2025 13:57:21 +0000

ACID properties ensure reliability and correctness of database transactions.

Create Accounts Table:

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

Insert Sample Data:

INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Ravi', 5000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Meena', 7000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Kumar', 6000);

1. Atomicity:

Atomicity ensures that a transaction is treated as a single, indivisible unit of work.

Either all the operations within a transaction are completed successfully, or none are applied.

Scenario: Transfer ₹1000 from Ravi to Meena. If an error occurs, rollback to prevent partial updates.

-- Deduct ₹1000 from Ravi_
UPDATE Accounts
SET balance = balance - 1000
WHERE acc_no = 101;

-- Simulate an error
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'ErrorTest', 1000);

-- Add ₹1000 to Meena
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

-- Rollback transaction
ROLLBACK;

-- Verify final balances
SELECT * FROM Accounts;

Explanation:
_
If an error occurs during transfer, ROLLBACK ensures no partial changes remain.

Atomicity ensures transactions are all-or-nothing.

2. Consistency:

Consistency ensures that a transaction brings the database from one valid state to another, maintaining all defined rules, constraints, and relationships.

Scenario: Database should reject invalid data like negative balance.

-- Fix existing negative balances if any
UPDATE Accounts
SET balance = 0
WHERE balance < 0;

-- Add CHECK constraint
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);

-- Test invalid insert
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Anita', -500);

_Explanation:
_
Constraint prevents negative balances, maintaining data consistency.

Anita’s balance is set to 0 to satisfy the constraint.

3. Isolation:

Isolation ensures that concurrent transactions do not interfere with each other.
Each transaction should execute as if it were the only one running, preventing issues like dirty reads, lost updates, or uncommitted data access.

Scenario: Observe concurrent transactions.

Session 1: Update Kumar’s balance

UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 103;
-- Do not commit yet

Session 2: Read Kumar’s balance

SELECT * FROM Accounts
WHERE acc_no = 103;

Explanation:

Session 2 does not see uncommitted changes from Session 1.

Isolation ensures concurrent transactions do not interfere incorrectly.

4. Durability:

Durability guarantees that once a transaction has been committed, its results are permanently saved in the database, even if the system crashes or power fails.

Scenario: Commit a transaction and ensure changes persist after DB restart.

UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 101;

COMMIT;

-- After reconnecting/restarting DB
SELECT * FROM Accounts;

Explanation:

Changes persist permanently after commit.

Durability ensures committed data survives failures.

Conclusion:

The ACID properties — Atomicity, Consistency, Isolation, and Durability — form the foundation of reliable transaction management in database systems.
They ensure that all transactions are accurate, consistent, isolated, and permanent, protecting data integrity and maintaining trust in the system. Together, these principles make database operations robust, even in cases of errors, concurrent access, or failures.

I would like to thank @santhoshnc for his guidance and support in completing this DBMS assignment.

dbms #sql #oracle #plsql #transactions #acid #database #assignment #learning

Indexing, Hashing & Query Optimization in DBMS

Jai Surya — Tue, 07 Oct 2025 13:29:56 +0000

Definiton:

Indexing: Indexing is a data structure technique used in databases to quickly locate and access the data in a table without having to search every row.

Hash Index: A hash index uses a hash function to convert a search key into a hash value, which points directly to the location of the data record.

B+ Tree Index: A B+ tree index is a balanced tree data structure where all data records are stored at the leaf nodes, and internal nodes only store keys for navigation.

B- Tree Index: A B-tree index is a balanced tree where keys and data pointers can appear in both internal and leaf nodes.

Query Optimization: Query optimization is the process of choosing the most efficient way to execute a database query.

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

Conclusion:

Indexing is a crucial technique in databases that enhances the speed and efficiency of data retrieval. Different types of indexes—such as Hash Index, B-Tree Index, and B+ Tree Index—serve different purposes depending on the query type.

Hash Indexes are best for exact match queries.
B-Tree and B+ Tree Indexes are ideal for both range and equality searches, with B+ Trees being the most widely used in modern databases due to their efficiency in sequential access.
Finally, Query Optimization ensures that the database executes queries in the most efficient way possible, making full use of indexes and other optimization strategies. Together, indexing and query optimization greatly improve database performance and responsiveness.

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

Jai Surya — Tue, 07 Oct 2025 13:01:29 +0000

Definition of cursor in DBMS:

A cursor in DBMS is a database object used to retrieve, manipulate, and navigate through a result set row by row.
It acts like a pointer that allows you to process individual rows returned by an SQL query.

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.

Defintion of trigger in DBMS:

A trigger is a stored procedure that automatically executes (fires) in response to certain events on a table such as INSERT, UPDATE, or DELETE.

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 help in row-by-row processing of query results when set-based operations are not sufficient.

*Triggers automate database tasks by responding to data modification events automatically.

*Both cursors and triggers enhance database functionality, control, and automation but should be used carefully to maintain performance and data integrity.

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

Jai Surya — Tue, 07 Oct 2025 12:30:31 +0000

Introduction to database Normalization:

Database normalization is a systematic process of organizing data in a database to reduce redundancy and improve data integrity.
It involves dividing large, complex tables into smaller, related ones to eliminate anomalies and ensure consistent, efficient data storage.

step 1 : Base Table

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

step 2 : Identifying anomalies

Insertion Anomaly :Can’t add a new course unless a student is enrolled in it.

Update Anomaly :If Prof. Rao changes department, we must update multiple rows.

Deletion Anomaly :If Alice drops “OS,” we lose data about that course/instructor

step 3 : Converting to 1NF

1NF Rule:

Each cell should contain atomic values (no repeating groups).

Each record must be unique.

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 : Converting to 2NF

2NF Rule:

The table must already be in 1NF.

Remove partial dependencies — every non-key attribute must depend on the whole primary key, not just part of it.

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

Step 5 : converting to 3NF

Rule:

The table must already be in 2NF.

Remove transitive dependencies — non-key attributes must not depend on other non-key attributes.

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

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 :

By following these normalization steps — 1NF → 2NF → 3NF — we’ve:

Eliminated redundancy
Prevented anomalies
Improved data consistency
Made queries easier and more efficient

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

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

College Student & Course Management System using Oracle SQL

Jai Surya — Sun, 24 Aug 2025 15:41:05 +0000

Introduction

This article presents the implementation of a College Student & Course Management System using Oracle SQL. The system demonstrates key relational database concepts including table creation, constraints, data insertion, queries, views, and stored procedures.

The schema supports student information, course details, faculty records, and student-course enrollments, making it suitable for academic institution management.

Database Schema

The database consists of four primary entities:

Students – Student details.
Courses – Course metadata.
Enrollments – Student–Course relationships with grades.
Faculty – Faculty information.

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

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

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

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

Data Insertion
INSERT INTO Students VALUES (1, 'Siva', 'CSE', DATE '2003-03-12', 'siva@gmail.com');
INSERT INTO Students VALUES (2, 'Surya', 'ECE', DATE '2002-04-11', 'surya@gmail.com');
INSERT INTO Students VALUES (3, 'Dinesh', 'MECH', DATE '2001-10-25', 'dinesh@gmail.com');

INSERT INTO Courses VALUES (101, 'DBMS', 4);
INSERT INTO Courses VALUES (102, 'OS', 3);
INSERT INTO Courses VALUES (103, 'Networks', 5);

INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 1, 102, 'B');
INSERT INTO Enrollments VALUES (3, 2, 101, 'A');
INSERT INTO Enrollments VALUES (4, 3, 103, 'C');

Table Alterations and Constraints
ALTER TABLE Students ADD PhoneNo VARCHAR2(10);
ALTER TABLE Courses
ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

Query Operations

String Functions and Length Calculation
SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
FROM Students;
Aggregate Functions Across Tables
SELECT AVG(Credits) AS AvgCredits, COUNT(*) AS TotalStudents
FROM Students, Courses;

Join Operations
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;

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

Database View
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;

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

Conclusion
This system forms the foundation for an extensible academic management solution, which can be expanded to support attendance tracking, fee management, and academic analytics.