在线考试系统数据库设计

1、数据库设计文档成员：曹丽琼 冯继伟刘维毅班级08软工三班指导老师：郭东恩日期：2011-5-22目录数据库设计文档1目录2数据库设计文档31 引言31.1 编写目的31.2 项目来源31.3 文档约定31.4 预期读者和阅读建议 31.5 参考资料32 数据库命名规则 33 数据库设计说明53.1 数据库逻辑设计5数据词典5实体关系5E_R 图9SR关系：93.2 数据库物理设计103.3 数据库分布103.4 基表设计103.4.1. tb_user:考生信息表 113.4.2. tb_invigilate:监考人员信息表123.4.3. tb_admin:管理员信息表 123.4.4. t

2、b_page:考试试卷信息表 133.4.5. tb_select:选择题信息表 143.4.6. tb_boolean:判断题信息表153.4.7. tb_answer:简答题信息表163.4.8. tb_subject:考试科目信息表 173.5 视图设计183.6 索引设计183.7 序列设计183.8 完整性设计193.9 授权设计193.10 触发器设计 193.11 存储过程设计243.11.1CRUD中的CUD三个操作 243.12 数据复制设计 284 查询记录语句285 词汇表296 历史数据处理303数据库设计文档1引言1.1编写目的本文档是本项目开发中至关重要的一部分，通

3、过本档编写，对本项目中相关数据存储信息进行明确定位，对数据结构等相关信息进行明确说明。本文档尽可能 的详细说明各个实体的属性及相互联系，使开发人员的开发效率尽可能增加。1.2项目来源实训要求开发人员：全体成员文档使用者：oracle开发人员、程序设计人员1.3文档约定1.4预期读者和阅读建议开发人员：对oracle和java熟悉测试人员：对java及oracle有一定的了解1.5参考资料1在线考试系统可行性分析2 在线考试系统需求分析3项目（bbs）数据库设计文档一刘维毅4 2数据库命名规则db_examonline:在线考试系统数据库tb_invigilate:监考人员5tb_user:学生

4、或用户信息表 tb_select选择题相关信息表tb_answer简答题相关信息 tb_boolean判断题相关信息tb_admin:管理员信息表 tb_page数据库测试试题303数据库设计说明3.1数据库逻辑设计数据字典考生属性：编号tid,姓名uname,登录密码upassword,学 号uno,班级uclass,考试状态ustate监考人员属性：编号tid,姓名name,登录密码password管理员属性：编号aid,姓名name,登录密码password试卷属性：编号pid,科目subject, 选择题数量snum,判断题数量bnum,问答题数量anum 考试时间pageDate选择

5、题属性：编号 id,题目name选项 A optionA,选项 BoptionB选项 C option C,选项 D optionD,所属科目 subject判断题属性：编号id,题目name对选项yes,错选项no ,所属科目subject简答题属性：编号id,题目name,所属科目subject科目属性：编号sid,科目subject实体关系E_R图S R关系：学 生 关 系 表Stb_user(usid,uname,upassword,uno,uclass,ustate)监考人员关系表:Stb_invigilate(tid,name,password)管理员信息关系:Stb_admin(

6、aid,name,password)Stb_page(us%,pid考 试 试 卷 信 息 关 系 ：,subject,snum,bnum,anum,pageDate)选择题信息关系：Stb_select(id|pid,subjeqt,name,optionA,optionB,optionC,optionD)判断题信息关系：Stb_boolean(id,pid,subjet,name,yes,no)简答题信息关系:Stb_answer(id,pid,subject,name)科目信息关系:Stb_subject(sid,subjeCt)3.2数据库物理设计暂无3.3数据库分布暂无3.4基表设计

