在線考試系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流在線考試系統(tǒng)數(shù)據(jù)庫設(shè)計(jì).精品文檔.數(shù)據(jù)庫設(shè)計(jì)文檔成員：曹麗瓊 馮繼偉 劉維毅 班級08軟工三班 指導(dǎo)老師：郭東恩 日 期：2011-5-22 目錄數(shù)據(jù)庫設(shè)計(jì)文檔1目錄2數(shù)據(jù)庫設(shè)計(jì)文檔31引言31.1編寫目的31.2項(xiàng)目來源31.3文檔約定31.4預(yù)期讀者和閱讀建議31.5參考資料32數(shù)據(jù)庫命名規(guī)則43數(shù)據(jù)庫設(shè)計(jì)說明53.1數(shù)據(jù)庫邏輯設(shè)計(jì)5數(shù)據(jù)詞典5實(shí)體關(guān)系6E_R圖10SR關(guān)系：103.2數(shù)據(jù)庫物理設(shè)計(jì)113.3數(shù)據(jù)庫分布113.4基表設(shè)計(jì)113.4.1.tb_user: 考生信息表123.4.2.tb_invigilate: 監(jiān)考人員信息表

2、133.4.3.tb_admin: 管理員信息表133.4.4.tb_page:考試試卷信息表143.4.5.tb_select:選擇題信息表153.4.6.tb_boolean:判斷題信息表163.4.7.tb_answer:簡答題信息表173.4.8.tb_subject:考試科目信息表183.5視圖設(shè)計(jì)193.6索引設(shè)計(jì)193.7序列設(shè)計(jì)193.8完整性設(shè)計(jì)203.9授權(quán)設(shè)計(jì)203.10觸發(fā)器設(shè)計(jì)203.11存儲過程設(shè)計(jì)243.11.1CRUD中的CUD三個操作243.12數(shù)據(jù)復(fù)制設(shè)計(jì)264查詢記錄語句265詞匯表276歷史數(shù)據(jù)處理27數(shù)據(jù)庫設(shè)計(jì)文檔1 引言1.1 編寫目的本文檔是本項(xiàng)目

3、開發(fā)中至關(guān)重要的一部分，通過本檔編寫，對本項(xiàng)目中相關(guān)數(shù)據(jù)存儲信息進(jìn)行明確定位，對數(shù)據(jù)結(jié)構(gòu)等相關(guān)信息進(jìn)行明確說明。本文檔盡可能的詳細(xì)說明各個實(shí)體的屬性及相互聯(lián)系，使開發(fā)人員的開發(fā)效率盡可能增加。1.2 項(xiàng)目來源Ø 實(shí)訓(xùn)要求Ø 開發(fā)人員：全體成員Ø 文檔使用者：oracle 開發(fā)人員、 程序設(shè)計(jì)人員1.3 文檔約定1.4 預(yù)期讀者和閱讀建議Ø 開發(fā)人員： 對oracle和java熟悉Ø 測試人員： 對java及oracle有一定的了解1.5 參考資料1 在線考試系統(tǒng)可行性分析2 在線考試系統(tǒng)需求分析3 項(xiàng)目（bbs）數(shù)據(jù)庫設(shè)計(jì)文檔劉維毅4 2 數(shù)據(jù)

4、庫命名規(guī)則Ø db_examonline: 在線考試系統(tǒng)數(shù)據(jù)庫Ø tb_invigilate: 監(jiān)考人員Ø tb_user: 學(xué)生或用戶信息表Ø tb_select:選擇題相關(guān)信息表Ø tb_answer:簡答題相關(guān)信息Ø tb_boolean:判斷題相關(guān)信息 Ø tb_admin: 管理員信息表Ø tb_page:數(shù)據(jù)庫測試試題3 數(shù)據(jù)庫設(shè)計(jì)說明3.1 數(shù)據(jù)庫邏輯設(shè)計(jì)數(shù)據(jù)字典l 考生屬性：編號tid,姓名uname,登錄密碼upassword,學(xué)號uno,班級uclass,考試狀態(tài)ustatel 監(jiān)考人員屬性：編

