Database Management Systems
32 Real Interview Questions

DBMS (Database Management System): Any software that organizes and manages data. Can be hierarchical (like IBM IMS), network-based, or relational. Provides data storage, retrieval, and basic management. Does NOT necessarily enforce relationships between data.

RDBMS (Relational Database Management System): A DBMS built on E.F. Codd's relational model. Data is organized in tables (relations) with rows and columns. Enforces referential integrity via foreign keys. Supports SQL. Guarantees ACID properties.

Referential integrity: A constraint ensuring that a foreign key value always references an existing primary key in the parent table. No orphan records — a child row cannot reference a non-existent parent.

ON DELETE behavior options:

CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT    -- reject delete if orders exist
        ON UPDATE CASCADE     -- update child FK if parent PK changes
);

Schema: The blueprint/structure of a database — defines all tables, columns, data types, constraints, indexes, and relationships. Contains no actual data.

Three-Level Schema Architecture (ANSI/SPARC):

Data Independence: The three levels provide independence. Physical independence: change storage without affecting logical schema. Logical independence: change schema without affecting application views.

View: A virtual table defined by a SELECT query. No physical storage — recomputed from base tables every time queried. Benefits: security (expose only certain columns/rows), simplify complex queries, centralize business logic.

-- Create a view for frequently-queried employee-department join
CREATE VIEW emp_dept AS
SELECT e.id, e.name, e.salary, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.id;

-- Query the view like a table
SELECT * FROM emp_dept WHERE dept_name = 'Engineering';
-- Database rewrites this as the full JOIN internally

Materialized View: Physically stores the query result as a table snapshot. Much faster for expensive aggregations. Needs explicit refresh when underlying data changes.

-- PostgreSQL materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS total
FROM orders GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_sales; -- refresh when data changes

A cursor is a database object that allows row-by-row processing of a query result set — like an iterator for SQL results. It maintains a pointer to the current row in a result set.

-- MySQL cursor example
DELIMITER $$
CREATE PROCEDURE process_employees()
BEGIN
    DECLARE emp_name VARCHAR(100);
    DECLARE done INT DEFAULT 0;
    DECLARE cur CURSOR FOR SELECT name FROM employees WHERE active = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO emp_name;
        IF done THEN LEAVE read_loop; END IF;
        -- process each row here
    END LOOP;
    CLOSE cur;
END$$

When to use: Complex procedural logic that cannot be expressed as set-based SQL — different processing logic per row, dependent sequential operations.

When to AVOID (most of the time): Cursors are significantly slower than set-based operations because SQL is optimized for sets, not row-by-row loops. Always try to replace cursor logic with JOINs, window functions, CTEs, or subqueries first. A cursor that processes 1 million rows may take 100x longer than an equivalent set-based query.

Anomalies are data integrity problems that arise from redundancy in poorly designed (un-normalized) schemas.

-- Un-normalized Orders table (violates 2NF):
-- OrderID | CustomerName | CustomerCity | Product  | Price
-- 1       | Alice        | Mumbai       | Laptop   | 60000
-- 2       | Alice        | Mumbai       | Mouse    |   500
-- 3       | Bob          | Delhi        | Keyboard |   800

Insertion Anomaly: Cannot register a new customer until they place an order.

-- Want to add Charlie from Pune as a customer (no orders yet)
-- IMPOSSIBLE: OrderID is NOT NULL and there is no product to attach yet.
-- We cannot store Charlie's city until he buys something.

Update Anomaly: Changing Alice's city requires updating EVERY row with her name.

UPDATE orders SET CustomerCity = 'Pune' WHERE CustomerName = 'Alice';
-- Must update rows 1 AND 2. Miss row 2 --> inconsistency.
-- Two rows say Alice lives in different cities --> DATA CORRUPTION.

Deletion Anomaly: Deleting Bob's only order also deletes his existence from the database.

DELETE FROM orders WHERE OrderID = 3;
-- Bob's name and city are now GONE from the database.
-- We lost customer information because he had no more orders.

Starting point — un-normalized order data:

OrderID | CustomerName | CustomerCity | Products         | Prices
1       | Alice        | Mumbai       | Laptop, Mouse    | 60000, 500
2       | Bob          | Delhi        | Keyboard         | 800

Step 1 — Apply 1NF: All values atomic. No multi-valued attributes. Each row uniquely identifiable.

