DEV Community: Rithika

MangoDB CRUD

Rithika — Mon, 06 Oct 2025 15:03:20 +0000

🚀 Exploring MongoDB CRUD Operations: A Beginner-Friendly Walkthrough

MongoDB is one of the most widely used NoSQL databases, known for its scalability, flexibility, and document-based structure. In this tutorial, we’ll explore how to perform the core CRUD operations (Create, Read, Update, Delete) on a college students collection to understand MongoDB’s power in real-world use cases.

🛠️ Setting Up the Cluster Create a free account on MongoDB Atlas. Build your first cluster using the default free-tier configuration. Connect it with MongoDB Compass or VS Code for seamless query execution.

🧩 Schema Design

Each student document in the collection follows this structure:

{ student_id: "A101", name: "Aarav", age: 20, department: "AIML", year: 2, cgpa: 8.6 }

This document model makes it easy to store and query data without worrying about rigid table structures.

✳️ CREATE (Insert Documents)

Let’s begin by inserting a few student records into our collection:

{ student_id: "A101", name: "Aarav", age: 20, department: "AIML", year: 2, cgpa: 8.6 } { student_id: "A102", name: "Diya", age: 21, department: "CSE", year: 3, cgpa: 9.2 } { student_id: "A103", name: "Karthik", age: 19, department: "EEE", year: 2, cgpa: 8.3 } { student_id: "A104", name: "Meera", age: 22, department: "ECE", year: 4, cgpa: 7.8 } { student_id: "A105", name: "Rahul", age: 20, department: "IT", year: 3, cgpa: 8.9 }

Each record represents a student with essential details like department, academic year, and CGPA.

🔍 READ (Query Documents)

To fetch all student records, you can simply use:

db.students.find({})

This command retrieves all documents from the students collection. You can also apply filters, for example, to display only students with a CGPA above 8.5:

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

These queries make MongoDB powerful and intuitive for working with large datasets.

✏️ UPDATE (Modify Documents)

Now, let’s perform some update operations.

1️⃣ Update a specific student’s CGPA:

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

2️⃣ Increase the year of study for all 3rd-year students by 1:

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

MongoDB allows both single document and bulk updates, making it ideal for large-scale applications like college management systems.

🗑️ DELETE (Remove Documents)

To remove a specific record by student ID:

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

To delete all students whose CGPA is below 7.5:

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

Deletion operations in MongoDB are straightforward yet powerful—ensuring that your database remains clean and efficient.

🎓 Wrap-Up

In this walkthrough, we learned how to:

Insert new student records Retrieve data based on filters like department and CGPA Update values dynamically Delete specific or conditional records

These CRUD operations form the backbone of every MongoDB application, whether it’s a college portal, an e-learning platform, or a student management dashboard.

A huge thanks to @santhoshnc sir for his continuous guidance and encouragement throughout this learning journey.

DBMS: TRANSACTIONS,DEADLOCKS & LOG- BASED RECOVERY

Rithika — Mon, 06 Oct 2025 14:22:43 +0000

🔐 Mastering MySQL Transactions and Row-Level Locking: A Practical Walkthrough

When working with relational databases, one of the biggest challenges developers face is maintaining data consistency when multiple users or applications try to access and modify data at the same time.

Imagine several banking transactions happening simultaneously — one user transferring money, another updating their profile, and a third checking their balance. Without proper control, these operations could interfere with each other, leading to inaccurate or inconsistent data.

🧩 Scenario 1: Locking Rows with SELECT ... FOR UPDATE

To start, let’s consider a simple example that demonstrates row-level locking — an essential feature that prevents multiple users from changing the same record at the same time.

START TRANSACTION;

Lock Riya's row
SELECT * FROM Accounts WHERE name = 'Riya' FOR UPDATE;
Update Arjun's balance (allowed if not locked by another session)
UPDATE Accounts SET balance = balance + 10 WHERE name = 'Arjun';

In the above example, we begin a transaction using the START TRANSACTION command. The statement SELECT ... FOR UPDATE is used to lock Riya’s row, which means that as long as this transaction remains active, no other user or session can modify that particular record. This kind of locking is incredibly useful in scenarios like banking systems or online wallets, where two users might attempt to modify the same account balance simultaneously.

