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

1、第第 10講 設(shè)計數(shù)據(jù)完整性設(shè)計數(shù)據(jù)完整性大型大型數(shù)數(shù)據(jù)據(jù)庫應(yīng)庫應(yīng)用（用（SQL Server 2008）本講要點本講要點關(guān)系數(shù)據(jù)庫設(shè)計概述關(guān)系數(shù)據(jù)庫設(shè)計概述設(shè)計數(shù)據(jù)完整性設(shè)計數(shù)據(jù)完整性數(shù)據(jù)庫表的規(guī)范化數(shù)據(jù)庫表的規(guī)范化 123 關(guān)系數(shù)據(jù)庫簡單嚴謹，四十多年來有了長關(guān)系數(shù)據(jù)庫簡單嚴謹，四十多年來有了長足發(fā)展，現(xiàn)在已經(jīng)成為事實上的標(biāo)準(zhǔn)，當(dāng)今幾足發(fā)展，現(xiàn)在已經(jīng)成為事實上的標(biāo)準(zhǔn)，當(dāng)今幾乎所有的數(shù)據(jù)庫產(chǎn)品都是基于關(guān)系數(shù)據(jù)庫的乎所有的數(shù)據(jù)庫產(chǎn)品都是基于關(guān)系數(shù)據(jù)庫的. 數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫設(shè)計是數(shù)據(jù)庫應(yīng)用的一個關(guān)鍵因素是數(shù)據(jù)庫應(yīng)用的一個關(guān)鍵因素,設(shè)計結(jié)構(gòu)合理、功能齊全的數(shù)據(jù)庫對于提高數(shù)設(shè)計結(jié)構(gòu)合理、功能齊全的數(shù)

2、據(jù)庫對于提高數(shù)據(jù)庫應(yīng)用程序的開發(fā)效率和程序的性能都是非據(jù)庫應(yīng)用程序的開發(fā)效率和程序的性能都是非常重要的常重要的. 數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫設(shè)計是指對于一個給定的應(yīng)用環(huán)境是指對于一個給定的應(yīng)用環(huán)境,構(gòu)造最優(yōu)的數(shù)據(jù)模式，建立數(shù)據(jù)庫，使其能夠構(gòu)造最優(yōu)的數(shù)據(jù)模式，建立數(shù)據(jù)庫，使其能夠有效地存儲數(shù)據(jù)記錄，并能滿足各種應(yīng)用需求有效地存儲數(shù)據(jù)記錄，并能滿足各種應(yīng)用需求.一一. . 關(guān)系數(shù)據(jù)庫設(shè)計概述關(guān)系數(shù)據(jù)庫設(shè)計概述 對于一個數(shù)據(jù)密集型應(yīng)用來說，數(shù)據(jù)庫設(shè)對于一個數(shù)據(jù)密集型應(yīng)用來說，數(shù)據(jù)庫設(shè)計是它的一個計是它的一個核心部分核心部分，但對于大型的軟件系，但對于大型的軟件系統(tǒng)設(shè)計來說統(tǒng)設(shè)計來說, 它只是其中一個部分它只是

3、其中一個部分. 數(shù)據(jù)庫設(shè)計的過程可以分為數(shù)據(jù)庫設(shè)計的過程可以分為 6 步步. (1) 需求分析需求分析 (2) 概念數(shù)據(jù)庫設(shè)計概念數(shù)據(jù)庫設(shè)計 (3) 邏輯數(shù)據(jù)庫設(shè)計邏輯數(shù)據(jù)庫設(shè)計 (4) 模式的細化模式的細化 (5) 物理數(shù)據(jù)庫設(shè)計物理數(shù)據(jù)庫設(shè)計 (6) 應(yīng)用與安全設(shè)計應(yīng)用與安全設(shè)計 進行進行數(shù)據(jù)庫規(guī)劃數(shù)據(jù)庫規(guī)劃是所有數(shù)據(jù)庫編程的第一步是所有數(shù)據(jù)庫編程的第一步,也是最重要的一步也是最重要的一步. 首先，列出客戶所關(guān)心的所有數(shù)據(jù)和建立這首先，列出客戶所關(guān)心的所有數(shù)據(jù)和建立這個數(shù)據(jù)庫的主要目的個數(shù)據(jù)庫的主要目的. 這可以通過收集各種相關(guān)這可以通過收集各種相關(guān)的數(shù)據(jù)報表和表格來完成的數(shù)據(jù)報表和表格來

