Top 72 Database Design / Normalization Interview Questions & Answers (2026)

A database is an organized collection of structured information or data stored electronically and managed by a Database Management System (DBMS). It allows data to be easily accessed, managed, modified, updated, controlled, and organized. Databases are fundamental to virtually all software applications — from social media platforms and banking systems to e-commerce sites and healthcare records. The most common type is the relational database, which organizes data into tables with rows and columns and uses SQL for querying.

A Database Management System (DBMS) is software that interacts with end users, applications, and the database itself to capture, store, and retrieve data. It provides an interface between users/applications and the database, handling data storage, security, backup, and concurrency. Types: Relational DBMS (RDBMS) — MySQL, PostgreSQL, Oracle, SQL Server; NoSQL DBMS — MongoDB, Cassandra, Redis; NewSQL — CockroachDB, Google Spanner. Key functions: data definition (DDL), data manipulation (DML), transaction management, and access control.

A relational database organizes data into tables (relations) with rows (tuples) and columns (attributes). Tables are linked to each other via keys (primary and foreign keys), establishing relationships. It is based on the relational model proposed by E.F. Codd in 1970. Data is queried using SQL (Structured Query Language). Relational databases enforce data integrity via constraints, support ACID transactions, and are excellent for structured data with well-defined schemas. Examples: MySQL, PostgreSQL, Oracle, SQL Server, SQLite.

A primary key (PK) is a column (or combination of columns) that uniquely identifies each row in a table. Primary key constraints: uniqueness (no two rows can have the same PK value) and NOT NULL (PK columns cannot be null). A table can have only one primary key. Types: natural key — a real-world attribute (e.g., email), and surrogate key — an artificial identifier (e.g., auto-increment integer id or UUID). Surrogate keys are preferred in practice because natural keys can change and are often longer, making joins less efficient.

A foreign key (FK) is a column in one table that references the primary key of another table, establishing a link between the two tables. It enforces referential integrity — ensuring that a value in the FK column must exist as a PK in the referenced table (no orphan records). Example: orders.customer_id is a foreign key referencing customers.id. FK constraints support cascade actions: ON DELETE CASCADE (deletes child rows when parent is deleted), ON DELETE SET NULL, and ON DELETE RESTRICT (prevents deletion if children exist).

Normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by dividing large tables into smaller, related tables. It follows a set of rules called normal forms (1NF, 2NF, 3NF, BCNF, 4NF, 5NF), each building on the previous. Benefits: eliminates data anomalies (update, insert, delete anomalies), reduces storage, and makes relationships explicit. The tradeoff: more JOINs are required to reconstruct data, which can impact read performance — this is why denormalization is sometimes applied strategically in high-read applications.

A table is in First Normal Form (1NF) when: 1) All column values are atomic (indivisible — no arrays, lists, or nested structures in a single column). 2) Each column holds values of a single type. 3) Each column has a unique name. 4) The order of rows does not matter. Example violation: a phone_numbers column containing "555-1234, 555-5678" (multiple values). Fix: create a separate phone_numbers table with one number per row. 1NF is the foundation all other normal forms build upon.

A table is in Second Normal Form (2NF) when it is in 1NF AND every non-key attribute is fully functionally dependent on the entire primary key — no partial dependencies. This only applies to tables with composite primary keys. Example: a table (order_id, product_id, product_name, quantity) — product_name depends only on product_id (partial dependency), not the full composite key. Fix: split into order_items(order_id, product_id, quantity) and products(product_id, product_name). Tables with single-column primary keys are automatically in 2NF if they are in 1NF.

A table is in Third Normal Form (3NF) when it is in 2NF AND there are no transitive dependencies — non-key attributes should not depend on other non-key attributes. Example: employees(emp_id, emp_name, dept_id, dept_name) — dept_name depends on dept_id (not the PK emp_id), creating a transitive dependency. Fix: split into employees(emp_id, emp_name, dept_id) and departments(dept_id, dept_name). The goal of 3NF is to ensure that every fact is stored in exactly one place — a key principle of good schema design.

Boyce-Codd Normal Form (BCNF) is a slightly stronger version of 3NF. A table is in BCNF if for every functional dependency X → Y, X is a superkey (a key that uniquely identifies rows). BCNF eliminates anomalies that 3NF can miss when there are multiple overlapping candidate keys. Example: a table (student, subject, teacher) where each teacher teaches one subject and each student-subject pair has one teacher. BCNF violations can arise when decomposition causes loss of information. BCNF is usually sufficient for practical design; 4NF and 5NF address multi-valued and join dependencies.

