項目5設(shè)計教學(xué)管理數(shù)據(jù)庫數(shù)據(jù)庫設(shè)計步驟、概念設(shè)計、邏輯設(shè)計方法設(shè)計教學(xué)管理數(shù)據(jù)庫5.1了解數(shù)據(jù)庫設(shè)計5.2需求分析5.3概念結(jié)構(gòu)設(shè)計5.4邏輯結(jié)構(gòu)設(shè)計5.5物理結(jié)構(gòu)設(shè)計5.6數(shù)據(jù)庫的實施和維護

什么是數(shù)據(jù)庫設(shè)計？數(shù)據(jù)庫設(shè)計（DatabaseDesign）是指對于一個給定的應(yīng)用環(huán)境，設(shè)計出優(yōu)化的數(shù)據(jù)庫邏輯模式和物理結(jié)構(gòu)，并在此基礎(chǔ)上建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)，使之能夠有效存儲和管理數(shù)據(jù)，滿足各種用戶的應(yīng)用需求，包括信息管理要求和數(shù)據(jù)操作要求。信息管理要求是指在數(shù)據(jù)庫中應(yīng)該存儲和管理哪些數(shù)據(jù)對象；數(shù)據(jù)操作要求是指對數(shù)據(jù)對象需要進行哪些操作，如查詢、增加、刪除、修改、統(tǒng)計等。數(shù)據(jù)庫設(shè)計基本步驟數(shù)據(jù)庫設(shè)計基本步驟需求分析

-信息的收集、確定數(shù)據(jù)概念設(shè)計-繪制ER模型圖

邏輯設(shè)計

-轉(zhuǎn)化成關(guān)系表

物理設(shè)計-建表設(shè)計教學(xué)管理數(shù)據(jù)庫5.1了解數(shù)據(jù)庫設(shè)計5.2需求分析5.3概念結(jié)構(gòu)設(shè)計5.4邏輯結(jié)構(gòu)設(shè)計5.5物理結(jié)構(gòu)設(shè)計5.6數(shù)據(jù)庫的實施和維護

需求分析需求分析簡單地說就是分析用戶的要求。需求分析是設(shè)計數(shù)據(jù)庫的起點，其結(jié)果是否準確，是否反映用戶的實際要求，將直接影響到后面各階段的設(shè)計。

需求分析的任務(wù)是通過詳細調(diào)查現(xiàn)實世界中要處理的對象（組織、部門、企業(yè)等），充分了解原系統(tǒng)（手工系統(tǒng)或者之前的計算機系統(tǒng)）的工作概況，明確用戶的各種需求，然后在此基礎(chǔ)上確定新系統(tǒng)的功能。需求分析

在需求分析階段，設(shè)計人員和用戶需要進行深入的溝通，以避免理解不準確導(dǎo)致后續(xù)的工作出現(xiàn)問題。（1）收集數(shù)據(jù)?？梢岳脭?shù)據(jù)流圖等工具輔助分析與理解。（2）解決沖突。解決沖突包括解決命名沖突、屬性沖突、結(jié)構(gòu)沖突。（3）為數(shù)據(jù)形成一些標準。設(shè)計教學(xué)管理數(shù)據(jù)庫5.1了解數(shù)據(jù)庫設(shè)計5.2需求分析5.3概念結(jié)構(gòu)設(shè)計5.4邏輯結(jié)構(gòu)設(shè)計5.5物理結(jié)構(gòu)設(shè)計5.6數(shù)據(jù)庫的實施和維護

概念模型中的基本概念(1)實體（Entity）客觀存在并可相互區(qū)別的事物稱為實體。實體可以是具體的人、事、物，也可以是抽象的概念或聯(lián)系.(2)屬性（Attribute）

實體所具有的某一特性稱為屬性。一個實體可以由若干個屬性來刻畫。

(3)碼（Key）唯一標識實體的屬性集稱為碼。例如，學(xué)號是學(xué)生實體的碼。概念模型中的基本概念(續(xù))(4)實體型（EntityType）

