model简单化连接数据库_数据库标准化，简便方法

model简单化连接数据库

20% of the world’s data is stored in a Structured way in the form of Relational Database.

世界上20％的数据以关系数据库的形式以结构化方式存储。

Being a Computers student you might have come across one of the most important topics in Database Management Systems that is Database Normalization. Normalization being a hot topic of discussion in Campus Placements Interviews, for PSUs & gate exams, for research as well. In this blog, I will discuss the various Normalization techniques & will show you how can you find the Highest Normal forms for the tables very easily.

作为计算机专业的学生，​​您可能会遇到数据库管理系统中最重要的主题之一，即数据库规范化。 规范化是校园实习面试中讨论的热门话题，适用于PSU和入学考试，也用于研究。 在此博客中，我将讨论各种规范化技术，并向您展示如何轻松地为表格找到“最高法线”形式。

Before jumping to the main topic let me introduce you to the evolution of DBMS first. Earlier, File System management was used where data was stored in terms of files. File System management does not have protocols for data redundancy, data inconsistency, concurrency of users, security & searching data from the files. So DBMS software came into the light where structured data is stored in terms of tables & it provides a systematic way for creating and managing databases. Now it has become very easy for users to store, process & analyze their data with the help of DBMS. In the database, data is stored in the form of tables which are also known as Relations, so we call it RDBMS, where R stands for Relational.

在转到主要主题之前，让我首先向您介绍DBMS的发展。 以前，使用文件系统管理是根据文件存储数据的。 文件系统管理没有用于数据冗余，数据不一致，用户并发，安全性和从文件中搜索数据的协议。 因此，DBMS软件的出现是用表来存储结构化数据，它提供了一种创建和管理数据库的系统方法。 现在，借助DBMS，用户可以非常轻松地存储，处理和分析其数据。 在数据库中，数据以表的形式存储，也称为“关系”，因此我们将其称为RDBMS，其中R代表“关系”。

What Exactly is Database Normalisation?

什么是数据库规范化？

Database Normalisation is a systematic technique of organizing the data in the database such that the above-mentioned protocols are met, most importantly eliminating the Data Redundancy. In simple words, the data in the tables should be stored logically such that later our operations like insertion, deletion, and the update should not cause any kind of ill effects also known as anomalies.

数据库规范化是一种组织数据库中数据的系统技术，可以满足上述协议，最重要的是消除数据冗余。 简而言之，表中的数据应进行逻辑存储，以便以后我们的操作(如插入，删除和更新)不会引起任何类型的不良影响，也称为异常。

In Normalisation, we decompose the tables into multiple tables to remove the data redundancy & unwanted anomalies.

在规范化中，我们将表分解为多个表，以消除数据冗余和不必要的异常。

So How Can We Normalise our Database?

那么我们如何规范我们的数据库？

Using normalization techniques:

使用规范化技术：

1. First Normal Form
   第一范式
2. Second Normal Form
   第二范式
3. Third Normal Form
   第三范式
4. BCNF (Boyce and Codd Normal Form )
   BCNF(Boyce和Codd范式)

1.第一范式(1NF)： (1. First Normal Form (1NF):)

Each attribute of a table must have atomic (single) values only then we can say that the table is in 1NF.

一个表的每个属性都必须具有原子(单个)值，然后我们可以说该表位于1NF中。

In the following example, Kiara has two phone numbers stored in a single cell that violates the 1NF rule.

在以下示例中，Kiara在违反1NF规则的单个单元格中存储了两个电话号码。

So we need to make sure that the cell in the table should not hold multiple values, only then we can say that table is in 1NF.

因此，我们需要确保表中的单元格不应保存多个值，只有这样我们才能说该表位于1NF中。

2.第二范式 (2. Second Normal Form)

For a table to be in 2NF :

对于2NF中的表格：

• It should be in 1NF.
  应该是1NF。
• Non-prime attributes of the table should not depend on the proper subset of any of the candidate key or we can say all the non-prime attributes should be fully functionally dependent on the Candidate key & not on parts of Candidate Key.
  该表的非主要属性不应依赖于任何候选键的适当子集，或者我们可以说所有非主要属性应在功能上完全取决于候选键而不是候选键的某些部分。

