Forem: Haresh B

AWS Honeycode: Your No-Code App Builder

Haresh B — Thu, 18 Dec 2025 17:26:50 +0000

AWS Honeycode: Build Apps Without Coding

AWS Honeycode is a fully managed service that allows users to create web and mobile applications without writing code. It empowers teams to build custom apps for project management, customer tracking, inventory management, and more, all while using familiar spreadsheet-like interfaces.

Overview of AWS Honeycode

AWS Honeycode enables organizations to design and deploy applications quickly without relying on traditional software development. It focuses on low-code/no-code app development, making it accessible to business users and developers alike. Users can automate workflows, manage tasks, and collaborate effectively within their teams.

Key Features

No-code App Builder: Design applications using a visual interface similar to spreadsheets.
Automation: Automate notifications, approvals, and workflow processes.
Team Collaboration: Share apps securely with team members and manage permissions.
Templates: Start quickly with pre-built templates for common business workflows.
Mobile and Web Access: Apps run on both browsers and mobile devices.
Integration: Connect with other AWS services and external tools via APIs.

AWS Category / Cloud Domain

Category: Application Development / Low-Code Platforms
Cloud Domain: Developer Tools / Productivity Applications

Where It Fits in Cloud / DevOps Lifecycle

AWS Honeycode fits into the development and operations stages of cloud and DevOps:

Development: Quickly build apps to automate business processes without writing code.
Testing & Deployment: Test app functionality in Honeycode and deploy instantly to team members.
Operations: Automate notifications, approvals, and task tracking to streamline workflows.
Collaboration & Optimization: Continuously improve apps based on user feedback and changing requirements.

AWS Honeycode Workflow

Programming Language / Access Methods

No programming required – built for non-developers.
APIs & AWS SDKs: Advanced users can connect Honeycode apps to other AWS services.
Web Interface: Design and use apps in a browser.
Mobile App: Access and use Honeycode apps on iOS and Android devices.

Pricing Model

Free Tier: Up to 20 users and 2,500 rows per workbook for learning and small teams.
Paid Plans: Based on the number of users and workbook size, starting at a low monthly fee per user.
Pay-as-you-go: Only pay for what you use, making it cost-effective for small and large teams alike.

Conclusion

AWS Honeycode is a powerful tool for organizations that want to build custom applications quickly without coding. By integrating workflow automation, team collaboration, and cloud-based accessibility, Honeycode helps teams innovate faster and manage processes efficiently. For students learning AWS cloud-driven DevOps, exploring Honeycode can provide insights into modern low-code application development.

I would like to express my sincere thanks to @santhoshnc Sir for his valuable guidance and support.

HashiCorp Vault: A Core Security Tool in DevSecOps

Haresh B — Thu, 18 Dec 2025 16:40:16 +0000

HashiCorp Vault: A Core Security Tool in DevSecOps

As organizations increasingly adopt cloud computing and DevOps practices, securing sensitive data has become a major challenge. Traditional security approaches are no longer sufficient in fast-paced development environments. This challenge has led to the adoption of DevSecOps, which integrates security into every phase of the DevOps lifecycle. One important tool that supports this approach is HashiCorp Vault.

Overview of HashiCorp Vault

HashiCorp Vault is a secrets management and data protection tool designed to securely store, manage, and control access to sensitive information such as passwords, API keys, tokens, and certificates. Instead of hard-coding secrets into application code or configuration files, Vault provides a centralized and secure solution for managing them.

Key Features

Secure storage and encryption of sensitive data
Dynamic secrets generation with limited lifetime
Role-Based Access Control (RBAC)
Audit logging to track access
Automatic secret rotation
Integration with CI/CD pipelines and cloud platforms

Role in DevOps and DevSecOps

In a DevOps environment, HashiCorp Vault enables secure automation by allowing applications and services to retrieve secrets at runtime without exposing them in source code.

In a DevSecOps workflow, Vault supports the shift-left security model by embedding security controls early in the development process. It reduces the risk of credential leakage and strengthens security across continuous integration and continuous deployment pipelines.

Programming Languages Supported

HashiCorp Vault provides APIs and SDKs that support multiple programming languages, including:

Go
Python
Java
JavaScript
Ruby

This allows seamless integration with different applications and platforms.

Parent Company

HashiCorp Vault is developed and maintained by HashiCorp, a company known for its cloud infrastructure and security automation tools such as Terraform, Consul, and Packer.

Licensing Model

Vault Community Edition is open source and free to use.
Vault Enterprise Edition is a paid version that offers advanced security features and enterprise-level support.

Conclusion

HashiCorp Vault is a fundamental tool in the DevSecOps ecosystem. By securely managing secrets and integrating seamlessly with DevOps pipelines, it helps organizations build secure, scalable, and reliable cloud-native applications. For students learning AWS cloud-driven DevOps, understanding HashiCorp Vault is essential.

I would like to express my sincere thanks to @santhoshnc Sir for his valuable guidance and support.

CRUD Operations in MongoDB: A Beginner's Guide for College Students

Haresh B — Wed, 08 Oct 2025 13:47:05 +0000

Introduction
As a college student, understanding database management is a fundamental skill. MongoDB, a popular NoSQL database, offers a flexible and scalable way to store data. In this guide, we’ll walk through the essential CRUD (Create, Read, Update, Delete) operations using a simple students collection schema. This is perfect for anyone getting started with MongoDB.

Schema (Collection: students)
Each document in our students collection will have the following structure:

JSON

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

Create (Insert) Operations The first step is to populate our database with data. We will insert five student records into the students collection.

Query to Insert 5 Student Records:

JavaScript

db.students.insertMany([
{ "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
{ "student_id": "S002", "name": "Priya", "age": 21, "department": "IT", "year": 3, "cgpa": 8.5 },
{ "student_id": "S003", "name": "Arjun", "age": 19, "department": "CSBS", "year": 1, "cgpa": 7.2 },
{ "student_id": "S004", "name": "Deepa", "age": 22, "department": "ECE", "year": 4, "cgpa": 9.1 },
{ "student_id": "S005", "name": "Rahul", "age": 20, "department": "IT", "year": 2, "cgpa": 7.8 }
])

Read (Query) Operations Querying is the most frequent operation. The find() method allows us to retrieve data based on specific criteria.

Query to Display all Student Records:

JavaScript

db.students.find({})

Since you have successfully performed the Read operations and have the corresponding screenshots, I will provide you with the blog post, incorporating your completed work. I will also provide the queries for the Update and Delete sections so you can complete the assignment.

CRUD Operations in MongoDB: A Beginner's Guide for College Students
Introduction
As a college student, understanding database management is a fundamental skill. MongoDB, a popular NoSQL database, offers a flexible and scalable way to store data. In this guide, we’ll walk through the essential CRUD (Create, Read, Update, Delete) operations using a simple students collection schema. This is perfect for anyone getting started with MongoDB.

Schema (Collection: students)
Each document in our students collection will have the following structure:

JSON

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

Create (Insert) Operations The first step is to populate our database with data. We will insert five student records into the students collection.

Query to Insert 5 Student Records:

JavaScript

Read (Query) Operations Querying is the most frequent operation. The find() method allows us to retrieve data based on specific criteria.

Query to Display all Student Records:

JavaScript

db.students.find({})
Execution Result:
([Screenshot of the query results showing all 5 documents, as in your screenshot "Screenshot 2025-10-08 171200.png" or similar])

Query to Find all Students with CGPA > 8:

JavaScript

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

Query to Find Students from the Computer Science Department:

JavaScript

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

Update Operations Updating allows us to modify existing documents. We will use updateOne() and updateMany() for targeted and bulk updates, respectively.

Query to Update a Specific Student's CGPA:

JavaScript

db.students.updateOne(
{ "student_id": "S001" },
{ "$set": { "cgpa": 9.2 } }
)

Query to Increase the Year of Study for all 3rd-Year Students:

JavaScript

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

Delete Operations Finally, we perform deletion to remove documents from the collection.

Query to Delete One Student Record by student_id:

JavaScript

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

Query to Delete all Students having CGPA < 7.5:

JavaScript

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

Conclusion
Mastering these CRUD operations is crucial for anyone working with databases. MongoDB's intuitive, JSON-like syntax makes these operations straightforward and powerful. This exercise provides a solid foundation for more complex database tasks, helping you build robust applications in the future.

Mastering Database Normalization with Oracle Live SQL: A Practical Guide

Haresh B — Wed, 08 Oct 2025 08:07:21 +0000

Introduction
Have you ever wondered why your database tables seem a bit messy? That's where database normalization comes in. It's a structured approach to designing a database to reduce data redundancy and improve data integrity. In this guide, we'll walk through the process of normalizing a simple table to 1NF, 2NF, and 3NF using Oracle Live SQL.

We'll start with a problematic base table, identify its flaws (anomalies), and then normalize it step-by-step. Let's get started!

The Problematic Base Table

Our starting point is a single table that contains all the data about students, courses, and instructors. As you'll see, this table is highly redundant.

This table is a great example of what not to do. It suffers from several anomalies:

Update Anomaly: If Dr. Kumar's phone number changes, we have to update multiple rows.

Insertion Anomaly: We can't add a new course without a student.

Deletion Anomaly: Deleting the last student in a course also deletes the course information itself.

Normalizing to 1NF (First Normal Form) A table is in 1NF if it contains only atomic values (single values in each cell) and a unique key. Our base table is already in 1NF, as there are no repeating groups. We just need to define a composite primary key to ensure each row is unique.

Here's the SQL statement for our 1NF table:

SQL

CREATE TABLE Courses_1NF (
StudentID VARCHAR2(5) NOT NULL,
StudentName VARCHAR2(50),
CourseID VARCHAR2(5) NOT NULL,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15),
PRIMARY KEY (StudentID, CourseID)
);

