---
title: "Database Design & Patterns"
description: "Master database design: normalization, indexing, query optimization, ACID, transactions, sharding, replication, and enterprise patterns for scalable systems."
author: "Mohammad Abu Mattar"
canonical: https://mkabumattar.com/quizzes/post/database-design-patterns-quiz
---

# Database Design & Patterns

Welcome to the Database Design & Patterns Quiz! Test your knowledge on database normalization, indexing, query optimization, ACID properties, sharding, replication, and more. Each question has a hint and detailed explanations for all options. Good luck!

## Questions

### 1. What is database normalization?

- **The process of organizing data to reduce redundancy and improve data integrity** ✅
  - Normalization: 1NF, 2NF, 3NF, BCNF. Eliminates anomalies.
- The process of duplicating data across tables to minimize join operations and latency
  - This describes denormalization, which is the opposite of the normalization process.
- The practice of converting unstructured NoSQL documents into a rigid relational schema
  - Normalization is about structure refinement within a system, not just format conversion.
- The technique of compressing large database files to save physical storage space
  - Normalization reduces logical redundancy, but it is not a file compression technique.

**Hint:** Think about reducing redundancy.

### 2. What is denormalization?

- **Strategically duplicating data to improve read performance at the cost of write complexity** ✅
  - Denormalization: caching, materialized views, stored calculations. Increases complexity but speeds reads.
- Strictly adhering to 3NF standards to ensure every non-key attribute depends only on the key
  - This describes the third normal form (3NF), which is a goal of normalization, not denormalization.
- Removing all primary and foreign key constraints to allow for faster bulk data ingestion
  - While this might speed up writes, it is a breakdown of integrity rather than a structural pattern.
- Applying compression algorithms to historical data partitions to reduce operational costs
  - Denormalization is a structural change to data models, not a storage-level compression task.

**Hint:** Think about trading consistency for speed.

### 3. What is database indexing?

- **Creating auxiliary structures to speed up data retrieval while increasing write overhead** ✅
  - Index: B-tree, hash, bitmap. Faster reads, slower writes. Choose wisely.
- Physically reordering rows on disk to match the alphabetical order of a specific column
  - This specifically describes a Clustered Index, whereas "indexing" generally refers to non-clustered structures.
- Defining primary key constraints that prevent the insertion of duplicate records in a table
  - While indexes support primary keys, indexing itself is a search optimization tool.
- Compressing column data into a smaller format to reduce the number of I/O operations
  - This is a feature of columnar storage or compression, not the primary function of indexing.

**Hint:** Think about speeding up queries.

### 4. What is a compound index?

- **A single index entry created on multiple columns to optimize specific multi-column queries** ✅
  - Compound index: order matters. `CREATE INDEX idx ON table(col1, col2)`. Selective index.
- A collection of several independent indexes that the database engine merges at runtime
  - This describes Index Intersection or merging, where the engine uses two separate indexes together.
- An index that automatically calculates and stores the sum of numeric values in a table
  - This describes a materialized view or a functional index, not a standard compound index.
- A specialized index designed to support full-text search across large blocks of string data
  - Full-text indexes are a separate category from standard B-Tree compound indexes.

**Hint:** Think about multiple columns in one index.

### 5. What is query optimization?

- **Improving execution efficiency via index selection, plan analysis, and query rewriting** ✅
  - Optimization: use EXPLAIN, identify slow queries, create indexes, denormalize strategically.
- Rewriting application code to reduce the total number of connections made to the database
  - This is connection management or pooling, not the optimization of specific SQL queries.
- Moving all active data into an in-memory cache to bypass the disk-based storage engine
  - Caching is a supplementary layer; query optimization focuses on the query itself within the engine.
- Applying minification to SQL strings to reduce the amount of network bandwidth consumed
  - SQL minification has negligible impact; optimization focuses on the execution plan logic.

**Hint:** Think about making queries efficient.

### 6. What is ACID?

- **A set of properties ensuring that database transactions are processed reliably and safely** ✅
  - ACID: Atomicity, Consistency, Isolation, Durability. Ensures data integrity.
