database 什么是数据库范式，你能举个例子吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/723998/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What are database normal forms and can you give examples?
提问by barfoon
In relational database design, there is a concept of database normalization or simply normalization, which is a process of organizing columns (attributes) and tables (relations) to reduce data redundancy and improve data integrity. (as written on Wikipedia).
在关系型数据库设计中，有一个数据库规范化或简称规范化的概念，它是一个组织列（属性）和表（关系）以减少数据冗余，提高数据完整性的过程。（如维基百科上所写）。
As most articles are somewhat technical and thus harder to understand, I'm asking for someone to write an easier to understand explanation based on examples about what 1NF, 2NF, 3NF, even 3.5NF (Boyce-Codd) mean.
由于大多数文章都有些技术性，因此更难理解，我要求某人根据有关 1NF、2NF、3NF 甚至 3.5NF（Boyce-Codd）含义的示例编写更易于理解的解释。
回答by Smashery
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.
1NF 是最基本的范式——表格中的每个单元格必须只包含一条信息，并且不能有重复的行。
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:
2NF 和 3NF 都是关于依赖于主键。回想一下，主键可以由多列组成。正如克里斯在他的回应中所说：
The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF] (so help me Codd).
数据取决于密钥 [1NF]、整个密钥 [2NF] 以及密钥 [3NF]（所以帮帮我Codd）。
2NF
2NF
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 |
This is not in 2NF, because the fourth column does not rely upon the entirekey - 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.
这不在 2NF 中，因为第四列不依赖于整个键 - 而只是其中的一部分。课程名称取决于课程的 ID，但与它所在的学期无关。因此，如您所见，我们有重复的信息 - 几行告诉我们 IT101 是编程，而 IT102 是数据库。所以我们通过将课程名称移动到另一个表中来解决这个问题，其中 CourseID 是 ENTIRE 键。
Primary Key |
CourseID | Course Name |
---------------------------|
IT101 | Programming |
IT102 | Databases |
IT103 | Web Design |
No redundancy!
没有冗余！
3NF
3NF
Okay, so let's say we also add the name of the teacher of the course, and some details about them, into the RDBMS:
好的，假设我们还将课程老师的姓名以及有关他们的一些详细信息添加到 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 |
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.
现在希望很明显， TeacherName 依赖于 TeacherID - 所以这不在 3NF 中。为了解决这个问题，我们与在 2NF 中所做的大致相同——从该表中取出教师姓名字段，并将其放入自己的字段中，该字段以教师 ID 作为键。
Primary Key |
TeacherID | TeacherName |
---------------------------|
332 | Mr Jones |
495 | Mr Bentley |
242 | Mrs Smith |
No redundancy!!
没有冗余！！
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 everythingthat the lower normal forms had, plus some extra conditions, which must allbe fulfilled.
要记住的一件重要事情是，如果某物不在 1NF 中，那么它也不在 2NF 或 3NF 中。所以每一个额外的范式都需要低范式所拥有的一切，加上一些额外的条件，这些条件都必须满足。
回答by Chris Shaffer
I've never had a good memory for exact wording, but in my database class I think the professor always said something like:
我对确切的措辞从来没有很好的记忆力，但在我的数据库课上，我认为教授总是说这样的话：
The data depends on the key [1NF], the whole key [2NF] and nothing but the key [3NF].
数据取决于密钥 [1NF]、整个密钥 [2NF] 和密钥 [3NF]。
回答by Dave Markle
Here's a quick, admittedly butcheredresponse, but in a sentence:
这是一个快速的，公认的屠杀回应，但在一句话中：
1NF : Your table is organized as an unordered setof data, and there are no repeating columns.
1NF：你的表被组织成一个无序集的数据，并且没有重复列。
2NF: You don't repeat data in one column of your table because of another column.
2NF：您不会因为另一列而在表的一列中重复数据。
3NF: Every column in your table relates only to your table's key -- you wouldn't have a column in a table that describes another column in your table which isn't the key.
3NF：表中的每一列仅与表的键相关——表中不会有一列描述表中不是键的另一列。
For more detail, see wikipedia...
更多详细信息，请参阅维基百科...
回答by Arcturus
1NF: Only one value per column
1NF：每列只有一个值
2NF: All the non primary key columns in the table should depend on the entire primary key.
2NF：表中所有非主键列都应该依赖于整个主键。
3NF: All the non primary key columns in the table should depend DIRECTLY on the entire primary key.
3NF：表中的所有非主键列都应该直接依赖于整个主键。
I have written an article in more detail over here
我在这里写了一篇更详细的文章
