劉錦培

?

淺析考試系統(tǒng)的數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)

劉錦培1,2

1.福州大學(xué)數(shù)學(xué)與計(jì)算機(jī)科學(xué)學(xué)院；2.福建師范大學(xué)信息技術(shù)學(xué)院

數(shù)據(jù)庫(kù)的應(yīng)用設(shè)計(jì)在軟件開發(fā)中的應(yīng)用日趨廣泛，它是數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)開發(fā)生命周期中的重要一環(huán)和系統(tǒng)開發(fā)成敗的決定性一步。該文通過(guò)“考試系統(tǒng)”實(shí)例，深入分析數(shù)據(jù)庫(kù)設(shè)計(jì)的基本原則和相關(guān)技巧。

數(shù)據(jù)庫(kù)設(shè)計(jì) 主外鍵 檢索 數(shù)據(jù)字典

數(shù)據(jù)庫(kù)設(shè)計(jì)是指對(duì)于一個(gè)給定的應(yīng)用環(huán)境，構(gòu)造最優(yōu)的數(shù)據(jù)庫(kù)模式，建立數(shù)據(jù)庫(kù)及其應(yīng)用系統(tǒng)，有效存儲(chǔ)數(shù)據(jù)，滿足用戶信息要求和處理要求。本文對(duì)數(shù)據(jù)庫(kù)應(yīng)用設(shè)計(jì)介紹以下幾個(gè)方面內(nèi)容：

1 主外鍵的使用

數(shù)據(jù)庫(kù)中的主鍵是為了使記錄能惟一標(biāo)識(shí)，外鍵用來(lái)關(guān)聯(lián)表與表之間的聯(lián)系，主鍵和外鍵是把多個(gè)表組織為一個(gè)有效的關(guān)系數(shù)據(jù)庫(kù)。可以通過(guò)主鍵和外鍵來(lái)了解數(shù)據(jù)庫(kù)的設(shè)計(jì)架構(gòu)，因此就出現(xiàn)以數(shù)據(jù)庫(kù)為驅(qū)動(dòng)的開發(fā)方式。開發(fā)人員根據(jù)數(shù)據(jù)庫(kù)的設(shè)計(jì)和流程順序開發(fā)程序，軟件設(shè)計(jì)人員只需嚴(yán)格控制數(shù)據(jù)庫(kù)的設(shè)計(jì)方向即可控制開發(fā)人員的開發(fā)過(guò)程。目前多數(shù)ERP或MIS系統(tǒng)都是按照這種模式進(jìn)行，因?yàn)橄到y(tǒng)本身的特點(diǎn)就是過(guò)程化的，數(shù)據(jù)庫(kù)驅(qū)動(dòng)方式就是一種過(guò)程化的開發(fā)方式，也是一種較為簡(jiǎn)捷快速的開發(fā)方式。

這里，通過(guò)“考試系統(tǒng)”中的試題管理模塊來(lái)體現(xiàn)主外鍵的使用。試題編號(hào)作為惟一標(biāo)識(shí)來(lái)標(biāo)識(shí)每道試題，而試題中的試題選項(xiàng)不該與試題放在同一張表中，而是通過(guò)另一張?jiān)囶}選項(xiàng)表來(lái)管理，此時(shí)試題選項(xiàng)表中的試題編號(hào)就要外鍵關(guān)聯(lián)試題表中的試題號(hào)，試題選項(xiàng)表再通過(guò)試題編號(hào)和其自身的選項(xiàng)編號(hào)來(lái)組合起來(lái)作為主鍵惟一標(biāo)識(shí)，詳細(xì)表設(shè)計(jì)如下：

Create table T_Subject(

SubjectID int,

Descriptions text

Primary key(SubjectID))

Create table T_SubjectOption(

SubjectID int,

OptionID int,

Descriptions text

Primary key(SubjectID,OptionID),

Foreign key(SubjectID) references T_Subject(SubjectID))

通過(guò)以上表設(shè)計(jì)，可以保證在試題選項(xiàng)表T_Subject- Option中的試題號(hào)都可以在試題表T_Subject中找到，體現(xiàn)了數(shù)據(jù)的相互依賴關(guān)系，同樣通過(guò)這樣的表結(jié)果可以看出試題表與試題選項(xiàng)表之間的關(guān)系。

2 數(shù)據(jù)字典的設(shè)計(jì)

數(shù)據(jù)字典（Data Dictionary）是一種用戶可以訪問(wèn)的記錄數(shù)據(jù)庫(kù)和應(yīng)用程序元數(shù)據(jù)的目錄。其特點(diǎn)是供人查詢對(duì)不了解的條目的解釋，數(shù)據(jù)量基本有限，在軟件中用于搭配其他模塊的屬性選擇，比如“考試系統(tǒng)”中安排考試的時(shí)候下拉選擇年級(jí)、專業(yè)、班級(jí)等字典信息，常規(guī)的設(shè)計(jì)方法是每個(gè)屬性設(shè)計(jì)一張字典表，如：

