Top 30 Intermediate Databases & Architecture Concepts Quiz

30 questions. Pick an answer, or open “Show the answer” to read it.

Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.

1. Question 1 of 30

   What primary challenge did the relational model address compared to earlier database systems?

   • aDifficulty in representing data with complex, hierarchical relationships
   • bInability to handle concurrent access from multiple applications
   • cThe need for complex procedural code to navigate specific data storage paths
   • dLack of support for storing unstructured or semi-structured data types
   Show the answer

   Answer: c · The need for complex procedural code to navigate specific data storage paths

   The card states that before the relational model, "Retrieving data required writing complex procedural code to navigate specific data paths," which was inflexible. The relational model aimed to separate the logical data structure from its physical implementation. Option A is a distractor because while hierarchical models were predecessors, the core problem was the procedural navigation within them, not just the representation of hierarchies.

   Read the full bite: The Relational Model: Data as Simple Tables

2. Question 2 of 30

   In a bank transfer, which ACID property guarantees that if the transaction fails midway, both accounts revert to their original state?

   • aIsolation
   • bConsistency
   • cDurability
   • dAtomicity
   Show the answer

   Answer: d · Atomicity

   Atomicity ensures that a transaction is treated as an 'all or nothing' operation; if any part fails, the entire transaction is rolled back. Consistency, while ensuring the database moves from one valid state to another, does not specifically handle the rollback of an incomplete operation in the same way Atomicity does.

   Read the full bite: ACID: The Four Guarantees of Database Transactions

3. Question 3 of 30

   What is the main objective of applying database normalization in a relational database?

   • aTo ensure data integrity and minimize redundancy by structuring data into smaller, related tables.
   • bTo facilitate the storage and retrieval of unstructured data types, such as documents or media files.
   • cTo consolidate all related data into a single, comprehensive table for easier access.
   • dTo optimize complex analytical queries by reducing the need for JOIN operations.
   Show the answer

   Answer: a · To ensure data integrity and minimize redundancy by structuring data into smaller, related tables.

   Normalization's primary goal is to eliminate data redundancy and prevent modification anomalies (update, insertion, deletion), thereby ensuring data integrity. Option D describes a benefit of denormalization, often used in analytical systems, which is the opposite of normalization's typical effect on JOINs.

   Read the full bite: Database Normalization: Tidy Tables, Less Redundancy

4. Question 4 of 30

   By default, what action does a relational database take when a user attempts to delete a record from a "parent" table that is referenced by a foreign key in a "child" table?

   • aIt converts the foreign key in the child table into a primary key to preserve the relationship.
   • bIt automatically sets the foreign key column in the child table to NULL for all referencing records.
   • cIt blocks the deletion of the parent record to maintain data integrity.
   • dIt automatically deletes all corresponding referencing records in the child table.
   Show the answer

   Answer: c · It blocks the deletion of the parent record to maintain data integrity.

   The card explicitly states that if you try to delete a user referenced by posts, "the database will, by default, block the deletion to avoid creating orphaned posts." This is the standard behavior to enforce referential integrity. Option B describes an 'ON DELETE SET NULL' action, which is a configurable behavior, not the default.

   Read the full bite: Foreign Keys: The Glue of Relational Databases

5. Question 5 of 30

   What is the main performance trade-off introduced by adding a database index?

   • aIt requires manual updates to maintain data consistency.
   • bIt increases the complexity of database schema design.
   • cIt makes queries on non-indexed columns significantly slower.
   • dIt speeds up data retrieval but slows down data modification operations.
   Show the answer

   Answer: d · It speeds up data retrieval but slows down data modification operations.

   The card explicitly states that indexes speed up data retrieval but add overhead to INSERT, UPDATE, and DELETE operations, as the index structure must also be modified. This trade-off between faster reads and slower writes is the primary consideration. Indexes do not inherently complicate schema design, slow down non-indexed queries, or require manual updates for consistency.

   Read the full bite: Database Index: The Phonebook for Your Data

