Forem: Iniko

AWS IoT Core Explained for DevOps & Cloud Engineers

Iniko — Mon, 22 Dec 2025 05:45:53 +0000

As IoT adoption grows rapidly, managing millions (or even billions) of connected devices securely and at scale becomes a major challenge. AWS IoT Core solves this problem by acting as a reliable bridge between devices and the cloud.
In this post, let’s understand AWS IoT Core from a DevOps and cloud lifecycle perspective.
🔹 What is AWS IoT Core?
AWS IoT Core is a fully managed cloud service that allows IoT devices—such as sensors, smart appliances, vehicles, and industrial machines—to securely connect and exchange data with cloud applications and other devices.
AWS handles the heavy lifting of authentication, message routing, scalability, and availability, so teams can focus on building solutions instead of managing infrastructure.
✅ Best for: Connecting billions of IoT devices and processing trillions of messages reliably.
🔹 Key Features
🔐 Secure Device Communication
Mutual authentication using X.509 certificates
Encrypted communication with TLS
📡 Message Broker
Supports MQTT, MQTT over WebSockets, and HTTPS
Designed for low latency and high throughput
⚙️ Rules Engine
Filter, transform, and route data to AWS services like:
AWS Lambda
Amazon S3
DynamoDB
Amazon Kinesis
🪞 Device Shadow
Maintains a virtual copy of device state
Enables applications to interact with devices even when they are offline
🔗 AWS Service Integration
Works seamlessly with analytics, monitoring, and AI/ML services
🔹 AWS Category / Cloud Domain
Cloud Domain: Internet of Things (IoT), Cloud Computing
AWS Category: IoT Services, Application Integration
🔹 Pricing Model
AWS IoT Core follows a pay-as-you-go pricing model:
Charged per million messages
Charges for device connectivity
Pricing for Rules Engine executions
Pricing for Device Shadow operations
✔ No upfront cost
✔ No minimum commitment
✔ Scales automatically with usage
🔹 Common Use Cases
Smart Home Systems
Industrial IoT (IIoT)
Fleet & Asset Tracking
Smart Cities
Healthcare Monitoring
Energy & Utilities Management
🔹 Final Thoughts
AWS IoT Core is a powerful and scalable service for building secure, cloud-connected IoT solutions. From smart homes to industrial automation, it fits naturally into modern DevOps and cloud architectures, enabling teams to process real-time data without worrying about infrastructure management.
If you're building IoT systems on AWS, IoT Core is the foundation you should start with.

DevSecOps Periodic Table — Tool “Fe”

Iniko — Mon, 22 Dec 2025 04:54:16 +0000

🧠 Overview of SonarQube
In the DevSecOps Periodic Table, SonarQube stands as a key security & quality tool that helps teams continuously inspect code quality and identify vulnerabilities early in the software delivery pipeline. It analyzes source code to detect bugs, code smells, and security flaws — giving developers actionable insights before code moves further downstream.�
Digital.ai
⭐ Key Features
Static Code Analysis: Automatically scans code for bugs and vulnerabilities without executing it.
Quality Gates: Lets teams enforce standards before merging or releasing code.
Multi-Language Support: Works with languages such as Java, C#, JavaScript, Python, Go, and more.
Security Reports & Trends: Offers dashboards showing issue trends over time.
Integration with CI/CD: Embeds easily in pipelines (Jenkins, GitHub Actions, GitLab CI, etc.).
These features make SonarQube indispensable in DevSecOps, where shifting left on security and code quality is essential.
🔧 How It Fits into DevOps/DevSecOps
SonarQube supports the DevSecOps philosophy of integrating security into development, not treating it as an afterthought. By performing early static analysis as part of build and CI/CD workflows, it reduces the risk of security defects and technical debt reaching production.
In classic DevOps you focus on fast builds and deployments — but without built-in checks, quality and security can slip. SonarQube ensures that speed doesn’t come at the cost of safety. Its reports can block deployments if critical issues are found, enforcing security policies and quality standards automatically.
🧾 Programming Language
SonarQube is language-agnostic — it analyzes many programming languages rather than being tied to one. The analyzer itself is typically written in Java, but it supports plugins for virtually all major languages used in modern DevSecOps stacks.
🏢 Parent Company
SonarQube is developed by SonarSource, a Swiss company founded in 2008 focused on continuous code quality and security tools.
💸 Open Source or Paid?
Open Source: SonarQube has a free Community Edition that covers basic static analysis and quality checks.
Paid (Commercial Editions): Developer, Enterprise, and Data Center Editions offer advanced rules, broader language support, deeper security testing (SAST), and better enterprise governance features.
💡 Most teams start with the free version and upgrade as their DevSecOps maturity grows.