5、號tid,姓名name,登錄密碼passwordl 管理員屬性：編號aid,姓名name,登錄密碼passwordl 試卷屬性：編號pid,科目subject, 選擇題數(shù)量snum, 判斷題數(shù)量bnum, 問答題數(shù)量anum，考試時間pageDatel 選擇題屬性：編號id,題目name,選項(xiàng)A optionA,選項(xiàng)B optionB,選項(xiàng)C option C,選項(xiàng)D optionD,所屬科目subjectl 判斷題屬性：編號id,題目name,對選項(xiàng)yes,錯選項(xiàng)no ,所屬科目subjectl 簡答題屬性：編號id,題目name, 所屬科目subjectl 科目屬性：編號sid,科目sub

6、ject實(shí)體關(guān)系 編號姓名學(xué)號考試狀態(tài)登錄密碼班級考生監(jiān)考人員編號姓名登錄密碼管理員姓名登錄密碼編號考試試卷選擇題數(shù)目判斷題數(shù)目科目名稱簡答題數(shù)目編號編號題目選項(xiàng)A科目名稱選項(xiàng)D選項(xiàng)B選項(xiàng)C選擇題判斷題題目科目名稱編號考試時間簡答題題目科目名稱編號科目科目名稱編號E_R圖考生監(jiān)考人員管理員選擇題簡答題判斷題科目考試試卷組成監(jiān)考管理做組成SR關(guān)系：學(xué)生關(guān)系表：Stb_user(usid,uname,upassword,uno,uclass,ustate)監(jiān)考人員關(guān)系表:Stb_invigilate(tid,name,password)管理員信息關(guān)系:Stb_admin(aid,name,pass

7、word)考試試卷信息關(guān)系：Stb_page(usid,pid,subject,snum,bnum,anum,pageDate)選擇題信息關(guān)系：Stb_select(id,pid,subject,name,optionA,optionB,optionC,optionD)判斷題信息關(guān)系：Stb_boolean(id,pid,subject,name,yes,no)簡答題信息關(guān)系:Stb_answer(id,pid,subject,name)科目信息關(guān)系:Stb_subject(sid,subject)3.2 數(shù)據(jù)庫物理設(shè)計(jì)暫無3.3 數(shù)據(jù)庫分布暫無3.4 基表設(shè)計(jì)在線考試系統(tǒng)數(shù)據(jù)庫：3.4.1.

