DEV Community: Jerlin vanessa Vincent paul

AWS Tool Starting With J: Amazon Java Corretto

Jerlin vanessa Vincent paul — Sun, 21 Dec 2025 06:46:28 +0000

-> AWS Tool Starting With J: Amazon Corretto

When building Java applications in the cloud, choosing the right Java runtime is critical for security, stability, and cost. Amazon Corretto, an AWS-provided distribution of OpenJDK, is designed to meet these needs in modern Cloud, DevOps, and DevSecOps environments.

-> Service Overview

Amazon Corretto is a free, multi-platform, production-ready OpenJDK distribution maintained by Amazon Web Services. It comes with long-term support (LTS) and regular security updates, making it a reliable choice for running Java applications at scale.

-> Key Features

*Fully compatible with OpenJDK

*Long-term support (LTS) versions

*Regular security patches and performance improvements

*No licensing or subscription costs

*Optimized for AWS workloads

*Available for Linux, Windows, and macOS

-> Where Amazon Corretto Fits in the Cloud & DevOps Lifecycle

*Amazon Corretto plays a key role across the software delivery lifecycle:

*Development: Consistent Java runtime across teams

*CI/CD: Used in build and test pipelines

*Deployment: Runs microservices and backend applications

*Security: Timely patches reduce vulnerabilities

*Operations: Stable performance in production

*It is commonly used with Docker, Kubernetes, and AWS CI/CD services.

-> Programming Language & Access Methods

*Programming Language: Java

*Access Methods:

*Command Line (CLI)

*IDEs (IntelliJ, Eclipse)

*Docker images

*CI/CD tools (CodeBuild, Jenkins)

-> AWS Category / Cloud Domain

*Compute / Programming Runtime

*Developer Tools

-> Pricing Model

*Open source

*Free to use

*No licensing fees (only AWS infrastructure costs apply)

Final Thoughts

Amazon Corretto is a secure, cost-effective, and production-ready Java runtime for cloud-native applications. Backed by AWS with long-term support and frequent security updates, it removes licensing concerns while ensuring reliability—making it a strong choice for Java developers and DevSecOps teams alike.

Thanks to @santhoshnc sir for guiding and motivating us.

DevSecOps Periodic Table: JIRA

Jerlin vanessa Vincent paul — Sun, 21 Dec 2025 06:38:14 +0000

DevSecOps Periodic Table: JIRA

In the DevSecOps periodic table, JIRA represents the planning and tracking element that binds development, security, and operations together. While it doesn’t run code or scan vulnerabilities, it plays a critical role in ensuring visibility, accountability, and continuous collaboration across the software lifecycle.

-> Overview of the Tool

JIRA is a project management and issue-tracking tool developed by Atlassian. It is widely used to manage software development tasks, bugs, operational incidents, and security findings using Agile frameworks such as Scrum and Kanban.

-> Key Features

*Issue and bug tracking

*Scrum and Kanban boards

*Sprint planning and backlog management

*Custom workflows and issue types

*Dashboards and reporting

*Integration with CI/CD and security tools

-> How It Fits into DevOps / DevSecOps

In a DevSecOps pipeline, JIRA acts as the single source of truth for:

*Development tasks

*Operational incidents

*Security vulnerabilities

Security issues discovered by tools like Snyk, Trivy, or SonarQube can be logged and tracked in JIRA, ensuring that security is addressed early and continuously throughout the SDLC.

-> Programming Language

*Backend: Java

*Frontend: JavaScript

*Integration: REST APIs

-> Parent Company of the Tool Atlassian

Open Source or Paid
Not open source
Commercial (Paid) tool

Free tier available for small teams

-> Final Thoughts

JIRA may not scan code or deploy applications, but it is a core element of DevSecOps governance. By enabling structured tracking, transparency, and collaboration, JIRA ensures that development, security, and operations teams move forward together—securely and efficiently.

Thanks to @santhoshnc Sir for his guidance and support and for everything

Database Normalization

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 12:50:44 +0000

Database Normalization:

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.

1️⃣ 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)
);

2️⃣ 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)
);