年級(jí)表

Create table T_Grade(

GradeID char(10),

GradeName varchar(100),

Primary key(GradeID))

專業(yè)表

Create table T_Major(

MajorID char(10),

MajorName varchar(100),

Primary key (MajorID))

此時(shí)只需對(duì)這些數(shù)據(jù)字典表進(jìn)行編輯即可，但是這樣的設(shè)計(jì)還會(huì)存在些問(wèn)題，這里專業(yè)代碼MajiorID是主鍵，不允許重復(fù)，且一旦被其他模塊選中后，則不允許再被修改，在實(shí)際軟件使用過(guò)程中經(jīng)常會(huì)有需要修改鍵值屬性的時(shí)候，比如“網(wǎng)絡(luò)系統(tǒng)管理”這個(gè)專業(yè)的專業(yè)代碼原來(lái)是“wlxtgl”，軟件經(jīng)使用后發(fā)現(xiàn)代碼太長(zhǎng)，不方便用戶記憶使用，需要將其縮短為wg,而按照以上字典設(shè)計(jì)就無(wú)法再進(jìn)行修改代碼。

當(dāng)然，數(shù)據(jù)庫(kù)中可以將這些關(guān)聯(lián)表的外鍵設(shè)置級(jí)聯(lián)更新，實(shí)現(xiàn)專業(yè)代碼更新，其他有關(guān)聯(lián)的模塊都級(jí)聯(lián)更新過(guò)來(lái)，可想而知，當(dāng)數(shù)據(jù)量少的時(shí)候級(jí)聯(lián)更新沒(méi)問(wèn)題，當(dāng)數(shù)據(jù)量大的時(shí)候，級(jí)聯(lián)更新就變得很慢，會(huì)造成極大的資源浪費(fèi)，而且在編輯數(shù)據(jù)字典時(shí)，主鍵的重復(fù)判斷也不好處理，有什么方法可以解決這些問(wèn)題呢？不妨看如下表設(shè)計(jì)結(jié)構(gòu)：

Create table T_Major(

MajorID int identity,

MajorCode char(10) unique,

MajorName varchar(100),

Primary key(MajorID))

以上設(shè)計(jì)是將主鍵用自動(dòng)增長(zhǎng)類型來(lái)實(shí)現(xiàn)，由數(shù)據(jù)庫(kù)來(lái)控制表中記錄的惟一性，此時(shí)程序就無(wú)需擔(dān)心表記錄的重復(fù)，而專業(yè)編號(hào)由另一個(gè)屬性MajorCode來(lái)表示，且該屬性設(shè)置成unique（惟一），在其他模塊關(guān)聯(lián)專業(yè)的主鍵MajorID，并不關(guān)聯(lián)MajorCode，程序可以通過(guò)視圖來(lái)讀取MajorCode，這樣就可以實(shí)現(xiàn)專業(yè)代碼允許用戶修改（即使專業(yè)代碼已被其他模塊關(guān)聯(lián)使用），似乎這樣的表結(jié)構(gòu)設(shè)計(jì)已經(jīng)滿足了我們數(shù)據(jù)字典的要求，編程人員也很方便實(shí)現(xiàn)，這樣的設(shè)計(jì)結(jié)構(gòu)并沒(méi)有真正發(fā)揮數(shù)據(jù)字典的特點(diǎn)。

上文提到，數(shù)據(jù)字典的數(shù)據(jù)特點(diǎn)是數(shù)據(jù)量少（相對(duì)其他模塊數(shù)據(jù)而言數(shù)據(jù)量相對(duì)少很多），屬性比較單一，一般只有主鍵和值組成。要是按照以上設(shè)計(jì)方法就會(huì)導(dǎo)致每個(gè)字典數(shù)據(jù)都要?jiǎng)?chuàng)建一張表，而且表中的記錄就幾十上百。例如“考試系統(tǒng)”中的專業(yè)表，一個(gè)學(xué)院乃至一個(gè)學(xué)校，最多也就上百個(gè)專業(yè)，用一張表來(lái)存儲(chǔ)較為浪費(fèi)，浪費(fèi)些磁盤空間沒(méi)關(guān)系，問(wèn)題是實(shí)際軟件中數(shù)據(jù)字典會(huì)非常多，從而就得創(chuàng)建很多表來(lái)管理數(shù)據(jù)字典。并且有一點(diǎn)非常被動(dòng)的就是，當(dāng)你要添加一個(gè)數(shù)據(jù)字典的時(shí)候，就要再添加一張表來(lái)支持。比如在財(cái)務(wù)軟件中經(jīng)常需要添加字典數(shù)據(jù)，要是按照這樣的方法來(lái)設(shè)計(jì)數(shù)據(jù)字典的話，那數(shù)據(jù)庫(kù)中會(huì)有百?gòu)埍矶际菙?shù)據(jù)字典，這會(huì)給開發(fā)人員就會(huì)帶來(lái)極大的不便。綜合以上分析，得出的結(jié)論就是得想辦法將多張數(shù)據(jù)字典表給合并到一個(gè)地方，統(tǒng)一來(lái)管理。由于數(shù)據(jù)量并不是很大，合并后的數(shù)據(jù)量對(duì)現(xiàn)有的數(shù)據(jù)庫(kù)軟件來(lái)說(shuō)也不會(huì)有太大壓力，可以考慮將多個(gè)數(shù)據(jù)字典表合并成一張表來(lái)處理，具體設(shè)計(jì)如下：