Indexing, Hashing & Query Optimization in DBMS

Iniko — Wed, 08 Oct 2025 09:37:15 +0000

Databases don’t just store data — they must retrieve it efficiently. As table sizes grow into the thousands, millions, or more, naive queries (scanning every row) become too slow. That’s where indexing and hashing come in — they act like shortcuts into your data. Combined with smart query design, they dramatically speed up lookups.

Let’s break down how these concepts work, when to use them, and what trade-offs to watch out for.

What Is Indexing?

An index is a data structure built over a table (or one or more columns) to help the database find rows faster. Rather than scanning every row, the DBMS can use the index to jump directly to relevant records.

It’s analogous to the index at the back of a book — it doesn’t contain the full content, but pointers to where things live.

Types of Indexes

Primary Index: Automatically associated with the table’s primary key.

Secondary (or Secondary) Index: Manually created index on a non-key column to speed queries.

Clustering Index: Controls the physical order of data in the table (rows are stored in index order).

Non-Clustering Index: Separate structure; the table’s rows aren’t physically stored in index order, but index entries point to them.

Most relational databases use B-Tree or B+-Tree variants for indexes:

B-Tree: Internal nodes and leaf nodes both can store keys and pointers.

B+-Tree: Internal nodes only store keys (no direct data pointers); actual data pointers (or record references) live in the leaf nodes. Leaf nodes are often linked to each other, improving range scans (you can traverse leaf nodes sequentially).

Because of this design, B+-Tree indexes are especially good for range queries (e.g. BETWEEN, <, >) and sorted access.

Hash Indexing (or Hash-Based Indexing)

A hash index uses a hash function: a key is passed through the hash function, and it maps to a bucket (or slot). Records with that key go into that bucket (or chain). Lookups compute the hash and go directly to the appropriate bucket.

Strength: Excellent for equality searches (column = value).

Weakness: Poor for range queries or sorted operations (you can’t easily traverse “next bucket” in order).

So when your queries often ask “find rows matching this exact key?”, a hash index shines. But for queries like “find rows between A and B”, a B+-Tree style index is far better.

Examples: Students Table

Suppose you have:

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

You might do:

-- Default B-Tree index (often automatic for primary key)
CREATE INDEX idx_roll_btree ON Students (roll_no);

-- If your database supports hash indexing:
CREATE INDEX idx_roll_hash ON Students USING HASH (roll_no);

Then:

SELECT * FROM Students WHERE roll_no = 50;
→ both B-Tree and hash index can perform well, but hash may be faster for exact match.

SELECT * FROM Students WHERE roll_no BETWEEN 10 AND 100;
→ B-Tree / B+-Tree index is much better suited, because entries are stored in sorted order.

Choosing Between B-Tree and Hash Index
Query Type Best Choice Reason
Exact match (=) Hash or B-Tree Hash is optimal for equality lookups
Range queries (BETWEEN, <, >) B-Tree / B+-Tree Maintains sorted order for efficient traversal
Sequential scans / ordered output B+-Tree Leaf nodes are linked, enabling fast in-order scanning

Other Considerations / Trade-Offs

Storage Overhead: Every index consumes additional disk/memory space.

Insert / Update / Delete Cost: More indexes = slower writes, because you must update all relevant indexes.

Low Cardinality Columns: Don’t index columns with very few distinct values (e.g. gender, status) — the benefit is minimal.

Index Fragmentation / Maintenance: Over time, indexes may become less efficient and need rebuilding, reorganization, or defragmentation.

Query Optimization & Index Strategy

Indexing is one of the most powerful levers for speeding up queries, but it must go hand-in-hand with how you write your SQL and how the database executes it.

Use the EXPLAIN (or EXPLAIN ANALYZE / EXPLAIN PLAN) command to see how your database is executing queries. It shows whether an index is being used, how many rows are being scanned, and more.

Avoid SELECT * — fetch only the columns you need (less I/O).

Use composite indexes (multi-column) when your queries often filter on multiple columns together (e.g. WHERE col1 = X AND col2 = Y).

Keep your indexes lean and tuned. Don’t over-index — too many indexes slows down writes and consumes space.

Final Thoughts

Indexing and hashing are essential tools for building performant database systems. They are not silver bullets but, when used smartly, can transform a slow system into a responsive one.

