teach me concept of 1NF,2NF ,3NF in database sql teach me using scaffolding method with interactive elmeents and voice let me fully understand instead of concept only

视频信息

答案文本 复制

视频字幕 复制

Welcome to database normalization! Let's start with a real-world problem. Here we have a student enrollment table that looks simple but contains serious issues. Notice the multi-valued cells in the Courses and Instructor columns - this violates basic database principles. We also see redundant data like 'Science' repeated multiple times in the Department column. These problems lead to data inconsistency, update anomalies, and wasted storage space. Database normalization provides a systematic solution to organize this data properly through a step-by-step process. Now let's learn about First Normal Form, or 1NF. This is the first step in database normalization. 1NF has two key rules: first, each column must contain atomic values - meaning indivisible, single values. Second, there should be no repeating groups. Look at our original table - the Courses column contains multiple values separated by commas, which violates 1NF. To fix this, we split each multi-valued cell into separate rows. Notice how 'Math,Physics' becomes two separate rows - one for Math and one for Physics. The same applies to Alice's courses. Now each cell contains only one atomic value, and we've eliminated repeating groups. This is what 1NF looks like - clean, atomic data with no multi-valued cells. Now let's move to Second Normal Form, or 2NF. To be in 2NF, a table must first be in 1NF and have no partial dependencies. But what is a functional dependency? It's written as A arrow B, meaning A determines B. A partial dependency occurs when a non-key attribute depends on only part of a composite primary key. Look at our 1NF table - it has a composite key of StudentID and CourseID. However, StudentName depends only on StudentID, not the full key. Similarly, CourseName depends only on CourseID. These are partial dependencies that violate 2NF. To fix this, we split the table into three separate tables: Students table with StudentID and StudentName, Courses table with CourseID and CourseName, and Enrollments table with the composite key and Grade. Now each non-key attribute depends on the entire primary key of its table, achieving 2NF. Finally, let's learn about Third Normal Form, or 3NF. To be in 3NF, a table must be in 2NF and have no transitive dependencies. A transitive dependency occurs when a non-key attribute depends on another non-key attribute, creating a chain relationship. Look at our 2NF table - StudentID determines DeptID, and DeptID determines DeptName and DeptHead. This creates transitive dependencies because StudentID indirectly determines department information through DeptID. This causes redundancy - notice how 'Computer Science' and 'Dr. Smith' are repeated for students in the same department. To achieve 3NF, we split this into two tables: a Students table containing StudentID, StudentName, and DeptID as a foreign key, and a separate Departments table with DeptID, DeptName, and DeptHead. Now we've completed the normalization journey from unnormalized data through 1NF, 2NF, to 3NF, achieving optimal data organization with no redundancy, anomalies, or dependencies. Let's practice with a comprehensive example - an Employee Project Assignment system. Here's our original unnormalized table with multiple violations. First, identify the 1NF violation - the Projects column contains multi-valued cells like 'P1,P2'. We fix this by creating separate rows for each project assignment, making all values atomic. Now we have 1NF, but notice the 2NF violation - we have a composite key of EmpID and ProjectID, but EmpName depends only on EmpID, and DeptName and Manager depend only on DeptID through EmpID. These are partial dependencies. We also have transitive dependencies - EmpID determines DeptID, which determines DeptName and Manager, violating 3NF. The solution is to split into three normalized tables: Employees table with EmpID, EmpName, and DeptID; Departments table with DeptID, DeptName, and Manager; and Assignments table with EmpID and ProjectID. This final 3NF design eliminates all redundancy, prevents anomalies, optimizes storage, and maintains data integrity. You've now mastered the complete normalization process from unnormalized data to 3NF!