DEV Community: Abirami Prabhakar

LazyLecture 🦥: Because Making Study Notes Shouldn't Be a Whole Study Session

Abirami Prabhakar — Thu, 22 Oct 2026 09:39:55 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 3: Slow and Steady
#devchallenge #hf26challenge #opensource #ai

What We Built

We've all had that moment when we sit down to study, open a huge PDF, and somehow spend the next 30 minutes deciding what to study instead of actually studying.

Summarising chapters, picking out important concepts, making flashcards, creating practice questions... the preparation itself becomes another task on the to-do list.

So we built LazyLecture 🦥, a local AI study assistant that turns lecture notes into a ready-to-use revision pack.

The idea was pretty simple: spend less time preparing to study and more time actually studying.

Drop in a text-based PDF or TXT file, choose how much revision you're aiming for, and LazyLecture generates study material based on your notes.

One tool, different study moods

Not every study session needs to become a full academic comeback.

Quick Study: A concise summary and exactly five key concepts. No flashcards, no quizzes, just a starting point.
5-minute pack: A short summary and a small set of concepts for a quick review.
15-minute pack: More detailed revision material, with flashcards and a practice quiz.
30-minute pack: A larger pack with additional concepts, flashcards, and practice questions.

You can flip through flashcards, reveal answers, take quizzes, check your score and explanations, copy your revision notes, and download the entire pack as Markdown.

The session lengths control how much material gets generated. They aren't promises that the AI will finish generating everything in five minutes. Even artificial intelligence hasn't negotiated that kind of contract with time yet.

And yes, there's a sleepy sloth and a lavender-themed interface. If the whole point is to make studying feel a little less overwhelming, the interface shouldn't feel like another assignment.

How It Works

LazyLecture runs on Qwen2.5 1.5B through Ollama, so the AI inference happens locally instead of relying on a hosted AI API.

Here's the flow:

Upload a text-based PDF or TXT file.
The FastAPI backend extracts and prepares the text.
The selected study format determines the prompt and output limits.
The local model generates structured study material.
Pydantic validates the response before the frontend displays it.

The frontend is built with React, TypeScript, Vite, and CSS. PyMuPDF handles PDF text extraction, while HTTPX connects the backend to Ollama.

We also added tests for the backend and frontend, because an AI-generated study pack is considerably less useful if the application falls apart while trying to display it.

Why Quick Study is the default

One thing we paid attention to was keeping the workload manageable.

A small local model doesn't need to generate an entire textbook's worth of content every time someone wants to revise a single topic. Quick Study uses a smaller output budget and makes one inference request without automatic retries.

For longer documents, the backend selects excerpts before sending text to the model. This helps keep the input manageable, but it can leave out important information. The application warns users when this happens.

We also avoided fake progress percentages. The interface shows elapsed time and a waiting indicator instead of pretending we know exactly how much work the model has completed.

Small decisions, but they make the experience more honest.

Demo

Here's a look at LazyLecture in action.

1. Start with your notes

Upload a text-based PDF or TXT file and choose your study format.

2. Get a quick revision summary

Start with a concise summary and five key concepts instead of trying to digest everything at once.

3. Revise with flashcards

Full study packs include interactive cards that let you reveal answers and work through the material at your own pace.

4. Test yourself

Take a practice quiz, check your score, review the explanations, and save your study pack for later.

Generated content should always be checked against the original notes. LazyLecture helps with revision; it doesn't guarantee factual accuracy or predict exam questions.

The Open-Source AI Part

We wanted the model to be part of the actual product, not just an API call hidden behind a nice interface.

Using an open-weight model with Ollama means users can generate their study material locally after setup, without needing a paid cloud AI API or an account with an external AI provider.

That also means lecture notes can stay on the user's machine during the normal local workflow, rather than being sent to a hosted AI service for inference.

There's something useful about being able to inspect the prompts, change the model, experiment with output limits, and modify the validation logic yourself. The AI isn't an untouchable black box that the application depends on someone else to operate.