4、完成. 需求分析需求分析是數(shù)據(jù)庫設(shè)是數(shù)據(jù)庫設(shè)計的第一階段，不斷的調(diào)查與研究，了解組織機計的第一階段，不斷的調(diào)查與研究，了解組織機構(gòu)的情況，了解部門的業(yè)務(wù)流程等系統(tǒng)需求，對構(gòu)的情況，了解部門的業(yè)務(wù)流程等系統(tǒng)需求，對于設(shè)計好數(shù)據(jù)庫是非常重要的于設(shè)計好數(shù)據(jù)庫是非常重要的. 然后，然后，數(shù)據(jù)表規(guī)劃數(shù)據(jù)表規(guī)劃是整個數(shù)據(jù)庫設(shè)計中技巧是整個數(shù)據(jù)庫設(shè)計中技巧性最強的一個步驟性最強的一個步驟. 數(shù)據(jù)表要滿足第三范式數(shù)據(jù)表要滿足第三范式.數(shù)據(jù)庫規(guī)劃數(shù)據(jù)庫規(guī)劃 規(guī)劃數(shù)據(jù)庫規(guī)劃數(shù)據(jù)庫有以下三條有以下三條基本原則基本原則： 數(shù)據(jù)庫中的記錄個數(shù)應(yīng)該反映現(xiàn)實世界數(shù)據(jù)庫中的記錄個數(shù)應(yīng)該反映現(xiàn)實世界對象的個數(shù)對象的個數(shù). 如果

5、一個對象的實例在現(xiàn)實世界中如果一個對象的實例在現(xiàn)實世界中存在，則在數(shù)據(jù)庫中有且只有一條記錄存在存在，則在數(shù)據(jù)庫中有且只有一條記錄存在. 每行記錄的字段應(yīng)該表達現(xiàn)實世界對象每行記錄的字段應(yīng)該表達現(xiàn)實世界對象的屬性的屬性. 現(xiàn)實世界對象之間的關(guān)系應(yīng)該反映在數(shù)現(xiàn)實世界對象之間的關(guān)系應(yīng)該反映在數(shù)據(jù)庫記錄之間的關(guān)系上據(jù)庫記錄之間的關(guān)系上.數(shù)據(jù)庫規(guī)劃基本原則數(shù)據(jù)庫規(guī)劃基本原則 關(guān)系數(shù)據(jù)庫的目的是建立現(xiàn)實世界的模型關(guān)系數(shù)據(jù)庫的目的是建立現(xiàn)實世界的模型.基于這一點基于這一點, 用來實施數(shù)據(jù)完整性的規(guī)則和方法用來實施數(shù)據(jù)完整性的規(guī)則和方法無論對于理論和數(shù)據(jù)庫開發(fā)的實踐都很重要無論對于理論和數(shù)據(jù)庫開發(fā)的實踐都很重

6、要. 數(shù)據(jù)完整性數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的正是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的正確性和相容性確性和相容性. 設(shè)計數(shù)據(jù)完整性的設(shè)計數(shù)據(jù)完整性的目的目的是為了確保數(shù)據(jù)的質(zhì)是為了確保數(shù)據(jù)的質(zhì)量量. 即防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù)即防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù), 防防止錯誤信息的輸入和輸出止錯誤信息的輸入和輸出.二二. . 設(shè)計數(shù)據(jù)完整性設(shè)計數(shù)據(jù)完整性 表是存儲數(shù)據(jù)的主要對象表是存儲數(shù)據(jù)的主要對象, 設(shè)計數(shù)據(jù)庫的設(shè)計數(shù)據(jù)庫的關(guān)鍵關(guān)鍵是設(shè)計表結(jié)構(gòu)是設(shè)計表結(jié)構(gòu). 本節(jié)我們將學(xué)習(xí)如何評價和設(shè)計好的數(shù)據(jù)庫本節(jié)我們將學(xué)習(xí)如何評價和設(shè)計好的數(shù)據(jù)庫表，控制數(shù)據(jù)冗余，從而避免數(shù)據(jù)異常，這就是表，控制數(shù)據(jù)冗余

