What are the benefits of third normal form and boyce-codd normal form, the benefits of 3nf and bcnf

Benefits of 3NF and BCNF

Third Normal Form (3NF) insists that all facts in a table are about the key (or the thing that the key identifies), the whole key and nothing but the key. [G K Gupta, DBMS, Tata McGraw Hill]. It eliminates non-key dependencies.

Boyce Codd Normal Form (BCNF) demands that every non-trivial determinant is a candidate key. It is even stricter that all the functional dependencies must have the candidate keys on the left hand side. Though 3NF insist on removing non-key dependencies, it accepts a transitive FD where a key attribute determined by non-key attribute. BCNF eliminates this FD too to become a stricter normal form.

Both 3NF and BCNF provides the following benefits;

Stricter than 2NF

Eliminates partial-key dependencies and non-key dependencies. [Please refer below]

Data redundancy is reduced

Due to non-key dependency, whenever a value occurs in the table for determinant attribute, the dependent attribute value is repeated. 3nf insist on every non-key attribute to non-transitively depend on the candidate key, the redundancies caused by non-key dependencies are eliminated.

Data modification anomalies are reduced

Insert, delete and update anomalies that are present in a non-3nf table are eliminated.

Increased data quality

Ensures that business rules (expressed as functional dependencies) are correctly enforced by keys in your data model.

Few requried descriptions

What is partial-key dependency? A functional dependency where the determinant (left hand side attribute) uniquely determines a non-key attribute.

What is non-key dependency? A functional dependency where both the determinant and determiners are not keys.

Example:

Consider a relation R(A, B, C, D, E) with set F of functional dependencies F = {A→B, C→D, D→E}

For this relation, AC is the candidate key.

As per the given set of functional dependencies, B depends on A alone (A→B), D depends on C alone (C→D). These dependencies are referred as partial-key dependencies because A and C are parts of candidate key AC.

The FD D→E is said to be a non-key dependency because neither D nor E is a key.

***************************

Related links: