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

Discuss denormalization: when to denormalize a relational schema for performance, typical denormalization patterns (redundant columns, precomputed aggregates, materialized views), and pitfalls such as update anomalies, increased write cost, and complexity. Provide a small example and how to keep denormalized data correct.

MediumTechnical

0 practiced

You need to add a new non-nullable column `status VARCHAR` with default 'active' to a 200M-row table in production without downtime. Describe a step-by-step zero or low-downtime migration strategy, including testing, data backfill, rolling deployments, and rollback plan.

HardTechnical

0 practiced

Design row-level security for a multi-role application where regular users may only see their rows, managers see their team's rows, and admins see all rows. Discuss implementation using PostgreSQL Row-Level Security (RLS) policies and the complementary application-level checks, including performance and testing considerations.

MediumTechnical

0 practiced

A query is unexpectedly slow even though the columns used in filters are indexed. Outline a systematic debugging approach using EXPLAIN/EXPLAIN ANALYZE, database statistics, index usage, and server metrics. What specific issues would you look for and how would you test potential fixes?

MediumTechnical

0 practiced

Compare master-slave (primary-replica) replication and multi-master replication. For each approach, discuss use cases, benefits (read scaling, failover), limitations (write conflicts, consistency), and approaches to conflict resolution and monitoring.

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.