数据库范式5nf_第四范式（4NF）| 数据库管理系统

朴灿烈づ我的快乐病毒、 2023-03-05 09:57 17阅读 0赞

数据库范式5nf

**Fourth normal form (4NF)** is a normal form used in [database normalization][], in which there are no non-trivial multivalued dependencies except a candidate key. After Boyce–Codd normal form (BCNF), 4NF is the next level of normalization. Although the second, third, and Boyce–Codd normal forms operate with functional dependencies, 4NF is operated with a more universal type of dependency known as a multivalued dependency.

**第四范式(4NF)**是[数据库规范化中][database normalization]使用的[范式][database normalization] ，其中没有除候选键之外的非平凡多值依赖项。 在Boyce-Codd范式(BCNF)之后，4NF是下一级别的规范化。 尽管第二，第三和Boyce-Codd范式具有功能依赖性，但4NF却具有更为通用的依赖性类型，即多值依赖性。

A table or relation is in 4NF if and only if, for each attribute of its non-trivial multivalued dependencies P→Q, P is a super key—that means, P is either a candidate key or a superset thence.

当且仅当表或关系对于其非平凡多值依赖项P→Q的每个属性，P是一个超级键，也就是说，P是一个候选键或一个父集，才在4NF中。

**Properties:**

**特性：**

A relation R with attributes, its values and tuples is in 4NF if and only if the following conditions are satisfied.

当且仅当满足以下条件时，带有属性，其值和元组的关系R才为4NF。

1.  The relation or table should be in the Boyce-Codd Normal Form (BCNF).
    
    关系或表格应采用Boyce-Codd范式(BCNF)。
2.  The relation or table should not have any Multivalued Dependency.
    
    关系或表不应具有任何多值依赖关系。

A table which consists of a multivalued dependency breaches the normalization attainment of **Fourth Normal Form (4NF)** because it generates unneeded redundancies and can contribute to incompatible data which will not be inconsistency. To take this forward and up towards 4NF, it is important to divide this data and information into two tables or relations.

由多值依赖关系组成的表违反了**第四范式(4NF)**的归一化程度，因为它产生了不必要的冗余，并且可能导致不兼容的数据，并且不会造成不一致。 为了朝着4NF迈进，将这些数据和信息分成两个表或关系很重要。

**Multivalued Dependency**

**多值依赖**

Multivalued dependency takes place when there are one or more rows in a table or relation which indicates the existence of one or more other rows in the same table or relation. In another way, if we observe, two attributes (or columns) in a table or relation are not dependent on each other, but both of them depend on a third attribute. A multivalued dependency always needs a minimum of three attributes because it contains at least two attributes that are dependent on a third.

当表或关系中存在一个或多个行时，即表示同一表或关系中存在一个或多个其他行，则发生多值依赖。 用另一种方式，如果我们观察到，表或关系中的两个属性(或列)彼此不依赖，但是它们两者都依赖于第三个属性。 多值依赖项始终至少需要三个属性，因为它包含至少两个依赖于第三个属性。

For a Functional dependency in which P holds Q (P -> Q), if for a single value of P, multiple values of Q exists, then the table or relation may have a multi-valued dependency. The table or relation should contain minimally three attributes and Q and S should not be dependent for P ->> Q multivalued dependency.

对于其中P保持Q(P-> Q)的功能依赖关系，如果对于P的单个值，存在Q的多个值，则表或关系可能具有多值依赖关系。 该表或关系应至少包含三个属性，并且Q和S不应依赖于P->> Q多值依赖关系。

If the following points are satisfied, then a table or relation has a multivalued dependency,

如果满足以下几点，则表或关系具有多值依赖关系，

1.  In a table, for a Functional Dependency P which holds Q (P → Q), if for a single value of P, multiple value of Q exists, then the table may have multi-valued dependency.
    
    在一个表中，对于具有Q(P→Q)的功能依赖性P，如果对于P的单个值，存在Q的多个值，则该表可能具有多值依赖性。
2.  Also, the condition is that a table should have at-least three columns for it to have a multivalued dependency.
    
    同样，条件是一个表应至少具有三列，以使其具有多值依赖关系。