用實體名及其屬性名集合來抽象和刻畫同類實體稱為實體型。例如，學(xué)生（學(xué)號，姓名，性別，出生年月，所在院系，入學(xué)時間）就是一個實體型。(5)實體集（EntitySet）同一類型實體的集合稱為實體集。例如，全體學(xué)生就是一個實體集。概念模型中的基本概念(續(xù))(6)聯(lián)系（Relationship）

現(xiàn)實世界中事物內(nèi)部以及事物之間的聯(lián)系在信息世界中反映為實體內(nèi)部的聯(lián)系和實體之間的聯(lián)系。實體內(nèi)部的聯(lián)系通常是指組成實體的各屬性之間的聯(lián)系實體之間的聯(lián)系通常是指不同實體集之間的聯(lián)系實體之間的聯(lián)系一對一聯(lián)系（1:1）

實例一個班級只有一個正班長一個班長只在一個班中任職定義：如果對于實體集A中的每一個實體，實體集B中至多有一個（也可以沒有）實體與之聯(lián)系，反之亦然，則稱實體集A與實體集B具有一對一聯(lián)系，記為1:1。班級班級-班長班長111:1聯(lián)系1.兩個實體之間的聯(lián)系實體之間的聯(lián)系(續(xù))一對多聯(lián)系（1：n）實例一個班級中有若干名學(xué)生，每個學(xué)生只在一個班級中學(xué)習(xí)定義：如果對于實體集A中的每一個實體，在實體集B中有n個實體（n≥0）與之聯(lián)系，反之，對于實體集B中的每一個實體，實體集A中至多只有一個實體與之聯(lián)系，則稱實體集A與實體集B有一對多聯(lián)系，記為1:n班級組成學(xué)生1n1:n聯(lián)系實體之間的聯(lián)系(續(xù))多對多聯(lián)系（m:n）實例課程與學(xué)生之間的聯(lián)系：一門課程同時有若干個學(xué)生選修一個學(xué)生可以同時選修多門課程定義：如果對于實體集A中的每一個實體，實體集B中有n個實體（n≥0）與之聯(lián)系，反之，對于實體集B中的每一個實體，實體集A中也有m個實體（m≥0）與之聯(lián)系，則稱實體集A與實體B具有多對多聯(lián)系，記為m:n課程選修學(xué)生mnm:n聯(lián)系實體之間的聯(lián)系(續(xù))實例：課程、教師與參考書三個實體型，一門課程可以有若干個教師講授，使用若干本參考書，每一個教師只講授一門課程，每一本參考書只供一門課程使用。課程講授教師1m兩個以上實體型間1:n聯(lián)系參考書n2.兩個以上實體之間的聯(lián)系一般的，兩個以上實體之間也存在一對一、一對多、多對多聯(lián)系。實體之間的聯(lián)系(續(xù))兩個以上實體型間的多對多聯(lián)系實例

供應(yīng)商、項目、零件三個實體型一個供應(yīng)商可以供給多個項目多種零件，每個項目可以使用多個供應(yīng)商供應(yīng)的零件，每種零件可由不同供應(yīng)商供給供應(yīng)商供應(yīng)項目mp兩個以上實體型間m:n聯(lián)系零件n一對多聯(lián)系實例

職工實體型內(nèi)部具有領(lǐng)導(dǎo)與被領(lǐng)導(dǎo)的聯(lián)系某一職工（干部）“領(lǐng)導(dǎo)”若干名職工一個職工僅被另外一個職工直接領(lǐng)導(dǎo)這是一對多的聯(lián)系職工領(lǐng)導(dǎo)1n單個實體型內(nèi)部1:n聯(lián)系3.單個實體內(nèi)的聯(lián)系實體之間的聯(lián)系(續(xù))同一個實體集內(nèi)的各實體之間也可以存在一對一、一對多、多對多聯(lián)系。

E-R圖E-R圖提供了表示實體、屬性和聯(lián)系的方法用E-R圖來描述現(xiàn)實世界的概念模型E-R圖也稱為E-R模型表示方法：（1）實體用矩形表示，矩形框內(nèi)寫明實體名。

（2）屬性用橢圓形表示，并用無向邊將其與相應(yīng)的實體連接起來。