We haven't verified fully disconnected operation yet, so that's still something we'd like to test properly.

Things We Learned Along the Way

Building this wasn't just about getting a model to return a summary.

We had to think about how much text to send to a small model, how to handle malformed JSON, how to make different study formats genuinely useful, and how to communicate waiting time without inventing progress.

We also learned that valid output isn't necessarily correct output. Pydantic can validate the structure of a response, but it can't guarantee that every explanation or flashcard accurately reflects the source material.

There are still things we'd like to improve:

Better source citations so users can trace generated content back to their notes.
Stronger grounding checks to reduce hallucinations.
OCR support for scanned PDFs.
Better handling of long documents without missing useful content.
Cancellable generation for longer requests.

There's plenty left to explore, which is kind of the fun part of building with open-source AI.

Built for Slow and Steady

For us, Slow and Steady meant making the first step of studying easier, not trying to automate the entire learning process.

LazyLecture doesn't replace understanding a topic or putting in the work. It just removes some of the repetitive preparation that can make getting started harder than it needs to be.

Sometimes, a manageable five-minute revision session is better than another perfectly organised study plan that never gets used.

And sometimes, the sloth has a point.

Team Credits

LazyLecture is a collaborative project built with my friend @pranitha_prakash_6c8f7ad7.

We worked on bringing together local AI inference, structured study-pack generation, and an interface that makes revision feel a little less intimidating.

Project repository: LazyLecture on GitHub

The project is open source under the MIT License. Contributions, feedback, and ideas for improving the study experience are welcome.

Built with React, FastAPI, Ollama, and a small model doing its best to help us survive our notes.

🦥

FixFlow: Turning Messy Maintenance Complaints into Actionable Tickets with Open-Source AI

Abirami Prabhakar — Sun, 18 Oct 2026 17:23:15 +0000

FixFlow: Turning Messy Maintenance Complaints into Actionable Tickets with Open-Source AI

