DEV Community: Gangeswara

AWS Service – Amazon S3 Glacier

Gangeswara — Thu, 18 Dec 2025 16:56:55 +0000

🔹 1. Service Overview

Amazon S3 Glacier is a secure, low-cost archival storage service.

Used for rarely accessed data.

Best for long-term data retention.

Integrated with the Amazon S3 ecosystem.

⭐ 2. Key Features

✔ Extremely low storage cost
✔ Designed for long-term backups
✔ High durability (99.999999999%)
✔ Data encrypted at rest & in transit
✔ Multiple storage classes (Glacier, Deep Archive)
✔ Supports lifecycle policies from S3
✔ Scalable for large volumes of data

☁️ 3. AWS Category / Cloud Domain

AWS Category: Storage Services

Cloud Domain:
• Cloud Storage
• Backup & Archival
• Disaster Recovery

🔄 4. Where It Fits in Cloud / DevOps Lifecycle

➤ Backup & restore stage
➤ Disaster recovery planning
➤ Storage of old application logs
➤ Archiving CI/CD pipeline artifacts
➤ Compliance & audit data storage
➤ Cost optimization in cloud lifecycle

💻 5. Programming Language / Access Methods

• AWS Management Console
• AWS Command Line Interface (CLI)
• AWS SDKs support:

Python (Boto3)

Java

Node.js

Shell scripts
• Infrastructure as Code tools (CloudFormation, Terraform)

💰 6. Pricing Model

🔹 Pay-as-you-go pricing
🔹 Very low storage cost
🔹 Retrieval cost depends on access speed
🔹 Extra charges for early deletion
🔹 No upfront or fixed cost

🧪 Ga – GitHub Actions (DevOps Periodic Table Element)

Gangeswara — Thu, 18 Dec 2025 16:10:56 +0000

🔹 1. Overview of the Tool

GitHub Actions is a CI/CD automation tool.

It automates build, test, and deploy processes.

Works directly inside GitHub repositories.

⭐ 2. Key Features

✔ Event-based workflows (push, pull request, schedule)
✔ YAML-based configuration
✔ GitHub Marketplace actions support
✔ GitHub-hosted & self-hosted runners
✔ Easy integration with cloud services

🔄 3. Role in DevOps / DevSecOps

➤ Enables Continuous Integration & Continuous Deployment
➤ Automates pipelines without external tools
➤ Supports security scans & code analysis
➤ Helps shift security left in DevSecOps

💻 4. Programming Languages Used

• YAML (workflow configuration)
• JavaScript
• Python
• Shell scripting
• Docker

🏢 5. Parent Company

Developed by GitHub

Owned by Microsoft

🔓 6. Open Source / Paid

🔹 Free tier available
🔹 Paid plans for higher usage
🔹 Many actions are open source

Indexing, Hashing & Query Optimization

Gangeswara — Thu, 02 Oct 2025 08:56:10 +0000

In this assignment, we’ll explore how to improve query performance in Oracle SQL using indexes. We’ll create a Students table, insert sample data, and perform optimized queries using B-Tree and B+ Tree indexes.

1. Definition

Indexing: A database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space.

B-Tree Index: A balanced tree index used for equality and range searches.

B+ Tree Index: A type of B-Tree that stores all values at leaf nodes, optimized for range queries.

Hash Index: Uses a hash function for fast equality searches. (Note: Oracle only supports hash-like behavior through specific functions or clustering.)

Query Optimization: Using indexes and proper SQL structures to reduce query execution time.

2. Creating the Students Table

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

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

4. 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;

5. 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;

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

7. Steps Summary

Created the Students table with fields roll_no, name, dept, cgpa.

Inserted 20 sample records.

Created B-Tree index on roll_no for quick lookups.

Created B+ Tree index on cgpa for optimized range queries.

Created index on dept to speed up equality searches.

Executed queries to verify indexing and performance improvements.

8. Conclusion

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

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

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

MongoDB CRUD Operations

