DEV Community: Shrieya S

SIMPLE COLLEGE STUDENT SCHEMA

Shrieya S — Wed, 08 Oct 2025 05:06:17 +0000

MongoDB is one of the most popular NoSQL databases, widely used for scalable and flexible applications. In this tutorial, we’ll perform CRUD (Create, Read, Update, Delete) operations on a college students collection to understand MongoDB better.

To make it more exciting, we’ll run these queries directly on MongoDB Atlas Cluster (cloud-based MongoDB). Along the way, I’ll include screenshots of my MongoDB Atlas dashboard and outputs so you can follow along visually.

Creating a Cluster

Create a free MongoDB Atlas account

Create a cluster and a database called collegeDB

Inside it, create a collection called students

Each student document follows this structure:

*Create *(Insert)

Insert at least 5 student records:

{
student_id: "S001",
name: "Jamie",
age: 19,
department: "CSBS",
year: 2,
cgpa: 8.5
}

{
student_id: "S002",
name: "David",
age: 21,
department: "CSE",
year: 3,
cgpa: 9
}

{
student_id: "S005",
name: "Jonathan",
age: 20,
department: "IT",
year: 3,
cgpa: 7.9
}

Read (Query):-

Display all student records.

Fetch all students: {}Find all students with CGPA > 8.

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

Find students belonging to the Computer Science department.

db.students.find({ department: "CSE" })

Update:-

Update the CGPA of a specific student.

{ "student_id": "S005" }

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

{ "year": 3 }

Delete:-

Delete one student record by student_id.

{ "student_id": "S004" }🚀 Wrap Up

In this tutorial, we learned how to:

Insert new student records

Query students by CGPA and department

Update CGPA and year of study

Delete students based on conditions

MongoDB CRUD operations form the foundation for building real-world apps like college portals, e-learning systems, or admin dashboards.

Thanks to @santhoshnc sir for his gudiance and support!

sir for guiding and motivating us.

DBMS- Transactions,Deadlock &Log -Based Recovery

Shrieya S — Wed, 08 Oct 2025 04:38:31 +0000

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

✅ Transactions & Rollback (Atomicity)

🔒 Deadlock Simulation

📝 Log-Based Recovery

Setup: The Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);INSERT INTO CustomerAccounts VALUES (1, 'Emily', 1000);
INSERT INTO CustomerAccounts VALUES (2, 'Bobby', 1500);
INSERT INTO CustomerAccounts VALUES (3, 'Caleb', 2000);SELECT * FROM CustomerAccounts;Transaction

Atomicity & Rollback

Transactions ensure all-or-nothing execution.

Let’s try transferring 500 from Emily to Bobby, but roll it back midway.

Deduct 500 from Emily

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

Rollback transaction

ROLLBACK;** Check balances**

SELECT * FROM Accounts;Deadlock Simulation

Deadlocks occur when two transactions wait on each other’s locks.

Let’s simulate with two sessions:

Session 1:

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

Do NOT commit

Session 2:

Lock Bobby
UPDATE CustomerAccounts SET balance = balance - 200 WHERE name = 'Bobby';

Do NOT commit

Continuing Session 1

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

Continuing Session 2

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

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

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

Rollback
ROLLBACK;

Verify balances
SELECT * FROM CustomerAccounts;

Wrap Up

In this tutorial, we covered:

Transactions & Rollback → Ensures atomicity

Deadlock Simulation → Shows concurrency pitfalls

Log-Based Recovery → Demonstrates how databases ensure durability

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

Special thanks to @santhoshnc Sir for guidance throughout this assignment.

Indexing,Hashing and Query Optimization in DBMS

Shrieya S — Wed, 08 Oct 2025 04:30:48 +0000

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

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

📖 Key Definitions

🔹 Indexing

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

B-Tree Index

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

B+ Tree Index

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

Hash Index

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

Query Optimization

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

Step 1: Create Students Table

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

Inserting Sample Records

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

CREATE INDEX idx_roll_no ON Students1(roll_no);Query with Index

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

SELECT * FROM Students1 WHERE roll_no = 110;B+ Tree Index on CGPA
B+ Trees are used for range queries, making them perfect for CGPA lookups.

CREATE INDEX idx_cgpa ON Students1(cgpa);Query
Display all students with a CGPA> 8.0

SELECT * FROM Students1 WHERE cgpa > 8.0;Hash Index on dept

Hashing is great for exact matches (not ranges).

