DEV Community: Haripriya V

Assignment 29

Haripriya V — Sat, 04 Apr 2026 15:08:01 +0000

After launching a web server, accessing it via IP address works — but it’s not practical. So the next step was to connect a domain name.
For this, I used Amazon Route 53 from Amazon Web Services.

Why DNS Matters
Humans prefer domain names, not IP addresses. DNS translates a domain into the correct server location.
Without it, users would have to remember numeric IPs — which isn’t realistic.

Step 1: Creating a Hosted Zone
Inside Route 53:
•I created a Public Hosted Zone
•Entered my domain name
AWS automatically generated:
NS (Name Server) records
SOA record
These are essential for DNS
functionality.

Step 2: Mapping Domain to Server
To connect my domain to the EC2 instance:
•Created an A Record
•Pointed it to the server’s public IP
This step tells AWS where to send incoming traffic.

**Step 3: **DNS Propagation
Even after correct setup, the domain didn’t work immediately.
This is expected.
DNS changes take time to propagate globally. In my case, it took some time before everything worked properly.

Challenges I Noticed
Expected instant results after configuration
Needed to double-check record values
Realized DNS delays are normal, not errors

What I Learned
DNS is a critical layer between users and servers
Small configuration mistakes can break routing
Patience is required due to propagation delays

Final Thoughts
Setting up DNS made my project feel more real. Instead of accessing a raw IP, I now understand how domains connect users to applications.
If you're learning cloud, don’t stop at launching a server — connect it properly using DNS.

Assignment 28

Haripriya V — Sat, 04 Apr 2026 15:03:24 +0000

Getting hands-on with cloud infrastructure was something I wanted to do for a while. So I started with a simple goal: launch a server and make a webpage accessible over the internet.
For this, I used Amazon Web Services and specifically Amazon EC2.

Step 1: Launching the Instance
I created a new EC2 instance with the following setup:

•Operating System: Amazon Linux
•Instance Type: Free-tier eligible
•Key Pair: Created for SSH access
•Security Group:
Allowed SSH (port 22)
Allowed HTTP (port 80)
One mistake here can block everything later, so I made sure the ports were correctly configured.

Step 2: Installing the Web Server
After connecting to the instance, I installed Apache:

sudo yum update -y
sudo yum install httpd -y
sudo systemctl start httpd
sudo systemctl enable httpd

Then I created a simple webpage:
echo "

My EC2 Server is Live!

" | sudo tee /var/www/html/index.html

Step 3: Accessing the Server
Using the public IP address of the instance, I opened it in a browser.
The page loaded successfully, confirming:
•Server is running
•Web service is active
•Network access is working

Problems I Faced
Not everything worked instantly:
Initially forgot to allow HTTP traffic
Mixed up private IP and public IP
Had to restart Apache once
These small mistakes helped me understand how each layer matters.

Key Takeaways
EC2 gives full control but requires manual setup
Security groups act like a firewall
Even a simple deployment has multiple checkpoints

What’s Next?
I plan to improve this setup by:
Adding a domain
Using a static IP
Exploring load balancing

ASSIGNMENT 36

Haripriya V — Sat, 28 Mar 2026 17:05:12 +0000

** Building a Reliable Digital Wallet System (Like PhonePe / GPay)**

Introduction

In today’s digital world, applications like Google Pay, PhonePe, and Paytm handle millions of financial transactions every second.

Behind these simple “Pay” buttons lies a highly critical system where data consistency is everything. Even a tiny inconsistency can lead to:
•Money loss
• Duplicate transactions
• Incorrect balances

To prevent this, databases rely on ACID properties, especially Isolation, which ensures transactions don’t interfere with each other in unsafe ways.

Database Setup

We begin with a simple accounts table:

CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance INT NOT NULL CHECK (balance >= 0), last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Sample Data

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

This simulates a wallet system where users hold balances.

Simulating Concurrent Transactions

To understand how isolation works, we simulate two concurrent database sessions.

