More in Databases & Architecture — page 7
3NF versus BCNF and the overlapping-key gap
WHAT IT TESTS: precise grasp of functional dependencies and candidate keys. OUTLINE: BCNF requires every determinant be a superkey; 3NF allows exceptions for prime attributes. RED FLAG: claiming 3NF and BCNF are always equivalent.
Adjacency List versus Nested Set for hierarchies
WHAT IT TESTS: read-versus-write trade-offs in tree storage. OUTLINE: adjacency list is simple writes but recursive reads; nested set is fast subtree reads but costly writes. RED FLAG: ignoring recursive CTEs or the wide updates nested sets need.
Modeling one-to-many versus many-to-many relationships
WHAT IT TESTS: cardinality and junction-table modeling. OUTLINE: one-to-many uses a foreign key on the many side; many-to-many needs a junction table with two foreign keys. RED FLAG: storing comma-separated IDs instead of a junction table.
Normalizing a flat orders table to 3NF
WHAT IT TESTS: applying 1NF, 2NF, 3NF rules concretely. OUTLINE: split repeating data, remove partial dependencies, remove transitive dependencies, define keys. RED FLAG: jumping to tables without naming which dependency each step removes.
When to intentionally denormalize a schema
WHAT IT TESTS: trading read speed for write complexity deliberately. OUTLINE: identify read-heavy join cost, duplicate or precompute data, and own the consistency burden. RED FLAG: denormalizing prematurely or ignoring how duplicates drift.
Diagnosing and fixing the N+1 query problem
WHAT IT TESTS: spotting hidden per-row queries from lazy loading. OUTLINE: define the 1 parent plus N child queries, fix via JOIN or batched IN, and ORM eager loading. RED FLAG: solving it only with caching while ignoring round-trip count.
Read Committed versus Serializable isolation levels
WHAT IT TESTS: grasp of concurrency anomalies versus consistency cost. OUTLINE: name the four levels, map each anomaly (dirty read, non-repeatable read, phantom) to the level that blocks it. RED FLAG: claiming Serializable blocks only phantoms.
Materialization and Pipelining
Two query-execution strategies: materialization writes each operator's full output to disk before the next reads it, while pipelining streams tuples operator-to-operator without intermediate storage.
What is the difference between WHERE and HAVING in SQL?
Tests SQL execution order and aggregation. A strong answer states WHERE filters rows before grouping, HAVING filters groups after aggregation, and gives an aggregate example that WHERE cannot evaluate.
Find users who never placed an order and explain JOIN choice
This tests SQL anti-joins and NULL semantics. A strong answer uses LEFT JOIN with IS NULL or NOT EXISTS, explains why NOT IN is risky with NULLs, and why NOT EXISTS is preferred. Red flag: using INNER JOIN or ignoring NULLs.
Explain database indexes, the classic data structure, and write-heavy trade-offs
Tests the read-write trade-off of indexing. A strong answer names B-Trees, explains they avoid full scans, and notes that inserts, updates, and deletes must update the index, adding write amplification and storage cost. Red flag: claiming indexes are free.
Describe 1NF, 2NF, 3NF, normalization's purpose, and its performance trade-off.
WHAT IT TESTS: Linking forms to anomaly prevention and join overhead. OUTLINE: 1NF atomic values; 2NF no partial dependencies; 3NF no transitive dependencies; prevents update anomalies but adds join overhead. RED FLAG: Jargon without linking to anomalies.
What is the difference between DDL and DML in SQL?
WHAT IT TESTS: Your grasp of the schema-versus-data boundary. ANSWER OUTLINE: DDL shapes schema with CREATE or ALTER; DML handles row-level data with SELECT, INSERT, UPDATE, or DELETE. RED FLAG: Labeling SELECT as DDL or insisting DDL never affects data.
Explain ACID properties and why they matter for banking or e-commerce
WHAT IT TESTS: Mapping ACID to real failure modes in finance. ANSWER OUTLINE: Define each as a failure-handling guarantee; show how partial commits cause double-spending. RED FLAG: Vague definitions that skip Isolation levels or Durability details.
What is the difference between primary, foreign, and unique keys?
This tests relational integrity basics. Answer: primary keys identify rows, foreign keys reference tables, and unique keys are alternate candidates. Red flag: saying unique keys are just for indexing or omitting a non-PK example like email.
Relevance Ranking: Sorting Results by Likely Usefulness
Relevance ranking orders results by how well they satisfy query intent, not just keyword overlap. It powers ecommerce, documentation, and log search. The footgun is chasing click-through over task completion, which surfaces popular but wrong answers.
Backpressure: Slow the Producer or Crash
Backpressure is a feedback signal telling upstream to slow down when downstream cannot keep up. You see it in stream processors like Flink or Kafka where a slow consumer risks memory exhaustion. Ignore it and queues grow until the service crashes.
Stream-Table Duality: Two Views of One Dataset
A table is a snapshot; a stream is the changelog that built it. The same data can be viewed either way: tables answer what is true now, while streams capture every change that led there. Treating them as separate systems is the expensive footgun.
The N+1 Query Problem
N+1 means fetching one record, then looping to query its relations one by one. It explodes latency in ORM code that looks innocent, turning a page load into hundreds of round-trips. The fix is eager loading, yet developers often miss it until production melts.
Leaderless Replication: No Master, No Bottleneck
Leaderless replication lets any node accept writes, skipping a single leader bottleneck. Systems like Dynamo stay available during partitions, reconciling conflicts with vector clocks later.