第10講設(shè)計(jì)數(shù)據(jù)完整性

第10講設(shè)計(jì)數(shù)據(jù)完整性大型數(shù)據(jù)庫應(yīng)用（SQLServer2008）本講要點(diǎn)關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述設(shè)計(jì)數(shù)據(jù)完整性數(shù)據(jù)庫表的規(guī)范化123

關(guān)系數(shù)據(jù)庫簡單嚴(yán)謹(jǐn)，四十多年來有了長足發(fā)展，現(xiàn)在已經(jīng)成為事實(shí)上的標(biāo)準(zhǔn)，當(dāng)今幾乎所有的數(shù)據(jù)庫產(chǎn)品都是基于關(guān)系數(shù)據(jù)庫的.

數(shù)據(jù)庫設(shè)計(jì)是數(shù)據(jù)庫應(yīng)用的一個(gè)關(guān)鍵因素,設(shè)計(jì)結(jié)構(gòu)合理、功能齊全的數(shù)據(jù)庫對于提高數(shù)據(jù)庫應(yīng)用程序的開發(fā)效率和程序的性能都是非常重要的.

數(shù)據(jù)庫設(shè)計(jì)是指對于一個(gè)給定的應(yīng)用環(huán)境,構(gòu)造最優(yōu)的數(shù)據(jù)模式，建立數(shù)據(jù)庫，使其能夠有效地存儲(chǔ)數(shù)據(jù)記錄，并能滿足各種應(yīng)用需求.一.關(guān)系數(shù)據(jù)庫設(shè)計(jì)概述

對于一個(gè)數(shù)據(jù)密集型應(yīng)用來說，數(shù)據(jù)庫設(shè)計(jì)是它的一個(gè)核心部分，但對于大型的軟件系統(tǒng)設(shè)計(jì)來說,它只是其中一個(gè)部分.

數(shù)據(jù)庫設(shè)計(jì)的過程可以分為6

步.(1)需求分析

(2)概念數(shù)據(jù)庫設(shè)計(jì)

(3)邏輯數(shù)據(jù)庫設(shè)計(jì)

(4)模式的細(xì)化

(5)物理數(shù)據(jù)庫設(shè)計(jì)

(6)應(yīng)用與安全設(shè)計(jì)

進(jìn)行數(shù)據(jù)庫規(guī)劃是所有數(shù)據(jù)庫編程的第一步,也是最重要的一步.

首先，列出客戶所關(guān)心的所有數(shù)據(jù)和建立這個(gè)數(shù)據(jù)庫的主要目的.這可以通過收集各種相關(guān)的數(shù)據(jù)報(bào)表和表格來完成.需求分析是數(shù)據(jù)庫設(shè)計(jì)的第一階段，不斷的調(diào)查與研究，了解組織機(jī)構(gòu)的情況，了解部門的業(yè)務(wù)流程等系統(tǒng)需求，對于設(shè)計(jì)好數(shù)據(jù)庫是非常重要的.

然后，數(shù)據(jù)表規(guī)劃是整個(gè)數(shù)據(jù)庫設(shè)計(jì)中技巧性最強(qiáng)的一個(gè)步驟.數(shù)據(jù)表要滿足第三范式.數(shù)據(jù)庫規(guī)劃

規(guī)劃數(shù)據(jù)庫有以下三條基本原則：

●數(shù)據(jù)庫中的記錄個(gè)數(shù)應(yīng)該反映現(xiàn)實(shí)世界對象的個(gè)數(shù).如果一個(gè)對象的實(shí)例在現(xiàn)實(shí)世界中存在，則在數(shù)據(jù)庫中有且只有一條記錄存在.

●

每行記錄的字段應(yīng)該表達(dá)現(xiàn)實(shí)世界對象的屬性.

●

現(xiàn)實(shí)世界對象之間的關(guān)系應(yīng)該反映在數(shù)據(jù)庫記錄之間的關(guān)系上.數(shù)據(jù)庫規(guī)劃基本原則

關(guān)系數(shù)據(jù)庫的目的是建立現(xiàn)實(shí)世界的模型.基于這一點(diǎn),用來實(shí)施數(shù)據(jù)完整性的規(guī)則和方法無論對于理論和數(shù)據(jù)庫開發(fā)的實(shí)踐都很重要.