Scenario: Two Transactions on Same Account

Session 1 (T1)

`BEGIN;

UPDATE accounts
SET balance = balance - 800
WHERE name = 'Alice';`

Alice’s balance becomes 200 (but uncommitted)

Session 2 (T2)

`BEGIN;

SELECT balance FROM accounts WHERE name = 'Alice'; `

Now the key question:
Can T2 see the updated balance (200)? Or still 1000?

This depends on the isolation level.

Isolation Levels Explained

Read Uncommitted

Lowest isolation (rarely used in PostgreSQL)
•T2 can see uncommitted data
•Leads to Dirty Reads

Problem

T2 might see balance = 200 even if T1 later rolls back.

Result: Incorrect financial state

Read Committed (Default in PostgreSQL)**
•T2 cannot see uncommitted changes
•Only committed data is visible

✔ Behavior

T2 sees:

Alice = 1000

Issue: Non-repeatable Reads

If T1 commits later, T2 may see different values in the same transaction.

Repeatable Read

•Ensures consistent snapshot during transaction

✔ Behavior
•T2 always sees 1000 during its transaction
•Even if T1 commits later

Issue: Phantom reads still possible (in some DBs)

Serialize (Highest Isolation)

Most strict and safest
•Transactions behave as if executed one after another

✔ Behavior
•Prevents:
•Dirty reads
•Non-repeatable reads
•Lost updates

Example Outcome

One transaction may fail with:

ERROR: could not serialize access due to concurrent update

You must retry the transaction

Key Problems Demonstrated

Dirty Read

Reading uncommitted data → leads to wrong balance

Non-Repeatable Read

Same query returns different results within a transaction

Lost Update

`-- T1 reads 1000
-- T2 reads 1000

-- T1 updates to 200
-- T2 updates to 300

-- Final = 300 (T1 lost) `

Two transactions overwrite each other’s updates

Example:

Why Isolation Matters in Wallet Systems

In a real-world wallet:
•Multiple users may send money simultaneously
•Systems must prevent:
•Double spending
•Balance corruption
•Race conditions

Without isolation, financial systems would be unreliable.

Best Practices for Wallet Systems

SELECT * FROM accounts WHERE name = 'Alice' FOR UPDATE;

•Use Serializable isolation for critical transactions
•Use row-level locking:

Implement retry logic for failed transactions
•Keep transactions short and efficient

Conclusion

Building a digital wallet system is not just about transferring money—it’s about guaranteeing correctness under concurrency.

Through this experiment, we observed how different isolation levels impact:
•Data visibility
•Transaction safety
•System reliability

ASSIGNMENT 32

Haripriya V — Sat, 28 Mar 2026 16:27:25 +0000

Introduction

In SQL, filtering data is essential to retrieve only the required records from a database. This is done using the WHERE clause along with different operators.

In this task, we explore filtering techniques such as:
•WHERE for conditions
•AND, OR for combining conditions
•IN, BETWEEN for range/value filtering
•LIKE, SIMILAR TO for pattern matching
•IS NULL for missing values
•LIMIT, OFFSET for controlling output

These operations help in extracting meaningful and specific data from large datasets.

Queries with Code & Explanation

Films with rental rate > 3

SELECT title, rental_rate FROM film WHERE rental_rate > 3;

Filters movies with rental rate greater than 3.

Rental rate > 3 AND replacement cost < 20

SELECT title, rental_rate, replacement_cost FROM film WHERE rental_rate > 3 AND replacement_cost < 20;

Uses AND to apply multiple conditions.

Rating = ‘PG’ OR rental rate = 0.99

SELECT title, rating, rental_rate FROM film WHERE rating = 'PG' OR rental_rate = 0.99;

Retrieves movies satisfying either condition.

Top 10 movies by rental rate

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC LIMIT 10;

Sorts and limits results.

Skip 5, fetch next 3 (ascending)

SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 LIMIT 3;

Skips first 5 rows and fetches next 3.

(Same as 5 – alternate syntax)

