數(shù)據(jù)庫的設(shè)計(jì)第3章教學(xué)目標(biāo)了解關(guān)系數(shù)據(jù)庫設(shè)計(jì)的過程01掌握關(guān)系數(shù)據(jù)庫設(shè)計(jì)的方法02掌握范式和函數(shù)依賴的概念03掌握關(guān)系模式規(guī)范化設(shè)計(jì)方法04§3.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述通常把使用數(shù)據(jù)庫的各類信息系統(tǒng)都稱為數(shù)據(jù)庫應(yīng)用系統(tǒng)，如：網(wǎng)絡(luò)購物系統(tǒng)、工資管理系統(tǒng)、票務(wù)系統(tǒng)、銀行系統(tǒng)、人臉識別系統(tǒng)等。數(shù)據(jù)庫設(shè)計(jì)從廣義上來說，是數(shù)據(jù)庫及其應(yīng)用系統(tǒng)的設(shè)計(jì)，即設(shè)計(jì)整個數(shù)據(jù)庫應(yīng)用系統(tǒng)。從狹義上來說，是設(shè)計(jì)數(shù)據(jù)庫本身，即設(shè)計(jì)數(shù)據(jù)庫的各級模式并建立數(shù)據(jù)庫，是數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計(jì)的一部分。本書講解側(cè)重廣義的數(shù)據(jù)庫設(shè)計(jì)。以網(wǎng)路購物系統(tǒng)為例講解數(shù)據(jù)庫及應(yīng)用系統(tǒng)的設(shè)計(jì)?！?.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述良好的數(shù)據(jù)庫設(shè)計(jì)表現(xiàn)在以下幾個方面：訪問效率高，使用方便，維護(hù)簡單。數(shù)據(jù)冗余度低，存儲空間小，便于進(jìn)一步擴(kuò)展。應(yīng)用程序易于開發(fā)?；谟行О踩珯C(jī)制的數(shù)據(jù)安全可以得到保障。數(shù)據(jù)的備份和恢復(fù)容易實(shí)現(xiàn)。低劣的數(shù)據(jù)庫設(shè)計(jì)往往表現(xiàn)在以下幾個方面：數(shù)據(jù)訪問效率低下。大量的數(shù)據(jù)冗余造成存儲空間浪費(fèi)。數(shù)據(jù)的插入和刪除出現(xiàn)異常等問題?！?.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述早期的數(shù)據(jù)庫設(shè)計(jì)工作，都是依賴于設(shè)計(jì)人員的自身經(jīng)驗(yàn)，缺乏成熟的科學(xué)理論和工程方法的支持，因此設(shè)計(jì)質(zhì)量難以保證，常常數(shù)據(jù)庫運(yùn)行一段時間后又會發(fā)現(xiàn)不同程度的問題，需要不斷修改甚至重新設(shè)計(jì)，大大地增加了系統(tǒng)維護(hù)成本。經(jīng)過多年來不斷地探索，人們提出了各種數(shù)據(jù)庫設(shè)計(jì)方法。其中，新奧爾良（NewOrleans）方法是一種比較著名的數(shù)據(jù)庫設(shè)計(jì)方法，它通過分步設(shè)計(jì)，遵循自頂向下、逐步求精的原則，將數(shù)據(jù)庫設(shè)計(jì)過程分解為若干相互獨(dú)立又相互依存的階段，每一階段采用不同的輔助工具，解決不同的問題，從而將問題局部化，減少局部問題對整體設(shè)計(jì)的影響。3.1.1數(shù)據(jù)庫設(shè)計(jì)方法和步驟§3.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述3.1.1數(shù)據(jù)庫設(shè)計(jì)方法和步驟物理結(jié)構(gòu)設(shè)計(jì)需求分析概念結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)需求說明書E-R模型關(guān)系模型新奧爾良方法數(shù)據(jù)庫設(shè)計(jì)步驟§3.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述目前公認(rèn)的比較權(quán)威的方法是將數(shù)據(jù)庫系統(tǒng)設(shè)計(jì)分為6個階段：需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、物理結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫實(shí)施和數(shù)據(jù)庫運(yùn)行及維護(hù)。另外，我們在相關(guān)的文獻(xiàn)或者資料中也會看到一些其他的數(shù)據(jù)庫設(shè)計(jì)方法比如：基于E-R模型的設(shè)計(jì)方法、基于3NF（第三范式）的設(shè)計(jì)方法、基于抽象語法規(guī)范的設(shè)計(jì)方法等，這些都是針對不同數(shù)據(jù)庫設(shè)計(jì)階段使用的具體技術(shù)和方法。3.1.1數(shù)據(jù)庫設(shè)計(jì)方法和步驟§3.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述考慮到數(shù)據(jù)庫開發(fā)的全過程，按照結(jié)構(gòu)化設(shè)計(jì)的方法，將數(shù)據(jù)庫設(shè)計(jì)分為六個階段。1）需求分析

