Third Normal Form (3NF) PDF

604 Third Normal Form (3NF) * * The Third Normal Form (3NF) is a further step in the normalization process that eliminates **...

604 Third Normal Form (3NF) * * The Third Normal Form (3NF) is a further step in the normalization process that eliminates ** ** ** ** ==* * **~~ transitive dependencies in a table. ~~** == ** 3NF ** == ensures that non-key attributes are directly dependent on the primary key ** ** *** *** ** **== and not indirectly dependent through another non-key attribute. *** *** ~~ ~~ ** ** == By achieving 3NF , we reduce redundancy even further, making the database more efficient ** **== * * ~~ ~~ * * and consistent. * * ** Note: For a table to be in 3NF, it must first be in 2NF. ** ** ** == ** **== * Requirements of 3NF * To satisfy the requirements of 3NF: ** ** 1. The table must already be in 2NF. == * * ** **== 2. There should be no transitive dependencies , meaning non-key attributes should not ==*** *** **~~ ~~**== ** ** ~~ depend on other non-key attributes. ~~ * Example: Student_Department Table * * * Consider the following Student_Department table, which records information about ** ` ` ** ` students , their departments , and department locations. ` ` ` ` ` Here, Student_ID is the primary key. ** ` ` ** ` Student_ID ` ` Student_Name ` ` Department_ID ` ** ` Department_Nam ` Department_ e ` ** ation `  101 Alice Smith D01 Science Building A 102 Bob Johnson D02 Arts Building B 103 Carol White D03 Commerce Building C In this table: ** ` Student_ID is the primary key. ` ** ** ** *** ` Department_ID uniquely determines Department_Name and Department_Location , ` ** ** ` ` ** ** ` ` ** so we have transitive dependencies: ** ** * ** ` Student_ID → Department_ID ` ** *** ` Department_ID → Department_Name and ` ** ** ` Department_ID → Department_Location ` *** Because Department_Name and Department_Location depend on Department_ID (a ** ` ` ** ** ` ` ** ** ` ` ** ** non-key attribute) instead of directly on Student_ID , this table violates 3NF due to ** ** ` ` ** * * ** ** ** == transitive dependencies. == ** * Converting to 3NF * To bring this table into 3NF, we need to remove the transitive dependencies by separating ** ** == **~~ ~~** the table into two tables: Student and Department. ** ` ` ** ** ` ` **== * Here’s how we can structure them: * ** Step 1: Create the Student Table** * * ** ` ` ** The Student table will store information specific to each student, with Student_ID as the ** ` ` ** ** ` ` ** ** primary key. ** ` Student_ID ` ` Student_Name ` ` Department_ID ` 101 Alice Smith D01 102 Bob Johnson D02 103 Carol White D03 ** Step 2: Create the Department Table ** * * ** ` ` ** The Department table will store department -related information, with Department_ID as ** ` ` ** ` ` ** ` ` **  ! the primary key. ** ** ** ` Department_ID ` ** ` Department_Name ` ` Department_Location ` D01 Science Building A D02 Arts Building B D03 Commerce Building C Result After Conversion to 3NF * * After separating the original table into Student and Department tables: ** ` ` ** ** ` ` ** The Student table now contains only the attributes directly related to students. ** ` ` ** * * ` ` The Department table only contains department -related attributes. ** ` ` ** ` ` The transitive dependency has been removed, as Department_Name and **~~ ~~** * * ** ` ` ** ** ` Department_Location are now in a separate table directly dependent on ` ** * * * * ** ` Department_ID. ` ** * Why 3NF is Important * * * ** 3NF helps: ** *** Reduce Redundancy: * ~~ ~~** * By eliminating transitive dependencies, we avoid repeating information (e.g., * **~~ ~~** * * ~~ ~~ ` department location ). ` * Improve Data Integrity: * ** ** ** Updates become more manageable, as each piece of data is stored in only one place. ** * * * * * * * Simplify Maintenance: * ** ** Changes in one table (e.g., updating department location ) don’t require updates in ` ` *** *** ~~ multiple rows, reducing the chance of errors. ~~ * * ~~ ~~ In the next lesson, we’ll discuss Boyce-Codd Normal Form (BCNF) , an advanced form of ==** ** ** ** == * * ** 3NF that resolves additional dependencies, ensuring a higher level of normalization. **