《數(shù)據(jù)庫原理及應(yīng)用》數(shù)據(jù)庫虛擬教研室制作數(shù)據(jù)庫目錄數(shù)據(jù)設(shè)計的方法和步驟需求分析概念結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)設(shè)計第九講數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫設(shè)計的方法數(shù)據(jù)庫設(shè)計是指根據(jù)應(yīng)用需求，通過一系列規(guī)范化的步驟，設(shè)計并創(chuàng)建合理有效的數(shù)據(jù)庫結(jié)構(gòu)的過程。目前常用的數(shù)據(jù)庫設(shè)計方法大多源自新奧爾良法。新奧爾良法將數(shù)據(jù)庫設(shè)計分成需求分析、概念結(jié)構(gòu)設(shè)計、邏輯結(jié)構(gòu)設(shè)計和物理結(jié)構(gòu)設(shè)計等幾個階段。在設(shè)計的每一階段采用一些輔助方法來具體實現(xiàn)，主要包括以下兩種：基于E-R模型的數(shù)據(jù)庫設(shè)計方法（概念結(jié)構(gòu)設(shè)計）基于3NF的數(shù)據(jù)庫設(shè)計方法（邏輯結(jié)構(gòu)設(shè)計）數(shù)據(jù)設(shè)計的步驟按照規(guī)范設(shè)計方法，數(shù)據(jù)庫設(shè)計分為6個階段。第1步需要分析需求分析說明書第2步概念結(jié)構(gòu)設(shè)計概念模型：E-R圖第3步邏輯結(jié)構(gòu)設(shè)計邏輯模型：關(guān)系模式第4步物理結(jié)構(gòu)設(shè)計物理模型：物理結(jié)構(gòu)圖第5步數(shù)據(jù)庫的實施創(chuàng)建數(shù)據(jù)庫，編寫應(yīng)用程序第6步數(shù)據(jù)庫的運行與維護評價數(shù)據(jù)庫系統(tǒng)性能并完善分析和設(shè)計階段實現(xiàn)和運行階段需求分析需求分析的任務(wù)：對現(xiàn)實世界要處理的對象（組織、部門、企業(yè)等）進行詳細的調(diào)查，通過對原系統(tǒng)的了解，收集支持新系統(tǒng)的基礎(chǔ)數(shù)據(jù)并對其進行處理，在此基礎(chǔ)上確定新系統(tǒng)的功能，形成需求分析說明書。具體地說，需求分析階段的任務(wù)包括以下三個主要方面：1．調(diào)查分析用戶的活動（1）調(diào)查組織機構(gòu)情況，包括組織機構(gòu)的部門組成情況、各部門的職責(zé)和任務(wù)等。（2）調(diào)查各部門的業(yè)務(wù)活動情況，包括各部門輸入和輸出的數(shù)據(jù)及其格式、所需的表格與卡片、加工處理數(shù)據(jù)的步驟、輸入和輸出的部門等。需求分析2．收集和分析需求數(shù)據(jù)，確定系統(tǒng)邊界在熟悉業(yè)務(wù)活動的基礎(chǔ)上，協(xié)助用戶明確對新系統(tǒng)的各種需求，包括用戶的信息需求、處理需求、安全性和完整性的需求等。（1）信息需求指目標范圍內(nèi)的所有實體、實體的屬性以及實體間的聯(lián)系等數(shù)據(jù)對象，即用戶需要從數(shù)據(jù)庫中獲得信息的內(nèi)容與性質(zhì)。由信息需求可以導(dǎo)出數(shù)據(jù)需求，即在數(shù)據(jù)庫中需要存儲哪些數(shù)據(jù)。（2）處理需求指用戶為了得到需求的信息而對數(shù)據(jù)進行加工處理的需求，包括某種處理功能的響應(yīng)時間、處理的方式（批處理或聯(lián)機處理）等。（3）安全性和完整性的需求。在定義信息需求和處理需求的同時必須相應(yīng)確定安全性和完整性約束。需求分析3．編寫需求分析說明書需求分析階段的最后一項工作是編寫需求分析說明書。編寫需求分析說明書是一個不斷反復(fù)、逐步深入和完善的過程，需求分析說明書通常包括如下內(nèi)容。（1）系統(tǒng)概況，如系統(tǒng)的目標、范圍、背景等。（2）系統(tǒng)架構(gòu)設(shè)計，如前端、后端、數(shù)據(jù)庫和系統(tǒng)部署等。（3）系統(tǒng)功能需求分析，如用戶角色和功能需要等。（4）系統(tǒng)數(shù)據(jù)庫設(shè)計。（5）系統(tǒng)性能需求分析。（6）系統(tǒng)界面設(shè)計。（7）系統(tǒng)的安全性、兼容性、可訪問性、可維護性、可擴展性等。例：學(xué)生選課系統(tǒng)需求分析說明書概念結(jié)構(gòu)設(shè)計是的主要任務(wù)是將需求分析得到的實體及其聯(lián)系轉(zhuǎn)換為信息世界的概念模型。概念模型概念模型是對現(xiàn)實世界中實體以及實體間聯(lián)系的一種抽象和概括模型。在概念結(jié)構(gòu)設(shè)計中，E-R模型是廣泛用于數(shù)據(jù)庫設(shè)計工作的一種概念模型，它通過E-R圖（實體-聯(lián)系圖）來表達實體之間的關(guān)系，E-R圖通過實體、屬性和聯(lián)系三個基本概念來描述數(shù)據(jù)模型。1.基本概念實體：現(xiàn)實世界中可以區(qū)別于其他對象的一個事物或一個對象。實體可以是一個具體對象，如一個學(xué)生、一本書等；實體也可以是抽象的，如開設(shè)的一門課程或預(yù)訂的一趟航班等。實體集：具有相同特征的實體的集合。實體集通常用矩形框表示，框內(nèi)標注實體集名。屬性：實體集中全部實體都具有的共同特征。通常用橢圓表示屬性，并用直線將其與相應(yīng)的實體集相連。聯(lián)系：描述的是不同實體集的實體之間以及同一實體集內(nèi)不同實體之間的關(guān)聯(lián)關(guān)系。在E-R圖中，聯(lián)系通常用菱形框表示，框內(nèi)注明聯(lián)系名，并用直線連接相關(guān)的實體，同時在線上標明聯(lián)系的類型。概念模型概念模型例如，學(xué)生選修課程的E-R圖，如圖所示。在E-R圖中，通常用實體集（矩形框）和屬性（橢圓）組合表示一個實體型，用下劃線表示關(guān)鍵屬性（或主鍵），注意：在很多情況下，為了表述簡潔，人們會把實體集或?qū)嶓w型簡稱為實體。2.實體間聯(lián)系的類型實體之間的聯(lián)系有一對一（1：1）、一對多（1：n）和多對多（m：n）3種類型。概念模型（1）1:1聯(lián)系：若對于實體集A中的每一個實體，實體集B中至多有一個實體與之聯(lián)系，反之亦然，則稱實體集A與實體集B具有一對一聯(lián)系，記為1:1。一對一聯(lián)系通常出現(xiàn)在兩個實體之間。例如，一個學(xué)生只有一張校園卡，一張校園卡只屬于一個學(xué)生。二元實體間1:1聯(lián)系（2）1:n聯(lián)系：若對于實體集A中的每一個實體，實體集B中有n個實體與之聯(lián)系，反之，對于實體集B中的每一個實體，實體集A中至多只有一個實體與之聯(lián)系，則稱實體集A與實體集B具有一對多的聯(lián)系，記為1:n。二元實現(xiàn)間1:n聯(lián)系一元實現(xiàn)間1:n聯(lián)系概念模型（3）m:n聯(lián)系：若對于實體集A中的每一個實體，實體集B中有n個實體與之聯(lián)系，反之亦然，則稱實體集A與實體集B具有多對多的聯(lián)系，記為m:n。二元實現(xiàn)間m:n聯(lián)系多元實現(xiàn)間m:n聯(lián)系概念模型3.強聯(lián)系和弱聯(lián)系強聯(lián)系又稱為完全參與聯(lián)系，即該端實體至少有一個參與聯(lián)系，最小為1，可表示為1..m（最少為1，最多為m）；弱聯(lián)系又稱為部分參與聯(lián)系，即該端實體可以不參與聯(lián)系，最小為0，可表示為0..m（最少為0，最多為m）。概念模型例如，部門與職工之間的聯(lián)系，一個部門有多個職工，一個職工屬于0個或1個部門。實體間發(fā)生聯(lián)系時往往會產(chǎn)生聯(lián)系屬性（也稱作關(guān)聯(lián)屬性），聯(lián)系屬性屬于聯(lián)系，不屬于任何實體，即聯(lián)系動作發(fā)生時該屬性存在，聯(lián)系動作不發(fā)生時該屬性不存在。4.聯(lián)系屬性概念模型例如，學(xué)生選修課程時，學(xué)期、成績、上課時間、上課地點等屬性是在學(xué)生選修課程這個動作發(fā)生的情況下產(chǎn)生的，都稱為聯(lián)系屬性。。復(fù)合屬性是由兩個或多個簡單屬性組成的屬性。復(fù)合屬性通常不直接體現(xiàn)為單獨的橢圓，而是分解為它的原子或簡單屬性，并將這些簡單屬性直接與實體相連。5.復(fù)合屬性概念模型例如，如果一個“家庭住址”是復(fù)合屬性，它可能分解為“城市”、“街道”、“門牌號”等簡單屬性。根據(jù)屬性不可再分原則，圖（a）是錯誤的，圖（b）是正確的。圖（a）圖（b）概念結(jié)構(gòu)設(shè)計概念結(jié)構(gòu)設(shè)計方法可根據(jù)需求分析文檔中數(shù)據(jù)庫系統(tǒng)的復(fù)雜程度，分為兩種設(shè)計方法。（1）對于簡單的數(shù)據(jù)庫系統(tǒng)，可以直接設(shè)計全局E-R模型。（2）對于復(fù)雜的數(shù)據(jù)庫系統(tǒng)，可以先設(shè)計局部E-R模型，再合成全局E-R模型。簡單概念結(jié)構(gòu)設(shè)計的方法簡單概念結(jié)構(gòu)設(shè)計分為兩個步驟。（1）先畫出所有的實體型，明確每個實體的屬性和主鍵。（2）再畫實體之間的聯(lián)系，明確聯(lián)系的類型和聯(lián)系屬性。概念結(jié)構(gòu)設(shè)計【例9.1】：以下是某數(shù)據(jù)庫系統(tǒng)與數(shù)據(jù)庫設(shè)計相關(guān)的語義，請根據(jù)語義進行概念結(jié)構(gòu)設(shè)計，畫出E-R圖。（1）一個倉庫可以存放多種零件，一種零件可以存放在多個倉庫中；

