Database Design Interview Quiz

Q: How many questions are in the Database Design quiz?

There are 30 questions in total: 10 Beginner, 10 Intermediate, and 10 Advanced, with randomized question order.

Q: Does this apply to SQL and NoSQL engines?

Yes. It covers relational fundamentals (PostgreSQL, MySQL) alongside distributed patterns seen in modern NoSQL and NewSQL engines.

Q: Is this interview quiz completely free?

Yes, 100% free with no sign-up or download required.

Question 1

What is the primary purpose of a primary key in a relational database?

Accepted Answer

To uniquely identify each distinct row record in a table

Answer

To sort table rows automatically in ascending alphabetical order

Answer

To establish a foreign referential link between two tables

Answer

To enforce NOT NULL constraints across all columns of a table

Question 2

What does a foreign key do in a relational database?

Accepted Answer

References the primary key of another table, ensuring the database enforces referential integrity across rows

Answer

Encrypts sensitive column values using AES-256 keys to guarantee cryptographic data security at rest

Answer

Accelerates analytical SELECT queries by indexing foreign key columns into specialized in-memory B-Trees

Answer

Prevents duplicate row records from being inserted into a single table without requiring unique constraints

Question 3

Which of the following best describes a NoSQL database?

Accepted Answer

A non-relational database supporting document, key-value, or graph models

Answer

A storage engine restricted to flat files without index capability

Answer

A relational database storing numeric and tabular metrics exclusively

Answer

A traditional relational engine that forbids standard SQL query syntax

Question 4

What does CRUD stand for in the context of database operations?

Accepted Answer

Create, Read, Update, Delete

Answer

Copy, Replace, Update, Delete

Answer

Connect, Retrieve, Upload, Drop

Answer

Create, Restore, Undo, Deploy

Question 5

What value does a NULL represent in SQL?

Accepted Answer

The absence of a value or an unknown state

Answer

A numerical zero integer value (`0`)

Answer

An empty character text string (`''`)

Answer

A boolean false condition evaluation (`FALSE`)

Question 6

Which relationship type exists when one row in Table A can relate to many rows in Table B, and one row in Table B can also relate to many rows in Table A?

Accepted Answer

Many-to-Many (M:N)

Answer

One-to-One (1:1)

Answer

One-to-Many (1:N)

Answer

Self-referential

Question 7

What is database normalization?

Accepted Answer

Organizing data to reduce redundancy and improve integrity

Answer

Converting all column names to lowercase

Answer

Merging all tables into a single flat table for performance

Answer

Backing up data to a secondary server

Question 8

Which property of ACID transactions ensures that a transaction either fully completes or has no effect at all?

Accepted Answer

Atomicity

Answer

Consistency

Answer

Isolation

Answer

Durability

Question 9

What is the main difference between the SQL and NoSQL query models?

Accepted Answer

SQL uses structured schemas with SQL; NoSQL uses flexible data models

Answer

SQL uses fixed JSON schemas; NoSQL uses dynamic XML hierarchies

Answer

SQL is restricted to Windows; NoSQL runs only in Linux containers

Answer

SQL requires clustered indexes; NoSQL forbids any secondary indexes

Question 10

What is the purpose of a database migration?

Accepted Answer

Applying versioned, incremental schema changes across database environments

Answer

Moving database storage volumes to an alternate cloud data center

Answer

Exporting relational table rows to flat compressed CSV archive files

Answer

Converting all relational tables and constraints into NoSQL collections

Question 11

Which normal form requires that every non-key attribute is fully functionally dependent on the entire primary key, eliminating partial dependencies?

Accepted Answer

Second Normal Form (2NF)

Answer

First Normal Form (1NF)

Answer

Third Normal Form (3NF)

Answer

Boyce-Codd Normal Form (BCNF)

Question 12

What does First Normal Form (1NF) require?

Accepted Answer

Atomic column values with each column containing a single data type

Answer

No transitive dependencies between non-key columns in the table

Answer

All foreign keys referencing existing primary keys in parent tables

Answer

A composite primary key containing at least two or more entity columns

Question 13

Which of the following is a valid reason to intentionally denormalize a database?

Accepted Answer

To improve read query throughput by reducing relational joins

Answer

To satisfy strict foreign key cascading delete constraints

Answer

To enforce stricter multi-column business domain validations

Answer

To guarantee that all application entities conform to 3NF rules

Question 14

What is a surrogate key?

Accepted Answer

A system-generated key (UUID or identity) with no business value

Answer

A natural key derived from customer attributes like an email

Answer

A multi-column composite key identifying junction table relations

Answer

A recursive foreign key pointing to the same table's primary key

Question 15

In an ER diagram, what does a double-line (total participation) on an entity side of a relationship indicate?

Accepted Answer

Every entity instance must participate in the relationship

Answer

The entity possesses a multi-column composite primary key

Answer

The entity establishes a Many-to-Many junction association

Answer

The entity represents a weak entity with partial key identity

Question 16

According to the CAP theorem, a distributed database can guarantee at most how many of the three properties simultaneously?

Accepted Answer

Any two

Answer

All three: Consistency, Availability, and Partition Tolerance

Answer

Only one

Answer

None — they are always in conflict

Question 17

What is the key distinction between OLTP and OLAP workloads?

Accepted Answer

OLTP handles fast transactional writes; OLAP runs complex analytical queries

Answer

OLTP processes unstructured JSON; OLAP manages structured tabular schemas

Answer

OLAP enforces strict ACID locks; OLTP relies on eventual consistency models

Answer

OLTP runs exclusively on cloud nodes; OLAP runs only on on-premises storage

