1、成員：曹麗瓊馮繼偉劉維毅班級08軟工三班指導老師：郭東恩日期：2期1-5-22目錄1引言1.1 編寫目的本文檔是本項目開發中至關重要的一部分，通過本檔編寫，對本項目中相關數據存儲信息 進行明確定位，對數據結構等相關信息進行明確說明。本文檔盡可能的詳細說明各個實體的屬 性及相互聯系，使開發人員的開發效率盡可能增加。1.2 項目來源實訓要求開發人員：全體成員文檔使用者：oracle 開發人員、程序設計人員1.3 文檔約定1.4 預期讀者和閱讀建議開發人員：對oracle和java熟悉測試人員：對java及oracle有一定的了解1.5 參考資料1在線考試系統可行性分析2在線考試系統需求分析3項目（

2、bbs）數據庫設計文檔一劉維毅42 數據庫命名規則db_examonline:在線考試系統數據庫tb_invigilate:監考人員tb_user:學生或用戶信息表tb_select:選擇題相關信息表 tb_answer:簡答題相關信息 tb_boolean:判斷題相關信息tb_admin:管理員信息表tb_page:數據庫測試試題3.1 數據庫邏輯設計數據字典考生屬性：編號tid,姓名uname,登錄密碼upassword,學號uno,班 級uclass,考試狀態ustate監考人員屬性：編號tid,姓名name,登錄密碼password管理員屬性：編號aid,姓名name,登錄密碼pass

3、word試卷屬性：編號pid,科目subject,選擇題數量snum,判斷題數量bnum,問答題數量anum 考試時間pageDate選擇題屬性：編號id,題目name,選項A optionA,選項B optionB, 選項 C option C,選項 D optionD,所屬科目 subject判斷題屬性：編號id,題目name,對選項yes,錯選項no ,所屬科目 subject簡答題屬性：編號id,題目name,所屬科目subject科目屬性：編號sid,科目subject實體關系考試試卷題目E_R圖Stb_user(usid,uname,upassword,uno,uclass,ust

4、ate)監考人員關系表：Stb_invigilate(tid,name,password)管理員信息關系：Stb_admin(aid,name,password)考 試 試 卷 信 息 關 系：Stb_page(gid,pid,sub|ect,snUm,bnum,anum,pageDate) 選擇題信息關系：Stb_select(id,pid,subject,name,optionA,optionB,optionC,optionD)判斷題信息關系：Stb_boolean(id,pisubject,name,yes,no) 簡答題信息關系：Stb_answer(id,pid，subject,na

5、，e)科目信息關系：Stb_subject(sid,subject)3.2 數據庫物理設計暫無3.3 數據庫分布暫無3.4 基表設計在線考試系統數據庫：3.4.1. tb_user:考生信息表屬性：編號Tid,姓名name,登錄密碼password,學號number,班級class,考試 狀態state字段名稱數據類型長度主鍵描述Tidnumber10是主鍵標識unameVarchar250否學生姓名upasswordchar16否登錄密碼unonumber10否學號uclassVarchar250否班級ustatenumber1否考試狀態Create table tb_user(Tid nu

6、mber(10) primary key,Uname varchar2(50),Upassword char(16),Uno number(10),Uclass varchar2(50),Ustate number(1);3.4.2. tb_invigilate:監考人員信息表屬性：編號tid,姓名name,登錄密碼password字段名稱數據類型長度主鍵描述tidnumber10是主鍵標識nameVarchar250否姓名passwordchar16否登錄密碼Create table tb_invigilate(Tid number(10) primary key,Name varchar2

7、 (50),Password char(16) );3.4.3.tbadmin:管理員信息表屬性：編號aid,姓名name,登錄密碼password字段名稱數據類型長度主鍵描述aidnumber10是主鍵標識nameVarchar250否姓名passwordchar16否登錄密碼Create table tb_admin(Aid number(10) primary key,Name varchar2(50),Password char(16);1.1.4. tb_page:考試試卷信息表屬性：編號pid,科目subject, 選擇題數量snum,判斷題數量bnum,問答題數量anum,考試時

8、間pageDate字段名稱數據類型長度主鍵描述pidnumber10是主鍵標識subjectVarchar250否科目（外鍵）snumnumber2否選擇題數量bnumnumber2否判斷題數量anumnumber2否問答題數量pageDatedate/否考試時間tidnumber10是學生編號（外鍵）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),const

9、raint page_FK foreign key (subject) referencestb_subject(subject),constraint page_FK2 foreign key(tid) referencestb_user(tid);1.1.5. tb_select:選擇題信息表屬性：編號id,題目name,選項A optionA,選項B optionB,選項C option C, 選項D optionD,所屬科目subject字段名稱數據類型長度主鍵描述idnumber10是主鍵標識nameVarchar250否題目optionAVarchar2100否選項AoptionB

10、Varchar2100否選項BoptionCVarchar2100否選項CoptionDVarchar2100否選項DsubjectVarchar250否科目（外鍵）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),constr