倉庫有倉庫號、倉庫類型和面積，零件有零件號、名稱、

規(guī)格、單價、描述等屬性。（2）一個職工只能在一個倉庫工作，一個倉庫有多個職工當保管員；

職工有職工號、姓名、性別、職務(wù)、出生年月屬性。（3）職工之間有領(lǐng)導(dǎo)與被領(lǐng)導(dǎo)關(guān)系，倉庫主任領(lǐng)導(dǎo)若干保管員；（4）一個供應(yīng)商可以供應(yīng)若干項目多種零件；而一個項目可以使用不同供應(yīng)商供應(yīng)的多種零件；一種零件可由不同供應(yīng)商供給多個工程項目。供應(yīng)商有供應(yīng)商號、姓名、住址、電話、帳號，項目有項目號、預(yù)算、開工日期等屬性。（1）確定實體：實體往往是需求說明書中的名詞概念結(jié)構(gòu)設(shè)計（2）確定實體的屬性及鍵（主屬性）概念結(jié)構(gòu)設(shè)計（3）確定實體的聯(lián)系概念結(jié)構(gòu)設(shè)計（4）確定聯(lián)系的基數(shù)和屬性概念結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計

邏輯結(jié)構(gòu)設(shè)計的任務(wù)是把概念結(jié)構(gòu)設(shè)計階段得到的E-R圖轉(zhuǎn)換為關(guān)系模式。