Gangeswara — Thu, 02 Oct 2025 07:11:07 +0000

Introduction

In this assignment, I explored MongoDB CRUD operations to gain hands-on experience with a NoSQL database. The main objective was to create, read, update, and delete student records in a simple college student schema.

MongoDB is a document-oriented database that stores data in JSON-like documents, making it flexible for dynamic data structures compared to traditional relational databases.

*Create Cluster & Database
*
Before performing CRUD operations, we need to create a cluster and define the database:

Create Cluster

In MongoDB Atlas, click “Create Deployment” → select Cluster.

Choose a cloud provider and region.

Enter Cluster Name: CollegeCluster

Click Create Deployment.

**
Create Database & Collection**

After the cluster is ready, go to Browse Collections → Add My Own Data.

Enter Database Name: college

Enter Collection Name: students

Click Create.

Student Schema

Each student document follows this structure:

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

student_id → Unique identifier for each student

name → Student’s name

age → Student’s age

department → Department name

year → Current academic year

cgpa → Current grade point average

1️⃣ Create (Insert)

We inserted 5 student records into the students collection using the insertMany command:

db.students.insertMany([
  { student_id: "S001", name: "Santhosh", age: 20, department: "CSBS", year: 2, cgpa: 9 },
  { student_id: "S002", name: "Anitha",  age: 19, department: "CSE",  year: 1, cgpa: 8.2 },
  { student_id: "S003", name: "Karthik",  age: 21, department: "ECE",  year: 3, cgpa: 7.8 },
  { student_id: "S004", name: "Meena",    age: 22, department: "IT",   year: 3, cgpa: 6.9 },
  { student_id: "S005", name: "Vikram",   age: 20, department: "CSBS", year: 2, cgpa: 8.9 }
]);

Explanation: insertMany allows inserting multiple documents at once into a MongoDB collection.

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

db.students.find().pretty();

find() → fetches all documents in the collection

pretty() → formats output for readability

2.2 Students with CGPA > 8

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

$gt → MongoDB operator for “greater than”

Only students with CGPA greater than 8 are retrieved

2.3 Students in CSBS or CSE department

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

$in → matches any value in the given array

Retrieves students belonging to either CSBS or CSE

3️⃣ Update

3.1 Update CGPA of a specific student

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

updateOne → updates a single document

$set → sets a new value for the specified field

3.2 Increase year for all 3rd-year students

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

updateMany → updates multiple documents matching the filter

$inc → increments numeric field by a specified value

4️⃣ Delete
4.1 Delete one student by ID

db.students.deleteOne({ student_id: "S004" });

deleteOne → deletes a single document matching the filter

4.2 Delete all students with CGPA < 7.5

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

deleteMany → deletes all documents matching the filter

$lt → “less than” operator

Final Collection Sample