Before that you should know the following terms:

在此之前，您应该了解以下术语：

1. Functional Dependency(FD): Method that describes the relationship between the attributes in a relation. X →Y, where X determines Y & Y is dependent on X. Example: Student Id → Student Name, here student name is determined by the Student ID.

   功能依赖(FD)：描述关系中属性之间关系的方法。 X→Y，其中X确定Y＆Y取决于X。 示例 ：学生ID→学生姓名，此处学生姓名由学生ID决定。

So when we say Student Name is dependent on the Student ID then which one of the following is not valid for this functional dependency?

因此，当我们说“学生姓名”取决于“学生ID”时，以下哪一项对该功能依赖性无效？

So highlighted one below is the answer to the above question, as Student Name is derived from the Student ID, we can’t have different Student names with the same Student ID.

因此，下面突出显示的是上述问题的答案，因为“学生姓名”是从“学生编号”派生的，因此我们不能使用相同的“学生编号”来使用不同的“学生姓名”。

2. Primary Key: I guess most of us would be aware of Primary Key, a minimal set of attributes that uniquely identify a tuple(row) in a relation.

2.主键：我想我们大多数人都会意识到主键，这是最小的一组属性，可唯一标识关系中的元组(行)。

3. Candidate keys: Keys that are not taking part in Primary Key formation but can serve as Primary Key. So it can also identify a tuple in the relation. You can use the Closure Method to identify the Candidate Keys when Functional dependencies are given.

3.候选密钥：不参与主密钥形成但可以用作主密钥的密钥。 因此，它也可以标识关系中的元组。 给定功能依赖项后，可以使用闭包方法来识别候选关键字。

Let’s find the Candidate key for R( ABCD ) given FD{ A → B, B →C, C →A, C→D} using Closure Method:

让我们使用闭包方法找到给定FD {A→B，B→C，C→A，C→D}的R(ABCD)的候选键：

Step 1: Take Closure of A represented as A+, we need to check what can be derived from A. Now see FD: A →B i.e From A we can determine B. So,

步骤1：以表示为A +的A闭合，我们需要检查可以从A导出什么。现在参见FD：A→B，即从A可以确定B。

A+ = AB ( as A can be determined from A, Reflexive Property)

A + = AB(因为A可以根据A， 自反性确定)

Step 2: If A → B & B→C, then by the Transitive property, A→C ( A can determine C) & similarly A → D. So,

步骤2：如果A→B＆B→C，则根据传递属性， A→C(A可以确定C)以及类似地A→D。

A+ = ABCD , So A can determine all the attributes in R( ABCD). Hence A is Candidate Key for sure. But we should find all the candidates key in a table when we are dealing with Normalization. Similarly when we take the closure of other attributes as:

A + = ABCD，因此A可以确定R(ABCD)中的所有属性。 因此， A无疑是候选密钥。 但是，在处理规范化时，我们应该在表中找到所有候选键。 类似地，当我们将其他属性的关闭视为：

B+ = CAD

B + = CAD

C+ = CA

C + = CA

D+ = D

D + = D

Please Note: here (AB)+ can determine all the attributes of the relation but still it’s not the Candidate key , because subset of (AB)+ that is B alone can determine all the attribute so (AB)+ is not the minimal set of attibutes, so it’s not a candidate key.

请注意：这里(AB)+可以确定关系的所有属性，但仍然不是候选键 ，因为(AB)+的子集(即B)可以确定所有属性，因此(AB)+不是最小集服装，所以它不是候选密钥。

4. Prime & Non-Prime Attributes :

4.主要和非主要属性：

Given R(ABCD) & Candidate key as BC.

给定R(ABCD)和候选键为BC。

Prime Attributes: Involved in the formation of Candidate Key (B, C)

主要属性：涉及候选密钥(B，C)的形成

Non-Prime Attributes: These are not involved in the formation of Candidate Key. ( A, D)

非主要属性：候选密钥的形成不涉及这些属性 。 ( 广告)

