1、第 6 講 設(shè)計數(shù)據(jù)完整性,大型數(shù)據(jù)庫應(yīng)用（SQL Server 2008）,本講要點,關(guān)系數(shù)據(jù)庫設(shè)計概述,設(shè)計數(shù)據(jù)完整性,數(shù)據(jù)庫表的規(guī)范化,1,2,3,1 美 Rob, P. Coonel, C. 數(shù)據(jù)庫系統(tǒng) 設(shè)計、實現(xiàn)與管理（第5版）. 陳立軍 等譯. 電子工業(yè)出版社，2004.3 2 美 Ramakrishnan, R. Gehrke, J. 數(shù)據(jù)庫管理系統(tǒng)原理與設(shè)計（第3版）. 周立柱 等譯. 北京：清華大學(xué)出版社，2004.3,本講參考書目,DBMS (DataBase Management System) 從產(chǎn)生直至發(fā)展到現(xiàn)在, 出現(xiàn)了多種類型. 按 其數(shù)據(jù)模型來分，主要有層次數(shù)據(jù)

2、庫、網(wǎng)狀數(shù) 據(jù)庫和關(guān)系數(shù)據(jù)庫. 前兩者在二十世紀六七十 年代較為流行，然而他們表示數(shù)據(jù)之間的聯(lián)系 太過于復(fù)雜，現(xiàn)在已經(jīng)很少使用，只是在講解 數(shù)據(jù)庫的時候才有所提及. 關(guān)系數(shù)據(jù)庫以關(guān)系的數(shù)學(xué)理論為基礎(chǔ)，是 數(shù)據(jù)庫技術(shù)的一項重大突破.,一. 關(guān)系數(shù)據(jù)庫設(shè)計概述,關(guān)系數(shù)據(jù)庫簡單嚴謹，四十多年來有了長 足發(fā)展，現(xiàn)在已經(jīng)成為事實上的標(biāo)準(zhǔn)，當(dāng)今幾 乎所有的數(shù)據(jù)庫產(chǎn)品都是基于關(guān)系數(shù)據(jù)庫的. 數(shù)據(jù)庫設(shè)計是數(shù)據(jù)庫應(yīng)用的一個關(guān)鍵因素, 設(shè)計結(jié)構(gòu)合理、功能齊全的數(shù)據(jù)庫對于提高數(shù) 據(jù)庫應(yīng)用程序的開發(fā)效率和程序的性能都是非 常重要的. 數(shù)據(jù)庫設(shè)計是指對于一個給定的應(yīng)用環(huán)境, 構(gòu)造最優(yōu)的數(shù)據(jù)模式，建立數(shù)據(jù)庫，使其能夠 有

3、效地存儲數(shù)據(jù)記錄，并能滿足各種應(yīng)用需求.,數(shù)據(jù)庫設(shè)計,對于一個數(shù)據(jù)密集型應(yīng)用來說，數(shù)據(jù)庫設(shè) 計是它的一個核心部分，但對于大型的軟件系 統(tǒng)設(shè)計來說, 它只是其中一個部分. 數(shù)據(jù)庫設(shè)計的過程可以分為 6 步. (1) 需求分析 (2) 概念數(shù)據(jù)庫設(shè)計 (3) 邏輯數(shù)據(jù)庫設(shè)計 (4) 模式的細化 (5) 物理數(shù)據(jù)庫設(shè)計 (6) 應(yīng)用與安全設(shè)計,進行數(shù)據(jù)庫規(guī)劃是所有數(shù)據(jù)庫編程的第一步, 也是最重要的一步. 首先，列出客戶所關(guān)心的所有數(shù)據(jù)和建立這 個數(shù)據(jù)庫的主要目的. 這可以通過收集各種相關(guān) 的數(shù)據(jù)報表和表格來完成. 需求分析是數(shù)據(jù)庫設(shè) 計的第一階段，不斷的調(diào)查與研究，了解組織機 構(gòu)的情況，了解部門的業(yè)