- A distributed system model that prioritizes high availability over immediate consistency
  - This describes the BASE model, which is often contrasted with the ACID model.
- A performance metric used to measure the average response time of a complex SQL join
  - ACID refers to reliability guarantees, not performance or latency metrics.
- A database architecture where data is automatically split into four independent shards
  - ACID is a set of transaction principles, not a physical sharding or partitioning strategy.

**Hint:** Think about transaction properties.

### 7. What is BASE (Eventual Consistency)?

- **A design philosophy prioritizing availability and scale by relaxing strict consistency rules** ✅
  - BASE: Basically Available, Soft state, Eventually consistent. Sacrifices consistency for CAP.
- A traditional relational model that guarantees every transaction results in a valid state
  - This describes the ACID model, which BASE is intended to replace in distributed systems.
- A specialized indexing technique used for geospatial data in high-concurrency environments
  - BASE is a consistency model, not a geospatial indexing or data storage method.
- An acronym for Big-data, Analytics, Storage, and Execution in modern cloud warehouses
  - This is an incorrect expansion; BASE stands for Basically Available, Soft state, Eventually consistent.

**Hint:** Think about NoSQL trade-offs.

### 8. What is sharding?

- **Distributing a large dataset across multiple independent database instances or servers** ✅
  - Sharding: range, hash, directory. Improves scalability. Complex but necessary at scale.
- Creating identical copies of the entire database to balance read requests across nodes
  - This describes replication. Sharding splits data into parts rather than duplicating it.
- Dividing a single table into multiple smaller parts within the same database server
  - This is vertical or horizontal partitioning on a single server, not cross-instance sharding.
- A technique for merging disparate data sources into a single unified schema for reporting
  - Merging data is known as data integration or warehousing, the opposite of sharding.

**Hint:** Think about horizontal data partitioning.

### 9. What is replication?

- **Maintaining synchronized copies of the database across multiple servers to ensure availability** ✅
  - Replication: master-slave, master-master. Trade-off: consistency complexity.
- Breaking a database into small chunks based on a shard key to increase write throughput
  - This describes sharding. Replication focuses on redundancy and availability.
- The process of migrating data from a relational system to a document-based storage engine
  - Data migration refers to moving data, while replication is about keeping multiple copies in sync.
- A method for restoring a database from a tape backup after a catastrophic hardware failure
  - Backup restoration is a recovery process; replication is a real-time synchronization process.

**Hint:** Think about copying data to multiple servers.

### 10. What is CAP theorem?

- **The principle that a distributed system can only provide two of three specific guarantees** ✅
  - CAP: Consistency, Availability, Partition tolerance. Fundamental tradeoff.
- The rule that database performance is limited by CPU, Arithmetic logic, and Power usage
  - This is an incorrect interpretation; CAP stands for Consistency, Availability, and Partition tolerance.
- A standard for measuring the total storage capacity of distributed cloud-native databases
  - The CAP theorem is about logic and consistency trade-offs, not hardware storage capacity.
- An optimization algorithm used to balance read and write loads across a replicated cluster
  - CAP is a theoretical theorem about constraints, not a load-balancing algorithm.

**Hint:** Think about tradeoffs in distributed systems.

### 11. What is materialized view?

- **A database object that contains the results of a query and is stored on disk for speed** ✅
  - Materialized view: faster queries, stale data. Strategy: immediate/on-demand refresh.
- A virtual table that runs a query dynamically every time it is accessed by the application
  - This describes a standard View. Materialized views store the data physically to improve performance.
- A temporary table created in memory that only exists for the duration of a single session
  - This describes a Temporary Table, whereas a materialized view is a persistent database object.
- A specialized report format designed for export into external data visualization tools
  - Materialized views are internal database structures, not export or reporting formats.

**Hint:** Think about precomputed query result.

### 12. What is transaction isolation level?

- **A setting that defines the degree to which transactions are visible to other concurrent operations** ✅
  - Higher isolation: safer, slower. Choose based on requirements. Classic phantom read issue.
