nf范式_第三范式（3NF）

最新推荐文章于 2023-10-16 08:44:26 发布

cunfen6312

最新推荐文章于 2023-10-16 08:44:26 发布

阅读量406

点赞数

文章标签：数据库 java .net html5 css3

原文链接：https://www.studytonight.com/dbms/third-normal-form.php

版权

nf范式

Third Normal Form is an upgrade to Second Normal Form. When a table is in the Second Normal Form and has no transitive dependency, then it is in the Third Normal Form.

第三范式是对第二范式的升级。当表为第二范式且没有传递依存关系时，则为第三范式。

The video below covers the concept of Third Normal Form in details.

下面的视频详细介绍了第三范式的概念。

演示地址

In our last tutorial, we learned about the second normal form and even normalized our Score table into the 2nd Normal Form.

在上一个教程中，我们了解了第二种范式，甚至将Score表标准化为第二种范式。

So let's use the same example, where we have 3 tables, Student, Subject and Score.

因此，让我们使用同一示例，其中有3个表Student ， Subject和Score 。

学生桌 (Student Table)

student_id	name	reg_no	branch	address
10	Akon	07-WY	CSE	Kerala
11	Akon	08-WY	IT	Gujarat
12	Bkon	09-WY	IT	Rajasthan

学生卡	名称	reg_no	科	地址
10	阿Kong	07年	自学考试	喀拉拉邦
11	阿Kong	08年	它	古吉拉特邦
12	布Kong	09月	它	拉贾斯坦邦

主题表 (Subject Table)

subject_id	subject_name	teacher
1	Java	Java Teacher
2	C++	C++ Teacher
3	Php	Php Teacher

subject_id	subject_name	老师
1个	Java	Java老师
2	C ++	C ++老师
3	p	Php老师

得分表 (Score Table)

score_id	student_id	subject_id	marks
1	10	1	70
2	10	2	75
3	11	1	80

score_id	学生卡	subject_id	分数
1个	10	1个	70
2	10	2	75
3	11	1个	80

In the Score table, we need to store some more information, which is the exam name and total marks, so let's add 2 more columns to the Score table.

在“分数”表中，我们需要存储更多信息，即考试名称和总分，因此让我们在“分数”表中再添加两列。

score_id	student_id	subject_id	marks	exam_name	total_marks

score_id	学生卡	subject_id	分数	考试名称	total_marks

第三范式的要求 (Requirements for Third Normal Form)

For a table to be in the third normal form,

为了使表格具有第三范式，

It should be in the Second Normal form.
它应为第二范式。
And it should not have Transitive Dependency.
并且它不应该具有传递依赖性。

什么是传递依存关系？ (What is Transitive Dependency?)

With exam_name and total_marks added to our Score table, it saves more data now. Primary key for our Score table is a composite key, which means it's made up of two attributes or columns → student_id + subject_id.

将exam_name和total_marks添加到我们的分数表后，它现在可以保存更多数据。我们的Score表的主键是一个复合键，这意味着它由两个属性或列→ student_id + subject_id组成 。

Our new column exam_name depends on both student and subject. For example, a mechanical engineering student will have Workshop exam but a computer science student won't. And for some subjects you have Prctical exams and for some you don't. So we can say that exam_name is dependent on both student_id and subject_id.

我们新的列exam_name取决于学生和学科。例如，机械工程专业的学生将参加Workshop考试，而计算机科学专业的学生则没有。对于某些科目，您需要进行Practical考试，而对于某些科目则没有。因此，我们可以说exam_name依赖于student_id和subject_id 。

And what about our second new column total_marks? Does it depend on our Score table's primary key?

那第二个新列total_marks呢？它是否取决于我们的分数表的主键？

Well, the column total_marks depends on exam_name as with exam type the total score changes. For example, practicals are of less marks while theory exams are of more marks.

好吧， total_marks列取决于exam_name因为总分的变化与考试类型有关。例如，实践的得分较低，而理论考试的得分较高。

But, exam_name is just another column in the score table. It is not a primary key or even a part of the primary key, and total_marks depends on it.

但是， exam_name只是分数表中的另一列。它不是主键，也不是主键的一部分， total_marks依赖total_marks 。

This is Transitive Dependency. When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.

这是传递依存关系 。当非素数属性取决于其他非素数属性而不是素数属性或主键时。

如何删除传递依赖项？ (How to remove Transitive Dependency?)

Again the solution is very simple. Take out the columns exam_name and total_marks from Score table and put them in an Exam table and use the exam_id wherever required.

同样，解决方案非常简单。从“分数”表中取出“ exam_name和“ total_marks ”列，并将其放入“ 考试”表中，并在需要时使用exam_id 。