第9講 數(shù)據(jù)庫設(shè)計《數(shù)據(jù)庫原理及應(yīng)用》教學課件

《數(shù)據(jù)庫原理及應(yīng)用》?精品課件合集目錄數(shù)據(jù)建模需求分析概念結(jié)構(gòu)設(shè)計邏輯結(jié)構(gòu)設(shè)計物理結(jié)構(gòu)設(shè)計第九講數(shù)據(jù)庫設(shè)計數(shù)據(jù)建模概念一級抽象二級抽象實體概念模型關(guān)系模型學生課程學習mn現(xiàn)實世界信息世界計算機世界把現(xiàn)實世界中的對象（實體）特征抽象出來，使用計算機進行描述，這個過程稱為數(shù)據(jù)建模，需要對三個世界（現(xiàn)實世界、信息世界、計算機世界）進行兩次抽象。1.現(xiàn)實世界的個體：個體是一個實際存在的且可以被識別的事物。

可以是可接觸的具體事物，如學生；

也可以是不可接觸的抽象概念，如課程；

個體之間往往存在一定的關(guān)聯(lián)關(guān)系。學生課程數(shù)據(jù)建模概念2.信息世界的概念模型實體(Entity)：即現(xiàn)實世界的個體，在信息世界一般面對的是個體的集合，稱為實體集。屬性：實體集共有的某一特征。聯(lián)系(Relationship)：實體集內(nèi)部以及實體集之間的相互關(guān)系。數(shù)據(jù)建模概念3.機器世界中的關(guān)系模型關(guān)系模式：是由概念模型轉(zhuǎn)換得到的邏輯模型。關(guān)系表：是由關(guān)系模式+數(shù)據(jù)的定義+完整性約束，即物理模型。關(guān)系模式：學生(學號，姓名，專業(yè)名，性別，出生日期，總學分，備注）課程（課程號，課程名，開課學期，學時，學分）成績（學號，課程號，成績）數(shù)據(jù)建模過程數(shù)據(jù)設(shè)計概述數(shù)據(jù)庫設(shè)計是指對于一個給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫模式，建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)，使之能夠有效地存儲數(shù)據(jù)，滿足各種用戶的應(yīng)用需求。共分四步。第1步需要分析需求分析說明書第2步概念結(jié)構(gòu)設(shè)計概念模型：E-R圖第3步邏輯結(jié)構(gòu)設(shè)計邏輯模型：關(guān)系模式第4步物理結(jié)構(gòu)設(shè)計物理模型：建表語句需求分析需求分析的目標和任務(wù)：對某數(shù)據(jù)庫系統(tǒng)的整個應(yīng)用情況作全面的詳細調(diào)查，確定企業(yè)組織的目標，收集支持系統(tǒng)總的設(shè)計目標的基礎(chǔ)數(shù)據(jù)和對這些數(shù)據(jù)的要求，確定用戶的需求，并把這些要求寫成用戶和數(shù)據(jù)庫設(shè)計者都能接受的文檔——需求分析說明書。需求分析方法：對數(shù)據(jù)庫的需求分析往往是嵌套在對數(shù)據(jù)庫系統(tǒng)功能的需求分析中，但也可以把對數(shù)據(jù)庫的需求分析從中抽取出來，單獨進行分析與設(shè)計。需求分析（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ù)算、開工日期等屬性。例：以下是從某數(shù)據(jù)庫系統(tǒng)需要說明書中抽取出來與數(shù)據(jù)庫設(shè)計相關(guān)的語義，根據(jù)語義完成概念結(jié)構(gòu)設(shè)計。所謂模型就是從特定角度對客觀事物及其聯(lián)系的一種簡化抽象和描述。概念模型信息世界中,對客觀實體及其聯(lián)系的描述，建立概念模型的工具很多，其中比較常用的是E-R模型，即實體關(guān)系模型（Entity-RelationshipModel）。概念模型1.E-R模型1976年，由美籍華裔計算機科學家陳品山(PeterChen)發(fā)明，這種數(shù)據(jù)模型典型的用在信息系統(tǒng)設(shè)計的第一階段。E-R模型是建立在語義基礎(chǔ)上的，即語義制造模型，即與時間/歷史等有關(guān)。E-R模型的基本觀點：世界是由一組稱作實體的基本對象和這些對象之間的聯(lián)系構(gòu)成的。概念結(jié)構(gòu)設(shè)計就是根據(jù)需求分析文檔畫出E-R圖。概念模型2.E-R圖中的符號約定實體名長方形：表示實體，內(nèi)注實體名；聯(lián)系名屬性名橢圓：表示實體的屬性，內(nèi)注屬性名，主碼屬性加下劃線；