-- PK = (OrderID, LineNum)
OrderID | LineNum | CustomerName | CustomerCity | Product  | Price
1       | 1       | Alice        | Mumbai       | Laptop   | 60000
1       | 2       | Alice        | Mumbai       | Mouse    |   500
2       | 1       | Bob          | Delhi        | Keyboard |   800
-- 1NF satisfied: all values atomic.
-- Problem: CustomerName and CustomerCity depend only on OrderID, not on LineNum.
-- This is a PARTIAL DEPENDENCY on the composite PK --> violates 2NF.

Step 2 — Apply 2NF: Remove partial dependencies. Split into Orders and OrderItems.

-- Orders: attributes depending only on OrderID
Orders(OrderID PK, CustomerName, CustomerCity)
1, Alice, Mumbai
2, Bob,   Delhi

-- OrderItems: attributes depending on the full (OrderID, LineNum) key
OrderItems(OrderID FK, LineNum, Product, Price)
1, 1, Laptop,   60000
1, 2, Mouse,    500
2, 1, Keyboard, 800
-- 2NF satisfied: every non-key attribute depends on the WHOLE key.
-- Problem: CustomerCity depends on CustomerName (not on OrderID directly).
-- If Alice moves, ALL her orders need updating --> transitive dependency --> violates 3NF.

Step 3 — Apply 3NF: Remove transitive dependencies.

-- Customers: CustomerCity is moved here (depends on CustomerName/ID, not OrderID)
Customers(CustomerID PK, CustomerName, CustomerCity)
1, Alice, Mumbai
2, Bob,   Delhi

-- Orders: now references Customer by ID
Orders(OrderID PK, CustomerID FK)
1, 1
2, 2
-- 3NF satisfied: no transitive dependencies.
-- Each fact stored in exactly one place.

BCNF (Boyce-Codd Normal Form): Stricter than 3NF. Rule: for every functional dependency A → B, A must be a candidate key (or superkey). A table can be in 3NF but violate BCNF when there are overlapping candidate keys.

-- Classic BCNF violation: CourseRegistration(Student, Course, Teacher)
-- Functional dependencies:
--   (Student, Course) --> Teacher  [a student in a course has one teacher]
--   Teacher --> Course              [each teacher teaches only one course]
-- Candidate keys: (Student, Course) and (Student, Teacher)
-- FD: Teacher --> Course violates BCNF because Teacher alone is NOT a candidate key.

-- Fix: decompose into two tables
TeacherCourse(Teacher PK, Course)
-- Teacher --> Course: Teacher IS the PK here. BCNF satisfied.

StudentTeacher(Student, Teacher FK references TeacherCourse)
-- Now every determinant is a candidate key.

Denormalization is the intentional introduction of redundancy to improve read performance by reducing JOIN overhead. You are deliberately reversing normalization for a performance reason.

When to denormalize:

• Data warehouses / OLAP: Reports run complex queries over billions of rows. JOIN cost is prohibitive. Star/snowflake schemas deliberately denormalize.
• Read-heavy, write-light workloads: Table read 10,000 times, written once a day — redundancy cost is trivial vs query speed gain.
• Pre-computed aggregates: Storing total_orders directly on the customer row instead of counting on every request.
• High-traffic hot paths: The user profile endpoint called 10M times/day — avoid 3 JOINs per call.

When NOT to denormalize:

• OLTP systems where data changes frequently — update anomalies return
• Before profiling to confirm JOINs are actually the bottleneck — premature optimization
• Without a plan for keeping redundant data consistent (trigger, application update, or explicit eventual consistency)

Star Schema (OLAP): one central fact table + multiple dimension tables directly connected. Simple, fast queries, some redundancy in dimensions.

Snowflake Schema (OLAP): dimension tables are further normalized into sub-dimensions. Less redundancy, more complex JOINs, slower queries. Star schema is preferred for most BI workloads.

Scenario: transfer ₹1000 from Account A (balance ₹5000) to Account B (balance ₹2000)

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;

A — Atomicity: "All or nothing"
If the system crashes after the first UPDATE (deducted from A) but before the second (not yet added to B), the transaction is automatically rolled back. The ₹1000 does not vanish. Implemented via: undo logs.

C — Consistency: "Valid state to valid state"
Before: A=5000, B=2000, total=7000. After: A=4000, B=3000, total=7000. Money is conserved. All constraints (NOT NULL, FK, CHECK) hold throughout. Implemented via: constraint checking at commit.