Normalizing to 2NF (Second Normal Form) To be in 2NF, a table must be in 1NF, and all non-key attributes must be fully dependent on the entire primary key. In our 1NF table, the primary key is (StudentID, CourseID). However, StudentName only depends on StudentID, and CourseName, Instructor, and InstructorPhone only depend on CourseID.

To fix this, we'll split our table into three smaller, more focused tables: Students_2NF, Courses_Instructors_2NF, and Enrollments_2NF.

SQL

CREATE TABLE Students_2NF (
StudentID VARCHAR2(5) PRIMARY KEY,
StudentName VARCHAR2(50)
);

CREATE TABLE Courses_Instructors_2NF (
CourseID VARCHAR2(5) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments_2NF (
StudentID VARCHAR2(5),
CourseID VARCHAR2(5),
PRIMARY KEY (StudentID, CourseID)
);

Normalizing to 3NF (Third Normal Form) 3NF requires that a table is in 2NF and has no transitive dependencies. A transitive dependency happens when a non-key attribute depends on another non-key attribute. In our Courses_Instructors_2NF table, InstructorPhone depends on Instructor, not directly on the CourseID primary key.

To achieve 3NF, we'll separate the instructor details into their own table. This gives us four normalized tables: Students, Instructors, Courses, and Enrollments.

SQL

CREATE TABLE Students (
StudentID VARCHAR2(5) PRIMARY KEY,
StudentName VARCHAR2(50)
);

CREATE TABLE Instructors (
Instructor VARCHAR2(50) PRIMARY KEY,
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Courses (
CourseID VARCHAR2(5) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
FOREIGN KEY (Instructor) REFERENCES Instructors(Instructor)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(5),
CourseID VARCHAR2(5),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Inserting and Querying the Normalized Data Now that our schema is ready, we can insert the sample data and write a query to retrieve information from our new, well-structured tables.

SQL

-- Insert into Instructors first
INSERT INTO Instructors (Instructor, InstructorPhone) VALUES ('Dr. Kumar', '9876543210');
INSERT INTO Instructors (Instructor, InstructorPhone) VALUES ('Dr. Mehta', '9123456780');
INSERT INTO Instructors (Instructor, InstructorPhone) VALUES ('Dr. Rao', '9988776655');

-- Insert into Students
INSERT INTO Students (StudentID, StudentName) VALUES ('S01', 'Arjun');
INSERT INTO Students (StudentID, StudentName) VALUES ('S02', 'Priya');
INSERT INTO Students (StudentID, StudentName) VALUES ('S03', 'Kiran');

-- Insert into Courses
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('C101', 'DBMS', 'Dr. Kumar');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('C102', 'Data Mining', 'Dr. Mehta');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('C103', 'AI', 'Dr. Rao');

-- Insert into Enrollments
INSERT INTO Enrollments (StudentID, CourseID) VALUES ('S01', 'C101');
INSERT INTO Enrollments (StudentID, CourseID) VALUES ('S01', 'C102');
INSERT INTO Enrollments (StudentID, CourseID) VALUES ('S02', 'C101');
INSERT INTO Enrollments (StudentID, CourseID) VALUES ('S03', 'C103');

Finally, here's the query to list all students, their courses, and instructor names using JOIN statements. This is much more efficient and reliable than querying the single, denormalized table.

SQL

SELECT
S.StudentName,
C.CourseName,
C.Instructor
FROM
Students S
JOIN
Enrollments E ON S.StudentID = E.StudentID
JOIN
Courses C ON E.CourseID = C.CourseID;

Conclusion
By normalizing our database, we've eliminated data redundancy and the risk of anomalies. Our database is now more efficient to update, easier to maintain, and ensures data consistency. This is the foundation of good database design!

Understanding ACID Properties with Oracle Live SQL

Haresh B — Wed, 08 Oct 2025 06:44:43 +0000

INTRODUCTION
ACID properties are a set of principles that guarantee the reliability of database transactions. They ensure that data remains consistent and accurate, even when multiple operations are happening at the same time or when a system failure occurs. For this assignment, I used Oracle Live SQL to demonstrate these fundamental properties.

What are ACID Properties?
Atomicity: An "all-or-nothing" principle. A transaction either completes entirely or doesn't happen at all. If any part fails, the entire transaction is rolled back.

Consistency: A transaction brings the database from one valid state to another. It ensures that all data integrity rules (like constraints) are maintained.

Isolation: Concurrent transactions don't interfere with each other. Each transaction appears to be the only one running, even if many are happening simultaneously.

Durability: Once a transaction is committed, its changes are permanent and will survive a system crash or restart.

To begin, I created a simple Accounts table and inserted some sample data.

SQL

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

INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 1000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Bob', 500);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Charlie', 2000);

SELECT * FROM Accounts;

Atomicity: The All-or-Nothing Transfer
To demonstrate atomicity, I simulated a failed money transfer from Alice to Bob. The transaction involves two steps: decreasing Alice's balance and increasing Bob's. I used a ROLLBACK to simulate a failure after the first step.

SQL

-- Before the transaction
SELECT * FROM Accounts WHERE acc_no IN (101, 102);

-- Start a transaction
SET TRANSACTION READ WRITE;

-- Step 1: Transfer $200 from Alice (101)
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 101;

-- Intentionally rollback
ROLLBACK;

-- After rollback, the balance returns to the original state
SELECT * FROM Accounts WHERE acc_no IN (101, 102);

As the screenshot shows, even though the UPDATE statement was executed, the ROLLBACK command ensured that no partial changes were saved. The balances for both Alice and Bob remained at their original values, proving the all-or-nothing nature of the transaction.

Consistency: Enforcing Data Rules
Consistency ensures that a transaction maintains the integrity of the database. I demonstrated this by adding a CHECK constraint to the Accounts table to prevent negative balances.

SQL

-- Drop the existing table and recreate it with a CHECK constraint
DROP TABLE Accounts;
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
);

-- Reinsert valid data
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 1000);
-- ... (rest of the valid inserts)