A database index is a data structure (typically a B-tree) that provides fast lookup of rows based on the values of one or more columns, similar to a book's index. Without an index, the database must scan every row (full table scan). With an index, it navigates the B-tree to find matching rows in O(log n) time. Create: CREATE INDEX idx_email ON users(email). Trade-offs: indexes speed up SELECT queries but slow down INSERT, UPDATE, DELETE (the index must be maintained). Always index: primary keys (automatic), foreign keys, and frequently filtered/sorted columns.

A clustered index determines the physical storage order of rows in the table — the table data is sorted and stored according to the clustered index key. Each table can have only one clustered index (the primary key is clustered by default in most RDBMS). Row lookup via the clustered index is fastest because the actual data is at the leaf node. A non-clustered index stores index key values and pointers (row locators) to the actual data rows separately — the table data is not reordered. A table can have many non-clustered indexes. Lookups require a pointer follow ("key lookup") after finding the index entry.

A composite index (also called a multi-column index) is an index on two or more columns. Example: CREATE INDEX idx_name_dept ON employees(last_name, dept_id). The order of columns matters: the index is most effective when queries filter on the leading column(s) first. A query filtering only on dept_id cannot use this index efficiently, but a query filtering on last_name alone or last_name + dept_id together can. This is called the leftmost prefix rule. Composite indexes are ideal for queries that frequently filter or sort by multiple columns together.

SQL (Structured Query Language) is the standard language for interacting with relational databases. It is divided into: DDL (Data Definition Language) — defines schema: CREATE, ALTER, DROP, TRUNCATE; DML (Data Manipulation Language) — manipulates data: SELECT, INSERT, UPDATE, DELETE; DCL (Data Control Language) — controls access: GRANT, REVOKE; TCL (Transaction Control Language) — manages transactions: COMMIT, ROLLBACK, SAVEPOINT. SQL is declarative — you describe what data you want, not how to retrieve it; the query planner figures out the execution plan.

A JOIN combines rows from two or more tables based on a related column. Types: INNER JOIN — returns only rows with matching values in both tables; LEFT (OUTER) JOIN — returns all rows from the left table and matched rows from the right (NULLs for no match); RIGHT (OUTER) JOIN — all rows from the right, matched from left; FULL OUTER JOIN — all rows from both tables; CROSS JOIN — Cartesian product (every row of A × every row of B); SELF JOIN — a table joined to itself (e.g., employees and their managers in the same table). Always specify JOIN conditions to avoid accidental Cartesian products.

DELETE removes specific rows filtered by a WHERE clause. It is DML, logs each row deletion (can be slow for large tables), can be rolled back in a transaction, and fires triggers. TRUNCATE removes all rows from a table by deallocating data pages — much faster than DELETE for clearing a table, minimally logged, cannot use a WHERE clause, resets identity/auto-increment counters, and in most databases cannot be rolled back. DROP removes the entire table structure (schema + data + indexes + constraints) permanently. DROP is DDL, cannot be rolled back in most RDBMS. Use: DELETE for selective removal, TRUNCATE for bulk clear, DROP for removing the table entirely.

A transaction is a unit of work that groups one or more SQL statements into a single atomic operation. All statements either succeed together (COMMIT) or are all undone (ROLLBACK). Transactions ensure the ACID properties: Atomicity, Consistency, Isolation, and Durability. Example: a bank transfer — debit one account and credit another — must be atomic; you cannot have the debit succeed without the credit. Syntax: BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;

ACID is a set of properties that guarantee reliable transaction processing: Atomicity — a transaction is all-or-nothing; partial completion is not allowed. Consistency — a transaction brings the database from one valid state to another, never leaving it in an invalid state (constraints are not violated). Isolation — concurrent transactions execute as if they were serial — intermediate states are not visible to other transactions. Durability — once a transaction is committed, it is permanently saved even in the event of a system failure (achieved via write-ahead logging). ACID is the foundation of correctness in relational databases.

Referential integrity ensures that relationships between tables remain consistent — a foreign key value in a child table must always match an existing primary key value in the parent table. It prevents orphan records (child rows with no matching parent). Enforced by the database via foreign key constraints. Violation examples: inserting an order for a non-existent customer, or deleting a customer who still has orders. Cascade options handle related row management: ON DELETE CASCADE, ON DELETE RESTRICT, ON DELETE SET NULL. Without referential integrity, data becomes inconsistent over time.

