Database Design, Normalization Forms (1NF, 2NF, 3NF, BCNF): 20 Real Interview Questions

Database normalization fundamentals form the foundation of efficient, maintainable database design. Normalization is the process of organizing data to minimize redundancy and dependency, reducing data anomalies and improving query performance. Understanding normalization forms—from 1NF through BCNF—separates junior database designers from senior architects.

Understanding Database Normalization Fundamentals

Database normalization fundamentals address a core problem: how do you structure tables to avoid storing the same information multiple times, which wastes storage and creates inconsistencies? A poorly designed database with duplication creates update anomalies (changing data in one place but not another), insertion anomalies (cant add data without redundancy), and deletion anomalies (removing a record deletes unrelated information).

First Normal Form (1NF): Atomic Values

Database normalization fundamentals start with 1NF: each column must contain atomic (indivisible) values, and each column must contain only one type of value. Violating 1NF means storing lists in cells like phone_numbers = 555-1234, 555-5678. Instead, create a separate phones table. No repeating groups, no multi-valued columns.

Second Normal Form (2NF): Remove Partial Dependencies

2NF requires 1NF plus: all non-key attributes depend on the entire primary key, not just part of it. In a table with composite key (student_id, course_id), having an attribute like student_name violates 2NF because it depends only on student_id, not the full key. Solution: move student_name to a separate students table.

Third Normal Form (3NF): Remove Transitive Dependencies

3NF requires 2NF plus: no non-key attribute depends on another non-key attribute. Example: an orders table with columns (order_id, customer_id, customer_city). The violation: customer_city transitively depends on customer_id through customer_city. Move customer_city to a customers table.

Boyce-Codd Normal Form (BCNF): Every Determinant is a Candidate Key

BCNF is stricter than 3NF: every determinant (attribute on which other attributes depend) must be a candidate key. Most databases achieve 3NF; BCNF is rarely necessary unless dealing with complex composite keys. BCNF eliminates edge cases 3NF misses.

Denormalization: When to Break the Rules

Database normalization fundamentals teach when NOT to follow the rules. Normalized databases often require multiple joins for queries, impacting performance. Data warehouses intentionally denormalize (star schemas, fact-dimension tables) to speed up analytics. The trade-off: faster reads, slower writes, redundant storage.

5 Complete Interview Questions with Solutions

Question 1: Identifying 1NF Violations

A table stores student_courses = Math, Physics, Chemistry in one cell. Explain why this violates 1NF and how to restructure it.

-- Violates 1NF (repeating groups)
students (student_id, name, courses)
1, John, Math, Physics, Chemistry

-- Normalized to 1NF
students (student_id, name)
enrollments (student_id, course_id)
where each row = one course per student

Explanation: 1NF requires atomic (indivisible) values. Storing multiple courses in one cell violates this. The solution: create a separate enrollments table with student_id and course_id. Query joins students→enrollments to find all courses for a student. Database normalization fundamentals: atomic values prevent parsing strings and enable querying.

Google – Senior Database EngineerAmazon – Data ArchitectMicrosoft – Database Architect

Question 2: Detecting 2NF Violations

A table has columns (student_id, course_id, student_name, grade). The primary key is (student_id, course_id). Why does this violate 2NF?

-- Violates 2NF (partial dependency)
enrollments (student_id, course_id, student_name, grade)
student_name depends only on student_id, not the full key

-- Normalized to 2NF
students (student_id, student_name)
enrollments (student_id, course_id, grade)
student_name now depends on full composite key in students table

Explanation: 2NF requires all non-key attributes depend on the ENTIRE primary key. student_name depends only on student_id, violating this rule. Solution: move student_name to a students table with student_id as primary key. Enrollments primary key becomes (student_id, course_id); grade depends on both.

Meta – Senior Database EngineerIBM – Data ArchitectOracle – Senior DBA

Question 3: Identifying 3NF Violations

An orders table has (order_id, customer_id, customer_city, customer_state). customer_state is determined by customer_city. Why is this a 3NF violation?

-- Violates 3NF (transitive dependency)
orders (order_id, customer_id, customer_city, customer_state)
customer_state depends on customer_city (transitive dependency)

-- Normalized to 3NF
orders (order_id, customer_id)
customers (customer_id, customer_city)
cities (customer_city, customer_state)
Each non-key attribute depends directly on primary key

Explanation: 3NF requires no non-key attribute depends on another non-key attribute. customer_state transitively depends on order_id through customer_city. Solution: create a cities table with customer_city→customer_state mapping. Reduces redundancy and anomalies.

Stripe – Senior Data EngineerLinkedIn – Database Architect

Question 4: BCNF vs 3NF Edge Case

A professors table has (professor_id, course_id, time_slot) with candidate keys (professor_id, time_slot) and (course_id, time_slot). Why does 3NF not catch the issue that BCNF does?

-- Satisfies 3NF but violates BCNF
professors (professor_id, course_id, time_slot)
candidate keys: (professor_id, time_slot), (course_id, time_slot)
professor_id→course_id (determinant not candidate key)

