数据库学习笔记——设计三大范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库。这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
When designing relational database, we should comply with different specifications and design a reasonable relational database. These different specification requirements are called different paradigms. The higher the paradigm submission specification, the smaller the redundancy of the paradigm database.

目前关系数据库有六种范式：

At present, there are six normal forms in relational database:

• 第一范式（1NF）
• 第二范式（2NF）
• 第三范式（3NF）
• 巴斯-科德范式（BCNF）
• 第四范式(4NF）
• 第五范式（5NF，又称完美范式）

the first normal form (1NF), the second normal form (2NF), the third normal form(3NF), the Bass-Cod normal form(BCNF), the fourth normal form(4NF) and the fifth normal form(5NF, also known as perfect normal form).

一般说来，数据库只需满足第三范式就行了。

Generally speaking, the database only needs to satisfy the third normal form.

第一范式First Normal Form:

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
In any relational database, the first normal form  is the basic requirement of relational schema. A database that does not satisfy the first normal form  is not a relational database.
The so-called first normal form  refers to that every column of a database table is an indivisible basic data item, and there can be no more than one value in the same column, that is, an attribute in an entity can not have more than one value or duplicate attributes. If duplicate attributes occur, a new entity may need to be defined. The new entity consists of duplicate attributes, and there is a one-to-many relationship between the new entity and the original entity. In the first normal form , each row of the table contains only one instance of information. In short, the first normal form is non-repetitive columns.

第二范式Second Normal Form:

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。
The second normal form  is established on the basis of the first normal form , that is, to satisfy the second normal form , the first normal form  must be satisfied first. The second normal form  requires that each instance or row in a database table must be uniquely partitioned. In order to achieve the distinction, a column is usually added to the table to store the unique identity of each instance.
The second normal form  requires entity attributes to depend entirely on the primary keyword. The so-called complete dependence refers to the fact that there can not exist attributes that only depend on a part of the primary keyword. If there exists, then this attribute and this part of the primary keyword should be separated to form a new entity, and the relationship between the new entity and the original entity is one-to-many. In order to achieve the distinction, a column is usually added to the table to store the unique identity of each instance. In short, the second normal form is that non-primary attributes are not partially dependent on primary keywords.

第三范式Third Normal Form:

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性。
To satisfy the third normal form , the second normal form  must be satisfied first. In short, the third normal form  requires that a database table does not contain non-primary keyword information already contained in other tables. In short, the third normal form is that attributes do not depend on other non-principal attributes.

转载于:https://www.cnblogs.com/stephen-java/p/10533918.html