數(shù)據(jù)完整性是指存儲(chǔ)在數(shù)據(jù)庫中的數(shù)據(jù)的正確性和相容性.

設(shè)計(jì)數(shù)據(jù)完整性的目的是為了確保數(shù)據(jù)的質(zhì)量.即防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù),防止錯(cuò)誤信息的輸入和輸出.二.設(shè)計(jì)數(shù)據(jù)完整性

表是存儲(chǔ)數(shù)據(jù)的主要對象,設(shè)計(jì)數(shù)據(jù)庫的關(guān)鍵是設(shè)計(jì)表結(jié)構(gòu).本節(jié)我們將學(xué)習(xí)如何評價(jià)和設(shè)計(jì)好的數(shù)據(jù)庫表，控制數(shù)據(jù)冗余，從而避免數(shù)據(jù)異常，這就是規(guī)范化.

為了識別和鑒賞好的表結(jié)構(gòu)的特性，考察一個(gè)不好的表結(jié)構(gòu)是很有用的.利用這種方法，我們將學(xué)會(huì)如何設(shè)計(jì)好的表結(jié)構(gòu)，如何修改已有的不好的表結(jié)構(gòu).三.數(shù)據(jù)庫表的規(guī)范化

通過規(guī)范化不僅可以消除數(shù)據(jù)異常，而且適當(dāng)規(guī)范化的表結(jié)構(gòu)實(shí)際上要比沒有規(guī)范化的來得簡單，它還能反映出一個(gè)組織的實(shí)際運(yùn)行情況.

擁有好的關(guān)系數(shù)據(jù)庫軟件，并不足以避免數(shù)據(jù)冗余.即使在一個(gè)好的數(shù)據(jù)庫設(shè)計(jì)中,也有可能生成不好的表結(jié)構(gòu).

規(guī)范化

那么，如何識別不好的表結(jié)構(gòu)，如何創(chuàng)建好的表結(jié)構(gòu)呢？兩個(gè)問題的答案都是基于規(guī)范化的.規(guī)范化是給實(shí)體分配屬性的過程.它能夠減少但不能夠消除數(shù)據(jù)冗余；相反，它通過生成有控制的冗余來連接數(shù)據(jù)庫表.規(guī)范化

規(guī)范化通過一系列稱為范式的階段來完成.最前面的三個(gè)階段分別叫做第一范式(1NF)、第二范式(2NF)和第三范式(3NF).

從結(jié)構(gòu)化的觀點(diǎn)來看，2NF比1NF好，3NF比2NF好.對于大多數(shù)的事物數(shù)據(jù)庫設(shè)計(jì)，我們只要能規(guī)范到3NF就已經(jīng)足夠了.

雖然規(guī)范化是數(shù)據(jù)庫設(shè)計(jì)的一個(gè)重要組成部分，但是并不是規(guī)范化級別越高越好.通常,范式級別越高，產(chǎn)生制定輸出所要求的連接就越多，系統(tǒng)對終端用戶請求的響應(yīng)就越慢.三種范式

我們考察某建筑公司的一個(gè)簡單數(shù)據(jù)庫行為.該公司管理幾個(gè)建筑項(xiàng)目，每個(gè)項(xiàng)目都有它自己的項(xiàng)目號、項(xiàng)目名、員工等屬性，每個(gè)員工有員工號、姓名和職位級別等屬性.

公司根據(jù)每個(gè)合同需要花費(fèi)的小時(shí)數(shù)來向客戶收費(fèi).每小時(shí)的報(bào)酬由員工的職位決定.

該應(yīng)用周期性地生成信息報(bào)表.由這個(gè)報(bào)表對應(yīng)地生成一個(gè)表.一個(gè)實(shí)例

這個(gè)表的結(jié)構(gòu)不符合完整性的要求，它也沒有很好的處理數(shù)據(jù).①項(xiàng)目號（PROJ_NUM）很明顯是希望作為一個(gè)主鍵，或者至少是主鍵的一部分，但是它包含空值.②表的數(shù)據(jù)輸入引起數(shù)據(jù)的不一致.如，Elect.Engineer可能寫成Elect.Eng,El.Eng,EE.

③該表中有數(shù)據(jù)冗余，會(huì)產(chǎn)生異常：更新異常:如修改105號的JOB_CLASS.