收集和分析用戶需求，包括數(shù)據(jù)與處理。2）概念結(jié)構(gòu)設(shè)計(jì)對用戶需求進(jìn)行歸納和抽象，形成概念模型（例如E-R模型）。3）邏輯結(jié)構(gòu)設(shè)計(jì)將概念模型轉(zhuǎn)換為某個數(shù)據(jù)庫管理系統(tǒng)所支持的數(shù)據(jù)模型（例如關(guān)系模型），并對其進(jìn)行優(yōu)化。3.1.2數(shù)據(jù)庫設(shè)計(jì)過程§3.1關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述4）物理結(jié)構(gòu)設(shè)計(jì)為數(shù)據(jù)模型選取一個最合適應(yīng)用環(huán)境的物理結(jié)構(gòu)（包括存儲結(jié)構(gòu)和存取方法）。5）數(shù)據(jù)庫實(shí)施根據(jù)邏輯設(shè)計(jì)和物理設(shè)計(jì)的結(jié)果建立數(shù)據(jù)庫，組織數(shù)據(jù)入庫并進(jìn)行試運(yùn)行。6）數(shù)據(jù)庫運(yùn)行和維護(hù)在數(shù)據(jù)庫系統(tǒng)運(yùn)行過程中不斷地對其進(jìn)行調(diào)整、評價與修改。設(shè)計(jì)一個完善的數(shù)據(jù)庫系統(tǒng)是不可能一蹴而就的，它往往是上述六個階段的不斷反復(fù)、逐步求精的過程。3.1.2數(shù)據(jù)庫設(shè)計(jì)過程§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)關(guān)系數(shù)據(jù)庫設(shè)計(jì)主要包括需求分析、概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)、物理結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)庫實(shí)施與維護(hù)6個階段。每個階段都有不同的內(nèi)容和任務(wù)，具體如下：§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)需求分析是設(shè)計(jì)數(shù)據(jù)庫的起點(diǎn)。需求分析是設(shè)計(jì)人員通過與用戶交流和調(diào)查等，分析并逐步明確用戶對系統(tǒng)的需求，包括數(shù)據(jù)需求、業(yè)務(wù)處理需求、安全性及完整性要求等。需求分析的結(jié)果是否準(zhǔn)確將直接影響后面各個階段的設(shè)計(jì)，并影響到設(shè)計(jì)結(jié)果是否合理和實(shí)用，因此需求分析是整個設(shè)計(jì)工作最基礎(chǔ)也是最耗時的部分。需求分析重點(diǎn)是“數(shù)據(jù)”和“處理”，通過調(diào)查、收集與分析，獲得用戶對數(shù)據(jù)庫的如下要求：1）數(shù)據(jù)要求。即用戶對需要處理的數(shù)據(jù)內(nèi)容及性質(zhì)的要求。2）處理要求。指用戶要求數(shù)據(jù)庫需要具備的數(shù)據(jù)處理功能，用戶對處理性能的要求。3）安全性與完整性要求。3.2.1需求分析§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)調(diào)查用戶需求的具體步驟如下：1）調(diào)查組織機(jī)構(gòu)情況和各部門的業(yè)務(wù)活動情況。2）在熟悉業(yè)務(wù)活動的基礎(chǔ)上，協(xié)助用戶明確對新系統(tǒng)的各種要求，包括數(shù)據(jù)要求、處理要求、安全性與完整性要求。3）確定新系統(tǒng)的邊界。基于前面的調(diào)查和分析，明確哪些功能由計(jì)算機(jī)完成或?qū)頊?zhǔn)備讓計(jì)算機(jī)完成，哪些活動由人工完成。由計(jì)算機(jī)完成的功能就是新系統(tǒng)應(yīng)該實(shí)現(xiàn)的功能。3.2.1需求分析§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)常用調(diào)查方法如下：1）跟班作業(yè)和業(yè)務(wù)咨詢。通過親身參加業(yè)務(wù)工作或者與客戶進(jìn)行座談、請專人介紹等來了解業(yè)務(wù)活動的情況。2）組織用戶填寫問卷調(diào)查。3）查閱記錄。查閱與原系統(tǒng)有關(guān)的數(shù)據(jù)記錄。3.2.1需求分析§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)【例3.1】對網(wǎng)絡(luò)購物系統(tǒng)進(jìn)行需求分析。3.2.1需求分析1）數(shù)據(jù)要求系統(tǒng)存儲客戶和商品的基本信息，詳細(xì)記錄客戶的訂單信息?！?.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)3.2.1需求分析2）處理要求客戶可以查看自己的信息；可以查看不同商品信息；能夠下單購買商品；可以查詢自己的訂單情況。系統(tǒng)能夠?qū)ι唐返那闆r進(jìn)行統(tǒng)計(jì)；能夠?qū)τ唵吻闆r進(jìn)行統(tǒng)計(jì)。系統(tǒng)可以加入新的客戶信息；加入新的商品信息。系統(tǒng)可以刪除過期商品信息。系統(tǒng)可以更新訂單情況。系統(tǒng)可以查詢客戶及商品信息；可以查詢訂單情況；可以對商品和客戶的購買情況進(jìn)行分類統(tǒng)計(jì)。§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)3.2.1需求分析3）數(shù)據(jù)庫安全性與完整性需求。為保證數(shù)據(jù)庫的安全，需要給不同用戶設(shè)置不同的權(quán)限。顧客對自己所購買商品有選擇和查詢的權(quán)限，可以查看自己的訂單，但是對其它顧客的訂單無查看權(quán)限，也沒有修改權(quán)限。為了防止不符合規(guī)范的數(shù)據(jù)進(jìn)入數(shù)據(jù)庫，確保數(shù)據(jù)庫中存儲的數(shù)據(jù)正確、有效、相容，關(guān)系數(shù)據(jù)庫必須滿足關(guān)系的完整性約束條件。精確的需求分析，可以幫助我們在接下來的概念結(jié)構(gòu)和邏輯結(jié)構(gòu)設(shè)計(jì)階段，準(zhǔn)確設(shè)定關(guān)系表的主鍵、外鍵以及各個屬性值的類型和取值范圍，從而在實(shí)體完整性、參照完整性和用戶定義完整性三個方面滿足數(shù)據(jù)庫的完整性需求?！?.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)明確了用戶的需求之后，接下來就要對用戶需要處理的數(shù)據(jù)以及操作進(jìn)行歸納和抽象，形成概念模型，這個過程就是概念結(jié)構(gòu)設(shè)計(jì)。概念結(jié)構(gòu)設(shè)計(jì)是設(shè)計(jì)人員從用戶的視角，對信息進(jìn)行抽象和描述，因此概念模型應(yīng)該具備真實(shí)、客觀反映現(xiàn)實(shí)世界和易于理解的特點(diǎn)。目前最經(jīng)常使用的概念模型是E-R模型。在前面2.1.1章節(jié)，我們介紹了概念模型的相關(guān)概念：實(shí)體、實(shí)體型、實(shí)體集、屬性、聯(lián)系，大家對概念模型有了初步的了解，下面來深入了解實(shí)體之間的聯(lián)系。3.2.2

