SQLServer數(shù)據(jù)庫完整性教學(xué)案例探析

岳清

摘要：數(shù)據(jù)庫完整性約束條件是數(shù)據(jù)模型三要素之一，與數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù)操作一起抽象表示現(xiàn)實(shí)世界的數(shù)據(jù)和信息，數(shù)據(jù)庫完整性是數(shù)據(jù)庫課程的重要組成部分。針對目前高校普遍開設(shè)的基于SQL Server數(shù)據(jù)庫課程，分析了數(shù)據(jù)庫完整性約束的重要性，給出了一個(gè)包含實(shí)體完整性、參照完整性和用戶自定義完整性的案例，探索數(shù)據(jù)庫教學(xué)方法，為提升數(shù)據(jù)庫能力奠定了基礎(chǔ)。

關(guān)鍵詞：SQL Server；數(shù)據(jù)庫；完整性；教學(xué)；案例

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2017）26-0001-02

Abstract： The constraints of database integrity is one of the three elements of data model， and together with data structure and manipulation， an abstract representation of the data and information in the real world. The integrity of database is one of the important part of courses about database. For the SQL Server Database courses commonly offered in colleges， the paper analyzes the importance of the constraint of database integrity， offers a case including the entity integrity， referential integrity and user-defined Integrity， and explores into the teaching methods of database to cement the base for improving database capacity.

Key words： SQL Server；Database； integrity； Teaching； Case

1 概述

完整性約束保證當(dāng)授權(quán)用戶對數(shù)據(jù)庫進(jìn)行修改時(shí)不會(huì)破壞數(shù)據(jù)的一致性，防止對數(shù)據(jù)的意外損壞[1]，是保障數(shù)據(jù)正確性的手段之一。在數(shù)據(jù)庫課程中，完整性約束的設(shè)計(jì)與實(shí)現(xiàn)是教學(xué)中不可缺少的一部分，在教學(xué)中可以通過案例幫助學(xué)生理解完整性約束的重要性和實(shí)現(xiàn)方法，本文給出了一個(gè)基于SQL Server的數(shù)據(jù)完整性的案例。

2 數(shù)據(jù)庫完整性案例

對于案例的選擇，要優(yōu)先選擇學(xué)生熟悉的例子，有助于理解語義，如本文選用了熟悉的學(xué)生借閱圖書的例子。本例中，一個(gè)學(xué)校有多名學(xué)生，每個(gè)學(xué)生有唯一的學(xué)號；學(xué)校的圖書館有若干本書，書有圖書編號，有些書如高等數(shù)學(xué)習(xí)題集借閱的人多，相同的書圖書館備有多本，它們有相同的圖書編號，數(shù)據(jù)庫要記錄相同圖書編號的書有幾本，未借出在庫的有幾本；每個(gè)借閱的信息和歸還的信息要記錄在數(shù)據(jù)庫中。

根據(jù)以上語義，進(jìn)行概念模型設(shè)計(jì)，得到如圖1所示的E-R圖，從圖中可以看出，系統(tǒng)有兩個(gè)實(shí)體：學(xué)生和圖書，由于一個(gè)學(xué)生可以借多本書，一本書也可以在不同時(shí)間被多個(gè)學(xué)生借閱，因此兩個(gè)實(shí)體間的關(guān)系是多對多（m：n）。E-R模型主要是面向用戶的，需要將其轉(zhuǎn)換為具體數(shù)據(jù)庫管理系統(tǒng)支持的數(shù)據(jù)模型[2]。由這個(gè)概念模型轉(zhuǎn)換為關(guān)系數(shù)據(jù)庫模型時(shí)，轉(zhuǎn)換為3個(gè)表：學(xué)生實(shí)體對應(yīng)的student表，圖書實(shí)體對應(yīng)的book表，表示學(xué)生實(shí)體和圖書實(shí)體多對多關(guān)系的borrow表。三個(gè)表的結(jié)構(gòu)如表1—表3所示。

2.1 實(shí)體完整性