4、務(wù)流程等系統(tǒng)需求，對 于設(shè)計好數(shù)據(jù)庫是非常重要的. 然后，數(shù)據(jù)表規(guī)劃是整個數(shù)據(jù)庫設(shè)計中技巧 性最強的一個步驟. 數(shù)據(jù)表要滿足第三范式.,數(shù)據(jù)庫規(guī)劃,規(guī)劃數(shù)據(jù)庫有以下三條基本原則： 數(shù)據(jù)庫中的記錄個數(shù)應(yīng)該反映現(xiàn)實世界 對象的個數(shù). 如果一個對象的實例在現(xiàn)實世界中 存在，則在數(shù)據(jù)庫中有且只有一條記錄存在. 每行記錄的字段應(yīng)該表達現(xiàn)實世界對象 的屬性. 現(xiàn)實世界對象之間的關(guān)系應(yīng)該反映在數(shù) 據(jù)庫記錄之間的關(guān)系上.,數(shù)據(jù)庫規(guī)劃基本原則,關(guān)系數(shù)據(jù)庫的目的是建立現(xiàn)實世界的模型. 基于這一點, 用來實施數(shù)據(jù)完整性的規(guī)則和方法 無論對于理論和數(shù)據(jù)庫開發(fā)的實踐都很重要. 數(shù)據(jù)完整性是指存儲在數(shù)據(jù)庫中的數(shù)據(jù)的正 確

5、性和相容性. 設(shè)計數(shù)據(jù)完整性的目的是為了確保數(shù)據(jù)的質(zhì) 量. 即防止數(shù)據(jù)庫中存在不符合語義的數(shù)據(jù), 防 止錯誤信息的輸入和輸出.,二. 設(shè)計數(shù)據(jù)完整性,數(shù)據(jù)完整性可以分為四類： 實體完整性 每個實體都保持惟一性. 值域完整性 如何限制向表中輸入值的范圍. 參照完整性 處理數(shù)據(jù)時維護表之間數(shù)據(jù)的一致性. 用戶定義的完整性 體現(xiàn)實際運用的業(yè)務(wù)規(guī)則.,數(shù)據(jù)完整性分類,實體完整性把數(shù)據(jù)表中的每行看作一個實體, 且具有唯一標(biāo)識. 即每一個實體都必須擁有一個 主鍵或者其他的惟一標(biāo)識列. 要求：所有的主鍵項都是唯一的，并且主鍵 的任何部分都不能為空. 目的：保證每個實體有惟一的標(biāo)識，確保外 鍵值可以正確地引用

6、主鍵值. 例子：發(fā)貨單沒有重復(fù)的號碼，號碼也不能 為空.,實體完整性,哈爾濱師范大學(xué)恒星學(xué)院,在 SQL Server 中， 可以通過建立 Primary Key 約束、Unique 約束、唯一索 引，以及列 Identity 屬性等措施來實施實體 完整性.,實體完整性的實現(xiàn),大型數(shù)據(jù)庫應(yīng)用（SQL Server 2008）,域完整性要求數(shù)據(jù)表中指定列的數(shù)據(jù)具 有正確的數(shù)據(jù)類型、格式和有效的數(shù)據(jù)范圍. 域完整性通過默認值、Foreign Key、 Check 等約束，以及默認、規(guī)則等數(shù)據(jù)庫對 象來實現(xiàn).,域完整性,參照完整性維持被參照表和參照表之間的 數(shù)據(jù)一致性. 在 SQL Server 中

7、，參照完整性通 過建立 Foreign Key 約束來實施. 要求：外鍵可能是空-只要它不是它自己 所在表的主鍵的一部分 - 每個非null外鍵值必 須參照已經(jīng)存在的主鍵值. 主鍵值被其他表所參 照時，該行不能被刪除，也不允許改變. 目的：不能有無效的項. 例子：客戶可以沒有給定的代理商號碼， 但是不能有無效的代理商號碼.,參照完整性,SQL Server提供的用來實施數(shù)據(jù)完整性的 方法主要是: 數(shù)據(jù)類型 ( data type ) 約束 ( constraint ) 標(biāo)識列 ( identity column) 默認值 ( default ) 索引 ( index ) 規(guī)則 ( rule )

8、 觸發(fā)器 ( trigger ) 存儲過程 ( stored procedure ),約束完整性是指數(shù)據(jù)的正確性和完備性. 在用 INSERT、 DELETE、 UPDATE 語句修改數(shù)據(jù)庫 內(nèi)容時，數(shù)據(jù)的完整性可能會遭到破壞， 有如下 幾種情況： - 無效的數(shù)據(jù)被添加到數(shù)據(jù)庫中，如某訂單 所指的產(chǎn)品不存在. - 對數(shù)據(jù)庫的修改不一致， 如為某產(chǎn)品增加 了訂單，但卻沒有調(diào)整產(chǎn)品的庫存信息. - 將存在的數(shù)據(jù)修改為無效的數(shù)據(jù).,約束,在 SQL Server 中實現(xiàn)數(shù)據(jù)完整性的主要方法就是約束. 使用這種方法簡單且不容易出錯， 它把完整性的要求定義在表和列上. 約束由 SQL Server 強制

