1NF: Atomic values, no repeating groups
2NF: 1NF + No partial dependency on composite key
3NF: 2NF + No transitive dependency
BCNF: Every determinant is a candidate key
Primary Key: Unique, NOT NULL, one per table
Foreign Key: References primary key of another table
Candidate Key: Minimal super key (can be PK)
Super Key: Set of attributes that uniquely identifies tuple
Composite Key: Multiple columns forming a key
Surrogate Key: Artificial (auto-increment)
Natural Key: Real-world data (email, SSN)
INNER JOIN: Only matching rows
LEFT JOIN: All left + matching right
RIGHT JOIN: All right + matching left
FULL OUTER JOIN: All from both
CROSS JOIN: Cartesian product
SELF JOIN: Table with itself
B-Tree: Balanced, sorted, O(log n), good for range queries
Hash: O(1) exact match, NOT for range
Clustered: Physical order = index order, one per table
Non-Clustered: Separate structure, multiple per table
When to index:
✅ WHERE, JOIN, ORDER BY columns
✅ High cardinality
✅ Read-heavy tables
When NOT to index:
❌ Small tables
❌ Frequently updated columns
❌ Low cardinality
C (Consistency): Every read gets latest write
A (Availability): Every request gets response
P (Partition Tolerance): Works despite network failures
Choose 2 of 3 (P is mandatory in distributed systems):
CP: Consistent but may reject requests (HBase, MongoDB)
AP: Available but may return stale data (Cassandra, DynamoDB)
-- Window Functions
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as rank,
AVG(salary) OVER (PARTITION BY dept) as dept_avg
FROM employees;
-- CTE
WITH active AS (
SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active WHERE age > 25;
-- Subquery
SELECT * FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NYC');
-- Aggregate
SELECT dept, COUNT(*), AVG(salary)
FROM employees
GROUP BY dept
HAVING AVG(salary) > 50000;
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK on failure