DEV Community: Naveens K

AWS Neptune: Where Data Relationships Actually Make Sense

Naveens K — Thu, 18 Dec 2025 14:44:14 +0000

Overview

AWS Neptune is a fully managed graph database service designed for workloads where relationships matter more than rows. If you try to force social networks, fraud detection, or recommendation engines into a relational database, you’ll hit performance walls fast. Neptune exists to solve that exact problem using graph models, not SQL gymnastics.

It supports two graph paradigms:

Property Graph (Gremlin)
RDF / Semantic Graph (SPARQL)

Key Features

Fully managed (no OS, patching, backups, or replication babysitting)
High performance graph queries (milliseconds at scale)
Supports Gremlin, SPARQL, and openCypher
Built-in high availability with up to 15 read replicas
Continuous backups to S3 and point-in-time recovery
Encryption at rest (KMS) and in transit (TLS)
Integrates with IAM, VPC, CloudWatch, and CloudTrail

AWS Category / Cloud Domain

Database
Specialized Databases → Graph Database
Part of Data & Analytics

Where It Fits in Cloud / DevOps Lifecycle

Design phase: Choosing the right data model for relationship-heavy systems
Build phase: Backend services query Neptune instead of relational DBs
Deploy phase: Provisioned via CloudFormation, CDK, or Terraform
Operate phase: Monitoring withCloudWatch, scaling via read replicas
DevSecOps: IAM-based access, VPC isolation, encryption, audit logs

Programming Language / Access Methods

Query Languages:

Gremlin (Apache TinkerPop)
SPARQL
openCypher

SDK / Access:

AWS SDKs (Java, Python, JavaScript,Go, etc.)
HTTP(S) endpoints
Gremlin drivers and SPARQL clients

Pricing Model

Instance-based pricing (pay for DB instance size and hours used)
Storage billed per GB/month
I/O operations billed separately
Backup storage beyond free limit is charged
No free tier worth mentioning for real workloads Translation: powerful but expensive. Use it only when graph queries are a hard requirement.

NuGet: The Backbone of .NET Dependency Management

Naveens K — Thu, 18 Dec 2025 14:24:54 +0000

Overview of the Tool

NuGet is the official package manager for the .NET ecosystem. Its only job is to find, install, update, and manage dependencies for .NET projects. If you’re building anything in .NET.

Key Features

Centralized package repository (nuget.org) with hundreds of thousands of libraries
Dependency resolution (handles transitive dependencies automatically)
Versioning support (semantic versioning, locking, rollback)
CLI (nuget, dotnet add package) and IDE integration (Visual Studio)
Private package feeds (Azure Artifacts, GitHub Packages, Nexus, Artifactory)
Package signing and vulnerability metadata (basic but improving)

How It Fits into DevOps / DevSecOps

DevOps:

Used in CI/CD pipelines to restore dependencies (dotnet restore)
Ensures consistent builds across environments
Works with artifact repositories for internal packages
DevSecOps:
Supports signed packages to reduce supply-chain attacks
Integrates with vulnerability scanners (Dependabot, Snyk, GitHub Advanced Security)
Can enforce approved/internal packages via private feeds

Programming Language

Primarily C# / .NET
Supports all .NET languages: C#, F#, VB.NET
Built on the .NET platform
Parent Company: Microsoft

Open Source Platform:

MIT License
Enterprise features come indirectly via paid tools (Azure DevOps, GitHub Enterprise, Artifactory), not NuGet itself

CRUD OPERATIONS IN MONGODB

Naveens K — Fri, 03 Oct 2025 09:21:06 +0000

INTRODUCTION

MongoDB is one of the most popular NoSQL databases, widely used for its flexibility and scalability. Unlike relational databases, MongoDB stores data in collections as JSON-like documents.

In this blog, we’ll perform CRUD (Create, Read, Update, Delete) operations on a simple college student schema to understand MongoDB basics hands-on.

Here we are using MongoDB Shell for scripting in MongoDB Compass
run all the commands in MongoDB Shell. this will work great.

Student Schema
We’ll use a collection called students, and each document follows this basic structure:

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

Create (Insert)

Inserting 5 student records:

db.students.insertMany([
  {
    "student_id": "S001",
    "name": "Santhosh",
    "age": 20,
    "department": "CSBS",
    "year": 2,
    "cgpa": 9
  },
  {
    "student_id": "S002",
    "name": "Kavya",
    "age": 21,
    "department": "CSE",
    "year": 3,
    "cgpa": 8.5
  },
  {
    "student_id": "S003",
    "name": "Arun",
    "age": 19,
    "department": "ECE",
    "year": 1,
    "cgpa": 7.8
  },
  {
    "student_id": "S004",
    "name": "Priya",
    "age": 22,
    "department": "CSE",
    "year": 4,
    "cgpa": 9.2
  },
  {
    "student_id": "S005",
    "name": "Vignesh",
    "age": 20,
    "department": "MECH",
    "year": 2,
    "cgpa": 6.9
  }
])

This will create 5 documents in the students collection.

Read (Query)

Display all student records:

db.students.find()

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 (say S002):

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

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

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

Delete

Delete one student record by student_id:

db.students.deleteOne({ student_id: "S005" })

Delete all students having CGPA < 7.5:

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

Deliverables

