什么是数据库范式，能否举例说明? [英] What are database normal forms and can you give examples?
问题描述
在关系型数据库设计中，有一个数据库规范化或简称规范化的概念，是一个组织列(属性)和表(关系)以减少数据冗余，提高数据完整性的过程.(如维基百科上所写).
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 中，因为第四列不依赖于整个键 - 而只是其中的一部分.课程名称取决于课程的 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 中所做的大致相同 - 从该表中取出教师姓名字段，并将其放入自己的字段中，该字段以教师 ID 为键.
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.
这篇关于什么是数据库范式，能否举例说明?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
