DEV Community: Pranav Aadithya

CRUD Operations in MongoDB: Hands-On with a Student Collection

Pranav Aadithya — Fri, 03 Oct 2025 05:40:31 +0000

MongoDB is a NoSQL document database that stores data in JSON-like documents. In this tutorial, we’ll perform CRUD operations using a simple college student schema.

1️⃣ Create (Insert)
Schema:
Student 1
{
"student_id": "S001",
"name": "Pranav",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
}
Student 2
{
"student_id": "S002",
"name": "Anitha",
"age": 21,
"department": "CSE",
"year": 3,
"cgpa": 8.5
}
Student 3
{
"student_id": "S003",
"name": "Ramesh",
"age": 22,
"department": "ECE",
"year": 4,
"cgpa": 7.2
}
Student 4
{
"student_id": "S004",
"name": "Priya",
"age": 19,
"department": "CSBS",
"year": 1,
"cgpa": 9.3
}
Student 5
{
"student_id": "S005",
"name": "Vignesh",
"age": 20,
"department": "Mechanical",
"year": 2,
"cgpa": 6.8
}

Screenshot:

2️⃣Read (Query)

A.Display all students
{}

B.Students with CGPA > 8

{ "cgpa": { "$gt": 8 } }

C.Students in CSBS department

{ "department": { "$in": ["CSBS"] } }

3️⃣Update

A.Update CGPA of a specific student (S002)
{ "student_id": "S002" }

B. Increase year of all 3rd year students by 1
{ "year": 3 }

4️⃣Delete

A. Delete one student by student_id (S005)

{ "student_id": "S004" }

B. Delete all students with CGPA < 7.5

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

✅ Summary of MongoDB CRUD Operations
Operation MongoDB Query
Create insertOne / insertMany
Read find()
Update updateOne / updateMany
Delete deleteOne / deleteMany

MongoDB allows flexible, schema-less data storage, making it ideal for applications requiring rapid iteration and scalability.

Indexing, Hashing & Query Optimization in SQL

Pranav Aadithya — Wed, 01 Oct 2025 08:14:49 +0000

Indexes are essential for faster data retrieval and query optimization. In this tutorial, we’ll explore B-Tree, B+ Tree, and Hash indexes using a Students table.
Step 1 — Create Students Table and Insert Data
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5),
(102, 'Bob', 'CSBS', 7.8),
(103, 'Charlie', 'ECE', 8.2),
(104, 'David', 'CSBS', 9.0),
(105, 'Eva', 'ECE', 6.9),
(106, 'Frank', 'CSBS', 8.1),
(107, 'Grace', 'CSBS', 7.5),
(108, 'Hannah', 'ECE', 8.6),
(109, 'Ivy', 'CSBS', 8.0),
(110, 'Jack', 'CSBS', 8.9),
(111, 'Kiran', 'ECE', 7.7),
(112, 'Liam', 'CSBS', 8.3),
(113, 'Mia', 'ECE', 9.2),
(114, 'Noah', 'CSBS', 8.4),
(115, 'Olivia', 'ECE', 7.6),
(116, 'Paul', 'CSBS', 8.7),
(117, 'Quinn', 'CSBS', 7.9),
(118, 'Ria', 'ECE', 8.8),
(119, 'Sam', 'CSBS', 8.0),
(120, 'Tina', 'ECE', 9.1);

Screenshot:

Step 2 — Create a B-Tree Index on roll_no
B-Tree indexes are default in MySQL for numeric primary keys, but we’ll explicitly create one:

CREATE INDEX idx_roll_no ON Students(roll_no);

Query to fetch a student by roll_no = 110:

SELECT * FROM Students WHERE roll_no = 110;

Expected Output:

+---------+------+------+------+
| roll_no | name | dept | cgpa |
+---------+------+------+------+
| 110 | Jack | CSBS | 8.90 |
+---------+------+------+------+

Using a B-Tree index speeds up retrieval for single-row lookups.
screenshot:

Step 3 — Create a B+ Tree Index on cgpa

MySQL/InnoDB uses B+ Tree internally for indexes on non-primary keys.

CREATE INDEX idx_cgpa ON Students(cgpa);

Query to display all students with cgpa > 8.0:

SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa DESC;