A schema is the logical structure that defines the organization of data in a database — the blueprint of tables, columns, data types, constraints, indexes, views, stored procedures, and relationships. It represents what data can be stored and how it is organized, without containing the actual data. In PostgreSQL and SQL Server, schemas are also namespaces that group database objects (tables, views) under a name (e.g., public.users, hr.employees). Schemas allow multiple logical groups of objects in one database, useful for multi-tenant applications or separating concerns (e.g., app, analytics, audit).

CHAR(n) is a fixed-length character type that always stores exactly n characters, padding with spaces if the input is shorter. It uses constant storage space (good for values of known fixed length like country codes "US", "UK"). VARCHAR(n) is a variable-length type that stores only the actual characters plus a small length overhead — more space-efficient for variable-length strings. Performance: CHAR can be marginally faster for fixed-width data in some engines since row sizes are predictable. In practice, use CHAR for fixed-length codes (ISO codes, status flags) and VARCHAR for names, emails, and other variable-length strings.

An Entity-Relationship (ER) diagram is a visual representation of the data model for a system. It shows entities (things in the domain — tables), attributes (properties of entities — columns), and relationships (how entities relate — foreign keys). Cardinality notation shows the multiplicity: 1:1 (one-to-one), 1:N (one-to-many), M:N (many-to-many). ER diagrams are used during the conceptual design phase to communicate the data model to stakeholders before implementing it in a specific RDBMS. Tools: dbdiagram.io, Lucidchart, draw.io, MySQL Workbench.

A view is a virtual table created by a stored SELECT query. It does not store data itself — it dynamically retrieves data from underlying tables each time it is queried. Benefits: simplify complex queries (encapsulate joins/aggregations), security (expose only certain columns to users), and data abstraction (hide table restructuring from application queries). Create: CREATE VIEW active_users AS SELECT * FROM users WHERE active = true. Materialized views (PostgreSQL, Oracle) store the query result physically and refresh on demand — faster reads at the cost of stale data.

A stored procedure is a pre-compiled, reusable block of SQL (and optionally procedural logic) stored in the database and executed by name. Benefits: code reuse, reduced network traffic (complex logic runs on the database server), performance (pre-compiled execution plan), and security (users execute the procedure without needing direct table access). Call with: EXEC procedure_name(param1, param2). Drawbacks: difficult to version control, test, and debug; tightly couples business logic to the database. Modern applications often move business logic to the application layer and use ORMs instead.

A trigger is a stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table. Triggers can fire BEFORE or AFTER the event and have access to the old and new row values (OLD and NEW pseudo-records). Common uses: audit logging (recording who changed what and when), enforcing complex business rules, automatically updating derived columns, and maintaining materialized data. Drawbacks: triggers are invisible to application code, making debugging difficult. Many teams prefer handling audit/business logic in the application layer for better observability and testability.

UNION combines the result sets of two SELECT queries and removes duplicate rows, requiring an implicit DISTINCT sort operation — slower because it must check for duplicates. UNION ALL combines result sets and includes all rows including duplicates — significantly faster since no deduplication is needed. Both require the same number of columns with compatible data types. Use UNION ALL when you know there are no duplicates (or duplicates are acceptable) to maximize performance. Example: combining results from archived_orders and current_orders tables where there can be no overlap.

Aggregate functions perform calculations on a set of rows and return a single value. Common functions: COUNT(*) — number of rows; SUM(column) — total of values; AVG(column) — average; MAX(column) — maximum; MIN(column) — minimum. Used with GROUP BY to compute aggregates per group: SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id. HAVING filters groups after aggregation (like WHERE for groups): HAVING COUNT(*) > 10. Aggregate functions ignore NULL values (except COUNT(*) which counts all rows).

GROUP BY divides query result rows into groups based on the values of one or more columns, typically to apply aggregate functions to each group. Every non-aggregated column in the SELECT list must appear in the GROUP BY clause. Query execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Example: SELECT category, COUNT(*) as product_count, AVG(price) as avg_price FROM products GROUP BY category HAVING COUNT(*) > 5 ORDER BY avg_price DESC. GROUP BY is fundamental for analytics and reporting queries.

A subquery (inner query or nested query) is a SELECT statement embedded within another SQL query. Types: scalar subquery — returns a single value: SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); row subquery — returns a single row; table subquery — returns multiple rows/columns, used with IN, EXISTS, or in the FROM clause (derived table). Subqueries run once (scalar/correlated exceptions aside). Correlated subqueries reference the outer query and run once per outer row — often slow; a JOIN may be more efficient.