6. Question 6 of 30

   What is the primary distinction between a standard database view and a base table?

   • aA view executes a predefined query against base tables each time it is accessed, without storing its own data.
   • bA view can only be used to restrict access to specific rows, while a base table allows full access.
   • cA view physically stores its own copy of the data, unlike a base table.
   • dA view automatically optimizes complex joins, making it inherently faster than querying base tables directly.
   Show the answer

   Answer: a · A view executes a predefined query against base tables each time it is accessed, without storing its own data.

   A standard view stores only its query definition and executes it against the underlying base tables each time it's accessed, as explained in the 'How It Works' section. Option C describes a common misconception; views do not store their own data, unlike materialized views.

   Read the full bite: Database Views: A Saved Query That Acts Like a Table

7. Question 7 of 30

   In the context of database design, what does the functional dependency "A -> B" primarily signify?

   • aChanges to column A automatically trigger updates in column B across all records.
   • bColumn A is a foreign key that references column B.
   • cFor every unique value in column A, there is exactly one corresponding value in column B.
   • dColumn B can be derived or calculated directly from the values in column A.
   Show the answer

   Answer: c · For every unique value in column A, there is exactly one corresponding value in column B.

   A functional dependency A -> B means that if you know the value(s) in column A, you can uniquely determine the value(s) in column B. Option B describes a foreign key relationship, which is a different concept from functional dependency.

   Read the full bite: Functional Dependency: The Rules Behind Your Data

8. Question 8 of 30

   Which of the following describes a 2NF violation in a relation that is in 1NF and has a composite candidate key?

   • aA non-prime attribute is functionally dependent on only one attribute of the composite candidate key.
   • bA non-prime attribute depends on another non-prime attribute that is not part of any candidate key.
   • cA non-prime attribute is functionally dependent on the entire composite candidate key.
   • dA prime attribute is functionally dependent on a subset of the composite candidate key.
   Show the answer

   Answer: a · A non-prime attribute is functionally dependent on only one attribute of the composite candidate key.

   2NF eliminates partial dependencies, meaning every non-prime attribute must depend on the whole composite candidate key, not just a subset. Option B describes a transitive dependency, which violates 3NF, not 2NF.

   Read the full bite: Second Normal Form (2NF)

9. Question 9 of 30

   Which scenario represents a violation of Third Normal Form (3NF)?

   • aA non-key attribute in a table is fully dependent on another non-key attribute.
   • bA table has a composite primary key where some non-key attributes depend only on part of it.
   • cA table contains multiple values in a single column for a given row.
   • dA table lacks a foreign key relationship to another table.
   Show the answer

   Answer: a · A non-key attribute in a table is fully dependent on another non-key attribute.

   Third Normal Form (3NF) specifically addresses transitive dependencies, which occur when a non-key attribute depends on another non-key attribute instead of directly on the primary key. Options A and C describe violations of First Normal Form (1NF) and Second Normal Form (2NF), respectively.

   Read the full bite: Third Normal Form (3NF): Nothing But The Key

10. Question 10 of 30

    What is the primary advantage of employing a surrogate key as the primary key for a table containing core business entities?

    • aIt ensures that relationships between tables remain stable even if the real-world data they represent changes.
    • bIt guarantees absolute uniqueness across all databases and systems without any collisions.
    • cIt provides a human-readable and easily memorable identifier for external use.
    • dIt simplifies data querying by allowing direct joins on meaningful business attributes.
    Show the answer

    Answer: a · It ensures that relationships between tables remain stable even if the real-world data they represent changes.

    The card emphasizes that surrogate keys solve the stability problem by decoupling internal linking from volatile real-world data, preventing painful cascading updates. Option C is incorrect because surrogate keys are meaningless and exposing them externally is a 'footgun'.

    Read the full bite: Surrogate Keys: Stable IDs for Unstable Data

