三層架構(gòu)數(shù)據(jù)庫設計

曹樹貴+盧志舟+劉娟+高振華

摘要摘要：以河北省教育考試統(tǒng)計分析系統(tǒng)為例，結(jié)合系統(tǒng)面向考試種類多、同類考試考次多、單次考試數(shù)據(jù)量大、各類考試基礎(chǔ)字典數(shù)據(jù)及統(tǒng)計分析需求存在差異等特點，介紹了一種三層架構(gòu)的數(shù)據(jù)庫設計方案，包括：頂層公共數(shù)據(jù)庫、第二層考試類別公共數(shù)據(jù)庫、第三層考次成績數(shù)據(jù)庫。對各層數(shù)據(jù)庫的表對象（Table）構(gòu)成進行分析，并提出了跨庫建立視圖（View）以及存儲過程中使用動態(tài)SQL語句的多層數(shù)據(jù)庫跨庫訪問方法。

關(guān)鍵詞關(guān)鍵詞：教育考試；考試數(shù)據(jù)；數(shù)據(jù)庫；三層架構(gòu)

DOIDOI：10.11907/rjdk.162228

中圖分類號：TP392文獻標識碼：A文章編號文章編號：16727800（2017）001014004

引言

三層架構(gòu)數(shù)據(jù)庫指將數(shù)據(jù)分布在不同層次的數(shù)據(jù)庫上，分層依據(jù)如按類型、層次、粒度、用途、使用環(huán)境等。本文以河北省教育考試統(tǒng)計分析系統(tǒng)為例，介紹了一種三層架構(gòu)數(shù)據(jù)庫設計方案。該系統(tǒng)旨在對各類終結(jié)性教育考試和學生在校的過程性考試成績進行統(tǒng)計分析，并針對不同的服務對象（學生、教師、學校、教研部門、教育行政部門）提供針對性的統(tǒng)計分析反饋報告[1]。系統(tǒng)具有面向考試種類多、同類考試考次多，單次考試數(shù)據(jù)量大、各類考試基礎(chǔ)字典數(shù)據(jù)差異化以及統(tǒng)計分析需求存在差異等特點。因此，在數(shù)據(jù)庫設計上需要基于這些特點采用針對性的解決方案。

1三層數(shù)據(jù)庫架構(gòu)

教育考試數(shù)據(jù)統(tǒng)計分析系統(tǒng)在數(shù)據(jù)庫設計上具有單一數(shù)據(jù)庫架構(gòu)和多數(shù)據(jù)庫架構(gòu)兩種解決方案。單一數(shù)據(jù)庫架構(gòu)指各類考試數(shù)據(jù)集中在一個數(shù)據(jù)庫中，將同類數(shù)據(jù)存放在同一張數(shù)據(jù)表下，例如，各次考試的考生數(shù)據(jù)與各類考試的考試成績數(shù)據(jù)各存放在一張數(shù)據(jù)庫表中。多數(shù)據(jù)庫架構(gòu)則是將考試數(shù)據(jù)按一定規(guī)則分布在多個數(shù)據(jù)庫中。

單一數(shù)據(jù)庫架構(gòu)的優(yōu)點是針對數(shù)據(jù)庫的編程邏輯相對簡單，便于多次考試統(tǒng)計分析對比等，但缺點也顯而易見。首先，數(shù)據(jù)庫表記錄行數(shù)龐大造成效率低下問題。由于系統(tǒng)需要管理多種類型的考試，每類考試又存在多次考試，單次考試數(shù)據(jù)量巨大，所以隨著時間推移，數(shù)據(jù)庫將存儲累積海量數(shù)據(jù)。單表行數(shù)規(guī)模很容易達到千萬級或億級以上規(guī)模，使數(shù)據(jù)統(tǒng)計分析效率受到嚴重影響。另外，數(shù)據(jù)集中在單一數(shù)據(jù)庫中也不利于解決不同類型考試在基礎(chǔ)數(shù)據(jù)及統(tǒng)計分析需求方面的差異化問題。

綜上，教育考試數(shù)據(jù)統(tǒng)計分析系統(tǒng)宜于采用多數(shù)據(jù)庫架構(gòu)。鑒于教育考試數(shù)據(jù)統(tǒng)計分析系統(tǒng)管理的考試有多種類別，每類考試既有共用數(shù)據(jù)，又有獨有數(shù)據(jù)。同時每類考試又有多次考試，每次考試同樣有該類考試的共用數(shù)據(jù)及獨有數(shù)據(jù)，因此可采用三層數(shù)據(jù)庫架構(gòu)，把不同類型的考試數(shù)據(jù)分布在不同層次的數(shù)據(jù)庫上。三層數(shù)據(jù)庫架構(gòu)由頂層TOP公共庫、第二層類別公共庫、第三層考次成績數(shù)據(jù)庫組成，如圖1所示。

第一層，頂層公共庫。用于存放各類型考試的公用數(shù)據(jù)，包括公用基礎(chǔ)字典數(shù)據(jù)以及考次管理數(shù)據(jù)。頂層公共庫可命名為：COMMOM_TOP。