11、aint select_FK foreign key(subject) referencestb_subject(subject),constraint select_FK2 foreign key(pid) referencestb_page(pid);1.1.6. tb_boolean:判斷題信息表屬性：編號id,題目name,對選項yes,錯選項no ,所屬科目subject字段名稱數據類型長度主鍵描述idnumber10是主鍵標識nameVarchar250否題目subjectVarchar250否科目（外鍵）pidnumber10否試卷編方（外鍵）Create table tb_bo

12、olean(id number(10) primary key,Name varchar2(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);1.1.7. tb_answer:簡答題信息表屬性：編號id,題目name,所屬科目subject字段名稱數據類型長度主鍵描述idnumber5是主鍵標

13、識nameVarchar250否題目subjectVarchar250否科目（外鍵）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) referencestb_subject(subject),constraint answer_FK2 foreign key(pid) referencestb_page(pid);1.

14、1.8. tb_subject:考試科目信息表屬性：編號sid,科目subject字段名稱數據類型長度主鍵描述sidnumber10否科目自增編號subjectVarchar250是主鍵標識Create table tb_subject(Sid number(10),Subject varchar2(50) primary key);/備注uid是關鍵字已被系統占用3.5 視圖設計3.6 索引設計3.7 序列設計設計目的：通過序列的設計，可以使得編號可以進行自增，從而達到設計目的。Tb_sq序列自動進行遞增初值為：1遞增為：1最大值：無Create sequence tb_sqIncremen

15、t by 1Start with 1NomaxvalueNocycleCache 10;Tb_invigilate_sq監考人員的tid 自動進行遞增Create sequence tb_invigilate_sqIncrement by 10Start with 10NomaxvalueNocycleCache 10;3.8 完整性設計請參閱本文檔的基表設計的外鍵設置3.9 授權設計3.10 觸發器設計Tb_user_triggerCreate or replace trigger tb_user_triggerBefore insert on tb_userFor each rowDecl

16、areNext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;/-insert into tb_user (uname, upassword,uno, uclass, ustate)-values()；Tb_invigilate_triggerCreate or replace trigger tb_invigilate_triggerBefore insert on tb_invigilateFor each rowDeclareNext_no number;BeginSelectInto next_noFrom dual;

17、:=next_no;End;Tb_admin_triggerCreate or replace trigger tb_admin_triggerBefore insert on tb_adminFor each rowDeclareNext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;Create or replace trigger tb_page_triggerBefore insert on tb_pageFor each rowDeclareNext_no number;BeginSelectInto next_no

18、From dual;:=next_no;End;Tb_select_triggerCreate or replace trigger tb_select_triggerBefore insert on tb_selectFor each rowDeclareNext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;Tb_boolean_triggerCreate or replace trigger tb_boolean_triggerBefore insert on tb_booleanFor each rowDeclareN

19、ext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;Tb_answer_triggerCreate or replace trigger tb_answer_triggerBefore insert on tb_answerFor each rowDeclareNext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;Tb_subject_triggerCreate or replace trigger tb_subject_triggerBefore ins

20、ert on tb_subjectFor each rowDeclareNext_no number;BeginSelectInto next_noFrom dual;:=next_no;End;3.11 存儲過程設計3.11.1. CRUDCUDE個操作-向試卷表中插入信息（create操作）Create or replace procedure insert_tb_page( p_subject in %type,p_snum in %type,p_bnum in %type,p_anum in %type,p_pageDate in %type,-插入系統時間sysdatep_tid i

21、n %type)asBeginInsert into tb_page(subject,snum,bnum,anum,pageDate,tid)values(p_subject,p_snum,p_bnum,p_anum,p_pageDate,p_tid);ExceptionWhen DUP_VAL_ON_INDEX then('subject primary key error!');When others then('異常)；Endinsert_tb_page;-執 行 過 程： executeinsert_tb_page( ,，,)；-向判斷題表中插入信息(creat

22、e操作)Create or replace procedure insert_tb_boolean(b_name in %type,b_subject in %type,b_pid in %type) asbeginInsert into tb_boolean(name, subject,pid) values(b_name,b_subject,b_pid);End insert_tb_boolean;- - 執行過程：execute insert_tb_boolean( '' ,' ' , );- -向簡答題表中插入信息Create or replace pr

23、ocedure insert_tb_answer(a_name in %type,a_subject in %type, a_pid in %type)asBeginInsert into tb_answer(name,subject,pid) values (a_name,a_subject,a_pid);End insert_tb_answer;- -向科目表中插入信息Create or replace procedure insert_tb_subject(s_subject in %type)asBeginInsert into tb_subject(subject) values (

24、s_subject);ExceptionWhen DUP_VAL_ON_INDEX then ('subject primary key error!');When others then('異常);End insert_tb_subject;-向選擇題表中插入信息Create or replace procedure insert_tb_select( s_name %type, s_optionA %type, s_optionB %type, s_optionC %type, s_optionD %type, s_subject %type, s_pid %type)asBeginInsert into tb_select(name, optionA, optionB, optionC,optionD, subject, pid)values(s_name,s_optionA,s_optionB,s_optionC,s_optionD,s_subject,s_pid);End insert_tb_select;-管理員信息插入Create or replace procedure