在数据库设计中，1NF，2NF和3NF是什么？ [英] What are 1NF, 2NF and 3NF in database design?

问题描述

我理解数据库规范化用于避免重复/冗余数据，并涉及为某些事情创建单独的表，但我不能确定我明白什么1NF，2NF和3NF。

I understand that database normalization is used to avoid duplicate/redundant data, and involves creating separate tables for certain things, but I'm not quite sure I understand what 1NF, 2NF and 3NF are. Can somebody explain the difference between, and how to convert between normal forms?

推荐答案

1NF是最基本的正常形式 - 每个表格中的单元格必须只包含一条信息，并且不能有重复的行。

1NF is the most basic of normal forms - each cell in a table must contain only one piece of information, and there can be no duplicate rows.

2NF和3NF都是取决于主键。回想一下，主键可以由多个列组成。正如Chris在他的回答中所说的：

2NF and 3NF are all about being dependent on the primary key. Recall that a primary key can be made up of multiple columns. As Chris said in his response:

数据取决于键[1NF]，整个键[2NF]，除了键[3NF] Codd ）。

The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help me Codd).

假设您有一个表格，其中包含在某个学期学习的课程，并且您有以下数据：

Say you have a table containing courses that are taken in a certain semester, and you have the following data:

|-----Primary Key----|               uh oh |
                                           V
CourseID | SemesterID | #Places  | Course Name  |
------------------------------------------------|
IT101    |   2009-1   | 100      | Programming  |
IT101    |   2009-2   | 100      | Programming  |
IT102    |   2009-1   | 200      | Databases    |
IT102    |   2010-1   | 150      | Databases    |
IT103    |   2009-2   | 120      | Web Design   |

这是不在2NF ，因为第四列不依赖整个键，但只是其中的一部分。课程名称取决于课程的ID，但与进入的学期无关。因此，如您所见，我们有重复的信息 - 几行告诉我们IT101是编程，而IT102是数据库。所以我们通过将课程名称移动到另一个表中来解决这个问题，其中CourseID是ENTIRE键。

This is not in 2NF, because the fourth column does not rely upon the entire key - but only a part of it. The course name is dependent on the Course's ID, but has nothing to do with which semester it's taken in. Thus, as you can see, we have duplicate information - several rows telling us that IT101 is programming, and IT102 is Databases. So we fix that by moving the course name into another table, where CourseID is the ENTIRE key.

Primary Key |

CourseID    |  Course Name |
---------------------------|
IT101       | Programming  |
IT102       | Databases    |
IT103       | Web Design   |

无冗余！

好吧，我们还要将课程的老师姓名和一些细节添加到RDBMS中：

Okay, so let's say we also add the name of the teacher of the course, and some details about them, into the RDBMS:

|-----Primary Key----|                           uh oh |
                                                       V
Course  |  Semester  |  #Places   |  TeacherID  | TeacherName  |
---------------------------------------------------------------|
IT101   |   2009-1   |  100       |  332        |  Mr Jones    |
IT101   |   2009-2   |  100       |  332        |  Mr Jones    |
IT102   |   2009-1   |  200       |  495        |  Mr Bentley  |
IT102   |   2010-1   |  150       |  332        |  Mr Jones    |
IT103   |   2009-2   |  120       |  242        |  Mrs Smith   |

现在希望很明显，TeacherName依赖于TeacherID，因此这是 3NF 。为了解决这个问题，我们做的事情和我们在2NF中的做法一样 - 把TeacherName字段从这个表中取出来，并把它放在自己的里面，以TeacherID作为键。

Now hopefully it should be obvious that TeacherName is dependent on TeacherID - so this is not in 3NF. To fix this, we do much the same as we did in 2NF - take the TeacherName field out of this table, and put it in its own, which has TeacherID as the key.

 Primary Key |

 TeacherID   | TeacherName  |
 ---------------------------|
 332         |  Mr Jones    |
 495         |  Mr Bentley  |
 242         |  Mrs Smith   |

无冗余!!

要记住的是，如果某个东西不是在1NF，它不是在2NF或3NF。因此，每个额外的普通表单都需要所有较低的正常表单加上一些额外的条件，这些条件必须全部。

One important thing to remember is that if something is not in 1NF, it is not in 2NF or 3NF either. So each additional Normal Form requires everything that the lower normal forms had, plus some extra conditions, which must all be fulfilled.

这篇关于在数据库设计中，1NF，2NF和3NF是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！