Mysql學習總結(jié)（16）Mysql之數(shù)據(jù)庫設(shè)計規(guī)范

一、三大范式 1、第一范式：消除一個字段包含多個數(shù)據(jù)庫，消除一個記錄包含重復的組（單獨的一列包含多個項目），即可滿足1NF。 2、第二范式：消除部分依賴性即可轉(zhuǎn)化為2NF。部分依賴性表示一個記錄中包括的字段只依賴于主鍵的一部分。解決部分依賴性的最簡

一、三大范式

1、第一范式：消除一個字段包含多個數(shù)據(jù)庫值，消除一個記錄包含重復的組（單獨的一列包含多個項目），即可滿足1NF。

2、第二范式：消除部分依賴性即可轉(zhuǎn)化為2NF。部分依賴性表示一個記錄中包括的字段只依賴于主鍵的一部分。解決部分依賴性的最簡單方法是將復合主鍵分成兩部分，每一部分表示一個單獨的表。

3、第三范式：消除可傳遞依賴性即可滿足3NF。可傳遞依賴性表示記錄中至少一個值不依賴主鍵，而是依賴于這個記錄中的另一個字段。

4、數(shù)據(jù)庫規(guī)范化：

1NF：刪除重復的組，并確定一個主鍵或復合主鍵。

2NF：確定表處于1NF狀態(tài)，消除任何部分依賴性。

3NF：確定表處于2NF狀態(tài)，消除任何可傳遞依賴性。

5、連接數(shù)據(jù)庫中的表：大多數(shù)情況下，兩個表之間的連接是通過一個公共字段建立的。公共字段是兩個表中都存在的一個字段。許多情況下，公共字段是其中一個表的主鍵。外鍵一般出現(xiàn)在“多”端。

6、關(guān)系數(shù)據(jù)庫中不能存在多對多關(guān)系。用來消除多對多關(guān)系的最常用方法是通過添加橋接表來創(chuàng)建兩個一對多關(guān)系。

二、數(shù)據(jù)庫設(shè)計相關(guān) 1.數(shù)據(jù)規(guī)范化 關(guān)系模式滿足的約束條件稱為范式。范式由低到高分為：1NF、2NF、3NF、BCNF、4NF、5NF。 規(guī)范化：就是指把一個低一級的關(guān)系模式分解為高一級關(guān)系模式的過程。 規(guī)范化的基本思想：逐步消除不合適的函數(shù)依賴，使數(shù)據(jù)庫中的各個關(guān)系模式達到某種程度的分離。 函數(shù)依賴：通俗的說，就像自變量x確定之后，相應的函數(shù)值f(x)也就唯一的確定了一樣。 碼：給定一個碼能完全決定一個元組。一個關(guān)系可能有多個碼，選其中一個做為主碼。包含在任一碼中的屬性稱為主屬性。不包含在任何碼中的屬性稱為非主屬性。 第一范式（1NF）：如果關(guān)系中所有屬性的值域都是簡單域，其元素（屬性）不可再分，是屬性項不是屬性組，那么關(guān)系模式屬于第一范式。這一限制是關(guān)系的基本性質(zhì)，所以任何關(guān)系都必須滿足第一范式。 第二范式（2NF）：如果一個范式屬于1NF，且所有的非主屬性都完全的依賴主屬性，稱為第二范式。可以用分解的方法消除部分依賴的情況，而使關(guān)系達到2NF的標準。方法是從現(xiàn)有關(guān)系中分解出新的關(guān)系表，使每個表中所有的非關(guān)鍵字都完全依賴于各自的主關(guān)鍵字。 (消除部分依賴) 第三范式（3NF）：如果一個關(guān)系屬于2NF，且每個非主屬性不傳遞依賴于主屬性，這種關(guān)系是3NF。從2NF中消除傳遞依賴，就是3NF。 (消除部分傳遞依賴) BC范式（BCNF）： 無論2NF還是3NF都沒有涉及主屬性間的函數(shù)依賴，所以有時仍會引起一些問題。 定義：如果關(guān)系模式屬于1NF，且每一個函數(shù)依賴關(guān)系中的決定因素都包含碼，則關(guān)系滿足BC范式。主屬性對不含他的碼完全函數(shù)依賴，沒有屬性完全函數(shù)依賴于一組非主屬性。 多值依賴和4NF：第四范式是BC范式的推廣。 定義：關(guān)系模式R屬于1NF，若對任意多值依賴X??Y。X必包含R的主鍵，則稱R是第四范式。 多值依賴：對列A中的一個值，不論列C取什么值，總有一組確定的列B的值。所以有A??B。如果A包含關(guān)系R的主鍵，則關(guān)系R滿足4NF。可以采用分解法消除不滿足4NF的多值依賴。 規(guī)范化設(shè)計帶來的性能問題在實際應用中可能令人無法想象。如果出現(xiàn)這種情況，就要進行非規(guī)范化處理。由于非規(guī)范化必然導致冗余，占用更多的存儲空間，因此需要對性能和空間的考慮進行平衡。常用方法有冗余屬性，合并表等等。