11. Question 11 of 30

    Which scenario best demonstrates the primary benefit of denormalization?

    • aA database designed to minimize disk space usage by eliminating redundant entries.
    • bA user profile system where user details are frequently updated by users.
    • cA financial system requiring immediate consistency for every transaction.
    • dAn analytics dashboard displaying pre-calculated sales trends over time.
    Show the answer

    Answer: d · An analytics dashboard displaying pre-calculated sales trends over time.

    Denormalization is ideal for read-heavy systems like analytics dashboards, where pre-calculating or duplicating data significantly speeds up frequent queries. It is generally avoided in write-heavy systems or when strict data integrity and minimal storage are paramount.

    Read the full bite: Denormalization: Trading Write Speed for Faster Reads

12. Question 12 of 30

    How does Two-Phase Locking (2PL) primarily ensure data consistency in concurrent transactions?

    • aBy acquiring all necessary locks in a growing phase before releasing any in a shrinking phase.
    • bBy allowing transactions to acquire new locks even after some have been released.
    • cBy rolling back any transaction that attempts to access data currently locked by another.
    • dBy immediately releasing locks once a data item is no longer needed to reduce contention.
    Show the answer

    Answer: a · By acquiring all necessary locks in a growing phase before releasing any in a shrinking phase.

    2PL's core mechanism involves a strict growing phase for acquiring all locks and a shrinking phase for releasing them, ensuring all locking precedes unlocking. This guarantees conflict-serializability, making concurrent operations appear sequential. Option C describes a general outcome of locking, not the specific two-phase mechanism that ensures serializability.

    Read the full bite: Two-Phase Locking (2PL): Preventing Database Race Conditions

13. Question 13 of 30

    When a database detects a deadlock between two transactions, what is its typical immediate action to resolve the situation?

    • aIt aborts one of the transactions, rolling back its changes to free up resources.
    • bIt automatically reorders the lock requests to prevent future deadlocks.
    • cIt pauses both transactions and prompts the user for intervention.
    • dIt grants both transactions access to the conflicting resources simultaneously.
    Show the answer

    Answer: a · It aborts one of the transactions, rolling back its changes to free up resources.

    The card explicitly states that the database 'breaks the stalemate by choosing one transaction as the 'victim,' aborting it, and rolling back all its changes.' Option B describes a prevention strategy that developers implement, not an automatic resolution action by the database during an active deadlock.

    Read the full bite: Database Deadlock: The Two-Way Standoff

14. Question 14 of 30

    Which of the following best describes the main advantage of Multi-Version Concurrency Control (MVCC)?

    • aIt minimizes storage requirements by maintaining only the latest committed state of each data record.
    • bIt completely removes the necessity for any locking mechanisms within the database.
    • cIt ensures that all transactions always operate on the most up-to-date version of data.
    • dIt enables read and write operations to occur concurrently without blocking each other.
    Show the answer

    Answer: d · It enables read and write operations to occur concurrently without blocking each other.

    The card explicitly states MVCC allows readers and writers to work concurrently without blocking each other. Option C is incorrect because transactions operate on a consistent snapshot from their start, not necessarily the absolute latest data.

    Read the full bite: MVCC: Read and Write Data Without Blocking Each Other

15. Question 15 of 30

    What is the main advantage of using Strict Two-Phase Locking (S2PL) in a database system?

    • aIt allows for faster release of locks, improving system responsiveness.
    • bIt prevents transactions from reading data that might later be rolled back.
    • cIt ensures that transactions can never encounter a deadlock situation.
    • dIt significantly increases the overall concurrency of transactions.
    Show the answer

    Answer: b · It prevents transactions from reading data that might later be rolled back.

    S2PL's core benefit is preventing transactions from seeing uncommitted work, thereby avoiding cascading aborts and ensuring strong consistency. Option D is incorrect because S2PL actually reduces concurrency due to longer lock durations.

    Read the full bite: Strict Two-Phase Locking (S2PL): Safety Over Speed

