MySQL數(shù)據(jù)庫(kù)教程課件

第2章數(shù)據(jù)庫(kù)設(shè)計(jì)學(xué)習(xí)目標(biāo)了解數(shù)據(jù)庫(kù)設(shè)計(jì)步驟。掌握E-R圖的繪制方法。掌握關(guān)系模式的設(shè)計(jì)方法。了解關(guān)系模式規(guī)范化的相關(guān)范式。2.1數(shù)據(jù)庫(kù)設(shè)計(jì)步驟數(shù)據(jù)庫(kù)設(shè)計(jì)大多采用生命周期法，將整個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)分解為6個(gè)階段。1.需求分析2.概念結(jié)構(gòu)設(shè)計(jì)3.邏輯結(jié)構(gòu)設(shè)計(jì)4.物理結(jié)構(gòu)設(shè)計(jì)5.數(shù)據(jù)庫(kù)實(shí)施6.數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)1.需求分析需求分析是根據(jù)用戶(hù)的需求收集并分析數(shù)據(jù)。通過(guò)調(diào)查和分析用戶(hù)的業(yè)務(wù)活動(dòng)和數(shù)據(jù)使用情況，分析所用數(shù)據(jù)的種類(lèi)、范圍、數(shù)量以及它們?cè)跇I(yè)務(wù)活動(dòng)中的應(yīng)用情況，確定用戶(hù)對(duì)數(shù)據(jù)庫(kù)系統(tǒng)的使用要求和各種約束條件等。2.概念結(jié)構(gòu)設(shè)計(jì)通過(guò)對(duì)需求分析階段收集的數(shù)據(jù)進(jìn)行綜合、歸納與抽象，形成一個(gè)不依賴(lài)于任何數(shù)據(jù)庫(kù)管理系統(tǒng)的概念模型。概念結(jié)構(gòu)設(shè)計(jì)最常用的方法是實(shí)體聯(lián)系法（E-R圖）。3.邏輯結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)的任務(wù)是把概念模型轉(zhuǎn)換為某個(gè)數(shù)據(jù)庫(kù)管理系統(tǒng)支持的邏輯數(shù)據(jù)模型。即將E-R圖轉(zhuǎn)換為所選擇的數(shù)據(jù)庫(kù)管理系統(tǒng)支持的數(shù)據(jù)模式，然后對(duì)其進(jìn)行優(yōu)化。4.物理結(jié)構(gòu)設(shè)計(jì)物理結(jié)構(gòu)設(shè)計(jì)的主要目標(biāo)是為所設(shè)計(jì)的邏輯數(shù)據(jù)模型選擇合適的存儲(chǔ)結(jié)構(gòu)和存取方法，以提高數(shù)據(jù)庫(kù)的訪問(wèn)速度和有效地利用存儲(chǔ)空間。關(guān)系數(shù)據(jù)庫(kù)中已大量屏蔽了數(shù)據(jù)庫(kù)內(nèi)部的存儲(chǔ)結(jié)構(gòu)，因此留給設(shè)計(jì)人員參與物理結(jié)構(gòu)設(shè)計(jì)的任務(wù)很少。5.數(shù)據(jù)庫(kù)實(shí)施在數(shù)據(jù)庫(kù)實(shí)施階段，設(shè)計(jì)人員運(yùn)用數(shù)據(jù)庫(kù)管理系統(tǒng)提供的數(shù)據(jù)庫(kù)語(yǔ)言及工具，根據(jù)邏輯結(jié)構(gòu)設(shè)計(jì)和物理結(jié)構(gòu)設(shè)計(jì)的結(jié)果建立數(shù)據(jù)庫(kù)，編寫(xiě)與調(diào)試應(yīng)用程序，組織數(shù)據(jù)入庫(kù)，并進(jìn)行試運(yùn)行。6.數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)數(shù)據(jù)庫(kù)正式投入運(yùn)行后，就進(jìn)入數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)階段。在數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中，必須不斷地對(duì)其進(jìn)行評(píng)價(jià)、調(diào)整、修改以及備份。