SELECT title, rental_rate FROM film ORDER BY rental_rate ASC OFFSET 5 FETCH NEXT 3 ROWS ONLY;

Uses FETCH instead of LIMIT.

Rental duration between 3 and 7

SELECT title, rental_duration FROM film WHERE rental_duration BETWEEN 3 AND 7;

Filters range using BETWEEN.

Title starts with ‘A’ and ends with ‘e’

SELECT title FROM film WHERE title LIKE 'A%e';

Pattern matching using LIKE.

Customers with no email

SELECT first_name, last_name FROM customer WHERE email IS NULL;

Finds missing values.

Movies (2006, specific rates, title starts with S)

SELECT title, rental_rate, release_year FROM film WHERE release_year = 2006 AND rental_rate IN (2.99, 3.99) AND title LIKE 'S%' LIMIT 5;

Combines multiple filters.

10 customers after skipping 20

SELECT first_name, last_name FROM customer ORDER BY last_name LIMIT 10 OFFSET 20;

Pagination using LIMIT + OFFSET.

Top 5 movies by replacement cost (skip highest)

SELECT title, replacement_cost FROM film ORDER BY replacement_cost DESC OFFSET 1 FETCH NEXT 5 ROWS ONLY;

Skips the highest and gets next 5.

Rentals between two dates

SELECT rental_id, rental_date, customer_id FROM rental WHERE rental_date BETWEEN '2005-05-01' AND '2005-06-01';

Filters date range.

Actors with “man” in last name

SELECT first_name, last_name FROM actor WHERE last_name LIKE '%man%';

Searches substring using %.

Movies with NULL special features

SELECT title FROM film WHERE special_features IS NULL;

Finds missing feature values.

Rental duration >7

SELECT title, rental_duration FROM film WHERE rental_duration > 7;

Filters based on duration.

Movies with multiple conditions

SELECT title, rental_rate, rating FROM film WHERE rental_rate IN (2.99, 4.99) AND rating = 'R' AND title LIKE '%L%' LIMIT 10;

Combines IN, AND, and LIKE.

Title starts with A or B and ends with s

SELECT title FROM film WHERE title SIMILAR TO '(A|B)%s';

Uses regex-like pattern matching.

Title contains Man, Men, or Woman

SELECT title FROM film WHERE title SIMILAR TO '%(Man|Men|Woman)%';

Advanced pattern matching.

Conclusion

This task demonstrates how SQL filtering helps in:
•Extracting specific data using conditions
•Combining multiple filters effectively
•Searching patterns in text
•Handling missing value
•Controlling output size

ASSIGNMENT 31

Haripriya V — Sat, 28 Mar 2026 16:27:19 +0000

Introduction

The SELECT statement in SQL is used to retrieve data from a database. In this exercise, we explore different ways of querying the DVD Rental database, such as sorting, filtering, renaming columns, and finding unique values.

These queries demonstrate essential SQL concepts like:
•Column selection
•Aliasing (AS)
•Sorting (ORDER BY)
•Removing duplicates (DISTINCT)
•Aggregation (MIN)
•Limiting results (LIMIT)

Queries with Code & Explanation

Film titles and rental rates

SELECT title AS "Movie Title",rental_rate AS "Rate" FROM film;

Retrieves movie titles and rental rates with user-friendly column names.

Customer names and email

SELECT first_name AS "First Name",last_name AS "Last Name",email FROM customer;

Displays customer details with renamed columns.

Films sorted by rental rate

SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;

Sorts films by highest rental rate, then alphabetically.

Actor names sorted

SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name ASC;

Lists actors in proper alphabetical order.

Unique replacement cost

SELECT DISTINCT replacement_cost FROM film;

Removes duplicate replacement cost values.

Film title and duration

SELECT title,length AS "Duration (min)" FROM film;

Shows movie duration with a clear column name.

Customer active status

SELECT first_name,last_name, active AS "Is Active" FROM customer;

Displays whether customers are active.

Film categories

