603 Second Normal Form (2NF) PDF

603 Second Normal Form (2NF) * The Second Normal Form (2NF) is the next stage in database normalization after ** ==...

603 Second Normal Form (2NF) * The Second Normal Form (2NF) is the next stage in database normalization after ** == == ** achieving the First Normal Form (1NF). ** ** * *** 2NF builds on 1NF by ** ** ** * * == eliminating partial dependencies , ~~** **~~== * ensuring that every non-key attribute in a table is fully (functionally) dependent ** ** on the entire primary key. ** ** * In other words, 2NF requires that each non-key attribute must rely on the whole primary * ** ** * * ** ** * * ** key, not just part of it. ** * * ** Note: A table needs to be in 1NF before it can be converted to 2NF. ** ** ** *** *** ** ** ** Partial dependencies only apply to tables with composite primary keys (primary keys ** == ** **== consisting of multiple columns). * Requirements of 2NF * * To satisfy the requirements of 2NF: ** ** * 1. The table must already be in 1NF. * == ** **== * 2. There should be no partial dependencies, meaning all non-key attributes must * ** ** **~~ ~~** ==** ** ** ** (functionally) depend on the entire primary key , not just part of it. ** **== * * Example: Student_Course_Enrollment * * * Consider the following Student_Course_Enrollment table, which records students ` ` ` ` enrolled in various courses along with their department. ` ` ` ` * In this table, the primary key is a composite key consisting of Student_ID and ** ** ** ** ` ` ` Course_ID. ` *  ! ! ** Student_ID ** ** == Course_ID == ** ** ` Student_Name ` ** ** ` Department ` ** 101 C101 Alice Smith Science 101 C102 Alice Smith Science 102 C101 Bob Johnson Arts 103 C103 Carol White Commerce * In this table: * The primary key is {Student_ID, Course_ID}. ` ` * ` Student_Name and Department (functionally) depend only on Student_ID , not on ` ` ` == ` ` == ** ** the entire primary key {Student_ID, Course_ID}. ** ** ` ` * ==* This means we have partial dependencies, violating 2NF. ** ** ** ** *== Converting to 2NF * To bring this table into 2NF, we need to remove the partial dependencies by separating the ** ** **~~ ~~** table into two tables: Student and Enrollment. ` ` ` ` * * Here’s how we can structure them: * Step 1: Create the Student Table * * The Student table stores information unique to each student, with Student_ID as the ` ` ` ` primary key. ** Student_ID ** ** Student_Name ** ** Department ** 101 Alice Smith Science 102 Bob Johnson Arts 103 Carol White Commerce Step 2: Create the Enrollment Table * * * The Enrollment table records the enrollment details of each student in various courses , ` ` ` ` ` ` using {Student_ID, Course_ID} as the composite primary key. ` ` ** ** * ** Student_ID ** ** Course_ID **  101 C101 101 C102 102 C101 103 C103 * Result After Conversion to 2NF * ** After separating the original table into Student and Enrollment tables: ` ` ` ` ** * ` Student_Name and Department are now only dependent on Student_ID in the ` ` ` ` ` ` Student table, eliminating partial dependencies. ` == **~~ ~~**== * * The Enrollment table now has only the attributes Student_ID and Course_ID , ` ` ` ` ` ` == which fully (functionally) depend on the composite primary key. ** ** ** **== * The reasoning comes from the principle of full functional dependency : ** == == ** In the Enrollment table, both Student_ID and Course_ID are necessary ` ` ` ` ` ` components of the primary key to uniquely identify an enrollment record. Neither Student_ID nor Course_ID alone can uniquely identify a record: ` ` ` ` ` Student_ID alone: A single student ( Student_ID ) could enroll in ` ` ` multiple courses ( Course_IDs ), so it's not sufficient. ` ` ` Course_ID alone: A single course ( Course_ID ) could have multiple ` ` ` students ( Student_IDs ) enrolled, so it's also not sufficient. ` ` However, the combination of Student_ID and Course_ID is unique for ` ` ` ` each enrollment record, meaning both are fully (functionally) ` ` ** dependent on the composite primary key.** ** ** Thus, attributes in the table (which include just Student_ID and ` ` ` Course_ID themselves here) are fully dependent on the composite ` ** ** ** primary key {Student_ID, Course_ID}. ** ` ` Why 2NF is Important * Achieving 2NF helps reduce redundancy by ensuring that non-key attributes are not ** ** ~~ ~~ ** ** ~~ duplicated unnecessarily. ~~ * * It also improves data consistency by grouping attributes based on their dependencies ** ** on the primary key, making data easier to manage and reducing the chances of update ** ** ~~ anomalies. ~~ * In the next lesson, we’ll cover Third Normal Form (3NF), which addresses transitive * dependencies to further reduce redundancy and ensure data integrity. * * * * *