數據庫數據規范化看不懂
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)
First of all, find the entire possible candidate key in the given relation.
首先，找到給定關系中的所有可能的候選鍵。
Divide all given attribute into two categories one is prime and other one is non-prime attributes.
將所有給定屬性分為兩類，一類是質數，另一類是非質數屬性。
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
數據庫數據規范化看不懂
