Database Fundamentals and Data Modeling Questions

Encompasses foundational database concepts and techniques for designing and querying data stores. Topics include relational database concepts, structured query language fundamentals for selecting and modifying data, schema design with tables and relationships, normalization principles, entity relationship modeling, indexing and basic performance considerations, transactions and data integrity, constraints and data types, and common operational tasks such as migrations, backups, and simple optimization strategies. Also discusses trade offs with nonrelational stores and when to choose different persistence models.

MediumTechnical

39 practiced

You are given a denormalized 'orders' table with columns: order_id, customer_name, customer_email, product_id, product_name, quantity, price, order_date. Explain step-by-step how to normalize this into 3NF, list the resulting tables and keys, and outline a migration strategy that minimizes downtime and preserves data integrity.

Sample Answer

Step-by-step normalization to 3NF

1. Identify repeating groups and functional dependencies: customer_name/email depend on customer, product_name/price depend on product, quantity/price per line depend on order line. order_date belongs to order.2. Extract entities: Customers, Products, Orders, OrderItems.3. Assign keys and remove transitive/partial dependencies to ensure 1NF–3NF: use surrogate or natural keys so non-key attributes depend only on the key.

Resulting tables and keys- Customers(customer_id PK, name, email) — customer_id FK referenced by Orders- Products(product_id PK, name, price)- Orders(order_id PK, customer_id FK, order_date, total_amount?) - OrderItems(order_item_id PK OR composite PK(order_id, product_id), order_id FK, product_id FK, quantity, unit_price)Notes: unit_price stored on OrderItems (price at purchase) to avoid historical mutation. total_amount can be computed or stored with triggers.

Migration strategy (minimize downtime, preserve integrity)1. Schema prep: create new tables (Customers, Products, Orders, OrderItems) alongside existing table in production database. Don’t add foreign keys yet or add them DEFERRABLE INITIALLY DEFERRED.2. Backfill in controlled batches: - Extract distinct customers and insert into Customers, mapping original customer_name/email -> customer_id. Use upserts to handle duplicates. - Extract distinct products into Products. - Insert Orders (order_id preserved) with customer_id lookup. - Insert OrderItems linking to Orders and Products; set unit_price from denormalized price. Perform each batch in transactions; verify counts/checksums.3. Maintain sync while backfill runs: - Implement dual-write in application: write both denormalized table and normalized tables for new writes, or use DB trigger/replication to propagate changes. - Alternatively enable CDC (logical replication) to stream changes into new tables.4. Validation: compare row counts, totals, checksums; run sample queries.5. Cutover: once synced and validated, switch application reads/writes to normalized schema (feature flag). Add FK constraints and indexes in maintenance window if needed.6. Rollback plan: keep denormalized table until stable; ability to switch feature flag back.7. Post-migration cleanup: after monitoring, drop denormalized columns/table or keep for archival.

Other considerations: preserve original order_id if required; handle NULLs and inconsistent emails; keep audit logs; run performance testing and add indexes (customer_id, order_date) before cutover.

EasyTechnical

32 practiced

Write a SQL query (PostgreSQL) to select the top 3 most recent orders per customer from an orders table with schema: orders(order_id INT PK, customer_id INT, total_amount NUMERIC, created_at TIMESTAMP). Explain how you handle ties when two orders have the same created_at and state any assumptions about indexing and performance.

Sample Answer

Approach: use a window function to number orders per customer ordered by created_at (most recent first). Decide tie behavior: use ROW_NUMBER() with a deterministic tiebreaker (order_id) to return exactly 3 rows per customer; or use RANK() to include ties (may return >3).

SQL (exactly 3 per customer, deterministic tie-breaker by order_id):

sql

SELECT order_id, customer_id, total_amount, created_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, order_id DESC
         ) AS rn
  FROM orders
) o
WHERE rn <= 3
ORDER BY customer_id, created_at DESC;

Alternative (include ties on created_at; may return >3 for a customer):

sql

SELECT order_id, customer_id, total_amount, created_at
FROM (
  SELECT *,
         RANK() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC
         ) AS rnk
  FROM orders
) o
WHERE rnk <= 3;