概念結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)實(shí)體之間的聯(lián)系通常指不同實(shí)體集之間的聯(lián)系。實(shí)體集之間的聯(lián)系類型有如下三種：1）一對一（1:1）：如果實(shí)體集A中的每個實(shí)體，在實(shí)體集B中至多有一個（也可以沒有）實(shí)體與之關(guān)聯(lián)，反之亦然。則實(shí)體集A與實(shí)體集B為一對一聯(lián)系，記為1：1。例如班長與班級之間的聯(lián)系：一個班長管理一個班級，每一個班級有一個班長，因此班長與班級之間的聯(lián)系類型為1：1，實(shí)體集之間的聯(lián)系可以用圖表示。2）一對多（1:n）:實(shí)體集A中的每個實(shí)體在實(shí)體集B中有n個（n≥0）實(shí)體與之關(guān)聯(lián)，而實(shí)體集B中的每個實(shí)體在實(shí)體集A中最多只有1個實(shí)體與之關(guān)聯(lián)，則實(shí)體集A與實(shí)體集B為一對多聯(lián)系，記為1：n。例如客戶與訂單之間的聯(lián)系：一個客戶可以下單多次，而每個訂單只涉及一個客戶，所以客戶與訂單之間的聯(lián)系類型為1：n。3.2.2概念結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)3）多對多（m:n）:實(shí)體集A中的每個實(shí)體在實(shí)體集B中有n個（n≥0）實(shí)體與之關(guān)聯(lián)，實(shí)體集B中的每個實(shí)體在實(shí)體集A中有m個（m≥0）實(shí)體與之關(guān)聯(lián)，則實(shí)體集A與實(shí)體集B為多對多聯(lián)系，記為m：n。例如訂單與商品之間的聯(lián)系：一個訂單會涉及多個商品，而每種商品會被多個訂單包含，因此訂單與商品之間的聯(lián)系類型就是m：n。3.2.2