第二層，類別公共庫。例如高考公共庫、高中過程性考試公共庫、自學考試公共庫、全國英語等級考試公共庫、全國計算機等級考試公共庫等，用于存放同一類型各次考試的公用數(shù)據(jù)，以及各次考試的統(tǒng)計匯總數(shù)據(jù)。類別數(shù)據(jù)庫的命名方式為：COMMON_考試類別。例如，高考公共庫命名為：COMMON_01，“01”為高考編碼。

第三層，考次成績數(shù)據(jù)庫，用于存放某次考試的考生、試卷、成績等數(shù)據(jù)。例如，2015年高考、2016年高考是高考的不同考次，考試成績數(shù)據(jù)分別保存在不同的數(shù)據(jù)庫中；石家莊高考模擬考試一、石家莊高考模擬考試二、石家莊高考模擬考試三是高中過程性考試的三次不同的考次，考試數(shù)據(jù)也分別存放在不同數(shù)據(jù)庫中。考次成績數(shù)據(jù)庫的命名方式為：KC_考試年度_考試類型_序號。例如，2016年高考的數(shù)據(jù)庫命名為：KC_2016_01_01，名稱中第一個“01”代表高考，第二個“01”代表考試序號。

2各層數(shù)據(jù)庫構(gòu)成設計

2.1頂層公共庫

頂層公共數(shù)據(jù)庫存放各類型考試的共用數(shù)據(jù)，這些數(shù)據(jù)包括公用基礎(chǔ)字典數(shù)據(jù)以及考次管理數(shù)據(jù)。

基礎(chǔ)字典數(shù)據(jù)包括：考試類型、學校類別、試卷類型、題型、性別、行政區(qū)劃等，如表1所示。

考次成績數(shù)據(jù)庫位于第三層，用于存放某次考試的考生、試卷、成績等數(shù)據(jù)[2]。以下仍以高考考次成績數(shù)據(jù)庫為例，高考考次成績數(shù)據(jù)庫的表對象主要由考生信息表、考生成績表、試卷信息表三大類構(gòu)成，其中考生成績表又可分為總成績表以及單科成績表，試卷信息表可以分為試題信息表、試卷知識結(jié)構(gòu)表、試卷能力結(jié)構(gòu)表（見表7）。圖2反映了高考考次成績數(shù)據(jù)庫中各表之間的關(guān)聯(lián)關(guān)系。

表7高考考次庫表對象（部分）表名稱表內(nèi)容考生信息（ksxx）存放每個考生的個人信息，字段如：考生號、姓名、性別、身份證號、報名點學校、班級、考生類別、科類等考生成績（zcj）存放每個考生的總成績信息，字段如：考生號、總成績考生單科成績（dkcj_x）按科目存放每個考生的單科成績信息，dkcj_x中隨著科目不同而不同，如語文對應表名為dkcj_00，英語為dkcj_01……，字段如：考生號、單科總分、試題01得分、試題02得分、試題03得分……試題信息（stxx）存放每個科目的試題信息，字段如：科目、試題編號、試題分值、試題答案、主客觀標志、選作標志等試卷知識結(jié)構(gòu)（sjzsjg）存放每個科目各試題對應的知識點，字段如：科目、試題編號、知識點編號試卷能力結(jié)構(gòu)（sjnljg）存放每個科目各試題對應的能力點，字段如：科目、試題編號、能力點編號圖2高考考次成績數(shù)據(jù)庫主要表對象關(guān)系

3多層數(shù)據(jù)庫跨庫訪問

三層數(shù)據(jù)庫架構(gòu)將數(shù)據(jù)分布在不同層次的數(shù)據(jù)庫中，可有效解決前文所述的若干問題，但是將有一定關(guān)聯(lián)關(guān)系的表分布在不同數(shù)據(jù)庫中，也會帶來訪問上的不便。例如，考次成績數(shù)據(jù)庫中相應表的代碼字段名稱需要在頂層公共庫或類別公共庫訪問到字典表才能獲得；用于數(shù)據(jù)統(tǒng)計分析的存儲過程以及數(shù)據(jù)統(tǒng)計結(jié)果分布在第二層類別公共庫中，而統(tǒng)計數(shù)據(jù)來源于考次成績庫，需要有效解決跨庫操作問題。針對以上問題，后文提供兩種解決方法，分別為建立跨庫視圖以及在存儲過程中使用動態(tài)SQL。

需要說明的是，河北省教育考試統(tǒng)計分析系統(tǒng)采用的是Mysql數(shù)據(jù)庫系統(tǒng)，所以后文所述的SQL語句為基于Mysql數(shù)據(jù)庫的語法。

3.1跨庫視圖建立

數(shù)據(jù)庫視圖（View）是基于多個表或遠程表的虛擬表，是用戶可以看見的虛關(guān)系。視圖能提高數(shù)據(jù)庫應用的靈活性，減少用戶對數(shù)據(jù)庫物理結(jié)構(gòu)的依賴，同時產(chǎn)生比邏輯模型更符合用戶需要的關(guān)系模型視圖[3]。利用視圖可以很好地解決數(shù)據(jù)表與字典表的連接問題，例如，將COMMON_TOP中的考試類型（KSLX）表引入為高考考次庫（KC_2016_01_01）的視圖V_KSLX，其創(chuàng)建語句為：