SELECT name FROM category ORDER BY name ASC;

Lists categories alphabetically.

Films by length

SELECT title, length FROM film ORDER BY length DESC;

Shows longest films first.

Actors sorted by first name

SELECT first_name, last_name FROM actor ORDER BY first_name DESC;

Sorts actors in reverse alphabetical order by first name.

Unique ratings

SELECT DISTINCT rating FROM film;

Displays all available movie ratings.

Unique rental durations

SELECT DISTINCT rental_duration FROM film;

Shows different rental duration values.

Customer ID with active status

SELECT DISTINCT customer_id,active FROM customer ORDER BY customer_id;

Displays unique customer IDs with their active status.

Earliest rental date per customer

SELECT customer_id, MIN(rental_date) AS rental_date FROM rental GROUP BY customer_id ORDER BY customer_id;

Finds each customer’s first rental.

10 shortest films

SELECT title, length FROM film ORDER BY length ASC LIMIT 10;

Lists the shortest movies.

Top 5 customers

SELECT title, length FROM film ORDER BY length ASC LIMIT 10;

Retrieves customers with highest IDs.

Unique store IDs

SELECT DISTINCT store_id FROM inventory;

Shows all store IDs.

Replacement cost sorted

SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;

Displays sorted unique replacement costs.

First rental per store

SELECT i.store_id, MIN(r.rental_date) AS rental_date FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id GROUP BY i.store_id ORDER BY i.store_id;

Finds earliest rental date for each store.

Unique film ratings sorted

SELECT DISTINCT rating FROM film ORDER BY rating ASC;

Displays ratings alphabetically.

Films by rating and length

SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;

Sorts by rating, then longest films first.

Actor sorting variation

SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;

Sorts actors with mixed order conditions.

Films by cost and rate

SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;

Sorts by cost and rental rate.

Customer sorting variation

SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;

Orders customers by last name and reverse first name.

Rentals sorted

SELECT * FROM rental ORDER BY customer_id ASC, rental_date DESC;

Shows all rentals sorted by customer and latest date.

Films by rental duration

SELECT title, rental_duration FROM film ORDER BY rental_duration ASC, title DESC;

Sorts films by duration and reverse title order.

Conclusion

These queries demonstrate how SQL can efficiently:
•Retrieve specific data
•Sort and organize results
•Remove duplicates
•Perform basic analysis

ASSIGNMENT 33

Haripriya V — Sat, 28 Mar 2026 16:27:11 +0000

*Restore the DVD Rental Database and perform role-based access control operations by creating users with specific permissions. Create a report_user role with limited access, modify permissions to allow or restrict access to certain tables and columns, and handle permission errors. Then create a support_user with controlled update rights, revoke specific privileges, and implement a readonly_group role with read access to all tables. Finally, create multiple users and assign them to this group to manage permissions efficiently.
*

Introduction

In database systems, role-based access control (RBAC) is used to manage who can access or modify data. Instead of giving permissions directly to every user, roles are created and privileges are assigned efficiently.

In this exercise, we:

Create users with restricted access
Grant and revoke permissions
Control access at both table-level and column-level
Use groups to simplify permission management

This ensures security, data privacy, and controlled operations.

Task 1: Create report_user with read access only to film

sql CREATE ROLE report_user LOGIN PASSWORD 'password123';

GRANT SELECT ON film TO report_user;

Explanation

Creates a login role
Grants read-only (SELECT) access to film table

Task 2: Fix permission denied for customer

sql GRANT SELECT ON customer TO report_user;

Explanation

Initially, access is denied
Granting SELECT allows reading the table

Task 3: Restrict access to specific columns
sql REVOKE SELECT ON customer FROM report_user;

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

Explanation

Removes full access
Grants column-level access only
Improves data privacy

Task 4: Create support_user with limited permissions

sql
CREATE ROLE support_user LOGIN PASSWORD 'password123';

GRANT SELECT ON customer TO support_user;

GRANT UPDATE (email) ON customer TO support_user;