概念結(jié)構(gòu)設(shè)計(jì)班長管理班級11客戶產(chǎn)生訂單m1訂單包含商品mn§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)E-R圖是E-R模型圖形化表示方法，它提供了實(shí)體集、屬性和實(shí)體集之間聯(lián)系表示方法：用矩形表示實(shí)體集，矩形框內(nèi)標(biāo)注實(shí)體集名稱。用橢圓形表示屬性，橢圓形框內(nèi)標(biāo)注屬性名字，并用無向邊將其與相應(yīng)的實(shí)體連接起來。用菱形表示實(shí)體集之間的聯(lián)系，菱形框內(nèi)標(biāo)注聯(lián)系名稱，并用無向邊將其與所聯(lián)系的實(shí)體集連接起來。3.2.2概念結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)例：用E-R圖來表示網(wǎng)絡(luò)購物系統(tǒng)概念模型。1）實(shí)體集網(wǎng)絡(luò)購物系統(tǒng)所涉及的實(shí)體集有：客戶、商品和訂單。2）屬性客戶實(shí)體的屬性有：編號、姓名、性別、注冊日期、電話等。商品實(shí)體屬性有：商品的名稱、編號、類別、庫存、是否上架、定價、售價等屬性刻畫。訂單實(shí)體屬性有：訂單號、客戶編號、商品編號、訂單日期、發(fā)貨日期、送貨地址、城市等。3.2.2概念結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)3）聯(lián)系每個客戶會下多個訂單，而每個訂單只能對應(yīng)一個客戶，因此客戶和訂單是1：m聯(lián)系。每個訂單可以涉及多種商品，而每種商品也可以包含在多個訂單中，因此訂單與商品之間是m：n的聯(lián)系。下圖為網(wǎng)絡(luò)購物系統(tǒng)E-R模型圖。3.2.2概念結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)是將概念結(jié)構(gòu)設(shè)計(jì)階段完成的E-R模型，轉(zhuǎn)換成被選定的數(shù)據(jù)庫管理系統(tǒng)支持的數(shù)據(jù)模型。由于目前的數(shù)據(jù)庫應(yīng)用系統(tǒng)大多采用支持關(guān)系模型的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，因此，這里主要講E-R模型轉(zhuǎn)換為關(guān)系模型的方法。E-R模型包含實(shí)體集、屬性、實(shí)體集之間的聯(lián)系三部分內(nèi)容。關(guān)系模型通過關(guān)系即二維表表示實(shí)體集以及實(shí)體集之間的聯(lián)系。因此，將E-R模型轉(zhuǎn)換成關(guān)系模型，實(shí)際上就是將實(shí)體集以及實(shí)體集之間的聯(lián)系轉(zhuǎn)換為一個個關(guān)系。轉(zhuǎn)換規(guī)則如下：3.2.2

邏輯結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)1）實(shí)體集的轉(zhuǎn)換：一個實(shí)體集轉(zhuǎn)換成一個關(guān)系，實(shí)體的屬性即為關(guān)系的屬性，實(shí)體的碼即為關(guān)系的碼。2）聯(lián)系的轉(zhuǎn)換：根據(jù)不同的聯(lián)系類型做不同的處理：一對一的聯(lián)系，可以轉(zhuǎn)換為一個獨(dú)立的關(guān)系，也可以與任意一端的關(guān)系合并。如果轉(zhuǎn)換為一個獨(dú)立的關(guān)系，則與該聯(lián)系相連的各個實(shí)體的碼以及該聯(lián)系本身的屬性作為關(guān)系的屬性；如果與任意一端的關(guān)系合并，則需要加入另外一個實(shí)體的碼和聯(lián)系本身的屬性作為此關(guān)系的屬性。一對多的聯(lián)系，可以轉(zhuǎn)換為一個獨(dú)立的關(guān)系，也可以與多端的關(guān)系合并。如果轉(zhuǎn)化為一個獨(dú)立的關(guān)系，則與該聯(lián)系相連的各實(shí)體的碼以及聯(lián)系本身的屬性作為此關(guān)系的屬性。若與多端的關(guān)系合并，需加入一端實(shí)體的碼和聯(lián)系的屬性作為此關(guān)系的屬性。3.2.2邏輯結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)多對多的聯(lián)系，轉(zhuǎn)換為一個獨(dú)立的關(guān)系，其屬性為多端實(shí)體的碼加上聯(lián)系本身的屬性，關(guān)系的主鍵包括各實(shí)體的碼。3個或3個以上的實(shí)體集間的一個多元聯(lián)系，不管是何種聯(lián)系類型，總是將多元聯(lián)系類型轉(zhuǎn)換成一個關(guān)系，其屬性為與該聯(lián)系相連的各實(shí)體的碼及聯(lián)系本身的屬性。3.2.2邏輯結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)例：將網(wǎng)絡(luò)購物系統(tǒng)E-R圖轉(zhuǎn)化為關(guān)系模型3.2.2邏輯結(jié)構(gòu)設(shè)計(jì)客戶實(shí)體集轉(zhuǎn)換為客戶表：customers（customer_id，name，gender，registration_date，phone）

商品實(shí)體集轉(zhuǎn)換為商品表：items（item_id，item_name，category，cost，price，inventory，is_online）訂單實(shí)體集轉(zhuǎn)換為訂單表：orders（order_id，order_date，address，city，shipping_date）客戶與訂單的聯(lián)系與多端（訂單實(shí)體集端）合并后新的訂單表：orders（order_id，customer_id，order_date，address，city，shipping_date）訂單與商品之間的聯(lián)系轉(zhuǎn)換為訂單明細(xì)表:order_details（order_id，item_id，discount，quantity）§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)數(shù)據(jù)庫在物理設(shè)備上的存儲結(jié)構(gòu)與存取方法稱為數(shù)據(jù)庫的物理結(jié)構(gòu)。針對已經(jīng)確定的數(shù)據(jù)庫邏輯結(jié)構(gòu)，選取一個最適合應(yīng)用需求的物理結(jié)構(gòu)的過程，就是數(shù)據(jù)庫的物理結(jié)構(gòu)設(shè)計(jì)，它依賴于選定的數(shù)據(jù)庫管理系統(tǒng)。系統(tǒng)會根據(jù)數(shù)據(jù)的具體情況確定存儲結(jié)構(gòu)和存取方法。物理結(jié)構(gòu)設(shè)計(jì)過程中要對時間效率、空間效率、維護(hù)代價和各種用戶要求進(jìn)行權(quán)衡，其結(jié)果可以產(chǎn)生多種方案，數(shù)據(jù)庫設(shè)計(jì)者必須對這些方案進(jìn)行細(xì)致的評價，從中選擇一個較優(yōu)的方案作為數(shù)據(jù)庫的物理結(jié)構(gòu)。如果評價結(jié)構(gòu)滿足原設(shè)計(jì)要求，則可進(jìn)入到物理實(shí)施階段，否則，就需要重新設(shè)計(jì)或修改物理結(jié)構(gòu)，有時甚至要返回邏輯設(shè)計(jì)階段修改數(shù)據(jù)模型。3.2.4

