Database Engineering: SQL, NoSQL & Vector DBs

Answer key & explanations

Every question in this quiz, with the correct answer marked and an explanation of why it is right. Use it to revise before or after taking the quiz above.

1. 1.Why are B+ Trees predominantly preferred over B Trees for relational database indexes (such as in InnoDB)?

   • AB+ Trees store all actual record pointers in leaf nodes linked sequentially, making range scans efficient✓ Correct
   • BB+ Trees consume significantly less disk space than hash indexes
   • CB+ Trees execute write operations in O(1) constant time
   • DB+ Trees prevent the need for write-ahead logging

   Correct answer: B+ Trees store all actual record pointers in leaf nodes linked sequentially, making range scans efficient

   In a B+ Tree, non-leaf nodes store only keys for navigation, while all actual data/pointers reside in sequentially linked leaf nodes, making range traversals fast.

2. 2.How does a Log-Structured Merge-tree (LSM-Tree) optimize write performance compared to a B-Tree?

   • AIt executes writes into random disk sectors directly
   • BIt buffers writes sequentially in memory (MemTable) before flushing immutable SSTables to disk sequentially✓ Correct
   • CIt compresses data using lossy compression algorithms
   • DIt avoids writing data to durable storage until the database shuts down

   Correct answer: It buffers writes sequentially in memory (MemTable) before flushing immutable SSTables to disk sequentially

   LSM-Trees append writes sequentially to a write-ahead log and an in-memory MemTable, avoiding expensive in-place random updates by batching and flushing sorted SSTables sequentially.

3. 3.What concurrency phenomenon can occur under the 'Repeatable Read' isolation level in standard ANSI SQL?

   • ADirty Reads
   • BNon-Repeatable Reads
   • CPhantom Reads✓ Correct
   • DLost Updates on unrelated rows

   Correct answer: Phantom Reads

   Repeatable Read guarantees that read rows do not change between queries, but concurrent transactions can still insert new matching rows that appear in subsequent scans (Phantom Reads).

4. 4.What does Multi-Version Concurrency Control (MVCC) enable in modern databases like PostgreSQL?

   • AWriting queries in multiple programming languages simultaneously
   • BReaders do not block writers, and writers do not block readers, by maintaining multiple row snapshots✓ Correct
   • CAutomatically sharding relational tables across physical storage devices
   • DPreventing hardware failures through real-time memory duplication

   Correct answer: Readers do not block writers, and writers do not block readers, by maintaining multiple row snapshots

   MVCC tracks transaction IDs on rows to present consistent historical snapshots, allowing readers to view stable states without acquiring read locks that block concurrent writes.

5. 5.What problem does an Inverted Vector File (IVF) index solve in vector databases like Milvus or Pinecone?

   • AIt converts floating-point vectors into natural language sentences
   • BIt partitions vector space into Voronoi cells via clustering to narrow the search scope for approximate nearest neighbors✓ Correct
   • CIt stores dense vector embeddings inside standard B-Tree indexes
   • DIt guarantees 100% exact brute-force Euclidean distance matching

   Correct answer: It partitions vector space into Voronoi cells via clustering to narrow the search scope for approximate nearest neighbors

   IVF partitions high-dimensional vector spaces into clusters using k-means; queries are compared only against centroids and vectors within the closest centroids, accelerating searches.

6. 6.What is the purpose of Product Quantization (PQ) in large-scale vector similarity search?

   • ADecomposing high-dimensional vectors into smaller sub-vectors and quantizing them to drastically reduce RAM usage✓ Correct
   • BCategorizing commercial retail products inside search engines
   • CGenerating human-readable metadata tags for dense embeddings
   • DCalculating cosine similarity using integer multiplication exclusively

   Correct answer: Decomposing high-dimensional vectors into smaller sub-vectors and quantizing them to drastically reduce RAM usage

   Product Quantization slices vectors into lower-dimensional sub-vectors and represents them with compact centroid codes, shrinking vector memory footprints by 80-95%.

7. 7.In distributed NoSQL systems like Apache Cassandra, what does the replication factor determine?

   • AThe number of CPU cores dedicated to query processing
   • BThe total number of nodes across the cluster that store copies of a specific row partition✓ Correct
   • CThe maximum number of tables allowed per keyspace
   • DThe speed at which SSD compaction cycles execute

   Correct answer: The total number of nodes across the cluster that store copies of a specific row partition

   The replication factor defines how many physical cluster nodes will maintain copies of a given partition key across the ring topology.

8. 8.What is the primary trade-off when configuring a write-heavy database with unbuffered, synchronous Write-Ahead Logging (WAL fsync)?

   • AIncreased query complexity requiring specialized SQL syntax
   • BSubstantially increased write latency due to blocking on physical disk rotational/flash flushes✓ Correct
   • CLoss of transaction durability during sudden server power loss
   • DInability to maintain foreign key referential integrity

   Correct answer: Substantially increased write latency due to blocking on physical disk rotational/flash flushes

   Calling fsync() on every transaction forces data out of volatile OS caches to non-volatile disk media, ensuring durability (ACID) at the cost of disk write latency.

9. 9.What is a 'covering index' in relational SQL optimization?

   • AAn index encrypted with an external hardware security module
   • BAn index that contains all columns requested by a specific query, eliminating the need to read the base table heap✓ Correct
   • CA full-text search index spanning every column in a database schema
   • DAn index placed exclusively on primary key columns

   Correct answer: An index that contains all columns requested by a specific query, eliminating the need to read the base table heap

   A covering index contains all the columns needed to resolve a query (via indexed or included columns), allowing the engine to return results purely from the index tree without fetching heap pages.