By locking the row until the transaction is either committed or rolled back, MySQL ensures that data changes are applied safely and in the correct order. Meanwhile, other rows — such as Arjun’s — remain accessible for updates by other transactions, allowing parallel processing without conflicts.

💾 Scenario 2: Rolling Back a Transaction

Now, let’s look at how MySQL lets you undo changes using the ROLLBACK command. This feature is crucial whenever an operation fails or you need to revert modifications after an error.

START TRANSACTION;
Transfer funds from Riya to Arjun
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Riya';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Arjun';

ROLLBACK;

Verify balances
SELECT * FROM Accounts;

Here, the idea is simple: Riya is transferring ₹500 to Arjun. Both updates happen inside a transaction, meaning MySQL treats them as a single logical unit of work. If everything goes as expected, the transaction can be committed, permanently saving the changes. But if something goes wrong midway — for example, a constraint fails or the server crashes — you can issue a ROLLBACK command to revert all changes made in that session.

This approach guarantees that your data never ends up in an incomplete or inconsistent state. It’s like having an undo button for your database operations — one that ensures reliability and prevents data corruption.

🔄 Scenario 3: Locking Arjun, Updating Riya

In the final example, let’s flip the situation around. Here, we’ll lock one user’s record and update another’s, illustrating how MySQL provides fine-grained concurrency control.

START TRANSACTION;

Lock Arjun's row
SELECT * FROM Accounts WHERE name = 'Arjun' FOR UPDATE;
Update Riya's balance
UPDATE Accounts SET balance = balance + 20 WHERE name = 'Riya';

💡 Key Takeaways

Transactions ensure that multiple operations are treated as one unit — either all succeed or all fail.

Row-level locking prevents data conflicts by restricting access to specific rows during transactions.

ROLLBACK allows you to revert changes safely in case of errors or failed operations.

These mechanisms together help maintain data consistency, accuracy, and system stability, even under heavy load.

🚀 Final Thoughts

Exploring these MySQL features through Oracle Live SQL or your local environment can really strengthen your understanding of how databases maintain order in complex, multi-user systems. Once you start experimenting with transactions and locks, you’ll realize how essential they are for real-world applications like banking, e-commerce, and analytics.

The more you play with these commands — testing commits, rollbacks, and concurrent updates the more confident you’ll become in building safe, reliable, and high-performance database systems.

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

ACID Properties with SQL Transactions in DBMS

Rithika — Mon, 06 Oct 2025 14:06:16 +0000

🔍 Demystifying SQL Constraints with Oracle Live SQL

Lately, I’ve been exploring Oracle Live SQL, and it’s been an exciting deep dive into how constraints and transaction control maintain the accuracy and reliability of data. I thought I’d share a few takeaways from my learning journey — especially how constraints act as invisible guards for your database!

🧱 Understanding Why Constraints Matter

When you design a database, constraints are like the rules that keep your data honest.

They prevent invalid entries and ensure your tables stay consistent.

Here’s a simple example I practiced:

CREATE TABLE ACCOUNTS (
account_id NUMBER,
account_type VARCHAR2(20),
balance NUMBER,
CONSTRAINT acc_pk PRIMARY KEY (account_id),
CONSTRAINT acc_balance_ck CHECK (balance >= 0) -- No negative balances allowed!
);

This structure guarantees two important things:

Every account must have a unique identifier.

No one can insert a negative balance, ensuring clean and logical financial data.

💡 In short: Constraints keep your tables reliable even before a single line of application code is written.

⚙️ Playing with Transactions: Merge or Mayhem?

Next, I experimented with transactions to see how SQL behaves when duplicate or conflicting data is inserted.

This helped me understand how constraints interact with multiple inserts inside a single block.

BEGIN
INSERT INTO Accounts VALUES ('David', 3000);
INSERT INTO Accounts VALUES ('David', 4000);
END;

Depending on how your schema is designed, this block might:

Merge the balances intelligently (if logic is built to handle it), or

Throw an error for violating the unique or primary key constraint.

This experiment taught me that transactions are not just about committing data—they’re also about managing conflicts and maintaining data accuracy when something goes wrong.