9、實行，它能夠限制用戶存放到表中的數(shù)據(jù)的格式和范圍. 約束是在CREATE TABLE、ALTER TABLE 語句中，通過 CONSTRAINT 和 DEFAULT實現(xiàn). 同時，約束獨立于表的結(jié)構(gòu)，可以在不改變表結(jié) 構(gòu)的情況下，使用 ALTER TABLE 語句來添加或 刪除.,約束,數(shù)據(jù)庫設(shè)計的挑戰(zhàn)：相沖突的目標(biāo) 數(shù)據(jù)庫設(shè)計者必須對互相沖突的目標(biāo)進行平 衡：遵守設(shè)計標(biāo)準(zhǔn)，處理速度和信息約束. (1) 數(shù)據(jù)庫設(shè)計必須符合一定的標(biāo)準(zhǔn). 以使得 數(shù)據(jù)冗余最小, 數(shù)據(jù)不一致降到最低. (2) 在很多時候, 數(shù)據(jù)庫設(shè)計最優(yōu)先考慮的目 標(biāo)是高的處理速度. 這可能要求對單表內(nèi)的數(shù)據(jù) 進行合并, 另一方面,

10、 為達到一定的設(shè)計標(biāo)準(zhǔn), 設(shè) 計者可能已經(jīng)傾向于將數(shù)據(jù)保存在不同的表中.,數(shù)據(jù)庫設(shè)計的挑戰(zhàn),(3) 對實時信息的追求可能成為數(shù)據(jù)庫設(shè)計的 焦點. 復(fù)雜信息約束可能需要數(shù)據(jù)變換, 從而在 設(shè)計時會增加實體和屬性的數(shù)量. 因此, 數(shù)據(jù)庫 可能不得不犧牲一些它的”干凈的” 設(shè)計結(jié)構(gòu)和高的處理速度, 以保證生成最多的信息. 設(shè)計要滿足所有這些合理要求和設(shè)計慣例, 是一個重要的目標(biāo). 然而, 如果這些完美的設(shè)計 不能滿足顧客的事務(wù)處理速度或信息約束要求, 那么從終端用戶眼中看來, 該設(shè)計者的工作做得 并不正確.,數(shù)據(jù)庫設(shè)計的挑戰(zhàn),即使我們集中精力于實體、屬性、聯(lián)系和約 束, 我們也應(yīng)該首先考慮終端用戶的

11、要求, 比如 性能、安全、共享訪問、數(shù)據(jù)完整性等. 設(shè)計者 必須考慮處理要求并保證所有的更新、檢索和刪 除選項都是可用的. 最后除非最終的產(chǎn)品可以成 功地完成所有指定的查詢和報表約束, 否則設(shè)計 就幾乎沒有價值. 文檔工作是保證數(shù)據(jù)相容性和一致性工作中 的一個十分重要的部分.,數(shù)據(jù)庫設(shè)計的挑戰(zhàn),表是存儲數(shù)據(jù)的主要對象, 設(shè)計數(shù)據(jù)庫的關(guān)鍵 是設(shè)計表結(jié)構(gòu). 本節(jié)我們將學(xué)習(xí)如何評價和設(shè)計好的數(shù)據(jù)庫 表，控制數(shù)據(jù)冗余，從而避免數(shù)據(jù)異常，這就是 規(guī)范化. 為了識別和鑒賞好的表結(jié)構(gòu)的特性，考察一 個不好的表結(jié)構(gòu)是很有用的. 利用這種方法，我 們將學(xué)會如何設(shè)計好的表結(jié)構(gòu)，如何修改已有的 不好的表結(jié)構(gòu).,三.

12、 數(shù)據(jù)庫表的規(guī)范化,通過規(guī)范化不僅可以消除數(shù)據(jù)異常，而且適 當(dāng)規(guī)范化的表結(jié)構(gòu)實際上要比沒有規(guī)范化的來得 簡單，它還能反映出一個組織的實際運行情況. 擁有好的關(guān)系數(shù)據(jù)庫軟件，并不足以避免 數(shù)據(jù)冗余. 即使在一個好的數(shù)據(jù)庫設(shè)計中, 也有 可能生成不好的表結(jié)構(gòu).,規(guī)范化,那么，如何識別不好的表結(jié)構(gòu)，如何創(chuàng)建 好的表結(jié)構(gòu)呢？兩個問題的答案都是基于規(guī)范 化的. 規(guī)范化是給實體分配屬性的過程. 它能 夠減少但不能夠消除數(shù)據(jù)冗余；相反，它通過 生成有控制的冗余來連接數(shù)據(jù)庫表.,規(guī)范化,規(guī)范化通過一系列稱為范式的階段來完成. 最前面的三個階段分別叫做第一范式 (1NF)、 第二范式 (2NF)和第三范式 (3

13、NF). 從結(jié)構(gòu)化 的觀點來看，2NF 比 1NF 好，3NF 比 2NF 好. 對于大多數(shù)的事物數(shù)據(jù)庫設(shè)計，我們只要能規(guī) 范到 3NF 就已經(jīng)足夠了. 雖然規(guī)范化是數(shù)據(jù)庫設(shè)計的一個重要組成 部分，但是并不是規(guī)范化級別越高越好. 通常, 范式級別越高，產(chǎn)生制定輸出所要求的連接就 越多，系統(tǒng)對終端用戶請求的響應(yīng)就越慢.,三種范式,我們考察某建筑公司的一個簡單數(shù)據(jù)庫行 為. 該公司管理幾個建筑項目，每個項目都有 它自己的項目號、項目名、員工等屬性，每個 員工有員工號、姓名和職位級別等屬性. 公司根據(jù)每個合同需要花費的小時數(shù)來向 客戶收費. 每小時的報酬由員工的職位決定. 該應(yīng)用周期性地生成信息報表