3️⃣ 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

Special thanks to @santhoshnc for mentoring me on database normalization concepts!

SQL #Oracle #DBMS #DatabaseNormalization #1NF #2NF #3NF #BCNF #4NF #5NF #DataModeling

Simple College Student Schema

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 12:06:37 +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
Schema

Create a cluster and a database called collegeDB

Inside it, create a collection called students

Schema

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: "S003",
name: "Natalia",
age: 18,
department: "ME",
year: 3,
cgpa: 8.7
}

{
student_id: "S004",
name: "Olivia",
age: 22,
department: "ECE",
year: 4,
cgpa: 8.5
}

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

Delete all students having CGPA < 7.5

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

🚀 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 guiding and motivating us.

ACID Properties with SQL Transactions in DBMS

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 08:00:43 +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 inserted

Check the table:

SELECT * FROM Accounts;

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;

ROLLBACK;

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

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 @santhoshnc Sir for his guidance and support and for Everything

dbms #MySql #oracle #transactions #acid #database #learning

Cursor and Trigger in DBMS

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 07:32:33 +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!

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

dbms #sql #oracle #plsql #database #cursors #triggers #programming #assignment #learning

Indexing, Hashing & Query Optimization in DBMS

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 06:55:05 +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.

SQL #Oracle #Indexing #BTree #BPlusTree #QueryOptimization #DBMS #Database

DBMS - Transactions, Deadlocks & Log-Based Recovery

Jerlin vanessa Vincent paul — Sat, 04 Oct 2025 06:25:27 +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.

dbms #oracle #sql #transactions #deadlock #recovery #assignment

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

Jerlin vanessa Vincent paul — Fri, 22 Aug 2025 06:10:02 +0000

I have just completed the database management system for college student and course management system.
Here,is the step by step process :

Creating the Students Table

First, we define a Students table to store basic information about each student.

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

Key Points:

StudentID is the primary key, ensuring uniqueness.

Email has a unique constraint to avoid duplicates.

Name is marked as NOT NULL to ensure every student has a name.

Creating the Courses Table

Next, we define the Courses table that lists all courses offered.

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

Later, we’ll add a check constraint on the Credits column to ensure values stay within a valid range.

Creating the Enrollments Table

This table maps students to the courses they’re enrolled in.

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

Key Points:

StudentID and CourseID are foreign keys linking to their respective tables.

Grade stores the letter grade (like A+, B, etc.).

Inserting Student Records

Now let’s add three students from different departments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'VINCENT', 'Computer Science', TO_DATE('2004-03-15', 'YYYY-MM-DD'), 'vincent.cs@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'SYLVIA', 'Electrical Engineering', TO_DATE('2005-11-22', 'YYYY-MM-DD'), 'sylvia.ee@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'TOM HOLLAND', 'Mechanical Engineering', TO_DATE('2006-07-09', 'YYYY-MM-DD'), 'tomholland.mech@example.com');

Adding a New Column: PhoneNo

We realized students need a phone number field. Here’s how to add it:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

Adding a Check Constraint on Course Credits

We only want courses to have between 1 and 5 credits:

ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

This helps maintain data integrity by preventing invalid credit values.

Inserting Courses

Let’s add three sample courses into our Courses table:

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'CYBER SECURITY', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'BEEE', 4);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'FLUID MECHANICS', 5);

Committing Changes

In Oracle, changes aren’t permanent until you commit:

COMMIT;

Querying the Database a. Display Student Names in Uppercase and Email Length SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;

This returns names in uppercase along with the length of each email.

b. View All Courses
SELECT CourseID, CourseName, Credits
FROM Courses;

Useful for displaying a course catalog.

c. View All Students
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Shows all students, including the newly added PhoneNo field.

In this simple Oracle SQL project, we:

Created relational tables with primary and foreign keys

Added constraints for data validation

Populated the tables with sample data

Executed basic queries to retrieve and format information

This project forms a foundational structure for any educational management system. From here, you can explore adding features like attendance, scheduling, and advanced reports.

🙌 Thank you @santhoshnc sir for guiding me!!