🧠 The Power of Minimal Insert Testing

To better understand how constraints respond to incomplete data, I tried inserting a record without all the required fields:

INSERT INTO Accounts (acc_no, name)
VALUES (101, 'Pranav');

If your table enforces a NOT NULL constraint or a DEFAULT value for the balance column, the database will either:

Reject the record immediately (constraint violation), or

Automatically fill in the missing value with a default.

Either way, it’s a great way to test the strength and flexibility of your schema design.

🔒 Key Takeaways

Primary Key → Ensures every row is unique.

Check Constraint → Validates that your data follows logical rules.

Transactions → Help you handle multiple actions safely.

Testing Inserts → Reveals how well your design prevents bad data.

🚀 Final Thoughts

Working with Oracle Live SQL gave me a hands-on understanding of how small details—like a single constraint—can protect entire databases from corruption.

Whether you’re a beginner or brushing up on SQL fundamentals, playing around with constraints and transactions is one of the best ways to build intuition for data integrity.

Thanks @santhoshnc sir for his guidance and support.

Indexing,Hashing & Query optimization in DBMS

Rithika — Mon, 06 Oct 2025 13:51:55 +0000

Getting Hands-On with Oracle Live SQL: My Journey into Database Fundamentals

I recently dove into Oracle Live SQL to sharpen my database skills, and I wanted to share some of the cool things I learned—especially around clusters, indexing, and querying data efficiently.

🧩Creating a Clustered Setup

I started by experimenting with clusters, which allow related tables to be stored together physically. Here's a snippet of what I tried:

CREATE CLUSTER emp_dept_cluster (
deptno NUMBER(3)
)
SIZE 512;

CREATE INDEX idx_emp_dept_cluster
ON CLUSTER emp_dept_cluster;

Then I created two tables—dept and emp—that share the deptno key and are clustered together:

CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
CLUSTER emp_dept_cluster (deptno);

CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3)
)
CLUSTER emp_dept_cluster (deptno);

Pro tip: If you get ORA-00955: name is already used by an existing object, it means you’ve already created that object—just drop it or rename it before retrying.

🎓Building a Student Table and Querying Data

Next, I created a simple student table and practiced inserting and retrieving data:

CREATE TABLE student (
roll_no NUMBER,
name VARCHAR2(20),
dept VARCHAR2(10),
cgpa NUMBER
);

INSERT INTO student VALUES (101, 'Alice', 'CS', 8.5);
INSERT INTO student VALUES (110, 'Bob', 'IT', 7.8);
-- more inserts...
SELECT * FROM student;

This helped me understand how to structure data and run basic queries.

⚡Indexing for Performance

I also explored how indexes improve query performance:

-- Primary key index is implicit
CREATE TABLE student (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(50),
cgpa NUMBER(3,2)
);

-- Explicit index on name
CREATE INDEX idx_name ON student(name);

-- Composite index
CREATE INDEX idx_dept_cgpa ON student(dept, cgpa);

Using these indexes, I ran queries like:

SELECT * FROM student WHERE name = 'Jack';
SELECT * FROM student WHERE dept = 'CSE' AND cgpa = 9.5;

The results were fast and efficient—Oracle Live SQL even shows you which indexes are being used!

🧮Categorizing with CASE Statements

Finally, I played with the CASE clause to categorize products based on price:

SELECT product_id, product_name,
CASE
WHEN list_price < 200 THEN 'Low'
WHEN list_price BETWEEN 200 AND 500 THEN 'Medium'
ELSE 'High'
END AS cost
FROM products
ORDER BY cost;

This is a great way to add logic directly into your queries.

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

Cursor & Trigger in DBMS

Rithika — Mon, 06 Oct 2025 09:08:55 +0000

When handling databases, there are times we need to process records one at a time (using Cursors) or automatically respond to specific events (using Triggers).

In this guide, we’ll:
✅ Create a Cursor to fetch employees earning more than 50,000
✅ Implement an AFTER-INSERT Trigger to maintain a student audit log

🔹 Cursor

A cursor acts as a pointer that allows processing query results row by row instead of all together. This is especially useful when applying custom logic to individual records.