- A hardware-level restriction that prevents more than one user from connecting to a database
  - Isolation levels manage concurrent data access, not the total number of user connections.
- The physical separation of database files on different disk drives to prevent I/O bottlenecks
  - This is disk striping or partitioning, which is unrelated to transactional isolation.
- A security feature that prevents unauthorized users from viewing sensitive row-level data
  - This is Row-Level Security (RLS), while isolation levels deal with transaction concurrency.

**Hint:** Think about concurrent transaction interference.

### 13. What is n+1 query problem?

- **A performance antipattern where an application makes separate queries for each related item** ✅
  - n+1: inefficient. Solution: JOIN, batch fetching, lazy loading config.
- A database limitation where a table can only be joined with a maximum of one other table
  - Most databases support many joins; n+1 is an application-level logic issue, not a limit.
- A syntax error occurring when a query attempts to select more columns than exist in a table
  - This would be a standard SQL error, whereas n+1 is a subtle performance bottleneck.
- An optimization technique that adds one extra index for every new column added to a schema
  - This would likely lead to write performance issues and is not related to the n+1 problem.

**Hint:** Think about fetching related data inefficiently.

### 14. What is EXPLAIN in databases?

- **A diagnostic statement that returns the execution plan chosen by the query optimizer** ✅
  - EXPLAIN: diagnostic tool. Identifies slow queries, missing indexes. Essential for optimization.
- A commenting syntax used to provide documentation for complex stored procedures
  - Explain is a functional command that returns data, not a documentation or comment tag.
- A security command that logs the identity of every user who accesses a specific table
  - This describes database auditing or logging, whereas EXPLAIN is for performance analysis.
- A keyword used to declare the intended data types for a set of temporary variables
  - Variable declaration uses keywords like DECLARE or SET, not the EXPLAIN command.

**Hint:** Think about query execution analysis.

### 15. What is referential integrity?

- **A state where all foreign key values correctly correspond to existing primary keys** ✅
  - Foreign key constraints: ON DELETE CASCADE, SET NULL. Prevents orphaned records.
- The requirement that every record in a database must have a unique identifier on disk
  - This describes Entity Integrity (Primary Keys), rather than Referential Integrity (Foreign Keys).
- An encryption standard that ensures sensitive data cannot be referenced by external apps
  - Referential integrity is about data consistency, not encryption or access security.
- The process of verifying that a database schema matches the intended business domain model
  - This is domain validation or modeling, not a technical integrity constraint on keys.

**Hint:** Think about foreign keys maintaining data relationships.

### 16. What is a deadlock in databases?

- **A situation where two or more transactions are permanently blocked by each other’s locks** ✅
  - Deadlock: transaction A waits B, B waits A. Database detects, rolls back one.
- A catastrophic hardware failure that prevents the database engine from booting up
  - This is a system crash; deadlocks are logical resource conflicts during normal operation.
- A security lock placed on a user account after multiple failed login attempts
  - This is account lockout, which is unrelated to database transaction deadlocks.
- The time delay that occurs when a query is waiting for a large disk read to complete
  - This is simply I/O latency or blocking, not a cyclical deadlock dependency.

**Hint:** Think about transactions waiting for each other.

### 17. What is connection pooling?

- **Maintaining a cache of open database connections to be reused by multiple requests** ✅
  - Connection pool: e.g., HikariCP, pgBouncer. Improves throughput, reduces latency.
- Combining multiple physical databases into a single virtual pool for global queries
  - This describes data federation or virtualization, not connection pooling.
- A security measure that limits the total number of simultaneous users allowed on a server
  - Connection limits are a configuration setting; pooling is a resource management strategy.
- A method for distributing database files across a storage area network (SAN) pool
  - This is storage pooling, whereas connection pooling deals with network sockets.

**Hint:** Think about reusing database connections.

### 18. What is optimistic vs pessimistic locking?

- **Two concurrency models that differ in whether they lock data before or after a transaction** ✅
  - Optimistic: check at commit. Pessimistic: lock immediately. Choice depends on contention.
- A performance comparison between traditional SQL databases and modern NoSQL systems
  - These are locking strategies that can be applied to almost any database system.
