第3章數據庫設計數據庫原理與應用教程-MySQL8.0在數據庫系統中，數據庫是最基本、最重要的部分。數據庫性能的高低，決定了整個數據庫應用系統的性能。一個好的數據庫需要通過嚴格的設計，才能滿足各方面對數據的需要。數據庫的設計和開發是一項龐大而復雜的工程。數據庫設計人員要具備的知識：（1）數據庫基本理論（2）數據庫設計技術（3）程序開發的實際經驗（4）軟件工程的原理和方法第3章數據庫設計本章主要內容：1、數據庫設計的基本概念2、數據庫設計的6個階段（1）需求分析（2）概念結構設計（3）邏輯結構設計（4）數據庫物理設計（5）數據庫實施（6）數據庫的運行和維護第3章數據庫設計數據庫設計是根據用戶需求設計數據庫結構的過程。具體一點講，數據庫設計是對于給定的應用環境，在關系數據庫理論的指導下，構造最優(最合理、最規范)的數據庫模式，在數據庫管理系統(如MySQL)上建立數據庫及其應用系統(如PHP網站、JAVA程序等)，使之能有效地存儲數據，滿足用戶的各種需求的過程。3.1數據庫設計概述數據庫設計方法有多種，按照規范設計的方法，數據庫設計可分為六個階段：需求分析、概念結構設計、邏輯結構設計、物理結構設計、數據庫實施、數據庫運行和維護。

3.1數據庫設計概述需求分析是設計數據庫的起點，就是要分析用戶的要求。需求分析階段的重要性*：需求分析就是數據庫設計人員，通過仔細地調查和向用戶詳細地咨詢，掌握用戶的需求，理解用戶的需求。需求分析的結果是否準確地反映了用戶的實際要求，將直接影響到后面各個階段的設計，并影響到設計結果是否合理和實用。可以說在一個大型數據庫系統的開發中，它的作用要遠遠大于其它各個階段。

3.2需求分析明確需求分析的任務：通過詳細調查現實世界要處理的對象（組織、部門、企業等），充分了解原系統（手工系統或計算機系統）工作概況，明確用戶的各種需求，并在此基礎上確定新系統的功能。注意：（1）一定要讓用戶解釋其需求，而不是數據庫設計人員自己憑空設計需求。（2）新系統必須充分考慮今后可能的擴充和改變，不能僅僅按當前應用需求來設計數據庫。

3.2.1需求分析的任務

需求分析調查的重點是“數據”和“處理”（1）信息(數據)要求：調查用戶的信息要求，確定在數據庫中需要存儲哪些數據。（2）處理要求:

指用戶要完成什么處理功能，處理時間要求及處理方式。（3）安全性與完整性要求安全性要求是指對數據庫的用戶、角色、權限、加密方法等安全保密措施的要求。完整性要求是指對數據取值范圍、數據之間各種聯系的要求等等。

3.2.1需求分析的任務

1.調查用戶需求的具體步驟(1)調查組織機構情況，包括了解該組織的部門組成情況、各部門的職責等，為分析信息流程做準備。(2)調查各部門的業務活動情況，包括了解各個部門輸入和使用什么數據，如何加工處理這些數據，輸出什么信息，輸出到什么部門，輸出結果的格式是什么，這是調查的重點。3.2.2需求分析的方法(3)在熟悉業務活動的基礎上，協助用戶明確對新系統的各種要求，包括信息要求、處理要求、完全性與完整性要求，這是調查的又一個重點。(4)確定新系統的邊界，對前面調查的結果進行初步分析，確定哪些功能由計算機完成或將來準備讓計算機完成，哪些活動由人工完成。3.2.2需求分析的方法2．常用的調查方法