?

2.數(shù)據(jù)庫設(shè)計

常用方法：

(1)基于3NF的數(shù)據(jù)庫設(shè)計方法：

在需求分析的基礎(chǔ)上，識別并確認數(shù)據(jù)庫模式中的全部屬性和屬性間的依賴，將他們組織成一個單一的關(guān)系模式，然后再分析模式中不符合3NF的約束條件，用投影和連接的辦法將其分解，使其達到3NF。

(2)LRA方法：邏輯記錄存取法。

(3)基于實體聯(lián)系（E-R）的數(shù)據(jù)庫設(shè)計方法。

(4)基于視圖概念的數(shù)據(jù)庫設(shè)計方法。

(5)面向?qū)ο蟮年P(guān)系數(shù)據(jù)庫設(shè)計方法。

通常將數(shù)據(jù)庫設(shè)計分為需求分析、概念結(jié)構(gòu)設(shè)計、邏輯結(jié)構(gòu)設(shè)計和數(shù)據(jù)庫物理設(shè)計4個階段。

?

概念結(jié)構(gòu)設(shè)計常用的方法是實體分析法、屬性綜合法。

二元聯(lián)系的類型與定義：二元聯(lián)系指兩個實體之間的聯(lián)系。分為一對一、一對多、多對多3種。

（1）一對一聯(lián)系：對于實體集A中的每一個實體，實體集B中至多有一個實體與之聯(lián)系。

（2）一對多聯(lián)系：對于實體集A中的每一個實體，實體集B有n個實體（n>=0）與之聯(lián)系，反之對于實體集B中的每一個實體，實體集A至多只有一個實體與之聯(lián)系。則實體集A與實體集B有一對多關(guān)系，記為1:n。

（3）多對多聯(lián)系：若對于實體集A中的每一個實體，實體集B有n個實體（n>=0）與之聯(lián)系。反過來，對于實體集B中的每一個實體，實體集A有m個實體（m>=0）與之聯(lián)系。則實體集A與實體集B具有多對多聯(lián)系，記為m:n。

消除冗余聯(lián)系：若出現(xiàn)兩個或兩個以上的聯(lián)系表示的是同一概念，則存在著冗余的聯(lián)系，具有冗余聯(lián)系的E-R模型轉(zhuǎn)換為關(guān)系模型可能會得到非規(guī)范化的關(guān)系，因此必須予以消除。

?

警惕連接陷阱：

連接陷阱是一種存在語義缺陷的聯(lián)系結(jié)構(gòu)，分為扇形陷阱、斷層陷阱、深層扇形陷阱3種信息。

扇形陷阱：指由一個實體引出的兩種不同類型的扇形聯(lián)系，形成雙扇形結(jié)構(gòu)。

3.數(shù)據(jù)庫物理設(shè)計：

利用已確定的邏輯結(jié)構(gòu)及DBMS提供的方法、技術(shù)。已較優(yōu)的存儲結(jié)構(gòu)、數(shù)據(jù)存儲路徑、合理的數(shù)據(jù)存儲位置及存儲分配，設(shè)計一個高效可實現(xiàn)的物理數(shù)據(jù)庫結(jié)構(gòu)。

?

三、模式

數(shù)據(jù)庫三級模式結(jié)構(gòu)：這是數(shù)據(jù)庫管理系統(tǒng)內(nèi)部的系統(tǒng)結(jié)構(gòu)。

1、概念模式：