- A security assessment used to determine the vulnerability of a database to SQL injection
  - Locking refers to concurrency control, not security audits or injection prevention.
- The difference between storing data in a local cache versus a persistent disk-based store
  - This is a caching strategy comparison, not a concurrency control or locking model.

**Hint:** Think about concurrency control strategies.

### 19. What is OLTP vs OLAP?

- **Two workload categories: one for real-time transactions and one for complex data analysis** ✅
  - OLTP: normalized (MySQL). OLAP: denormalized (Data Warehouse). Different storage patterns.
- Two protocol standards for transferring data between different database vendors
  - These are workload types, not communication protocols like ODBC or JDBC.
- The difference between storing data in rows versus storing data in a nested document format
  - This refers to row-store vs document-store, which is a structural rather than workload distinction.
- A set of encryption algorithms used to secure data in transit versus data at rest
  - OLTP and OLAP are functional categories for database usage, not security standards.

**Hint:** Think about transactional vs analytical workloads.

### 20. What is database partitioning?

- **The process of dividing a large table into smaller, more manageable pieces on a single server** ✅
  - Partitioning: range, hash, list. Improves query performance. Different from sharding.
- Splitting the database schema into separate microservices with their own storage
  - This is a microservices architectural pattern, not a database partitioning technique.
- Creating a complete copy of the database to serve read-only requests for analytics
  - This is replication. Partitioning focuses on splitting a single dataset into pieces.
- The act of moving old data into a secondary cold storage system to save on costs
  - This is archiving. While partitioning helps with archiving, it is a broader structural tool.

**Hint:** Think about dividing large tables.

### 21. What is a window function?

- **A function that performs calculations across a set of table rows related to the current row** ✅
  - Window functions: ROW_NUMBER(), RANK(), LEAD(). Powerful analytics without GROUP BY.
- A scheduled task that only executes during a specific maintenance window at night
  - This is a cron job or a maintenance task, unrelated to SQL window functions.
- A specialized UI component used to display database records in a desktop application
  - This refers to a software window in a GUI, not a functional calculation in SQL.
- A query that limits the result set to only those records created within the last hour
  - This is a simple filtered query or a "sliding window" logic, but not a window function.

**Hint:** Think about analyzing data within partitions.

### 22. What is a CTE (Common Table Expression)?

- **A temporary named result set that can be referenced within a single SQL statement** ✅
  - CTE: recursive CTEs for hierarchies. Better than nested subqueries. Scoped to query.
- A permanent table structure used to store metadata about the database configuration
  - CTEs are temporary and exist only for the duration of the query, unlike system tables.
- A database-wide variable that can be shared across multiple different user sessions
  - CTEs are scoped to a single query and cannot be shared across different sessions or users.
- An external tool used to convert legacy Excel spreadsheets into relational database rows
  - CTE is a native SQL feature (WITH clause), not an external data conversion tool.

**Hint:** Think about WITH clause for subqueries.

### 23. What is write-ahead logging (WAL)?

- **A technique where changes are recorded in a log before being applied to the main database** ✅
  - WAL: Ensures durability if crash occurs. Log to disk first, then apply.
- An optimization that delays writing data until the user explicitly refreshes the page
  - WAL is an automated internal process for durability, not a user-triggered optimization.
- A security feature that logs the IP address of every user who performs a write operation
  - This is audit logging, whereas WAL is specifically for data integrity and recovery.
- A method for pre-calculating the results of expensive queries before they are requested
  - This describes pre-computation or caching, not a write-ahead transaction log.

**Hint:** Think about durability in transactions.

### 24. What is schema migration?

- **The management of incremental, reversible changes to a database schema over time** ✅
  - Schema migration: version management (Flyway, Liquibase), backward compatibility.
- The one-time process of moving all data from an old server to a new hardware cluster
  - This is a data or server migration, whereas schema migration focuses on the structure.
- The automatic conversion of SQL queries into a different language for another vendor
  - This is SQL translation or transpilation, not a schema evolution process.