WHERE filters rows before aggregation — it operates on individual rows and cannot reference aggregate functions. HAVING filters groups after GROUP BY aggregation — it can reference aggregate functions. Example: to find departments with more than 10 employees whose average salary exceeds 50,000: SELECT dept_id, COUNT(*), AVG(salary) FROM employees WHERE status = 'active' GROUP BY dept_id HAVING COUNT(*) > 10 AND AVG(salary) > 50000. WHERE filters out inactive employees before grouping; HAVING filters resulting groups after the COUNT and AVG are computed.

NULL represents the absence of a value — an unknown or inapplicable value. It is not zero, not an empty string, and not "false." NULL comparisons always return NULL (unknown), not true/false: NULL = NULL is NULL, not TRUE. Use IS NULL and IS NOT NULL for comparisons. NULL propagates through expressions: 100 + NULL = NULL. Aggregate functions ignore NULLs (except COUNT(*)). Best practices: use COALESCE(column, default) to substitute a default for NULL; add NOT NULL constraints to required columns; be careful with NULLs in JOINs and WHERE clauses as they can silently exclude rows.

A natural key is a column with real-world meaning that naturally identifies a row (e.g., email address, SSN, ISBN). It can be meaningful but may change over time (people change emails) and can be long (inefficient for joins). A surrogate key is an artificially generated identifier with no business meaning — typically an auto-incrementing integer (SERIAL/AUTO_INCREMENT) or UUID. Surrogate keys are immutable, compact (4–16 bytes), and uniform across tables. Best practice: use surrogate keys as primary keys for most tables, and add unique constraints on natural keys where needed for business uniqueness rules.

Data integrity ensures the accuracy, consistency, and reliability of data throughout its lifecycle. Types: Entity integrity — every table row is uniquely identified (primary key constraint). Referential integrity — foreign key values must match existing primary keys. Domain integrity — column values must be within valid ranges/types (data type, NOT NULL, CHECK constraints). User-defined integrity — business rules enforced via triggers, stored procedures, or application logic. Maintaining data integrity prevents corrupted or inconsistent data from entering the database, which can cause incorrect results and hard-to-debug bugs.

A table physically stores data on disk — rows are persisted and occupy storage. DML operations (INSERT, UPDATE, DELETE) directly modify the underlying data. Tables are the primary storage unit in a relational database. A view is a virtual table defined by a SELECT query — it stores the query definition, not the data. When you query a view, the database executes the underlying SELECT dynamically. Views cannot (usually) be directly inserted/updated unless they are simple views on a single table with no aggregations. Views abstract complexity, provide security by limiting column/row exposure, and simplify repeated complex queries.

An auto-increment (MySQL) or SERIAL (PostgreSQL) column automatically generates a unique integer value for each new row, typically used for surrogate primary keys. The database maintains a sequence counter that increments by 1 with each insert. In MySQL: id INT AUTO_INCREMENT PRIMARY KEY. In PostgreSQL: id SERIAL PRIMARY KEY (shorthand for SEQUENCE + integer column + DEFAULT). In SQL Server: IDENTITY(1,1). The generated value is accessible after insert via LAST_INSERT_ID() (MySQL) or RETURNING id (PostgreSQL). Auto-increment values are never reused after deletion.

A UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct — no two rows can have the same value. Unlike the primary key, a table can have multiple unique constraints, and unique constraint columns can contain NULL (most RDBMS treat multiple NULLs as not violating uniqueness). Create: ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email). A unique constraint implicitly creates a unique index, providing fast lookups. Use cases: enforcing uniqueness on email addresses, usernames, product codes, or any natural identifier that isn't the primary key.

SQL (relational) databases store data in structured tables with a predefined schema, use SQL for queries, enforce ACID transactions, and are excellent for complex queries with JOINs. Examples: PostgreSQL, MySQL, Oracle. NoSQL databases use flexible schemas (document, key-value, wide-column, graph models), are designed to scale horizontally, and prioritize availability and partition tolerance (BASE properties). Examples: MongoDB (document), Redis (key-value), Cassandra (wide-column), Neo4j (graph). Choose SQL for: complex relationships, strong consistency, and structured data. Choose NoSQL for: huge scale, flexible schema, or specialized data models.