物理結(jié)構(gòu)設(shè)計(jì)§3.2關(guān)系數(shù)據(jù)庫的設(shè)計(jì)

數(shù)據(jù)庫實(shí)施階段，設(shè)計(jì)人員根據(jù)邏輯設(shè)計(jì)和物理設(shè)計(jì)的結(jié)果建立數(shù)據(jù)庫，加載數(shù)據(jù)，并進(jìn)行試運(yùn)行。試運(yùn)行階段要重視數(shù)據(jù)庫的校驗(yàn)工作。試運(yùn)行過程中要測試各個功能是否滿足設(shè)計(jì)的要求，對數(shù)據(jù)庫的性能指標(biāo)進(jìn)行測試，分析其是否達(dá)到設(shè)計(jì)目標(biāo)，未達(dá)到目標(biāo)時需要針對數(shù)據(jù)庫設(shè)計(jì)的各個階段進(jìn)行修改和調(diào)整，以滿足用戶的需求。數(shù)據(jù)庫系統(tǒng)經(jīng)過試運(yùn)行后，即可投入正式運(yùn)行，在數(shù)據(jù)庫系統(tǒng)運(yùn)行過程中必須不斷地對其進(jìn)行評價、調(diào)整、修改。數(shù)據(jù)庫設(shè)計(jì)的優(yōu)劣是相對的，不能保證數(shù)據(jù)庫應(yīng)用系統(tǒng)始終處于良好狀態(tài)，為了保證良好的應(yīng)用效果，需要在運(yùn)行過程中不斷的對數(shù)據(jù)庫進(jìn)行優(yōu)化和維護(hù)。3.2.5

數(shù)據(jù)庫實(shí)施與維護(hù)§3.3關(guān)系模型規(guī)范化設(shè)計(jì)數(shù)據(jù)庫設(shè)計(jì)的邏輯結(jié)構(gòu)設(shè)計(jì)過程中，針對一個具體的應(yīng)用，怎樣才能構(gòu)造一個合適的數(shù)據(jù)庫模式，即應(yīng)該構(gòu)造幾個關(guān)系模式，每個關(guān)系應(yīng)該有哪些屬性組成等，這需要一定的衡量標(biāo)準(zhǔn)，關(guān)系模型的規(guī)范化理論就是一個數(shù)據(jù)庫設(shè)計(jì)指導(dǎo)理論和衡量標(biāo)準(zhǔn)，它可以幫助我們設(shè)計(jì)出良好的關(guān)系模式并避免后續(xù)數(shù)據(jù)庫操作過程中可能出現(xiàn)的問題。這里涉及到范式的概念，不同的范式表示關(guān)系模式需要遵守的不同規(guī)則，在學(xué)習(xí)范式之前需要先了解函數(shù)依賴和關(guān)系模式中的鍵。§3.3關(guān)系模型規(guī)范化設(shè)計(jì)設(shè)關(guān)系模式a_schema（customer_id，name，gender，address，city，item_id，order_date，shipping_date），（customer_id，item_id）為主鍵，表3.1中是關(guān)系模式某一時刻的數(shù)據(jù)表：3.3.1函數(shù)依賴customer_idnamegenderaddresscityitem_idorder_dateshipping_date101薛為民男海淀區(qū)西三旗育新花園小區(qū)0號樓302北京市b0012019-12-312020-1-1101薛為民男海淀區(qū)西三旗育新花園小區(qū)0號樓302北京市f0022019-12-312020-1-1101薛為民男海淀區(qū)西三旗育新花園小區(qū)0號樓302北京市b0022019-12-312020-1-1105Adrian男武清區(qū)巴孔閘路22號0號樓201天津市c0032020-5-62020-5-7105Adrian男武清區(qū)巴孔閘路22號0號樓201天津市b0022020-5-62020-5-7105Adrian男武清區(qū)巴孔閘路22號0號樓201天津市f0012018-9-82018-9-10106孫麗娜女萊西市姜山鎮(zhèn)北口村0號101青島市f0012021-3-32021-3-3§3.3關(guān)系模型規(guī)范化設(shè)計(jì)1）數(shù)據(jù)冗余問題：這個關(guān)系中，顧客的基本信息（包括姓名、性別）以及顧客購物的地址和訂單日期有冗余。一個顧客每個訂單買多少種不同的商品，這個顧客對應(yīng)的姓名、性別基本信息以及購物的地址、城市以及訂單日期這些信息就會重復(fù)多少遍。2）數(shù)據(jù)更新問題：如果一個顧客的一個基本信息發(fā)生了變化，那么該顧客購買了幾種商品我們就需要更改多少條顧客基本信息，從而使修改變得繁瑣，這樣還會造成信息不一致。3）數(shù)據(jù)插入問題：如果購物平臺有了新的顧客加入，即已經(jīng)有了顧客基本信息，但是我們也不能把該顧客加入到這個表中，因?yàn)轭櫩蜎]有購物，它的item_id是空的，而作為主鍵的item_id，是不允許為空的。4）數(shù)據(jù)刪除問題：如果一個顧客只購買了一種商品，后來又不買了，在刪掉該顧客的購買記錄的同時，這個顧客基本信息也被刪除掉了。3.3.1