1.邏輯結(jié)構(gòu)設(shè)計的目標和任務(wù)（1）實體到關(guān)系模式的轉(zhuǎn)換;（2）聯(lián)系到關(guān)系模式的轉(zhuǎn)換。2.設(shè)計方法，分兩步完成實體的名和屬性就是關(guān)系的名和屬性，實體的碼就是關(guān)系的碼。關(guān)系的名和屬性也可以不同，但意義要相同。（1）實體到關(guān)系模式的轉(zhuǎn)換student(sno,sname,sex,age,sdept)1學(xué)生學(xué)號姓名性別年齡專業(yè)邏輯結(jié)構(gòu)設(shè)計1∶1聯(lián)系：由于兩個實體的地位是平等的，可以將任意一端對應(yīng)的關(guān)系模式合并，方法為在一個關(guān)系模式中加入另一個關(guān)系模式的碼。（2）聯(lián)系到關(guān)系模式的轉(zhuǎn)換校園卡（卡號，……）學(xué)生（學(xué)號，……，校園卡號）學(xué)生持有校園卡11校園卡（卡號，……，學(xué)號）學(xué)生（學(xué)號，……）邏輯結(jié)構(gòu)設(shè)計1∶n聯(lián)系：一般將1端實體的碼和聯(lián)系本身的屬性與n端的關(guān)系模式合并。部門（部門號，……）職工（職工號，……，部門號）部門有職工1n邏輯結(jié)構(gòu)設(shè)計職工（職工號，姓名，性別，領(lǐng)導(dǎo)）1∶n聯(lián)系：只有一個實體的1∶n聯(lián)系，該聯(lián)系在轉(zhuǎn)換過程中需要在實體關(guān)系模式的屬性中加入另一個屬性。邏輯結(jié)構(gòu)設(shè)計邏輯設(shè)計項目（項目編號，……）職工（職工號，……）參加（項目編號，職工號，參與時間，……）m∶n聯(lián)系：與該聯(lián)系相連的各實體的碼以及聯(lián)系本身的屬性均轉(zhuǎn)換為關(guān)系的屬性,各實體碼組成關(guān)系的碼或關(guān)系碼的一部分,或定義一個自增型的邏輯主鍵。項目參與職工mn參與時間項目（項目編號，……）職工（職工號，……）參加（id,項目編號，職工號，參與時間，……）