一個(gè)實(shí)體在數(shù)據(jù)庫中表現(xiàn)為表中的一條記錄，實(shí)體完整性保證表中每個(gè)記錄是唯一的，通過主鍵來實(shí)現(xiàn)。在student表和book表中，studentID和bookID做主鍵，保證了唯一。在borrow表中，由于一個(gè)學(xué)生可以借閱多本書，一本書可以在不同時(shí)間被多個(gè)學(xué)生借閱，因此，studentID和bookID分別做主鍵都是不合適的。一個(gè)學(xué)生在不同的時(shí)間段可借閱相同的書，例如某同學(xué)在大一時(shí)借閱了高等數(shù)學(xué)習(xí)題集這本書，在大二可以再次借閱這本書，因此studentID和bookID做復(fù)合主鍵也不合適。在borrow表中可以將studentID、bookID和borrowDate這三個(gè)字段做復(fù)合主鍵。在數(shù)據(jù)庫設(shè)計(jì)中，當(dāng)復(fù)合主鍵的元素大于等于3時(shí)，通常就增加一個(gè)沒有實(shí)際意義的編號字段作為主鍵，在borrow表中，增加了一個(gè)borrowID作為主鍵，該字段沒有其他含義，只是標(biāo)注記錄的唯一性。在教學(xué)環(huán)節(jié)中，可以通過設(shè)置borrow表的主鍵來講解復(fù)合主鍵的概念，強(qiáng)調(diào)復(fù)合主鍵不是多個(gè)主鍵，而是多個(gè)字段組成的一個(gè)集合，是一個(gè)整體。

2.2 參照完整性

參照完整性實(shí)現(xiàn)手段是外鍵，表明了表之間的關(guān)系。在borrow表中，studentID一定是學(xué)生表存在的studentID，因此，borrow表中的studentID字段作為外鍵，引用了學(xué)生表的studentID。同理，borrow表的bookID也是外鍵，引用了book表的bookID。

在教學(xué)過程中，為了讓學(xué)生更好地體會(huì)什么是外鍵，可以完成以下2個(gè)小實(shí)驗(yàn)。

1） 在borrow表中添加一條記錄，該記錄中的studentID在student表中不存在。測試的結(jié)果：記錄無法添加到borrow表。

2） 刪除student表中的一個(gè)學(xué)生信息，該學(xué)生在borrow表中有借閱信息。測試結(jié)果：無法刪除，原因是如果刪除了，borrow表中對應(yīng)的記錄就失去了意義，無法確定這條借閱信息是那個(gè)學(xué)生的借閱信息，因?yàn)閷W(xué)生的信息已經(jīng)在student表中刪除。endprint

設(shè)定參照完整性之后，student表、book表和borrow表之間的關(guān)系如圖2所示，student表和borrow表是一對多的關(guān)系，book表和borrow表也是一對多的關(guān)系。

2.3 用戶自定義完整性

用戶定義完整性涵蓋的內(nèi)容比較多，例如：字段的數(shù)據(jù)類型、字段的約束、觸發(fā)器等。

2.3.1 字段的數(shù)據(jù)類型

在book表中，bookPrice表示圖書的定價(jià)，數(shù)據(jù)類型為float，bookCount和bookInCount表示該本書的數(shù)量和目前在庫的數(shù)量，數(shù)據(jù)類型為smallint。限定了數(shù)據(jù)類型就可以確保對應(yīng)的字段值只能是數(shù)值類型的數(shù)據(jù)，避免由于操作錯(cuò)誤輸入非數(shù)字字符。borrow表的borrowDate和returnDate的數(shù)據(jù)類型是smalldatetime，不僅避免了非日期型的數(shù)據(jù)的存儲，另一個(gè)優(yōu)點(diǎn)就是在這兩個(gè)日期型的字段上可以利用SQL Server的內(nèi)置函數(shù)進(jìn)行計(jì)算，如計(jì)算圖書借閱圖書的天數(shù)，可以使用DateDiff函數(shù)求出borrowDate和returnDate之間的天數(shù)。

在教學(xué)過程中，應(yīng)說明數(shù)據(jù)類型的選擇的重要性。許多初學(xué)者經(jīng)常將所有的字段的數(shù)據(jù)類型都定為char類型，認(rèn)為這樣不容易出錯(cuò)。這種思路是錯(cuò)誤的，根據(jù)語義為字段選擇合適的數(shù)據(jù)類型，SQL Server有許多內(nèi)置的函數(shù)可以用于不同的數(shù)據(jù)類型的數(shù)據(jù)上。

2.3.2 字段的約束