-- Attempt to insert a record with a negative balance
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -100);
The INSERT statement with a negative balance was immediately rejected by the database. The screenshot below shows the specific error message, confirming that the CHECK constraint prevented the database from entering an invalid state.

Isolation: Concurrent Transactions
Isolation ensures that concurrent transactions don't affect each other. I used two separate Oracle Live SQL tabs (representing two different sessions) to demonstrate this.

Session 1 (The Updater):

In the first session, I started a transaction to update Alice's balance but did not commit it.

SQL

-- Session 1
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 101;
-- The transaction is still open...
Session 2 (The Reader):

In the second session, I ran a SELECT query on Alice's account. The database returned the old balance, as the changes from Session 1 were not yet committed and therefore not visible to other sessions.

SQL

-- Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
-- The result will show the original balance (1000)
Finally, I went back to Session 1 and committed the transaction. I then re-ran the query in Session 2, and this time, the new, updated balance was visible.

SQL

-- Back in Session 1
COMMIT;

-- Back in Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
-- The result will now show the new balance (900)
This demonstrates that the uncommitted changes from one transaction were isolated and invisible to another until the COMMIT command was executed.

Durability: Permanent Changes
Durability ensures that once a transaction is committed, the changes are permanent. I tested this by committing an update and then logging out and back into Oracle Live SQL (simulating a database restart) to check if the data persisted.