Data anomalies are inconsistencies that arise from storing redundant data in poorly designed tables. Three types: Update anomaly — if the same fact is stored in multiple rows, updating one without updating others creates inconsistency (e.g., a department name stored in every employee row). Insert anomaly — cannot insert data without also including other unrelated data (e.g., cannot add a new department without an employee). Delete anomaly — deleting one fact accidentally deletes another (e.g., deleting the last employee in a department loses the department record). Normalization eliminates these by ensuring each fact is stored exactly once.

Denormalization is the intentional introduction of redundancy into a normalized schema to improve read performance. Examples: storing a pre-calculated order_total in the orders table (instead of summing line items each query), duplicating a user's name in a comments table (avoiding a JOIN), or maintaining a posts_count column on the users table. Denormalization trades write complexity (maintaining consistency of duplicated data) for faster reads and fewer JOINs. It is commonly used in: read-heavy applications, data warehouses/OLAP, caching layers, and document databases. Always normalize first, then denormalize specific bottlenecks based on profiling.

Transaction isolation levels define how concurrent transactions see each other's uncommitted changes. From weakest to strongest: Read Uncommitted — can read uncommitted changes (dirty reads possible). Read Committed — only reads committed data (default in PostgreSQL, Oracle) — prevents dirty reads but allows non-repeatable reads. Repeatable Read — same query returns the same rows within a transaction (default in MySQL InnoDB) — prevents dirty and non-repeatable reads but allows phantom reads. Serializable — transactions appear to execute serially — prevents all anomalies but has the most locking overhead. Higher isolation = more correctness but lower concurrency.

Dirty read: Transaction A reads data that Transaction B has modified but not yet committed. If B rolls back, A read invalid data. Prevented by Read Committed+. Non-repeatable read: Transaction A reads a row, Transaction B updates and commits it, then A reads the same row again and gets a different value. The same query returns different results within a transaction. Prevented by Repeatable Read+. Phantom read: Transaction A queries rows matching a condition, Transaction B inserts a new matching row and commits, then A re-runs the query and sees a new "phantom" row. Prevented only at Serializable level. Each requires progressively higher isolation to prevent.

The query optimizer is a component of the RDBMS that analyzes SQL queries and determines the most efficient way to execute them — choosing join algorithms, join order, and whether to use indexes. The query execution plan (or explain plan) is the optimizer's chosen strategy. Use EXPLAIN SELECT ... (MySQL/PostgreSQL) to see the plan. Key operators: Seq Scan (full table scan — potentially slow), Index Scan (uses index — fast), Hash Join, Nested Loop Join, Merge Join. Look for high row estimates, missing indexes, and expensive sorts. The optimizer uses table statistics (row counts, column cardinality) — run ANALYZE (PostgreSQL) to update them.

A covering index includes all columns referenced in a query (in WHERE, SELECT, ORDER BY, GROUP BY) so the database can satisfy the entire query from the index alone, without accessing the base table. This eliminates a "table heap fetch" for each row, significantly improving performance for read-heavy queries. Example: for SELECT name, salary FROM employees WHERE dept_id = 5, a covering index on (dept_id, name, salary) allows the query to be fully resolved from the index. Covering indexes are a powerful optimization tool but increase index size and write overhead, so use them strategically for identified slow queries.

Partitioning divides a large table into smaller physical segments while keeping it logically as one table. Types: Range partitioning — by value ranges (e.g., orders by year: 2022_orders, 2023_orders); List partitioning — by specific values (e.g., by country); Hash partitioning — hash of a key distributes rows evenly; Composite partitioning — combination. Benefits: query performance (partition pruning scans only relevant partitions), faster maintenance (drop a partition instead of deleting millions of rows), and better data lifecycle management. Supported natively in PostgreSQL, MySQL 8+, Oracle, SQL Server.

A materialized view stores the result of a complex query physically on disk, unlike regular views which execute the query each time. Reading a materialized view is as fast as reading a regular table. The trade-off: the stored data can become stale — it must be refreshed periodically (REFRESH MATERIALIZED VIEW view_name in PostgreSQL). Refresh strategies: on-demand, on a schedule, or automatically on base table change (some RDBMS). Use cases: expensive aggregations for dashboards, pre-joined data for reporting, and reducing load on OLTP tables by pre-computing analytics. PostgreSQL and Oracle support materialized views natively.

OLTP (Online Transaction Processing) handles real-time, high-volume, short transactional queries — INSERT, UPDATE, DELETE of individual records. Optimized for write performance, normalized schema, and low latency. Examples: order placement, banking transactions, user authentication. OLAP (Online Analytical Processing) handles complex analytical queries over large datasets — aggregations, grouping, historical analysis. Optimized for read performance, often uses denormalized star/snowflake schemas, and benefits from columnar storage. Examples: business intelligence dashboards, sales reporting, trend analysis. Many organizations have separate OLTP (production database) and OLAP (data warehouse) systems.