(1)跟班作業。(2)開座談會。(3)詢問或請專人介紹。一般應包括領導、管理人員、操作員等。(4)設計調查表請用戶填寫需求。這種方法易于用戶接受。(5)查閱記錄。查閱與原系統有關的數據記錄。實際應用中，具體問題具體分析，但必須有用戶的參與。3.2.2需求分析的方法概念設計階段不是直接將需求分析得到的數據轉換為DBMS能處理的數據庫模式，而是將需求分析得到的用戶需求抽象為反映用戶觀點的概念模型（E-R模型）。3.3概念結構設計概念結構的模型的特點:（1）有豐富的語義表達能力：表達用戶的各種需求,反映數據及其聯系。（2）易于交流和理解：系統分析師、數據庫設計人員和用戶交流、理解。（3）易于修改：靈活性、反映用戶需求和環境的變化。（4）易于向各種數據模型轉換。3.3概念結構設計（1）自頂向下法：建立初步框架，即全局E-R模型，然后再逐步細化（2）自底向上法。先得到局部E-R模型，再進一步綜合成全局E-R模型（3）逐步擴張法。先定義最重要的核心概念E-R模型，然后向外擴充（4）混合策略：（1）（2）混合使用其中最常用的策略是自底向上法。3.3.1概念結構設計的方法1．設計局部E-R模型的步驟：（1）確定局部E-R圖描述的范圍兩條原則：獨立性原則：一個范圍內的應用功能具有獨立性與完整性，與其他范圍內的應用有最少的聯系。規模適度原則：局部E-R圖規模應適度，一般以6個左右實體為宜。3.3.2概念結構設計的步驟（2）確定局部E-R圖的實體根據需求分析說明書，將用戶需求中涉及的數據對象進行歸類，指明是實體、聯系還是屬性。（3）定義實體的屬性（4）定義實體間的聯系一對一、一對多、多對多三種。3.3.2概念結構設計的步驟2．局部E-R模型的集成將各個局部E-R模型去掉不一致和重復的地方，合并為全局視圖，即局部E-R模型的集成。一般有兩種集成方式：（1）多個分E-R圖一次集成；（2）逐步集成*：用累加的方式一次集成兩個分E-R圖。3.3.2概念結構設計的步驟集成分為兩個階段：第一步是合并，以消除各局部E-R圖之間的不一致（沖突）情況，生成初步的E-R圖；第二步是優化，消除不必要的數據冗余（多余的數據或聯系），生成全局E-R圖。

3.3.2概念結構設計的步驟簡易教學信息數據庫系統E-R圖（學生和教師，學生和學院為多余的聯系；平均成績為多余的屬性）3.3.2概念結構設計的步驟1NNM課程學生選修姓名性別年齡課程號課程名學分成績學號班級屬于班主任班級名教師教師號姓名性別教授N11學院地址學院名隸屬M屬于1N質量評價平均成績數據庫概念設計階段得到的數據模式轉換成某個具體的DBMS所支持的數據模式，并建立相應的外模式，這是數據庫邏輯設計的任務，是數據庫結構設計的重要階段。邏輯結構設計一般要分為三步*：將E-R圖轉化為關系數據模型關系模式的優化設計用戶外模式3.4邏輯結構設計將E-R圖轉化為關系數據模型實際上是要將實體、屬性和實體之間的聯系轉化為關系模式。轉化過程中要遵循的原則：1.實體的轉換*一個實體轉化為一個關系模式。實體的屬性就是關系的屬性。實體的主碼就是關系的主碼。

3.4.1將E-R圖轉化為關系數據模型

例：將如圖所示的學生實體，轉換為關系模式。轉換成的關系模式為：學生（學號,姓名,出生日期,所在系,年級,平均成績)3.4.1將E-R圖轉化為關系數據模型

2.聯系的轉換**（1）兩實體間1:1聯系可以轉換為一個獨立的關系模式，也可以與任意一端對應的關系模式合并。②與某一端對應的關系模式合并合并后關系的屬性包括自身關系模式的屬性和另一關系模式的碼及聯系本身的屬性；合并后關系的主碼不變。3.4.1將E-R圖轉化為關系數據模型

例：將如圖所示的E-R圖，轉換為關系模式。1班級班號年齡班主任班長班長-班級任期姓名入學時間學號系別性別13.4.1將E-R圖轉化為關系數據模型

轉換成如下關系模式：班級(班號,系別,班主任,入學時間班長(學號,姓名,性別,年齡)或：班級(班號,系別,班主任,入學時間)班長(學號,姓名,性別,年齡）,班長學號,班長任期),班號,任期))3.4.1將E-R圖轉化為關系數據模型

（2）兩實體間1:n聯系可以轉換為一個獨立的關系模式，也可以與n端對應的關系模式合并。②與n端對應的關系模式合并合并后關系的屬性：在n端關系中加入1端關系的主碼和聯系本身的屬性。合并后關系的主碼不變。3.4.1將E-R圖轉化為關系數據模型

例：將如圖所示的E-R圖轉換為合適的關系模式。3.4.1將E-R圖轉化為關系數據模型

對應的關系模式如下：系（系號，系名，系主任）教師（教師號，教師名，年齡，職稱，系號，入系日期）（4）兩實體間m:n聯系**：必須為聯系產生一個新的關系。該關系中至少包含被它所聯系的雙方實體的主碼，若聯系有屬性，也要并入該關系中。如果沒有指定另外的屬性（比如此聯系的ID）做為該關系的主碼，則一般該關系的主碼為雙方實體的主碼的組合或者雙方實體的主碼加其它屬性的組合。3.4.1將E-R圖轉化為關系數據模型

