Relational Database Fundamentals and Design Questions

Core concepts of relational databases and schema design including tables, relationships such as one to one one to many and many to many, primary keys and foreign keys, data integrity constraints, and the properties of atomicity consistency isolation and durability and why they matter. Understand differences between relational systems using structured query language and nonrelational databases, indexing strategies, normalization and denormalization trade offs, simple query optimization techniques, and when to choose a normalized relational design versus a document or key value store. Candidates should be able to perform basic entity identification, produce simple schema diagrams, explain persistence and durability considerations, and reason about basic performance and scaling trade offs.

MediumTechnical

0 practiced

Design a star-schema for analytics reporting on e-commerce orders with fact table and relevant dimensions (customers, products, time, geography). Explain grain choice, surrogate keys for dimensions, typical indexes for queries, and how slowly-changing dimensions (SCD Type 2) would be modeled.

EasyTechnical

0 practiced

What is an index in a relational database? Explain how indexes (e.g., B-tree) speed up reads, the typical trade-offs for inserts/updates/deletes, and why SELECT performance can degrade with too many indexes. Include the concept of selectivity and covering indexes in your explanation.

EasyTechnical

0 practiced

Explain normalization: 1NF, 2NF, 3NF (and mention BCNF). For each normal form give a short example of a table that violates it and show the normalized schema. Explain the practical reasons for normalizing and situations where you might stop before BCNF in production.

HardTechnical

0 practiced

Discuss two-phase commit (2PC) for distributed transactions: how it works, why it's used, and its limitations (blocking coordinator, latency). Present alternatives such as the Saga pattern for long-running distributed workflows and provide a concrete example of implementing a saga with compensation actions.

MediumTechnical

0 practiced

You store millions of event rows with columns: (event_id, user_id, event_type, created_at). Typical queries filter by event_type and created_at range, sometimes by user_id. Propose concrete indexing strategies (single-column, composite, partial, clustering) and explain how each supports query patterns and affects write throughput.

Unlock Full Question Bank

Get access to hundreds of Relational Database Fundamentals and Design interview questions and detailed answers.

Join thousands of developers preparing for their dream job.