Star schema is a data warehouse design with a central fact table (containing measurable events like sales transactions) surrounded by dimension tables (descriptive attributes like product, customer, date). Dimension tables are denormalized for query simplicity. Queries are fast with fewer JOINs. Snowflake schema is a more normalized star schema — dimension tables are split into sub-dimensions (e.g., product → category → department). Snowflake reduces storage redundancy but requires more JOINs, making queries more complex. Star schema is preferred for most data warehouses for query performance; snowflake for storage efficiency and strict data integrity.

Replication copies data from one database server (primary/master) to one or more replica servers (secondary/slave) to improve availability, read scalability, and disaster recovery. Types: Synchronous replication — primary waits for replicas to confirm before committing (strong consistency, higher write latency). Asynchronous replication — primary commits immediately, replicas catch up (lower latency, risk of data loss on failover). Use cases: read replicas serve SELECT queries to distribute load; standby replicas for failover; geo-distributed replicas for low-latency global access. Replication does not replace backups — a corrupt write replicates to all replicas.

Connection pooling maintains a pool of pre-established database connections that are reused rather than opened and closed for each request. Opening a new database connection is expensive (TCP handshake, authentication, session setup — 20–200ms). A connection pool reduces this overhead dramatically. A pooler (PgBouncer for PostgreSQL, HikariCP for Java) manages the pool: when a request needs a connection, it borrows one from the pool; when done, it returns it. Key parameters: min_connections, max_connections, connection_timeout. Sizing the pool: too small causes queueing; too many exhausts the RDBMS's connection limit and increases memory pressure.

A Common Table Expression (CTE) is a temporary named result set defined within a WITH clause that can be referenced within the main query. It improves readability by breaking complex queries into named logical steps. WITH high_earners AS (SELECT * FROM employees WHERE salary > 100000) SELECT dept_id, COUNT(*) FROM high_earners GROUP BY dept_id. Recursive CTEs reference themselves and are used to traverse hierarchical data (organizational charts, file systems, bills of materials): WITH RECURSIVE subordinates AS .... CTEs are materialized once (in some RDBMS) or treated as an inline view — check your RDBMS documentation for optimization behavior.

Window functions perform calculations across a set of rows related to the current row (the "window"), without collapsing them into groups like GROUP BY. Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS/RANGE ...). Common functions: ROW_NUMBER() — sequential row number in partition; RANK() / DENSE_RANK() — rank with/without gaps for ties; LAG(col, n) / LEAD(col, n) — access previous/next row values; SUM() OVER () — running total; NTILE(n) — divide rows into n buckets. Window functions are invaluable for analytics: calculating running totals, percentile rankings, and comparing rows to their peers.

Vertical scaling (scale up) adds more resources to the existing database server — more CPU, RAM, faster storage. Simple to implement (no code changes needed), but has physical limits and creates a single point of failure. Works well for RDBMS since they are designed for single-server deployment. Horizontal scaling (scale out) adds more servers to distribute the load. For databases, this means replication (read scaling with replicas) or sharding (distributing rows across multiple servers). Horizontal scaling is complex — cross-shard queries, distributed transactions, and data consistency become challenges. NoSQL databases like Cassandra are designed for horizontal scaling from the ground up.

An ORM (Object-Relational Mapper) maps database tables to classes and rows to objects in your programming language, allowing you to query and manipulate data using the language's native paradigm instead of writing SQL. Examples: Hibernate (Java), ActiveRecord (Rails), SQLAlchemy (Python), Entity Framework (.NET), Eloquent (Laravel). Benefits: faster development, database abstraction (switch RDBMS with minimal code changes), protection against SQL injection (parameterized queries). Trade-offs: generated SQL can be inefficient or cause N+1 queries, learning curve of the ORM API, and complex queries are often cleaner in raw SQL. Most ORMs allow dropping to raw SQL when needed.

Optimistic locking assumes conflicts are rare. Multiple transactions read the same row simultaneously. Before saving changes, the transaction verifies the row hasn't been modified by comparing a version number or timestamp. If it has changed, the save fails with a conflict error and the client retries. No database locks are held during the transaction — high concurrency, low overhead. Pessimistic locking acquires an exclusive lock on the row when it is read (SELECT ... FOR UPDATE), preventing others from modifying it until the transaction completes. Guarantees no conflicts but serializes access, reducing concurrency. Use optimistic for high-read/low-conflict scenarios; pessimistic for high-conflict critical sections.

