What are the normal forms in Database. How to understand the difference between 1NF 2NF and 3NF

视频信息

答案文本 复制

视频字幕 复制

Normal forms are a series of guidelines used to structure database tables to reduce redundancy and improve data integrity. The most common normal forms are First Normal Form, Second Normal Form, and Third Normal Form. Each normal form builds upon the previous one, creating increasingly organized and efficient database structures. First Normal Form, or 1NF, is the basic structural requirement for database tables. A table is in 1NF if each column contains atomic values, meaning no cell should contain multiple values separated by commas. There should be no repeating groups of columns, and each row must be unique. For example, a customer table with multiple phone numbers in one cell violates 1NF. To fix this, we create separate rows for each phone number, ensuring each cell contains only one value. Second Normal Form, or 2NF, builds upon 1NF by eliminating partial dependencies. A table is in 2NF if it's in 1NF and all non-key attributes are fully functionally dependent on the entire primary key. This is relevant when the primary key is composite, made of multiple columns. For example, in an OrderDetails table with composite primary key OrderID and ProductID, if ProductName depends only on ProductID and not the entire key, this creates a partial dependency. To achieve 2NF, we split this into separate Orders and Products tables. Third Normal Form, or 3NF, eliminates transitive dependencies. A table is in 3NF if it's in 2NF and there are no transitive dependencies, where a non-key attribute depends on another non-key attribute rather than directly on the primary key. For example, in an Employees table, if DepartmentLocation depends on DepartmentName, and DepartmentName depends on EmployeeID, then DepartmentLocation is transitively dependent on EmployeeID. To achieve 3NF, we separate this into Employees and Departments tables, removing the transitive dependency. To summarize the differences between normal forms: First Normal Form ensures atomic values and flat table structure. Second Normal Form builds on 1NF by eliminating partial dependencies, ensuring all non-key attributes depend on the entire primary key. Third Normal Form extends 2NF by removing transitive dependencies, where non-key attributes depend on other non-key attributes. Each normal form builds upon the previous one, progressively reducing redundancy and improving data integrity. Understanding these differences helps database designers create more efficient and maintainable database structures.