CREATE INDEX idx_dept ON Students1(dept);Query
Retrieve all students from the CSBS department

SELECT * FROM Students1 WHERE dept = 'CSBS';

🚀Wrap Up

In this tutorial, we explored:

B-Tree Index → Fast lookup by roll_no

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

Hash Index → Quick equality checks (dept = CSBS)

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

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

Acid properties with SQL Transaction in DBMS

Shrieya S — Wed, 08 Oct 2025 04:22:03 +0000

When working with relational databases, transactions are the building blocks that ensure reliability.

They follow the ACID properties:

Atomicity → All or nothing

Consistency → Valid state before & after

Isolation → Transactions don’t interfere

Durability → Changes survive crashes

In this blog, we’ll explore ACID with SQL scripts using an Accounts table.

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;Step 1: Setup the Accounts Table

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
) ENGINE=InnoDB;

Insert 3 sample rows.

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Sarah', 5000),
(2, 'Jessie', 3000),
(3, 'Benson', 7000);

Run it.

Output:

3 rows insertedCheck the table:

Atomicity

Definition: A transaction is atomic, meaning either all operations succeed or none do.

Example: Transfer 500 from Sarah to Jessie, then rollback

ROLLBACK:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
ROLLBACK;

SELECT * FROM Accounts;

Output:
balances remain unchanged (Sarah=5000, Jessie=3000).

This proves atomicity: either all updates happen, or none.

COMMIT:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
COMMIT;
SELECT * FROM Accounts;

Output: Sarah=4500, Jessie=3500.
Committed → permanent update.

Consistency

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

Rules like constraints must never be violated.

Example: Try inserting negative balance

INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);

Output: Error – CHECK constraint failed.

Database rejects invalid data → consistency is preserved.

Isolation

Definition: Transactions executing at the same time should not interfere with each other.

Example: Two sessions

Session 1 (updating):

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;

Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;

Session 1 sees the reduced balance (2500).Session 2 (reading at same time):

SELECT balance FROM Accounts WHERE acc_no = 1

Durability

Definition: Once a transaction is committed, its changes persist even if the system crashes.

Example: Commit, restart DB, check again

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
COMMIT;Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Benson’s balance increases (7500).

Now restart MySQL server
Reconnect, run again:

USE acid_demo;
SELECT * FROM Accounts WHERE acc_no = 3;

Balance is still 7500.
This proves durability: committed changes survive restarts.

🚀Wrap Up

We demonstrated the ACID properties using SQL:

🔹 Atomicity → Rollback prevents partial updates

🔹 Consistency → Constraints keep data valid

🔹 Isolation → Transactions run independently

🔹 Durability → Committed changes survive crashes

These principles ensure that databases remain reliable, safe, and trustworthy, even under concurrent workloads or unexpected failures.

Thanks to @santhoshnc sir for his guidance and support.

Cursor and Trigger

Shrieya S — Wed, 08 Oct 2025 04:11:02 +0000

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

In this tutorial, we’ll:

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

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

🔹 Cursor

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

Step 1: Employee Cursor Example

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

Step i: Create Employee Table

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

Step ii: Insert Sample Data

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

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

Trigger

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

Step 2:AFTER INSERT Trigger

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

Step i: Create Students Table

CREATE TABLE Students2 (
Student_ID NUMBER PRIMARY KEY,
Student_Name VARCHAR2(50),
Course VARCHAR2(50)
);

Step ii: Create Students_Audit Table

CREATE TABLE Students_Audit (
Audit_ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Student_ID NUMBER,
Student_Name VARCHAR2(50),
Action VARCHAR2(50),
Action_Time TIMESTAMP
);

Step iii: Create AFTER INSERT Trigger(Trigger Implementation)

CREATE OR REPLACE TRIGGER trg_after_student_insert
AFTER INSERT ON Students2
FOR EACH ROW
BEGIN
INSERT INTO Students_Audit (Student_ID, Student_Name, Action, Action_Time)
VALUES (:NEW.Student_ID, :NEW.Student_Name, 'INSERT', SYSTIMESTAMP);
END;
/

Test the Trigger

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

Step iv: Verify Audit Table

SELECT * FROM Students_Audit;Wrap Up

In this tutorial, we learned:

Cursor → Process query results row by row

Trigger → Automatically log student registrations after insert

These features add power and automation to SQL programming!

Thankyou @santhoshnc Sir for his valuable guidance and continuous support in successfully completing this DBMS assignment.