字段的約束是為了保證語義的正確性。例如本例中，相同的圖書可以有多本，但是在庫的，也就是沒有借出的數(shù)量應(yīng)該小于等于該書中的數(shù)量，即book表中的應(yīng)該添加一條約束：“bookInCount<=bookCount”。同理，借閱圖書中歸還日期不應(yīng)早與借閱的日期，在borrow表中也要增加一約束：“returnDate is null or returnDate>borrowDate”，表示歸還日期或者為空（只借出還沒有歸還的情況）或者歸還日期不早于借出日期。在本例中，student表中studentGender字段的取值只能是“男”或“女”，應(yīng)該添加約束：“studentGender in （'男'，'女'）”。

教學(xué)中要強(qiáng)調(diào)數(shù)據(jù)庫設(shè)計(jì)中字段的約束重要性，這是對語義的保障，這一工作如果不在數(shù)據(jù)庫設(shè)計(jì)中完成，那么就要在程序設(shè)計(jì)中完成。在數(shù)據(jù)庫設(shè)計(jì)階段完成不僅是工作量小而且也符合規(guī)范化設(shè)計(jì)流程。

2.3.3 觸發(fā)器

觸發(fā)器功能強(qiáng)大，能夠?yàn)閿?shù)據(jù)完整性和系統(tǒng)的總體操作增加極大的靈活性。觸發(fā)器功能上類似于CHECK約束，但是可以跨表、數(shù)據(jù)庫或服務(wù)器工作[3]。通常用觸發(fā)器來滿足業(yè)務(wù)邏輯，實(shí)現(xiàn)完整性。

觸發(fā)器triborrow在對借閱表borrow添加數(shù)據(jù)時(shí)系統(tǒng)自動(dòng)觸發(fā)，統(tǒng)計(jì)borrow表中該生，即學(xué)號相同的學(xué)生的借閱信息中沒有歸還的日期的記錄的個(gè)數(shù)，也就是該生的正在借閱沒有歸還的書的數(shù)量，當(dāng)超過5本時(shí)，則進(jìn)行回滾，借閱不成功。否則借閱成功，同時(shí)book表中的表示在庫數(shù)量的字段bookInCount減1。

當(dāng)還書的時(shí)候，修改borrow表，添加returnDate字段的值，同時(shí)修改book表中的表示在庫數(shù)量的字段bookInCount，將其值加1。該觸發(fā)器的代碼如下：

該觸發(fā)器首先判斷是否修改前的returnDate字段為空，而修改后的returnDate字段不為空，也就是在進(jìn)行還書操作，如果是，book表中的字段表示在庫數(shù)量的字段值加1。

觸發(fā)器是教學(xué)過程的一個(gè)難點(diǎn)，在教學(xué)中首先要強(qiáng)調(diào)觸發(fā)器的作用和執(zhí)行過程，觸發(fā)器是用來實(shí)現(xiàn)用戶自定義完整性，觸發(fā)器的執(zhí)行不是用戶直接調(diào)用，而是在對某個(gè)表執(zhí)行特定的操作，如增刪改，自動(dòng)的觸發(fā)?？梢酝ㄟ^以上案例分析觸發(fā)器是如何實(shí)現(xiàn)特定的業(yè)務(wù)邏輯。其次在編寫觸發(fā)器中，通常會(huì)用到系統(tǒng)的兩個(gè)臨時(shí)表deleted和inserted，如在本例triUpdateborrow觸發(fā)器中，要檢查修改前的returnDate字段為空，而修改后的returnDate字段不為空，修改前記錄信息放在deleted表中，修改后的記錄放在了inserted表中。

3 結(jié)論

本文通過了一個(gè)完整的案例介紹了數(shù)據(jù)庫課程中的數(shù)據(jù)完整性實(shí)現(xiàn)過程。在實(shí)際的教學(xué)中，教師通過理論講解、案例分析、設(shè)計(jì)和實(shí)現(xiàn)等多個(gè)環(huán)節(jié)讓學(xué)生理解數(shù)據(jù)庫完整性的作用和實(shí)現(xiàn)方法。為學(xué)生今后從事相關(guān)數(shù)據(jù)庫開發(fā)工作奠定了基礎(chǔ)。

參考文獻(xiàn)：

[1] Abraham Silberschatz.數(shù)據(jù)庫系統(tǒng)概述[M].北京：機(jī)械工業(yè)出版社，2006.

[2] 何玉潔，劉福剛.數(shù)據(jù)庫原理及應(yīng)用[M].北京：人民郵電出版社，2012.

[3] Robert Vieira.SQL Server 2008高級程序設(shè)計(jì)[M].北京：清華大學(xué)出版社，2010.endprint