本章主要介紹前3個(gè)階段，即需求分析、概念結(jié)構(gòu)設(shè)計(jì)和邏輯結(jié)構(gòu)設(shè)計(jì)2.2需求分析需求分析是整個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)的基礎(chǔ)。需求分析的充分和準(zhǔn)確程度，決定了在其上構(gòu)建數(shù)據(jù)庫(kù)的速度與質(zhì)量。2.2.1需求分析的任務(wù)和重點(diǎn)1.任務(wù)詳細(xì)調(diào)查現(xiàn)實(shí)世界中要處理的對(duì)象（如組織、部門(mén)、企業(yè)等），充分了解該對(duì)象當(dāng)前數(shù)據(jù)管理系統(tǒng)（手工系統(tǒng)或計(jì)算機(jī)系統(tǒng)）的工作概況，盡可能多地收集數(shù)據(jù)，明確用戶(hù)的各種需求，然后在此基礎(chǔ)上確定新系統(tǒng)的功能。2.重點(diǎn)調(diào)查、收集與分析用戶(hù)在數(shù)據(jù)管理中的信息要求、處理要求、安全性與數(shù)據(jù)完整性要求。2.2.2需求分析的方法1.調(diào)查用戶(hù)的實(shí)際需求2.分析和表達(dá)用戶(hù)需求3.得到用戶(hù)的認(rèn)可1.調(diào)查用戶(hù)的實(shí)際需求調(diào)查用戶(hù)實(shí)際需求的具體步驟如下。①調(diào)查組織機(jī)構(gòu)情況，包括了解該組織的部門(mén)組成情況，以及各個(gè)部門(mén)的職能等。②調(diào)查各個(gè)部門(mén)的業(yè)務(wù)活動(dòng)情況，包括了解各個(gè)部門(mén)輸入和使用什么數(shù)據(jù)、如何處理這些數(shù)據(jù)、輸出什么格式的數(shù)據(jù)、輸出到什么部門(mén)。③協(xié)助用戶(hù)明確對(duì)新系統(tǒng)的各種要求，包括信息要求、處理要求、安全性與數(shù)據(jù)完整性要求。④確定新系統(tǒng)的邊界，確定哪些功能由計(jì)算機(jī)完成或?qū)?lái)準(zhǔn)備讓計(jì)算機(jī)完成、哪些功能由人工完成。2.分析和表達(dá)用戶(hù)需求結(jié)構(gòu)化分析方法是最簡(jiǎn)單、最實(shí)用分析和表達(dá)用戶(hù)需求的方法。結(jié)構(gòu)化分析方法采用自頂向下、逐層分解的方式分析系統(tǒng)，建立系統(tǒng)的處理流程。3.得到用戶(hù)的認(rèn)可對(duì)用戶(hù)需求進(jìn)行分析與表達(dá)后，必須將結(jié)果提交給用戶(hù)，得到用戶(hù)的認(rèn)可。用戶(hù)認(rèn)可后，數(shù)據(jù)庫(kù)中需要存儲(chǔ)的具體數(shù)據(jù)和新系統(tǒng)的功能就確定了。2.3概念結(jié)構(gòu)設(shè)計(jì)概念結(jié)構(gòu)設(shè)計(jì)的任務(wù)是將需求分析階段得到的用戶(hù)需求抽象為概念模型。概念結(jié)構(gòu)設(shè)計(jì)的最終結(jié)果為整個(gè)系統(tǒng)的E-R圖。與需求分析相反，概念結(jié)構(gòu)設(shè)計(jì)采用自底向上、逐層設(shè)計(jì)與合并的方法，先設(shè)計(jì)底層的局部概念結(jié)構(gòu)（局部E-R圖），然后將它們逐層合并起來(lái)，最終形成完整的全局概念結(jié)構(gòu)（全局E-R圖）。2.3.1局部E-R圖設(shè)計(jì)將每個(gè)局部需求所涉及的數(shù)據(jù)抽象為實(shí)體集、屬性以及實(shí)體集之間的聯(lián)系。1.標(biāo)識(shí)實(shí)體集在現(xiàn)實(shí)世界中，一組具有某些共同特性和行為的人、事或物可以抽象為一個(gè)實(shí)體集。例如在學(xué)校環(huán)境中，可以把宋洪博、劉向志、李媛媛等學(xué)生抽象為學(xué)生實(shí)體集。2.標(biāo)識(shí)實(shí)體集的屬性及主鍵屬性即實(shí)體集的特征，主鍵是能夠唯一標(biāo)識(shí)實(shí)體集中某個(gè)實(shí)體的屬性集。例如學(xué)號(hào)、姓名、性別、班級(jí)等可以抽象為學(xué)生實(shí)體集的屬性，其中學(xué)號(hào)是主鍵。3.標(biāo)識(shí)實(shí)體集之間的聯(lián)系如果實(shí)體集之間存在聯(lián)系，要確定聯(lián)系的類(lèi)型是一對(duì)一、一對(duì)多還是多對(duì)多。1.局部E-R圖設(shè)計(jì)【例2-1】設(shè)有如下4個(gè)實(shí)體集及其屬性，其中帶下劃線的屬性為主鍵。學(xué)生（學(xué)號(hào)、姓名、性別、班級(jí)、院系、績(jī)點(diǎn)）課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)、學(xué)分、開(kāi)課院系）教師（教師工號(hào)、姓名、性別、職稱(chēng)、所屬部門(mén)）院系（院系代碼、院系名稱(chēng)、負(fù)責(zé)人）請(qǐng)給出院系學(xué)生選課的局部E-R圖和院系教師講授課程的局部E-R圖。