Expected Output:
+---------+--------+------+------+
| roll_no | name | dept | cgpa |
+---------+--------+------+------+
| 113 | Mia | ECE | 9.20 |
| 120 | Tina | ECE | 9.10 |
| 104 | David | CSBS | 9.00 |
| 110 | Jack | CSBS | 8.90 |
| 118 | Ria | ECE | 8.80 |
| 101 | Alice | CSBS | 8.50 |
| 114 | Noah | CSBS | 8.40 |
| 112 | Liam | CSBS | 8.30 |
| 106 | Frank | CSBS | 8.10 |
| 109 | Ivy | CSBS | 8.00 |
+---------+--------+------+------+
B+ Tree index helps with range queries like cgpa > 8.0.

screenshot:

Step 4 — Create a Hash Index on dept
Hash indexes are great for equality lookups.

Note: MySQL only supports hash indexes in Memory tables, or you can simulate using InnoDB for equality searches.

-- For demo, create a MEMORY table
CREATE TABLE StudentsHash (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
) ENGINE=MEMORY;

INSERT INTO StudentsHash SELECT * FROM Students;
CREATE INDEX idx_dept_hash USING HASH ON StudentsHash(dept);

Query to fetch all students from CSBS department:

SELECT * FROM StudentsHash WHERE dept = 'CSBS';

Expected Output:

+---------+--------+------+------+
| roll_no | name | dept | cgpa |
+---------+--------+------+------+
| 101 | Alice | CSBS | 8.50 |
| 102 | Bob | CSBS | 7.80 |
| 104 | David | CSBS | 9.00 |
| 106 | Frank | CSBS | 8.10 |
| 107 | Grace | CSBS | 7.50 |
| 109 | Ivy | CSBS | 8.00 |
| 110 | Jack | CSBS | 8.90 |
| 112 | Liam | CSBS | 8.30 |
| 114 | Noah | CSBS | 8.40 |
| 116 | Paul | CSBS | 8.70 |
| 117 | Quinn | CSBS | 7.90 |
| 119 | Sam | CSBS | 8.00 |
+---------+--------+------+------+

Hash indexes optimize exact match queries, but not range queries.
screenshot:

Step 5 — Observations & Query Optimization Tips
1.B-Tree / B+ Tree: Efficient for range queries and ordered retrieval.
2.Hash Index: Fast for equality searches but not for ranges.
3.Indexes improve SELECT performance but may slow down INSERT/UPDATE due to maintenance.
4.Always choose the right index type depending on query patterns.

✅ Full SQL Script
CREATE DATABASE IF NOT EXISTS StudentsDemo;
USE StudentsDemo;

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
);
-- Insert 20 sample records
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Alice', 'CSBS', 8.5),
(102, 'Bob', 'CSBS', 7.8),
(103, 'Charlie', 'ECE', 8.2),
(104, 'David', 'CSBS', 9.0),
(105, 'Eva', 'ECE', 6.9),
(106, 'Frank', 'CSBS', 8.1),
(107, 'Grace', 'CSBS', 7.5),
(108, 'Hannah', 'ECE', 8.6),
(109, 'Ivy', 'CSBS', 8.0),
(110, 'Jack', 'CSBS', 8.9),
(111, 'Kiran', 'ECE', 7.7),
(112, 'Liam', 'CSBS', 8.3),
(113, 'Mia', 'ECE', 9.2),
(114, 'Noah', 'CSBS', 8.4),
(115, 'Olivia', 'ECE', 7.6),
(116, 'Paul', 'CSBS', 8.7),
(117, 'Quinn', 'CSBS', 7.9),
(118, 'Ria', 'ECE', 8.8),
(119, 'Sam', 'CSBS', 8.0),
(120, 'Tina', 'ECE', 9.1);
-- B-Tree index on roll_no
CREATE INDEX idx_roll_no ON Students(roll_no);
SELECT * FROM Students WHERE roll_no = 110;
-- B+ Tree index on cgpa
CREATE INDEX idx_cgpa ON Students(cgpa);
SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa DESC;
-- Hash index on dept (Memory table)
CREATE TABLE StudentsHash (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(30),
cgpa DECIMAL(3,2)
) ENGINE=MEMORY;
INSERT INTO StudentsHash SELECT * FROM Students;
CREATE INDEX idx_dept_hash USING HASH ON StudentsHash(dept);
SELECT * FROM StudentsHash WHERE dept = 'CSBS';

