What is database normalization?

The process of organizing data to reduce redundancy and improve data integrity

The process of duplicating data across tables to minimize join operations and latency

The practice of converting unstructured NoSQL documents into a rigid relational schema

The technique of compressing large database files to save physical storage space

What is denormalization?

Strategically duplicating data to improve read performance at the cost of write complexity

Strictly adhering to 3NF standards to ensure every non-key attribute depends only on the key

Removing all primary and foreign key constraints to allow for faster bulk data ingestion

Applying compression algorithms to historical data partitions to reduce operational costs

What is database indexing?

Creating auxiliary structures to speed up data retrieval while increasing write overhead

Physically reordering rows on disk to match the alphabetical order of a specific column

Defining primary key constraints that prevent the insertion of duplicate records in a table

Compressing column data into a smaller format to reduce the number of I/O operations

What is a compound index?

A single index entry created on multiple columns to optimize specific multi-column queries

A collection of several independent indexes that the database engine merges at runtime

An index that automatically calculates and stores the sum of numeric values in a table

A specialized index designed to support full-text search across large blocks of string data

What is query optimization?

Improving execution efficiency via index selection, plan analysis, and query rewriting

Rewriting application code to reduce the total number of connections made to the database

Moving all active data into an in-memory cache to bypass the disk-based storage engine

Applying minification to SQL strings to reduce the amount of network bandwidth consumed

A set of properties ensuring that database transactions are processed reliably and safely

A distributed system model that prioritizes high availability over immediate consistency

A performance metric used to measure the average response time of a complex SQL join

A database architecture where data is automatically split into four independent shards

What is BASE (Eventual Consistency)?

A design philosophy prioritizing availability and scale by relaxing strict consistency rules

A traditional relational model that guarantees every transaction results in a valid state

A specialized indexing technique used for geospatial data in high-concurrency environments

An acronym for Big-data, Analytics, Storage, and Execution in modern cloud warehouses

Distributing a large dataset across multiple independent database instances or servers

Creating identical copies of the entire database to balance read requests across nodes

Dividing a single table into multiple smaller parts within the same database server

A technique for merging disparate data sources into a single unified schema for reporting

Maintaining synchronized copies of the database across multiple servers to ensure availability

Breaking a database into small chunks based on a shard key to increase write throughput

The process of migrating data from a relational system to a document-based storage engine

A method for restoring a database from a tape backup after a catastrophic hardware failure

The principle that a distributed system can only provide two of three specific guarantees

The rule that database performance is limited by CPU, Arithmetic logic, and Power usage

A standard for measuring the total storage capacity of distributed cloud-native databases

An optimization algorithm used to balance read and write loads across a replicated cluster

What is materialized view?

A database object that contains the results of a query and is stored on disk for speed

A virtual table that runs a query dynamically every time it is accessed by the application

A temporary table created in memory that only exists for the duration of a single session

A specialized report format designed for export into external data visualization tools

What is transaction isolation level?

A setting that defines the degree to which transactions are visible to other concurrent operations

A hardware-level restriction that prevents more than one user from connecting to a database

The physical separation of database files on different disk drives to prevent I/O bottlenecks

A security feature that prevents unauthorized users from viewing sensitive row-level data

What is n+1 query problem?

A performance antipattern where an application makes separate queries for each related item

A database limitation where a table can only be joined with a maximum of one other table

A syntax error occurring when a query attempts to select more columns than exist in a table

An optimization technique that adds one extra index for every new column added to a schema

What is EXPLAIN in databases?

A diagnostic statement that returns the execution plan chosen by the query optimizer

A commenting syntax used to provide documentation for complex stored procedures

A security command that logs the identity of every user who accesses a specific table

A keyword used to declare the intended data types for a set of temporary variables

What is referential integrity?

A state where all foreign key values correctly correspond to existing primary keys

The requirement that every record in a database must have a unique identifier on disk

An encryption standard that ensures sensitive data cannot be referenced by external apps

The process of verifying that a database schema matches the intended business domain model

What is a deadlock in databases?

A situation where two or more transactions are permanently blocked by each other’s locks

A catastrophic hardware failure that prevents the database engine from booting up

A security lock placed on a user account after multiple failed login attempts

The time delay that occurs when a query is waiting for a large disk read to complete

What is connection pooling?

Maintaining a cache of open database connections to be reused by multiple requests

Combining multiple physical databases into a single virtual pool for global queries

A security measure that limits the total number of simultaneous users allowed on a server

A method for distributing database files across a storage area network (SAN) pool

What is optimistic vs pessimistic locking?

Two concurrency models that differ in whether they lock data before or after a transaction

A performance comparison between traditional SQL databases and modern NoSQL systems

A security assessment used to determine the vulnerability of a database to SQL injection

The difference between storing data in a local cache versus a persistent disk-based store

What is OLTP vs OLAP?

Two workload categories: one for real-time transactions and one for complex data analysis

Two protocol standards for transferring data between different database vendors

The difference between storing data in rows versus storing data in a nested document format

A set of encryption algorithms used to secure data in transit versus data at rest

What is database partitioning?

The process of dividing a large table into smaller, more manageable pieces on a single server

Splitting the database schema into separate microservices with their own storage

Creating a complete copy of the database to serve read-only requests for analytics

The act of moving old data into a secondary cold storage system to save on costs

What is a window function?

A function that performs calculations across a set of table rows related to the current row

A scheduled task that only executes during a specific maintenance window at night

A specialized UI component used to display database records in a desktop application

A query that limits the result set to only those records created within the last hour

What is a CTE (Common Table Expression)?

A temporary named result set that can be referenced within a single SQL statement

A permanent table structure used to store metadata about the database configuration

A database-wide variable that can be shared across multiple different user sessions