Key points and assumptions:- ROW_NUMBER + order_id tiebreaker returns exactly 3 most-recent orders per customer deterministically.- RANK returns all orders that tie on created_at within top-3 ranks (could be >3 rows).- Performance: window functions require sorting partitioned by customer; with large OLTP tables, index on (customer_id, created_at DESC, order_id DESC) helps the planner and can reduce sorting. Consider limiting scanned rows with date filters or materialized summaries for very large datasets.- Complexity: dominated by sorting per partition; runtime depends on data distribution and available indexes.

EasyTechnical

31 practiced

Describe the practical differences between DELETE, TRUNCATE, and DROP in SQL. Cover transactional behavior, rollback capabilities, performance implications, permission requirements, and whether triggers are fired in each case.

Sample Answer

DELETE, TRUNCATE, and DROP differ in purpose and behavior:

Purpose- DELETE: DML — removes rows from a table (optionally with WHERE).- TRUNCATE: DDL-ish — removes all rows quickly, resets storage/identity metadata in some DBs.- DROP: DDL — removes the table (schema) entirely.

Transactional behavior & rollback- DELETE: Fully transactional. Each row deletion is logged (unless minimally logged); you can ROLLBACK the transaction to restore rows.- TRUNCATE: In many RDBMS (e.g., SQL Server, PostgreSQL), TRUNCATE is transactional and can be rolled back if run inside a transaction. However, it may be minimally logged and treated as a DDL operation (autocommit in some systems), so behavior can differ by DB.- DROP: DDL; typically transactional in modern DBs (Postgres supports transactional DROP), but in some systems DROP is immediate and not reversible except via backups. Usually cannot recover without backup once committed.

Performance- DELETE: Slower for large tables because it logs each row and may fire constraints/triggers; can cause large transaction logs.- TRUNCATE: Much faster for large tables because it deallocates data pages or resets internal pointers rather than row-by-row deletes.- DROP: Fastest if you want to remove entire table and metadata; deallocates all storage and associated indexes.

Permissions required- DELETE: Requires DELETE permission on the table (or ownership).- TRUNCATE: Typically requires higher privilege (e.g., ALTER or DROP or table owner) because it affects metadata.- DROP: Requires DROP/ALTER or ownership permissions.

Triggers and constraints- DELETE: Fires DELETE triggers; enforces foreign keys per-row.- TRUNCATE: Usually does NOT fire DELETE triggers. It may be blocked if there are foreign key references; sometimes requires disabling FK constraints or dropping referencing constraints first.- DROP: Does not fire row-level triggers because table is removed; cascade behavior may drop dependent objects depending on syntax (DROP TABLE ... CASCADE).

Practical takeaway: Use DELETE with WHERE for selective, transactional row removal. Use TRUNCATE for fast clearing of entire table when you don’t need per-row triggers and understand transactional semantics of your DB. Use DROP when you want to remove the table and its schema. Always verify behavior and permissions for your specific RDBMS.

EasyTechnical

36 practiced

Describe the goals of normalization and explain 1NF, 2NF, and 3NF with simple examples. Also describe when denormalization might be appropriate for performance and the trade-offs involved, especially in read-heavy workloads.

Sample Answer

Goals of normalization- Eliminate redundant data, avoid update/insert/delete anomalies, and produce a clear, maintainable schema that enforces data integrity.

1NF (First Normal Form)- Rule: Each table cell holds a single (atomic) value; rows are unique.- Example: Bad: Orders(OrderID, CustomerID, Items) where Items = "A,B,C". Good: Orders(OrderID, CustomerID) and OrderItems(OrderID, ProductID, Quantity).- Why: Prevents repeating groups and makes querying predictable.

2NF (Second Normal Form)- Requirement: Table is in 1NF and every non-key column is fully functionally dependent on the whole primary key (relevant when PK is composite).- Example: OrderItems(OrderID, ProductID, ProductName, Quantity). If PK=(OrderID,ProductID), ProductName depends only on ProductID (partial dependency). Fix: Move product attributes to Products(ProductID, ProductName), keep OrderItems(OrderID, ProductID, Quantity).- Why: Removes partial dependencies to reduce redundancy.