Transactions, Deadlocks & Log-Based Recovery in SQL

Pranav Aadithya — Wed, 01 Oct 2025 08:02:22 +0000

In this tutorial, we’ll explore how to work with transactions, simulate deadlocks, and understand log-based recovery using SQL (MySQL/PostgreSQL).
We’ll use a simple Accounts table:

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

Screenshot:

1️⃣Transaction – Atomicity & Rollback

Goal: Ensure all-or-nothing transactions.
-Start a transaction to transfer 500 from Alice to Bob.
-Rollback before committing.
-- Start transaction
START TRANSACTION;
-- Deduct 500 from Alice
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
-- Add 500 to Bob
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
-- Rollback the transaction
ROLLBACK;
-- Check balances
SELECT * FROM Accounts;

Expected Output (balances unchanged):

+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 1 | Alice | 1000 |
| 2 | Bob | 1500 |
| 3 | Charlie | 2000 |
+--------+---------+---------+
The rollback ensures no partial update occurs.

screenshot:

2️⃣ Deadlock Simulation
Goal: Observe deadlock when two sessions try to access the same resources in conflicting order.

Step 1: Open two sessions

Session 1:

START TRANSACTION;
-- Lock Alice’s account
SELECT * FROM Accounts WHERE acc_no = 1 FOR UPDATE;
-- Try to update Bob (this will wait if Session 2 locks it)
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2;

Session 2:

START TRANSACTION;
-- Lock Bob’s account
SELECT * FROM Accounts WHERE acc_no = 2 FOR UPDATE;
-- Try to update Alice (this will wait if Session 1 locks it)
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 1;

Expected Outcome:
Both sessions are waiting for each other → deadlock detected.
MySQL/PostgreSQL will automatically roll back one transaction to resolve the deadlock.

Screenshot:

3️⃣ Log-Based Recovery
Goal: Verify undo operations are recorded in logs for crash recovery.

Ensure logging is enabled (MySQL: Binary log; PostgreSQL: WAL).
-- Start transaction
START TRANSACTION;
-- Update a record
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
-- Rollback
ROLLBACK;

Check the log:

MySQL: SHOW BINLOG EVENTS; or inspect binary log.
PostgreSQL: WAL automatically records undo info.

Expected Result:
The update is rolled back, and the log contains the undo operation.

Screenshot:

✅ Full SQL Script

You can save the following as transactions_deadlocks.sql and run in MySQL CLI:
CREATE DATABASE IF NOT EXISTS TransactionsDemo;
USE TransactionsDemo;

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);
-- Transaction and rollback demo
START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM Accounts;
-- Deadlock simulation: run in two sessions using SELECT ... FOR UPDATE
-- Session 1 locks Alice, Session 2 locks Bob
-- Attempt conflicting updates to observe deadlock
-- Log-based recovery demo
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
ROLLBACK;

Troubleshooting Tips
1.Rollback not working? Make sure you start the transaction with START TRANSACTION;.
2.Deadlock not detected? Ensure both sessions are using SELECT ... FOR UPDATE or update statements in conflicting order.
3.Logs not visible? Check that binary logging is enabled in MySQL (SHOW VARIABLES LIKE 'log_bin';).

Understanding ACID Properties with SQL Transactions

Pranav Aadithya — Wed, 01 Oct 2025 07:50:54 +0000

ACID stands for Atomicity, Consistency, Isolation, Durability – the four key properties that ensure reliable database transactions. In this tutorial, we’ll demonstrate ACID concepts using MySQL 8.0 with practical examples.

We’ll use a simple Accounts table:

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

INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);

Screenshot:

1️⃣ Atomicity
Goal: Ensure transactions are all-or-nothing.
1.Start a transaction to transfer money from Alice to Bob.
2.Rollback midway and verify no partial updates occur.

-- Start transaction
START TRANSACTION;
-- Deduct 1000 from Alice
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
-- Simulate an error (e.g., invalid operation)
-- For demonstration, we rollback instead of committing
ROLLBACK;
-- Check balances
SELECT * FROM Accounts;
Expected Output (balances unchanged):

+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 101 | Alice | 5000 |
| 102 | Bob | 3000 |
| 103 | Charlie | 7000 |
+--------+---------+---------+

The rollback ensures no partial update occurs.

screenshot:

2️⃣ Consistency
Goal: Ensure the database remains in a valid state.
1.Try inserting a record with a negative balance.
2.Define a CHECK constraint to reject invalid balances.

ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- Attempt invalid insert
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

Expected Result:
1.MySQL rejects the insert with an error due to the constraint.
2.Database remains consistent.
Screenshot:

3️⃣ Isolation
Goal: Ensure concurrent transactions do not interfere.
Open two MySQL sessions:
Session 1: Update Alice’s balance.
Session 2: Read Alice’s balance before Session 1 commits.

Session 1:
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
-- Do not commit yet
Session 2:
SELECT * FROM Accounts WHERE acc_no = 101;

Expected Output:
Session 2 does not see uncommitted changes (depending on isolation level).
Commit Session 1:
COMMIT;

Screenshot:

4️⃣ Durability
Goal: Ensure changes persist even after a crash.
Commit a transaction:
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;

-Restart MySQL server.
-Check balances:

SELECT * FROM Accounts;

Expected Output:
-The update remains permanent.

Screenshot:

+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 101 | Alice | 5000 |
| 102 | Bob | 3500 |
| 103 | Charlie | 7000 |
+--------+---------+---------+

✅ Full SQL Script
You can save the following in ACID_demo.sql and run in MySQL CLI:
CREATE DATABASE IF NOT EXISTS ACID_demo;
USE ACID_demo;

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

INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);

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

-- Atomicity demo
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
ROLLBACK;

-- Isolation demo (run in two sessions)
-- Session 1
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
-- Session 2: SELECT * FROM Accounts WHERE acc_no = 101;
-- Then commit Session 1
COMMIT;

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

SELECT * FROM Accounts;

Troubleshooting Tips

1.Rollback not working? Ensure you’re in a transaction using START TRANSACTION;.
2.Negative balances accepted? Add CHECK (balance >= 0) constraint.
3.Isolation behavior varies: MySQL default is REPEATABLE READ; change with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;.
4.Durability: MySQL uses InnoDB engine for ACID support.

Cursors and Triggers

Pranav Aadithya — Wed, 01 Oct 2025 07:26:09 +0000

In this tutorial, we’ll explore how to use cursors and triggers in MySQL 8.0.

We will cover:

Using a cursor to fetch employees with salary greater than 50,000.
Creating an AFTER INSERT trigger to automatically log student registrations. The guide includes ready-to-paste SQL, expected outputs, screenshots, and troubleshooting tips.

Step 1 — Create a fresh database
DROP DATABASE IF EXISTS demo_db;
CREATE DATABASE demo_db;
USE demo_db;

Screenshot:

Step 2 — Create Employee table and insert sample rows
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);
INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Arjun', 60000),
(2, 'Priya', 45000),
(3, 'Kiran', 75000),
(4, 'Meera', 30000);

Screenshot:

Step 3 — Create stored procedure with a cursor
We’ll create ShowHighSalaryEmployees() to display employees with Salary > 50,000 row by row.

DROP PROCEDURE IF EXISTS ShowHighSalaryEmployees;
DELIMITER $$
CREATE PROCEDURE ShowHighSalaryEmployees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empName VARCHAR(50);
DECLARE empSalary DECIMAL(10,2);
DECLARE cur CURSOR FOR
SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO empName, empSalary;
IF done = 1 THEN
LEAVE read_loop;
END IF;
SELECT empName AS Employee, empSalary AS Salary;
END LOOP;
CLOSE cur;
END$$
DELIMITER ;

Screenshot:

Step 4 — Call the procedure
CALL ShowHighSalaryEmployees();

Expected Output:
+----------+---------+
| Employee | Salary |
+----------+---------+
| Arjun | 60000.0 |
+----------+---------+

+----------+---------+
| Employee | Salary |
+----------+---------+
| Kiran | 75000.0 |
+----------+---------+

Each SELECT inside the procedure appears as a separate result set in MySQL CLI.

Step 5 — Create Students and Student_Audit tables

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50),
Dept VARCHAR(30)
);
CREATE TABLE Student_Audit (
AuditID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
Action VARCHAR(100),
ActionTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Screenshot:

Step 6 — Create AFTER INSERT trigger on Students
DROP TRIGGER IF EXISTS AfterStudentInsert;
DELIMITER $$
CREATE TRIGGER AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action)
VALUES (NEW.StudentID, CONCAT('Student ', NEW.StudentName, ' registered'));
END$$
DELIMITER ;

