DEV Community: MBEWE CATHERINE 24CB068

ACID Properties with SQL Transactions in DBMS

MBEWE CATHERINE 24CB068 — Tue, 07 Oct 2025 14:06:49 +0000

Introduction: Understanding ACID Properties through SQL Transactions

In the world of database management, ensuring data reliability and consistency is paramount. This is where the ACID properties Atomicity, Consistency, Isolation, and Durability come into play. These principles form the backbone of transaction management, guaranteeing that database operations are executed safely, even in the face of errors, system crashes, or concurrent access.

In this blog, we’ll explore how to demonstrate each of the ACID properties practically using SQL transactions in MySQL. Starting from creating a simple database, we'll walk through examples that showcase how ACID principles maintain data integrity and provide a robust framework for handling complex operations.

Let’s dive in by setting up our demo environment and then uncover the power of ACID in action.

Demonstrating ACID Properties with SQL Transactions:-

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;

Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT).

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, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 7000);

Output: 3 rows inserted.

Check the table:

SELECT * FROM Accounts;

Output:
acc_no | name | balance
1 | Alice | 5000
2 | Bob | 3000
3 | Charlie | 7000

Atomicity (Rollback vs Commit)

Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

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 (Alice=5000, Bob=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: Alice=4500, Bob=3500.
Committed → permanent update.

Consistency

Try inserting a wrong record:

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

Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.

isolation (needs 2 query tabs)

Open two query tabs in Workbench:

Tab A = Session 1

Tab B = Session 2

In Tab A,

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
-- Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;

Tab A sees the reduced balance (2500).

In Tab B,

SELECT balance FROM Accounts WHERE acc_no = 1;

If using default isolation (REPEATABLE READ), Tab B still sees old committed value (4500), not Tab A’s uncommitted update.

Now, back in Tab A:

ROLLBACK;

Both sessions see Alice back to 4500.
This shows isolation.

Durability

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

Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Charlie’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.

Conclusion: The Power of ACID in Reliable Database Transactions

ACID properties are fundamental to ensuring that database transactions are processed reliably and securely. By guaranteeing atomicity, consistency, isolation, and durability, databases can handle complex operations without risking data corruption or loss even under concurrent access or unexpected failures.

Through practical examples in MySQL, we've seen how these principles work behind the scenes to protect data integrity and provide a stable environment for applications. Whether you're a developer, database administrator, or tech enthusiast, understanding and leveraging ACID properties is essential for building trustworthy and efficient data-driven systems.

Embracing ACID transactions means embracing confidence in your data and that’s a critical step toward scalable, robust applications.

DBMS – Transactions, Deadlocks & Log-Based Recovery

MBEWE CATHERINE 24CB068 — Sun, 05 Oct 2025 19:16:59 +0000

Introduction to Transactions, Deadlocks & Log-Based Recovery in Databases

Databases handle lots of operations all at once, and keeping everything running smoothly is no easy task. That’s why concepts like transactions, deadlocks, and log-based recovery are so important in database management systems (DBMS).

A transaction is a group of actions that happen together as a single unit, ensuring data stays accurate and consistent.

Sometimes, transactions can get stuck waiting for each other, causing a deadlock think of it like two people blocking each other in a hallway.

To keep data safe even if things go wrong, log-based recovery keeps a detailed record of changes so the database can bounce back quickly after crashes or errors.

Understanding these concepts helps make sure your data stays reliable, even when multiple users and complex operations are happening at the same time.

Step 1: Create Table & Insert Data
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

SELECT * FROM Accounts;

Transaction – Atomicity & Rollback

Task: Transfer 500 from Alice to Bob, but rollback before committing.

Deduct 500 from Alice

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

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

Rollback transaction

ROLLBACK;

Check balances

SELECT * FROM Accounts;

Deadlock Simulation (Conceptual)

Session 1 (conceptual)

Lock Alice
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
Do NOT commit

Session 2 (conceptual)
Lock Bob
UPDATE Accounts SET balance = balance - 200 WHERE name = 'Bob';
Do NOT commit

Continuing Session 1
Try updating Bob (held by Session 2)
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

Continuing Session 2
Try updating Alice (held by Session 1)
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Alice';

Log-Based Recovery

Task: Show rollback and explain internal logs (undo/redo).

Update Charlie
UPDATE Accounts SET balance = balance + 300 WHERE name = 'Charlie';

Rollback
ROLLBACK;

Verify balances
SELECT * FROM Accounts;

conclusion
Transactions, deadlocks, and log-based recovery are the backbone of reliable and efficient database management. They work together to keep your data accurate, prevent system freezes, and recover smoothly from unexpected problems. By understanding these key concepts, you can appreciate how databases handle complex operations behind the scenes — ensuring your information is always safe and accessible.

Cursor and Trigger in DBMS

MBEWE CATHERINE 24CB068 — Sun, 05 Oct 2025 18:41:03 +0000

Introduction to Cursors and Triggers in Databases

When working with databases, sometimes you need more control over how data is handled behind the scenes. That’s where cursors and triggers come in handy!

A cursor lets you work with data row-by-row, giving you the ability to process or manipulate individual records one at a time — kind of like flipping through a book page by page.

A trigger is like an automatic helper that responds to certain events in the database, such as when data is added, updated, or deleted. It acts immediately to enforce rules or perform tasks without you having to manually intervene.

Together, cursors and triggers make databases smarter and more responsive, helping to manage complex operations smoothly and efficiently.

Cursor Example – Retrieving and Displaying Employee Records Where the Salary is Greater Than ₹50,000

Step 1: Create Employee Table

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

Step 2: Insert Sample Data

INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (1, 'Cathy', 60000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (2, 'Faith', 45000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (3, 'Lee', 75000);
INSERT INTO Employee (Emp_ID, Emp_Name, Salary) VALUES (4, 'Grace', 50000);

Step 3: Create and Process Cursor

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

Trigger Example – Executing an AFTER INSERT Operation on the Students Table

Step 1: Create Students Table

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

Step 2: Create Studen_Audit Table

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

Step 3: Create AFTER INSERT Trigger

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

Step 4: Test Trigger

INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (1, 'Cathy', 'Computer Science');
INSERT INTO Students (Student_ID, Student_Name, Course) VALUES (2, 'Grace', 'Electrical Engineering');

Step 5: Verify Audit Table

SELECT * FROM Student_Audit;

Conclusion:-

Cursors provide the capability to process query results one row at a time based on specific conditions, enabling detailed and controlled data handling. Triggers, on the other hand, help in automating actions such as maintaining logs or updating related tables, ensuring that certain tasks are carried out automatically without the need for manual execution.
Together, these features play a vital role in advanced database management by improving efficiency, consistency, and reliability.

Indexing, Hashing & Query Optimization in DBMS

MBEWE CATHERINE 24CB068 — Sun, 05 Oct 2025 18:16:43 +0000

INTRODUCTION
When working with databases, speed and efficiency matter a lot especially when you have tons of data. That’s where indexing, hashing, and query optimization come into play.

Indexing is like the index in a book; it helps the database find information quickly without scanning every single record.

Hashing is a smart way to organize data so you can jump straight to what you need using a unique key.

And query optimization is the brainpower behind the scenes, figuring out the fastest way to get the data you asked for.

Together, these techniques make databases faster and more efficient, saving time and resources whether you’re searching, sorting, or managing large amounts of data.

Creating the Studentss Table

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

Inserting Sample Records

INSERT INTO Students VALUES (101, 'Arjun', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Priya', 'CSBS', 7.8);
INSERT INTO Students VALUES (103, 'Kiran', 'ECE', 9.0);
INSERT INTO Students VALUES (104, 'Anita', 'ME', 8.2);
INSERT INTO Students VALUES (105, 'Vikram', 'CSBS', 8.8);
INSERT INTO Students VALUES (106, 'Ravi', 'ECE', 7.5);
INSERT INTO Students VALUES (107, 'Sneha', 'ME', 8.7);
INSERT INTO Students VALUES (108, 'Nikhil', 'CSBS', 6.9);
INSERT INTO Students VALUES (109, 'Maya', 'ECE', 8.0);
INSERT INTO Students VALUES (110, 'Aditya', 'CSBS', 9.2);
INSERT INTO Students VALUES (111, 'Tanya', 'ME', 7.9);
INSERT INTO Students VALUES (112, 'Rohan', 'CSBS', 8.3);
INSERT INTO Students VALUES (113, 'Divya', 'ECE', 9.1);
INSERT INTO Students VALUES (114, 'Karthik', 'ME', 7.7);
INSERT INTO Students VALUES (115, 'Isha', 'CSBS', 8.6);
INSERT INTO Students VALUES (116, 'Suresh', 'ECE', 8.4);
INSERT INTO Students VALUES (117, 'Meena', 'ME', 8.0);
INSERT INTO Students VALUES (118, 'Aravind', 'CSBS', 7.6);
INSERT INTO Students VALUES (119, 'Pooja', 'ECE', 8.9);
INSERT INTO Students VALUES (120, 'Rahul', 'ME', 8.1);

Creating a B-Tree Index on roll_no

CREATE INDEX idx_roll_no ON Students(roll_no);

Query: Fetch student with roll_no = 117

SELECT * FROM Students WHERE roll_no = 117;

Creating a B+ Tree Index on cgpa

Oracle automatically uses B+ Tree for numeric indexes.

CREATE INDEX idx_cgpa ON Students(cgpa);

Query: Display all students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0;

Query: Retrieve all students from the CSBS department

SELECT * FROM Students WHERE dept = 'CSBS';

summary

Defined the Students table with columns roll_no, name, dept, and cgpa.
Added 20 sample records into the table. Built a B-Tree index on roll_no to enable faster lookups. Created a B+ Tree index on cgpa to improve range query performance. Indexed the dept column to accelerate equality-based searches. Executed queries to test and confirm indexing benefits in performance.
By using indexes, query execution becomes faster and more efficient. Indexing is a vital part of query optimization in DBMS.

Database Normalization

MBEWE CATHERINE 24CB068 — Sun, 05 Oct 2025 17:48:08 +0000

INTRODUCTION

Database normalization is like tidying up your data so everything is neat, organized, and easy to find. It’s a way of designing your database so that there’s no duplicate information, and everything is stored in the right place. This makes your database faster, easier to update, and less likely to run into problems later. Think of it as giving your data a clean, clutter-free home .It involves dividing a large, unstructured table into smaller, well-structured tables and establishing relationships between them using keys.

Step 1: Data Table

Above is 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 person who enrolled.

Step 3: Converting to 1NF

_First Normal Form (1NF) rules:
_
Every column should hold atomic (indivisible) values.
Each record must be unique.

Steps applied:

Divided multivalued attributes into individual rows.
Ensured that each column stores only one value.

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

Step 4: Conversion to 2NF

2NF Rule: Remove partial dependency, ensuring that non-key attributes rely on the entire composite primary key.

Observations:
Student_Name depends only on Student_ID.

Course_Name, Instructor, Instructor_Phone depend only on Course_ID.

Grade (if existed) would depend on both Student_ID + Course_ID.

Solution: Split into three tables:

Students → Student_ID, Student_Name

Courses → Course_ID, Course_Name, Instructor, Instructor_Phone

Enrollments → Student_ID, Course_ID

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

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

CREATE TABLE Enrollments1 (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students1(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses1(CourseID)
);

Step 5: Conversion to 3NF

3NF Rule: Remove transitive dependencies (non-prime attributes must depend only on primary key).

Instructor’s phone depends on Instructor, not Course_ID → we can split Courses into:
Courses → Course_ID, Course_Name, Instructor_ID

Instructors → Instructor_ID, Instructor_Name, Instructor_Phone

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

CREATE TABLE Courses13NF (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(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. Kay', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mia', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Ray', '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
JOIN Student3NF s ON e.StudentID = s.StudentID
JOIN Course3NF c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion:-

Normalization helps achieve reduced data redundancy, improved data integrity simplified maintenance and updates .Following the progression from 1NF → 2NF → 3NF in Oracle SQL enables the creation of efficient designs.

Simple College Student Schema

MBEWE CATHERINE 24CB068 — Sun, 05 Oct 2025 16:49:40 +0000

INTRODUCTION

When it comes to building modern ,high performance applications MongoDB is one of the most popular NoSQL databases used by developers today for it is developer friendly, scalable applications. Unlike traditional relational databases, MongoDB stores data in flexible JSON-like documents, making it easier to work with real-world scenarios.

In this blog, we will walk through CRUD operations (Create, Read, Update, Delete) in MongoDB using a simple example: a college student database. We’ll insert student details, query them, update academic information, and even delete records.

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

OUTCOME:

How to insert multiple documents into a collection

How to read and filter records using queries

How to update documents (single & multiple)

How to delete documents based on conditions

How CRUD fits into real-world development

Setup: Creating a Cluster

Create a free MongoDB Atlas account

Create a cluster and a database called COLLEGESTUDENTS

Inside it, create a collection called STUDENTSDATA

Create (Insert):-

Insert at least 5 student records into the students collection.

We cannot start by inserting 5 student records into our students collection together. We can create each student as separate document.

Code:-

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

{
student_id: "S002",
name: "Lee",
age: 20,
department: "Chemical",
year: 3,
cgpa: 8.5
}

student_id: "S003",
name: "Faith",
age: 20,
department: "CSBS",
year: 2,
cgpa: 7
}

{
student_id: "S004",
name: "Shalom",
age: 19,
department: "Mechatronics",
year: 2,
cgpa: 7.8
}

{
student_id: "S005",
name: "Grace",
age: 22,
department: "CSE",
year: 2,
cgpa: 9.5
}

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","CSBS" })_

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" }

Result:

_Delete all students having CGPA < 7.5

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

Conclusion:-

In this blog, we explored how to perform CRUD operations in MongoDB using a real-world example of a student database. Starting from inserting records, querying based on conditions, updating multiple documents, and finally deleting specific records we’ve covered the foundation of working with MongoDB.

CRUD operations form the building blocks of every application, whether you’re managing users in a website, products in an e-commerce app, or students in a college system.

This step-by-step approach gave me a solid understanding of how a database schema works in real-world academic systems

Thanks to @santhoshnc sir for his mentorship.

College Student & Course Management System

MBEWE CATHERINE 24CB068 — Mon, 25 Aug 2025 16:38:33 +0000

INTRODUCTION

Hi everyone

I’m a college student currently learning about databases, and I recently worked on an assignment where I had to build a Student & Course Management System using SQL. It was a great hands-on way to practice what i have been learning in class like creating tables, inserting data, using joins, writing queries, and even building stored procedures.

In this blog post, I will walk you through everything I did step by step. If you're also learning SQL or working on a similar project, I hope this helps you understand the concepts more clearly.

📘 Assignment Objective

Design and create tables for Students, Courses, Enrollments, and Faculty

Use DDL, DML, constraints, joins, views, and a stored procedure

Demonstrate data manipulation, querying, and basic database programming

While applying what we learned in class to build a real world style database schema.

Step-by-Step Implementation

Creating the Core Tables

I started by creating the base tables:

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

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

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

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

Insight: Creating relationships between tables helped me understand the concept of foreign keys and how they maintain referential integrity.

Inserting Sample Data INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'CATHERINE', 'Computer Science', TO_DATE('2001-05-15', 'YYYY-MM-DD'), 'cathy@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'FAITH', 'Mechanical Engineering', TO_DATE('2000-08-22', 'YYYY-MM-DD'), 'fay@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Charlie Brown', 'Physics', TO_DATE('2002-01-10', 'YYYY-MM-DD'), 'charlie@example.com');

Insight: Practicing DML helped me get more comfortable with date formats and inserting accurate test data.

Altering the Table
ALTER TABLE Students
ADD PhoneNo NUMBER(10);
Defining Constraints
ALTER TABLE Courses
MODIFY Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5);

Insight: Adding constraints felt like writing rules into the database. It prevents errors before they happen.

Using SQL Functions SELECT UPPER(Name) AS UpperCaseName, LENGTH(Email) AS EmailLength FROM Students;

6.Aggregate Functions
SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled FROM Enrollments;

JOIN Operation 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;

Insight: This was the "aha!" moment where everything connected. I saw how normalized tables work together through joins.

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

9.Creating a View
CREATE 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;

Insight: Views make it easy to create simplified representations of complex joins. Perfect for reporting.

Stored Procedure to Update Grades
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;

Insight: Writing procedures was new for me. I learned how SQL can include some logic — not just data manipulation.

what i learnt

How normalization and foreign keys maintain database integrity

Importance of constraints for data validation

Power of joins and how they reflect real-world relationships

Views and procedures make data handling cleaner and more reusable

Final Thoughts

As a student, doing this assignment really helped me understand SQL beyond just the syntax. I got to apply it to a mini real-world use case, and it made concepts like joins, constraints, and stored procedures click.

Image

image

Image