（3）聯(lián)系用菱形表示，菱形框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實體連接起來，同時在無向邊旁標上聯(lián)系的類型（1∶1、1∶n或m∶n）。E-R

圖實體型用矩形表示，矩形框內(nèi)寫明實體名。屬性用橢圓形表示，并用無向邊將其與相應(yīng)的實體連接起來學(xué)生教師E-R圖(續(xù))聯(lián)系

用菱形表示，菱形框內(nèi)寫明聯(lián)系名，并用無向邊分別與有關(guān)實體連接起來，同時在無向邊旁標上聯(lián)系的類型（1:1、1:n或m:n）班長屬于班級111:1聯(lián)系課程選修學(xué)生mnm:n聯(lián)系班級擁有學(xué)生1n1:n聯(lián)系聯(lián)系的屬性聯(lián)系的屬性：聯(lián)系本身也是一種實體型，也可以有屬性。如果一個聯(lián)系具有屬性，則這些屬性也要用無向邊與該聯(lián)系連接起來E-R圖

【例5.1】根據(jù)用戶需求信息設(shè)計E-R圖。

某網(wǎng)上商城系統(tǒng)在進行數(shù)據(jù)庫設(shè)計時，收集到的用戶需求包括：網(wǎng)站可以進行用戶的注冊，注冊需要填寫用戶名、密碼、E-mail、手機號、真實姓名等信息；每一個用戶可以有多個派送地址，每個用戶在下單時可以選擇相應(yīng)的派送地址；商品信息包括商品ID、商品名稱、價格、庫存數(shù)量、生產(chǎn)廠家等信息；用戶可以直接購買多種商品，同時下單；每個訂單可以有訂單詳情，用于記錄用戶所購買的商品信息、數(shù)量等信息；此外，用戶隨著消費累計金額的增長而屬于不同的等級，不同的用戶等級決定用戶享受的折扣。E-R圖概念結(jié)構(gòu)設(shè)計過程1．實體和屬性的劃分原則為了簡化E-R圖，現(xiàn)實世界中的事物能作為屬性對待的盡量作為屬性對待。符合以下條件的事物一般作為屬性對待。（1）作為屬性，不能有需要描述的性質(zhì)，即屬性必須是不可分的數(shù)據(jù)項，不能包含其他屬性。（2）屬性不能與其他實體具有聯(lián)系，即E-R圖所表示的聯(lián)系是實體之間的聯(lián)系。概念結(jié)構(gòu)設(shè)計過程2．E-R圖的集成

在開發(fā)一個大型信息系統(tǒng)時，最經(jīng)常采用的策略是自頂向下進行需求分析，再自底向上設(shè)計概念結(jié)構(gòu)。也就是首先設(shè)計各子系統(tǒng)的分E-R圖，然后將它們集成起來，得到全局E-R圖。E-R圖的集成一般需要分為以下兩步。（1）合并。解決各分E-R圖之間的沖突，將分E-R圖合并起來生成初步E-R圖。（2）修改和重構(gòu)。消除不必要的冗余，生成基本E-R圖。設(shè)計教學(xué)管理數(shù)據(jù)庫5.1了解數(shù)據(jù)庫設(shè)計5.2需求分析5.3概念結(jié)構(gòu)設(shè)計5.4邏輯結(jié)構(gòu)設(shè)計5.5物理結(jié)構(gòu)設(shè)計5.6數(shù)據(jù)庫的實施和維護

5.4.1關(guān)系模型

邏輯結(jié)構(gòu)模型是指數(shù)據(jù)庫中數(shù)據(jù)的組織形式和聯(lián)系方式，簡稱數(shù)據(jù)模型?，F(xiàn)有的數(shù)據(jù)庫管理系統(tǒng)都基于某種數(shù)據(jù)模型。按照數(shù)據(jù)庫中數(shù)據(jù)采取的不同聯(lián)系方式，數(shù)據(jù)模型分為層次模型、網(wǎng)狀模型和關(guān)系模型。