7、，從而避免數(shù)據(jù)異常，這就是規(guī)范化規(guī)范化. 為了識別和鑒賞好的表結(jié)構(gòu)的特性，考察一為了識別和鑒賞好的表結(jié)構(gòu)的特性，考察一個不好的表結(jié)構(gòu)是很有用的個不好的表結(jié)構(gòu)是很有用的. 利用這種方法，我利用這種方法，我們將學(xué)會如何設(shè)計好的表結(jié)構(gòu)，如何修改已有的們將學(xué)會如何設(shè)計好的表結(jié)構(gòu)，如何修改已有的不好的表結(jié)構(gòu)不好的表結(jié)構(gòu).三三. . 數(shù)據(jù)庫表的規(guī)范化數(shù)據(jù)庫表的規(guī)范化 通過規(guī)范化不僅可以消除數(shù)據(jù)異常，而且適通過規(guī)范化不僅可以消除數(shù)據(jù)異常，而且適當(dāng)規(guī)范化的表結(jié)構(gòu)實際上要比沒有規(guī)范化的來得當(dāng)規(guī)范化的表結(jié)構(gòu)實際上要比沒有規(guī)范化的來得簡單，它還能反映出一個組織的實際運行情況簡單，它還能反映出一個組織的實際運行情況.

8、 擁有好的關(guān)系數(shù)據(jù)庫軟件，并不足以避免擁有好的關(guān)系數(shù)據(jù)庫軟件，并不足以避免數(shù)據(jù)冗余數(shù)據(jù)冗余. 即使在一個好的數(shù)據(jù)庫設(shè)計中即使在一個好的數(shù)據(jù)庫設(shè)計中, 也有也有可能生成不好的表結(jié)構(gòu)可能生成不好的表結(jié)構(gòu). 規(guī)范化規(guī)范化 那么，如何識別不好的表結(jié)構(gòu)，如何創(chuàng)建那么，如何識別不好的表結(jié)構(gòu)，如何創(chuàng)建好的表結(jié)構(gòu)呢？兩個問題的答案都是基于規(guī)范好的表結(jié)構(gòu)呢？兩個問題的答案都是基于規(guī)范化的化的. 規(guī)范化規(guī)范化是給實體分配屬性的過程是給實體分配屬性的過程. 它能它能夠減少但不能夠消除數(shù)據(jù)冗余；相反，它通過夠減少但不能夠消除數(shù)據(jù)冗余；相反，它通過生成有控制的冗余來連接數(shù)據(jù)庫表生成有控制的冗余來連接數(shù)據(jù)庫表.規(guī)范化規(guī)范

9、化 規(guī)范化通過一系列稱為規(guī)范化通過一系列稱為范式范式的階段來完成的階段來完成.最前面的三個階段分別叫做第一范式最前面的三個階段分別叫做第一范式 (1NF)、第二范式第二范式 (2NF)和和第三范式第三范式 (3NF). 從結(jié)構(gòu)化從結(jié)構(gòu)化的觀點來看，的觀點來看，2NF 比比 1NF 好，好，3NF 比比 2NF 好好.對于大多數(shù)的事物數(shù)據(jù)庫設(shè)計，我們只要能規(guī)對于大多數(shù)的事物數(shù)據(jù)庫設(shè)計，我們只要能規(guī)范到范到 3NF 就已經(jīng)足夠了就已經(jīng)足夠了. 雖然規(guī)范化是數(shù)據(jù)庫設(shè)計的一個重要組成雖然規(guī)范化是數(shù)據(jù)庫設(shè)計的一個重要組成部分，但是并不是規(guī)范化級別越高越好部分，但是并不是規(guī)范化級別越高越好. 通常通常,范

10、式級別越高，產(chǎn)生制定輸出所要求的連接就范式級別越高，產(chǎn)生制定輸出所要求的連接就越多，系統(tǒng)對終端用戶請求的響應(yīng)就越慢越多，系統(tǒng)對終端用戶請求的響應(yīng)就越慢.三種范式三種范式 我們考察某建筑公司的一個簡單數(shù)據(jù)庫行我們考察某建筑公司的一個簡單數(shù)據(jù)庫行為為. 該公司管理幾個建筑項目，每個項目都有該公司管理幾個建筑項目，每個項目都有它自己的項目號、項目名、員工等屬性，每個它自己的項目號、項目名、員工等屬性，每個員工有員工號、姓名和職位級別等屬性員工有員工號、姓名和職位級別等屬性. 公司根據(jù)每個合同需要花費的小時數(shù)來向公司根據(jù)每個合同需要花費的小時數(shù)來向客戶收費客戶收費. 每小時的報酬由員工的職位決定每小時