I — Isolation: "As if serial"
If another transaction reads A's balance concurrently during the transfer, it sees either 5000 (before) or 4000 (after commit) — never an inconsistent intermediate state. Implemented via: locking or MVCC.

D — Durability: "Committed = permanent"
Once COMMIT succeeds, the transfer persists even if the server loses power 1ms later. Implemented via: redo logs (Write-Ahead Log), writing changes to durable storage before confirming commit.

Transaction states: New → Active (executing) → Partially Committed (last statement done, not yet on disk) → Committed (success) / Failed → Aborted (rolled back).

START TRANSACTION;

INSERT INTO orders VALUES (101, 5, 'pending');
SAVEPOINT after_order;        -- mark a save point

INSERT INTO payments VALUES (201, 101, 500);
-- Payment processing fails here

ROLLBACK TO after_order;      -- undo payment only, keep order
COMMIT;                       -- order committed, payment rolled back

SAVEPOINT rules:

• ROLLBACK TO savepoint — undoes work done AFTER the savepoint, keeps work before it
• RELEASE SAVEPOINT — removes the savepoint (cannot rollback to it anymore)
• Plain ROLLBACK — undoes the ENTIRE transaction since START TRANSACTION
• COMMIT — makes all remaining changes permanent (savepoints disappear)

Three read anomalies to know:

• Dirty Read: T1 reads uncommitted data from T2. T2 then rolls back. T1 read data that never existed.
• Non-Repeatable Read: T1 reads a row. T2 updates and commits that row. T1 reads the same row again — gets a different value.
• Phantom Read: T1 runs a range query. T2 inserts a new row matching the range and commits. T1 runs the same query — gets an extra "phantom" row.