首先要標(biāo)識(shí)出局部需求中所涉及的實(shí)體集、實(shí)體集的屬性及主鍵，以及實(shí)體集之間的聯(lián)系，然后才能繪制出E-R圖。院系學(xué)生選課的局部E-R圖學(xué)生（學(xué)號(hào)、姓名、性別、班級(jí)、院系、績(jī)點(diǎn)）課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)、學(xué)分、開(kāi)課院系）院系（院系代碼、院系名稱(chēng)、負(fù)責(zé)人）（1）院系和學(xué)生是一對(duì)多聯(lián)系。（2）院系和課程是一對(duì)多聯(lián)系。（3）學(xué)生和課程是多對(duì)多聯(lián)系。院系教師講授課程的局部E-R圖課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)、學(xué)分、開(kāi)課院系）教師（教師工號(hào)、姓名、性別、職稱(chēng)、所屬部門(mén)）院系（院系代碼、院系名稱(chēng)、負(fù)責(zé)人）（1）院系和教師是一對(duì)多聯(lián)系。（2）院系和課程是一對(duì)多聯(lián)系。（3）教師和課程是多對(duì)多聯(lián)系。2.全局E-R圖設(shè)計(jì)各個(gè)局部E-R圖建立好后，需要將它們合并，形成一個(gè)整體的概念模型，即全局E-R圖。全局E-R圖設(shè)計(jì)主要分為合并和優(yōu)化兩個(gè)步驟。（1）合并合并并不是簡(jiǎn)單地將各個(gè)局部E-R圖拼接在一起，而是消除各個(gè)局部E-R圖之間的沖突。E-R圖中的沖突主要有3種：屬性沖突：分為屬性值域沖突和屬性的取值單位沖突。命名沖突：實(shí)體集名、屬性名或聯(lián)系名之間存在的同名異義或異名同義。結(jié)構(gòu)沖突：有3種情況。第1種情況是同一個(gè)對(duì)象，一個(gè)抽象為實(shí)體集，另一個(gè)抽象為屬性；第2種情況是同一個(gè)實(shí)體集的屬性組成不同；第3種情況是實(shí)體集之間的聯(lián)系類(lèi)型不同，一個(gè)是一對(duì)一，另一個(gè)是多對(duì)多。消除合并過(guò)程中的沖突存在命名沖突：“學(xué)生”實(shí)體集中的“院系”、“課程”實(shí)體集中的“開(kāi)課院系”以及“教師”實(shí)體集中的“所屬部門(mén)”實(shí)際上是同樣的含義，即異名同義。存在結(jié)構(gòu)沖突：“學(xué)生”實(shí)體集中的“院系”是屬性，同時(shí)有“院系”實(shí)體集。（2）優(yōu)化優(yōu)化的目的是消除不必要的冗余。冗余的屬性：是指可由基本屬性推導(dǎo)或計(jì)算得到的屬性。冗余的實(shí)體集之間的聯(lián)系：是指可由其他聯(lián)系推導(dǎo)出的聯(lián)系。（2）優(yōu)化冗余的屬性：學(xué)生的“院系”“績(jī)點(diǎn)”；課程的“學(xué)分”“開(kāi)課院系”；教師的“所屬部門(mén)”。冗余的實(shí)體集之間的聯(lián)系：課程與院系之間的“開(kāi)設(shè)”聯(lián)系。合并與優(yōu)化后的全局E-R圖2.4邏輯結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)就是將E-R圖中的各個(gè)實(shí)體集以及實(shí)體集之間的聯(lián)系轉(zhuǎn)換為一組關(guān)系模式。2.4.1關(guān)系模式設(shè)計(jì)將E-R圖中各個(gè)實(shí)體集以及實(shí)體集之間的聯(lián)系轉(zhuǎn)換為一組關(guān)系模式時(shí)，需要遵循以下原則。1.實(shí)體集的轉(zhuǎn)換原則一個(gè)實(shí)體集轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式。實(shí)體集的屬性就是關(guān)系的屬性，實(shí)體集的主鍵就是關(guān)系的主鍵。2.實(shí)體集之間的聯(lián)系的轉(zhuǎn)換原則根據(jù)不同的聯(lián)系類(lèi)型做不同的處理，有5種情況。2.4.1關(guān)系模式設(shè)計(jì)2.實(shí)體集之間的聯(lián)系的轉(zhuǎn)換原則（1）一對(duì)一聯(lián)系：轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，或者與任意一端的關(guān)系模式合并。（2）一對(duì)多聯(lián)系：轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式，或者與n端的關(guān)系模式合并。（3）多對(duì)多聯(lián)系：轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式。（4）3個(gè)或3個(gè)以上實(shí)體集之間的多元聯(lián)系：轉(zhuǎn)換為一個(gè)獨(dú)立的關(guān)系模式。（5）具有相同主鍵的關(guān)系模式可以合并：將其中一個(gè)關(guān)系模式的全部屬性加入另一個(gè)關(guān)系模式中，然后去掉其中的同義屬性。關(guān)系模式設(shè)計(jì)舉例【例2-2】將合并與優(yōu)化后得到的全局E-R圖中各個(gè)實(shí)體集以及實(shí)體集之間的聯(lián)系轉(zhuǎn)換為一組關(guān)系模式。關(guān)系模式設(shè)計(jì)舉例方案1：將4個(gè)實(shí)體集“院系”“學(xué)生”“課程”“教師”和4個(gè)聯(lián)系“擁有”“屬于”“選修”“講授”分別轉(zhuǎn)換成8個(gè)獨(dú)立的關(guān)系模式。院系（院系代碼、院系名稱(chēng)、負(fù)責(zé)人）學(xué)生（學(xué)號(hào)、姓名、性別、班級(jí)）課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)）教師（教師工號(hào)、姓名、性別、職稱(chēng)）屬于（學(xué)號(hào)、院系代碼）擁有（教師工號(hào)、院系代碼）選修成績(jī)（學(xué)號(hào)、課程編號(hào)、成績(jī)）講授安排（班級(jí)、教師工號(hào)、課程編號(hào)）關(guān)系模式設(shè)計(jì)舉例方案2：將“擁有”和“屬于”聯(lián)系分別合并到n端對(duì)應(yīng)的關(guān)系模式中。院系（院系代碼、院系名稱(chēng)、負(fù)責(zé)人）學(xué)生（學(xué)號(hào)、姓名、性別、班級(jí)、院系代碼）課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)）教師（教師工號(hào)、姓名、性別、職稱(chēng)，院系代碼）選修成績(jī)（學(xué)號(hào)、課程編號(hào)、成績(jī)）講授安排（班級(jí)、教師工號(hào)、課程編號(hào)）2.4.2關(guān)系模式的規(guī)范化為了提高數(shù)據(jù)庫(kù)的性能，還應(yīng)該對(duì)關(guān)系模式進(jìn)行優(yōu)化。關(guān)系模式的優(yōu)化通常以關(guān)系數(shù)據(jù)庫(kù)范式理論為指導(dǎo)。按照規(guī)范化的級(jí)別，可以將范式分為5種：第一范式（1NF）第二范式（2NF）第三范式（3NF）第四范式（4NF）第五范式（5NF）在實(shí)際的數(shù)據(jù)庫(kù)設(shè)計(jì)中，通常只需要用到前3種范式。1.第一范式（1NF）1NF要求關(guān)系（數(shù)據(jù)表）中的每一個(gè)屬性值都是不可再分割的數(shù)據(jù)項(xiàng)，即一個(gè)屬性不能有多個(gè)值。2.第二范式（2NF）2NF是在1NF的基礎(chǔ)上建立的。2NF要求數(shù)據(jù)表中的每條數(shù)據(jù)都是唯一的，而且每個(gè)屬性完全依賴(lài)主鍵。完全依賴(lài)主鍵是指不能僅依賴(lài)主鍵中的一部分屬性。如果存在不完全依賴(lài)主鍵的屬性，就應(yīng)該分離出來(lái)形成一個(gè)新的數(shù)據(jù)表，新數(shù)據(jù)表與原數(shù)據(jù)表之間是一對(duì)多聯(lián)系。2.第二范式（2NF）分離為3張表3.第三范式（3NF）3NF是在2NF的基礎(chǔ)上建立的。3NF要求數(shù)據(jù)表不存在非主屬性對(duì)任意主屬性的傳遞函數(shù)依賴(lài)。主屬性是指能夠唯一標(biāo)識(shí)一條記錄的所有屬性。傳遞函數(shù)依賴(lài)是指如果存在主屬性A決定非主屬性B，而非主屬性B決定非主屬性C，則稱(chēng)非主屬性C傳遞函數(shù)依賴(lài)主屬性A。3.第三范式（3NF）分離為2張表3.第三范式（3NF）3NF還要求不在數(shù)據(jù)庫(kù)中存儲(chǔ)可以通過(guò)簡(jiǎn)單計(jì)算得出的數(shù)據(jù)。關(guān)系模式的規(guī)范化舉例【例2-3】檢驗(yàn)例2-2方案1和方案2中的關(guān)系模式是否滿(mǎn)足規(guī)范化要求。從1NF開(kāi)始，逐步進(jìn)行規(guī)范化檢驗(yàn)。（1）判斷是否滿(mǎn)足1NF。由于每個(gè)關(guān)系模式中的所有屬性值都是不可再分割的數(shù)據(jù)項(xiàng)，因此滿(mǎn)足1NF。（2）判斷是否滿(mǎn)足2NF。以“學(xué)生”關(guān)系模式為例，“學(xué)號(hào)”為主鍵，其余的所有屬性都完全依賴(lài)學(xué)號(hào)。其他關(guān)系模式都如此，因此滿(mǎn)足2NF。（3）判斷是否滿(mǎn)足3NF。由于所有模式都不存在非主屬性對(duì)主屬性的傳遞函數(shù)依賴(lài)，因此滿(mǎn)足3NF。綜上，例2-2方案1和方案2中的關(guān)系模式滿(mǎn)足規(guī)范化要求。2.5課堂案例：設(shè)計(jì)學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)1．需求分析（收集數(shù)據(jù)）通過(guò)與學(xué)生和教務(wù)管理人員進(jìn)行交流，得到以下需求。（1）數(shù)據(jù)庫(kù)要存儲(chǔ)每個(gè)學(xué)生的基本信息、各院系的基本信息、任課教師的基本信息、開(kāi)設(shè)課程的基本信息。（2）能夠輸出學(xué)生的成績(jī)單。（3）可以按要求查詢(xún)相關(guān)數(shù)據(jù)，如查詢(xún)某院系的學(xué)生信息、學(xué)生某門(mén)課程的成績(jī)等。（4）可以實(shí)現(xiàn)各種數(shù)據(jù)統(tǒng)計(jì)，如學(xué)生人數(shù)、學(xué)生平均成績(jī)、已修學(xué)分、績(jī)點(diǎn)等。2.5課堂案例：設(shè)計(jì)學(xué)生成績(jī)管理數(shù)據(jù)庫(kù)2．概念結(jié)構(gòu)設(shè)計(jì)（繪制E-R圖）對(duì)需求分析得到的信息進(jìn)行抽象，得出如下系統(tǒng)包含的實(shí)體集及其屬性如下。學(xué)生（學(xué)號(hào)、姓名、性別、出生日期、政治面貌、班級(jí)、院系名稱(chēng)、入學(xué)總分、績(jī)點(diǎn)、獎(jiǎng)懲情況）課程（課程編號(hào)、課程名稱(chēng)、學(xué)時(shí)、學(xué)分、開(kāi)課院系、任課教師）教師（教師工號(hào)、姓名、性