Create table T_DataDictionary(

DataDictionaryID int identity,

DataDIctionaryType char(10),

DataDictionaryCode char(10 ) unique,

DataDictionaryName varchar(100),

Primary key(DataDictionaryID))

在DataDictionaryType中存儲(chǔ)的就是數(shù)據(jù)字典的類型，比如“考試系統(tǒng)”中數(shù)據(jù)字典類型有：學(xué)期（Term）、年級(jí)（Grade）、專業(yè)（Major）、班級(jí)(Class)等等。在此數(shù)據(jù)字典的設(shè)計(jì)似乎可以告一段落，但實(shí)際軟件開發(fā)過(guò)程中遠(yuǎn)遠(yuǎn)不夠，數(shù)據(jù)之間會(huì)有復(fù)雜關(guān)系，比如年級(jí)與專業(yè)的關(guān)系，一個(gè)年級(jí)有那么多個(gè)專業(yè)，不同年級(jí)下的專業(yè)有可能不大相同，那在設(shè)計(jì)數(shù)據(jù)字典的時(shí)候怎么保證數(shù)據(jù)字典之間的關(guān)系呢？而且在實(shí)際使用過(guò)程中關(guān)系有可能嵌套，比如一個(gè)年級(jí)下有多個(gè)專業(yè)，一個(gè)專業(yè)下又有多個(gè)班級(jí)，難道我們要再通過(guò)兩張表來(lái)記錄他們之間的關(guān)系嗎？要是這樣的話，那關(guān)系多的話，豈不是關(guān)系表又越來(lái)越多，又回到了之前字典表太多的問(wèn)題上？這樣設(shè)計(jì)固然可用，但并不科學(xué)，有沒(méi)有辦法能夠?qū)崿F(xiàn)父與子關(guān)系的結(jié)構(gòu)（樹形結(jié)構(gòu)），這里我們就應(yīng)用父子表的概念來(lái)設(shè)計(jì)數(shù)據(jù)庫(kù)中數(shù)據(jù)字典之間的關(guān)系，表設(shè)計(jì)如下：

Create table T_DataRelation(

DataRelationID int not null,

ParentDataRelationID int null,

primary key (DataRelationID),

foreign key(DataRelationID) references T_DataDictionary(DataDictionaryID),

foreign key(ParentDataRelationID) references T_DataDictionary (DataDictionaryID));

程序可以通過(guò)一個(gè)遞歸算法來(lái)構(gòu)造出字典數(shù)據(jù)之間的關(guān)系，關(guān)系可以是多級(jí)樹形結(jié)構(gòu)也可以是一級(jí)平行結(jié)構(gòu)。在這里，我們數(shù)據(jù)字典的設(shè)計(jì)才算基本完成，當(dāng)然，在實(shí)際軟件實(shí)施過(guò)程中還會(huì)有些其他的功能需求，在設(shè)計(jì)上還需要加以調(diào)整，而一般軟件的數(shù)據(jù)字典通過(guò)以上兩張表來(lái)管理即已足夠。

可能會(huì)發(fā)現(xiàn)，為什么在設(shè)計(jì)表的時(shí)候Code列的字段類型都用char類型，而不用varchar類型，這因?yàn)閏har類型是固定長(zhǎng)類型，在檢索的時(shí)候比varchar類型要快的多，而且在SQLSever老版本的數(shù)據(jù)庫(kù)上用varchar類型做主鍵，數(shù)據(jù)量大時(shí)會(huì)導(dǎo)致檢索出錯(cuò)，所以一般比較固定的列或比較經(jīng)常要檢索的列的屬性都設(shè)置成固定長(zhǎng)度類型。

3 數(shù)據(jù)檢索技巧

數(shù)據(jù)庫(kù)中對(duì)數(shù)據(jù)的檢索有多種，一般會(huì)以視圖的形式表現(xiàn)出來(lái)，例如學(xué)生信息表。