SQL

-- Update Charlie's account and commit
UPDATE Accounts SET balance = balance + 50 WHERE acc_no = 103;
COMMIT;
After committing the change, I started a new, fresh session. A simple SELECT query confirmed that the new balance for Charlie was still there, proving that the committed change was durable.

SQL

-- In a new session
SELECT * FROM Accounts WHERE acc_no = 103;

CONCLUSION
This exercise with Oracle Live SQL provided a practical demonstration of the ACID properties. It highlighted how these principles are fundamental to ensuring data integrity, reliability, and security in any database system, from a simple application to a large-scale enterprise system.

Mastering DBMS: A Practical Guide to Indexing, Hashing, and Query Optimization

Haresh B — Wed, 08 Oct 2025 04:45:10 +0000

Introduction
In the world of database management, performance is key. One of the most effective ways to optimize query performance is by using indexes. This blog post will walk you through a practical assignment on creating and using different types of indexes in Oracle Live SQL. We'll cover B-Tree, B+ Tree, and Hash indexes, demonstrating how they speed up data retrieval.

1.Setting Up Our Database: The Students Table
First, let's create a table to work with. We'll design a simple Students table with fields for roll number, name, department, and CGPA. We’ll also insert at least 20 sample records to have enough data for our queries.

Here's the SQL code:

SQL

-- Create the Students table
CREATE TABLE Students (
roll_no NUMBER(5) PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(10),
cgpa NUMBER(4, 2)
);

-- Insert at least 20 sample records
-- Student: Akhil
INSERT INTO Students VALUES (110, 'Akhil', 'CSBS', 9.15);
INSERT INTO Students VALUES (101, 'Aarav', 'CSE', 8.50);
INSERT INTO Students VALUES (102, 'Bhavana', 'ECE', 7.80);
INSERT INTO Students VALUES (103, 'Chetan', 'IT', 9.00);
INSERT INTO Students VALUES (104, 'Divya', 'MECH', 7.25);
INSERT INTO Students VALUES (105, 'Esha', 'CIVIL', 8.90);
INSERT INTO Students VALUES (106, 'Farhan', 'CSE', 6.50);
INSERT INTO Students VALUES (107, 'Gauri', 'ECE', 8.10);
INSERT INTO Students VALUES (108, 'Harish', 'IT', 7.55);
INSERT INTO Students VALUES (109, 'Isha', 'MECH', 9.40);
INSERT INTO Students VALUES (111, 'Jatin', 'CIVIL', 6.80);
INSERT INTO Students VALUES (112, 'Kavya', 'CSE', 8.30);
INSERT INTO Students VALUES (113, 'Lakshya', 'ECE', 7.95);
INSERT INTO Students VALUES (114, 'Meera', 'IT', 9.20);
INSERT INTO Students VALUES (115, 'Naveen', 'MECH', 8.75);
INSERT INTO Students VALUES (116, 'Om', 'CIVIL', 7.00);
INSERT INTO Students VALUES (117, 'Pooja', 'CSE', 9.50);
INSERT INTO Students VALUES (118, 'Qadir', 'ECE', 6.90);
INSERT INTO Students VALUES (119, 'Riya', 'IT', 8.65);
INSERT INTO Students VALUES (120, 'Samar', 'MECH', 7.10);
INSERT INTO Students VALUES (121, 'Tanya', 'CIVIL', 8.45);
INSERT INTO Students VALUES (122, 'Uday', 'CSE', 9.05);