REVOKE DELETE ON customer FROM support_user;

Explanation

Can read all customer data
Can update only email
Cannot delete records --> prevents data loss

Task 5: Remove SELECT access on film

sql REVOKE SELECT ON film FROM report_user;

Explanation

Removes previously granted permission
Demonstrates privilege revocation

Task 6: Create readonly_group with SELECT on all tables

sql CREATE ROLE readonly_group;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_group;

Explanation

Creates a group role
Grants read-only access to all tables
Useful for analysts and reporting users

Task 7: Create users and assign to group

sql
CREATE ROLE analyst1 LOGIN PASSWORD 'password123';
CREATE ROLE analyst2 LOGIN PASSWORD 'password123';

GRANT readonly_group TO analyst1;
GRANT readonly_group TO analyst2;

Explanation

Creates multiple users
Assigns them to the group
Inherits permissions automatically

Conclusion

This exercise demonstrates how PostgreSQL enforces secure and structured access control:

Roles simplify permission management
GRANT and REVOKE control access precisely
Column-level security enhances privacy
Group roles improve scalability

ASSIGNMENT 34

Haripriya V — Sat, 28 Mar 2026 16:27:05 +0000

*Design a transaction using the given accounts table to transfer money from one user to another by debiting the sender and crediting the receiver within a single transaction block. Then introduce errors at different stages of the transaction (such as after the debit operation) and observe whether the database allows partial updates or rolls back the entire transaction. Analyze the results to verify that atomicity is maintained, ensuring that either both operations succeed or none are applied.
*

Introduction

In a digital wallet system (like PhonePe/GPay/Paytm), Atomicity ensures:

A transaction is completed fully or not executed at all.

This is critical because:

Partial updates --> money loss
Failed transfers --> inconsistent balances

Atomicity guarantees:

Either both debit and credit happen
Or nothing happens
Given Table

Code

CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance INT NOT NULL CHECK (balance >= 0), last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Initial Data

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Initial State:

Alice --> ₹1000
Bob --> ₹500

Step 1: Successful Transaction

Code

BEGIN;

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

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

COMMIT;

Result

Alice --> ₹800
Bob --> ₹700

Both operations succeed

Step 2: Introduce Failure After Debit

Code

BEGIN;

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

UPDATE accounts SET bal = balance + 300 WHERE name = 'Bob';

COMMIT;

Observation

Second query fails
PostgreSQL automatically aborts the transaction
Entire transaction is rolled back

Final State

Alice --> ₹800 (unchanged)
Bob --> ₹700 (unchanged)

No partial update

Step 3: Manual Rollback Scenario

Code

BEGIN;

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

ROLLBACK;

Observation

Transaction is cancelled
No changes saved

Final State

Alice --> ₹800
Bob --> ₹700

Result:

Money disappears
System becomes unreliable
How PostgreSQL Ensures Atomicity

Transaction Blocks (BEGIN ... COMMIT) Groups multiple queries into one unit
Automatic Rollback on Failure Any error --> entire transaction is cancelled
Write-Ahead Logging (WAL) Tracks changes for safe recovery

Conclusion

Atomicity ensures:

Reliable money transfers
No partial transactions
Strong financial integrity

ASSIGNMENT 35

Haripriya V — Sat, 28 Mar 2026 16:26:55 +0000

*Using the given accounts table, test how PostgreSQL maintains data consistency by attempting operations that violate rules such as making the balance negative. Perform invalid updates like deducting more money than available or setting a negative balance, observe the errors produced, and explain whether these failures are due to database constraints or transaction logic. Finally, analyze how consistency is ensured and differentiate between rules enforced by the database schema and those handled at the application level.
*

Introduction

In a digital wallet system (like PhonePe/GPay/Paytm), consistency ensures that the database always remains in a valid state.

For example:

A user should never have a negative balance
Invalid transactions must be rejected
Only valid data should be stored

PostgreSQL enforces this using constraints, while additional rules are handled through transaction logic.