例:將如圖所示的E-R圖轉換成對應的關系模式。NM職工職務年齡職工號項目參加起始日期項目名姓名項目號薪酬性別鑒定日期3.4.1將E-R圖轉化為關系數據模型

關系模式如下：職工（職工號，姓名，性別，年齡，職務）項目（項目號，名稱，起始日期，鑒定日期）參加（職工號，項目號，薪酬）3.4.1將E-R圖轉化為關系數據模型

為了進一步提高數據庫應用系統的性能，還應努力減少關系模式中存在的各種異常，改善完整性、一致性和存儲效率。（1）規范化：具體過程見第2章。（2）調整結構，提高使用效率的優化：合并與分解3.4.2關系模式的優化

外模式也叫子模式，是用戶可直接訪問的數據模式。

外模式的優點：為應用程序提供了邏輯獨立性；可以更好地適應不同用戶對數據的需求；為用戶劃定了訪問數據的范圍，有利于數據的保密等。3.4.3設計用戶外模式數據庫最終要存儲在物理設備上。將邏輯設計中產生的數據庫邏輯模型結合指定的DBMS，設計出最適合應用環境的物理結構的過程，稱為數據庫的物理結構設計。為了設計數據庫的物理結構，設計人員必須充分了解所用DBMS的內部特征；充分了解數據系統的實際應用環境，特別是數據應用處理的頻率和響應時間的要求；充分了解外存儲設備的特性。3.5物理結構設計數據庫的物理結構設計分為兩個步驟：(1)確定數據庫的物理結構。(2)對所設計的物理結構進行評價。

3.5物理結構設計數據庫物理設計內容包括：（1）確定數據的存儲結構確定數據庫存儲結構時要綜合考慮存取時間、存儲空間利用率和維護代價三方面的因素。這三個方面常常是相互矛盾的，例如消除一切冗余數據雖然能夠節約存儲空間，但往往會導致檢索代價的增加，因此必須進行權衡，選擇一個折中方案。3.5.1確定數據庫的物理結構（2）設計數據的存取路徑DBMS常用存取方法有：索引方法（目前主要是B+樹索引方法），聚簇（CLUSTER）方法和HASH（哈希）方法。（3）確定數據的存放位置（經常存取部分和存取頻率較低部分分磁盤存放、數據表和索引分磁盤存放、數據和日志分磁盤存放等等）。3.5.1確定數據庫的物理結構（4）確定系統配置DBMS產品一般都提供了一些存儲分配參數，供設計人員和DBA對數據庫進行物理優化。初始情況下，系統都為這些變量賦予了合理的缺省值。但是這些值不一定適合每一種應用環境。

對系統配置的變量:例如,同時使用數據庫的用戶數,同時打開的數據庫對象數,緩沖區分配參數,物理塊裝填因子,數據庫的大小,鎖的數目等。在物理設計時應根據應用環境確定這些參數值，以使系統性能最佳。3.5.1確定數據庫的物理結構數據庫物理設計過程中需要對時間效率、空間效率、維護代價和各種用戶要求進行權衡，其結果可以產生多種方案，數據庫設計人員必須對這些方案進行細致的評價，從中選擇一個較優的方案作為數據庫的物理結構。

3.5.2評價物理結構3.6.1數據庫實施根據邏輯和物理設計的結果，在計算機上建立起實際的數據庫結構，并裝入數據，進行試運行和評價的過程，叫做數據庫的實施（或實現）。

3.6數據庫實施和運行、維護數據庫試運行結果符合設計目標后，數據庫就可以真正投入運行了。數據庫投入運行標著開發任務的基本完成和維護工作的開始。維護工作包括:數據庫的轉儲和恢復；數據庫的安全性和完整性控制；數據庫性能的監督、分析和改造；數據庫的重組織和重構造。3.6.2數據庫運行、維護1、需求分析與用戶協商，需要哪些數據和操作（主要是查詢）確定系統中應包含以下實體：書籍、員工、部門和出版社。書籍的屬性確定為：圖書號、分類、書名、作者、單價、數量；員工的屬性確定為：工號、姓名、性別、出生年月；部門的屬性確定為：部門號、部門名稱、電話；出版社的屬性確定為：出版社名、地址、電話、聯系人。其中，每個員工可以借閱多本書，每本書也可以由多個員工借閱，每個員工每借一本書都有一個借閱日期、應還日期和實際還書日期；每個員工只屬于一個部門；每本圖書只能由一個出版社出版。3.7.2圖書借閱管理系統數據庫設計2、概念結構設計畫E-R圖（1）局部E-R圖書籍圖書號書名分類作者出版社地址出版社名電話聯系人出版N1單價數量員工工號姓名性別出生年月部門電話部門名稱屬于M1部門號3.7.2圖書借閱管理系統數據庫設計畫E-R圖（2）全局E-R圖員工工號