-- Commit the changes
COMMIT;

2.B-Tree Indexing on roll_no
A B-Tree index is a data structure that helps locate a specific row in a table quickly. When you declare a column as a PRIMARY KEY, Oracle automatically creates a B-Tree index on it. This is perfect for single-value lookups, like searching for a student by their roll_no.

Let's fetch the details for the student with roll_no = 110 (Akhil). The B-Tree index on roll_no makes this a very fast operation.

SQL

-- Execute a query to fetch the details of a student with roll_no = 110
SELECT * FROM Students WHERE roll_no = 110;

3.B+ Tree Index for cgpa (Range Queries)
While Oracle's standard index is a B-Tree, it's designed to efficiently handle range queries as well, much like a B+ Tree. The index on cgpa will allow us to quickly find all students whose grades fall within a certain range without scanning the entire table.

We'll create an index on the cgpa column and then run a query to find all students with a CGPA greater than 8.0.

SQL

-- Create a B-Tree index on the cgpa column of the Students table.
CREATE INDEX idx_students_cgpa ON Students(cgpa);

-- Write a query to display all students with cgpa > 8.0.
SELECT * FROM Students WHERE cgpa > 8.0;

4.Hash Index on dept (Equality Queries)
A Hash index is ideal for equality searches, where you are looking for an exact match. In Oracle, a B-Tree index is also highly effective for this, but the concept of a hash index is worth understanding for its direct-addressing nature. For our purpose, we'll create a standard index on the dept column, which Oracle's query optimizer will use efficiently for our exact match query.

Let's find all students from the 'CSBS' department.

SQL

-- Create an index on the dept column
CREATE INDEX idx_students_dept ON Students(dept);

-- Run a query to retrieve all students from the 'CSBS' department.
SELECT * FROM Students WHERE dept = 'CSBS';

Conclusion
As you can see, using indexes is crucial for database performance. Whether you're doing a single-row lookup, a range search, or an exact-match query, a well-placed index can drastically reduce the time it takes to get your results. By understanding and implementing these indexing strategies, you can ensure your database queries are as optimized as possible.

Solving a DBMS Assignment on Oracle Live SQL: Transactions, Deadlocks & Log-Based Recovery

Haresh B — Tue, 07 Oct 2025 18:30:35 +0000

For a recent database management systems (DBMS) assignment, I had to demonstrate key concepts like atomicity, deadlocks, and log-based recovery using Oracle Live SQL. The assignment used a simple Accounts table to illustrate these principles. Here’s a breakdown of the tasks and how I solved them, along with the SQL queries I used.

The Setup: A Simple Accounts Table First, I created the Accounts table and populated it with some initial data. This is the starting point for all the tasks.

Key Point: The CREATE TABLE and INSERT queries set up the initial schema and data. COMMIT makes the initial state permanent.

SQL

-- Create the Accounts table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

-- Insert the initial data
INSERT INTO Accounts VALUES (1, 'Alice', 1000);
INSERT INTO Accounts VALUES (2, 'Bob', 1500);
INSERT INTO Accounts VALUES (3, 'Charlie', 2000);

-- Commit the initial data to make it permanent
COMMIT;

-- View the initial state
SELECT * FROM Accounts;

Transaction – Atomicity & Rollback The goal of this task was to demonstrate atomicity, the "all or nothing" property of a transaction. I needed to perform a money transfer and then use ROLLBACK to undo it completely, proving that the transfer never partially completed.

Key Point: A transaction starts implicitly with the first UPDATE. ROLLBACK reverts both UPDATE statements simultaneously, showcasing atomicity.

Queries:

SQL

-- Perform a transfer from Alice to Bob
UPDATE Accounts
SET balance = balance - 500
WHERE acc_no = 1;
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 2;

-- Check the balances. They are updated, but not committed yet.
SELECT * FROM Accounts;

-- Rollback the transaction
ROLLBACK;

-- Check the balances again. They are back to the original values.
SELECT * FROM Accounts;