關(guān)系模型是目前最重要的一種數(shù)據(jù)模型。1970年，美國IBM公司的研究員埃德加·弗蘭克·科德（EdgarFrankCodd，E.F.Codd），首次提出了數(shù)據(jù)庫系統(tǒng)的關(guān)系模型。

從用戶觀點看，關(guān)系模型由一組關(guān)系組成，每個關(guān)系的數(shù)據(jù)結(jié)構(gòu)是一張規(guī)范化的二維表。5.4.1關(guān)系模型1.關(guān)系術(shù)語

（1）關(guān)系。一個關(guān)系就是一個二維表，都有一個關(guān)系名。（2）記錄（元組）。表中的一行即為一個記錄，又稱元組。（3）字段（屬性）。表中的一列即為一個字段，又稱屬性。（4）域。域就是屬性的取值范圍，即不同記錄對同一個屬性的取值予以限定的范圍。例如，“性別”屬性的域為（男,女），“成績”屬性的域是0~100。（5）碼（key）。碼也稱為關(guān)鍵字，是表中的一個屬性或?qū)傩越M，它的值可以唯一確定一個元組。例如，表5-1中的學(xué)號可以唯一確定一個學(xué)生，因此成為學(xué)生關(guān)系的碼。（6）關(guān)系模式。對關(guān)系的描述稱為關(guān)系模式，一般表示如下。關(guān)系名（屬性1,屬性2,…,屬性n）一個關(guān)系模型通常是若干個相聯(lián)系的關(guān)系模式的集合。5.4.1關(guān)系模型2.規(guī)范化的關(guān)系

關(guān)系模型要求關(guān)系必須是規(guī)范化的，即要求關(guān)系必須滿足一定的規(guī)范條件，這些規(guī)范條件有以下幾點。（1）關(guān)系的每個屬性都是不可分割的數(shù)據(jù)單元，即屬性不能再分，不允許“表中有表”。（2）在同一個關(guān)系中不能出現(xiàn)相同的屬性名。（3）任意交換列的次序，不會改變關(guān)系的實際意義。（4）關(guān)系中的行又稱為元組，代表一個實體，因此關(guān)系中不存在完全相同的兩行。（5）任意交換行的次序，不會改變關(guān)系的實際意義。5.4.2E-R圖轉(zhuǎn)換成關(guān)系模型1.轉(zhuǎn)換規(guī)則

（1）一個實體轉(zhuǎn)換為一個關(guān)系模式，關(guān)系的屬性就是實體的屬性，關(guān)系的碼就是實體的碼。（2）一個1∶1聯(lián)系可以與任意一端對應(yīng)的關(guān)系模式合并，合并時需要在該關(guān)系模式的屬性中加入另一個關(guān)系模式的碼和聯(lián)系本身的屬性。（3）一個1∶n聯(lián)系通常與n端對應(yīng)的關(guān)系模式合并，即在n端關(guān)系中加入1端關(guān)系的碼和聯(lián)系本身的屬性，其中，1端關(guān)系的碼加入n端關(guān)系后應(yīng)作為關(guān)系的外碼。（4）一個m∶n聯(lián)系將轉(zhuǎn)換為一個關(guān)系模式，與該聯(lián)系相連的各實體的碼及聯(lián)系本身的屬性均轉(zhuǎn)換為關(guān)系的屬性，各實體的碼組成關(guān)系的碼或關(guān)系碼的一部分。5.4.2E-R圖轉(zhuǎn)換成關(guān)系模型1.轉(zhuǎn)換規(guī)則

（5）3個或3個以上實體間的一個多元聯(lián)系可以轉(zhuǎn)換為一個關(guān)系模式。與該多元聯(lián)系相連的各實體的碼及聯(lián)系本身的屬性均轉(zhuǎn)換為關(guān)系的屬性，各實體的碼組成關(guān)系的碼或關(guān)系碼的一部分。（6）同一實體集的實體間的聯(lián)系，即實體內(nèi)部的聯(lián)系，可參照實體間的聯(lián)系1∶1、1∶n、m∶n3種情況分別處理。（7）具有相同碼的關(guān)系模式可合并。合并方法是將其中一個關(guān)系模式的全部屬性加入另一個關(guān)系模式，去掉其中的同義屬性，調(diào)整屬性的次序。5.4.2E-R圖轉(zhuǎn)換成關(guān)系模型【例5.2】繼續(xù)設(shè)計例5.1中的網(wǎng)上購物系統(tǒng)數(shù)據(jù)庫，將圖5-8所示的E-R圖轉(zhuǎn)化為關(guān)系模型。