Step 1: Employee Cursor Example

Let’s create a cursor to list employees with a salary above 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('Employee: ' || v_EmpName || ', Salary: ₹' || v_Salary);
END LOOP;
CLOSE emp_cursor;
END;
/
🔹 Trigger

A trigger is a stored procedure that runs automatically when a defined event occurs (INSERT, UPDATE, or DELETE).

Step 2: AFTER INSERT Trigger

We’ll create a Students table and a Students_Audit table to log 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

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;
/
Step iv: 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 v: Verify Audit Table

SELECT * FROM Students_Audit;

Conclusion

In this tutorial, we explored:

Cursor → Process query results row by row
Trigger → Automatically log student registrations after insertion

These tools bring efficiency and automation to your SQL workflows!

A special thanks to @santhoshnc Sir for the guidance and support in completing this DBMS assignment successfully.

Understanding Database Normalization (1NF 2NF 3NF) with Oracle SQL — Step-by-Step Example

Rithika — Mon, 06 Oct 2025 08:29:23 +0000

Database normalization is a crucial concept in DBMS that ensures data integrity and eliminates redundancy.
In this article, I’ve explained 1NF, 2NF, and 3NF through a real-world example and demonstrated each step using Oracle Live SQL.

🧩 The Original Data Structure
We’ll begin with this unnormalized dataset, which contains redundant information:

StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Abi C101 DBMS Dr. Saran 9876543210
S01 Abi C102 Data Mining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655

⚠️ Step 1 — Detecting Data Issues
In the above table, we can identify three common anomalies:

🔸 Insertion anomaly – Can’t add a new course unless a student enrolls in it.
🔸 Update anomaly – If Dr. Kumar changes his phone number, it must be modified in multiple rows.
🔸 Deletion anomaly – If all students drop the “AI” course, we lose Dr. Rao’s details completely.

We’ll now fix these issues using normalization, step by step.

🧱 Step 2 — Transform to 1NF (First Normal Form)

Rule: Every field should hold only atomic (single) values, and repeating groups must be removed.

✅ Our data already meets 1NF requirements, but let’s formally create it in SQL:

CREATE TABLE BaseTable (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

🧩 Step 3 — Move to 2NF (Second Normal Form)

Rule: Eliminate partial dependencies — every non-key attribute must depend on the entire composite key.

Here, the composite key is (StudentID, CourseID).

We’ll divide the structure into Student, Course, and Enrollment tables:

CREATE TABLE Student (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);

CREATE TABLE Course (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

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

🧠 Step 4 — Upgrade to 3NF (Third Normal Form)

Rule: Remove transitive dependencies — non-key attributes should not depend on other non-key attributes.

Since InstructorPhone depends on Instructor, not CourseID, we’ll introduce a separate Instructor table:

CREATE TABLE Instructor (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

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

🧾 Step 5 — Final Normalized Structure
Tables Overview:

Student(StudentID, StudentName)

Instructor(InstructorID, InstructorName, InstructorPhone)

Course(CourseID, CourseName, InstructorID)

Enrollment(EnrollID, StudentID, CourseID)

This model completely removes redundancy and ensures a consistent, scalable database structure.

🧩 Step 6 — Insert Sample Records

INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');

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

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

INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S01', 'C101');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S01', 'C102');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S02', 'C101');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('S03', 'C103');

🔍 Step 7 — Retrieve Data Using JOINs

SELECT
s.StudentID,
s.StudentName,
c.CourseName,
i.InstructorName,
i.InstructorPhone
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Output:
StudentID StudentName CourseName InstructorName InstructorPhone
S01 Arjun DBMS Dr. Kumar 9876543210
S01 Arjun Data Mining Dr. Mehta 9123456780
S02 Priya DBMS Dr. Kumar 9876543210
S03 Kiran AI Dr. Rao 9988776655

✅ Final Insights

Through normalization:

Data duplication is minimized
Insertion, update, and deletion anomalies are resolved
The database becomes more structured, consistent, and efficient
By applying these steps in Oracle Live SQL, you can clearly visualize how normalization enhances your database design.

A special thanks to @santhoshnc sir for guiding me throughout these normalization concepts.