同樣，可以將頂層公共庫以及類型公共庫的其它字典表引入到考次庫，從而方便數(shù)據(jù)之間的關(guān)聯(lián)訪問。

3.2動態(tài)SQL

為了減輕客戶端與數(shù)據(jù)庫系統(tǒng)間網(wǎng)絡I/O的負載，從而提高數(shù)據(jù)分析效率，系統(tǒng)采用存儲過程來完成數(shù)據(jù)的查詢處理[4]。如果存儲過程和所訪問的數(shù)據(jù)表在同一個數(shù)據(jù)庫下，則在存儲過程中可以直接書寫SQL語句完成計算。例如在考高考次庫（KC_2016_01_01）中建立一個存儲過程sp_ks_tj，統(tǒng)計考生相關(guān)信息，其中包括對考生人數(shù)的統(tǒng)計，則存儲過程中可直接書寫如下SQL語句完成人數(shù)統(tǒng)計：

SELECT COUNT（1） FROM ksxx

而如果存儲過程建立在高考公共庫（COMMON_01）中，此時要訪問考次庫KC_2016_01_01中的考生人數(shù)，則SQL語句中ksxx表需要加上考次成績數(shù)據(jù)庫名稱作為前綴，即：KC_2016_01_01.ksxx，而該存儲過程需要適用于各個高考考次庫，所以ksxx表的數(shù)據(jù)庫前綴應該為變量（如@db），整個SQL語句應該為字符串變量（如@sql）。因此，在存儲過程中需要構(gòu)造字符串變量@sql，構(gòu)造方法如下：

set @sql=CONCAT（"SELECT COUNT（1） into @result FROM "，ls_db，".ksxx"）；

此時，雖然SQL語句已經(jīng)構(gòu)造出來，但僅僅是存放在字符串變量@sql中，還需要有一種機制來執(zhí)行變量@sql所存儲的語句，這里采用MYSQL數(shù)據(jù)庫的prepare預處理語句組。MySQL prepare預處理語句組由預處理定義語句、預處理執(zhí)行語句、預處理刪除語句構(gòu)成[5]。3個語句的簡化語法格式如下所示：4結(jié)語

以河北省教育考試統(tǒng)計分析系統(tǒng)為例，結(jié)合系統(tǒng)面向考試種類多、同類考試考次多、單次考試數(shù)據(jù)量大、各類考試基礎(chǔ)字典數(shù)據(jù)及統(tǒng)計分析需求存在差異等特點，介紹了一種三層架構(gòu)的數(shù)據(jù)庫設計方案，包括頂層公共數(shù)據(jù)庫、類別公共庫、考次成績數(shù)據(jù)庫。頂層公共庫用于存放各類型考試的公用數(shù)據(jù)；類別公共庫存放同一類型各次考試的公用數(shù)據(jù)，以及各次考試的統(tǒng)計數(shù)據(jù)；考次成績數(shù)據(jù)庫用于存放某次考試的考生信息、試卷信息、成績信息等數(shù)據(jù)。針對分層架構(gòu)的跨庫訪問，可以采用建立跨庫視圖以及在存儲過程中執(zhí)行動態(tài)SQL語句的方法。

三層架構(gòu)的數(shù)據(jù)庫設計方案已在河北省教育考試分析系統(tǒng)開發(fā)中得到實施，數(shù)據(jù)處理效率得到了有效提高，不同類型考試的差異化更易于處理。河北省教育考試系統(tǒng)數(shù)據(jù)庫目前是基于單臺數(shù)據(jù)庫服務器，隨著系統(tǒng)管理的考試類型逐步豐富，考次數(shù)量增加，以及考生個性化評測報告查詢并發(fā)數(shù)量的增加，可逐步過渡到基于多臺數(shù)據(jù)庫服務器的數(shù)據(jù)庫集群模式，而分層數(shù)據(jù)庫架構(gòu)更易于向集群模式過渡[6]。

參考文獻：

[1]劉娟，高振華.化枯燥為神奇——高考數(shù)據(jù)統(tǒng)計分析報告“把脈”中學教學[J].考試與招生，2010（12）：5354.

[2]張向兵.考試相關(guān)數(shù)據(jù)標準和統(tǒng)計分析[D].天津：天津大學，2007.

[3]湯國華，葉丹，徐罡，等.基于設計模式的通用數(shù)據(jù)庫視圖生成方法[J].計算機輔助工程，2008（1）：7276.

[4]支春明.影響數(shù)據(jù)庫性能的因素分析及改進策略[J].中國高新技術(shù)企業(yè)，2008（23）： 152.

[5]SQL syntax for prepared statements[EB/OL].http：//dev.mysql.com/doc/refman/5.7/en/sqlsyntaxpreparedstatements.html.