Screenshot:

Step 7 — Insert sample students and view audit log

INSERT INTO Students (StudentID, StudentName, Dept) VALUES (101, 'Rahul', 'CSE');
INSERT INTO Students (StudentID, StudentName, Dept) VALUES (102, 'Anita', 'ECE');
SELECT * FROM Student_Audit;

Expected Output:
+---------+-----------+---------------------------+---------------------+
| AuditID | StudentID | Action | ActionTime |
+---------+-----------+---------------------------+---------------------+
| 1 | 101 | Student Rahul registered | 2025-10-01 11:30:00 |
| 2 | 102 | Student Anita registered | 2025-10-01 11:31:00 |
+---------+-----------+---------------------------+---------------------+

Step 8 — Verification Queries
SELECT * FROM Employee;
SELECT * FROM Students;
SELECT * FROM Student_Audit;
SELECT EmpName, Salary FROM Employee WHERE Salary > 50000;

Screenshot:

Step 10 — Full ready-to-run .sql script
Save the SQL from Steps 1–7 into demo_script.sql and run:
mysql -u root -p < demo_script.sql

Troubleshooting Tips
1.DELIMITER errors — Use MySQL CLI; GUI clients sometimes reject DELIMITER.
2.Cursor/Handler errors — Ensure DECLARE statements are at the start of the block.
3.Trigger not firing — Check table names and case sensitivity.
4.Permission issues — Ensure your MySQL user has CREATE ROUTINE, CREATE TRIGGER, INSERT privileges.

Database Normalization in MySQL (1NF 2NF 3NF) – Simple Example

Pranav Aadithya — Wed, 01 Oct 2025 06:52:45 +0000

Introduction:
In this post, we will understand Database Normalization with a simple student-course example.
We will start from a single table (base table) and convert it step by step into 1NF, 2NF, and 3NF using MySQL 8.0.
Finally, we will run a JOIN query to display students with their courses and instructors.
🔹Base Table
Here is the starting data:
StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Arjun C101 DBMS Dr. Kumar 9876543210
S01 Arjun C102 DataMining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655

🔹Anomalies in this table:
Insert anomaly: Can’t add a new instructor without adding a course.
Update anomaly: If Dr. Kumar’s phone changes, we must update many rows.
Delete anomaly: If S02’s record is deleted, course DBMS info may also be lost.

🔹Step 1 — First Normal Form (1NF):
In 1NF, all values should be atomic (no repeating groups).
We also add a primary key = (StudentID, CourseID).
CREATE TABLE Base1NF (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(20),
PRIMARY KEY (StudentID, CourseID)
);
INSERT INTO Base1NF VALUES
('S01','Arjun','C101','DBMS','Dr. Kumar','9876543210'),
('S01','Arjun','C102','Data Mining','Dr. Mehta','9123456780'),
('S02','Priya','C101','DBMS','Dr. Kumar','9876543210'),
('S03','Kiran','C103','AI','Dr. Rao','9988776655');

🔹Step 2 — Second Normal Form (2NF)
In 2NF, we remove partial dependencies.
We create 3 tables: Students, Courses, Enrollments.
CREATE TABLE Students2NF (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Courses2NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(20)
);
CREATE TABLE Enrollments2NF (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students2NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses2NF(CourseID)
);
-- Insert Students
INSERT INTO Students2NF VALUES ('S01','Arjun'),('S02','Priya'),('S03','Kiran');
-- Insert Courses
INSERT INTO Courses2NF VALUES
('C101','DBMS','Dr. Kumar','9876543210'),
('C102','Data Mining','Dr. Mehta','9123456780'),
('C103','AI','Dr. Rao','9988776655');
-- Insert Enrollments
INSERT INTO Enrollments2NF VALUES
('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');