MongoDB queries (provided above)
Screenshots of execution results from MongoDB Atlas / Compass (to be added when you run them).
Export final students collection as JSON/CSV

Conclusion

We’ve successfully performed CRUD operations in MongoDB using a simple student schema. This hands-on exercise gives a clear picture of how MongoDB works with JSON-like documents.

Indexing, Hashing & Query Optimization in SQL

Naveens K — Fri, 03 Oct 2025 07:42:26 +0000

Intro

When working with large databases, retrieving data efficiently becomes a big challenge. SQL provides indexing and hashing techniques to speed up query execution. In this blog, we’ll explore B-Tree, B+ Tree, and Hash indexes with hands-on SQL examples using a Students table.

Create the Students Table

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

Insert Sample Records

INSERT INTO Students VALUES
(101, 'Aarav', 'CSBS', 8.5),
(102, 'Meera', 'ECE', 7.2),
(103, 'Rohan', 'MECH', 6.9),
(104, 'Sita', 'CIVIL', 8.1),
(105, 'Vikram', 'CSE', 9.0),
(106, 'Priya', 'IT', 8.3),
(107, 'Arjun', 'CSBS', 7.5),
(108, 'Neha', 'ECE', 8.7),
(109, 'Kiran', 'CSE', 6.8),
(110, 'Rahul', 'CSBS', 9.2),
(111, 'Sneha', 'MECH', 7.9),
(112, 'Dev', 'CIVIL', 8.4),
(113, 'Pooja', 'CSE', 7.3),
(114, 'Varun', 'IT', 8.6),
(115, 'Isha', 'ECE', 9.1),
(116, 'Nikhil', 'MECH', 7.6),
(117, 'Ritu', 'CIVIL', 6.5),
(118, 'Sameer', 'CSE', 8.8),
(119, 'Tina', 'CSBS', 7.8),
(120, 'Yash', 'IT', 9.3);

Now we have 20 student records.

Create a B-Tree Index on `roll_no`

B-Tree indexes are default in most RDBMS. They make searching on primary/unique columns faster.

CREATE INDEX idx_rollno_btree ON Students(roll_no);

The DBMS uses the B-Tree index to find roll_no = 110 quickly without scanning the whole table.

Create a B+ Tree Index on `cgpa`

B+ Trees are widely used for range queries.

CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

Instead of checking each row, the B+ Tree index helps to traverse efficiently for CGPA values greater than 8.5 .

Create a Hash Index on `dept`

Hash indexes are best for equality lookups (e.g., = condition).

CREATE INDEX idx_dept_hash ON Students(dept) USING HASH;

Query Optimization

Without indexes → Full Table Scan (slow for large datasets).
With indexes → Optimized Execution Plan (quick lookup).
Use EXPLAIN to check whether your query uses the index.

Final Thoughts

B-Tree Index → Best for unique lookups & ordering.
B+ Tree Index → Best for range queries.
Hash Index → Best for equality lookups.

By using indexes wisely, we can reduce query execution time drastically in real-world applications.

Transactions, Deadlocks & Log Based Recovery in SQL

Naveens K — Fri, 03 Oct 2025 05:44:33 +0000

Intro

In this blog, let’s explore three very important database concepts: Transactions, Deadlocks, and Log-Based Recovery. We’ll be using a simple Accounts table and running SQL queries to demonstrate these in action.

Schema Setup

First, let’s create a table Accounts and insert some sample data.

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

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

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

Transaction – Atomicity & Rollback

Transactions are all-or-nothing operations. Either all changes are applied (commit) or none (rollback).

Let’s try transferring 500 from Alice to Bob but roll it back before committing.

-- Start transaction
START TRANSACTION;

-- Deduct from Alice
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';

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

-- Check balances before rollback
SELECT * FROM Accounts;

-- Rollback transaction
ROLLBACK;

-- Check balances after rollback
SELECT * FROM Accounts;

Result: Alice still has 1000, Bob has 1500.
No partial update happened – this is Atomicity.

Deadlock Simulation

Deadlocks happen when two transactions are waiting for each other’s resources.

We’ll simulate this using two sessions:

Session 1

START TRANSACTION;
-- Lock Bob
UPDATE Accounts SET balance = balance - 50 WHERE name = 'Bob';

-- Now try updating Alice (but Alice is locked by Session 1)
UPDATE Accounts SET balance = balance + 50 WHERE name = 'Alice';

At this point, both sessions are waiting for each other → Deadlock occurs.
The database will detect this automatically and kill one transaction, rolling it back.

Log-Based Recovery

Databases maintain logs (like binary log in MySQL or WAL in PostgreSQL) to ensure durability and recovery.

Let’s test this:

-- Start transaction
START TRANSACTION;

-- Update a record
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;

Even though the update was rolled back, the log will record both the update and the undo operation.
This ensures that if the system crashes, recovery can undo uncommitted transactions and redo committed ones.

Key Takeaways

Atomicity: All or nothing – rollback prevents partial updates.
Deadlock: Two transactions waiting for each other’s lock → system resolves it.
Log-Based Recovery: Every change is logged so the DB can recover from crashes.

Understanding ACID Properties in SQL Transactions

Naveens K — Fri, 03 Oct 2025 04:26:08 +0000

Intro

When working with databases, data integrity is everything. Imagine transferring money from one account to another — you wouldn’t want money to disappear or double by mistake, right?