16. Question 16 of 30

    What is the main purpose of a database's query optimizer?

    • aTo enforce referential integrity and other constraints defined in the database schema.
    • bTo compile SQL statements into machine-readable bytecode for faster execution.
    • cTo automatically translate a declarative SQL query into the most efficient data retrieval strategy.
    • dTo cache frequently accessed data in memory to speed up subsequent requests.
    Show the answer

    Answer: c · To automatically translate a declarative SQL query into the most efficient data retrieval strategy.

    The query optimizer's core function is to take a declarative SQL query (what data is wanted) and determine the most efficient 'how' to retrieve it by selecting the best execution plan. While other options describe important database functions, they are not the primary role of the query optimizer, which focuses on execution strategy rather than data integrity, caching, or mere compilation.

    Read the full bite: The Query Optimizer: Your Database's Internal GPS

17. Question 17 of 30

    For a query filtering on both 'last_name' and 'first_name', what is the main benefit of a composite index on (last_name, first_name) compared to separate indexes on each column?

    • aIt optimizes queries that filter solely on the 'first_name' column.
    • bIt enables the database to efficiently merge results from two independent index scans.
    • cIt allows the database to perform a single, direct lookup in a pre-sorted, combined structure.
    • dIt significantly reduces the database's overall storage footprint for indexes.
    Show the answer

    Answer: c · It allows the database to perform a single, direct lookup in a pre-sorted, combined structure.

    A composite index creates a single B-Tree sorted by both columns, allowing for a direct, efficient lookup as described in the card's 'HOW IT WORKS' section. Option B describes the 'Index Merge Intersect' alternative, which the card explicitly states is slower than a direct composite index lookup.

    Read the full bite: Composite Indexes: One Index for Multiple Columns

18. Question 18 of 30

    Which scenario best justifies using a heap file organization for a database table?

    • aA staging table for ingesting large volumes of raw sensor data that will be processed later.
    • bA user session table where records are frequently updated with new activity timestamps.
    • cA critical lookup table where data integrity and rapid retrieval by primary key are paramount.
    • dA table designed for real-time analytics requiring complex joins and aggregations.
    Show the answer

    Answer: a · A staging table for ingesting large volumes of raw sensor data that will be processed later.

    Heap file organization prioritizes extremely fast writes, making it ideal for bulk-loading data into staging tables before further processing. It is unsuitable for tables requiring frequent reads, updates, or complex queries, as these operations necessitate slow full table scans.

    Read the full bite: Heap File Organization: Fast Writes, Slow Reads

19. Question 19 of 30

    What is the key mechanism enabling stable pointers to records in a slotted page structure?

    • aFree space is immediately compacted after deletions, shifting records to maintain contiguity.
    • bAn array of item pointers (slots) provides an unchanging reference number, each storing the current data offset.
    • cEach record is assigned a unique, immutable identifier that indexes directly reference.
    • dRecords are stored in fixed-size blocks, preventing any movement within the page.
    Show the answer

    Answer: b · An array of item pointers (slots) provides an unchanging reference number, each storing the current data offset.

    The slotted page structure uses an array of item pointers (slots) where external indexes refer to records by their stable slot number. If a record's physical location within the page changes, only the offset stored within that slot needs updating, not the slot number itself. Option A describes the inefficient behavior that slotted pages are designed to avoid, as shifting records would invalidate direct pointers.

    Read the full bite: Slotted Page Structure: Stable Pointers on Disk

20. Question 20 of 30

    What is the primary benefit of implementing database checkpoints?

    • aTo ensure that all database changes are immediately persisted to disk, guaranteeing zero data loss.
    • bTo improve query performance by frequently reorganizing data pages on disk.
    • cTo minimize the amount of transaction log processing required to restore the database after a system crash.
    • dTo prevent long-running, uncommitted transactions from blocking other database operations.
    Show the answer

    Answer: c · To minimize the amount of transaction log processing required to restore the database after a system crash.

    The card states that checkpoints reduce the amount of log data to process and allow the database to only replay transactions that occurred after the checkpoint, thereby reducing recovery time. Option A describes a scenario that checkpoints avoid to improve performance, as writing every change immediately is slow.

    Read the full bite: Database Checkpoints: Faster Recovery After a Crash

