第一范式第二范式第三范式_什么是第二范式？-CSDN博客

第一范式第二范式第三范式

For a table to be in the Second Normal Form, it must satisfy two conditions:

为了使表格处于第二范式，它必须满足两个条件：

The table should be in the First Normal Form.
该表应为“第一范式”。
There should be no Partial Dependency.
不应有部分依赖性。

If you want you can skip the video, as the concept is covered in detail below the video.

如果需要，可以跳过视频，因为在视频下方详细介绍了该概念。

演示地址

What is Partial Dependency? Do not worry about it. First let's understand what is Dependency in a table?

什么是部分依赖 ？不用担心。首先让我们了解表中的依赖关系是什么？

什么是依赖性？ (What is Dependency?)

Let's take an example of a Student table with columns student_id, name, reg_no(registration number), branch and address(student's home address).

让我们以一个Student表为例，该表的reg_no student_id ， name ， reg_no (注册号)， branch和address (学生的家庭住址)。

student_id	name	reg_no	branch	address

学生卡	名称	reg_no	科	地址

In this table, student_id is the primary key and will be unique for every row, hence we can use student_id to fetch any row of data from this table

在此表中， student_id是主键，并且对于每一行都是唯一的，因此我们可以使用student_id从该表中获取任何数据行

Even for a case, where student names are same, if we know the student_id we can easily fetch the correct record.

即使在学生姓名相同的情况下，如果我们知道student_id我们也可以轻松获取正确的记录。

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

学生卡	名称	reg_no	科	地址
10	阿Kong	07年	自学考试	喀拉拉邦
11	阿Kong	08年	它	古吉拉特邦

Hence we can say a Primary Key for a table is the column or a group of columns(composite key) which can uniquely identify each record in the table.

因此，我们可以说表的主键是可以唯一标识表中每个记录的列或一组列(复合键)。

I can ask from branch name of student with student_id 10, and I can get it. Similarly, if I ask for name of student with student_id 10 or 11, I will get it. So all I need is student_id and every other column depends on it, or can be fetched using it.

我可以从student_id 10的student的分支名称中询问，我可以得到它。同样，如果我要求输入具有student_id 10或11的学生的姓名，我会得到的。因此，我需要的只是student_id ，其他所有列都取决于它，或者可以使用它来获取。

This is Dependency and we also call it Functional Dependency.

这是依赖关系 ，我们也称它为功能依赖关系 。

什么是部分依赖？ (What is Partial Dependency?)

Now that we know what dependency is, we are in a better state to understand what partial dependency is.

既然我们知道了什么是依赖关系，我们就可以更好地了解什么是部分依赖关系。

For a simple table like Student, a single column like student_id can uniquely identfy all the records in a table.

对于像Student这样的简单表，像student_id这样的单个列可以唯一地标识表中的所有记录。

But this is not true all the time. So now let's extend our example to see if more than 1 column together can act as a primary key.

但这并非一直如此。因此，现在让我们扩展示例，看看是否有超过1列可以同时充当主键。

Let's create another table for Subject, which will have subject_id and subject_name fields and subject_id will be the primary key.

让我们为Subject创建另一个表，该表将具有subject_id和subject_name字段，而subject_id将为主键。

subject_id	subject_name
1	Java
2	C++
3	Php

subject_id	subject_name
1个	Java
2	C ++
3	p

Now we have a Student table with student information and another table Subject for storing subject information.

现在，我们有一个带有学生信息的Student表和另一个用于存储学科信息的Subject表。

Let's create another table Score, to store the marks obtained by students in the respective subjects. We will also be saving name of the teacher who teaches that subject along with marks.

让我们创建另一个表得分，存储由学生在各学科中取得的分数。我们还将保存教授该主题的老师的名字以及分数。

score_id	student_id	subject_id	marks	teacher
1	10	1	70	Java Teacher
2	10	2	75	C++ Teacher
3	11	1	80	Java Teacher

score_id	学生卡	subject_id	分数	老师
1个	10	1个	70	Java老师
2	10	2	75	C ++老师
3	11	1个	80	Java老师

In the score table we are saving the student_id to know which student's marks are these and subject_id to know for which subject the marks are for.

在分数表中，我们保存了student_id来知道这些学生的分数，而subject_id来知道分数用于哪些学科。

Together, student_id + subject_id forms a Candidate Key(learn about Database Keys) for this table, which can be the Primary key.

student_id + subject_id一起构成了该表的候选键 (有关数据库键的知识 )，它可以是主键。

Confused, How this combination can be a primary key?

困惑，此组合如何成为主键？

See, if I ask you to get me marks of student with student_id 10, can you get it from this table? No, because you don't know for which subject. And if I give you subject_id, you would not know for which student. Hence we need student_id + subject_id to uniquely identify any row.

瞧，如果我要求您给我student_id 10的学生分数，您可以从此表中获得吗？不，因为您不知道哪个学科。如果我给您subject_id ，您将不知道是哪个学生。因此，我们需要student_id + subject_id来唯一标识任何行。

但是部分依赖在哪里？ (But where is Partial Dependency?)

Now if you look at the Score table, we have a column names teacher which is only dependent on the subject, for Java it's Java Teacher and for C++ it's C++ Teacher & so on.

现在，如果您查看“ 分数”表，我们将有一个仅取决于主题的名称为teacher的列，对于Java，它是Java老师，对于C ++是C ++老师，依此类推。

Now as we just discussed that the primary key for this table is a composition of two columns which is student_id & subject_id but the teacher's name only depends on subject, hence the subject_id, and has nothing to do with student_id.

现在，正如我们刚刚讨论的那样，该表的主键是由两列组成的，即student_id和subject_id但是老师的姓名仅取决于subject，因此取决于subject_id ，而与student_id无关。

This is Partial Dependency, where an attribute in a table depends on only a part of the primary key and not on the whole key.

这是部分依赖关系 ，其中表中的属性仅依赖于主键的一部分，而不依赖于整个键。

如何删除部分依赖关系？ (How to remove Partial Dependency?)

There can be many different solutions for this, but out objective is to remove teacher's name from Score table.

可以有许多不同的解决方案，但目的是从成绩表中删除老师的名字。

The simplest solution is to remove columns teacher from Score table and add it to the Subject table. Hence, the Subject table will become:

最简单的解决方案是从“分数”表中删除“ teacher列并将其添加到“主题”表中。因此，主题表将变为：

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老师

And our Score table is now in the second normal form, with no partial dependency.

现在，我们的成绩表采用第二种标准形式，没有部分依赖性。

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

快速回顾 (Quick Recap)

For a table to be in the Second Normal form, it should be in the First Normal form and it should not have Partial Dependency.
为了使表具有第二普通形式，该表应具有第一普通形式，并且不应具有部分依赖关系。
Partial Dependency exists, when for a composite primary key, any attribute in the table depends only on a part of the primary key and not on the complete primary key.
存在部分依赖关系时，对于复合主键，表中的任何属性仅取决于主键的一部分，而不取决于完整的主键。
To remove Partial dependency, we can divide the table, remove the attribute which is causing partial dependency, and move it to some other table where it fits in well.
要删除部分依赖关系，我们可以对表进行划分，删除导致部分依赖关系的属性，然后将其移至其他合适的表中。