7、在线考试系统数据库:3.4.1. tb_user:考生信息表属性：编号Tid,姓名name,登录密码password，学号number,班级class,考试状态state字段名称数据类型长度主键描述Tidnumber10是主键标识unameVarchar250否学生姓名upasswordchar16否登录密码unonumber10否学号uclassVarchar250否班级ustatenumber1否考试状态Create table tb_user(Tid number(10) primary key,Uname varchar2(50),Upassword char(16),Uno numb

8、er(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 (50),Password char(16);3.4.3. tb_admin:管理员信息表属性：编号aid,姓名name,登录密码p

9、assword字段名称数据类型长度主键描述aidnumber10是主键标识nameVarchar250否姓名passwordchar16否登录密码Create table tb_admin(Aid number(10) primary key,Name varchar2(50),Password char(16)）；3.4.4. tb_page:考试试卷信息表属性：编号pid,科目subject,选择题数量snum,判断题数量bnum,问答题数量anum 考试时间pageDate字段名称数据类型长度主键描述pidnumber10是主键标识subjectVarchar250否科目（外键）snum

10、number2否选择题数量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),constraint page_FK foreign key (subject) references tb_subject(subject),

11、constraint page_FK2 foreign key(tid) references tb_user(tid);3.4.5. tb_select选择题信息表属性：编号id,题目name选项A optionA,选项B optionB,选项Coption C,选项 D optionD,所属科目 subject字段名称数据类型长度主键描述idnumber10是主键标识nameVarchar250否题目optionAVarchar2100否选项AoptionBVarchar2100否选项BoptionCVarchar2100否选项CoptionDVarchar2100否选项DsubjectV

12、archar250否科目（外键）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) referencestb_subject(subject),c

13、onstraint select_FK2 foreign key(pid) referencestb_page(pid);3.4.6. tb boolean:判断题信息表属性：编号id,题目name对选项yes,错选项no ,所属科目subject字段名称数据类型长度主键描述idnumber10是主键标识nameVarchar250否题目subjectVarchar250否科目（外键）pidnumber10否试履编p （外键）Create table tb_boolean(id number(10) primary key,Name varchar2(50),Subject varchar2(

14、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字段名称数据类型长度主键描述idnumber5是主键标识nameVarchar250否题目subjectVarchar250否科目（外键）pidnumber10否试履编p （外键）Crea

15、te 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);348 tb_subject:考试科目信息表属性:编号sid,科目subject字段名称数据类型长度主键描述sidnumber10否

16、科目自增编号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_sqIncrement by 1Start with 1NomaxvalueNocycleCache 10;Tb_invigilate_sq监考人员的tid白

17、动进行递增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 rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.tid

18、:=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;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.tid:=next_no;End;Tb_admin_trigge

19、rCreate or replace trigger tb_admin_triggerBefore insert on tb_adminFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.aid:=next_no;End;Tb_page_triggerCreate or replace trigger tb_page_triggerBefore insert on tb_pageFor each rowDeclareNext_no number;BeginSelect tb_

20、sq.nextvalInto next_noFrom dual;:new.pid:=next_no;End;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:=next_no;End;Tb_boolean_triggerCreate or replace trigger tb_boolean_tri

21、ggerBefore insert on tb_booleanFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.id:=next_no;End;Tb_answer_triggerCreate or replace trigger tb_answer_triggerBefore insert on tb_answerFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;

22、:new.id:=next_no;End;Tb_subject_triggerCreate or replace trigger tb_subject_triggerBefore insert on tb_subjectFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.sid:=next_no;End;3.11存储过程设计3.11.1 CRUD中的CUD三个操作-向试卷表中插入彳息(create操作)Create or replace procedure insert_tb

23、_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, -插入系统时间sysdate p_tid in tb_page.tid%type )asBeginInsert into tb_page(subject,snum,bnum,anum,pageDate,tid) values(p_subject,p_snum,p_bn

24、um,p_anum,p_pageDate,p_tid);ExceptionWhen DUP_VAL_ON_INDEX thenDbms_output.put_line('subject primary key error!');When others thenDbms_output.put_line('异常');End insert_tb_page;-/ 执 行过程： execute insert_tb_page( :' ,');-向判断题表中插入信息(create操作)Create or replace procedure insert_tb_

25、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;-/执行过程：execute insert_tb_boolean(丁，')；-向简答题表中插入信息Create or replace procedure insert_tb_a

26、nswer(a_name in tb_%type,a_subject in tb_answer.subject%type,a_pid in tb_answer.pid%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 tb_subject.subject%type)asBeginI

27、nsert into tb_subject(subject) values(s_subject);ExceptionWhen 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 procedure insert_tb_select(s_name tb_%type,s_opti

28、onA 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)asBeginInsert into tb_select(name, optionA, optionB, optionC, optionD, subject, pid)values(s_name,s_optionA,s_optionB,s_optionC,s_option D,s_subject,s_pid);End insert_tb_select;-管理员信息插入Crea