21. Question 21 of 30

    When does an operating system primarily invoke a page replacement algorithm?

    • aWhen the operating system needs to allocate additional virtual memory space to a running application.
    • bWhen a program requests a page not in physical memory, and RAM is full.
    • cWhen the CPU cache misses a requested data block and needs to fetch it from main memory.
    • dWhen a program attempts to access a memory address that is outside its virtual memory range.
    Show the answer

    Answer: b · When a program requests a page not in physical memory, and RAM is full.

    A page replacement algorithm is triggered when a program requests a page that isn't in physical RAM (a page fault) AND there's no free physical memory available, necessitating the eviction of an existing page. Option D describes a segmentation fault, which is a different memory error.

    Read the full bite: Page Replacement Algorithms: Evicting Data from Memory

22. Question 22 of 30

    Under which scenario would a database's query optimizer most likely choose a hash join?

    • aJoining two large, unsorted tables on an equality condition, with one table significantly smaller.
    • bJoining two large tables that are both pre-sorted on the join key.
    • cPerforming a join using a non-equality operator like '<' or '>'.
    • dJoining two small tables that both have indexes on the join key.
    Show the answer

    Answer: a · Joining two large, unsorted tables on an equality condition, with one table significantly smaller.

    A hash join is ideal for large, unsorted equijoins where one table is significantly smaller, allowing its hash table to fit in memory. For pre-sorted tables, a merge join is typically more efficient, and hash joins are unsuitable for non-equijoins.

    Read the full bite: Hash Join: Faster Database Joins with Hash Tables

23. Question 23 of 30

    What scenario makes a sort-merge join particularly efficient compared to other join methods?

    • aWhen the join condition is a non-equality comparison (e.g., A.id > B.id).
    • bWhen the database system has a large amount of available memory.
    • cWhen the data in both tables is already ordered by the join key.
    • dWhen one of the tables is small enough to fit entirely in memory.
    Show the answer

    Answer: c · When the data in both tables is already ordered by the join key.

    A sort-merge join is most efficient when the tables are already sorted on the join key, as this allows the database to skip the expensive initial sort phase. Option D is incorrect because a hash join is typically faster when one table fits in memory.

    Read the full bite: Sort-Merge Join: The 'Line Up and Walk' Join

24. Question 24 of 30

    What is the primary factor limiting the speed of External Merge Sort?

    • aThe amount of available RAM for buffering during the merge phase.
    • bThe number of CPU cores available for parallel processing.
    • cThe efficiency of the in-memory sorting algorithm used for chunks.
    • dThe speed of disk I/O operations.
    Show the answer

    Answer: d · The speed of disk I/O operations.

    The card explicitly states that "its speed is limited by disk I/O, not CPU." While RAM size and the efficiency of the in-memory sort are important for overall performance, the fundamental bottleneck for external merge sort is the slow speed of reading from and writing to disk.

    Read the full bite: External Merge Sort: Sorting Data Bigger Than RAM

25. Question 25 of 30

    When a database's query rewriter reorders operations like filtering and joining, what is its main objective?

    • aTo ensure the query strictly follows the order of operations specified by the user.
    • bTo achieve the identical final result set using a more performant execution strategy.
    • cTo reduce the amount of data returned to the client application, improving network latency.
    • dTo simplify the query's structure for easier debugging by developers.
    Show the answer

    Answer: b · To achieve the identical final result set using a more performant execution strategy.

    The card states the rewriter's goal is to transform queries into "faster equivalents" that "always produce the same final result" by reordering operations. Option A is incorrect because the rewriter often changes the user's written order to optimize performance.

    Read the full bite: Query Rewriting: Your Database's Unseen Optimizer

