DATA STRUCTURE TASKS

Tables with constraints QN:1  Create a table called students where each student has an id, name, and age. Ensure that the id uniquely identifies each student. Query: CREATE TABLE students ( id SERIAL PRIMARY KEY, name TEXT, age INT ); since, id should uniquely identify each student.. we use PRIMARY KEY QN:2  Create a table employees where name and email cannot be empty, but phone_number can be optional. Query: CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL, phone_number TEXT ); since, name and email should not be empty.. we use NOT NULL.. phone is optional so no constraint QN:3  Create a table users where both username and email must be unique across all records. Query: CREATE TABLE users ( id SERIAL PRIMARY KEY, username TEXT UNIQUE, email TEXT UNIQUE ); since, username and email should not repeat.. we use UNIQUE QN:4  Create a table products where price must always be greater than 0 and stock cannot be negative. Query: CREAT...

Let me explain duplicate transaction (same request multiple times) First, we have accounts table Alice = 1000 Bob = 500 since, now we try same transfer again and again. First time transfer: BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; After first transfer: Alice = 800 Bob = 700 since, 200 is transferred successfully. Now we run the same transfer again (duplicate request). BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; After second time: Alice = 600 Bob = 900 since, same operation is applied again.. so money is transferred again. Final observation: since, database does not know it is duplicate request it will execute again and again so duplicate transactions are allowed Problem: since, same request runs multiple times money will be deducted multiple times this ...

Let me explain durability using accounts table First, we have accounts table Alice = 1000 Bob = 500 since, now we perform a normal money transfer. BEGIN; UPDATE accounts SET balance = balance - 300 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 300 WHERE name = 'Bob'; COMMIT; since, we deduct from Alice and add to Bob.. and commit the transaction. After transaction: Alice = 700 Bob = 800 since, changes are successfully saved. Now we simulate system restart (like closing and reopening database). Reconnect and check data: SELECT * FROM accounts; Result: Alice = 700 Bob = 800 since, even after restart.. data is still there. Final understanding: since, once COMMIT is done.. changes are permanently saved in database this is called durability Now think about failure cases If failure happens before COMMIT: since, transaction is not completed.. changes will not be saved so, data remains old If failure happens after COMMIT: since, commit is already done.. data wil...

Let me explain concurrent transactions using accounts table First, we have accounts table Alice = 1000 Bob = 500 since, now we try to do multiple transactions at same time from same account. Now we open 2 sessions (2 query windows). In Session 1: BEGIN; UPDATE accounts SET balance = balance - 800 WHERE name = 'Alice';  not committing since, we deducted 800 from Alice.. but not committed yet.. so change is not final. Now in Session 2: BEGIN; SELECT * FROM accounts WHERE name = 'Alice'; since, we are checking balance in another session. Result: Alice = 1000 since, Session 2 cannot see uncommitted changes from Session 1.. so it still shows old value. Now in Session 2 we try to deduct again: UPDATE accounts SET balance = balance - 500 WHERE name = 'Alice'; since, system will try to update same row. Result: it will wait (lock) since, Session 1 is already using that row.. so Session 2 cannot update until Session 1 completes. Now go back to Session 1 and do: COMMIT; si...

Let me explain how database maintains valid data using accounts table First, we already have accounts table with a rule: balance >= 0 since, this is a constraint.. database will not allow negative balance. Initial data: Alice = 1000 Bob = 500 Now we try to break the rule by deducting more money than available. UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice'; since, Alice has only 1000.. after deduction it becomes -1000.. which is not allowed. Result: ERROR will come since, CHECK (balance >= 0) constraint fails.. so database stops the query. Now we try direct update to negative value. UPDATE accounts SET balance = -500 WHERE name = 'Bob'; since, we are directly setting negative value. Result: ERROR will come since, again constraint is violated.. so update will not happen. Now we check data again: SELECT * FROM accounts; Alice = 1000 Bob = 500 since, invalid operations are rejected.. so data remains unchanged. Now we try inside transaction. BEGIN; ...

Let me explain the transaction working using accounts table First, we have an accounts table with balance for each user. Alice = 1000 Bob = 500 since, we are doing money transfer.. we need to deduct from one and add to another. Now we start transaction to transfer 200 from Alice to Bob. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'; UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'; COMMIT; since, both debit and credit should happen together.. so we use transaction. After successful transaction: Alice = 800 Bob = 700 since, 200 is deducted from Alice and added to Bob. Now we try with an error in between. BEGIN; UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';  error happens here UPDATE accounts SET balance = balance + 2000o WHERE name = 'Bob'; ROLLBACK; since, second query has error.. so transaction fails. After this failed transaction: Alice = 1000 Bob = 500 since, even though money was deducted fi...