Given Table

Code

CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance INT NOT NULL CHECK (balance >= 0), last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Key Rule

CHECK (balance >= 0)

Ensures balance is never negative

Step 1: Insert Dummy Data

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

Step 2: Try Invalid Operation (Over-Deduction)
Code

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

Explanation

Deduction makes balance = -500
Violates CHECK (balance >= 0)

Database blocks the update

Step 3: Directly Setting Negative Balance

Code

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

Some rules must be handled manually:

Example (Safe Transfer Logic)
BEGIN;

Check balance first

SELECT balance FROM accounts WHERE name='Alice';

Only proceed if sufficient balance UPDATE accounts SET balance = balance - 500 WHERE name='Alice' AND balance >= 500;

UPDATE accounts SET balance = balance + 500 WHERE name='Bob';

COMMIT;

Final Analysis

Observations

PostgreSQL prevents invalid states using constraints
Invalid operations are rejected immediately
Database ensures consistency at data level

Limitations

Database doesn’t understand business logic fully
Needs application checks for:
Sufficient balance before transfer
Fraud detection
Transaction validation

Conclusion

Consistency is a shared responsibility

Database --> enforces structural rules
Application --> enforces logical rules

ASSIGNMENT 37

Haripriya V — Sat, 28 Mar 2026 16:26:46 +0000

Design a digital wallet system (like PhonePe/GPay/Paytm) using the given accounts table and sample data. Perform a money transfer between two users within a transaction and commit the changes. Then simulate a system crash or restart and retrieve the account balances to verify whether the committed transaction persists. Based on your observation, explain how the database ensures durability, and analyze what happens if a failure occurs just before or just after the COMMIT

Introduction

In applications like PhonePe / GPay / Paytm, even a tiny inconsistency can cause:

Money loss
Duplicate transactions
Incorrect balances

To prevent this, databases follow ACID properties, where Durability ensures:

Once a transaction is committed, it is permanently stored — even if the system crashes.

Given Table Structure

Code

CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, balance INT NOT NULL CHECK (balance >= 0), last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Explanation

balance >= 0 prevents negative money
last_updated tracks latest changes
Ensures basic integrity

Step 1: Insert Dummy Data

INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

Initial State:

Alice --> ₹1000
Bob --> ₹500

Step 2: Perform a Transaction (Transfer Money)

Code

BEGIN;

UPDATE accounts SET balance = balance - 200, last_updated = CURRENT_TIMESTAMP WHERE name = 'Alice';

UPDATE accounts SET balance = balance + 200, last_updated = CURRENT_TIMESTAMP WHERE name = 'Bob';

COMMIT;

Result After Commit
Alice --> ₹800
Bob --> ₹700

Transaction successful and committed

Step 3: Simulate System Crash / Restart

Now imagine:

Database crashes
Server restarts

Observation After Restart

Output remains:

Alice --> ₹800
Bob --> ₹700

Changes are still محفوظ (persisted)

Why Did This Work? (Durability Explained)

Durability is guaranteed by the database using:

Write-Ahead Logging (WAL)

Before modifying data, changes are written to a log file
Even if crash happens → database can recover using logs

Think of it like:

“Write it in a notebook before actually doing it”

Commit = Permanent Record

Once COMMIT is executed:
Changes are flushed to disk
Marked as permanent

What Happens During Failures?

Case 1: Crash BEFORE COMMIT

BEGIN;

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

Result after restart:

Transaction is rolled back

Alice still --> ₹1000

No partial updates (Atomicity + Durability)

Case 2: Crash AFTER COMMIT

COMMIT;

Result after restart:

Changes are preserved

Alice --> ₹800

Durability ensures no data loss

What If Durability Didn’t Exist?

Without durability:

Transactions may disappear after crash
Users could:
Lose money

Real-World Implementation (Wallet Apps)

Apps like PhonePe / GPay / Paytm ensure durability by:

Using robust databases (PostgreSQL, MySQL)
Maintaining replicated logs across servers
Performing frequent backups
Using distributed transaction systems

