数据库数据规范化看不懂_数据库管理系统中的规范化

数据库数据规范化看不懂

DBMS中的规范化 (Normalization in DBMS)

Every table must have a single idea. The method by which we divide tables approximately is called normalization and the rest used for normalization is a functional dependency. For the normalization process, every column in a table will have a unique name. Order of rows and columns are insignificant and every cell will contain single or atomic values. Every table will contain a primary key and the value of the primary key cannot be null.

每个表都必须有一个主意。 我们大致划分表的方法称为规范化，而其余用于规范化的方法则是功能相关性。 对于规范化过程，表中的每一列都将具有唯一的名称。 行和列的顺序无关紧要，每个单元格将包含单个或原子值。 每个表都将包含一个主键，并且主键的值不能为null。

Because of normalization number of tables in the database increase because of which excess become slow and query retrieval time increases in that cases, we must be in the position to combine decomposed table to get back the parent table. this process is called denormalization.

在这种情况下，由于数据库中表的规范化数量增加，从而导致多余的速度变慢并且查询检索时间增加，因此我们必须处于组合分解表的位置才能返回父表。 此过程称为非规范化。

DBMS中的规范化表单的类型 (Types of Normalization form in DBMS)

1) First Normalization form

1)第一个规范化表格

A relational schema R is said to be in first normal form if every cell in the row contains atomic values. It means first normal form does not allow composite or multivalued attributes. A table in first normal form will always have exactly one primary key (null value is not allowed in primary key). It always we assumed that if we have a proper relational table than it will always be in first normal form.

如果行中的每个单元格都包含原子值，则关系模式R被称为第一范式。 这意味着第一个范式不允许使用复合或多值属性。 第一个标准格式的表将始终仅具有一个主键(主键中不允许使用空值)。 我们总是假设，如果我们有一个适当的关系表，那么它将始终处于第一范式。

2) Second Normalization form

2)第二标准化表格

A table is said to in second normal form if it is first normal form and there must not be exist any partial dependency.

如果表是第一范式并且不能存在任何部分依赖关系，则称该表为第二范式。

Partial dependency

部分依赖

If a non-prime attribute instead of depending on the entire candidate key depends on its subset than this is the partial dependency (from prime to non-prime).a attribute is called prime attribute if it is the part of any candidate key.

如果非素数属性不是部分依赖项(从素数到非素数)，而不是依赖于整个候选键的子集，那么它是部分依赖项。如果该属性是任何候选键的一部分，则称为素数属性。

If a table is not in second normal form than construct first table for the candidate key only those attribute will come which totally depend on the key and then for every partial dependency we have a separate table.

如果一个表不是第二范式，而不是为候选键构造第一个表，则只有那些完全依赖于键的属性才会出现，然后对于每个部分依赖项，我们都有一个单独的表。

3) Third Normalization form

3)第三规范化形式

A relational table is said third normal form when it is in first normal form and there exit no transitive dependency.

当关系表处于第一范式并且不存在传递依存关系时，则称为第三范式。

A relational schema is said to be in third normal form if every dependency from A → B either A is a superkey or a prime attribute.

如果从A→B的每个依赖关系(无论A是超键还是素数属性)，则关系模式都称为第三范式。

Example- R (ABC) B → C

示例-R(ABC)B→C

Here, A is a prime attribute and BC are the non-prime attribute here dependency from B to C is transitive as one non-prime is finding another non-prime. Here if B gets null values at runtime then we cannot identify C.

在这里， A是素数属性，而BC是非素数属性，这里从B到C的依赖关系是可传递的，因为一个非素数正在找到另一个非素数。 在这里，如果B在运行时获得空值，则我们无法识别C。

4) Boyce-Codd Normalization form (BCNF)

4)Boyce-Codd规范化表格(BCNF)

A relational table is said to be in BCNF normal form if for dependency from A to B and A must be a super key.

如果从A到B的依赖关系以及A必须是超级键，则关系表被称为BCNF范式。

查找关系的最高范式的步骤 (Steps to find the highest normal form of a relation)

1. First of all, find the entire possible candidate key in the given relation.

   首先，找到给定关系中的所有可能的候选键。

2. Divide all given attribute into two categories one is prime and other one is non-prime attributes.

   将所有给定属性分为两类，一类是质数，另一类是非质数属性。

3. Firstly check for the first normal form then second normal form and so on if we fail to satisfy nth normal form than the highest normal form will be n-1.

   首先检查第一个范式，然后检查第二个范式，依此类推，如果我们不满足第n个范式，则最高范式将为n-1。

数据库管理系统异常 (Anomalies in Database Management System)

There are different types of anomalies which can occur in referencing and the referenced relation which we will discuss with the help of an example,

在引用和引用的关系中可能会发生不同类型的异常，我们将借助一个示例来进行讨论，

Ename	Ssn	Dnumber	Dname	Dmgrssn
Smith	1234	5	Research	3334
Wong	3334	5	Research	3334
Zeloya	9998	4	Admin	9876
Wallace	9876	4	Admin	9876
Jabbar	9879	4	Admin	9876

姓	n	编号	姓名	Dmgrssn
史密斯	1234	5	研究	3334
黄	3334	5	研究	3334
Zeloya	9998	4	管理员	9876
华莱士	9876	4	管理员	9876
贾巴尔	9879	4	管理员	9876

Insertion Anomalies

插入异常

If we need to insert a new employee with no department assigned yet, we need to enter null values. We cannot insert a new department with no employees working on it on SSN= null write not be valid, a primary key cannot be null.

如果需要插入尚未分配部门的新员工，则需要输入空值。 我们不能插入没有员工在SSN上工作的新部门=空写入无效，主键不能为空。

Deletion Anomalies

删除异常

If we delete last working employee of the department, the information regarding department will be lost.

如果我们删除部门的最后一名工作人员，则有关部门的信息将丢失。

Modification anomalies

修改异常

If we change the 'depart_mgr' of dept 5 we must update the tuples of all employees who work that department otherwise database will be inconsistent.

如果我们更改部门5的'depart_mgr'，则必须更新在该部门工作的所有员工的元组，否则数据库将不一致。

翻译自: https://www.includehelp.com/dbms/normalization-in-database-management-system.aspx

数据库数据规范化看不懂