Database Design Normalization – 1NF, 2NF, 3NF, BCNF, Anomalies, Relationships

This article is a glossary entry on database normalization, complete with exam questions and tags.

In a Nutshell

Normalization eliminates redundancy and anomalies in databases by systematically decomposing relations into well-defined normal forms, ensuring data integrity and consistency.

Technical Overview

Normalization is a systematic process for restructuring relational databases to eliminate redundancy and prevent anomalies. First Normal Form (1NF) requires atomic values and unique primary keys. Second Normal Form (2NF) requires 1NF and removes partial dependencies of non-key attributes. Third Normal Form (3NF) requires 2NF and eliminates transitive dependencies. Boyce-Codd Normal Form (BCNF) is a stricter variant of 3NF that ensures all determinants are candidate keys. Normalization prevents insert, update, and delete anomalies while improving data integrity.

Key Points for Exam Preparation

• 1NF: Atomic values, no repeating groups, unique primary keys
• 2NF: 1NF satisfied, no partial dependencies, non-key attributes fully dependent
• 3NF: 2NF satisfied, no transitive dependencies
• BCNF: Every determinant is a candidate key
• Functional dependencies: X → Y means Y is functionally dependent on X
• Preventing anomalies: insert, update, delete anomalies
• Industry standard: Recognize and apply normal forms
• Practice: Reduce redundancy, optimize performance, ensure consistency

Core Components

1. Atomic values and 1NF
2. Functional dependencies
3. Primary keys and candidate keys
4. Partial dependencies and 2NF
5. Transitive dependencies and 3NF
6. Determinants and BCNF
7. Anomalies (insert, update, delete)
8. Redundancy and its consequences
9. Relationships and foreign keys
10. Denormalization as a trade-off

Practical Example

// Before normalization (redundancy problem)
Order(OrderID, CustomerID, CustomerName, ProductID, ProductName, Quantity)

// After normalization to 3NF
Customer(CustomerID, CustomerName)
Product(ProductID, ProductName)
Order(OrderID, CustomerID, Date)
OrderItem(OrderID, ProductID, Quantity)

Explanation: Redundancy eliminated, each data record stored once, anomalies prevented.

Advantages and Disadvantages

Advantages

• Reduces redundancy and storage requirements
• Prevents anomalies during data manipulation
• Improves data integrity and consistency
• Enables more flexible schema changes

Disadvantages

• Increased complexity from multiple tables
• Performance overhead from joins
• Higher implementation effort
• May require denormalization for performance

Common Exam Questions (with Brief Answers)

1. What does 1NF require? Atomic values, no repeating groups, unique primary keys.

2. How does 2NF differ from 1NF? 2NF requires 1NF and eliminates partial dependencies of non-key attributes.

3. What is transitive dependency in 3NF? Attribute A depends on B, B depends on C, but A does not depend directly on C.

4. Is BCNF stricter than 3NF? Yes, BCNF requires that every determinant is a candidate key, a stricter condition than 3NF.

5. How do you prevent insert anomalies? Normalization allows new data to be inserted without duplicating existing records.

6. What does functional dependency X → Y mean? For each value of X, there is exactly one corresponding value of Y.

7. What is an update anomaly and give an example? Changing a customer name requires updates across all orders, risking inconsistency.

8. When is denormalization justified? When performance problems arise from complex joins; deliberate redundancy is accepted as a trade-off.

Key References

1. https://en.wikipedia.org/wiki/Database_normalization
2. https://docs.microsoft.com/en-us/sql/relational-databases/normalization
3. https://www.guru99.com/database-normalization.html

---

Recommended Reading: Databases