-- BCNF solution
professor_courses (professor_id, course_id)
professor_schedule (professor_id, time_slot)
Every determinant is now a candidate key

Explanation: BCNF requires every determinant be a candidate key. Here, professor_id→course_id (determinant not a candidate key). 3NF allows this because course_id is non-key; BCNF rejects it. BCNF is stricter and eliminates anomalies 3NF misses.

Microsoft – Senior Database ArchitectGoogle – Data Engineer

Question 5: Denormalization for Performance

A normalized database requires 5 joins to get customer purchase history. Would you denormalize for reporting? What trade-offs emerge?

-- Denormalized reporting table
customer_orders_denorm (customer_id, customer_name, order_id, 
  product_name, category, price, quantity, total)
Redundant storage (customer_name, category repeated per order)
Fast queries (no joins)
Slow writes (update customer_name everywhere)
Storage overhead (10x larger)

-- Normalized OLTP database
customers, orders, order_items, products, categories
Slow query (multiple joins)
Fast writes (update once)
Minimal storage

Explanation: Normalization optimizes for writes and consistency. Denormalization optimizes for reads and speed. Data warehouses (OLAP) intentionally denormalize. Production databases (OLTP) stay normalized. Trade-off: normalized=consistency, denormalized=speed.

Amazon – Data ArchitectMeta – Senior Database Engineer

15 Practice Questions (Test Yourself)

Question 6: If a table stores phone_numbers as a comma-separated string in a single column and you want to enforce that only valid phone numbers are stored, what structural changes would be necessary to achieve 1NF compliance?

Google – Senior Data EngineerAmazon – Database Architect

Question 7: When a table with a composite key (department_id, employee_id) stores an attribute that depends only on department_id, would moving that attribute to a separate departments table violate any functional dependency rules?

Microsoft – Senior Database EngineerMeta – Data Architect

Question 8: How would you identify a transitive dependency like where product_name depends on product_id which depends on order_id, and what specific normal form requires elimination of this pattern?

IBM – Senior Data ArchitectOracle – Database ArchitectLinkedIn – Senior DBA

Question 9: When you have a table where every candidate key is a single column and there are no composite keys, would achieving BCNF be straightforward, or are there still potential violations to watch for?

Stripe – Senior Data EngineerGoogle – Database Engineer

Question 10: In a data warehouse used for analytics where you intentionally denormalize tables by combining customer, order, and product information into one large fact table, what specific query performance benefits justify accepting data redundancy?

Amazon – Senior Data ArchitectMicrosoft – Data EngineerMeta – Analytics Engineer

Question 11: If updating a single customers email address requires changing that value in multiple tables due to denormalization, how would you mitigate the risk of inconsistencies during concurrent updates?

Flipkart – Senior Database EngineerLinkedIn – Database Architect

Question 12: When designing a schema for a financial system where data accuracy is critical, would you prioritize achieving BCNF normalization over performance optimizations, and what are the implications?

Google – Senior Database ArchitectOracle – Data ArchitectIBM – Senior DBA

Question 13: If you discover that a seemingly 3NF compliant table actually has multiple candidate keys and exhibits anomalies with one of them, how would BCNF decomposition help resolve conflicts that 3NF fails to address?

Stripe – Data EngineerAmazon – Senior Database Engineer

Question 14: In a production database where a customer record needs to be deleted along with all associated orders, would a normalized structure with foreign key constraints provide better data integrity compared to a denormalized structure?

Microsoft – Senior Data ArchitectMeta – Database EngineerGoogle – BI Architect

Question 15: When a reporting application requires aggregated summary tables that combine normalized source data, would creating materialized views of denormalized data be a better solution than denormalizing the source tables permanently?

LinkedIn – Senior Data EngineerFlipkart – Analytics Engineer

Question 16: How would you explain to a stakeholder why normalizing a database to 3NF might initially slow down queries but ultimately improve system maintainability and reduce data anomalies in the long term?

Stripe – Senior Data ArchitectAmazon – Database EngineerOracle – Senior DBA

Question 17: In a scenario where you have a junction table for many-to-many relationships, would this table inherently satisfy 1NF and 2NF requirements, or are there additional considerations for higher normal forms?

Google – Senior Data EngineerIBM – Database Architect

Question 18: When inheriting a legacy database that violates 2NF due to partial dependencies in composite key tables, what is the safest approach to refactoring without losing historical data?

Meta – Senior Data ArchitectMicrosoft – Senior Database EngineerLinkedIn – Data Architect

Question 19: If youre designing a schema for an e-commerce application handling millions of transactions daily, how would you balance the need for normalization to prevent anomalies against denormalization for read performance?

Stripe – Database EngineerAmazon – Senior Data Architect

Question 20: When analyzing a table to determine if it meets BCNF requirements, what is the most efficient method to identify all candidate keys and determinant relationships before making decomposition decisions?

Google – Senior Database ArchitectOracle – Senior DBAIBM – Data Architect

Ready to Master Database Design & Normalization?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.