First, Second & Third Normal Forms

CS403 Database Management Systems Lecture 20 delivers an exhaustive exploration of relational normal forms, guiding Virtual University students through Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF). Based on foundational principles from Catherine Ricardo and Raghu Ramakrishnan, this module focuses on eliminating data anomalies through disciplined relational decomposition. A relation resides in Second Normal Form (2NF) if and only if it is in First Normal Form and every non-key attribute is fully functionally dependent on the entire primary key. Consequently, if a relation possesses a single-attribute primary key, it is automatically in 2NF; partial functional dependencies only threaten relations governed by composite primary keys. The lecture illustrates this through an unnormalized CLASS relation where student names depend solely on student ID and course rooms depend solely on course ID. Failure to achieve 2NF produces severe insertion, deletion, and update anomalies, which are resolved by decomposing the table into distinct student, course, and enrollment relations linked through foreign keys. Progressing further, Third Normal Form (3NF) requires a relation to be in 2NF with zero transitive dependencies, meaning no non-key attribute functionally determines another non-key attribute. Transitive dependencies cause duplicate values and inconsistent modifications, demonstrated by student relations where program credit requirements depend on degree names rather than student IDs. Decomposing relations into independent entity tables eliminates transitive chains while preserving functional integrity. The curriculum then introduces Boyce-Codd Normal Form (BCNF), a stricter refinement where every determinant must strictly be a candidate key. While most 3NF relations satisfy BCNF, subtle anomalies arise when relations feature multiple composite, overlapping candidate keys. The lecture outlines BCNF decomposition techniques using the classical student enrollment scenario and introduces higher normal forms, including Fourth Normal Form (4NF) addressing multivalued dependencies, Fifth Normal Form (5NF) handling join dependencies and project-join normal forms, and Domain-Key Normal Form (DKNF) which eliminates all possible residual update anomalies. Essential for CS403 midterm examinations, solved past papers by Moaaz and Waqar Siddhu, and chapter-wise solved practice MCQs, mastering 2NF, 3NF, and BCNF ensures optimal database architecture.