劉錦培

?

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

劉錦培1,2

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

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

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

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

1 主外鍵的使用

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

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

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))

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

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

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

年級表

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

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

Create table T_Major(

MajorID int identity,

MajorCode char(10) unique,

MajorName varchar(100),

Primary key(MajorID))

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

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

Create table T_DataDictionary(

DataDictionaryID int identity,

DataDIctionaryType char(10),

DataDictionaryCode char(10 ) unique,

DataDictionaryName varchar(100),

Primary key(DataDictionaryID))

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

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));

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

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

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

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

學生表：

Create table T_Student(

StudentID int,

StudentCode char(12),

StudentName varchar(100),

MajorID int,

GradeID int,

ClassID int,

Primary key(StudentID))

學生視圖：

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

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

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)改進后的SQL語句變得非常復雜，因此在實際開發(fā)過程中可以考慮用存儲過程來實現(xiàn)，將復雜的SQL語句寫入存儲過程中。如果經(jīng)常對某些列，比如學號列、專業(yè)列等要頻繁做檢索操作，可以考慮在其表上對這些列創(chuàng)建索引，這樣可以加快檢索索引。

4 結(jié)束語

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

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

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