學(xué)生表：

Create table T_Student(

StudentID int,

StudentCode char(12),

StudentName varchar(100),

MajorID int,

GradeID int,

ClassID int,

Primary key(StudentID))

學(xué)生視圖：

Create view V_Student

As

Select

a.StudentID,

a.StudentCode,

a.StudentName,

a.MajorID,

b.MajorName,

a.GradeID,

c.GradeName,

a.ClassID,

d.ClassName

From T_Student a

Left join T_Major b on a.MajorID = b.MajorID

Left join T_Grade c on a.GradeID = c.GradeID

Left join T_Class d on a.ClassID = d.ClassID

假設(shè)學(xué)生表經(jīng)多年使用后數(shù)據(jù)量非常龐大，要在其中找出專業(yè)名稱為“網(wǎng)絡(luò)系統(tǒng)管理”學(xué)生的所有信息，自然想到直接查詢視圖，條件鎖定在專業(yè)名稱，SQL語(yǔ)句如下：Select * from V_Student where MajorName =‘網(wǎng)絡(luò)系統(tǒng)管理’

若學(xué)生數(shù)據(jù)量并不多，使用視圖來(lái)檢索并不會(huì)慢，但是當(dāng)數(shù)據(jù)量大的時(shí)候檢索就會(huì)變得非常慢，其原因就在于視圖中左連接了三張數(shù)據(jù)字典表，若使用視圖來(lái)檢索，數(shù)據(jù)庫(kù)內(nèi)部的操作流程是首先對(duì)學(xué)生表中的所有記錄左連接這三張數(shù)據(jù)字典表，然后再去檢索專業(yè)名稱為“網(wǎng)絡(luò)系統(tǒng)管理”學(xué)生，檢索速度慢是因視圖內(nèi)部的左連接了所有的數(shù)據(jù)導(dǎo)致，應(yīng)該先找出指定專業(yè)的學(xué)生，然后左連接專業(yè)、年級(jí)、班級(jí)到這些指定專業(yè)的學(xué)生上，這樣就大大減少了左連接的數(shù)據(jù)量，因此類似于這樣的檢索數(shù)據(jù)就不能用視圖來(lái)完成，需要實(shí)時(shí)編寫SQL語(yǔ)句來(lái)滿足不同的需要，按照以上要求改造后的SQL語(yǔ)句如下：

Create view V_Student

As

Select

a.StudentID,

a.StudentCode,

a.StudentName,

a.MajorID,

b.MajorName,

a.GradeID,

c.GradeName,

a.ClassID,

d.ClassName

From(Select * from T_Student where StudentID=( Select MajorID from T_Major where MajorName ='網(wǎng)絡(luò)系統(tǒng)管理')) a

Left join T_Major b on a.MajorID = b.MajorID

Left join T_Grade c on a.GradeID = c.GradeID

Left join T_Class d on a.ClassID = d.ClassID

經(jīng)改進(jìn)后的SQL語(yǔ)句變得非常復(fù)雜，因此在實(shí)際開發(fā)過(guò)程中可以考慮用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)，將復(fù)雜的SQL語(yǔ)句寫入存儲(chǔ)過(guò)程中。如果經(jīng)常對(duì)某些列，比如學(xué)號(hào)列、專業(yè)列等要頻繁做檢索操作，可以考慮在其表上對(duì)這些列創(chuàng)建索引，這樣可以加快檢索索引。

4 結(jié)束語(yǔ)

數(shù)據(jù)庫(kù)的設(shè)計(jì)要結(jié)合軟件的需求來(lái)完成。隨著時(shí)間的推移，軟件的需求是不斷變化的，一個(gè)好的數(shù)據(jù)庫(kù)設(shè)計(jì)必須能適應(yīng)其需求的變化。當(dāng)然在很多情況下無(wú)法適應(yīng)，這時(shí)就要做詳細(xì)的分析，判斷是否重構(gòu)系統(tǒng)，如果前期設(shè)計(jì)得當(dāng)，將大大減輕軟件在后期的使用和維護(hù)方面的工作量，而目前許多軟件開發(fā)在前期細(xì)節(jié)方面投入不多，導(dǎo)致后期修改的工作量加大，這是本文討論數(shù)據(jù)庫(kù)設(shè)計(jì)中應(yīng)注意的細(xì)節(jié)問(wèn)題。

[1] 吳爽．軟件開發(fā)中的數(shù)據(jù)庫(kù)設(shè)計(jì)的理論和實(shí)踐[J]．計(jì)算機(jī)光盤軟件與應(yīng)用, 2011(6): 182.

[2] 李維. Delphi MDA/DDA程序設(shè)計(jì)——使用ECO[M]. 北京: 電子工業(yè)出版社, 2007.