Start by analyzing your most frequent, slowest queries. Add or adjust indexes based on patterns. Measure impact with EXPLAIN and query profiling. Over time you'll develop instincts for which columns — and which kinds of indexing (B-Tree, hash, composite) — will yield the best performance.

Transactions, Deadlocks & Log-Based Recovery in DBMS

Iniko — Wed, 08 Oct 2025 09:34:16 +0000

Transactions, Deadlocks & Log-Based Recovery in DBMS

When working with databases, you must ensure that operations remain safe, consistent, and recoverable. In this article, I’ll explain:

What a transaction is, and how atomicity and rollback work

How deadlocks happen and how to handle them

What log-based recovery means and how it ensures durability

Transactions: Atomicity & Rollback

A transaction is a unit of work comprising one or more operations (such as UPDATE, INSERT, DELETE). The key property is atomicity — the transaction must complete entirely or not at all.

If something inside the transaction fails (due to constraint violation, runtime error, etc.), none of the changes should persist.

The database can rollback the transaction, undoing all partial changes.

Example Workflow

Start transaction

Run some updates (e.g. deduct money, add to another account)

If all succeed → commit

If any step fails → rollback

This ensures that you never end up in some halfway state with only a few operations applied.

Deadlocks

A deadlock occurs when two (or more) transactions hold locks on resources that the other needs, and neither can proceed or release its lock. Each waits for the other — a cycle of waiting.

Deadlock example scenario

Transaction T1 locks row A, then requests a lock on row B

Transaction T2 locks row B, then requests a lock on row A

They both wait indefinitely for each other → deadlock

In many DBMS (like MySQL, PostgreSQL), a deadlock is detected automatically. The system will abort one of the transactions with an error like:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

To handle deadlocks:

Use shorter transactions (keep locks held for minimal time)

Access objects in a consistent order in different transactions

Retry aborted transactions

Log-Based Recovery

To ensure durability and recoverability after crashes or failures, modern DBMS maintain transaction logs (also called write-ahead logs). These logs record each change (before it’s applied) so the system can:

Undo incomplete or uncommitted changes

Redo committed changes during recovery

How it works (simplified)

Before modifying data pages, write a record of the intended change to the log.

Apply the change to the data pages.

On commit, mark in the log that the transaction is committed.

On recovery (after crash), scan the log:

Undo all uncommitted transactions

Redo the committed ones

This ensures that even if the system crashes after a commit, the change is persisted. Likewise, half-done transactions are rolled back.

Summary & Takeaways

A transaction groups multiple operations into a single unit: either it all succeeds or it all fails (atomicity).

Deadlocks arise when transactions block each other; DBMS detect them and abort one transaction, but application logic should minimize them.

Log-based recovery (using a transaction log) is vital for ensuring durability and allowing rollback or redo during system recovery.

ACID Properties in DBMS

Iniko — Wed, 08 Oct 2025 09:27:57 +0000

ACID Properties in DBMS: Ensuring Reliable Database Transactions

Databases are the backbone of nearly every application—from banking software to e-commerce platforms. When multiple operations happen concurrently or failures occur, how do we maintain correct, reliable data? That’s where the ACID properties come in: Atomicity, Consistency, Isolation, and Durability.

What is ACID?

ACID is a set of properties that guarantee that database transactions are processed reliably, even in the presence of concurrency, crashes, or errors. Let’s go through each:

Atomicity

A transaction must either complete in full or not at all.
If any part of the transaction fails, the database rolls back to its prior state, ensuring partial changes aren’t committed.

Example: Transferring money between two accounts

Debit from Account A

Credit to Account B
If the debit succeeds but the credit fails, the entire operation is rolled back so that no money is lost or duplicated.

Consistency

The database must move from one valid state to another, preserving all defined rules, constraints, triggers, etc.
After a transaction, all integrity rules must still be valid.

Example: If there’s a rule that a student's age must be ≥ 18, a transaction that attempts to insert a student with age 16 should not succeed. The transaction must either be fully valid or rejected.

Isolation

Concurrent transactions should not interfere with each other’s intermediate steps.
One transaction should not see the partial, uncommitted results of another.

Problems prevented:

Dirty reads: reading data from another transaction before it's committed

Non-repeatable reads: data changes between reads in the same transaction

Phantom reads: new rows appear in subsequent reads

Different isolation levels (e.g. Read Uncommitted, Read Committed, Repeatable Read, Serializable) help manage tradeoffs between performance and strictness.

Durability