8、 tb_user: 考生信息表屬性：編號Tid,姓名name,登錄密碼password,學(xué)號number,班級class,考試狀態(tài)state字段名稱數(shù)據(jù)類型長度主鍵描述Tidnumber10是主鍵標(biāo)識unameVarchar250否學(xué)生姓名upasswordchar16否登錄密碼unonumber10否學(xué)號uclassVarchar250否班級ustatenumber1否考試狀態(tài)Create table tb_user( Tid number(10) primary key, Uname varchar2(50), Upassword char(16), Uno number(10), Ucl

9、ass varchar2(50), Ustate number(1)3.4.2. tb_invigilate: 監(jiān)考人員信息表屬性：編號tid,姓名name,登錄密碼password字段名稱數(shù)據(jù)類型長度主鍵描述tidnumber10是主鍵標(biāo)識nameVarchar250否姓名passwordchar16否登錄密碼Create table tb_invigilate( Tid number(10) primary key, Name varchar2 (50), Password char(16)3.4.3. tb_admin: 管理員信息表屬性：編號aid,姓名name,登錄密碼passwor

10、d字段名稱數(shù)據(jù)類型長度主鍵描述aidnumber10是主鍵標(biāo)識nameVarchar250否姓名passwordchar16否登錄密碼Create table tb_admin( Aid number(10) primary key, Name varchar2(50), Password char(16)3.4.4. tb_page:考試試卷信息表屬性：編號pid,科目subject, 選擇題數(shù)量snum, 判斷題數(shù)量bnum, 問答題數(shù)量anum，考試時間pageDate字段名稱數(shù)據(jù)類型長度主鍵描述pidnumber10是主鍵標(biāo)識subjectVarchar250否科目(外鍵)snumnu

11、mber2否選擇題數(shù)量bnumnumber2否判斷題數(shù)量anumnumber2否問答題數(shù)量pageDatedate/否考試時間tidnumber10是學(xué)生編號(外鍵) Create table tb_page( Pid number(10) primary key, Subject varchar2(50), Snum number(2), Bnum number(2), Anum number(2), pageDate date, tid number(10), constraint page_FK foreign key (subject) references tb_subject(su

12、bject), constraint page_FK2 foreign key(tid) references tb_user(tid)3.4.5. tb_select:選擇題信息表屬性：編號id,題目name,選項(xiàng)A optionA,選項(xiàng)B optionB,選項(xiàng)C option C,選項(xiàng)D optionD,所屬科目subject字段名稱數(shù)據(jù)類型長度主鍵描述idnumber10是主鍵標(biāo)識nameVarchar250否題目optionAVarchar2100否選項(xiàng)AoptionBVarchar2100否選項(xiàng)BoptionCVarchar2100否選項(xiàng)CoptionDVarchar2100否選項(xiàng)D

13、subjectVarchar250否科目(外鍵)pidnumber10是試卷編號(外鍵) Create table tb_select( Id number(10) primary key, Name varchar2(50), optionA varchar2(100), optionB varchar2(100), optionC varchar2(100), optionD varchar2(100), subject varchar2(50), pid number(10), constraint select_FK foreign key(subject) references tb

14、_subject(subject), constraint select_FK2 foreign key(pid) references tb_page(pid)3.4.6. tb_boolean:判斷題信息表 屬性：編號id,題目name,對選項(xiàng)yes,錯選項(xiàng)no ,所屬科目subject字段名稱數(shù)據(jù)類型長度主鍵描述idnumber10是主鍵標(biāo)識nameVarchar250否題目subjectVarchar250否科目(外鍵)pidnumber10否試卷編號(外鍵) Create table tb_boolean( id number(10) primary key, Name varcha

15、r2(50), Subject varchar2(50), Pid number(10), constraint boolean_FK foreign key(subject) references tb_subject(subject), constraint boolean_FK2 foreign key(pid) references tb_page(pid)3.4.7. tb_answer:簡答題信息表屬性：編號id,題目name, 所屬科目subject字段名稱數(shù)據(jù)類型長度主鍵描述idnumber5是主鍵標(biāo)識nameVarchar250否題目subjectVarchar250否科目(

16、外鍵)pidnumber10否試卷編號(外鍵)Create table tb_answer( Id number(5) primary key, Name varchar2(50), Subject varchar2(50), Pid number(10), constraint answer_FK foreign key(subject) references tb_subject(subject), constraint answer_FK2 foreign key(pid) references tb_page(pid)3.4.8. tb_subject:考試科目信息表 屬性：編號sid

17、,科目subject字段名稱數(shù)據(jù)類型長度主鍵描述sidnumber10否科目自增編號subjectVarchar250 是主鍵標(biāo)識 Create table tb_subject( Sid number(10) , Subject varchar2(50) primary key/備注uid是關(guān)鍵字已被系統(tǒng)占用3.5 視圖設(shè)計(jì)3.6 索引設(shè)計(jì)3.7 序列設(shè)計(jì)Ø 設(shè)計(jì)目的：通過序列的設(shè)計(jì)，可以使得編號可以進(jìn)行自增，從而達(dá)到設(shè)計(jì)目的。l Tb_sq 序列自動進(jìn)行遞增初值為：1遞增為：1最大值：無Create sequence tb_sqIncrement by 1Start with 1

18、NomaxvalueNocycleCache 10;l Tb_invigilate_sq監(jiān)考人員的 tid 自動進(jìn)行遞增Create sequence tb_invigilate_sqIncrement by 10Start with 10NomaxvalueNocycleCache 10;3.8 完整性設(shè)計(jì)請參閱本文檔的基表設(shè)計(jì)的外鍵設(shè)置3.9 授權(quán)設(shè)計(jì)3.10 觸發(fā)器設(shè)計(jì)l Tb_user_triggerCreate or replace trigger tb_user_triggerBefore insert on tb_userFor each rowDeclareNext_no nu

19、mber;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.tid:=next_no;End;-insert into tb_user (uname, upassword,uno, uclass, ustate)-values();l Tb_invigilate_triggerCreate or replace trigger tb_invigilate_triggerBefore insert on tb_invigilateFor each rowDeclareNext_no number;BeginSelect tb_sq.nextv

20、alInto next_noFrom dual;:new.tid:=next_no;End;l Tb_admin_triggerCreate or replace trigger tb_admin_triggerBefore insert on tb_adminFor each rowDeclareNext_no number;Begin Select tb_sq.nextvalInto next_noFrom dual;:new.aid:=next_no;End;l Tb_page_triggerCreate or replace trigger tb_page_triggerBefore

21、insert on tb_pageFor each rowDeclareNext_no number;Begin Select tb_sq.nextvalInto next_noFrom dual;:new.pid:=next_no;End;l Tb_select_triggerCreate or replace trigger tb_select_triggerBefore insert on tb_selectFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.id:=n

22、ext_no;End;l Tb_boolean_triggerCreate or replace trigger tb_boolean_triggerBefore insert on tb_booleanFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.id:=next_no;End;l Tb_answer_triggerCreate or replace trigger tb_answer_triggerBefore insert on tb_answerFor each

23、 rowDeclareNext_no number;Begin Select tb_sq.nextvalInto next_noFrom dual;:new.id:=next_no;End;l Tb_subject_triggerCreate or replace trigger tb_subject_triggerBefore insert on tb_subjectFor each rowDeclare Next_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.sid:=next_no;End;3.11 存儲過程設(shè)

24、計(jì)3.11.1 CRUD中的CUD三個操作-向試卷表中插入信息(create操作)Create or replace procedure insert_tb_page(p_subject in tb_page.subject%type,p_snum in tb_page.snum%type,p_bnum in tb_page.bnum%type,p_anum in tb_page.anum%type,p_pageDate in tb_page.pageDate%type,-插入系統(tǒng)時間sysdatep_tid in tb_page.tid%type)asBeginInsert into tb_

25、page(subject,snum,bnum,anum,pageDate,tid) values(p_subject,p_snum,p_bnum,p_anum,p_pageDate,p_tid);Exception When DUP_VAL_ON_INDEX thenDbms_output.put_line('subject primary key error!');When others thenDbms_output.put_line('異常');End insert_tb_page;-/執(zhí)行過程: execute insert_tb_page(,);-向判

26、斷題表中插入信息(create操作)Create or replace procedure insert_tb_boolean(b_name in tb_%type,b_subject in tb_boolean.subject%type,b_pid in tb_boolean.pid%type) asbeginInsert into tb_boolean(name, subject,pid) values(b_name,b_subject,b_pid);End insert_tb_boolean;-/執(zhí)行過程: execute insert_tb_boolean(,)

27、;-向簡答題表中插入信息Create or replace procedure insert_tb_answer(a_name in tb_%type,a_subject in tb_answer.subject%type,a_pid in tb_answer.pid%typeas BeginInsert into tb_answer(name,subject,pid) values(a_name,a_subject,a_pid);End insert_tb_answer;-向科目表中插入信息Create or replace procedure insert_tb_su

28、bject(s_subject in tb_subject.subject%type)asBeginInsert into tb_subject(subject) values(s_subject);Exception When DUP_VAL_ON_INDEX thenDbms_output.put_line('subject primary key error!');When others thenDbms_output.put_line('異常');End insert_tb_subject;-向選擇題表中插入信息Create or replace pro

29、cedure insert_tb_select(s_name tb_%type,s_optionA tb_select.optionA%type,s_optionB tb_select.optionB%type,s_optionC tb_select.optionC%type,s_optionD tb_select.optionD%type,s_subject tb_select.subject%type,s_pid tb_select.pid%type)asBegin Insert into tb_select(name, optionA, optionB, optionC, optionD, subject, pid) values(s_name,s_optionA,s_optionB,s_optionC,s_optionD,s_subje