3NF and BCNF

Definition

Database Normalization is the process of organizing data to reduce redundancy and improve data integrity. Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF) are advanced levels of normalization used to eliminate transitive dependencies and anomalies in relational database design.

Main Content

1. Third Normal Form (3NF)

• A table is in 3NF if it is already in Second Normal Form (2NF) and contains no transitive dependencies.
• A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute, rather than depending directly on the primary key (e.g., if A -> B and B -> C, then A -> C is a transitive dependency).

2. Boyce-Codd Normal Form (BCNF)

• BCNF is a stronger version of 3NF, often referred to as 3.5NF. It is used to handle anomalies that 3NF might miss when a table has multiple overlapping candidate keys.
• A table is in BCNF if, for every functional dependency X -> Y, X is a superkey.

3. Comparison of Requirements

• 3NF allows a dependency where a non-prime attribute determines another non-prime attribute if the determinant is a superkey. BCNF forbids this entirely by requiring the determinant to be a superkey in every scenario.
• BCNF is more restrictive than 3NF; therefore, every table in BCNF is in 3NF, but not every table in 3NF is in BCNF.

Working / Process

1. Identifying Functional Dependencies

• Analyze the attributes in the table to determine which columns define the values of other columns.
• List all dependencies (e.g., Student_ID -> Course_Name).

2. Eliminating Transitive Dependencies (3NF)

• If you find a dependency A -> B and B -> C, create a new table for the attributes B and C.
• Keep the determinant (B) as a foreign key in the original table to maintain the relationship.

[Table: Student_Detail]
Student_ID (PK) -> City
City -> Zip_Code

Transformation to 3NF:
Table 1: [Student_ID, City]
Table 2: [City, Zip_Code]

3. Applying BCNF Rules

• Check if every determinant in the table is a candidate key.
• If a determinant is not a candidate key, decompose the relation into two or more tables to ensure the determinant becomes a key in the new table.

[Table: Student_Project]
Student_ID, Project_ID -> Advisor
Advisor -> Project_ID

Transformation to BCNF:
Table 1: [Student_ID, Advisor]
Table 2: [Advisor, Project_ID]

Advantages / Applications

• Data Integrity: Reduces the risk of data inconsistency during updates, insertions, and deletions.
• Efficient Storage: Minimizes redundant data, saving disk space and improving performance.
• Simplified Maintenance: Tables are smaller and more specialized, making the database schema easier to manage and scale for complex applications.

Summary

3NF and BCNF are systematic normalization techniques that structure databases to minimize redundancy and prevent logical data errors. 3NF removes transitive dependencies where non-key columns rely on other non-key columns, while BCNF enforces a stricter rule requiring all determinants to be superkeys. Important terms to remember include functional dependency, transitive dependency, candidate key, superkey, and normalization.