That’s where ACID properties come in. They ensure our transactions are reliable, safe, and consistent, even when things go wrong.

In this blog, let’s break down ACID with SQL examples using a simple Accounts table.

What is ACID?

ACID stands for:

Atomicity → All or nothing (no partial transactions).
Consistency → Data must remain valid before and after a transaction.
Isolation → Transactions run independently of each other.
Durability → Once committed, changes are permanent (even after crash).

Setting up a Table

We’ll create an Accounts table with a balance check to avoid negative values.

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

INSERT INTO Accounts VALUES
(101, 'Santhosh', 5000),
(102, 'Ganges', 3000),
(103, 'Rakshanth', 7000),
(104, 'Jaswant', 6000);

Atomicity

Atomicity ensures that either the entire transaction happens or nothing happens.

Example: Money transfer (Rollback midway)

START TRANSACTION;

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

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

-- Oops! Something went wrong
ROLLBACK;

-- Check balances (should remain unchanged)
SELECT * FROM Accounts;

Since we rolled back, no changes were applied. That’s Atomicity in action!

Consistency

Consistency ensures that a transaction brings the database from one valid state to another.

Example: Insert a record with negative balance

INSERT INTO Accounts VALUES (104, 'Naveens', -500);

This fails because of our CHECK (balance >= 0) constraint.
The database remains valid → Consistency maintained.

Isolation

Isolation ensures that transactions don’t interfere with each other.

Imagine two sessions running at the same time:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;
-- Don’t commit yet

Session 2:

SELECT * FROM Accounts WHERE acc_no = 103;

On READ COMMITTED → Session 2 sees old balance.
On READ UNCOMMITTED → Session 2 may see uncommitted changes (dirty read).
On SERIALIZABLE → Session 2 waits until Session 1 commits.

Different isolation levels decide how much transactions can "see" each other’s work.

Durability

Durability ensures that once a transaction is committed, it survives crashes.

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

Now restart your database and run:

SELECT * FROM Accounts WHERE acc_no = 102;

You’ll still see the updated balance.
That’s Durability — committed data is permanent.

Final Thoughts

The ACID properties are the backbone of reliable databases.

Atomicity → No partial updates
Consistency → Always valid data
Isolation → Independent transactions
Durability → Permanent changes

So next time you run a SQL transaction, remember: ACID is silently ensuring your data stays safe, even if things go wrong.

Understanding Cursors & Triggers in DBMS

Naveens K — Fri, 03 Oct 2025 03:23:22 +0000

Introduction

Databases are the backbone of almost every software application today. From banking systems to e-commerce sites, managing and manipulating data efficiently is crucial. While SQL gives us powerful tools for querying and updating data, there are advanced features in DBMS like Cursors and Triggers that allow us to handle complex situations with ease.

In this blog, we will explore:

What Cursors are, why we need them, and how to use them.
What Triggers are, how they help automate actions, and practical examples.

Cursors in DBMS

What is a Cursor?

A Cursor in DBMS is a database object that allows us to fetch and process query results row by row. Normally, SQL works with sets of data all at once, but sometimes we need to handle each row individually. That’s where cursors come in.

Think of it like a pointer that moves through the rows of a result set one at a time.

Why Use Cursors?

When we need row-by-row processing.
To perform custom logic on each record.
To store results temporarily and use them later.

Example: Cursor for High Salary Employees
Suppose we have an Employee table:

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES
(1, 'Bob Smith', 60000),
(2, 'Charlie Brown', 55000),
(3, 'Edward Wilson', 75000),
(4, 'Fiona Davis', 52000),
(5, 'Hannah Lee', 60000);
(6,'naveens',67000);

Now, let’s create a stored procedure with a cursor that selects employees earning more than 50,000.

DELIMITER $$

CREATE PROCEDURE get_high_salary_employees()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_emp_name VARCHAR(100);

    DECLARE emp_cursor CURSOR FOR
        SELECT emp_name FROM Employee WHERE salary > 50000;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    CREATE TABLE IF NOT EXISTS high_salary_emp(emp_name VARCHAR(100));
    TRUNCATE TABLE high_salary_emp;

    OPEN emp_cursor;

    read_loop: LOOP
        FETCH emp_cursor INTO v_emp_name;
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO high_salary_emp VALUES (v_emp_name);
    END LOOP;

    CLOSE emp_cursor;

    SELECT * FROM high_salary_emp;
END$$

DELIMITER ;

CALL get_high_salary_employees();

Output:

This shows how a cursor fetches each row one by one and stores it into another table.

Triggers in DBMS

A Trigger is a special type of stored program that automatically executes when a specific event occurs in a table. Events can be:

INSERT
UPDATE
DELETE

In simple words, triggers are rules that fire automatically when data changes.

Why Use Triggers?

To maintain data integrity.
To automatically log changes.
To enforce business rules.

Example 1: AFTER INSERT Trigger (Audit Log)

We want to keep track of all new employees added. So, we create an audit table:

CREATE TABLE Employee_Audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_id INT,
    emp_name VARCHAR(100),
    salary DECIMAL(10,2),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Now, the trigger:

DELIMITER $$

CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
    INSERT INTO Employee_Audit (emp_id, emp_name, salary)
    VALUES (NEW.emp_id, NEW.emp_name, NEW.salary);
END$$

DELIMITER ;

Test it:

INSERT INTO Employee VALUES (34, 'Meera', 72000);