[
  { "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
  { "student_id": "S002", "name": "Anitha",  "age": 19, "department": "CSE",  "year": 1, "cgpa": 8.5 },
  { "student_id": "S005", "name": "Vikram",   "age": 20, "department": "CSBS", "year": 2, "cgpa": 8.9 }
]

Conclusion

Through this assignment, I gained practical experience with MongoDB CRUD operations: inserting, querying, updating, deleting, and exporting data.
It highlighted the flexibility of document-oriented databases for managing dynamic data, compared to traditional relational databases.

Thanks to @santhoshnc Sir for his guidance and support throughout this assignment. 🙏

mongodb #crud #nosql #students #dbms #atlas #assignment #learningbydoing

DBMS – Transactions, Deadlocks & Log-Based Recovery

Gangeswara — Wed, 01 Oct 2025 16:02:56 +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.

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;

1️⃣ 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;

2️⃣ 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

3️⃣ 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

DBMS : ACID Properties with SQL Transactions

Gangeswara — Wed, 01 Oct 2025 10:06:50 +0000

ACID properties ensure reliability and correctness of database transactions.

Step 1: 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:

A transaction is all-or-nothing.

Either all operations in a transaction are executed successfully, or none are applied.

Ensures no partial updates occur in case of errors.

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:

A transaction must bring the database from one valid state to another.

Database rules (constraints, triggers) are preserved.

Prevents invalid data like negative balances or duplicate primary keys.

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:

Transactions operate independently of each other.

Changes made in one transaction are not visible to others until committed.

Ensures concurrent transactions do not interfere incorrectly.

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:

Once a transaction is committed, its changes are permanent, even in the event of a system crash or failure.

Guarantees the persistence of committed data.

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:

_Atomicity: _Transactions are “all or nothing”.

Consistency: Database enforces valid data.

Isolation: Concurrent transactions do not interfere.

Durability: Committed changes are permanent.

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

DBMS : Cursor & Trigger

Gangeswara — Wed, 01 Oct 2025 09:12:28 +0000

1️⃣ Cursor in DBMS

Definition:
A cursor is a database object used to retrieve and process rows from a result set one at a time. It allows row-by-row processing of query results, unlike normal SQL queries that work on the entire set at once.

Cursor Example – Display Employees with Salary > ₹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.

2️⃣ Trigger in DBMS

Definition:
A trigger is a stored procedure that automatically executes in response to certain events on a table or view.

trigger Example – AFTER INSERT on 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 allow row-by-row processing based on conditions.

Triggers automate actions like logging new entries without manual intervention.

Both are essential tools for advanced database management.

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

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

Database Normalization

Gangeswara — Wed, 01 Oct 2025 08:27:00 +0000

Database normalization is a crucial process in designing efficient and reliable databases. In this tutorial, I’ll show you how to normalize a sample table from 1NF to 3NF using Oracle Live SQL, with clear explanations and examples

1. The Base Table

We start with an unnormalized table that contains students, courses, instructors, and grades

2. Identifying Anomalies

Before normalization, it’s important to understand the potential anomalies in the table:

Insertion anomaly: You can’t add a new course without adding a student.

Update anomaly: Updating a course name requires changing multiple rows.

Deletion anomaly: Deleting a student might remove important course information if that student was the only one enrolled.

3. Converting to 1NF

1NF (First Normal Form) requires:

Each column must contain atomic (indivisible) values.

Each row must be unique.

Steps taken:

Split multivalued fields into separate rows.

Ensured each column contains a single value.

SQL Table in 1NF:

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

4. Conversion to 2NF

2NF Rule: Eliminate partial dependency (non-prime attributes must depend on the whole 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):

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

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

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 ensures:

Minimal data redundancy
Data integrity
Easier maintenance and updates

Using 1NF → 2NF → 3NF in Oracle SQL allows building scalable and efficient database designs.

Special thanks to @santhoshnc for guiding me through database normalization concepts.

#SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #DataModeling

College Student & Course Management System

Gangeswara — Thu, 21 Aug 2025 04:26:52 +0000

Introduction

In this post, I am sharing a simple Database Management System (DBMS) mini project using SQL (Oracle).
I designed a database schema for a college system that manages Students, Faculty, Courses, and Enrollments.
Then I implemented different queries step by step — from table creation to stored procedures.

🗂 Database Schema

Students table → stores student details

Faculty table → stores faculty details

Courses table → stores courses offered

Enrollments table → connects students and courses (many-to-many relationship)

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

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

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

-- Enrollments table (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', TO_DATE('2004-03-15','YYYY-MM-DD'), 'arun.kumar@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', TO_DATE('2003-07-22','YYYY-MM-DD'), 'priya.sharma@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Raj', 'MECH', TO_DATE('2004-11-10','YYYY-MM-DD'), 'rahul.raj@example.com');

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

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

SELECT
UPPER(Name) AS StudentName_Uppercase,
LENGTH(Email) AS Email_Length
FROM Students;

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

Through this mini-project, I practiced DDL, DML, Constraints, Joins, Group By, Views, and Stored Procedures in SQL.
This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems.

Thanks to @santhoshnc for guiding and supporting us.