Log-Based Recovery This task aimed to show that a ROLLBACK operation relies on the database’s logging system. In Oracle, this is handled by the undo tablespace, which stores the "before" image of the data. When I issue a ROLLBACK, the system uses this information to restore the original state.

Key Point: The database automatically uses the undo tablespace for recovery. ROLLBACK relies on this logged information to revert changes.

Queries:

SQL

-- Update Charlie's balance (uncommitted)
UPDATE Accounts
SET balance = 2500
WHERE acc_no = 3;

-- Check the updated balance
SELECT * FROM Accounts;

-- Rollback the change
ROLLBACK;

-- Check the balance again. It has been reverted.
SELECT * FROM Accounts;

Deadlock Simulation This was the most challenging part of the assignment because it cannot be performed with a single script in Oracle Live SQL. A deadlock occurs when two or more transactions are waiting for a resource locked by another. This requires at least two separate sessions (two distinct connections to the database).

Key Point: Deadlocks can't be simulated in a single-session environment like Oracle Live SQL. They require two separate sessions to create a circular dependency of locked resources. The DBMS detects this and automatically terminates one transaction.

How it works conceptually:

In Session 1, I would lock Alice’s account.

In Session 2, I would lock Bob’s account.

Session 1 would then try to update Bob’s account, and would wait.

Session 2 would then try to update Alice’s account, causing a circular wait and a deadlock.

This assignment was an excellent way to learn about the fundamental principles of transaction management that are critical for any database professional. I hope this helps anyone else working on a similar assignment!

Mastering SQL: A Practical Guide to Cursors and Triggers in Oracle

Haresh B — Tue, 07 Oct 2025 17:21:51 +0000

Database management is a core skill for any developer, and two of the most powerful tools in SQL are cursors and triggers. While they serve different purposes, both allow for fine-grained control and automation within your database. In this post, we'll walk through a practical example of each using Oracle Live SQL.

Part 1: Cursors – Processing a Result Set Row by Row
A cursor is a database object that enables you to iterate through the records of a query's result set one at a time. This is particularly useful for performing actions that can't be done with a single SQL statement, like conditional processing or calling a function for each row.

The Task: We want to create a cursor that finds and displays the names of all employees whose salary is greater than $50,000.

Step 1: Create the Employee Table
First, we need a table to hold our employee data. Here's the SQL to create the Employee table and populate it with some sample data.

SQL

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR2(100),
Salary NUMBER(10, 2)
);

INSERT INTO Employee VALUES (1, 'Ravi', 65000.00);
INSERT INTO Employee VALUES (2, 'Ram', 48000.00);
INSERT INTO Employee VALUES (3, 'Nivas', 72000.00);
INSERT INTO Employee VALUES (4, 'Guhan', 51000.00);

COMMIT;

Step 2: Use a PL/SQL Block to Process the Cursor
Now, we'll write a PL/SQL block. We'll declare a cursor that selects employees with a salary over $50,000, open it, loop through the results, and print the employee's name and salary.

SQL

SET SERVEROUTPUT ON;

DECLARE
v_employee_name Employee.EmployeeName%TYPE;
v_salary Employee.Salary%TYPE;

CURSOR c_employee IS
SELECT EmployeeName, Salary
FROM Employee
WHERE Salary > 50000;

BEGIN
OPEN c_employee;

LOOP
    FETCH c_employee INTO v_employee_name, v_salary;

    EXIT WHEN c_employee%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_name || ' | Salary: ' || v_salary);
END LOOP;

CLOSE c_employee;

END;
/
As you can see from the output, the code successfully identified and printed the employees who meet the salary condition.

Part 2: Triggers – Automating Database Events
A trigger is a stored procedure that automatically runs when a specific event occurs on a table, such as an INSERT, UPDATE, or DELETE. Triggers are perfect for tasks like maintaining audit logs or enforcing business rules.

The Task: We want to create an AFTER INSERT trigger on our Students table. Whenever a new student is added, a log entry should be automatically inserted into a separate Student_Audit table.

Step 1: Create the Students and Student_Audit Tables
We need two tables: Students to store the student data and Student_Audit to store the log entries.

SQL

-- Create the Students table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR2(100),
RegistrationDate DATE
);