Achieving zero-downtime migrations requires making schema changes backward-compatible with the running application code. Strategy (expand-contract pattern): Phase 1 (Expand) — add the new column/table as nullable; deploy new app code that writes to both old and new structures. Phase 2 (Migrate) — backfill data in batches (never lock the table with a single UPDATE of millions of rows). Phase 3 (Contract) — remove old column/code once migration is verified. Specific techniques: adding columns is safe; renaming columns requires a multi-step process; removing NOT NULL from a column must be done after backfill; adding indexes concurrently (CREATE INDEX CONCURRENTLY in PostgreSQL) avoids locking.

A deadlock occurs when two or more transactions each hold a lock that the other needs, causing them to wait indefinitely. Example: Transaction A locks row 1 and wants row 2; Transaction B locks row 2 and wants row 1. Neither can proceed. Databases detect deadlocks automatically using a wait-for graph and resolve them by killing one transaction (the "deadlock victim") and rolling it back. Prevention strategies: consistent lock ordering (always lock resources in the same order), keep transactions short (minimize lock hold time), use lower isolation levels where appropriate, and lock at coarser granularity. Application code should always handle deadlock errors with a retry loop.

Database constraints enforce rules on the data stored in tables, ensuring integrity at the database level regardless of application code. Types: NOT NULL — column must have a value; UNIQUE — all values in a column (or group) must be distinct; PRIMARY KEY — uniquely identifies rows (NOT NULL + UNIQUE); FOREIGN KEY — referential integrity between tables; CHECK — custom condition must be true for all row values; DEFAULT — specifies a value when none is provided (technically not an integrity constraint). Constraints can be defined at column level (inline) or table level. Named constraints are easier to identify in error messages.

Selectivity measures how unique the values in an indexed column are — the ratio of distinct values to total rows. High selectivity (many distinct values, like email or UUID) means the index is very effective at narrowing down results quickly. Low selectivity (few distinct values, like a boolean is_active or a status column with 3 values) means the index returns a large fraction of rows — the query planner may choose a full table scan instead since following index pointers for 50% of rows is slower. Rule: indexes work best on high-cardinality columns. Index a low-selectivity column only in composite indexes where high-selectivity columns lead.

A self-join joins a table to itself, treating the same table as two separate tables with different aliases. Used to query hierarchical or relational data within a single table. Classic example: an employees table with a manager_id FK referencing id in the same table. SELECT e.name, m.name as manager_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id. Other use cases: finding pairs of rows with a relationship (employees in the same department: e1.dept_id = e2.dept_id AND e1.id < e2.id), or comparing consecutive rows (with ORDER BY and ROW_NUMBER). Recursive CTEs are often a cleaner alternative for deep hierarchies.

Sharding horizontally partitions data across multiple database servers (shards), each holding a subset of rows. A shard key (e.g., user_id, tenant_id) determines which shard stores each row. Sharding solves write scalability beyond a single server's capacity. Challenges: cross-shard queries (JOINs across shards require scatter-gather); cross-shard transactions (distributed transactions are complex and slow); hotspots (one shard receives disproportionate traffic — use consistent hashing or hash-based sharding); re-sharding (redistributing data as you add shards is disruptive); and operational complexity (monitoring and maintaining N databases). Consider sharding only after exhausting single-server optimization.

The CAP theorem (Brewer, 2000) states that a distributed database can guarantee at most two of three properties simultaneously: Consistency (C) — every read returns the most recent write or an error; Availability (A) — every request receives a response (not necessarily the latest data); Partition Tolerance (P) — the system continues operating despite network partitions. Since network partitions are unavoidable in distributed systems, the real choice is between CP (consistent but may be unavailable during a partition — HBase, Zookeeper) and AP (available but may serve stale data — Cassandra, DynamoDB). Traditional RDBMS prioritize CP. Many systems allow tunable consistency per operation.

Eventual consistency is a consistency model used in distributed systems where, in the absence of new updates, all replicas will converge to the same value given enough time. It sacrifices strong consistency (all reads return the latest write immediately) for higher availability and lower latency. Used by: DynamoDB, Cassandra, Couchbase. Example: you post a comment on social media and a friend in another region does not see it for a few seconds. For many applications (shopping cart, social feeds, DNS) eventual consistency is acceptable. For financial transactions, it is not. The application must handle cases where it reads stale data and design conflict resolution strategies (last-write-wins, vector clocks).