14、. 由這個報 表對應(yīng)地生成一個表.,一個實例,這個表的結(jié)構(gòu)不符合完整性的要求，它也 沒有很好的處理數(shù)據(jù). 項目號（PROJ_NUM）很明顯是希望 作為一個主鍵，或者至少是主鍵的一部分，但 是它包含空值. 表的數(shù)據(jù)輸入引起數(shù)據(jù)的不一致. 如， Elect.Engineer 可能寫成Elect.Eng, El.Eng,EE. 該表中有數(shù)據(jù)冗余，會產(chǎn)生異常： 更新異常: 如修改105號的 JOB_CLASS. 插入異常，刪除異常. 當(dāng)把某員工分配到某項目時,重復(fù)輸入數(shù)據(jù).,一個實例, 消除組重復(fù) 在作為主鍵的列中加入合適的項即可. 主鍵應(yīng)能恰當(dāng)?shù)?、惟一地確定任何屬性 值. 取主鍵為 PROJ_NUM

15、 和 EMP_NUM 的 組合. 這樣得到了符合第一范式的表.,確定主鍵,屬性關(guān)系圖,術(shù)語 1NF 描述了如下的表格式： 定義了所有的鍵屬性. 表中沒有重復(fù)組. 所有的屬性都依賴于主鍵. 所有的關(guān)系表都屬于 1NF. 上圖所示的第 一范式的表結(jié)構(gòu)的問題是它包含部分依賴, 也 就是說，基于部分主鍵的依賴. 雖然有時為了提高性能需要使用部分依賴, 但要謹慎使用. 因為這樣的表仍有數(shù)據(jù)冗余(由 每行記錄都要求有數(shù)據(jù)副本而產(chǎn)生), 重復(fù)輸入 使效率很低，也會助長數(shù)據(jù)異常的產(chǎn)生.,第一范式,一個表屬于第二范式是指： 它屬于 1NF . 它不包含部分依賴，即，沒有屬性只依 賴于主鍵的一部分. 到第二范式的

16、轉(zhuǎn)換是，將原表分為三個表: PROJCT ( PROJ_NUM, PROJ_NAME ) EMPLOYEE ( 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)中的部分依賴和傳遞依賴后, 還要集中改善數(shù)據(jù)庫提供信息和增強關(guān)

17、系特性.,第三范式,練習(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）為主鍵約束并帶有聚集索引.,USE pubs GO CREATE FUNCTION fun_titles( vartype varchar(30) RETURNS table AS RETURN(select title_id, title, type，pr

18、ice from titles where type=vartype) GO select * from dbo.fun_titles(business),練習(xí)答案,大型數(shù)據(jù)庫應(yīng)用（SQL Server 2008）,6.4 實驗指導(dǎo),實驗內(nèi)容 從網(wǎng)上下載示例數(shù)據(jù)庫Northwind，在SQL Serve 2008中導(dǎo)入Northwind，在 “ SQL Server Management Studio”管理窗口中，完成以下實驗內(nèi)容的代碼調(diào)試。 1. 在Northwind數(shù)據(jù)庫中創(chuàng)建一個xml_Categories表，并在表中創(chuàng)建一個XML類型字段，并將XML實例分配給它。 2. 分別使用AUTO和RAW模式，查詢前5條客戶訂購產(chǎn)品的信息。 3. 在Northwind數(shù)據(jù)庫的Customers表中，使用 OPENXML 語句將查詢合同編號和電話號碼的存放到XML數(shù)據(jù)類型變量var中。然后，在INSERT語句中使用該變量varXml中，最后將變量varXm