-- Create the Student_Audit table
CREATE TABLE Student_Audit (
AuditID INT GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
StudentID INT,
StudentName VARCHAR2(100),
ActionType VARCHAR2(20),
ActionDate TIMESTAMP
);

Step 2: Create the AFTER INSERT Trigger
The following trigger uses the special :NEW virtual record to access the values of the row that was just inserted and automatically logs them into the Student_Audit table.

SQL

CREATE OR REPLACE TRIGGER trg_Student_Audit
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (
StudentID,
StudentName,
ActionType,
ActionDate
) VALUES (
:NEW.StudentID,
:NEW.StudentName,
'INSERT',
SYSTIMESTAMP
);
END;
/
Step 3: Test the Trigger by Inserting Data
Now, let's insert a few student records. For each INSERT statement we run, the trigger will automatically fire.

SQL

-- Insert the new student records
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (101, 'Ravi', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (102, 'Ram', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (103, 'Nivas', SYSDATE);
INSERT INTO Students (StudentID, StudentName, RegistrationDate) VALUES (104, 'Guhan', SYSDATE);

Finally, we'll query the Student_Audit table to confirm that all four INSERT actions were logged.

SQL

SELECT * FROM Student_Audit;
The output clearly shows that the trigger worked perfectly. Each new student was logged with their StudentID, StudentName, and the time of the insertion.

Conclusion
As you can see, cursors and triggers are incredibly useful for handling specific data-processing tasks and automating operations. They give you the power to go beyond standard SQL statements and build a more robust and responsive database system.

College Student & Course Management System

Haresh B — Wed, 20 Aug 2025 17:32:41 +0000

Introduction

This blog covers the implementation of a simple College Student & Course Management System using SQL on Oracle LiveSQL. It demonstrates key database concepts such as table creation, data insertion, constraint addition, queries with functions and aggregates, joins, views, and stored procedures.

The use case focuses on managing students, courses, enrollments, and faculty members with related operations.

Database Schema

The database contains four main tables: Students, Courses, Enrollments, and Faculty.

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

Data Insertion

Sample data was inserted into these tables to represent students, courses, and their enrollments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-04-15', 'YYYY-MM-DD'), 'alice.johnson@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob Smith', 'Mathematics', TO_DATE('2001-11-23', 'YYYY-MM-DD'), 'bob.smith@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Cathy Brown', 'Physics', TO_DATE('2003-07-02', 'YYYY-MM-DD'), 'cathy.brown@example.com');

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (101, 'Databases', 4);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (102, 'Algorithms', 3);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (103, 'Physics', 5);

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (2, 2, 102, 'B+');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (3, 3, 103, 'A-');

Table Alterations and Constraints

Added a new column PhoneNo to Students and a CHECK constraint on the Credits column of Courses:

ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

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

SQL Queries with Functions and Aggregates

Listing student names in uppercase and length of their emails:

SELECT UPPER(Name) AS UppercaseName, LENGTH(Email) AS EmailLength FROM Students;

Calculating average course credits and counting enrolled students:

SELECT
(SELECT AVG(Credits) FROM Courses) AS AvgCredits,
(SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalStudentsEnrolled
FROM dual;

Sample result:

UppercaseName	EmailLength
ALICE JOHNSON	25
BOB SMITH	21
CATHY BROWN	23

AvgCredits	TotalStudentsEnrolled
4.0	3

JOIN Queries

Joining Students, Enrollments, and Courses to show which student is enrolled in which course along with grades:

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;

Sample result:

StudentName	CourseName	Grade
Alice Johnson	Databases	A
Bob Smith	Algorithms	B+
Cathy Brown	Physics	A-

GROUP BY and HAVING Clause

Counting students in each department and filtering departments with more than 2 students:

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

Views

Created a view to simplify student-course-grade lookup:

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

Stored Procedure

Procedure to update a student's grade in enrollments:

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

Summary

This assignment reinforced understanding of:

Creating and managing SQL database schemas
Writing data manipulation queries
Using SQL functions and aggregate operations
Performing joins to combine related data
Creating views and stored procedures to enhance SQL capabilities

Feel free to try the full script on Oracle LiveSQL to see these operations in action.