Final Conclusion

Key Takeaways

COMMIT = guarantee of persistence
Database logs ensure recovery after crash
Durability prevents data loss in financial systems

ASSIGNMENT 38

Haripriya V — Sat, 28 Mar 2026 16:26:38 +0000

Simulate a situation where the same transfer operation is executed more than once, as might happen in a real system due to network retries or duplicate requests. Perform the same deduction and credit operations multiple times and observe how the account balances are affected. Analyze whether the system prevents duplicate processing or allows the same transaction to be applied repeatedly. Based on your observations, think about how real-world systems ensure that repeated requests do not lead to inconsistent or duplicated state changes.

Introduction

In real-world systems (like banking or payment apps), a transaction might be sent multiple times due to:

Network retries
Slow server responses
Duplicate API requests

If the system is not designed properly, this can lead to duplicate deductions or credits, causing incorrect balances.

Step 1: Create Accounts Table

Code

CREATE TABLE accounts ( id INT PRIMARY KEY, name VARCHAR(100), balance DECIMAL(10,2) );

Explanation

Stores account details
balance represents available money

Step 2: Insert Sample Data

Code

INSERT INTO accounts VALUES (1, 'Alice', 1000);
INSERT INTO accounts VALUES (2, 'Bob', 500);

Explanation

Alice --> ₹1000
Bob --> ₹500

Step 3: Simulate a Transfer (Single Execution)

Code

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

Result

Alice --> ₹800
Bob --> ₹700

Step 4: Simulate Duplicate Execution (Retry Scenario)

Code

UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

Result

Alice --> ₹600
Bob --> ₹900

Observation

The system allowed duplicate processing
Same transaction applied twice
No mechanism to detect duplicates

This leads to:

Incorrect balances
Financial inconsistency
Potential fraud or system failure

The database:

Executes each query independently
Has no memory of previous transactions
Cannot identify if the request is a duplicate

Real-World Solutions

Transaction ID (Idempotency Key)

Example

CREATE TABLE transactions ( txn_id VARCHAR(50) PRIMARY KEY, from_account INT, to_account INT, amount DECIMAL(10,2) );

Each request has a unique txn_id
Duplicate txn_id --> rejected automatically

Check Before Processing

Logic

IF NOT EXISTS (SELECT * FROM transactions WHERE txn_id = 'TXN123') THEN
-- perform transfer
END IF;

Use Database Transactions (ACID)

Code

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;

Ensures atomicity

Unique Constraint on Transactions

ALTER TABLE transactions ADD CONSTRAINT unique_txn UNIQUE (txn_id);

Application-Level Idempotency

APIs store request IDs

If same request comes again → return previous result
Used by systems like:
Payment gateways
Banking apps

Final Conclusion

Key Findings
Basic SQL operations do NOT prevent duplicate execution
Same transaction can be applied multiple times
Leads to data inconsistency

ASSIGNMENT 39

Haripriya V — Sat, 28 Mar 2026 16:26:28 +0000

Students table with unique ID

Introduction

Each student must have a unique identifier.

Code

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

Explanation

PRIMARY KEY ensures:
No duplicate IDs
No NULL values
Each student is uniquely identified.

Employees table with required name & email

Introduction

Some fields are mandatory, while others can be optional.

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

Explanation

NOT NULL ensures name and email must be provided
phone_number is optional (can be NULL)

Users table with unique username & email Introduction

Usernames and emails must be unique for authentication systems.

Code

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

Explanation

UNIQUE prevents duplicate values
Ensures no two users share the same username/email

Products table with price and stock constraints

Introduction

Products must have valid pricing and stock values.

Code

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

Explanation

CHECK (price > 0) --> no free/negative products
CHECK (stock >= 0) --> stock cannot be negative

Orders table with default status & timestamp

Introduction

Automating values reduces manual errors.

Code

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

Explanation

DEFAULT 'pending' --> automatic order status
CURRENT_TIMESTAMP --> stores insertion time