用戶（用戶名，密碼，E-mail，手機號，真實姓名，消費金額，等級）。商品（商品id，商品名稱，價格，庫存數(shù)量，生產(chǎn)廠家）。地址（地址id，地址名稱，用戶名）。訂單（訂單id，用戶名，訂單金額，下單時間，郵寄地址，是否付款）。訂單詳情（詳情id，訂單id，商品id，購買數(shù)量）。經(jīng)過分析，轉(zhuǎn)換后的關(guān)系模型如下：5.4.3關(guān)系模型的優(yōu)化

在數(shù)據(jù)庫邏輯設(shè)計過程中，為了避免不規(guī)范的數(shù)據(jù)庫出現(xiàn)數(shù)據(jù)冗余，造成插入、刪除、更新等操作異常情況，需要滿足一定的規(guī)范化要求，這就是范式（NormalForm）。

根據(jù)要求的程度不同，范式有多種級別，常用的有第一范式（1NF）、第二范式（2NF）和第三范式（3NF），它們由E.F.Codd于1971年相繼提出。后來又有人提出了BC范式（Boyce_x0002_CoddNormalForm，BCNF）、第四范式（4NF）和第五范式（5NF）等。一般來說，數(shù)據(jù)庫設(shè)計只需滿足第三范式即可。5.4.3關(guān)系模型的優(yōu)化1.第一范式（1NF）

第一范式（1NF）是指數(shù)據(jù)表的每一列都是不可分割的基本數(shù)據(jù)項，同一列中不能有多個值，即實體中的某個屬性不能有多個值，或不能有重復(fù)的屬性。第一范式遵從原子性，即屬性不可再分。學(xué)

號聯(lián)系方式101

李偉

郵箱：liwei@，手機號：18900000000102

張?zhí)m

郵箱：zhanglan@，手機號17300000000學(xué)

號姓名郵箱手機號手機號101李偉liwei/p>

102張?zhí)mzhanglan@15900000000173000000005.4.3關(guān)系模型的優(yōu)化2.第二范式（2NF）

第二范式是在第一范式的基礎(chǔ)上建立起來的，滿足第二范式必須先滿足第一范式。第二范式要求實體的屬性完全依賴于主鍵，不能僅依賴主鍵的一部分（對于復(fù)合主鍵而言）。簡而言之，第二范式遵從唯一性，非主鍵字段需完全依賴主鍵。學(xué)號姓名性別專業(yè)所屬院系院系地址課程號成績101李偉男計算機應(yīng)用信息工程系篤信樓C0185101李偉男計算機應(yīng)用信息工程系篤信樓C0278102張?zhí)m女軟件技術(shù)信息工程系篤信樓C0290103王艷女數(shù)控技術(shù)機電工程系智造樓C0265學(xué)號和課程號組成了復(fù)合主鍵，成績完全依賴于復(fù)合主鍵，而姓名、性別和專業(yè)等學(xué)生信息只依賴于學(xué)號。5.4.3關(guān)系模型的優(yōu)化2.第二范式（2NF）

學(xué)號姓名性別專業(yè)所屬院系院系地址101李偉男計算機應(yīng)用信息工程系篤信樓101李偉男計算機應(yīng)用信息工程系篤信樓102張?zhí)m女軟件技術(shù)信息工程系篤信樓103王艷女數(shù)控技術(shù)機電工程系智造樓學(xué)號課程號成績101C0185101C0278102C0290103C02655.4.3關(guān)系模型的優(yōu)化3.第三范式（3NF）

第三范式（3NF）是在第二范式的基礎(chǔ)上建立起來的，即滿足第三范式必須先滿足第二范式。第三范式要求一個數(shù)據(jù)表的每一列數(shù)據(jù)都和主鍵直接相關(guān)，而不能間接相關(guān)。