m∶n聯(lián)系供應(yīng)商（供應(yīng)商號，……）項目（項目號，……）零件（零件號，……）訂單（供應(yīng)商號，項目號，零件號，數(shù)量）供應(yīng)商（供應(yīng)商號，……）項目（項目號，……）零件（零件號，……）訂單（id,供應(yīng)商號，項目號，零件號，數(shù)量）邏輯結(jié)構(gòu)設(shè)計【例9.3】將例9.1數(shù)據(jù)庫系統(tǒng)設(shè)計所得到的E-R圖轉(zhuǎn)換為相應(yīng)的關(guān)系模式。邏輯結(jié)構(gòu)設(shè)計（1）將實體轉(zhuǎn)換為關(guān)系模式供應(yīng)商（供應(yīng)商號，姓名，帳號，地址，電話）項目（項目號，預(yù)算，開工日期）零件（零件號，名稱，規(guī)格，單價，描述）職工（職工號，姓名，性別，職務(wù)，出生年月）倉庫（倉庫號，倉庫類型，倉庫面積）邏輯結(jié)構(gòu)設(shè)計（2）將聯(lián)系轉(zhuǎn)換為關(guān)系模式供應(yīng)（id,供應(yīng)商號,項目號,零件號,數(shù)量）存放（零件號，倉庫號，庫存量）職工（職工號，姓名，性別，職務(wù)，倉庫號，領(lǐng)導(dǎo)）邏輯結(jié)構(gòu)設(shè)計（3）合并關(guān)系模式。供應(yīng)商（供應(yīng)商號，姓名，帳號，地址，電話）項目（項目號，預(yù)算，開工日期）零件（零件號，名稱，規(guī)格，單價，描述）倉庫（倉庫號，倉庫類型，倉庫面積）供應(yīng)（id,供應(yīng)商號,項目號,零件號,數(shù)量）存放（id,零件號，倉庫號，庫存量）職工（職工號，姓名，性別，職務(wù)，倉庫號，領(lǐng)導(dǎo)）邏輯結(jié)構(gòu)設(shè)計（4）標出外鍵供應(yīng)商（供應(yīng)商號，姓名，帳號，地址，電話）項目（項目號，預(yù)算，開工日期）零件（零件號，名稱，規(guī)格，單價，描述）職工（職工號，姓名，性別，職務(wù)，出生年月,倉庫號，領(lǐng)導(dǎo)）倉庫（倉庫號，倉庫類型，倉庫面積）供應(yīng)（id，供應(yīng)商號，項目號，零件號，數(shù)量）存放（id,零件號，倉庫號，庫存量）邏輯結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)設(shè)計選取一個最適合應(yīng)用環(huán)境要求的物理結(jié)構(gòu)的過程，稱為數(shù)據(jù)庫的物理設(shè)計。物理設(shè)計過程需要對存取時間、存儲空間利用率和維護代價及各種用戶要求進行權(quán)衡，其結(jié)果可以產(chǎn)生多種方案。在實施數(shù)據(jù)庫前，對這些方案進行細致的評價，以選擇一個較優(yōu)的方案。數(shù)據(jù)庫物理設(shè)計的主要目標是：提高數(shù)據(jù)庫的性能；節(jié)省存儲容量。1.物理結(jié)構(gòu)設(shè)計的目標