-- Set isolation level for current session (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;  -- reads committed data only
COMMIT;

Deadlock: Two or more transactions each holding a lock that the other needs — circular wait — none can proceed.

-- Classic DB deadlock:
-- T1: UPDATE accounts SET balance = balance - 100 WHERE id = 1;  (locks row 1)
-- T2: UPDATE accounts SET balance = balance - 100 WHERE id = 2;  (locks row 2)
-- T1: UPDATE accounts SET balance = balance + 100 WHERE id = 2;  (waits for T2's lock on row 2)
-- T2: UPDATE accounts SET balance = balance + 100 WHERE id = 1;  (waits for T1's lock on row 1)
-- DEADLOCK: both wait forever

Four Coffman Conditions (ALL must hold simultaneously):

1. Mutual Exclusion: Resources not sharable — only one transaction can hold a lock at a time
2. Hold and Wait: Transaction holds a lock while waiting for another
3. No Preemption: Locks cannot be forcibly taken from a transaction
4. Circular Wait: Chain of transactions each waiting for a resource held by the next

How databases handle deadlocks:

• Detection + Recovery (most common): DBMS builds a wait-for graph; if a cycle exists, it picks a "victim" transaction (usually the one with least work done) and kills it. The victim gets a deadlock error — application should retry.
• Prevention via lock ordering: Always acquire locks in the same order (e.g., always lock lower-numbered rows first). Eliminates circular wait.
• Timeouts: If a transaction waits longer than N seconds for a lock, abort it. Simple but may abort non-deadlocked transactions.

2PL Protocol: A concurrency control protocol that guarantees serializability (transactions appear to execute sequentially). All lock acquisitions happen before any lock releases.

Two phases:

• Growing phase: transaction acquires locks (shared for reads, exclusive for writes). Never releases any lock.
• Shrinking phase: transaction releases locks. Never acquires new ones.

-- Growing phase: T1 acquires locks
LOCK TABLE accounts READ;    -- shared lock for read
LOCK TABLE orders WRITE;     -- exclusive lock for write

-- Execute operations...
SELECT * FROM accounts WHERE ...;
UPDATE orders SET status = 'processed' WHERE ...;

-- Shrinking phase: T1 releases locks
UNLOCK TABLES;  -- all locks released together

Strict 2PL: Hold ALL locks until commit. Prevents cascading rollbacks (if T1 releases a lock early and T2 reads that data, T1's rollback forces T2's rollback too). Most databases implement Strict 2PL.

A checkpoint is a synchronization point where the DBMS flushes all dirty (modified, not yet written to disk) buffer pages from memory to disk AND records the current position in the transaction log.

Why it matters for crash recovery:

• After a crash, the DBMS must replay the transaction log to restore a consistent state
• Without checkpoints: must replay the ENTIRE log from the beginning — could be hours for a busy system
• With checkpoints: replay only starts from the last checkpoint — dramatically reduces recovery time

-- Recovery after crash (simplified):
-- 1. Read the log backwards from crash point to find last checkpoint
-- 2. Redo all committed transactions that were in-flight at checkpoint
-- 3. Undo all uncommitted transactions that were in-flight at checkpoint
-- Result: consistent state as of the last completed checkpoint + all committed work

Trade-off: More frequent checkpoints = faster recovery but higher runtime I/O cost (more disk writes). Less frequent = lower overhead but longer recovery time after a crash. Most databases checkpoint every few seconds to minutes.

A database index is a separate data structure (usually a B-tree) maintained alongside a table to speed up data retrieval — like a book's index letting you jump to a page without reading the whole book.

-- Create an index on frequently-searched column
CREATE INDEX idx_employees_email ON employees(email);

-- Composite index: speeds up queries filtering BOTH columns together
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);

-- Unique index: enforces uniqueness AND speeds up lookups
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Advantages:

• Dramatically faster SELECT with WHERE, JOIN, ORDER BY, GROUP BY on indexed columns
• Turns O(n) full table scan into O(log n) B-tree lookup
• Unique indexes enforce data integrity at the database level
• Can enable index-only scans — query satisfied entirely from the index without touching the table

Disadvantages:

• Slower writes: every INSERT/UPDATE/DELETE must update all affected indexes — 2–5x slower for write-heavy tables
• Extra disk space: each index can be 10–50% the size of the table
• Index maintenance: fragmentation over time requires periodic REBUILD/REORGANIZE
• Query planner confusion: stale statistics can cause the optimizer to choose a bad index

A B-tree (Balanced tree) is the default index structure in virtually all RDBMS. It is a self-balancing tree where all leaf nodes are at the same depth, guaranteeing O(log n) operations.

Structure:

• Root node: top-level node with key ranges guiding traversal
• Internal nodes: contain separator keys and pointers to child nodes
• Leaf nodes: contain the actual index keys + pointers to table rows (for non-clustered) or the actual row data (for clustered)
• Leaf nodes are linked: a doubly-linked list connects all leaf nodes — enables efficient range scans without backtracking up the tree

-- B+ tree for index on salary column (simplified):
--
--                [50000]
--               /        --       [30000]           [70000]
--       /     \           /     -- [10k,20k] [30k,40k] [50k,60k] [70k,80k]
--   |   |     |   |     |   |     |   |
-- row  row   row  row   row row  row  row
--  pointers to actual table rows (non-clustered)

-- Range query: WHERE salary BETWEEN 30000 AND 60000
-- 1. Traverse tree to find leaf with 30000 (3 hops for this tree)
-- 2. Scan linked leaf nodes rightward until > 60000
-- No full table scan needed!

Why B-trees are used for databases (not binary search trees): High branching factor (each node holds many keys) — a single disk page stores hundreds of keys, minimizing disk I/O. For 1 billion records, a B-tree is typically only 3–4 levels deep.

Analogy:

• Clustered: like a physical dictionary — words stored in alphabetical order ON THE PAGE ITSELF. Find "algorithm" → open to A section → definition is right there.
• Non-clustered: like a book's back index — look up "algorithm" → get page number (pointer) → flip to that page to read.

-- In MySQL InnoDB: PRIMARY KEY = Clustered Index (automatically)
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,   -- THIS is the clustered index
    name   VARCHAR(100),
    email  VARCHAR(100)
);
-- Rows are physically stored sorted by emp_id.
-- SELECT * WHERE emp_id = 42: one B-tree traversal, directly at the row.

CREATE INDEX idx_email ON employees(email);  -- non-clustered
-- SELECT * WHERE email = 'x@y.com':
-- Step 1: traverse email B-tree --> find emp_id = 42
-- Step 2: traverse primary key B-tree with emp_id = 42 --> find the row
-- This second lookup is called a "bookmark lookup" or "key lookup".

The query optimizer/planner automatically finds the most efficient execution plan for a SQL query. It considers: available indexes, table statistics (row counts, value distributions), join order, and available join algorithms (nested loop, hash join, merge join).

-- Use EXPLAIN to see the query plan (MySQL)
EXPLAIN SELECT e.name, d.dept_name
FROM employees e JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 50000;

-- Key columns in EXPLAIN output:
-- type:  const > ref > range > index > ALL (ALL = full table scan, avoid!)
-- key:   which index was used (NULL = no index!)
-- rows:  estimated rows examined
-- Extra: Using index, Using filesort, Using temporary (bad ones to watch)

-- EXPLAIN ANALYZE (PostgreSQL) - shows ACTUAL execution times
EXPLAIN ANALYZE SELECT * FROM employees WHERE email = 'x@y.com';

Common query optimization tips:

• Avoid functions on indexed columns in WHERE: WHERE YEAR(created_at) = 2024 cannot use index on created_at — use WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
• Use covering indexes: if the index contains all columns the query needs, the table is never touched
• Left-most prefix rule for composite indexes: index on (a, b, c) helps queries filtering on (a), (a,b), (a,b,c) — but NOT on (b) alone
• Keep statistics updated: ANALYZE TABLE employees refreshes the planner's statistics

MVCC (Multi-Version Concurrency Control): A concurrency control method where each write creates a new version of the row instead of overwriting the old one. Readers see a consistent snapshot from the start of their transaction — without blocking writers, and writers without blocking readers.

How it works (PostgreSQL model):

-- Each row has hidden system columns:
-- xmin: transaction ID that created this row version
-- xmax: transaction ID that deleted/updated this row version (0 = visible)

-- Initial state: row (id=1, salary=50000) created by T100
-- Row: id=1, salary=50000, xmin=100, xmax=0

-- T200 updates salary to 60000:
-- Old row: id=1, salary=50000, xmin=100, xmax=200  (marked deleted by T200)
-- New row: id=1, salary=60000, xmin=200, xmax=0     (new version by T200)

-- T300 started BEFORE T200 committed:
-- T300 sees xmax=200 but T200 has not committed yet when T300 started
-- T300 reads salary=50000 (the old version) -- snapshot isolation!

-- T400 started AFTER T200 committed:
-- T400 sees xmax=200, T200 committed, so old row is invisible
-- T400 reads salary=60000 (new version)

Benefits: reads never block writes, writes never block reads. Consistent snapshots for each transaction. Cost: old row versions accumulate (dead tuples in PostgreSQL). Requires VACUUM to reclaim space.

WAL (Write-Ahead Log): A logging strategy where all changes are written to an append-only log on durable storage BEFORE being applied to the actual data files. The fundamental rule: the log record must be on disk before the corresponding data change is considered committed.

-- WAL write sequence for a transaction:
-- 1. Transaction modifies data in memory buffer pool
-- 2. WAL record describing the change is written to disk (the log)
-- 3. COMMIT is acknowledged to the application
-- 4. Data page is written to disk later (background, in batches)
--
-- If crash happens between step 2 and step 4:
-- On restart, DBMS reads WAL and REDOES all committed changes
-- Data files are brought to a consistent committed state

WAL enables:

• Durability: once WAL record is on disk, the change is durable even if data file not yet written
• Crash recovery: replay WAL from last checkpoint — redo committed, undo uncommitted transactions
• Replication: streaming WAL to replica servers — PostgreSQL streaming replication, MySQL binlog
• Point-in-time recovery: replay WAL up to any specific moment in time

Performance benefit: Sequential writes to the WAL are much faster than random writes to data files. Fsync the WAL once per commit instead of fsync-ing potentially many random data pages.

A connection pool is a cache of pre-established database connections that are reused across requests instead of creating a new one each time.

Why connection creation is expensive:

• TCP handshake with the database server
• Authentication (username/password validation)
• Session setup (time zone, character set, search path)
• Total cost: 20–200ms per connection — unacceptable for a web request targeting <50ms

-- Without connection pool: creates/destroys connection per request
-- Request 1: create connection (100ms) + query (5ms) + close = 105ms
-- Request 2: create connection (100ms) + query (5ms) + close = 105ms
-- 1000 req/sec = 1000 connections per second created!

-- With connection pool (HikariCP, Java):
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost/mydb");
config.setMaximumPoolSize(20);          // max 20 connections
config.setMinimumIdle(5);               // keep 5 ready
config.setConnectionTimeout(30000);     // 30s timeout if pool exhausted
HikariDataSource ds = new HikariDataSource(config);

// Each request borrows a connection from pool (~1ms), returns it after
// 20 connections can serve thousands of concurrent requests

Configuration guidelines: max pool size ‸ CPU cores × 2 + effective disk spindle count (for I/O-bound). Too many connections overwhelm the DB; too few creates a bottleneck. Popular tools: HikariCP (Java), pgBouncer (PostgreSQL connection pooler), ProxySQL (MySQL).

Choose SQL when: complex joins needed, ACID required (financial, booking), schema is well-defined and stable, data is inherently relational.

Choose NoSQL when: need horizontal scale beyond what SQL can handle, schema is flexible or rapidly evolving, simple access patterns (get by ID), need to store documents/blobs, or need extreme write throughput (Cassandra: millions of writes/sec).

Real decision examples:

• Bank ledger → PostgreSQL (ACID critical, complex queries)
• User session store → Redis (ultra-fast KV, TTL support)
• Product catalog (varied fields) → MongoDB (flexible schema per product category)
• Social graph → Neo4j (graph traversal — "friends of friends" impossible in SQL)
• Clickstream analytics → Cassandra (massive write throughput, time-series)

In a distributed system, you can guarantee at most 2 of these 3 properties simultaneously:

• Consistency (C): every read returns the most recent write (or an error)
• Availability (A): every request gets a non-error response (though it may not be the latest data)
• Partition Tolerance (P): system continues operating despite network partitions (communication failures between nodes)

Why P is non-negotiable: In any distributed system, network partitions WILL happen. So the real choice is between C and A during a partition.

-- Analogy: bank with two branches, network goes down:
-- CP choice: reject all transactions until link restored
--            (customers cannot withdraw but no inconsistency)
-- AP choice: both branches keep serving, may make conflicting changes
--            (customer might overdraft by withdrawing from both branches)

Strong Consistency: After a write, all subsequent reads from any node see the new value immediately. Safe. Slower (requires coordination between nodes).

Eventual Consistency: All replicas will converge to the same value given no new writes — but reads may temporarily return stale data. Higher availability and performance.

Replication: Copying data to multiple database nodes (replicas/slaves). One primary accepts writes; replicas serve reads. Solves: read scalability, high availability, disaster recovery.

Sharding: Horizontal partitioning of data across multiple database instances. Each shard holds a subset of data. Solves: write scalability and storage beyond what one machine can handle.

-- Sharding by user_id (hash-based):
-- Shard 1: user_ids 0-999,999
-- Shard 2: user_ids 1,000,000-1,999,999
-- Shard 3: user_ids 2,000,000-2,999,999

-- Query "SELECT * FROM orders WHERE user_id = 1500000"
-- --> Goes directly to Shard 2 only (no fan-out needed)

-- Query "SELECT COUNT(*) FROM orders" (total count)
-- --> Must query ALL shards and sum results (fan-out + aggregate)

The Entity-Relationship (ER) Model is a conceptual data model used during database design to describe the structure of data before implementing in a RDBMS.

Core components:

• Entity: A real-world object or concept with independent existence. Represented as a rectangle. Example: Student, Course, Employee.
• Attribute: A property of an entity. Types: simple (name), composite (address = street + city + zip), derived (age derived from date of birth), multi-valued (phone numbers).
• Relationship: Association between entities. Represented as a diamond. Example: Student ENROLLS IN Course.
• Cardinality: How many entities participate in a relationship.

ER to Relational mapping: Entities become tables. Attributes become columns. Relationships become foreign keys or junction tables. The ER diagram is the design; the relational schema is the implementation.

CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,                    -- Entity integrity
    dept_id   INT REFERENCES departments(id),    -- Referential integrity
    salary    DECIMAL(10,2) NOT NULL              -- Domain: not null
              CHECK (salary > 0),                -- Domain: must be positive
    hire_date DATE NOT NULL,
    end_date  DATE,
    CONSTRAINT chk_dates                         -- User-defined
        CHECK (end_date IS NULL OR end_date > hire_date)
);

A data warehouse is a large, centralized repository of historical, integrated data from multiple source systems — structured specifically for analytics and reporting, not for transactional operations.

Key characteristics:

• Subject-oriented: organized around business subjects (sales, customers, products) rather than applications
• Integrated: data from many sources cleansed to consistent format, units, naming
• Time-variant: stores historical snapshots — always has a time dimension
• Non-volatile: data is loaded in bulk, not updated in place — read-heavy

ETL Pipeline: Extract data from OLTP sources → Transform (clean, join, aggregate) → Load into warehouse. Modern approach: ELT — load raw first, transform in the warehouse using SQL.