簡而言之，第三范式就是非主鍵字段不能相互依賴。學(xué)號姓名性別專業(yè)所屬院系院系地址101李偉男計算機應(yīng)用信息工程系篤信樓101李偉男計算機應(yīng)用信息工程系篤信樓102張?zhí)m女軟件技術(shù)信息工程系篤信樓103王艷女數(shù)控技術(shù)機電工程系智造樓同一院系的地址在同一個地方，所以院系地址依賴于所屬院系，與主鍵學(xué)號不直接相關(guān)，而是間接相關(guān)5.4.3關(guān)系模型的優(yōu)化3.第三范式（3NF）

學(xué)號姓名性別專業(yè)101李偉男計算機應(yīng)用101李偉男計算機應(yīng)用102張?zhí)m女軟件技術(shù)103王艷女數(shù)控技術(shù)專業(yè)所屬院系計算機應(yīng)用信息工程系軟件技術(shù)信息工程系數(shù)控技術(shù)機電工程系院系名稱院系地址信息工程系篤信樓機電工程系智造樓拆分后的學(xué)生表、專業(yè)表和院系表都滿足第三范式。設(shè)計教學(xué)管理數(shù)據(jù)庫5.1了解數(shù)據(jù)庫設(shè)計5.2需求分析5.3概念結(jié)構(gòu)設(shè)計5.4邏輯結(jié)構(gòu)設(shè)計5.5物理結(jié)構(gòu)設(shè)計5.6數(shù)據(jù)庫的實施和維護

5.5物理結(jié)構(gòu)設(shè)計

物理結(jié)構(gòu)設(shè)計階段的任務(wù)是把邏輯設(shè)計階段得到的數(shù)據(jù)庫邏輯在物理上加以實現(xiàn)，其主要內(nèi)容是根據(jù)DBMS提供的各種手段，設(shè)計數(shù)據(jù)的存儲形式和存取路徑，如文件結(jié)構(gòu)、數(shù)據(jù)類型、索引設(shè)計等，即設(shè)計數(shù)據(jù)庫的內(nèi)模式或存儲模式。一.索引存取方法B-Tree索引和Hash索引是數(shù)據(jù)庫中經(jīng)典的存取方法。1.使用B-Tree索引規(guī)則一般來說，符合下列條件可以使用B-Tree索引。（1）如果一個（或一組）字段經(jīng)常在查詢條件中出現(xiàn)，則考慮在這個（或這組）字段上建立索引（或組合索引）。（2）如果一個字段經(jīng)常作為最大值和最小值等聚集函數(shù)的參數(shù)，則考慮在這個字段上建立索引。（3）如果一個（或一組）字段經(jīng)常在連接操作的連接條件中出現(xiàn)，則考慮在這個（或這組）屬性上建立索引。一.索引存取方法

5.5物理結(jié)構(gòu)設(shè)計2．使用Hash索引規(guī)則選擇Hash索引的規(guī)則如下。如果一個表的字段主要出現(xiàn)在等值連接條件或等值比較選擇條件中，且滿足下列兩個條件之一，則此表可以選擇Hash索引。（1）一個表的大小可預(yù)知，而且不變。（2）表的大小動態(tài)改變，但DBMS提供了動態(tài)Hash存取方法。MySQL數(shù)據(jù)庫目前僅有MEMORY存儲引擎和HEAP存儲引擎支持這類索引。其中，MEMORY存儲引擎支持B-Tree索引和Hash索引，且將Hash索引當成默認索引。二、MySQL存儲引擎

5.5物理結(jié)構(gòu)設(shè)計數(shù)據(jù)庫的存儲引擎決定了表在計算機中的存儲方式。存儲引擎就是存儲數(shù)據(jù)、為存儲的數(shù)據(jù)建立索引和更新、查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方法。MySQL8.0支持的存儲引擎有MEMORY、MRG_MYISAM、CSV、FEDERATED、PERFORMANCE_SCHEMA、MyISAM、In