MVCC (Multi-Version Concurrency Control) allows concurrent transactions to read a consistent snapshot of the database without taking read locks. Each row has multiple timestamped versions; readers see the version valid at their transaction start time while writers create new versions. This means readers never block writers and vice versa — critical for OLTP performance. Used by PostgreSQL, Oracle, MySQL InnoDB. WAL (Write-Ahead Log) is a durability mechanism — every change is written to a sequential log file before it is applied to data pages. On crash recovery, the RDBMS replays the WAL to restore committed transactions. WAL also enables streaming replication by shipping log records to replicas.

4NF (Fourth Normal Form) requires a table to be in BCNF and have no non-trivial multi-valued dependencies. A multi-valued dependency X →→ Y exists when X determines a set of Y values independently of other attributes. Example: if Employee can have multiple skills and multiple languages independently, storing them in one table creates spurious combinations. Fix: separate into EmployeeSkill and EmployeeLanguage. 5NF (Fifth Normal Form / PJNF) addresses join dependencies — a table is in 5NF if every join dependency is implied by candidate keys. A violation means a table cannot be reconstructed from its projections without spurious rows. 4NF and 5NF violations are rare in practice and complex to identify — most designs stop at BCNF or 3NF.

A cursor is a database object that allows row-by-row processing of a query result set. Unlike set-based SQL (which processes all matching rows at once), cursors iterate one row at a time. Declared and used within stored procedures or procedural SQL blocks. Syntax (PostgreSQL): DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH NEXT FROM cur INTO vars; CLOSE cur;. Use cases: when row-by-row processing with complex conditional logic is unavoidable, ETL with per-row transformations. Drawbacks: very slow for large datasets (1000x slower than set-based), consumes server memory, and holds locks longer. Almost always a set-based SQL rewrite (CTEs, window functions, bulk UPDATE) is preferable over cursors.

LIKE '%keyword%' does a substring scan — it cannot use indexes (leading wildcard disables index usage), is case-sensitive by default, and has no relevance ranking. Full-text search builds an inverted index mapping each word to the rows containing it — searches are O(log n) instead of O(n), support stemming ("run" matches "running"), stop word removal, synonym matching, and relevance ranking (TF-IDF). PostgreSQL full-text search: to_tsvector('english', body) @@ to_tsquery('search & terms'). MySQL: MATCH(col) AGAINST ('term'). For advanced needs, dedicated engines like Elasticsearch or Meilisearch offer richer features (fuzzy matching, faceted search, multi-language).

Change Data Capture (CDC) tracks and captures every insert, update, and delete made to database tables, making these change events available to downstream systems in near real-time. Implementation methods: WAL-based CDC (reads the database's write-ahead log directly — minimal overhead, used by Debezium with PostgreSQL and MySQL binlog); trigger-based CDC (triggers write changes to a history table — more intrusive); timestamp-based polling (periodically polls for rows with updated_at > last_run — misses deletes). CDC enables: event sourcing, data synchronization, real-time analytics pipelines (Kafka + Debezium), cache invalidation, and audit logging. Popular tools: Debezium, AWS DMS, Fivetran.

A heap table stores rows in no particular order — rows are inserted wherever free space is available. The clustered index (if present) stores pointers back to the heap. In PostgreSQL, all tables are heap tables. In MySQL InnoDB, all tables are index-organized tables (IOT): the primary key IS the clustered index and rows are stored in B-tree order by primary key. IOTs make primary key lookups very fast (single tree traversal to data) but secondary index lookups require two traversals (secondary B-tree to PK, then PK B-tree to data). Choosing a good primary key in InnoDB matters: sequential PKs (AUTO_INCREMENT) cause sequential writes; random PKs (UUIDs) cause random writes and page fragmentation.

Key database anti-patterns: EAV (Entity-Attribute-Value) — storing attributes as rows in a key-value table instead of columns — flexible but destroys query performance and type safety; God table — one massive table with hundreds of columns for everything; Jaywalking — storing multiple values in a single delimited column instead of a proper relation; Implicit columns — using SELECT * in application queries; Soft deletes without care — a deleted_at column that you forget to filter, causing stale data to appear; Missing foreign keys — relying on application code for referential integrity; Over-indexing — adding indexes on every column, slowing writes; Using NULLs for missing meaning — distinguishing "unknown" from "not applicable" with a single NULL.