函數(shù)依賴§3.3關(guān)系模型規(guī)范化設(shè)計(jì)

基于以上種種問題，我們可以看出，該關(guān)系模式不是一個好的關(guān)系模式，究其原因則是因?yàn)檫@個關(guān)系模式中某些屬性存在“不良”函數(shù)依賴關(guān)系，下面我們來介紹函數(shù)依賴概念。函數(shù)依賴定義：

設(shè)R(U)是屬性集U上的關(guān)系模式，X、Y是U的子集。若對于R(U)的任意一個可能的關(guān)系r，如果r中兩個元組在X上的屬性值相等，在Y上的屬性值也一定相等，則稱X函數(shù)確定Y或Y函數(shù)依賴于X，記作X→Y。其中X叫做決定因素，Y叫做依賴因素。3.3.1

函數(shù)依賴§3.3關(guān)系模型規(guī)范化設(shè)計(jì)跟函數(shù)依賴相關(guān)的一些術(shù)語和記號：1）X→Y，但YX（Y不包含于X），則稱X→Y是非平凡的函數(shù)依賴。2）X→Y，但YX（Y包含于X），則稱X→Y是平凡的函數(shù)依賴。若不特別聲明，本書后面提到的函數(shù)依賴，都指非平凡的函數(shù)依賴。1）若X→Y，Y→X，則記作XY。2）若Y不函數(shù)依賴于X，則記作X→Y。3）如果X→Y，并且對于X的任何一個真子集X′，都有X′→Y，則稱Y對X完全函數(shù)依賴，記作X→Y。3.3.1函數(shù)依賴F§3.3關(guān)系模型規(guī)范化設(shè)計(jì)4）若X→Y，如果存在X的某一真子集X′，使X′→Y，則稱Y對X部分函數(shù)依賴，記作X→Y。5）如果X→Y（非平凡函數(shù)依賴，且Y→X），Y→Z，則稱Z傳遞函數(shù)依賴于X，記作X→Z。例：關(guān)系模式customers（customer_id，name，gender，phone），主鍵為customer_id，則存在以下函數(shù)依賴關(guān)系：customer_id→name客戶姓名函數(shù)依賴于客戶編號（customer_id，gender）→name客戶姓名部分函數(shù)依賴于客戶編號和性別3.3.1函數(shù)依賴p傳遞§3.3關(guān)系模型規(guī)范化設(shè)計(jì)【例3.5】關(guān)系模式order_details（order_id，item_id，discount，quantity），主鍵為（order_id，item_id），存在以下函數(shù)依賴關(guān)系：（order_id，item_id）→discount商品折扣完全函數(shù)依賴于訂單編號和商品編號3.3.1

