Databases

ACID Properties
• Atomicity: all operations in a transaction succeed or all fail
• Consistency: data must be valid according to defined rules
• Isolation: concurrent transactions don't interfere
• Durability: committed data persists even after failures

Normalization
• 1NF: atomic values, no repeating groups
• 2NF: no partial dependency on composite primary key
• 3NF: no transitive dependency (non-key column depends on non-key column)
• BCNF: every determinant is a candidate key

Indexing
• B-Tree index: default, supports equality and range queries
• Hash index: equality only, faster for exact matches
• Composite index: multiple columns, order matters
• Covering index: includes all columns needed for a query (no table lookup)
• Partial index: index a subset of rows

Query Optimization
• EXPLAIN ANALYZE to read query plans
• Avoid SELECT *, only fetch needed columns
• Use indexes on WHERE, JOIN, and ORDER BY columns
• Avoid functions on indexed columns in WHERE clauses
• Paginate large result sets

Document (MongoDB, Firestore)
• JSON-like documents with flexible schema
• Good for: content management, user profiles, catalogs
• Query by any field, nested documents supported

Key-Value (Redis, DynamoDB)
• Simplest model: key maps to value
• Redis: in-memory, sub-millisecond, supports strings/lists/sets/hashes/sorted sets
• DynamoDB: fully managed, single-digit ms, scales to any size
• Good for: caching, sessions, leaderboards, real-time features

Column-Family (Cassandra, HBase)
• Data stored by columns, not rows
• Excellent write performance, scales horizontally
• Good for: time-series, event logs, IoT data
• CQL (Cassandra Query Language) looks like SQL

Graph (Neo4j, Amazon Neptune)
• Nodes and edges with properties
• Native graph traversal (no expensive JOINs)
• Good for: social networks, recommendation engines, fraud detection
• Query language: Cypher (Neo4j)

ACID in Practice (PostgreSQL) ```sql -- Transaction with savepoints BEGIN;

SAVEPOINT step1; UPDATE accounts SET balance = balance - 500 WHERE id = 1;

SAVEPOINT step2; UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Verify SELECT id, balance FROM accounts WHERE id IN (1, 2);

-- If verification fails: -- ROLLBACK TO SAVEPOINT step2;

COMMIT;

-- ── Isolation Levels ────────────────────────────────── SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- default SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- no non-repeatable reads SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- strictest, slowest

-- ── Advisory Locks (application-level) ─────────────── -- Useful for distributed locking (e.g., prevent duplicate job runs) SELECT pg_try_advisory_lock(42); -- returns true if lock acquired -- ... do work ... SELECT pg_advisory_unlock(42);

-- ── Row-level locking ───────────────────────────────── -- FOR UPDATE lock rows for modification BEGIN; SELECT * FROM inventory WHERE sku = 'ABC-123' FOR UPDATE; UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'ABC-123'; COMMIT;

-- FOR SHARE allow other reads, prevent writes SELECT * FROM products WHERE id = $1 FOR SHARE; ```