SELECT * FROM Employee_Audit;

Output shows Meera automatically logged in Employee_Audit.

Conclusion

Cursors let us handle query results row by row, useful for special operations.
Triggers allow us to automatically enforce rules or log changes when data is inserted, updated, or deleted.
Together, they give DBMS more power, automation, and data integrity.

Demystifying Normalization in RDBMS: From 1NF to 3NF

Naveens K — Thu, 02 Oct 2025 11:15:36 +0000

Hi everyone! I’m really excited to share my learnings in RDBMS through this blog. As someone who is learning and exploring RDBMS concepts, I decided to write down my understanding in the simplest way possible so that anyone—especially beginners like me—can easily follow along. Today, let’s begin with one of the most important topics: Normalization.

Normalization-->

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

In simple words: It ensures your data is stored in the right place, without unnecessary duplication.

First Normalization Form(1NF)

For example: instead of storing a multiple phone number for a single person in the same column of the table breakdown the single table into two and through connecting the any of the attributes together to ensure the 1NF and to get the desired output use Joins
let we dive into the example:

drop table if exists students_non1NF;
create table students_non1NF(
    student_id INT,
    student_name VARCHAR(20),
    phone_no VARCHAR(100)
)

Insert Values:

INSERT INTO students_non1NF(student_id,student_name,phone_no) VALUES 
(1, 'jaswant','1234567890,0986235162'),
(2,'santhosh','7185689465,8643851084'),
(3,'rakshanth','3486512964,6853264273'),
(4,'ganges','6153956426,6426492386')

This table is not in First normalization from why because in column phone _no has two phone numbers in the single field

To avoid this problem we split the single table into two

