ডাটাবেস ইন্টারভিউ প্রশ্ন 2026: SQL, নরমালাইজেশন, ACID এবং Sharding

Normalization: organize tables to reduce redundancy and improve integrity

1NF (First Normal Form):
  - Each column contains atomic (indivisible) values
  - No repeating groups or arrays in columns
  BAD:  users(id, name, phones="555-1234,555-5678")
  GOOD: users(id, name) + phones(id, user_id, phone)

2NF (Second Normal Form):
  - Must be in 1NF
  - All non-key columns depend on the ENTIRE primary key (no partial dependency)
  BAD:  orders(order_id, product_id, product_name, qty)
        product_name depends only on product_id, not full key
  GOOD: orders(order_id, product_id, qty)
        products(product_id, product_name)

3NF (Third Normal Form):
  - Must be in 2NF
  - No transitive dependencies (non-key columns depend only on key)
  BAD:  employees(emp_id, dept_id, dept_name)
        dept_name depends on dept_id, not emp_id
  GOOD: employees(emp_id, dept_id)
        departments(dept_id, dept_name)

-- Isolation levels (weakest to strongest):

-- READ UNCOMMITTED: can read uncommitted data (dirty reads)
-- Rarely used, maximum performance

-- READ COMMITTED (default in PostgreSQL):
-- Only reads committed data. Prevents dirty reads.
-- Non-repeatable reads possible (same query returns different results)

-- REPEATABLE READ (default in MySQL):
-- Same rows return same data within transaction.
-- Phantom reads possible (new rows can appear)

-- SERIALIZABLE: full isolation, no phantoms
-- Slowest, all transactions run as if sequential
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- In PostgreSQL
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ...
COMMIT;

-- Common choice: READ COMMITTED for most apps
-- REPEATABLE READ for financial reporting
-- SERIALIZABLE for critical financial transactions

-- Clustered: physical order of data = index order
-- PRIMARY KEY automatically creates clustered index in most DBs
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,  -- clustered index
  email VARCHAR(255),
  name VARCHAR(100)
);

-- Non-clustered: lookup structure → pointer to data
CREATE INDEX idx_email ON users(email);  -- non-clustered

-- Covering index: includes all needed columns
-- Allows index-only scan (never touches data pages)
CREATE INDEX idx_user_lookup ON users(email) INCLUDE (id, name);
-- SELECT id, name FROM users WHERE email = 'alice@example.com'
-- Can be answered entirely from index — very fast!

Sharding strategies:

Range-based: shard by value range
  Shard 1: user_id 1-1M
  Shard 2: user_id 1M-2M
  Pros: range queries efficient
  Cons: hot spots (newest users all on last shard)

Hash-based: shard_num = hash(user_id) % num_shards
  Even distribution
  Cons: range queries hit all shards, resharding is hard

Directory-based: lookup table maps key → shard
  Flexible, easy to rebalance
  Cons: lookup overhead, single point of failure

Cross-shard challenges:
  - JOINs must be done in application layer
  - Distributed transactions are complex
  - Global uniqueness (use UUIDs instead of auto-increment)