插入異常，刪除異常.

當(dāng)把某員工分配到某項(xiàng)目時(shí),重復(fù)輸入數(shù)據(jù).一個(gè)實(shí)例

●

消除組重復(fù)在作為主鍵的列中加入合適的項(xiàng)即可.

●主鍵應(yīng)能恰當(dāng)?shù)?、惟一地確定任何屬性值.取主鍵為PROJ_NUM

和EMP_NUM

的組合.

這樣得到了符合第一范式的表.

確定主鍵PROJ_NUM

PROJ_NAME

EMP_NUM

EMP_NAMEJOB_CLASSCHG_HOUR

HOUR部分依賴部分依賴傳遞依賴屬性關(guān)系圖

術(shù)語1NF

描述了如下的表格式：

●定義了所有的鍵屬性.●表中沒有重復(fù)組.●所有的屬性都依賴于主碼.

所有的關(guān)系表都屬于1NF.上圖所示的第一范式的表結(jié)構(gòu)的問題是它包含部分依賴,

也就是說，基于部分主鍵的依賴.

雖然有時(shí)為了提高性能需要使用部分依賴,但要謹(jǐn)慎使用.因?yàn)檫@樣的表仍有數(shù)據(jù)冗余(由每行記錄都要求有數(shù)據(jù)副本而產(chǎn)生),重復(fù)輸入使效率很低，也會(huì)助長數(shù)據(jù)異常的產(chǎn)生.

第一范式

一個(gè)表屬于第二范式是指：

●

它屬于1NF.

●它不包含部分依賴，即，沒有屬性只依賴于主鍵的一部分.

到第二范式的轉(zhuǎn)換是，將原表分為三個(gè)表:

PROJCT(PROJ_NUM,PROJ_NAME)EMPLOYEE(EMP_NUM,EMP_NAME,JOB_CLASS,CHG_HOUR)

ASSIGN(PROJ_NUM,EMP_NUM,ASSIGN_HOUR)第二范式

將三個(gè)表再分出一個(gè)表，消除傳遞依賴:EMPLOYEE(EMP_NUM,EMP_NAME,JOB_CLASS)

JOB(JOB_CLASS,CHG_HOUR)

表屬于第三范式是指：

●它屬于2NF.

●它不包含傳遞依賴.

消除了表結(jié)構(gòu)中的部分依賴和傳遞依賴后,還要集中改善數(shù)據(jù)庫提供信息和增強(qiáng)關(guān)系特性.

第三范式

練習(xí)寫出T-SQL

語句建立下表存放學(xué)生的信息.

表名為：student,含有列：name(char,10位)、id(char,7位)、sex(char,2位)、birthday(datetime,8位)、class(char,10位)birth_place(varchar,50位)，設(shè)置學(xué)號（id）為主鍵約束并帶有聚集索引.

USEpubsGO

CREATEFUNCTIONfun_titles(@vartypevarchar(30))

RETURNS

table

ASRETURN(selecttitle_id,title,type，price

from

titles

where

type=@vartype)GOselect*fromdbo.fun_titles(‘business’)

練習(xí)答案大型數(shù)據(jù)庫應(yīng)用（SQLServer2008）6.4實(shí)驗(yàn)指導(dǎo)實(shí)驗(yàn)內(nèi)容從網(wǎng)上下載示例數(shù)據(jù)庫Northwind，在SQLServe2008中導(dǎo)入Northwind，在“SQLServerManagementStudio”管理窗口中，完成以下實(shí)驗(yàn)內(nèi)容的代碼調(diào)試。1.在Northwind數(shù)據(jù)庫中創(chuàng)建一個(gè)xml_Categories表，并在表中創(chuàng)建一個(gè)XML類型字段，并將XML實(shí)例分配給它。2.分別使用AUTO和RAW模式，查詢前5條客戶訂購產(chǎn)品的信息。3.在Northwind數(shù)據(jù)庫的Customers表中，使用OPENXML語句將查詢合同編號和電話號碼的存放到XML數(shù)據(jù)類型變量@var中。然后，在INSERT語句中使用該變量@varXml中，最后將變量@varXml中的XML數(shù)據(jù)插入新建的表CustomerOutput中的XmlData列中（此列數(shù)據(jù)類型為XML）。4.使用OPENXML語句將