An external tool used to convert legacy Excel spreadsheets into relational database rows

What is write-ahead logging (WAL)?

A technique where changes are recorded in a log before being applied to the main database

An optimization that delays writing data until the user explicitly refreshes the page

A security feature that logs the IP address of every user who performs a write operation

A method for pre-calculating the results of expensive queries before they are requested

What is schema migration?

The management of incremental, reversible changes to a database schema over time

The one-time process of moving all data from an old server to a new hardware cluster

The automatic conversion of SQL queries into a different language for another vendor

A security process that moves sensitive tables into a hidden, encrypted partition

What is the hot table problem?

A performance bottleneck caused by high contention for locks on a single, popular table

A physical hardware issue where a database server overheats due to high CPU usage

A security vulnerability where a table is accidentally exposed to the public internet

An error that occurs when a table exceeds the maximum row limit of the storage engine

What is read replica?

A read-only copy of the primary database used to offload traffic from the master node

A backup file stored on an offline drive to be used only during a disaster recovery

A specialized database user account that only has permission to view specific columns

An identical database server that remains idle until the primary server fails

What is lazy loading vs eager loading?

Two strategies defining whether related data is fetched on-demand or during the initial query

Two methods for deciding whether to use a local cache or a remote database server

The difference between loading data into a browser versus loading it into a mobile app

A comparison of how quickly images are rendered on a webpage versus text content

What is database backup strategy?

A defined plan combining full, incremental, and log backups to enable data recovery

The practice of making an exact copy of the database schema without any of the data

A security rule that requires every user to change their password once every 30 days

A technique for deleting old records to ensure the database does not exceed disk capacity

What is a primary key design consideration?

The decision between using inherent domain data or system-generated IDs as identifiers

The choice between using a single server or multiple shards to host the primary key

The requirement that every primary key must be encrypted before it is stored on disk

A performance limit on the total number of primary keys allowed in a single database

What is database caching strategy?

A plan for storing frequently accessed data in high-speed memory to reduce backend load

A method for permanently moving historical data into an in-memory database for storage

A specialized indexing technique used to speed up queries on large BLOB or image columns

The process of minifying SQL queries to reduce the amount of RAM consumed by the engine

Quizzes

Prev in BackendAsync JavaScript: Promises, Async/Await, Event Loop Next in BackendNode.js & Express Fundamentals: Building Server Applications

Backend Database

Database Design & Patterns

Mohammad Abu Mattar20 QuestionsIntermediatePublished: 10 Mar, 2026

Markdown for AI

Database Design & Patterns

Up to 20 questions, shuffled on every run

Quiz Configuration

Enterto start

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!

Answer key and explanations20 questions

The quiz above draws 20 questions at random from these 30, so a second attempt will not be the same run. Everything in the pool is listed here.

What is database normalization?
AnswerThe process of organizing data to reduce redundancy and improve data integrity
What is denormalization?
AnswerStrategically duplicating data to improve read performance at the cost of write complexity
What is database indexing?
AnswerCreating auxiliary structures to speed up data retrieval while increasing write overhead
What is a compound index?
AnswerA single index entry created on multiple columns to optimize specific multi-column queries
What is query optimization?
AnswerImproving execution efficiency via index selection, plan analysis, and query rewriting
What is ACID?
AnswerA set of properties ensuring that database transactions are processed reliably and safely
What is BASE (Eventual Consistency)?
AnswerA design philosophy prioritizing availability and scale by relaxing strict consistency rules
What is sharding?
AnswerDistributing a large dataset across multiple independent database instances or servers
What is replication?
AnswerMaintaining synchronized copies of the database across multiple servers to ensure availability
What is CAP theorem?
AnswerThe principle that a distributed system can only provide two of three specific guarantees
What is materialized view?
AnswerA database object that contains the results of a query and is stored on disk for speed
What is transaction isolation level?
AnswerA setting that defines the degree to which transactions are visible to other concurrent operations
What is n+1 query problem?
AnswerA performance antipattern where an application makes separate queries for each related item
What is EXPLAIN in databases?
AnswerA diagnostic statement that returns the execution plan chosen by the query optimizer
What is referential integrity?
AnswerA state where all foreign key values correctly correspond to existing primary keys
What is a deadlock in databases?
AnswerA situation where two or more transactions are permanently blocked by each other’s locks
What is connection pooling?
AnswerMaintaining a cache of open database connections to be reused by multiple requests
What is optimistic vs pessimistic locking?
AnswerTwo concurrency models that differ in whether they lock data before or after a transaction
What is OLTP vs OLAP?
AnswerTwo workload categories: one for real-time transactions and one for complex data analysis
What is database partitioning?
AnswerThe process of dividing a large table into smaller, more manageable pieces on a single server
What is a window function?
AnswerA function that performs calculations across a set of table rows related to the current row
What is a CTE (Common Table Expression)?
AnswerA temporary named result set that can be referenced within a single SQL statement
What is write-ahead logging (WAL)?
AnswerA technique where changes are recorded in a log before being applied to the main database
What is schema migration?
AnswerThe management of incremental, reversible changes to a database schema over time
What is the hot table problem?
AnswerA performance bottleneck caused by high contention for locks on a single, popular table
What is read replica?
AnswerA read-only copy of the primary database used to offload traffic from the master node
What is lazy loading vs eager loading?
AnswerTwo strategies defining whether related data is fetched on-demand or during the initial query
What is database backup strategy?
AnswerA defined plan combining full, incremental, and log backups to enable data recovery
What is a primary key design consideration?
AnswerThe decision between using inherent domain data or system-generated IDs as identifiers
What is database caching strategy?
AnswerA plan for storing frequently accessed data in high-speed memory to reduce backend load