This is a submission for the "Hacktoberfest Open-Source AI Challenge Week 2: Side Hustle" (https://dev.to/challenges/hacktoberfest-week2-2026-10-12).

What I Built

FixFlow is an AI-powered maintenance request manager designed for small hostels, PG accommodations, and property managers.

Residents often report problems informally: a leaking tap, a broken light, a faulty fan, or a water supply issue. These complaints can get lost in chats, lack important details, or remain unresolved because nobody knows who is responsible.

FixFlow uses the open-weight Gemma model to transform natural-language complaints into structured maintenance tickets. It extracts the issue category, summary, location, missing information, and suggested priority. When essential details are missing, it can ask follow-up questions instead of making assumptions.

Managers can use a dashboard to track requests, assign staff, update statuses, identify possible duplicate tickets, and monitor maintenance activity.

The goal is simple: make maintenance easier to report, easier to manage, and harder to forget.

Who could benefit? Small hostel owners, PG operators, student accommodation managers, and property maintenance teams that need a lightweight alternative to complicated property management systems.

Screenshot

Code

GitHub repository: https://github.com/abirami-prabhakar/flowfix

The project is designed around a simple Python and Streamlit application, with SQLite for persistence and Gemma through Ollama for local inference.

How I Built It

I chose a deliberately lightweight architecture:

Gemma + Ollama: Extracts structured information from unstructured maintenance complaints using an open-weight model running locally.
Python: Handles the application logic, input processing, validation, and ticket workflow.
Streamlit: Provides the resident request form and manager dashboard.
SQLite: Stores tickets and their status history.
Pydantic: Validates AI-generated structured output before it is accepted by the application.
Sentry: Supports error monitoring and performance tracing, where configured.
Render: A potential deployment target, subject to the requirements of running Ollama and providing persistent storage.

The AI is not treated as an unquestionable decision-maker. Its output must be validated, missing details must remain visible, and potentially urgent complaints should be flagged for human review.

I also designed the workflow around predictable failure handling. Malformed model responses, inference failures, and duplicate-looking requests should not silently produce unreliable records. A deterministic demo mode can make testing possible without requiring a live model for every test.

Why Does Open Innovation Matter?

Open innovation makes the core AI functionality more accessible and gives developers control over how models run and how data moves through an application.

With Gemma running locally through Ollama, FixFlow can process complaints without sending their contents to a hosted AI inference API. This is particularly useful for accommodation providers handling room information and potentially sensitive resident reports.

It also makes experimentation easier. Developers can change the model, inspect its behavior, adjust prompts, and test structured extraction against realistic complaints without being locked into one proprietary inference service.

Open-weight models do not automatically guarantee privacy or reliability. Those still depend on how the application is deployed, how data is stored, and how the model's output is validated. But they give smaller teams more control over those decisions.

That is what makes this approach interesting to me: useful AI should not require every small organization to build its workflow around a closed API.

Prize Categories

Open-source AI / Gemma, where eligible under the challenge's official category rules.

🤝 Team Credits

FixFlow is a collaborative project built with my friend https://dev.to/pranitha_prakash_6c8f7ad7 (@pranitha_prakash_6c8f7ad7).

Together, we explored how AI can transform everyday maintenance complaints into structured, actionable maintenance tickets using open-source AI tools.

🔗 Project Repository: https://github.com/abirami-prabhakar/flowfix

RideCircle: Finding Someone to Ride With Shouldn’t Be the Hard Part

Abirami Prabhakar — Sun, 11 Oct 2026 17:50:19 +0000

This is a submission for the Hacktoberfest Open-Source AI Challenge Week 1: Touch Grass.

What I Built

Sometimes the hardest part of going for a ride happens before you even get on the bike.

Where should you go? Is there anyone nearby who wants to join? Will their idea of an “easy ride” leave you struggling to keep up?

I built RideCircle to help answer those questions.

It’s a cycling discovery app where you can find nearby riders with compatible preferences, explore destinations and routes, and organize group rides. I started with Chennai, using real destinations such as Marina Beach and Semmozhi Poonga.

You can set your pace, experience level, preferred ride distance, availability, and interests. RideCircle uses those preferences to help you find people you could comfortably ride with—and explains why a match appears.

You can also:

Explore the map: browse destinations, cyclists, routes, and events.
Search your area: find results within the map area you’re viewing.
Organize a ride: choose a meeting point and set a participant limit.
Join a group: mark yourself interested or attending, and see who’s coming.
Control your visibility: keep your profile private until you choose to make it discoverable.

The app is for someone who wants company on a weekend ride, a beginner looking for a relaxed group, or a cyclist who wants somewhere different to explore.

Cycling apps already exist. My focus here is the decision before the ride: finding a place, a compatible person, and a time that works.

The goal is to make planning short enough that you actually leave the house.

Screenshots

RideCircle currently runs locally with Docker.

The main flow takes you through creating a profile, exploring the map, finding compatible cyclists, and organizing a group ride.

The demo includes clearly labeled fictional riders and sample events so the discovery experience can be explored without needing an existing community. The destinations are real, and the bundled cycling route includes its data source.

To run it yourself, follow the environment setup in the repository README, then run:

docker compose up -d --build

Open http://localhost:5173.

The README also includes instructions for enabling local Gemma inference.

Code

The source code is available on GitHub:

abirami-prabhakar / Ridecircle

RideCircle

A local MVP for finding compatible cyclists, exploring real destinations, and organizing group rides. React + TypeScript, FastAPI, PostgreSQL/PostGIS, and optional local Gemma inference through Ollama.

Start the app

Install Docker Desktop with its Linux engine enabled. From this directory:

Copy-Item .env.example .env
# Replace JWT_SECRET with a random secret of at least 32 characters.
docker compose up -d --build

Open http://localhost:5173. Create your own account, set your preferences, then explore. API documentation: http://localhost:8011/docs. PostGIS is bound to localhost port 54329. Services are local only. To stop them, run docker compose down; the database volume survives. Do not use down -v unless you intend to erase your local database.

The seed contains three sourced Chennai destinations, a real provider-calculated “Garden to coast” bicycle route, four explicitly fictional sample cyclists, and four sample events. Sample people and events are labeled and can be hidden…

View on GitHub

Explore the RideCircle repository →

The project contains the frontend, backend, database migrations, seed data, tests, and Docker configuration.

I’ve documented the local setup, where the map and route data come from, and the parts that are still outside the scope of this prototype.

How I Built It

RideCircle uses:

Technology	Its role
React + TypeScript	Application interface
Leaflet	Interactive map
FastAPI	Backend API
PostgreSQL + PostGIS	Data storage and geographic searches
Gemma 3 through Ollama	Local AI search integration
Docker Compose	Local application setup

Turning a request into search preferences

The AI integration addresses a small but useful problem: turning a cycling request into search preferences.

For example:

“Find beginner cyclists who enjoy relaxed rides on Sunday mornings.”

The Gemma integration asks the model to return structured preferences, such as experience level, pace, day, and time. The backend validates that output before using it to search the database.

The model’s job stops at interpreting the request. Location filtering, compatibility checks, privacy rules, and event capacity are handled by the application.

That boundary matters. An invented cyclist or destination would make the app frustrating to use. Search results need to come from records that actually exist.

Keeping keyword mode transparent

The project also includes a clearly labeled keyword mode for running the app before downloading a model. That is the mode used in the current local deployment.

It recognizes explicit terms such as “beginner,” “Sunday,” and “relaxed,” but has more limited interpretation than the Gemma integration.

Grounding the map in real data

For the map, I used OpenStreetMap data and source-backed destinations. Route geometry comes from a bicycle routing provider rather than being generated by the language model.

Privacy also shaped the implementation. Public cyclist results omit email addresses and exact stored coordinates. Rider markers use broad neighborhood reference points, while event meeting points are public locations chosen by organizers.

Why Does Open Innovation Matter?

For RideCircle, open innovation makes the project easier to run, inspect, and change.

With Gemma running through Ollama, search interpretation can happen on the user’s machine without sending the search text to a hosted model API. That also gives me room to experiment with prompts and model sizes without paying for every inference request.

It doesn’t make the whole application offline. Map tiles and live route calculations still need internet access. But it gives me control over where the AI part runs.

The open tools around the model matter just as much. PostGIS handles geographic searches, Leaflet makes the map interactive, and OpenStreetMap provides the map data. Together, they let me build a useful local prototype without a paid mapping subscription.

A closed API could also interpret cycling requests. What this approach gives me is the ability to run that part locally, inspect the surrounding code, and adapt it as the project develops.

That feels especially relevant for a community app. Someone should be able to take the project, add destinations in their own city, adjust the matching rules, and build something that fits their local cycling community.

What comes next?

RideCircle is still an MVP. It hasn’t yet been validated through an actual group ride, and the sample profiles aren’t evidence of an active community.

The next useful test is getting a few real cyclists to try it and seeing whether it helps them arrange a ride.

That’s the outcome I care about: a plan that turns into people getting on their bikes.

Digit Manipulation

Abirami Prabhakar — Wed, 08 Apr 2026 05:42:51 +0000

//Sum of Digits

int value = 12345;
         int res = 0;
         while ( value > 0){
            res = res + ( value % 10);
            value = value / 10;
        }
        System.out.println(res);

//Reverse a digit

int value = 12345;
        int res = 0;
        int rem = 0;
        while ( value > 0){
             res = (value %10);
             rem = rem *10 + res;
             value = value/10;    
         }
        System.out.println(rem);

//Multiplying all didgits

 int value = 2546987;
        int res = 1;
        while (value > 0){
            res = res *(value %10);
            value = value/10;
        }
        System.out.println("The Multiplication of Digits is"+ res);

// Max of all didgits

int value = 67854095;
       int max = value % 10;
       while( value > 0){
        int lastdigit = value %10;
        if ( max < lastdigit){
            max = lastdigit;
        }
        value = value /10;
       }
       System.out.println("The largest digit " + max);

Armstrong Number - finding if the number is a armstrong number by using manual multiplication for finding the power

int num = 1634;
int aNum = 0;
int bcup = num;

while (num > 0) {
    int ld = num % 10;
    aNum += (ld * ld * ld * ld);
    num = num / 10;
}

if (bcup == aNum) {
    System.out.println("Is an Armstrong Number");
} else {
    System.out.println("Is NOT an Armstrong Number");
}

to find power of a number

int number = 10;
        int power = 4;
        int res = 1;
        for ( int i = 1 ; i <= power ; i++){
            res *= num
        }
        System.out.println("Power of give number is " + res);

Armstrong Number using a loop to find the power of number


int num = 1634;
            int aNum = 0;
            int bcup = num;
            int count = 0;
            while ( num > 0){    
                count++;
                num /= 10;
            }
            num = bcup;
            while ( num > 0){
                int ld = num %10;
                int pow = 1;
                for ( int i = 0; i < count ; i++){
                    pow = pow * ld;
                }
                aNum = aNum + pow;
                num /= 10;
            }
            if ( aNum == bcup){
                System.out.println("Is a Armstrong Number");
            } else {
                System.out.println("Is NOT a Armstrong Number");
            }

Setup a DNS hosted zone in Route53 in AWS.

Abirami Prabhakar — Sun, 29 Mar 2026 16:56:27 +0000

When I first tried setting up DNS in Amazon Web Services Route 53, I thought it would be straightforward—just create a hosted zone, add a record, and my website would magically work. I created a hosted zone with a random name, added an A record pointing to my EC2 public IP, and felt pretty confident.But then I reliazed it doesnt work as simple as I thought it will work like .

No matter what I tried, the domain wouldn’t open in the browser. That’s when I realized I had completely missed something basic—I didn’t even own a domain name. I had been treating Route 53 like it could create a website address for me, when in reality, it only manages DNS for domains that already exist.

That moment was frustrating, but also a turning point. I finally understood how DNS actually works: you need a registered domain first, and then you connect it to Route 53 using nameservers. Without that, there’s nothing for DNS to resolve.

In the end, I learned two important things. First, Route 53 is not a domain provider—it’s a DNS service. And second, if you don’t have a domain, you can still test your app using the EC2 public IP or DNS, which works perfectly for development.

It was a small mistake, but it made the whole concept much clearer. Sometimes, not having something—in this case, a domain—teaches you more than having everything set up correctly from the start.

Creating a hosted Zone in Route 53

Retriving the public IP of EC2 inatance to create a record

Creating Records

Users, Roles, Groups

Abirami Prabhakar — Sun, 29 Mar 2026 16:30:10 +0000

Think of a user as an individual person who logs into the database and does work. A role is like a set of permissions — what actions are allowed, like reading or updating data. Instead of giving permissions to each user one by one, we create groups (roles) and assign permissions to them, then simply add users to those groups. This way, managing access becomes much easier, because changing the role automatically updates permissions for everyone in it.lets look into some examples to understand better

Task 1: Create a login role report_user that can only read from the film table.

CREATE ROLE report_user WITH LOGIN PASSWORD 'pass123';
GRANT SELECT ON film TO report_user;

GRANT is used to grant access to use commands to roles

Task 2: Now try to access customer table. When report_user tries to query customer and gets permission denied. Fix it.

SELECT * FROM customer;
*Error prompted*
GRANT SELECT ON customer TO report_user;

as user was initially given access only yto film table, he was unable to access the customer table

Task 3: Allow report_user to see only customer_id, first_name, last_name of the customer table.

REVOKE SELECT ON customer FROM report_user;
GRANT SELECT (customer_id, first_name, last_name) ON customer TO report_user;

REVOKE is used to disable a user from doing certain commands in a table

Task 4: Create support_user who can, SELECT from customer, UPDATE only email column, Cannot DELETE

CREATE ROLE support_user WITH LOGIN PASSWORD 'pass123';
GRANT SELECT ON customer TO support_user;
GRANT UPDATE (email) ON customer TO support_user;
REVOKE DELETE ON customer FROM support_user;

Task 5: Remove SELECT access on film from report_user.

REVOKE SELECT ON film TO report_user;

Task 6: Create readonly_group that has SELECT on all tables.

CREATE ROLE readonly_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public to readonly_group;

Task 7: Create analyst1 and analyst2 and add them to readonly_group.

CREATE ROLE analyst1 WITH LOGIN PASSWORD 'pass123';
CREATE ROLE analyst2 WITH LOGIN PASSWORD 'pass123';
GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Idempotency Situation

Abirami Prabhakar — Sun, 29 Mar 2026 16:08:04 +0000

Idempotency Situation is a when Even if the same request comes multiple times,it should behave like it happened only once this often happens in payment transactions due to network issues can be cause loss to customer if it is not handled properly.

This can be solves by keeping track of transactions and every bank transaction has a transaction ID and even if retry is given the trasaction takes place only once

The trasaction table keep a record all the amount that is transferred from a user to another user

CREATE TABLE transactions (
    id SERIAL PRIMARY KEY,
    sender TEXT,
    receiver TEXT,
    amount INT,
    idempotency_key TEXT UNIQUE
);

how it works

BEGIN;

INSERT INTO transactions (sender, receiver, amount, idempotency_key)
VALUES ('Alice', 'Bob', 200, 'txn_101');

UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';

COMMIT;

The data is entered before every transaction and then the query is updated

now, when the same transaction with the same idempotency_key is there for another query , an error is prompted because duplicate key values violates the unique constraint of the attribute

INSERT INTO transactions (sender, receiver, amount, idempotency_key)
VALUES ('Alice', 'Bob', 200, 'txn_101');

Consistency

Abirami Prabhakar — Sun, 29 Mar 2026 15:36:10 +0000

The consistency property in ACID, is to ensure that the transactions in queries are always in sense and all the constraints are satistfied before executing them and this in terms of wallet system can be a problem if a user tries to debit more than what is in their balance or update data in negative number and those can be stopped by being consistent

Examples of such situations and how an error is prompted to maintain consistency of the data

the balance is updated to negative value

UPDATE accounts 
SET balance = -100 
WHERE name = 'Alice';

an error is prompted

trying to debit more than the users balance

UPDATE accounts 
SET balance = balance - 2000 
WHERE name = 'Bob';

an error is prompted as cuz Bob only has balance of 500 in his account

Changes can be COMMIT only when the data is completely logically correct

Durablity

Abirami Prabhakar — Sun, 29 Mar 2026 15:26:45 +0000

Durability means once a transaction is successfully committed, its changes are permanently saved in the database. Yes, even when the system crashes, power failure or any other and this relaiblity in transaction is possible in databases like postgresql by WAL( Write Ahead Logs) where Before making actual changes, they first record the transaction in a log file. This way even if the system crashes before executing, the queries can be retrieved to execute them

In case of Wallet systems
Once money is transferred and confirmed, it will always be reflected and there is No risk of losing completed transactions and records for the user

BEGIN;

UPDATE accounts 
SET balance = balance - 100 
WHERE name = 'Alice';

COMMIT;

Atomicity

Abirami Prabhakar — Sun, 29 Mar 2026 15:20:05 +0000

Atomicity means a transaction is treated as one complete action, not separate steps. All the operations inside it must succeed together, or else none of them are applied. If something goes wrong in between, the system cancels everything and returns to the original state so no partial changes remain.

Atomicity is what makes a wallet system reliable for me. I see every transfer as one complete action, not separate steps. Either the full transaction happens or nothing happens at all. If something fails in between, everything is cancelled, so no money is lost and no partial updates occurs.

BEGIN;
initiatiing a transaction by detucting money from Alice
UPDATE accounts 
SET balance = balance - 200 
WHERE name = 'Alice';

making a error intentionlly 
UPDATE accounts 
SET balance = balance + 200 
WHERE name = 'Charlie';  (user Charlie does not exist)

COMMIT;

The Transaction does not take place and commit does not happen and rolledback

ROLLBACK;

Thus, Alice’s balance remains unchanged and No partial deduction happens, This is Atomicity

Alter Tables

Abirami Prabhakar — Sat, 28 Mar 2026 14:57:23 +0000

1. You have a table customers with a column email that currently allows NULL values. Modify the table so that future entries must always have an email.

ALTER TABLE customer
ALTER COLUMN email SET NOT NULL;

2. In the users table, ensure that the username column is unique across all records using an ALTER statement.

ALTER TABLE users
ADD CONSTRAINT unique_username UNIQUE (username);

the reason we cant deal with this like previous question is because , unique is a table level property can not be alter as a column property so we add it as a constraint

3. In the products table, enforce that price must always be greater than 0 using an ALTER command.

ALTER TABLE products
ADD CONSTRAINT price_check CHECK (price > 0);

checking values can be can be updated only as a constraint

4. Modify the orders table so that the status column defaults to 'pending' if no value is provided during insertion.

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';\

5. Alter the employees table by adding a new column salary such that
It cannot be NULL,It must always be greater than 10,000

ALTER TABLE employees
ADD COLUMN salary DECIMAL NOT NULL CHECK (salary > 10000);

6. Modify the foreign key constraint between employees and departments so that when a department is deleted, all related employees are automatically removed.

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE;

7. In the accounts table, remove an existing CHECK constraint that enforces balance >= 0.

ALTER TABLE accounts
DROP CONSTRAINT accounts_balance_check;

8. In the payments table, ensure that the combination of user_id and transaction_id is unique using an ALTER TABLE statement.

ALTER TABLE payments
ADD CONSTRAINT unique_payment UNIQUE (user_id, transaction_id);

Creating Table

Abirami Prabhakar — Sat, 28 Mar 2026 14:40:45 +0000

1. Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student.

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

2. Create a table employees where name and email cannot be empty, but phone_number can be optional.

NOT NULL contraint is used to ensure that a attribute cannot be left empty

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15)
);