Problems that we might expect :

我们可能期望的问题：

Check whether the following relation is in 2NF or not:

检查以下关系是否在2NF中：

R(A, B, C, D, E, F) given FD as {C → F, E →A, EC → D, A →B }.

R(A，B，C，D，E，F)给定FD为{C→F，E→A，EC→D，A→B}。

Solution:

解：

Step1: Find all the possible Candidates Keys.

步骤1：找到所有可能的候选关键字。

See RHS of the FD, I can see only FADB {C → F, E →A, EC → D, A →B }. can be determined from the attributes on the LHS but comparing it with R(ABCDEF) I can see CE is not getting derived from any attribute present in LHS of FD. So C & E will surely be a part of Candidate keys because they are not getting determined from any other attributes.

请参阅FD的RHS，我只能看到FADB {C→ F ，E→ A ，EC→ D ，A→ B }。 可以从LHS的属性中确定，但是将其与R(ABCDEF)进行比较，我可以看到CE并不是从FD的LHS中存在的任何属性派生的。 因此C＆E肯定会成为候选关键字的一部分，因为它们不是从其他任何属性确定的。

So let’s take the closure of CE as

因此，让我们以关闭CE作为

(CE)+ = CE

(CE)+ = CE

(CE)+ = CEF (C → F )

(CE)+ = CE F (C→ F)

(CE)+ = ACEF (E →A)

(CE)+ = A CEF(E→ A)

(CE)+ = ACDEF (EC → D)

(CE)+ = AC D EF(EC→ D )

(CE)+ = ABCDEF (A →B)

(CE)+ = A B CDEF(A→ B )

So CE is determining all the attributes of the Relation. It is a Candidate key & no other Candidate key exists, so we can say,

因此， CE正在确定关系的所有属性。 这是一个候选密钥，不存在其他候选密钥，所以我们可以说，

Prime Attributes = C & E

主要属性= C＆E

Non-Prime Attributes = A B D F

非主要属性= ABDF

For a table to be in 2NF: Non-Prime attribute should not be dependent on a proper subset of Candidate Key.

对于要以2NF表示的表：Non-Prime属性不应依赖于Candidate Key的适当子集。

Subset of Candidate key are {{C},{E},{CE}}

候选键的子集为{{C}，{E}，{CE}}

Proper Subset of Candidate Key : {{C},{E}}

候选关键字的正确子集：{{C}，{E}}

From given FD (C → F, E →A ). Clearly we can see that non-prime attributes(F & A ) are determined from the part of the Candidate key ( C & E). So the given relation is not in 2NF.

从给定的FD(C→ F ，E→ A )。 显然，我们可以看到非素数属性(F＆A)是由候选键(C＆E)的部分确定的。 因此，给定的关系不在2NF中。

3.第三范式： (3. Third Normal Form :)

For a table to be in 3NF :

对于3NF中的表格：

• It should be in 2NF.
  应该是2NF。
• There should be no transitive dependency in the table or we can say, no non-prime attribute should be derived from a non-prime attribute in a relation. They should be determined from the Candidate or Super key.
  表中应该没有传递依赖性，或者可以说，关系中的任何非素数属性都不应派生非素数属性。 它们应从“候选”或“超级”键确定。

R( PQRS ), FD {PQ →R, R →S}

R(PQRS)，FD {PQ→R，R→S}

Candidate key: PQ

候选键：PQ

Prime attributes: P,Q

主要属性：P，Q

Non-prime atrributes: R,S

非素数的R折：R，S

Since R is a non-prime attribute R can be determined from another non-prime attribute. So this table is not in 3NF.

由于R是非素数属性，因此可以从另一个非素数属性确定R。 因此，该表不在3NF中。

4. BCNF(Boyce和Codd范式)： (4. BCNF (Boyce and Codd Normal Form ):)

Also known as a special form of 3NF as BCNF puts a restriction on 3NF. In BCNF each functional dependency should be derived from Candidate Key or Super Key.

BCNF也被称为3NF的一种特殊形式，它对3NF施加了限制。 在BCNF中，每个功能依赖性都应从候选键或超级键派生。