Once a transaction commits, its changes must persist—even if the system crashes right after.
This means they should be stored in non-volatile memory (e.g. on disk), with logging, backups, and recovery mechanisms ensuring durability.

How It All Fits Together (Simplified Example)

Imagine you have a Loans table in your database. You want to update loan records within a transaction:

Atomicity: If updating one loan fails, revert all updates done in the transaction.

Consistency: Ensure that the updated interest rate falls within allowed business bounds or constraints.

Isolation: Prevent other concurrent transactions from interfering (e.g. they shouldn’t see half-updated data).

Durability: After the commit, the new values survive system crashes, reboots, or hardware failures.

Why ACID Matters

Data integrity under concurrency: Multiple users or processes can act on data without corrupting state.

Fault tolerance: Partial failures don’t leave the system in an inconsistent state.

Trust: For systems handling critical data (finance, medical records, etc.), ACID properties are foundational to reliability.

SQL Cursors & Triggers

Iniko — Wed, 08 Oct 2025 07:16:17 +0000

SQL Cursors & Triggers: What They Are and When to Use Them

In SQL, you often run set-based operations (e.g. SELECT, UPDATE, DELETE) over many rows at once. But what if you need to treat each row individually? Or automatically react to changes in your data? That’s where Cursors and Triggers enter the picture.

Let’s dive into each, see examples, and explore when to use one over the other.

Cursors: Row-by-Row Processing

A cursor is like a pointer that lets you step through the result set of a query, one row at a time. In scenarios where you need procedural logic or conditional steps per row, a cursor can be very useful.

Example

Suppose you have a table of employees and you want to process those whose salary is greater than 50,000:

DECLARE @empId INT, @salary DECIMAL(10,2)

DECLARE emp_cursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees
WHERE Salary > 50000

OPEN emp_cursor;

FETCH NEXT FROM emp_cursor INTO @empId, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
-- Example logic: give a bonus or update something
PRINT 'Employee ' + CAST(@empId AS VARCHAR) + ' has salary ' + CAST(@salary AS VARCHAR)

FETCH NEXT FROM emp_cursor INTO @empId, @salary;
END

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Here, the cursor:

Declares which result set to iterate over.

Opens it.

Fetches rows one by one.

Lets you run custom logic per row.

Finally closes and deallocates the resource.

When to Use Cursors

Use a cursor when:

You need to perform row-level operations that can’t be done easily in a set operation.

Your logic depends on the value from the previous row, or you need sequential access.

You must mix complex procedural logic with SQL data.

However, be cautious: cursors can be slower and more resource-intensive compared to pure set-based queries, especially on large datasets.

Triggers: Automate Actions on Data Changes

A trigger is a database object that defines automatic behavior when certain events occur — e.g. INSERT, UPDATE, or DELETE. Once you set it up, it “listens” for those events and runs code accordingly.

Example: AFTER INSERT Trigger for Auditing

Let’s say you want to audit insertions into a Students table by copying new rows into a separate Student_Audit table:

-- Audit table
CREATE TABLE Student_Audit (
StudentID INT,
Name VARCHAR(100),
InsertedOn DATETIME DEFAULT GETDATE()
);

-- Trigger definition
CREATE TRIGGER trg_AfterInsert_Students
ON Students
AFTER INSERT
AS
BEGIN
INSERT INTO Student_Audit (StudentID, Name)
SELECT i.StudentID, i.Name
FROM inserted AS i;
END;

Now, whenever a new student record is inserted into Students, the trigger will fire automatically and log the inserted data into Student_Audit.

When to Use Triggers

Triggers are suited when:

You want automatic logging, auditing, or enforcing rules at the database layer.

You want to centralize certain logic so that it can’t be bypassed (e.g., always enforce referential integrity or soft deletes).

You need to react to changes in data without requiring the application to do so.

Be careful, though: triggers can introduce complexity and unexpected side-effects if not managed carefully (e.g., cascading triggers, nested triggers, or impacting performance).

Summary & Best Practices Feature Purpose Use Case Example Cursor Process rows one at a time Complex per-row logic, sequential ops Trigger Automate reaction to data changes Auditing, enforcing business rules

Key takeaways:

Use cursors when you absolutely need fine-grained control on each record.

Use triggers when you want automatic reactions at the database level.

Prefer set-based SQL operations when possible, for performance and readability.

Monitor and test performance, especially for triggers or cursor loops on large tables.

Database Normalization Made Simple: From 1NF to 3NF