只涉及行的描述，不涉及具體的值。概念模式的一個具體值稱為模式的一個實例，同一模式可以有很多實例。概念模式反映的是數(shù)據(jù)庫的結(jié)構(gòu)及其聯(lián)系，所以是相對穩(wěn)定的。而實例反映的是數(shù)據(jù)庫某一時刻的狀態(tài)，所以是相對變動的。

概念模式不僅要描述記錄類型，還要描述記錄間的聯(lián)系、操作、數(shù)據(jù)的完整性、安全性。但概念模式不涉及存儲結(jié)構(gòu)、訪問技術(shù)等細節(jié)。

（注：可理解為系統(tǒng)表部分）

2、外模式：

也稱用戶模式或子模式。是用戶與數(shù)據(jù)庫系統(tǒng)的接口，是用戶用到的那部分記錄的描述。由若干外部記錄組成，用戶使用DML（數(shù)據(jù)操作語言）操作外模式的外部記錄。

（注：可理解為用戶表部分）

3、內(nèi)模式：

也稱存儲模式，是數(shù)據(jù)庫物理結(jié)構(gòu)和存儲方式的描述，是數(shù)據(jù)在數(shù)據(jù)庫內(nèi)部的表示方式。定義所有內(nèi)部記錄的類型、索引、文件的組織方式。記錄的存儲方式是順序存儲、B樹存儲、Hash方法存儲等。

?

兩級映像：模式/內(nèi)模式映像、外模式/模式映像。

?

實體與記錄：實體表示客觀存在，能區(qū)別的事物。記錄是字段的有序集合，一般一條記錄描述一個實體。

屬性與字段：屬性描述實體某方面的特性，字段標記實體屬性的命名單位。

碼與記錄碼：碼是唯一能區(qū)分實體的屬性或?qū)傩约涗洿a是唯一標識文件中的每條記錄的字段或字段集。

實體集與文件：實體集是具有共同特性的實體的集合。文件是同一類記錄的匯集。

實體型與記錄型：實體型是屬性的集合，記錄型是記錄的結(jié)構(gòu)定義。

?

數(shù)據(jù)模型三要素：

數(shù)據(jù)庫結(jié)構(gòu)的基礎(chǔ)是數(shù)據(jù)模型，是用來描述數(shù)據(jù)的一組概念和定義。

數(shù)據(jù)模型三要素是數(shù)據(jù)結(jié)構(gòu)、數(shù)據(jù)操作、數(shù)據(jù)的約束條件。

?

E-R模型：是實體-聯(lián)系模型的簡稱。所采用的3個主要概念是實體、聯(lián)系、屬性。

實體：現(xiàn)實世界中可以區(qū)別其它對象的物體或事件。

聯(lián)系：實體的聯(lián)系分為實體內(nèi)部的聯(lián)系和實體與實體之間的聯(lián)系。

?

兩個不同實體之間的聯(lián)系：

（1）一對一：指實體集E1中的一個實體最多只與實體集E2中的一個實體相聯(lián)系。（1:1）

（2）一對多：表示實體集E1中的一個實體可與實體集E2中的多個實體相聯(lián)系。（1:N）

（3）多對多：表示實體集中E1中的多個實體可與實體集E2中的多個實體相聯(lián)系。（M:N）

?

兩個以上不同實體集的聯(lián)系：

兩個以上不同實體集之間存在1：1：1、1：1：N、1：M：N和R：M：N

?

同一實體集內(nèi)的二元聯(lián)系：

同一實體集內(nèi)的各實體之間也存在1：1、1：N和M：N的聯(lián)系。

?

屬性是實體某方面的特性。

?

派生屬性可以從其它屬性得來，例如：參加工作時間和工作年限，工作年限可以從當前時間和參加工作時間得到，這里工作年限就是一個派生屬性。

?

概念模型中最常用的方法是實體-聯(lián)系法，簡稱E-R方法。

?

擴充的E-R模型：

弱實體：這種實體對另一些實體有著很強的依賴關(guān)系，即一個實體的存在必須以另一個實體為前提。例如職工與家屬的關(guān)系。

特殊化：一個實體集可以按照某種特征區(qū)分為幾個子實體。例如：學生實體集可以分為研究生、本科生、大專生。我們稱這種過程為特殊化，反之叫普遍化。

?