- A security process that moves sensitive tables into a hidden, encrypted partition
  - This is encryption at rest or data masking, not a versioned schema change.

**Hint:** Think about evolving database schema safely.

### 25. What is the hot table problem?

- **A performance bottleneck caused by high contention for locks on a single, popular table** ✅
  - Hot table: e.g., counter tables. Solutions: de-normalization, caching (Redis).
- A physical hardware issue where a database server overheats due to high CPU usage
  - This is a thermal or hardware issue, whereas "hot table" is a logical contention problem.
- A security vulnerability where a table is accidentally exposed to the public internet
  - Exposure is a security breach, while a "hot table" is a performance scaling issue.
- An error that occurs when a table exceeds the maximum row limit of the storage engine
  - This is a capacity limit error, while the "hot table" problem refers to access contention.

**Hint:** Think about contention on heavily accessed tables.

### 26. What is read replica?

- **A read-only copy of the primary database used to offload traffic from the master node** ✅
  - Read replica: Use for analytics, backups, geographic distribution. Replication lag possible.
- A backup file stored on an offline drive to be used only during a disaster recovery
  - Read replicas are online and active, unlike offline backup files used for recovery.
- A specialized database user account that only has permission to view specific columns
  - This describes a read-only user or view, not a physical replica of the database instance.
- An identical database server that remains idle until the primary server fails
  - This describes a standby or failover node, not an active read-scaling replica.

**Hint:** Think about scaling read capacity.

### 27. What is lazy loading vs eager loading?

- **Two strategies defining whether related data is fetched on-demand or during the initial query** ✅
  - Lazy loading: load on access. Eager loading: load upfront. Trade-off: queries vs. memory.
- Two methods for deciding whether to use a local cache or a remote database server
  - This is a caching strategy (cache-aside), while loading strategies focus on object relationships.
- The difference between loading data into a browser versus loading it into a mobile app
  - This is a platform distinction, not a technical data fetching strategy for related objects.
- A comparison of how quickly images are rendered on a webpage versus text content
  - This refers to web performance patterns, not database relationship loading strategies.

**Hint:** Think about when related data is fetched.

### 28. What is database backup strategy?

- **A defined plan combining full, incremental, and log backups to enable data recovery** ✅
  - Backup strategy: full weekly, incremental daily, WAL for PITR. Test recovery procedures.
- The practice of making an exact copy of the database schema without any of the data
  - This is a schema dump. A backup strategy must include the actual data to be effective.
- A security rule that requires every user to change their password once every 30 days
  - This is a password rotation policy, which is unrelated to database disaster recovery.
- A technique for deleting old records to ensure the database does not exceed disk capacity
  - This is data pruning or archiving, not a strategy for ensuring data can be recovered.

**Hint:** Think about disaster recovery planning.

### 29. What is a primary key design consideration?

- **The decision between using inherent domain data or system-generated IDs as identifiers** ✅
  - Natural (email) vs Surrogate (ID). Modern practice: surrogate preferred for stability.
- The choice between using a single server or multiple shards to host the primary key
  - This is a sharding consideration, while PK design focuses on the attribute itself.
- The requirement that every primary key must be encrypted before it is stored on disk
  - Encryption is a security layer; primary keys are primarily used for identification and joins.
- A performance limit on the total number of primary keys allowed in a single database
  - Primary keys are defined per table; there is no global limit on the count of keys in a database.

**Hint:** Think about choosing natural vs surrogate keys.

### 30. What is database caching strategy?

- **A plan for storing frequently accessed data in high-speed memory to reduce backend load** ✅
  - Caching strategy: cache-aside, write-through, write-behind. Invalidate on writes.
- A method for permanently moving historical data into an in-memory database for storage
  - Caching is a temporary copy of data; moving data permanently is known as data migration.
- A specialized indexing technique used to speed up queries on large BLOB or image columns
  - Indexing is an internal database feature, while caching is usually a separate architectural layer.
- The process of minifying SQL queries to reduce the amount of RAM consumed by the engine
  - Minifying queries does not significantly impact memory; caching focuses on the data itself.

**Hint:** Think about reducing database load with cache layers.