oracle中dbms

To find the highest normal form of a relation, you have to first understand the basics of Functional dependency, Candidate keys, and Normal Forms.

要查找關系的最高范式 ，您必須首先了解功能依賴項 ，候選鍵和范式的基礎。

In relation, a Functional Dependency P holds Q (P->Q) if two tuples having similar values of attributes for P also have similar values of attributes for Q i.e.?P distinctively decides Q.

相對地，如果具有相似屬性值P的兩個元組也具有相似屬性值Q，即P獨特地決定Q ，則功能相關性P保持Q(P-> Q) 。

In a Database, Functional dependency performs assistance as a restriction between two sets of attributes.

在數據庫中，功能依賴項作為輔助功能來限制兩組屬性。

A Candidate Key in a relation is a minimal set of attributes of which can be used to identify a tuple distinctively. A candidate key is a column or set of columns in a table or relation that can distinctly identify any database collection of record without indicating towards any other data. Each table may contain one or more candidate keys, but one candidate key is distinct in each table or relation, and it is called the primary key. The primary key is the best among the candidate keys which is usually used for identification.

關系中的候選關鍵字是屬性的最小集合，可用于區別性地標識元組。 候選鍵是表或關系中的一列或一組列，它們可以清楚地標識記錄的任何數據庫集合，而無需指示其他任何數據。 每個表可以包含一個或多個候選鍵，但是一個候選鍵在每個表或關系中都不同，因此稱為主鍵。 主密鑰是通常用于標識的候選密鑰中最好的。

The process of Normalization is to minimize redundancy from a database table or relation or a set of relations. Insertion, Deletion and Updation anomalies may cause by redundancy. So, it aids to reduce the redundancy in relations.

規范化的過程是最小化數據庫表或關系或一組關系的冗余。 插入，刪除和更新異常可能是由冗余引起的。 因此，它有助于減少關系中的冗余。

Normal forms are used to eliminate or minimize redundancy in database tables or relations. These Normal Forms are:

范式用于消除或最小化數據庫表或關系中的冗余。 這些范式為：

First Normal Form

第一范式

Second Normal Form

第二范式

Third Normal Form

第三范式

Boyce-Codd Normal Form (BCNF)

博伊斯·科德范式(BCNF)

Steps to follow to find the highest normal form of a relation

查找關系的最高范式的步驟

The first step is to find all feasible candidate keys of the relation and its attributes.

第一步是找到該關系及其屬性的所有可行候選鍵。

The second step is to organize into two categories all the attributes of the relation:

第二步是將關系的所有屬性分為兩類：

Prime attributes

Non-prime attributes

Third and the last step is to examine to determine for 1st normal form and then 2nd and so on. If the process is unsuccessful in satisfying nth normal form condition, then the highest normal form will be n-1.

第三步也是最后一步是檢查確定第一個范式，然后確定第二個，依此類推。 如果該過程不能滿足第n個范式條件，則最高范式將為n-1。

例子 (Examples)

Problem 1) Find the highest normal form of a relation R(P, Q, R, S, T) with Functional dependency set as (QR->S, PR->QT, Q->T).

問題1)找到功能依賴項設置為(QR-> S，PR-> QT，Q-> T)的關系R(P，Q，R，S，T)的最高范式。

Solution:

解：

Step 1:

第1步：

As the relation (PR)+ = (P, Q, R, S, T) is given, but not a single of its subset can determine all attributes of relation, So PR will be candidate key. P or R can’t be derived from any other attribute of the relation, so there will be only one candidate key (PR).

由于給出了關系(PR)+ =(P，Q，R，S，T)，但沒有一個子集可以確定關系的所有屬性，因此PR將成為候選關鍵字。 P或R不能從關系的任何其他屬性派生，因此將只有一個候選鍵(PR)。

Step 2:

第2步：

The attributes which are part of candidate key (P, R) are Prime attributes.

屬于候選鍵(P，R)的屬性是素數屬性。

The others will be non-prime attributes (Q, R, S).

其他將是非素數屬性(Q，R，S)。

Step 3:

第三步：

A Relational Database Management System does not enable multi-valued or composite attribute. So, the relation R(P, Q, R, S, T) is in 1st normal form.

關系數據庫管理系統不啟用多值或復合屬性。 因此，關系R(P，Q，R，S，T)為第一范式。

Because QR->S is in 2nd normal form (QR is not a proper subset of candidate key PR) and PR->QT is in 2nd normal form (PR is candidate key) and Q->T is in 2nd normal form (Q is not a proper subset of candidate key PR).So, the relation is in 2nd normal form.

因為QR-> S為第二范式(QR不是候選關鍵字PR的適當子集)，而PR-> QT為第二范式(PR為候選關鍵字)，而Q-> T為第二范式(Q不是候選密鑰PR的適當子集)。因此，該關系為第二范式。

Because in QR->S (neither QR is a super key nor S is a prime attribute) and in Q->T (neither Q is a super key nor T is a prime attribute) but to satisfy 3rd normal form, either LHS of a Functional Dependency should be super key or RHS should be prime attribute. So, the relation is not in 3rd normal form.

