什么是数据库范式，您可以举一些例子吗？ [英] What are database normal forms and can you give examples?
问题描述
在关系数据库设计中，存在数据库规范化或简单规范化的概念，这是组织列（属性）和表（关系）以减少数据冗余并提高数据完整性的过程。 （如 Wikipedia 所写。）
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).
由于大多数文章都是技术性文章，因此较难理解，我要求有人根据有关1NF，2NF，3NF甚至3.5NF（Boyce-Codd）含义的示例，写一个更容易理解的解释。
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是最基本的普通形式-表中的每个单元格只能包含一条信息，并且不能重复
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都依赖于主键。回想一下，一个主键可以由多列组成。就像克里斯在回答中所说的：
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]（所以请帮助我科德）。
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格式，因为第四列不依赖 entire 键-但只有一部分。课程名称取决于课程的ID，但与课程的学期无关。因此，如您所见，我们拥有重复的信息-几行告诉我们IT101正在编程，而IT102是数据库。因此，我们通过将课程名称移到另一个表中来解决该问题，其中CourseID是整个键。
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中。因此，每一个额外的范式都要求所有较低的范式具有的条件，以及一些必须满足 all 的额外条件。
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.
这篇关于什么是数据库范式，您可以举一些例子吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