層次模型：采用樹形結(jié)構(gòu)表示數(shù)據(jù)與數(shù)據(jù)之間的聯(lián)系。

網(wǎng)狀模型：采用網(wǎng)狀結(jié)構(gòu)表示數(shù)據(jù)與數(shù)據(jù)之間的聯(lián)系。

?

關(guān)系模型：在關(guān)系模型中以表格結(jié)構(gòu)表達實體集，以及實體集之間的聯(lián)系。

?

關(guān)系代數(shù)：

笛卡爾積：D1={0，1}、D2={a，b}。D1*D2={0,a}{0,b}{1,a}{1,b}。

?

關(guān)系的3種類型：

基本關(guān)系：實際存在的表，是實際存儲數(shù)據(jù)的邏輯表示。

查詢表：查詢結(jié)果對應的表。

視圖表：由基本表或其它視圖表導出的表，由于它本身不獨立存儲在數(shù)據(jù)庫中。數(shù)據(jù)庫只存放它的定義，所以常稱為虛表。

?

完整性約束：

完整性規(guī)則提供了一種手段來保證授權(quán)用戶對數(shù)據(jù)庫操作修改時不會破壞數(shù)據(jù)的一致性。

?

關(guān)系的完整性分為3類：

（1）實體完整性：規(guī)定基本關(guān)系R的主屬性A不能取空值。

（2）參照完整性：在關(guān)系模型中實體與實體間的聯(lián)系是用關(guān)系來描述的。這樣自然就存在著關(guān)系與關(guān)系間的引用。

（3）用戶定義完整性：反映某一具體應用所涉及的數(shù)據(jù)必須滿足的語義要求，由應用環(huán)境決定。

?

5種基本的關(guān)系代數(shù)運算：并、差、廣義笛卡爾積、投影、選擇。

擴展關(guān)系運算：交、連接、除、廣義投影、外連接。

列舉關(guān)系運算的例子。

?

SQL支持三級模式結(jié)構(gòu)：視圖對應外模式，基本表對應模式，存儲文件對應內(nèi)模式。

?

索引：

數(shù)據(jù)庫中索引與書籍中索引類似，利用索引可以快速查找整本書信息，無需閱讀整本書。

數(shù)據(jù)庫索引可以使數(shù)據(jù)庫程序無需對整個表進行掃描，就可以在其中找到所需數(shù)據(jù)。

?

索引分為：

聚集索引和非聚集索引。

聚集索引是指索引表中索引項的順序與表中記錄的物理順序一致的索引。

?

視圖創(chuàng)建遵循如下規(guī)定：

（1）子查詢不允許有order by和distinct語句。

（2）with check option表示對update、insert、delete操作時保證更新、插入或刪除的行滿足視圖定義的謂詞條件（即滿足子查詢中的where后的條件表達式）。

（3）組成視圖的屬性列名或者全部省略或者全部指定。如果省略屬性列名，則隱含視圖由SELECT子查詢目標列的主屬性組成。

?

SQL的訪問控制：數(shù)據(jù)庫控制是控制用戶的存儲權(quán)限,由DBA來決定。

通過GRANT和REVORK將授權(quán)通知系統(tǒng)，并存入數(shù)據(jù)字典。

?

四、規(guī)范化

規(guī)范化：將關(guān)系模式從低一級范式轉(zhuǎn)化成高一級范式的過程。

5NF包含于4NF包含于BCNF包含于3NF包含于2NF包含于1NF。

?

1NF定義：關(guān)系模式R中的每個分量是不可再分的數(shù)據(jù)項，則關(guān)系模式R屬于第一范式。1NF冗余度大、引起修改的不一致性、插入及刪除異常。

2NF定義：若關(guān)系模式屬于1NF，且每個非主屬性完全依賴于碼，則關(guān)系模式屬于2NF。即1NF消除了非主屬性對碼的部分函數(shù)依賴。

3NF定義：2NF消除了非主屬性對碼的傳遞函數(shù)依賴，則稱3NF。3NF的模式必是2NF的模式。產(chǎn)生冗余和異常的兩個重要原因是部分函數(shù)依賴和傳遞依賴。

BCNF（巴科斯范式）：即3NF消除了主屬性對碼的部分和傳遞依賴，稱為BCNF。

4NF：4NF是限制關(guān)系模式的屬性間不允許有非平凡且非函數(shù)依賴的多值依賴。