Question 18

Which transaction isolation level prevents dirty reads but still allows non-repeatable reads?

Accepted Answer

Read Committed

Answer

Read Uncommitted

Answer

Repeatable Read

Answer

Serializable

Question 19

What is the difference between a soft delete and a hard delete?

Accepted Answer

Soft delete flags rows as inactive; hard delete permanently removes rows

Answer

Soft delete deletes rows immediately; hard delete sets an expiration TTL

Answer

Soft delete drops secondary indexes; hard delete deletes underlying heap rows

Answer

Both terms represent equivalent operations executed by database background jobs

Question 20

What does 'schema-on-write' mean compared to 'schema-on-read'?

Accepted Answer

Schema-on-write validates at insertion; schema-on-read validates at query time

Answer

Schema-on-write is exclusive to NoSQL; schema-on-read is exclusive to SQL engines

Answer

Schema-on-read requires static column types prior to inserting any table payloads

Answer

Both models enforce identical compile-time constraint validation during inserts

Question 21

In hash-based sharding, what is the main advantage over range-based sharding?

Accepted Answer

Hash sharding distributes writes evenly across shards to avoid hotspots

Answer

Hash sharding optimizes sequential range queries over key intervals

Answer

Hash sharding allows adding cluster nodes without any shard rebalancing

Answer

Hash sharding completely eliminates the need for selecting a partition key

Question 22

What is the main trade-off of synchronous replication compared to asynchronous replication?

Accepted Answer

Synchronous replication guarantees zero data loss at higher write latency

Answer

Synchronous replication increases throughput but drops data on failover

Answer

Asynchronous replication guarantees zero data loss under crash scenarios

Answer

Synchronous replication is restricted to document databases like MongoDB

Question 23

What distinguishes Event Sourcing from a traditional CRUD data model?

Accepted Answer

Event Sourcing logs immutable state events; current state is derived by replay

Answer

Event Sourcing persists only current state; CRUD logs every historical change

Answer

Event Sourcing requires relational tables; CRUD functions only in NoSQL stores

Answer

Event Sourcing updates records in place; CRUD appends new versions sequentially

Question 24

What problem does CQRS (Command Query Responsibility Segregation) primarily solve?

Accepted Answer

It separates read and write models so each can be scaled and optimized apart

Answer

It eliminates secondary indexes by maintaining an in-memory document cache

Answer

It replaces database transactions with distributed eventual consistency locks

Answer

It enforces 3NF relational normalization across all microservice database nodes

Question 25

What is the key difference between optimistic and pessimistic locking?

Accepted Answer

Optimistic locking validates versions at commit assuming conflicts are rare; pessimistic acquires locks upfront

Answer

Optimistic locking prevents all concurrent reads; pessimistic locking tolerates dirty reads under relaxed isolation

Answer

Pessimistic locking is the sole model in relational engines; optimistic locking is restricted to NoSQL engines

Answer

Both approaches share identical table-level hardware locks, differing exclusively in the SQL invocation syntax

Question 26

What is the difference between horizontal partitioning (sharding) and vertical partitioning?

Accepted Answer

Horizontal splits rows across nodes; vertical splits columns into distinct tables

Answer

Horizontal splits columns across tables; vertical splits rows across cluster nodes

Answer

Horizontal partitioning applies to SQL; vertical partitioning applies to NoSQL

Answer

Both approaches represent identical physical storage reorganizations in memory

Question 27

What problem does the Two-Phase Commit (2PC) protocol solve in distributed databases?

Accepted Answer

It ensures atomic commit across nodes so all participants commit or all abort

Answer

It accelerates read queries across multiple sharded relational database nodes

Answer

It replicates write-ahead logs from primary instances to read-only replicas

Answer

It compresses transaction logs to minimize disk storage consumption on nodes

Question 28

Which data modeling technique is best suited for time-series data with high write throughput and range-based time queries?

Accepted Answer

Using append-only tables partitioned by time ranges with timestamp primary keys

Answer

Storing all events inside a single denormalized JSON row per customer entity

Answer

Designing Many-to-Many junction tables with recursive foreign key references

Answer

Enforcing strict 3NF normalization to eliminate redundant timestamp attributes

Question 29

In a multi-tenant SaaS application, what is the main advantage of the 'database-per-tenant' isolation strategy over a shared schema?

Accepted Answer

It ensures strong data isolation, simple tenant backups, and avoids noisy neighbors

Answer

It consumes less overall disk storage and shared memory than a shared schema

Answer

It eliminates the need for schema migration scripts when onboarding new clients

Answer

It is the only multi-tenant architecture pattern supporting ACID transactions

Question 30

How does the Saga pattern address distributed transaction consistency without using 2PC?

Accepted Answer

Breaks a distributed transaction into local transactions, with events triggering compensating transactions on failure

Answer

Locks database tables across all microservices using XA distributed transactions until a coordinator confirms success

Answer

Persists all pending state mutations in a shared relational database that centralizes row locks across microservices

Answer

Queues mutations in temporary node memory until an asynchronous worker executes a single unified ACID commit at destination

Database Design Interview Quiz

What the Database Design interview quiz covers

Difficulty levels

Question topics covered

How to use the Database Design quiz

Frequently asked questions about the Database Design quiz

Database Indexing

System Architecture

Node.js Fundamentals

Database Design Interview Quiz

What the Database Design interview quiz covers

Difficulty levels

Question topics covered

How to use the Database Design quiz

Frequently asked questions about the Database Design quiz

Related Interview Drills

Database Indexing

System Architecture

Node.js Fundamentals