3NF (Third Normal Form)- Requirement: Table is in 2NF and no non-key column depends transitively on the key (no transitive dependencies).- Example: Customers(CustomerID, Zip, City, State). If City and State derive from Zip, that's transitive. Fix: ZipCodes(Zip, City, State) and Customers(CustomerID, Zip).- Why: Ensures each non-key attribute depends only on the key, improving consistency.

When to denormalize (for performance)- Denormalization duplicates data or combines tables to reduce expensive joins and improve read latency. Appropriate when: - Workload is read-heavy and joins are the primary bottleneck. - Low write rate or acceptable replication lag. - Need for low-latency reads (e.g., user-facing APIs, analytics dashboards). - Using columnar stores or caches where precomputed aggregates help.

Trade-offs and risks- Pros: Faster reads, fewer joins, simpler read queries, potential for materialized views/index-only scans.- Cons: Increased storage, data redundancy, and risk of update anomalies; more complex write logic (must update duplicates), harder to maintain referential integrity, and more testing/monitoring required. Also can complicate transactional consistency—may require eventual consistency or extra transactions to keep copies synchronized.- Best practice: Start normalized, identify hotspots via profiling, then selectively denormalize (or use caching/materialized views) with clear rules for sync and monitoring.

EasyTechnical

30 practiced

Explain the difference between OLTP and OLAP systems. Discuss typical schema patterns (normalized OLTP vs star/snowflake OLAP schemas), query patterns, latency expectations, and how these differences influence database selection and tuning.

Sample Answer

OLTP vs OLAP — high-level difference:- OLTP (Online Transaction Processing) handles many concurrent short transactions (inserts/updates/deletes/selects) to support applications (e.g., e‑commerce checkout). It prioritizes data integrity, low-latency single-row operations, and high concurrency.- OLAP (Online Analytical Processing) supports complex, read-heavy analytical queries over large historical datasets (e.g., sales trends, BI dashboards). It prioritizes query throughput, aggregations, and fast scans.

Schema patterns:- OLTP: highly normalized schemas (3NF+) to avoid data duplication, maintain ACID integrity, and make transactional updates efficient. Example: separate customer, order, order_line tables with foreign keys.- OLAP: denormalized star or snowflake schemas. Star: large fact table (events/transactions) linking to dimension tables (time, product, customer) for fast aggregations. Snowflake: normalized dimensions for storage savings but slightly more joins.

Query patterns & latency:- OLTP: many small point lookups or indexed range scans, sub-10ms latencies expected. Queries are short and predictable.- OLAP: fewer but heavier queries—large table scans, GROUP BYs, window functions, multi-dimensional aggregations. Latencies range from seconds (ad hoc) to minutes (complex models); interactive BI aims for sub-second to few-second by pre-aggregation.

How this drives DB selection & tuning:- OLTP choices: relational OLTP DBs (Postgres, MySQL, CockroachDB), tuned for write throughput — proper indexing, normalized schema, connection pooling, short transactions, row-level locking, partitioning for hot data, and hardware with low-latency IO.- OLAP choices: columnar stores or MPP engines (ClickHouse, Redshift, BigQuery, Snowflake) or OLAP-optimized extensions. Tune via columnar compression, clustering/partitioning on query keys, materialized views/aggregate tables, bitmap or BRIN indexes, and ample CPU for parallel scans.- Hybrid considerations: HTAP or lambda architectures if both workloads needed — isolate workloads (separate DW/OLTP), use change data capture (CDC) to ETL/ELT into analytics store, or use read replicas for reporting to avoid impacting OLTP.

Key takeaway: pick schema, storage format, and tuning strategies to match access patterns—normalized + indexes for transactional correctness and fast writes; denormalized/columnar + pre-aggregation for analytical scan/aggregation performance.

Unlock Full Question Bank

Get access to hundreds of Database Fundamentals and Data Modeling interview questions and detailed answers.

Join thousands of developers preparing for their dream job.