🔹 Step 3 — Third Normal Form (3NF)
In 3NF, we remove transitive dependencies.
Instructor phone depends on Instructor, not Course → so we create a separate Instructors table.
CREATE TABLE Instructors3NF (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(20)
);
CREATE TABLE Students3NF (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);
CREATE TABLE Courses3NF (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors3NF(InstructorID)
);
CREATE TABLE Enrollments3NF (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses3NF(CourseID)
);
-- Insert Instructors
INSERT INTO Instructors3NF VALUES
(1,'Dr. Kumar','9876543210'),
(2,'Dr. Mehta','9123456780'),
(3,'Dr. Rao','9988776655');
-- Insert Students
INSERT INTO Students3NF VALUES
('S01','Arjun'),('S02','Priya'),('S03','Kiran');
-- Insert Courses
INSERT INTO Courses3NF VALUES
('C101','DBMS',1),
('C102','Data Mining',2),
('C103','AI',3);
-- Insert Enrollments
INSERT INTO Enrollments3NF VALUES
('S01','C101'),('S01','C102'),('S02','C101'),('S03','C103');

🔹Step 4 — JOIN Query

Now we can easily display all students with their courses and instructors.

SELECT s.StudentID, s.StudentName,
c.CourseID, c.CourseName,
i.InstructorName, i.InstructorPhone
FROM Enrollments3NF e
JOIN Students3NF s ON e.StudentID = s.StudentID
JOIN Courses3NF c ON e.CourseID = c.CourseID
JOIN Instructors3NF i ON c.InstructorID = i.InstructorID;

👉 Output:
S01 | Arjun | C101 | DBMS | Dr. Kumar | 9876543210
S01 | Arjun | C102 | Data Mining | Dr. Mehta | 9123456780
S02 | Priya | C101 | DBMS | Dr. Kumar | 9876543210
S03 | Kiran | C103 | AI | Dr. Rao | 9988776655

✅ Conclusion
1NF: atomic values, primary key added.
2NF: separated Students, Courses, Enrollments.
3NF: separated Instructors table, removed redundancy.
Now our database is clean, consistent, and free from anomalies 🎉.

Build a College Database in Oracle LiveSQL – Step-by-Step Guide

Pranav Aadithya — Wed, 20 Aug 2025 16:07:43 +0000

🎓 College Database Management System – Oracle LiveSQL

This project demonstrates how to design and query a simple College Database using Oracle SQL on LiveSQL
.
We create tables, insert data, apply constraints, run queries, and even build views & stored procedures.

Schema Design

We define four tables:

Faculty – stores teacher details.

Students – student records with department, DOB, email & phone.

Courses – subject details with credits (1–5 only).

Enrollments – junction table mapping students ↔ courses with grades.

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

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

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5)
);

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

✅ Here we use PRIMARY KEY, UNIQUE, and CHECK constraints to ensure data integrity.

Inserting Data

We add sample students, courses, and enrollments.

-- Students
INSERT INTO Students VALUES (1, 'Arjun', 'CSE', DATE '2004-05-15', 'arjun@college.com', 9876543210);
INSERT INTO Students VALUES (2, 'Meera', 'ECE', DATE '2003-11-20', 'meera@college.com', 9876543211);
INSERT INTO Students VALUES (3, 'Rahul', 'Mechanical', DATE '2004-02-10', 'rahul@college.com', 9876543212);

-- Courses
INSERT INTO Courses VALUES (101, 'Database Systems', 4);
INSERT INTO Courses VALUES (102, 'Operating Systems', 3);
INSERT INTO Courses VALUES (103, 'Networks', 2);

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

Queries

Now, let’s explore the data.

a) String Functions & Aggregates
SELECT UPPER(Name) AS StudentNameUpper, LENGTH(Email) AS EmailLength
FROM Students;

👉 Converts names to uppercase & shows email length.

SELECT AVG(Credits) AS AvgCredits, (SELECT COUNT(*) FROM Students) AS TotalStudents
FROM Courses;

👉 Finds average course credits & total number of students.

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

👉 Displays each student with their enrolled course and grade.

c) Group By + Having
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 0;

👉 Shows number of students per department (only departments with students).

Views CREATE OR REPLACE VIEW StudentCoursesView AS SELECT s.Name, c.CourseName, e.Grade FROM Students s JOIN Enrollments e ON s.StudentID = e.StudentID JOIN Courses c ON e.CourseID = c.CourseID;

👉 A view gives a reusable query showing student ↔ course ↔ grade.

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

👉 Procedure to update a student’s grade in a course easily.

✅ Conclusion

This LiveSQL use case covers:
✔️ Table creation with constraints
✔️ Data insertion
✔️ String, aggregate & join queries
✔️ Group By with Having
✔️ Views for easy access
✔️ Stored procedure for updates

A neat mini College Database Management System! 🚀