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

This post is a glossary entry on database normalization – including exam questions and tags.

In a Nutshell

Normalization eliminates redundancies and anomalies in databases through stepwise decomposition of relations into well-defined normal forms to ensure data integrity and consistency.

Compact Technical Description

Normalization is a systematic process for restructuring relational databases to eliminate redundancies and prevent anomalies. The first normal form (1NF) requires atomic values and unique primary keys. The second normal form (2NF) requires 1NF and eliminates partial dependencies of non-key attributes. The third normal form (3NF) requires 2NF and removes transitive dependencies. Boyce-Codd normal form (BCNF) is a stronger form of 3NF that ensures all determinants are key candidates. Through normalization, insert, update, and delete anomalies are prevented and data integrity is improved.

Exam-Relevant Key Points

• 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 key candidate
• Functional dependencies: X → Y means Y functionally depends on X
• Avoid anomalies: Insert, update, delete anomalies
• IHK relevant: Able to recognize and apply normal forms
• Practice: Reduce redundancies, optimize performance, ensure consistency

Core Components

1. Atomic values and 1NF
2. Functional dependencies
3. Primary keys and key candidates
4. Partial dependencies and 2NF
5. Transitive dependencies and 3NF
6. Determinants and BCNF
7. Anomalies (Insert, Update, Delete)
8. Redundancies and their consequences
9. Relationships and foreign keys
10. Denormalization as counterpart

Practical Example

// Before normalization (problem with redundancies)
Order(OrderNo, CustomerNo, CustomerName, ArticleNo, ArticleName, Quantity)

// After normalization in 3NF
Customer(CustomerNo, CustomerName)
Article(ArticleNo, ArticleName)
Order(OrderNo, CustomerNo, Date)
OrderPosition(OrderNo, ArticleNo, Quantity)

Explanation: Redundancies eliminated, each record stored only once, anomalies prevented.

Advantages and Disadvantages

Advantages

• Reduces redundancies and storage requirements
• Prevents anomalies in data manipulation
• Improves data integrity and consistency
• Enables more flexible data structure changes

Disadvantages

• Increased complexity through multiple tables
• Performance losses through joins
• Higher implementation effort
• Possible denormalization necessary for performance

Typical Exam Questions (with Short Answer)

1. 1NF requires? Atomic values, no repeating groups, unique primary keys.

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

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

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

5. Prevent insert anomaly? Through normalization, new data can be inserted without duplicating existing data.

6. Functional dependency X → Y means? For each value of X there is exactly one associated value of Y.

7. Update anomaly and example? Changing a customer name requires changes in all orders, risks inconsistency.

8. When is denormalization useful? For performance problems due to complex joins, deliberately accepting targeted redundancies.

Most Important Sources

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