無向邊：表示將實體與相關(guān)的屬性和聯(lián)系連接。菱形：表示實體間的聯(lián)系，內(nèi)注聯(lián)系名，一般用為動詞；概念模型3.實體間聯(lián)系的類型一元聯(lián)系：同一實體集合內(nèi)的實體間的聯(lián)系如：班長管理學生；領(lǐng)導(dǎo)管理員工；一個零件由多個零件組成。二元聯(lián)系：兩個不同實體集合的實體間聯(lián)系如：學生選修課程；學生借閱圖書。

多元聯(lián)系：兩個以上不同實體集合的實體間的聯(lián)系如：某工程項目需要多個供應(yīng)商提供多種零件概念模型4.實體間聯(lián)系的基數(shù)（1）1:1聯(lián)系：若對于實體集A中的每一個實體，實體集B中至多有一個實體與之聯(lián)系，反之亦然，則稱實體集A與實體集B具有一對一聯(lián)系，記為1:1。概念模型（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)系概念模型實體間發(fā)生聯(lián)系時往往會產(chǎn)生中間屬性，中間屬性屬于聯(lián)系，不屬于任何實體。5.聯(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學生學號姓名性別年齡專業(yè)邏輯結(jié)構(gòu)設(shè)計1∶1聯(lián)系：由于兩個實體的地位是平等的，可以將任意一端對應(yīng)的關(guān)系模式合并，方法為在一個關(guān)系模式中加入另一個關(guān)系模式的碼。（2）聯(lián)系到關(guān)系模式的轉(zhuǎn)換工資卡（卡號，……）員工（員工號，……，工資卡號）員工持有工資卡11工資卡（卡號，……，員工號）員工（員工號，……）邏輯結(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ù)量）存放（零件號，倉庫號，庫存量）職工（職工號，姓名，性別，職務(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ù)量）存放（零件號，倉庫號，庫存量）邏輯結(jié)構(gòu)設(shè)計業(yè)務(wù)主鍵：把具有業(yè)務(wù)邏輯含義的字段作為主鍵，如學號，課程號。邏輯主鍵：把一個與當前表中信息無關(guān)的字段作為其主鍵，如自增整型。（1）使用邏輯主鍵的主要原因有：業(yè)務(wù)主鍵一旦改變，則系統(tǒng)中關(guān)聯(lián)該主鍵部分的修改將是不可避免的，最典型的例子就是身份證升位和駕駛執(zhí)照號換用身份證號的業(yè)務(wù)變更。業(yè)務(wù)主鍵過大，不利于傳輸、處理和存儲。一般如果業(yè)務(wù)主鍵超過8字節(jié)就應(yīng)該考慮使用邏輯主鍵。使用邏輯主鍵替代多對多關(guān)系中的復(fù)合主鍵，因為復(fù)合主鍵處理效率非常低，而邏輯主鍵的字節(jié)少，處理效率都很高。關(guān)于業(yè)務(wù)主鍵與邏輯主鍵的使用原則邏輯結(jié)構(gòu)設(shè)計有些業(yè)務(wù)主鍵中的字符串是有特殊含義的，如學號中往往包含入學年份、學院、專業(yè)等信息，此時不能使用邏輯主鍵替代業(yè)務(wù)主鍵；增加邏輯主鍵就是增加了一個業(yè)務(wù)無關(guān)的字段，而用戶通常都是對于業(yè)務(wù)相關(guān)的字段進行查找（比如員工的工號，學生的學號），這樣我們還必須為這些業(yè)務(wù)字段加索引，增加了系統(tǒng)的開銷。對于如銀行系統(tǒng)，其安全性比性能更加重要，這時候就會考慮使用業(yè)務(wù)主鍵，業(yè)務(wù)主鍵需要在重要的表中有冗余存在，例如身份證號、存折號、卡號等。（2）使用業(yè)務(wù)主鍵的主要原因有：邏輯結(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ù)量，如學生表用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操作，多表