因為在QR-> S中(QR既不是超鍵也不是S是素數屬性)，而在Q-> T中(Q既不是超鍵也不是T是素數屬性)但是要滿足第三范式，所以LHS功能依賴關系應該是超級鍵，RHS應該是主要屬性。 因此，該關系不是第三范式。

So, the highest normal form of relation will be 2nd Normal form.

因此，關系的最高范式將是第二范式。

Problem 2) Find the highest normal form of a relation R (P, Q, R, S, T) with Functional Dependency set (Q->P, P->R, QR->S, PR->QT).

問題2)找到具有功能相關性集(Q-> P，P-> R，QR-> S，PR-> QT)的關系R(P，Q，R，S，T)的最高范式。

Solution:

解：

Step 1:

第1步：

As the relation (PR) + = (P, Q, R, S, T) is given, Q will be a candidate key. Q can be derived from PR using PR->Q (Decomposing PR->QT to PR->Q and PR->T). So PR will be super key but (R) + ={R} and (P) + = {P, R, Q, S, T}. So P (subset of PR) will be a candidate key.

由于關系(PR)+ =(P，Q，R，S，T)，Q將成為候選關鍵字。 可以使用PR-> Q(將PR-> QT分解為PR-> Q和PR-> T)從PR導出Q。 因此PR將是超級鍵，但(R)+ = {R}和(P)+ = {P，R，Q，S，T}。 因此，P(PR的子集)將成為候選關鍵字。

So there will be two candidate keys {P, Q}.

因此將有兩個候選鍵{P，Q}。

Step 2:

第2步：

The attributes which are part of candidate key (P, Q) are Prime attributes.

屬于候選鍵(P，Q)的屬性是素數屬性。

The others will be non-prime attributes (R, S, T).

其他將是非素數屬性(R，S，T)。

Step 3:

第三步：

A Relational Database Management System does not enable multi-valued or composite attribute. So, the relation R (P, Q, R, S, T) is in 1st normal form.

關系數據庫管理系統不啟用多值或復合屬性。 因此，關系R(P，Q，R，S，T)處于第一范式。

The relation is in 2nd normal form because Q->P is in 2nd normal form (Q is a super key) and P->R is in 2nd normal form (P is super key) and QR->S is in 2nd normal form (QR is a super key) and PR->QT is in 2nd normal form (PR is a super key).

該關系為第二范式，因為Q-> P為第二范式(Q為超級鍵)，而P-> R為第二范式(P為超級鍵)，而QR-> S為第二范式。 (QR是超級鍵)，PR-> QT為第二范式(PR是超級鍵)。

Because LHS of all Functional Dependencies are super keys, the relation is in 3rd normal form.

因為所有功能依賴項的LHS都是超鍵，所以該關系為第三范式。

The relation is in BCNF as all LHS of all Functional Dependencies are super keys.

該關系在BCNF中，因為所有功能依賴項的所有LHS都是超級鍵。

So, the highest normal form is BCNF.

因此，最高范式是BCNF。

Problem 3) Find the highest normal form of a relation R (P, Q, R, S, T) with Functional Dependency set (P->S, Q->P, QR->S, PR->QT).

問題3)找到具有功能相關性集(P-> S，Q-> P，QR-> S，PR-> QT)的關系R(P，Q，R，S，T)的最高范式。

Solution:

解：

Step 1:

第1步：

As the relation (PR) + = (P, Q, R, S, T) is given, but not a single of its subset can determine all attributes of relation, so PR will be candidate key. P can be derived from Q, so we can replace P in PR by Q. So QR will also be a candidate key.

由于給出了關系(PR)+ =(P，Q，R，S，T)，但是沒有一個子集可以確定關系的所有屬性，因此PR將成為候選關鍵字。 P可以從Q派生，所以我們可以用Q代替PR中的P。因此QR也是候選關鍵字。

So the two candidate keys will be (PR, QR).

因此，兩個候選鍵將是(PR，QR)。

Step 2:

第2步：

The attributes which are part of candidate key (P, Q, R) are Prime attributes.

屬于候選鍵(P，Q，R)的屬性是素數屬性。

The others will be non-prime attributes (S, T).

其他將是非素數屬性(S，T)。

Step 3:

第三步：

A Relational Database Management System does not enable multi-valued or composite attribute. So, the relation R (P, Q, R, S, T) is in 1st normal form.

關系數據庫管理系統不啟用多值或復合屬性。 因此，關系R(P，Q，R，S，T)處于第一范式。

Because P->S is partial dependency (P which is a subset of candidate key PR is determining non-prime attribute S), the relation is not in 2nd Normal form because the 2nd normal form does not enable partial dependency.

因為P-> S是部分依賴關系(作為候選鍵PR的子集的P正在確定非主要屬性S)，所以該關系不是第二范式，因為第二范式不允許部分依賴。

So, the highest normal form will be 1st Normal Form.

因此，最高范式將是第一范式。

翻譯自: https://www.includehelp.com/dbms/how-to-find-the-highest-normal-form-of-a-relation.aspx

oracle中dbms

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的oracle中dbms_如何在DBMS中找到关系的最高范式？的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。