Accounts table with constraints

Introduction

Bank accounts need strict validation rules.

Code

CREATE TABLE accounts ( id INT PRIMARY KEY, account_number VARCHAR(20) UNIQUE NOT NULL, balance DECIMAL(10,2) CHECK (balance >= 0) );

Explanation

UNIQUE NOT NULL --> ensures valid account numbers
CHECK (balance >= 0) --> prevents negative balance

Enrollments table with composite uniqueness

Introduction

A student can enroll in many courses—but not the same course twice.

Code

CREATE TABLE enrollments ( student_id INT, course_id INT, UNIQUE (student_id, course_id) );

Explanation

Composite UNIQUE ensures:
No duplicate student-course combinations
Allows multiple courses per student

Departments & Employees with foreign key

Introduction

Relationships between tables ensure data consistency.

Code

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

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

Explanation

FOREIGN KEY links employees to departments
Prevents invalid department references

Foreign key with CASCADE options

Introduction

Sometimes related data should update/delete automatically.

Code

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE ON UPDATE CASCADE );

Explanation

ON DELETE CASCADE --> deletes employees when department is deleted
ON UPDATE CASCADE --> updates employee records if department ID changes

Conclusion

Using constraints in table creation:

Improves data integrity
Prevents invalid or duplicate data
Maintains relationships between tables

ASSIGNMENT 40

Haripriya V — Sat, 28 Mar 2026 16:26:20 +0000

Enforcing NOT NULL on email in customers table

Introduction

Sometimes, a column initially allows NULL values, but later business rules require it to always have data. Here, we enforce that every customer must have an email.

Code

ALTER TABLE customers MODIFY email VARCHAR(255) NOT NULL;

Explanation

MODIFY changes the definition of the column.
NOT NULL ensures future records must include an email.
Existing NULL values must be handled before applying this (or it will fail).

Making username UNIQUE in users table

Introduction

Usernames should be unique to avoid duplication and conflicts.

Code

ALTER TABLE users ADD CONSTRAINT unique_username UNIQUE (username);

Explanation

Adds a UNIQUE constraint.
Prevents duplicate usernames.
Automatically creates an index for faster lookups.

Enforcing price > 0 in products table

Introduction

Products should never have zero or negative prices.

Code

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

Explanation

CHECK constraint validates data before insertion/update.
Ensures all product prices are strictly positive.

Setting default status as 'pending' in orders

Introduction

If no status is provided, the system should automatically assign a default value.

Code
ALTER TABLE orders MODIFY status VARCHAR(50) DEFAULT 'pending';

Explanation

DEFAULT 'pending' assigns a value automatically.
Simplifies insert operations and ensures consistency.

Adding salary column with constraints in employees

Introduction

We add a new column with rules to ensure valid salary values.

Code

ALTER TABLE employees ADD salary DECIMAL(10,2) NOT NULL CHECK (salary > 10000);

Explanation

Adds a new column salary.
NOT NULL → salary is mandatory.
CHECK → ensures salary is greater than 10,000.

Cascading delete between departments and employees

Introduction

When a department is deleted, all related employees should also be removed automatically.

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

Explanation

First removes the existing foreign key.
Recreates it with ON DELETE CASCADE.
Ensures dependent rows are automatically deleted.

Removing CHECK constraint from accounts table

Introduction

Sometimes constraints are no longer needed and must be removed.

Code

ALTER TABLE accounts DROP CHECK check_balance;

Explanation

Removes the constraint enforcing balance >= 0.
Constraint name (check_balance) must be known beforehand.

Making (user_id, transaction_id) unique in payments

Introduction

Some scenarios require composite uniqueness, where a combination of columns must be unique.

Code

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

Explanation

Ensures no duplicate pair of user_id and transaction_id.
Useful for preventing duplicate transactions per user.

Conclusion

Using ALTER TABLE, we can:

Enforce data integrity rules
Modify existing schema without recreating tables
Improve data quality and reliability