🚀Database Normalization

Shrieya S — Wed, 08 Oct 2025 03:47:13 +0000

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

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

Step 1: Base Table

The initial unnormalized table includes details of students, their courses, instructors, and corresponding grades.Step 2: Identifying Anomalies

Insertion anomaly: A new course cannot be added unless it is linked to a student.

Update anomaly: Modifying a course name requires updating it in several rows.

Deletion anomaly: Removing a student may also remove valuable course details if that student was the only enrollee.

First Normal Form (1NF)

Rule: Eliminate repeating groups, ensure atomic values.

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

SQL Table in 1 NF,

CREATE TABLE Students_1NF (
Student_ID INT,
Student_Name VARCHAR2(100),
Course_ID INT,
Course_Name VARCHAR2(100),
Instructor VARCHAR2(100),
Grade CHAR(2),
PRIMARY KEY (Student_ID, Course_ID)
);

Second Normal Form (2NF)

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

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

SQL Create Tables (2NF):

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Third Normal Form (3NF)

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

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

SQL Create Tables (3NF):

REATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses3NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

CREATE TABLE Students3NF (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Enrollments3NF (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student3NF(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course3NF(CourseID)
);

Step 6: Insert Sample Data

-- Instructors
INSERT INTO Instructor VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Course3NF VALUES ('C101', 'DBMS', 'I01');
INSERT INTO Course3NF VALUES ('C102', 'Data Mining', 'I02');
INSERT INTO Course3NF VALUES ('C103', 'AI', 'I03');

-- Students
INSERT INTO Student3NF VALUES ('S01', 'Arjun');
INSERT INTO Student3NF VALUES ('S02', 'Priya');
INSERT INTO Student3NF VALUES ('S03', 'Kiran');

-- Enrollment
INSERT INTO Enrollment3NF VALUES ('S01', 'C101');
INSERT INTO Enrollment3NF VALUES ('S01', 'C102');
INSERT INTO Enrollment3NF VALUES ('S02', 'C101');
INSERT INTO Enrollment3NF VALUES ('S03', 'C103');Step 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;

** Wrap Up**

We started with an unnormalized table and step-by-step applied:

1NF → Removed repeating groups

2NF → Removed partial dependencies

3NF → Removed transitive dependencies

Result → A clean, normalized database with reduced redundancy, better integrity, and easier queries

A special thanks to @santhoshnc sir for guiding throughout!

College Database Management System with Oracle LiveSQL #sql #oracle #database #learning

Shrieya S — Mon, 25 Aug 2025 13:06:58 +0000

Overview

In this article, I’ll share how I built a College Database Management System using Oracle LiveSQL.
The goal was to design a system that manages students, faculty, and courses while applying SQL concepts like constraints, joins, and views.

Key Highlights

🔹 Creating tables with proper constraints
🔹 Inserting records into tables
🔹 Altering table structures
🔹 Running queries with string & aggregate functions
🔹 Performing joins
🔹 Defining views for simplified access

1. Defining Tables

I began with three main entities: Students, Faculty, and Courses.

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

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

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

2. Populating the Tables

Sample student records:

INSERT INTO Students VALUES (1, 'Aarav Kumar', 'CSE', TO_DATE('2004-05-12','YYYY-MM-DD'), 'aarav.cs@example.com');
INSERT INTO Students VALUES (2, 'Meera Reddy', 'EEE', TO_DATE('2003-09-23','YYYY-MM-DD'), 'meera.ee@example.com');
INSERT INTO Students VALUES (3, 'Vikram Sharma', 'MECH', TO_DATE('2004-01-30','YYYY-MM-DD'), 'vikram.me@example.com');

3. Schema Modification

Later, I added a column for phone numbers to the Students table:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

4. Working with Functions

Query to display names in uppercase and calculate the length of email IDs:

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

5. Connecting Students and Courses

An Enrollments table was introduced to keep track of which student took which course, along with their grade.

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

To simplify, I created a view:

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

6. Aggregate Queries

Example: average credits of all courses and student count:

SELECT AVG(Credits) AS AvgCredits,
COUNT(DISTINCT StudentID) AS TotalStudents
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID;

Final Thoughts

This project helped me:

Practice constraints and alterations
Work with joins, views, and aggregates
Get comfortable with Oracle LiveSQL environment

It’s a compact project idea for students who want to sharpen their SQL basics while building something meaningful. 🚀