函數(shù)依賴F設(shè)關(guān)系模式c_schema（customer_id，city，province），主鍵為customer_id，存在函數(shù)依賴：customer_id→city城市函數(shù)依賴于顧客編號city→province所在省份函數(shù)依賴于城市所以有：customer_id→province省份通過城市傳遞函數(shù)依賴于顧客編號傳遞§3.3關(guān)系模型規(guī)范化設(shè)計(jì)鍵（碼）是關(guān)系模式中一個很重要的概念，前面第二章已經(jīng)給出了鍵的若干定義，這里用函數(shù)依賴的概念來定義鍵。設(shè)U表示關(guān)系模式R的屬性全集，即U=R{A1，A2，A3…，An}，F(xiàn)表示關(guān)系模式R上的函數(shù)依賴集，則關(guān)系模式可以表示為R（U，F(xiàn)）。1）候選鍵設(shè)K為關(guān)系模式R（U，F(xiàn)）的屬性或?qū)傩越M，若K→U，則K為R的候選鍵。2）主鍵如果關(guān)系模式R（U，F(xiàn)）中有多個候選鍵，選其中的一個作為主鍵。3）全鍵如果候選鍵為整個屬性組則稱為全鍵。3.3.2關(guān)系模式中的鍵§3.3關(guān)系模型規(guī)范化設(shè)計(jì)關(guān)系模式R（U，F(xiàn)）中，包含在任一候選鍵中的屬性稱為主屬性，不包含在任一候選鍵中的屬性稱為非主屬性。4）外鍵一個關(guān)系模式R（U，F(xiàn)）中的某個屬性（組）不是R的主鍵，但它是另外一個關(guān)系的主鍵，則該屬性（組）稱為關(guān)系R的外鍵。例如關(guān)系模式order_details（order_id，item_id，discount，quantity）中，order_id不是主鍵，但它是關(guān)系模式orders（order_id，customer_id，order_date，address，city，shipping_date）的主鍵，因此order_id是order_details的外鍵，同理，item_id也是order_details的外鍵。3.3.2關(guān)系模式中的鍵§3.3關(guān)系模型規(guī)范化設(shè)計(jì)【例3.7】關(guān)系模式customers（customer_id，name，gender，phone），設(shè)不同的顧客手機(jī)號碼不同，則該關(guān)系模式中：候選鍵：customer_id，phone主鍵：customer_id，或者phone主屬性：customer_id，phone非主屬性：name，gender3.3.2關(guān)系模式中的鍵§3.3關(guān)系模型規(guī)范化設(shè)計(jì)例3.8】關(guān)系模式order_details（order_id，item_id，discount，quantity），則該關(guān)系模式中：候選鍵：（order_id，item_id）主鍵：（order_id，item_id）主屬性：order_id，item_id非主屬性：discount，quantity外鍵：order_id，item_id3.3.2關(guān)系模式中的鍵§3.3關(guān)系模型規(guī)范化設(shè)計(jì)例3.9】設(shè)有關(guān)系模式order_schema（customer_id，item_id，order_date），如果規(guī)定每一個顧客同一種商品可以多次購買，order_date中的數(shù)據(jù)包括日期和時間，則該關(guān)系模式中：候選鍵：（customer_id，item_id，order_date）主鍵：也就是該候選鍵主屬性：customer_id，item_id，order_date非主屬性：無外鍵：customer_id，item_id這種候選鍵為全部屬性的表稱為全鍵表3.3.2關(guān)系模式中的鍵§3.3關(guān)系模型規(guī)范化設(shè)計(jì)關(guān)系數(shù)據(jù)庫中的關(guān)系模式需要滿足一定的要求，不同的要求對應(yīng)不同范式。關(guān)系模式按其規(guī)范化程度從低到高可分為5級范式。滿足最低要求的關(guān)系模式稱為第一范式，簡稱1NF。在第一范式中又滿足一些要求的稱為第二范式，簡稱2NF，以此類推，還有3NF、BCNF、4NF、5NF。所有范式中，只要關(guān)系模式滿足第一范式，他就是合法的、允許的。后來，人們發(fā)現(xiàn)某些關(guān)系模式存在插入、刪除異常，以及冗余度高、修改復(fù)雜等問題，因此開始研究關(guān)系模式的規(guī)范化問題。E.F.Codd在1971-1972年提出1NF、2NF、3NF的概念，1974年Codd和Boyce共同提出了新的范式BCNF，后來又有研究人員相繼提出了4NF、5NF。規(guī)范化程度較高者必是較低者的子集。各種范式之間的關(guān)系如下：5NF?4NF?BCNF?3NF?2NF?1NF3.3.3

范式§3.3關(guān)系模型規(guī)范化設(shè)計(jì)1．第一范式定義：每個屬性均不能再分解的關(guān)系模式。它是關(guān)系模式最基本的規(guī)范形式。如果關(guān)系模式R為第一范式則記作R∈1NF。第一范式要求每個屬性必須是不可分的數(shù)據(jù)項(xiàng)。圖3.4這個表中“訂單情況”不是基本數(shù)據(jù)項(xiàng)，它是由兩個基本數(shù)據(jù)項(xiàng)“訂單日期”和“發(fā)貨日期”組成的一個復(fù)合數(shù)據(jù)項(xiàng)。因此這個關(guān)系模式不是第一范式。非第一范式的關(guān)系轉(zhuǎn)換成第一范式關(guān)系只需要將所有數(shù)據(jù)項(xiàng)都表示為不可分的最小數(shù)據(jù)項(xiàng)即可，如圖所示。3.3.3

范式顧客編號訂單情況訂單日期發(fā)貨日期1012019-12-312020-1-11022021-3-52021-3-61032020-1-22020-1-3顧客編號訂單日期發(fā)貨日期1012019-12-312020-1-11022021-3-52021-3-61032020-1-22020-1-3不符合第一范式符合第一范式§3.3關(guān)系模型規(guī)范化設(shè)計(jì)2．第二范式定義：如果關(guān)系模式R（U，F(xiàn)）∈1NF，且R中的每個非主屬性完全函數(shù)依賴于R的候選鍵，則R為第二范式，記作R∈2NF。例如關(guān)系模式c_schema（order_id，item_id，order_date，quantity），候選鍵為（order_id，item_id）。該關(guān)系模式存在以下函數(shù)依賴關(guān)系：

order_id→order_date（訂單日期函數(shù)依賴于訂單編號）所以存在部分函數(shù)依賴關(guān)系：（order_id，item_id）→order_date（訂單日期部分函數(shù)依賴于候選鍵）因此，該關(guān)系模式不滿足2NF。關(guān)系模式不滿足2NF就會產(chǎn)生插入和刪除異常以及修改復(fù)雜的問題。3.3.3