（1）確定數(shù)據(jù)庫的物理結(jié)構(gòu)，在RDBMS中主要指存取方法和存儲結(jié)構(gòu)。確定DBMS選型；確定系統(tǒng)架構(gòu)設(shè)計及其環(huán)境配置；確定表結(jié)構(gòu)、索引、視圖、存儲過程、觸發(fā)器、日志和備份等；確定系統(tǒng)的完整性、一致性和安全性等要求。

2.物理結(jié)構(gòu)設(shè)計的步驟物理結(jié)構(gòu)設(shè)計（2）對物理結(jié)構(gòu)進行評價。評價的重點是時間和空間效率，選擇一個較優(yōu)的方案。數(shù)據(jù)庫服務(wù)器部署環(huán)境一般為Linux系統(tǒng)，在Linux系統(tǒng)下默認是區(qū)分大小寫。因此，數(shù)據(jù)庫名、表名、字段名，都使用小寫字母；表名僅僅表示表里面的實體內(nèi)容，不應(yīng)該表示實體數(shù)量，如學(xué)生表用student，而不是students；如果存儲的字符串長度幾乎相等，使用char類型varchar類型長度不要超過5000，如果存儲長度大于此值，定義字段類型為text。小數(shù)類型根據(jù)不同的數(shù)據(jù)庫產(chǎn)品可定義為decimal或number，禁止使用float和double。表必備的附加字段：create_time,update_time，記錄記錄創(chuàng)建和修改時間。業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也必須創(chuàng)建唯一索引。超過三個表禁止join操作，多表關(guān)聯(lián)查詢時，保證被關(guān)聯(lián)的字段需要有索引。在varchar字段上建立索引時，需指定索引長度，沒必要對全字段建立索引。3.物理結(jié)構(gòu)設(shè)計中應(yīng)遵循的一些規(guī)范物理結(jié)構(gòu)設(shè)計當確定好數(shù)據(jù)庫的物理結(jié)構(gòu)后，當數(shù)據(jù)庫中的表比較多且關(guān)系比較復(fù)雜時，最好選擇數(shù)據(jù)庫設(shè)計工具來對數(shù)據(jù)庫進行物理設(shè)計，然后通過正向工程自動生成建庫建表語句；常用的數(shù)據(jù)庫設(shè)計工具有PowerDesigner，MySQLWorkbench等，

Powe