DROP TABLE if EXISTS students;
CREATE TABLE students(
    student_id INT,
    student_name VARCHAR(100),
    PRIMARY KEY(student_id)
);
 CREATE TABLE student_phone(
    student_id INT,
    phone_no VARCHAR(100),
    PRIMARY KEY (student_id, phone_no),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

And the insert values are:

INSERT INTO students (student_id,student_name) values (1, 'jaswant'),(2,'santhosh'),(3,'rakshanth'),
(4,'ganges');
INSERT INTO student_phone(student_id,phone_no) values (1,'1234567890'),
(1,'0986235162'),(2,'7185689465'),(2,'8643851084'),(3,'3486512964'),(3,'6853264273'),(4,'6153956426'),(4,'6426492386');

the output is:

Boom..!!!!
The problem solved now we able to give the multiple numbers for the same people and it will not violate the 1NF
to see the output then we use Join Query:

SELECT s.student_id,
       s.student_name,
       p.phone_no
FROM students s
LEFT JOIN student_phone p
ON s.student_id = p.student_id;

And the output is:

Second Normalization Form(2NF)

The Second Normal Form (2NF) builds on the foundation of 1NF and takes the process of organizing data one step further. A table is said to be in 2NF if it is already in 1NF and all non-key attributes are fully dependent on the entire primary key. This mainly applies when a table has a composite primary key (a primary key made up of two or more columns). If some non-key attributes depend only on part of that composite key instead of the whole key, it creates what we call a partial dependency, and this violates 2NF. For example, if we have a table where the primary key is a combination of StudentID and CourseID, and we also store the InstructorName, then InstructorName depends only on CourseID and not on the full key. To fix this, we separate the data into two tables—one for the student-course mapping and another for the course-instructor details. By doing this, we eliminate partial dependency, making the database more structured

Now let we see the table without 2NF:

 CREATE TABLE Enrollment_2nonNF(
     student_id INT,
     course_id INT,
     course_name VARCHAR(200),
     instructor VARCHAR(200),
     PRIMARY KEY (student_id,course_id)
 )

And the insert values are:

INSERT INTO Enrollment_2nonNF(student_id,course_id,course_name,instructor) VALUES 
(1,101,'Intro to SQL','Jorn'),
(2,101,'Intro to SQL','Jorn'),
(1,102,'Intro to JAVA','smith');

The output is :

Here the instructor column is dependent on course_id
to avoid this problem let we breakdown the table into two which one is for holding student_id, and course_id and the another one for holding course_id, course_name and instructor

drop table if exists Courses;
CREATE TABLE Courses(
    course_id INT,
    course_name VARCHAR(200),
    instructor VARCHAR(200),
    PRIMARY KEY (course_id)


);
drop table if exists Enrollment;
CREATE TABLE Enrollment(
    student_id INT ,
    course_id INT ,
    PRIMARY KEY(student_id,course_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

INSERT INTO Courses (course_id, course_name, instructor) VALUES
(101,"Intro to SQL",'Jorn'),
(102,'Intro to JAVA','smith');
INSERT INTO Enrollment (student_id,course_id) VALUES 
(1,101),
(2,101),
(1,102);

The join query is:

SELECT e.student_id , c.Course_id, c.course_name, c.instructor 
from Enrollment e LEFT JOIN Courses c ON e.course_id = c.course_id;

the output is:

Third Normalization Form(3NF)

The Third Normal Form (3NF) is the next step after 2NF, and its goal is to eliminate what is known as transitive dependency. A table is said to be in 3NF if it is already in 2NF and every non-key attribute depends only on the primary key, not on another non-key attribute. In simpler terms, no column should give information about another column unless it is through the primary key. For example, consider a table with course_id, course_name, instructor, and instructor_office. Here, instructor_office depends on instructor, which means it is indirectly dependent on course_id. This violates 3NF. To fix this, we split the table into two: one for course details (course_id, course_name, instructor) and another for Instructor details (instructor, instructor_office). By doing this, every non-key attribute now depends only on the primary key, and we remove unnecessary dependencies. Achieving 3NF ensures that the data is well-organized, reduces redundancy even further, and makes the database easier to maintain and scale.

The table without 3NF look like:

CREATE TABLE courses_Non3NF(
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100),
    instructor_office VARCHAR(100)
);


INSERT INTO courses_Non3NF(course_id,course_name,instructor,instructor_office) VALUES 
(101, 'Intro to SQL','DR.Jorn' , 'Room 101'),
(102, 'Intro to JAVA','DR. Smith' , 'Room 102');

As we already discussed now we split the table into two and the code like this:

CREATE TABLE Insturctors(
    instructor VARCHAR(100) PRIMARY KEY,
    instructor_office VARCHAR(100)
);
CREATE TABLE courses1(
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100),
    FOREIGN KEY (instructor) REFERENCES Insturctors(instructor)
);

INSERT INTO Insturctors (instructor,instructor_office) VALUES 
('DR.jorn' , 'Room 101'),
('DR.smith' , 'Room 102');

INSERT INTO courses1(course_id,course_name,instructor) VALUES 
(101, 'Intro to SQL','DR.jorn' ),
(102, 'Intro to JAVA','DR.smith');

And the join query is:

select c.course_id, c.course_name , i.instructor, i.instructor_office 
from courses1 c LEFT JOIN Insturctors i ON c.instructor = i.instructor;

Super..!!, these are the concepts and practical learnings in 1NF, 2NF and 3NF and beyond that we have BCNF, 4NF and 5NF these three are not very much usable even if you eager to learn these three normalization forms please search in internet you will easily understood the things

College Student and Course Management System using SQL (Oracle)

Naveens K — Fri, 22 Aug 2025 17:35:22 +0000

Introduction

This is a simple, basic SQL project that demonstrates how to design and manage a small College Management System.

The idea is to create a database that stores information about:

Students (their details and department)
Courses (subjects offered)
Faculty (professors teaching in departments)
Enrollments (which student takes which course, along with grades)

Although this is a small-scale project, it reflects how real-world college databases are structured.

Why is this project useful?

Helps beginners understand how relational databases work.
Shows how different entities (students, courses, faculty) are linked through relationships.
Demonstrates SQL features like constraints, joins, views, and stored procedures.
Can be easily extended into a bigger college portal system (with attendance, GPA calculation, reports, etc.).

In short, this project is a foundation exercise that not only strengthens SQL skills but also gives a taste of how databases are applied in real educational institutions.

Database Design – Tables and Purpose

In this project, we created four main tables: Students, Courses, Enrollments, and Faculty.
Each table represents an important entity in a college system.
1. Students Table

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

Purpose:

Stores basic information about students like name, department, date of birth, and email.
StudentID acts as a primary key so that every student is uniquely identified.
The Email field is marked as UNIQUE, ensuring no two students have the same email

Learnings:

How to define primary keys for unique identification.
How to enforce data integrity using NOT NULL and UNIQUE constraints.
How to handle different data types (VARCHAR2 for text, DATE for birthdate).

2. Course Table

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

Purpose:

Stores all courses offered by the institution.
Each course has an ID, name, and credits.
CourseID uniquely identifies each course.

Learnings:

Designing a table that holds course catalog information.
Adding constraints like NOT NULL for required fields.
Understanding numeric columns (credits between 1–5).

3. Enrollments Table

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

Purpose:

Acts as a bridge table between Students and Courses.
Stores which student is enrolled in which course, along with their grade.
Uses foreign keys to connect to Students and Courses.

Learnings:

How to create relationships between tables.
Use of foreign keys for maintaining referential integrity.
Handling many-to-many relationships (one student can enroll in many courses, one course can have many students).

4. Faculty Table

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

Purpose:

Stores details of faculty members (professors).
Each faculty is linked to a department and has a unique email.

Learnings:

Designing another entity in the college system.
Importance of uniqueness (faculty emails).
Setting up department-wise grouping of faculty for future queries.

By creating these tables, we learn:

How to design normalized tables for real-world entities.

How to apply constraints (Primary Key, Unique, Not Null, Foreign Key).

How to connect multiple tables to model real-world relationships.

Inserting Data into Tables

1. Insert Data into Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

2. Insert Data into Courses

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(104, 'Database Systems', 5);

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

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Thermodynamics', 3);

3. Insert Data into Enrollments

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

4. Insert Data into Faculty

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

Purpose:

To design a basic but realistic database that represents a college management system.
To define the main entities — Students, Courses, Enrollments, and Faculty — and establish relationships among them.
To insert sample records so the database is ready for practicing queries and analysis.

Learnings:

Database Design Principles – understanding how to model real-world entities (like students and courses) as database tables.
Constraints for Data Integrity – applying Primary Keys, Unique Keys, Not Null, and Foreign Keys to avoid duplicate or invalid data.
Relationships in Databases – creating one-to-many and many-to-many relationships (via Enrollments).
Data Insertion – learning how to insert records correctly, including handling dates, text fields, and numeric constraints.
Normalization Basics – separating data into different tables instead of storing everything in one, which avoids redundancy and makes queries more efficient.

In short, this phase helps us move from theory to practice:
we don’t just define tables, but also fill them with meaningful data that mirrors a real college environment, preparing the ground for more advanced SQL queries and procedures.

Queries and Learnings

1. Display Students with Uppercase Names and Email Length

SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
FROM Students;

Purpose:

Converts all student names to uppercase.
Shows the length of each student’s email address.

Learning:

Use of string functions (UPPER, LENGTH).
How SQL can transform and analyze text data.

2. Find Average Credits and Total Enrollments

SELECT 
   (SELECT AVG(CREDITS) FROM Courses) AS AvgCredits,
   (SELECT COUNT(DISTINCT EnrollID) FROM Enrollments) AS TotalEnrolledStudents
FROM dual;

Purpose:

Finds the average credits across all courses.
Counts how many students are enrolled overall.

Learning:

Use of aggregate functions (AVG, COUNT).
Writing subqueries inside a SELECT.
Using DUAL (special table in Oracle for single-row queries).

3. List Students with Their Courses and Grades

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;

Purpose:

Displays each student along with the course they enrolled in and their grade.

Learning:

How to use INNER JOIN to combine multiple tables.
Understanding relationships (Students ↔ Enrollments ↔ Courses).

4. Department-Wise Student Count (Only if >2 Students)

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

Purpose:

Groups students by department.
Shows only departments having more than 2 students.

Learning:

Difference between WHERE and HAVING.
How to use GROUP BY with aggregate functions.

With these queries, we are able to:

Analyze text and numeric data.

Use joins to link tables.

Work with aggregate functions for reporting.

Apply filtering at both row-level (WHERE) and group-level (HAVING).

Views & Stored Procedures

1. Creating a View: StudentCoursesView

CREATE OR REPLACE 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 c.CourseID = e.CourseID;

Purpose:

A view is like a virtual table that stores the result of a query.
Instead of writing the long JOIN query every time, we can just select from the view.

Learning:

Views improve readability and reusability.
Good for reporting, since they provide a simplified version of complex joins.

Example Usage:

SELECT * FROM StudentCoursesView;

This directly shows all students with their enrolled courses and grades.

2. Creating a Stored Procedure: UpdateGrade

CREATE OR REPLACE PROCEDURE UpdateGrade (
    p_StudentID IN NUMBER,
    p_CourseID IN NUMBER,
    p_NewGrade IN VARCHAR2
) AS
BEGIN
    UPDATE Enrollments
    SET Grade = p_NewGrade
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;
    COMMIT;
END;
/

Purpose:

A stored procedure is a block of SQL code that can be executed with parameters.
Here, we use it to update a student’s grade in a course.

Learning:

How to create reusable PL/SQL procedures.
Passing parameters (IN) to make updates dynamic.
Importance of COMMIT to save changes permanently.

Example Usage:

EXEC UpdateGrade(1, 101, 'B+');

This updates Rahul Sharma’s grade in Database Systems to B+.

With Views & Procedures, we learn how to:

Simplify repeated queries using views.

Automate updates and tasks using stored procedures.

Combine SQL with PL/SQL programming concepts for more flexibility.

Complete Code:

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

SELECT * FROM STUDENTS;

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

SELECT * FROM COURSES;

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

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



-- Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

-- Courses
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(101, 'Database Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Operating Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(103, 'Thermodynamics', 5);

-- Enrollments
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

-- Faculty
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

SELECT * FROM FACULTY ;

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 , LENGTH(Email) as EmailLength  FROM Students;

SELECT (SELECT AVG(CREDITS) FROM COURSES) AS AvgCredits ,(SELECT COUNT(DISTINCT ENROLLID) FROM ENROLLMENTS) AS TotalEnrolledStudents FROM dual; 

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
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;

-- Show each department and the count of students. Display only those departments with more than 2 students.

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

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
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;

-- Show each department and the count of students. Display only those departments with more than 2 students.

SELECT Dept, COUNT(*) AS StudentCount
FROM Students


CREATE OR REPLACE 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 c.CourseID = e.CourseID;


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 project was a simple, beginner-friendly implementation of a College Management System using SQL.

We designed and implemented four key entities:

Students – storing student details.
Courses – listing subjects with credits.
Enrollments – linking students to their courses and grades.
Faculty – storing professor details.

We also:

Inserted sample data to simulate a real college environment.
Wrote queries to analyze and extract useful information.
Created a view for simplified reporting.
Built a stored procedure to dynamically update grades.

Learnings Recap

How to design normalized tables with primary & foreign key's.
How to use constraints (unique, not null,check).
Writing queries with joins, aggregates, and group filters.
Using views for simplified queries.
Creating procedures to automate tasks.

Future Improvements

Although this is a basic project, it can be extended with:

Attendance tracking (students’ daily presence).
Faculty-course mapping (which professor teaches which course).
GPA/CGPA calculation functions.
Triggers to automatically update data (like grade-based performance status).
More analytics reports (top students, most popular courses, etc.).

With this, we’ve shown how SQL can be used to build a realistic mini-database system that models everyday college operations.
Even though it’s simple, the same principles can be scaled up to design larger student management systems, portals, or ERP solutions.

College Student and Course Management System using SQL (Oracle)

Naveens K — Fri, 22 Aug 2025 17:35:22 +0000

Introduction

This is a simple, basic SQL project that demonstrates how to design and manage a small College Management System.

The idea is to create a database that stores information about:

Students (their details and department)
Courses (subjects offered)
Faculty (professors teaching in departments)
Enrollments (which student takes which course, along with grades)

Although this is a small-scale project, it reflects how real-world college databases are structured.

Why is this project useful?

Helps beginners understand how relational databases work.
Shows how different entities (students, courses, faculty) are linked through relationships.
Demonstrates SQL features like constraints, joins, views, and stored procedures.
Can be easily extended into a bigger college portal system (with attendance, GPA calculation, reports, etc.).

In short, this project is a foundation exercise that not only strengthens SQL skills but also gives a taste of how databases are applied in real educational institutions.

Database Design – Tables and Purpose

In this project, we created four main tables: Students, Courses, Enrollments, and Faculty.
Each table represents an important entity in a college system.
1. Students Table

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

Purpose:

Stores basic information about students like name, department, date of birth, and email.
StudentID acts as a primary key so that every student is uniquely identified.
The Email field is marked as UNIQUE, ensuring no two students have the same email

Learnings:

How to define primary keys for unique identification.
How to enforce data integrity using NOT NULL and UNIQUE constraints.
How to handle different data types (VARCHAR2 for text, DATE for birthdate).

2. Course Table

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

Purpose:

Stores all courses offered by the institution.
Each course has an ID, name, and credits.
CourseID uniquely identifies each course.

Learnings:

Designing a table that holds course catalog information.
Adding constraints like NOT NULL for required fields.
Understanding numeric columns (credits between 1–5).

3. Enrollments Table

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

Purpose:

Acts as a bridge table between Students and Courses.
Stores which student is enrolled in which course, along with their grade.
Uses foreign keys to connect to Students and Courses.

Learnings:

How to create relationships between tables.
Use of foreign keys for maintaining referential integrity.
Handling many-to-many relationships (one student can enroll in many courses, one course can have many students).

4. Faculty Table

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

Purpose:

Stores details of faculty members (professors).
Each faculty is linked to a department and has a unique email.

Learnings:

Designing another entity in the college system.
Importance of uniqueness (faculty emails).
Setting up department-wise grouping of faculty for future queries.

By creating these tables, we learn:

How to design normalized tables for real-world entities.

How to apply constraints (Primary Key, Unique, Not Null, Foreign Key).

How to connect multiple tables to model real-world relationships.

Inserting Data into Tables

1. Insert Data into Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

2. Insert Data into Courses

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(104, 'Database Systems', 5);

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

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Thermodynamics', 3);

3. Insert Data into Enrollments

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

4. Insert Data into Faculty

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

Purpose:

To design a basic but realistic database that represents a college management system.
To define the main entities — Students, Courses, Enrollments, and Faculty — and establish relationships among them.
To insert sample records so the database is ready for practicing queries and analysis.

Learnings:

Database Design Principles – understanding how to model real-world entities (like students and courses) as database tables.
Constraints for Data Integrity – applying Primary Keys, Unique Keys, Not Null, and Foreign Keys to avoid duplicate or invalid data.
Relationships in Databases – creating one-to-many and many-to-many relationships (via Enrollments).
Data Insertion – learning how to insert records correctly, including handling dates, text fields, and numeric constraints.
Normalization Basics – separating data into different tables instead of storing everything in one, which avoids redundancy and makes queries more efficient.

In short, this phase helps us move from theory to practice:
we don’t just define tables, but also fill them with meaningful data that mirrors a real college environment, preparing the ground for more advanced SQL queries and procedures.

Queries and Learnings

1. Display Students with Uppercase Names and Email Length

SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
FROM Students;

Purpose:

Converts all student names to uppercase.
Shows the length of each student’s email address.

Learning:

Use of string functions (UPPER, LENGTH).
How SQL can transform and analyze text data.

2. Find Average Credits and Total Enrollments

SELECT 
   (SELECT AVG(CREDITS) FROM Courses) AS AvgCredits,
   (SELECT COUNT(DISTINCT EnrollID) FROM Enrollments) AS TotalEnrolledStudents
FROM dual;

Purpose:

Finds the average credits across all courses.
Counts how many students are enrolled overall.

Learning:

Use of aggregate functions (AVG, COUNT).
Writing subqueries inside a SELECT.
Using DUAL (special table in Oracle for single-row queries).

3. List Students with Their Courses and Grades

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;

Purpose:

Displays each student along with the course they enrolled in and their grade.

Learning:

How to use INNER JOIN to combine multiple tables.
Understanding relationships (Students ↔ Enrollments ↔ Courses).

4. Department-Wise Student Count (Only if >2 Students)

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

Purpose:

Groups students by department.
Shows only departments having more than 2 students.

Learning:

Difference between WHERE and HAVING.
How to use GROUP BY with aggregate functions.

With these queries, we are able to:

Analyze text and numeric data.

Use joins to link tables.

Work with aggregate functions for reporting.

Apply filtering at both row-level (WHERE) and group-level (HAVING).

Views & Stored Procedures

1. Creating a View: StudentCoursesView

CREATE OR REPLACE 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 c.CourseID = e.CourseID;

Purpose:

A view is like a virtual table that stores the result of a query.
Instead of writing the long JOIN query every time, we can just select from the view.

Learning:

Views improve readability and reusability.
Good for reporting, since they provide a simplified version of complex joins.

Example Usage:

SELECT * FROM StudentCoursesView;

This directly shows all students with their enrolled courses and grades.

2. Creating a Stored Procedure: UpdateGrade

CREATE OR REPLACE PROCEDURE UpdateGrade (
    p_StudentID IN NUMBER,
    p_CourseID IN NUMBER,
    p_NewGrade IN VARCHAR2
) AS
BEGIN
    UPDATE Enrollments
    SET Grade = p_NewGrade
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;
    COMMIT;
END;
/

Purpose:

A stored procedure is a block of SQL code that can be executed with parameters.
Here, we use it to update a student’s grade in a course.

Learning:

How to create reusable PL/SQL procedures.
Passing parameters (IN) to make updates dynamic.
Importance of COMMIT to save changes permanently.

Example Usage:

EXEC UpdateGrade(1, 101, 'B+');

This updates Rahul Sharma’s grade in Database Systems to B+.

With Views & Procedures, we learn how to:

Simplify repeated queries using views.

Automate updates and tasks using stored procedures.

Combine SQL with PL/SQL programming concepts for more flexibility.

Complete Code:

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

SELECT * FROM STUDENTS;

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

SELECT * FROM COURSES;

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

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



-- Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

-- Courses
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(101, 'Database Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Operating Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(103, 'Thermodynamics', 5);

-- Enrollments
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

-- Faculty
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

SELECT * FROM FACULTY ;

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 , LENGTH(Email) as EmailLength  FROM Students;

SELECT (SELECT AVG(CREDITS) FROM COURSES) AS AvgCredits ,(SELECT COUNT(DISTINCT ENROLLID) FROM ENROLLMENTS) AS TotalEnrolledStudents FROM dual; 

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
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;

-- Show each department and the count of students. Display only those departments with more than 2 students.

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

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
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;

-- Show each department and the count of students. Display only those departments with more than 2 students.

SELECT Dept, COUNT(*) AS StudentCount
FROM Students


CREATE OR REPLACE 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 c.CourseID = e.CourseID;


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 project was a simple, beginner-friendly implementation of a College Management System using SQL.

We designed and implemented four key entities:

Students – storing student details.
Courses – listing subjects with credits.
Enrollments – linking students to their courses and grades.
Faculty – storing professor details.

We also:

Inserted sample data to simulate a real college environment.
Wrote queries to analyze and extract useful information.
Created a view for simplified reporting.
Built a stored procedure to dynamically update grades.

Learnings Recap

How to design normalized tables with primary & foreign key's.
How to use constraints (unique, not null,check).
Writing queries with joins, aggregates, and group filters.
Using views for** simplified queries.**
Creating procedures to automate tasks.

Future Improvements

Although this is a basic project, it can be extended with:

Attendance tracking (students’ daily presence).
Faculty-course mapping (which professor teaches which course).
GPA/CGPA calculation functions.
Triggers to automatically update data (like grade-based performance status).
More analytics reports (top students, most popular courses, etc.).

With this, we’ve shown how SQL can be used to build a realistic mini-database system that models everyday college operations.
Even though it’s simple, the same principles can be scaled up to design larger student management systems, portals, or ERP solutions.

DEV Community: Naveens K

AWS Neptune: Where Data Relationships Actually Make Sense

Overview

Key Features

AWS Category / Cloud Domain

Where It Fits in Cloud / DevOps Lifecycle

Programming Language / Access Methods

Pricing Model

NuGet: The Backbone of .NET Dependency Management

Overview of the Tool

Key Features

How It Fits into DevOps / DevSecOps

Programming Language

Open Source Platform:

CRUD OPERATIONS IN MONGODB

INTRODUCTION

Create (Insert)

Read (Query)

Update

Delete

Deliverables

Conclusion

Indexing, Hashing & Query Optimization in SQL

Intro

Create a B-Tree Index on roll_no

Create a B+ Tree Index on cgpa

Create a Hash Index on dept

Transactions, Deadlocks & Log Based Recovery in SQL

Intro

Schema Setup

Transaction – Atomicity & Rollback

Deadlock Simulation

Session 1

Log-Based Recovery

Understanding ACID Properties in SQL Transactions

Intro

What is ACID?

Atomicity

Consistency

Isolation

Durability

Understanding Cursors & Triggers in DBMS

Introduction

Cursors in DBMS

Triggers in DBMS

Example 1: AFTER INSERT Trigger (Audit Log)

Demystifying Normalization in RDBMS: From 1NF to 3NF

Normalization-->

First Normalization Form(1NF)

Second Normalization Form(2NF)

Third Normalization Form(3NF)

College Student and Course Management System using SQL (Oracle)

Introduction

Database Design – Tables and Purpose

Inserting Data into Tables

Queries and Learnings

Views & Stored Procedures

Complete Code:

Conclusion

Learnings Recap

Future Improvements

College Student and Course Management System using SQL (Oracle)

Introduction

Database Design – Tables and Purpose

Inserting Data into Tables

Queries and Learnings

Views & Stored Procedures

Complete Code:

Conclusion

Learnings Recap

Future Improvements

Create a B-Tree Index on `roll_no`

Create a B+ Tree Index on `cgpa`

Create a Hash Index on `dept`