范式§3.3關(guān)系模型規(guī)范化設(shè)計(jì)可以通過模式分解將低一級范式的關(guān)系模式轉(zhuǎn)換為高一級范式的關(guān)系模式集合。我們可以將關(guān)系模式c_schema分解為c_schema（order_id，item_id，quantity）和d_schema（order_id，order_date）兩個關(guān)系模式。分解后的這兩個關(guān)系模式都符合2NF了。3.3.3

范式§3.3關(guān)系模型規(guī)范化設(shè)計(jì)

3． 第三范式定義：如果關(guān)系模式R（U，F(xiàn)）∈2NF，且每個非主屬性都不傳遞函數(shù)依賴于候選鍵屬性，則R滿足第三范式，記作R∈3NF。例如關(guān)系模式e_schema（order_id，city，province），候選鍵為order_id，存在如下函數(shù)依賴關(guān)系：

order_id→city（收貨城市函數(shù)依賴于訂單編號）

city→province（省份函數(shù)依賴于城市）所以有以下傳遞函數(shù)依賴關(guān)系：order_id→province3.3.3范式傳遞§3.3關(guān)系模型規(guī)范化設(shè)計(jì)因此，該關(guān)系模式不滿足3NF，從而也會導(dǎo)致數(shù)據(jù)插入、刪除操作異常，以及修改復(fù)雜的問題。可以將此關(guān)系模式分解為e_schema（order_id，city）和f_schema（city，province），分解后的兩個關(guān)系均不存在非主屬性對候選鍵屬性的傳遞函數(shù)依賴，滿足第三范式。3.3.3

范式§3.3關(guān)系模型規(guī)范化設(shè)計(jì)對于規(guī)范化程度低的關(guān)系模式，可以將其分解為若干個規(guī)范化程度高的關(guān)系模式，以此提高關(guān)系模式規(guī)范化程度。分解后的關(guān)系模式從語義上來說，每個關(guān)系只能描述一個主題，如果描述了多個主題，這個關(guān)系還要進(jìn)行進(jìn)一步分解。分解后的模式應(yīng)該與原來的模式等價，不能在規(guī)范化過程中消除了一個問題同時又產(chǎn)生了其他問題，因此模式分解必須遵守以下兩個原則：模式分解具有無損連接性。模式分解保持函數(shù)依賴。無損連接是指分解后的關(guān)系模式通過自然連接能夠恢復(fù)到原來的關(guān)系，恢復(fù)后既不會增加信息也不會減少信息。保持函數(shù)依賴是指分解過程中，原來關(guān)系模式中的函數(shù)依賴不能丟失，也就是分解前后關(guān)系模式的語義應(yīng)該保持一致。3.3.4關(guān)系模式的規(guī)范化§3.3關(guān)系模型規(guī)范化設(shè)計(jì)

【例3.8】關(guān)系模式h_schema（order_id，item_id，customer_id，name，discount，address，city，order_date），某個時刻數(shù)據(jù)表如表3.2所示，對其進(jìn)行規(guī)范化處理。

3.3.4

關(guān)系模式的規(guī)范化order_iditem_idcustomer_idnamediscountaddresscityorder_date1sm01105Adrian_Smith0.85海淀區(qū)西三旗幸福小區(qū)60號樓6單元606北京市2018-5-612:10:203b001107林琳0.80武清區(qū)流星花園6-6-66天津市2018-6-1818:00:023b002107林琳0.85武清區(qū)流星花園6-6-66天津市2018-6-1818:00:023sm01107林琳0.90武清區(qū)流星花園6-6-66天津市2018-6-1818:00:024f001106孫麗娜0.80道里區(qū)和諧家園66-66-666哈爾濱市2018-11-1117:10:214sm01106孫麗娜0.90道里區(qū)和諧家園66-66-666哈爾濱市2018-11-1117:10:214sm02106孫麗娜0.90道里區(qū)和諧家園66-66-666哈爾濱市2018-11-1117:10:215m001103Grace_Brown0.90市北區(qū)幸福北里88號院青島市2019-2-117:51:015sm01103Grace_Brown0.90市北區(qū)幸福北里88號院青島市2019-2-117:51:016s002104趙文博0.90海淀區(qū)清河小營東路12號學(xué)9公寓北京市2019-6-1819:01:32§3.3關(guān)系模型規(guī)范化設(shè)計(jì)

【例3.8】關(guān)系模式h_schema（order_id，item_id，customer_id，

name，discount，address，city，order_date），某個時刻數(shù)據(jù)表如表3.2所示，對其進(jìn)行規(guī)范化處理。

從關(guān)系模式各個屬性來看，每個屬性都是最小的數(shù)據(jù)項(xiàng)，不可以再分解，因此該關(guān)系模式符合1NF。關(guān)系模式的候選鍵為有order_id和（customer_id，item_id，order_date），由于客戶編號不同，一般客戶的姓名也不同（即使客戶姓名相同也是不同的兩個人，重名現(xiàn)象），因此存在函數(shù)依賴關(guān)系：customer_id→name存在部分函數(shù)依賴關(guān)系：（customer_id，item_