26. Question 26 of 30

    For which scenario would an architect most appropriately choose an eventually consistent database?

    • aStoring critical financial transaction records that require immediate, absolute accuracy.
    • bEnsuring all distributed nodes reflect the exact same bank account balance at the same microsecond.
    • cHandling user session data for a social media application where a slight delay in updates is tolerable.
    • dManaging real-time inventory for an e-commerce platform where overselling is unacceptable.
    Show the answer

    Answer: c · Handling user session data for a social media application where a slight delay in updates is tolerable.

    The card states that eventual consistency is suitable when high availability is paramount and temporary data staleness is acceptable, explicitly listing social media feeds and user session data as examples. Options A, B, and D describe scenarios requiring immediate, transactional accuracy, which the card identifies as poor candidates for eventual consistency.

    Read the full bite: Eventual Consistency: Availability Now, Correctness Later

27. Question 27 of 30

    Which characteristic primarily distinguishes a wide-column store from a relational database?

    • aIts capacity to allow each individual row to possess a unique and dynamic set of columns.
    • bIts ability to perform complex, multi-table joins efficiently.
    • cIts requirement for all rows in a table to adhere to a fixed, predefined schema.
    • dIts primary focus on strong ACID compliance for distributed transactions.
    Show the answer

    Answer: a · Its capacity to allow each individual row to possess a unique and dynamic set of columns.

    The core distinction of a wide-column store is its flexible schema, allowing each row to have its own unique set of columns, which contrasts with the fixed schema required by relational databases. Option C describes a relational database, not a wide-column store.

    Read the full bite: Wide-Column Store: Flexible Schema for Massive Datasets

28. Question 28 of 30

    Which application best leverages the core strength of a graph database?

    • aAnalyzing financial transactions to detect patterns of fraudulent activity across multiple accounts.
    • bStoring a large catalog of products with their prices and descriptions for e-commerce.
    • cAggregating website traffic logs to generate daily unique visitor counts.
    • dMaintaining employee records, including salary and department, for HR reporting.
    Show the answer

    Answer: a · Analyzing financial transactions to detect patterns of fraudulent activity across multiple accounts.

    Graph databases are designed to make relationships first-class citizens, excelling in scenarios like fraud detection where identifying complex, linked patterns between entities is critical. The other options describe use cases better suited for relational or analytical databases, focusing on simple record storage, tabular data, or aggregate queries rather than intricate relationships.

    Read the full bite: Graph Databases: When Relationships Are the Data

29. Question 29 of 30

    According to the card, which scenario makes database sharding a poor fit?

    • aWhen the application requires extremely high transaction rates.
    • bWhen queries frequently involve joining data that would reside on different shards.
    • cWhen the database is bottlenecked by write volume or storage capacity.
    • dWhen the system needs to support massive datasets.
    Show the answer

    Answer: b · When queries frequently involve joining data that would reside on different shards.

    The card explicitly states that sharding is a poor fit "if your queries frequently require joining data that would end up on different shards, as this negates the performance benefits and adds significant complexity." Options A, B, and D describe situations where sharding is beneficial and recommended.

    Read the full bite: Database Sharding: Splitting Data for Scale

30. Question 30 of 30

    Which scenario is LEAST appropriate for utilizing a Time Series Database (TSDB)?

    • aManaging user profiles, preferences, and authentication credentials.
    • bTracking the real-time location and speed of delivery vehicles.
    • cStoring daily closing prices of various financial instruments.
    • dMonitoring CPU load and memory usage of application servers.
    Show the answer

    Answer: a · Managing user profiles, preferences, and authentication credentials.

    The card explicitly states that TSDBs are unsuitable for relational data like user profiles, product catalogs, or transactional order data. They are optimized for high-volume, time-ordered data such as server metrics, sensor readings, and financial data, which are represented in options A, B, and C.

    Read the full bite: Time Series Database: A Logbook, Not a Filing Cabinet