Iniko — Wed, 08 Oct 2025 03:33:25 +0000

🧠 Database Normalization Made Simple: From 1NF to 3NF

If you’ve ever managed a database where updating one piece of information breaks everything — congratulations, you’ve experienced data anomalies.
The solution? Normalization — a systematic way to structure your database so it’s efficient, consistent, and free from redundancy.

In this post, we’ll walk through the journey from 1NF → 2NF → 3NF, breaking down what each normal form means and why it matters.

💡 What Is Database Normalization?

Normalization is the process of organizing data in a relational database to minimize redundancy and dependency issues.
In simpler terms, it ensures your data is clean and consistent.

Why normalize?

✅ Avoid duplicate data

✅ Prevent update, insert, and delete anomalies

✅ Maintain data integrity

✅ Make querying and maintenance easier

⚠️ The Problem With Unnormalized Data

Imagine you start with one giant table storing students, their courses, and instructors — all in the same place:

StudentID StudentName Course Instructor Phone
1 Alice DBMS Prof. Raj 555-1001
2 Bob DBMS Prof. Raj 555-1001
1 Alice Networks Prof. Kim 555-2002

At first, it looks fine. But soon, issues appear:

Insertion anomaly: You can’t add a new student until they enroll in a course.

Update anomaly: Changing Prof. Raj’s phone number means updating it in multiple rows.

Deletion anomaly: Removing Alice’s last course also deletes all her info.

Let’s fix this, step by step.

🧩 First Normal Form (1NF): Atomic Data

Rule: Each cell should hold only one value — no lists, no repeating groups.

Before 1NF:
Student Courses
Alice DBMS, Networks
After 1NF:
Student Course
Alice DBMS
Alice Networks

Now every value is atomic, and rows are unique. Congratulations, you’re in 1NF!

⚙️ Second Normal Form (2NF): Full Dependency on the Key

A table is in 2NF if:

It’s already in 1NF, and

Every non-key attribute depends on the whole primary key, not just part of it.

This matters when your table has a composite key (like StudentID + CourseID).

If StudentName depends only on StudentID, but not on CourseID, that’s a partial dependency — and it breaks 2NF.

Solution:

Split the data into separate tables:

Students → (StudentID, StudentName)

Courses → (CourseID, CourseName)

Enrollments → (StudentID, CourseID)

Now, every non-key attribute depends on its full key.

🔁 Third Normal Form (3NF): No Transitive Dependencies

A table is in 3NF if:

It’s already in 2NF, and

Non-key attributes depend only on the key, not on other non-key attributes.

For example, suppose your Courses table looks like this:

CourseID CourseName Instructor Phone

Here, Phone depends on Instructor, not directly on CourseID. That’s a transitive dependency.

Fix:

Break it into two tables:

Courses → (CourseID, CourseName, InstructorID)

Instructors → (InstructorID, InstructorName, Phone)

Now your database is in 3NF, clean and dependency-free.

🏗️ Final Normalized Design (Up to 3NF)

Here’s what the structure looks like now:

Table Columns
Students StudentID, StudentName
Courses CourseID, CourseName, InstructorID
Instructors InstructorID, InstructorName, Phone
StudentCourses StudentID, CourseID

✅ No redundant data
✅ No update anomalies
✅ Easy to query and maintain

🧭 Key Takeaways

1NF: Make data atomic — no repeating groups.

2NF: Remove partial dependencies on composite keys.

3NF: Remove transitive dependencies between non-key attributes.

Bonus: Real-world systems sometimes denormalize for performance, but always start with a normalized design.

🧱 Wrapping Up

Normalization might sound theoretical, but it’s one of the most practical ways to make your databases scalable and reliable.
Whether you’re designing your first app or refactoring legacy data, 1NF → 3NF is your foundation for a healthy schema.

If you found this helpful, share it with someone who’s still fighting messy tables!
Or drop a comment if you’d like a follow-up on BCNF and higher normal forms.

CRUD Operations in MongoDB for Beginners

Iniko — Tue, 07 Oct 2025 08:41:45 +0000

CRUD Operations in MongoDB for Beginners

mongodb #database #tutorial #beginners

MongoDB is a widely used NoSQL database that offers flexibility and scalability for modern applications. Unlike traditional SQL databases, MongoDB stores data as JSON-like documents, making it perfect for handling dynamic and complex data structures.

In this article, we’ll explore the CRUD operations — Create, Read, Update, and Delete — in MongoDB through a simple student database example. You’ll learn how to insert, retrieve, modify, and remove records while getting hands-on with MongoDB Atlas, the cloud-based version of MongoDB.