11、的報酬由員工的職位決定. 該應(yīng)用周期性地生成信息報表該應(yīng)用周期性地生成信息報表. 由這個報由這個報表對應(yīng)地生成一個表表對應(yīng)地生成一個表. 一個實例一個實例 這個表的結(jié)構(gòu)不符合完整性的要求，它也這個表的結(jié)構(gòu)不符合完整性的要求，它也沒有很好的處理數(shù)據(jù)沒有很好的處理數(shù)據(jù). 項目號（項目號（PROJ_NUM）很明顯是希望）很明顯是希望作為一個主鍵，或者至少是主鍵的一部分，但作為一個主鍵，或者至少是主鍵的一部分，但是它包含空值是它包含空值. 表的數(shù)據(jù)輸入引起數(shù)據(jù)的不一致表的數(shù)據(jù)輸入引起數(shù)據(jù)的不一致. 如，如，Elect.Engineer 可能寫成可能寫成Elect.Eng, El.Eng,EE. 該表中

12、有數(shù)據(jù)冗余，會產(chǎn)生異常：該表中有數(shù)據(jù)冗余，會產(chǎn)生異常： 更新異常更新異常: 如修改如修改105號的號的 JOB_CLASS. 插入異常，刪除異常插入異常，刪除異常. 當(dāng)把某員工分配到某項目時當(dāng)把某員工分配到某項目時,重復(fù)輸入數(shù)據(jù)重復(fù)輸入數(shù)據(jù).一個實例一個實例 消除組重復(fù)消除組重復(fù) 在作為主鍵的列中加入合適的項即可在作為主鍵的列中加入合適的項即可. 主鍵應(yīng)能恰當(dāng)?shù)?、惟一地確定任何屬性主鍵應(yīng)能恰當(dāng)?shù)亍⑽┮坏卮_定任何屬性值值. 取取主鍵主鍵為為 PROJ_NUM 和和 EMP_NUM 的的組合組合. 這樣得到了符合第一范式的表這樣得到了符合第一范式的表. 確定主鍵確定主鍵PROJ_NUM PROJ_

13、NAME EMP_NUM EMP_NAME JOB_CLASS CHG_HOUR HOUR部分依賴部分依賴部分依賴部分依賴傳遞依賴傳遞依賴屬性關(guān)系圖屬性關(guān)系圖 術(shù)語術(shù)語 1NF 描述了如下的表格式：描述了如下的表格式： 定義了所有的鍵屬性定義了所有的鍵屬性. 表中沒有重復(fù)組表中沒有重復(fù)組. 所有的屬性都依賴于主碼所有的屬性都依賴于主碼. 所有的關(guān)系表都屬于所有的關(guān)系表都屬于 1NF. 上圖所示的第上圖所示的第一范式的表結(jié)構(gòu)的問題是它包含一范式的表結(jié)構(gòu)的問題是它包含部分依賴部分依賴, 也也就是說，基于部分主鍵的依賴就是說，基于部分主鍵的依賴. 雖然有時為了提高性能需要使用部分依賴雖然有時為了提高

14、性能需要使用部分依賴,但要謹慎使用但要謹慎使用. 因為這樣的表仍有數(shù)據(jù)冗余因為這樣的表仍有數(shù)據(jù)冗余(由由每行記錄都要求有數(shù)據(jù)副本而產(chǎn)生每行記錄都要求有數(shù)據(jù)副本而產(chǎn)生), 重復(fù)輸入重復(fù)輸入使效率很低，也會助長數(shù)據(jù)異常的產(chǎn)生使效率很低，也會助長數(shù)據(jù)異常的產(chǎn)生. 第一范式第一范式 一個表屬于一個表屬于第二范式第二范式是指：是指： 它屬于它屬于 1NF . 它不包含它不包含部分依賴部分依賴，即，沒有屬性只依，即，沒有屬性只依賴于主鍵的一部分賴于主鍵的一部分. 到第二范式的轉(zhuǎn)換是，將原表分為三個表到第二范式的轉(zhuǎn)換是，將原表分為三個表: PROJCT ( PROJ_NUM, PROJ_NAME ) EMP