如果只考慮函數(shù)依賴，BCNF是關(guān)系模式最高的規(guī)范化程度。如果考慮多值依賴，4NF是關(guān)系模式最高的規(guī)范化程度。

?

五、事務管理

事務有4個特性ACID。

原子性（A）：要么全做，要么全不做。

一致性（C）：一個事務獨立執(zhí)行的結(jié)果，將保持數(shù)據(jù)的一致性，即數(shù)據(jù)不會因為數(shù)據(jù)的執(zhí)行而遭受破壞。

隔離性（I）：一個事務的執(zhí)行不能被其它事物干擾。

持久性（D）：一個事物一旦提交，對數(shù)據(jù)庫的改變必須是永久的。

SQL中事物定義語句有3條：

BEGIN TRANSACTION：事務開始。

COMMIT：事務提交。

ROLLBACK：事務回滾。

?

六、并發(fā)控制 并發(fā)控制主要技術(shù)是封鎖，主要包含：排他鎖（簡稱X鎖或?qū)戞i）、共享鎖（簡稱S鎖或讀鎖）。 排他鎖：若事務T對數(shù)據(jù)對象A加上X鎖，則只允許T讀取和修改A。其它事務不能對A加任何鎖，直到T釋放鎖。 共享鎖：若事務T對數(shù)據(jù)對象A加上S鎖，則只允許T讀取A，但不能修改A。其它事務只能再對A加S鎖。保證其它事務可以讀取A，但在T釋放A上的S鎖前不能修改A。 ? 三級封鎖協(xié)議： 一級封鎖協(xié)議：事務在修改數(shù)據(jù)前必須先對其加X鎖，直到事務結(jié)束才釋放（結(jié)束包括commit或rollback）。一級封鎖協(xié)議解決丟失更新的問題。 二級封鎖協(xié)議：在一級協(xié)議的基礎(chǔ)上，加上事務在讀數(shù)據(jù)之前必須加S鎖，讀完后即可釋放S鎖。二級封鎖協(xié)議解決讀臟數(shù)據(jù)的問題。因為讀完后即釋放，所以不能保證可重復讀。 三級封鎖協(xié)議：在一級協(xié)議的基礎(chǔ)上，加上事務在讀數(shù)據(jù)之前必須加S鎖，直到事務結(jié)束時釋放S鎖。除了防止丟失更新、讀臟數(shù)據(jù)的問題，還進一步防止不可重復讀。 ? 活鎖和死鎖： 活鎖：當事務T1封鎖數(shù)據(jù)R，事務T2請求數(shù)據(jù)R于是T2等待。T1釋放了R上的封鎖，系統(tǒng)首先批準了T3的請求，T2繼續(xù)等待，之后系統(tǒng)批準了T4的請求……依此類推，T2可能永久等待。這種現(xiàn)象稱為活鎖。 死鎖：是指兩個以上事務分別請求封鎖對方已經(jīng)封鎖的數(shù)據(jù)，導致長期等待而無法繼續(xù)進行下去的現(xiàn)象叫死鎖。 ? 并發(fā)調(diào)用可串行性： 多個事務并發(fā)執(zhí)行，當且僅當其結(jié)果與某一次序串行地執(zhí)行它們時的結(jié)果相同，我們稱這種調(diào)度是可串行化調(diào)度。 給定一個并發(fā)調(diào)度，當且僅當它是可串行化的才認為是正確調(diào)度。 ? 兩段封鎖協(xié)議：指所有事務必須分為兩個階段對數(shù)據(jù)項加鎖和解鎖。 第一階段是獲得封鎖：事務可以獲得任何數(shù)據(jù)項上的任何類型的鎖，但不能釋放。 第二階段是釋放封鎖：事務可以釋放任何數(shù)據(jù)項上的任何類型的鎖，但不能申請。 ? 事務是不能嵌套的，因為這違背了事務的原子性。事務是不能嵌套是指當且僅當當前沒有事務在運行時，程序才能執(zhí)行BEGIN TRANSACTION操作。 ? 通過Resource授權(quán)來控制創(chuàng)建新關(guān)系的能力，具有Resource授權(quán)的用戶在創(chuàng)建新關(guān)系后自動獲得該關(guān)系上的所有權(quán)限。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com