1. N+1 Problem
Question: What is the N+1 problem in ORMs and how do you fix it?
The N+1 problem occurs when code needs to load the children of a parent-child relationship (the "one"), and then issues N additional queries to load the children.
Fix: Use eager loading (e.g., select_related or prefetch_related in Django, joinedload in SQLAlchemy) to fetch data in a single query.
2. ACID Properties
Question: Explain ACID properties in databases.
- Atomicity: All or nothing.
- Consistency: Database remains in a valid state.
- Isolation: Concurrent transactions don't interfere.
- Durability: Committed transactions are permanent.
3. Indexing
Question: How do database indexes work and what is the trade-off?
Concept: An index is a data structure (usually a B-Tree) that improves the speed of data retrieval operations on a table.
Trade-off: While indexes speed up SELECT queries, they slow down INSERT, UPDATE, and DELETE operations because the index must be updated whenever the data changes. They also consume additional disk space.
4. Normalization
Question: What is Database Normalization?
The process of organizing data to reduce redundancy and improve data integrity.
- 1NF: Atomic values (no lists in a cell).
- 2NF: No partial dependencies (all columns depend on the full primary key).
- 3NF: No transitive dependencies (columns depend only on the key, not on other non-key columns).
5. Transaction Isolation Levels
Question: What are the common Transaction Isolation Levels?
- Read Uncommitted: Dirty reads allowed (lowest isolation).
- Read Committed: Prevents dirty reads (default in Postgres).
- Repeatable Read: Prevents dirty and non-repeatable reads (default in MySQL).
- Serializable: Strict serial execution (highest isolation, lowest performance).