Data Redundancy decreases as we move from 1 NF to BCNF.

随着我们从1 NF迁移到BCNF，数据冗余减少。

Let’s Practice Few Questions:

让我们练习一些问题：

Find the highest normal form for R(MNOPQR) and

找到R(MNOPQR)的最高范式，然后

FD {MN→O, O→PQ, Q→R, R→M}

FD {MN→O，O→PQ，Q→R，R→M}

Solution:

解：

Find Candidate Key with Closure method:

使用闭包方法查找候选关键字：

OPQRM attributes can be derived from other attributes but N can’t be derived so N will be part of a Candidate key. Let’s check it’s Closure:

OPQRM属性可以从其他属性派生，但是N不能派生，因此N将成为候选关键字的一部分。 让我们检查一下它的关闭：

N+ = N, but it can’t determine anything else. So let’s check closure for MN,

N + = N，但无法确定其他任何内容。 因此，让我们检查MN的关闭情况，

(MN)+ = MNOPQR so MN is our Candidate Key. So let us continue,

(MN)+ = MNOPQR，因此MN是我们的候选密钥。 因此，让我们继续，

See which attributes are determining attribute M ( check if M comes on RHS in FD, if so replace it the source attribute in the above Candidate key )

查看哪些属性正在确定属性M(检查M是否在FD中的RHS上出现，如果是，请替换上面的候选关键字中的source属性)

(RN)+ = ? ( as R → M, so M can be replaced by R )

(RN)+ =？ (因为R→M，所以M可以用R代替)

so (RN)+ = MNOPQR, RN closure can determine all the attributes in relation so RN is a candidate key.

因此(RN)+ = MNOPQR，RN闭包可以确定相关的所有属性，因此RN是候选关键字。

See which attributes are determining attribute R( check if R comes on RHS in FD if so replace it the source attribute in the above Candidate key ) & check it’s closure.

查看哪些属性正在确定属性R(检查R是否在FD中出现在RHS上，如果是，请替换上面的Candidate键中的source属性)并检查其是否关闭。

(QN)+ = ?( as Q→ R, so R can be replaced by Q).

(QN)+ =？(如Q→R，因此R可以用Q代替)。

(QN)+ = QNRMOP, QN closure can determine all the attributes in relation so QN is a candidate key.

(QN)+ = QNRMOP，QN闭包可以确定相关的所有属性，因此QN是候选关键字。

Similarly, we can find ON is also a Candidate key as from FD(O→PQ), (ON)+ can be found in a similar fashion.

同样，从FD(O→PQ)可以发现ON也是候选键，可以类似的方式找到(ON)+。

So the Candidate Keys are MN, ON, RN, QN.

因此，候选键为MN，ON，RN，QN。

Prime attributes: M, N, O, R, Q

主要属性：M，N，O，R，Q

Non-Prime attributes: P

非主要属性：P

So we have all the candidate keys now, so we will move ahead & our very first step will be to check for BCNF then 3NF after that 2NF. We generally don’t check for 1 NF & assume that the table is in 1 NF.

因此，我们现在拥有所有候选密钥，因此我们将继续进行，我们的第一步将是先检查BCNF，然后再检查2NF之后的3NF。 我们通常不检查1 NF并假定表格位于1 NF中。

So the table is in 1 NF.

因此该表的单位为1 NF。

This completes the Normalization Part of Database!!

这样就完成了数据库的标准化部分！

I hope you liked this article. Feel Free to reach out to me at medha.rwt@gmail.com. Suggestions for any betterment of this article is highly welcome. Do let me know your reviews about this blog. And tell me the next topic you want me to write for you guys.

希望您喜欢这篇文章。 随时通过medha.rwt@gmail.com与我联系。 欢迎对本文进行任何改进的建议。 让我知道您对这个博客的评论。 告诉我您要我为你们写的下一个主题。

Till then Stay Safe & Keep Reading.

然后保持安全并继续阅读。

翻译自: https://medium.com/swlh/normalization-of-database-the-easy-way-98f96a7a6863

model简单化连接数据库