3. Create a table users where both username and email must be unique across all records.

UNIQUE constaint is allow only unique values and no repeated values

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE
);

*4. Create a table products where price must always be greater than 0 and stock cannot be negative.
*
DECIMAL datatype is used to keep records of money in detail and arthmetic operators are used to check certain values

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

5. Create a table orders where status should default to 'pending' if no value is provided, and created_at should store the current timestamp automatically.

DEFAULT is used to fill any string/ value when a record has no input from user

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. Create a table accounts where account_number must be unique and not null balance must always be greater than or equal to 0

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    account_number VARCHAR(50) UNIQUE NOT NULL,
    balance DECIMAL CHECK (balance >= 0) 
);

7. Create a table enrollments where a student can enroll in multiple courses, but the combination of student_id and course_id must be unique.

CREATE TABLE enrollments (
    id SERIAL PRIMARY KEY,
    student_id INT UNIQUE, 
    course_id INT UNIQUE,   
);

8. Create two tables:
departments with id and name
employees with id, name, and department_id
Ensure that department_id in employees must exist in departments.

foreign key refernce from the employee table to the departments table as be used with constraint to ensure the presence of dept_id attribute in both table

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

9. When a department is deleted, all related employees are also deleted
When a department ID is updated, it reflects in the employees table

the previous foreign key contraint on the employee table as been dropped and a new constraint with refernces to dept_ id with ON DELETE CASCADE & ON UPDATE CASCADE as one cannot have two foreign constraint on the same table

ALTER TABLE employees
DROP CONSTRAINT employees_department_id_fkey;

ALTER TABLE employees
ADD FOREIGN KEY (department_id)
REFERENCES departments(id)
ON DELETE CASCADE
ON UPDATE CASCADE;