10. 10.What failure scenario does the Write-Ahead Log (WAL) protect against in relational databases?

    • ANetwork degradation across internet service providers
    • BSystem crashes and power loss before modified dirty pages in the buffer pool are written to data files✓ Correct
    • CSQL injection attacks against user authentication forms
    • DAccidental deletion of database schemas by human administrators

    Correct answer: System crashes and power loss before modified dirty pages in the buffer pool are written to data files

    The WAL logs all changes sequentially to durable storage before dirty in-memory buffer pool pages are flushed; on recovery, the database replays the log to restore consistency.

11. 11.In MongoDB, what is the consequence of setting write concern to 'w: 1' versus 'w: majority'?

    • A'w: 1' guarantees zero data loss in the event of an immediate primary node failure
    • B'w: 1' acknowledges writes as soon as the standalone primary writes to memory, risking data loss during failovers✓ Correct
    • C'w: majority' writes to disk on all nodes before returning
    • D'w: majority' disables replica set replication entirely

    Correct answer: 'w: 1' acknowledges writes as soon as the standalone primary writes to memory, risking data loss during failovers

    'w: 1' acknowledges as soon as the primary node writes (often in memory), risking rollbacks if the primary crashes before replicating; 'w: majority' requires a majority of replicas to acknowledge.

12. 12.What does write amplification describe in the context of database storage engines?

    • AThe volume of audio data recorded during voice queries
    • BThe amount of physical data written to disk relative to the logical data size of the original write operation✓ Correct
    • CThe multiplication of write queries distributed across sharded nodes
    • DThe compression ratio achieved when dumping database backups

    Correct answer: The amount of physical data written to disk relative to the logical data size of the original write operation

    Write amplification measures how many bytes are physically written to durable storage relative to the logical payload; high amplification occurs during B-Tree splits and LSM-Tree compactions.

13. 13.What is the primary role of a Bloom filter in LSM-Tree-based databases (e.g., RocksDB, Cassandra)?

    • ATo permanently compress SSTable blocks on disk
    • BTo rapidly verify whether an SSTable definitely does not contain a specific key without performing disk I/O✓ Correct
    • CTo sort records alphabetically in memory prior to flushing
    • DTo encrypt user password fields with one-way salted hashing

    Correct answer: To rapidly verify whether an SSTable definitely does not contain a specific key without performing disk I/O

    Bloom filters are probabilistic in-memory data structures that can definitively confirm if a key is absent from an on-disk SSTable, sparing unnecessary disk seek operations.

14. 14.In document databases, what is the trade-off between normalized (referenced) data modeling and denormalized (embedded) data modeling?

    • ANormalized data yields faster single-document writes; denormalization prevents the use of indexes
    • BEmbedding enables fast, single-read retrievals but risks update anomalies and document size growth; referencing avoids duplicates but requires multi-query joins✓ Correct
    • CEmbedding is restricted strictly to relational schemas
    • DReferencing completely prevents concurrent read operations

    Correct answer: Embedding enables fast, single-read retrievals but risks update anomalies and document size growth; referencing avoids duplicates but requires multi-query joins

    Embedding co-locates related data for fast atomic reads but risks document size limits and update anomalies, whereas referencing avoids redundancy at the expense of application-level joins.

15. 15.What is the difference between an exact k-Nearest Neighbors (k-NN) search and an Approximate Nearest Neighbors (ANN) search?

    • AExact k-NN calculates distances to every vector in the dataset (linear complexity); ANN uses heuristics to find close matches much faster✓ Correct
    • BANN search can only be executed on integer data arrays
    • CExact k-NN runs in O(log N) time on unindexed datasets
    • DANN search is restricted to two-dimensional Cartesian planes

    Correct answer: Exact k-NN calculates distances to every vector in the dataset (linear complexity); ANN uses heuristics to find close matches much faster

    Exact k-NN computes distance across every single vector (brute-force linear scan, O(N)), which becomes unfeasible at scale; ANN trades 100% recall for fast sub-linear query execution using graph or clustered indexes.

16. 16.What is the purpose of database sharding?

    • ACompressing cold historical tables into read-only zip files
    • BHorizontally partitioning a dataset across multiple independent database instances to scale beyond single-node compute and storage limits✓ Correct
    • CReplicating identical full copies of data to every server in an office building
    • DConverting relational schemas into non-relational graph networks

    Correct answer: Horizontally partitioning a dataset across multiple independent database instances to scale beyond single-node compute and storage limits

    Sharding splits massive datasets into partitions distributed across multiple separate database servers, enabling horizontal scaling when single-node vertical scaling limits are reached.

More free quizzes

• ITDistributed Systems: Consensus & Fault ToleranceDeep dive into distributed systems engineering, covering consensus protocols (Raft/Paxos), the CAP theorem, and vector clocks.16 questions
• ITLLM Engineering: Fine-Tuning, Alignment & ServingTest your knowledge of transformer architectures, PEFT techniques (LoRA), RLHF, vLLM, and production deployment strategies.16 questions
• ITApplied AI: Systems, Embeddings & RAGAssess your understanding of practical machine learning architectures, embedding spaces, and Retrieval-Augmented Generation systems.16 questions