3.  And, for a relation R(P, Q, S), if there is a multi-valued dependency between P and Q, then Q and S should not be dependent on each other.
    
    并且，对于关系R(P，Q，S)，如果在P和Q之间存在多值依赖关系，则Q和S不应相互依赖。

**Example:**

**例：**

Consider a database table which contain student Name, Hobby and subject,

考虑一个包含学生姓名，爱好和科目的数据库表，

<table> 
 <tbody> 
  <tr> 
   <th>Name</th> 
   <th>Hobby</th> 
   <th>Subject</th> 
  </tr> 
  <tr> 
   <td>Arvind</td> 
   <td>Cricket</td> 
   <td>Maths</td> 
  </tr> 
  <tr> 
   <td>Sameer</td> 
   <td>Football</td> 
   <td>Science</td> 
  </tr> 
  <tr> 
   <td>Rajeev</td> 
   <td>Chess</td> 
   <td>Social science</td> 
  </tr> 
  <tr> 
   <td>Simran</td> 
   <td>Badminton</td> 
   <td>English</td> 
  </tr> 
  <tr> 
   <td>Rajat</td> 
   <td>Singing</td> 
   <td>Hindi</td> 
  </tr> 
 </tbody> 
</table>

In the above table, there is no connection between the columns of Hobby and Subject. They are both independent of each other.

在上表中，“爱好”和“主题”列之间没有任何联系。 它们彼此独立。

So there is a multivalued dependency, which assists towards unneeded repetition and redundancy of data and other anomalies as well.

因此，存在一个多值依赖关系，它也有助于不必要的重复和数据冗余以及其他异常。

To make the above relation satisfy with the **fourth normal form** condition and its properties, we can divide the table into two tables, which contain Name and Subject in one table and Name and Hobby in another table.

为了使上述关系满足**第四范式**条件及其属性，我们可以将表分为两个表，一个表中包含Name和Subject，另一表中包含Name和Hobby。

<table> 
 <tbody> 
  <tr> 
   <th>Name</th> 
   <th>Hobby</th> 
  </tr> 
  <tr> 
   <td>Arvind</td> 
   <td>Cricket</td> 
  </tr> 
  <tr> 
   <td>Sameer</td> 
   <td>Football</td> 
  </tr> 
  <tr> 
   <td>Rajeev</td> 
   <td>Chess</td> 
  </tr> 
  <tr> 
   <td>Simran</td> 
   <td>Badminton</td> 
  </tr> 
  <tr> 
   <td>Rajat</td> 
   <td>Singing</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <th>Name</th> 
   <th>Subject</th> 
  </tr> 
  <tr> 
   <td>Arvind</td> 
   <td>Maths</td> 
  </tr> 
  <tr> 
   <td>Sameer</td> 
   <td>Science</td> 
  </tr> 
  <tr> 
   <td>Rajeev</td> 
   <td>Social science</td> 
  </tr> 
  <tr> 
   <td>Simran</td> 
   <td>English</td> 
  </tr> 
  <tr> 
   <td>Rajat</td> 
   <td>Hindi</td> 
  </tr> 
 </tbody> 
</table>

Now, this relation satisfied the conditions of the fourth normal form.

现在，该关系满足第四范式的条件。

A table can also contain a functional dependency along with multi-valued dependency. In that case, the functionally dependent columns are transferred in a separate table and the multi-valued dependent columns are transferred to separate tables.

表还可以包含功能依赖项以及多值依赖项。 在这种情况下，功能相关的列将转移到单独的表中，多值相关的列将转移到单独的表中。

> 翻译自: [https://www.includehelp.com/dbms/fourth-normal-form-4nf.aspx][https_www.includehelp.com_dbms_fourth-normal-form-4nf.aspx]

数据库范式5nf

[database normalization]: https://www.includehelp.com/dbms/normalization-in-database-management-system.aspx
[https_www.includehelp.com_dbms_fourth-normal-form-4nf.aspx]: https://www.includehelp.com/dbms/fourth-normal-form-4nf.aspx