Which database normal form is considered satisfactory for relational database design?

Question

Accepted Answer

The correct option is (3):
Third Normal Form (3 NF) is widely considered the satisfactory and pragmatic standard for relational database design. A relation is in 3NF if it is already in 2NF and if no non-prime attribute is transitively dependent on the primary key. In simpler terms, 3NF ensures that every non-key attribute depends only on the primary key and not on any other non-key attribute. This process eliminates the most common and problematic types of data redundancy and the resultant insertion, deletion, and update anomalies. By removing transitive dependencies, 3NF strikes an optimal balance between minimizing redundancy to maintain data integrity and preventing the excessive complexity that higher normal forms often introduce.

The incorrect option is (1):
First Normal Form (1 NF) is a very basic level of normalization that only requires all column values to be atomic (single-valued) and to eliminate repeating groups. It allows for significant partial and transitive dependencies to remain in the table structure. Due to the high levels of redundancy and numerous data anomalies it permits, a database designed only to 1NF is highly unsatisfactory for managing transactional data effectively and reliably in a professional environment.

The incorrect option is (2):
Second Normal Form (2 NF) is an improvement over 1NF, requiring the table to be in 1NF and all non-key attributes to be fully dependent on the entire primary key. This step successfully eliminates partial dependencies, which are common when a table has a composite primary key. However, 2NF still permits transitive dependencies, meaning non-key attributes can depend on other non-key attributes rather than just the primary key. This remaining flaw means 2NF tables can still suffer from data inconsistencies and anomalies, which is why it is not the commonly accepted satisfactory goal.

The incorrect option is (4):
Fourth Normal Form (4 NF) is a higher level of normalization that specifically eliminates multi-valued dependencies. While achieving 4NF results in a purer database structure, it is often considered over-normalization for typical business applications. The complexities and overhead involved in splitting tables further to satisfy 4NF are usually not justified by the minimal additional benefits in data integrity beyond what 3NF already provides. Therefore, 3NF is the practical standard, and 4NF is only pursued in specific, specialized scenarios.