🎯 Learning Outcomes

By the end of this tutorial, you’ll understand how to:

Insert one or multiple documents into a collection

Retrieve and filter data using queries

Update single or multiple documents

Delete documents based on specific conditions

Apply CRUD logic in real-world development

⚙ Setup: Creating Your MongoDB Cluster

Create a free account on MongoDB Atlas

Set up a cluster and a database named collegeDB

Inside it, create a collection called STUDENTS

Once your setup is ready, let’s dive into the CRUD operations.

🟢 CREATE (INSERT)

We’ll begin by inserting student data into our STUDENTS collection. Each record represents a student as a separate document.

{
"student_id": "S001",
"name": "Harsh",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
}
{
"student_id": "S002",
"name": "ini",
"age": 21,
"department": "CSBS",
"year": 3,
"cgpa": 8.5
}
{
"student_id": "S003",
"name": "madh",
"age": 20,
"department": "MI",
"year": 3,
"cgpa": 8
}
{
"student_id": "S005",
"name": "nidh",
"age": 20,
"department": "CIVIL",
"year": 2,
"cgpa": 8.8
}
{
"student_id": "S006",
"name": "rohi",
"age": 22,
"department": "CSE",
"year": 3,
"cgpa": 9.2
}

🔍 READ (QUERY)

Let’s fetch data from our collection based on certain conditions.

Find students with CGPA greater than 8:

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

Find students belonging to the Computer Science department:

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

🛠 UPDATE

We can update student details such as CGPA or department.
For example, to update a specific student’s CGPA:

db.students.updateOne(
{ student_id: "S005" },
{ $set: { cgpa: 9.0 } }
)

❌ DELETE

To remove a student record from the database, use:

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

🧾 RESULT

You’ll now see that the inserted, queried, updated, and deleted data reflect directly in your MongoDB Atlas Dashboard — making it easy to visualize every operation.

🧠 CONCLUSION

We’ve successfully explored CRUD operations in MongoDB using a practical student database. From inserting and querying to updating and deleting, you now have a clear understanding of how to manage data in MongoDB effectively.

These CRUD fundamentals are the backbone of most applications — whether managing users, products, or student records.

A special thanks to Mr. @santhoshnc for guiding and inspiring us to explore new technologies and keep learning! 🚀

STUDENT MANAGEMENT SYSTEM

Iniko — Fri, 22 Aug 2025 09:42:29 +0000

🚀 Creating SQL Queries in Oracle LiveSQL for a DBMS Assignment

In this blog, I’ll walk you through how I created and executed SQL queries for my DBMS Assignment using Oracle LiveSQL. This will cover everything from table creation to running queries and verifying results with screenshots.

🔑 Step 1: Setting up the Tables

The assignment required creating three core tables: Students, Courses, and Enrollments. I logged into Oracle LiveSQL and used the SQL Worksheet.

Here’s an example for the Enrollments table:

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

✅ After running the command, Oracle confirmed that the table was created successfully.

✍️ Step 2: Inserting Data into Students

Next, I inserted sample data into the Students table. This helps later when running queries like SELECT, JOIN, and GROUP BY.

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice', 'CSE', DATE '2003-05-12', 'alice@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob', 'ECE', DATE '2002-11-23', 'bob@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Charlie', 'MECH', DATE '2004-02-01', 'charlie@example.com');

Each INSERT executed successfully, and I verified it by checking the Script Output.

🛠 Step 3: Altering the Students Table

The assignment asked to add a PhoneNo column. I used:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

✅ The column was added successfully.

🔍 Step 4: Running Queries

Now for the fun part! I ran multiple queries to meet assignment requirements.

a) Select with Functions
SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
FROM Students;

This returned student names in uppercase and the length of their email IDs.

b) Join Operation

To display students with their enrolled courses and 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 e.CourseID = c.CourseID;

At this stage, since no enrollment data was added, the result was empty. (In practice, I would insert course and enrollment data first.)

📸 Step 5: Screenshots for Submission

Throughout the process, I took screenshots of:

Table creation

Data insertion

Alter table

Function queries

Join queries

These were required as proof of execution for the assignment.

🎯 Conclusion

Using Oracle LiveSQL made it simple to:

Create and manage tables

Insert and modify data

Run queries and view results instantly

Collect screenshots as evidence for submission

This workflow not only completed my DBMS assignment but also strengthened my SQL skills with hands-on practice. 🚀