15、LOYEE ( EMP_NUM, EMP_NAME,JOB_CLASS, CHG_HOUR ) ASSIGN ( PROJ_NUM, EMP_NUM, ASSIGN_HOUR)第二范式第二范式 將三個表再分出一個表，消除將三個表再分出一個表，消除傳遞依賴傳遞依賴: EMPLOYEE ( EMP_NUM, EMP_NAME,JOB_CLASS ) JOB ( JOB_CLASS, CHG_HOUR ) 表屬于表屬于第三范式第三范式是指：是指： 它屬于它屬于 2NF . 它不包含傳遞依賴它不包含傳遞依賴. 消除了表結(jié)構(gòu)中的部分依賴和傳遞依賴后消除了表結(jié)構(gòu)中的部分依賴和傳遞依賴后,還要集中改善數(shù)據(jù)庫

16、提供信息和增強關(guān)系特性還要集中改善數(shù)據(jù)庫提供信息和增強關(guān)系特性. 第三范式第三范式 練習(xí)練習(xí) 寫出寫出 T-SQL 語句建立下表存放學(xué)語句建立下表存放學(xué)生的信息生的信息. 表名為：表名為：student, 含有列：含有列：name(char,10位位)、id(char, 7位位)、sex(char,2位位)、birthday(datetime, 8位位) 、 class(char, 10位位) birth_place(varchar, 50位位)，設(shè)置學(xué)號（，設(shè)置學(xué)號（id）為主鍵約束并帶有聚集索引為主鍵約束并帶有聚集索引. USE pubs GO CREATE FUNCTION fun_ti

17、tles( vartype varchar(30) RETURNS table AS RETURN(select title_id, title, type，price from titles where type=vartype) GO select * from dbo.fun_titles(business) 練習(xí)答案練習(xí)答案大型大型數(shù)數(shù)據(jù)據(jù)庫應(yīng)庫應(yīng)用（用（SQL Server 2008）6.4 實驗指導(dǎo) 實驗內(nèi)容從網(wǎng)上下載示例數(shù)據(jù)庫從網(wǎng)上下載示例數(shù)據(jù)庫Northwind，在，在SQL Serve 2008中導(dǎo)入中導(dǎo)入Northwind，在，在 “ SQL Server Manageme

18、nt Studio”管理窗口中，管理窗口中，完成以下實驗內(nèi)容的代碼調(diào)試。完成以下實驗內(nèi)容的代碼調(diào)試。1. 在在Northwind數(shù)據(jù)庫中創(chuàng)建一個數(shù)據(jù)庫中創(chuàng)建一個xml_Categories表，并在表中創(chuàng)建一表，并在表中創(chuàng)建一個個XML類型字段，并將類型字段，并將XML實例分配給它。實例分配給它。2. 分別使用分別使用AUTO和和RAW模式，查詢前模式，查詢前5條客戶訂購產(chǎn)品的信息。條客戶訂購產(chǎn)品的信息。3. 在在Northwind數(shù)據(jù)庫的數(shù)據(jù)庫的Customers表中，使用表中，使用 OPENXML 語句將查詢語句將查詢合同編號和電話號碼的存放到合同編號和電話號碼的存放到XML數(shù)據(jù)類型變量數(shù)據(jù)類型變量var中。然后，在中。然后，在INSERT語句中使用該變量語句中使用該變量varXml中，最后將變量中，最后將變量varXml中的中的XML數(shù)據(jù)插入新建的表數(shù)據(jù)插入新建的表CustomerOutput中的中的XmlData列中（此列數(shù)據(jù)類型列中（此列數(shù)據(jù)類型為為XML）。）。4. 使用使用 OPENXML 語句將語句將XML 文檔的對象樹轉(zhuǎn)化為表的形式顯示出來。文檔的對象樹轉(zhuǎn)化為表的形式顯示出來。 大型大型數(shù)數(shù)據(jù)據(jù)