1、在线考试系统数据库设计 Document number:WTWYT-WYWY-BTGTT-YTTYU-2018GT在线考试系统数据库设计数据库设计文档成员:曹丽琼 冯继伟 刘维毅 班级08软工三班 指导老师:郭东恩 日 期:2011-5-22 数据库设计文档1引言1.1编写目的本文档是本项目开发中至关重要的一部分,通过本档编写,对本项目中相关数据存储信息进行明确定位,对数据结构等相关信息进行明确说明。本文档尽可能的详细说明各个实体的属性及相互联系,使开发人员的开发效率尽可能增加。1.2项目来源实训要求开发人员:全体成员文档使用者:oracle 开发人员、 程序设计人员1.3文档约定1.4预期读
2、者和阅读建议开发人员: 对oracle和java熟悉测试人员: 对java及oracle有一定的了解1.5参考资料1 在线考试系统可行性分析2 在线考试系统需求分析3 项目(bbs)数据库设计文档刘维毅4 2数据库命名规则db_examonline: 在线考试系统数据库tb_invigilate: 监考人员tb_user: 学生或用户信息表tb_select:选择题相关信息表tb_answer:简答题相关信息tb_boolean:判断题相关信息 tb_admin: 管理员信息表tb_page:数据库测试试题3数据库设计说明3.1数据库逻辑设计数据字典考生属性:编号tid,姓名uname,登录密
3、码upassword,学号uno,班级uclass,考试状态ustate监考人员属性:编号tid,姓名name,登录密码password管理员属性:编号aid,姓名name,登录密码password试卷属性:编号pid,科目subject, 选择题数量snum, 判断题数量bnum, 问答题数量anum,考试时间pageDate选择题属性:编号id,题目name,选项A optionA,选项B optionB,选项C option C,选项D optionD,所属科目subject判断题属性:编号id,题目name,对选项yes,错选项no ,所属科目subject简答题属性:编号id,题目n
4、ame, 所属科目subject科目属性:编号sid,科目subject实体关系 E_R图SR关系:学生关系表:Stb_user(usid,uname,upassword,uno,uclass,ustate)监考人员关系表:Stb_invigilate(tid,name,password)管理员信息关系:Stb_admin(aid,name,password)考试试卷信息关系:Stb_page(usid,pid,subject,snum,bnum,anum,pageDate)选择题信息关系:Stb_select(id,pid,subject,name,optionA,optionB,optio
5、nC,optionD)判断题信息关系:Stb_boolean(id,pid,subject,name,yes,no)简答题信息关系:Stb_answer(id,pid,subject,name)科目信息关系:Stb_subject(sid,subject)3.2数据库物理设计暂无3.3数据库分布暂无3.4基表设计在线考试系统数据库:3.4.1.tb_user: 考生信息表属性:编号Tid,姓名name,登录密码password,学号number,班级class,考试状态state字段名称数据类型长度主键描述Tidnumber10是主键标识unameVarchar250否学生姓名upasswor
6、dchar16否登录密码unonumber10否学号uclassVarchar250否班级ustatenumber1否考试状态Create table tb_user( Tid number(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是主键标识nameV
7、archar250否姓名passwordchar16否登录密码Create table tb_invigilate( Tid number(10) primary key, Name varchar2 (50), Password char(16) );3.4.3.tb_admin: 管理员信息表属性:编号aid,姓名name,登录密码password字段名称数据类型长度主键描述aidnumber10是主键标识nameVarchar250否姓名passwordchar16否登录密码 Create table tb_admin( Aid number(10) primary key, Name
8、varchar2(50), Password char(16) );3.4.4.tb_page:考试试卷信息表 属性:编号pid,科目subject, 选择题数量snum, 判断题数量bnum, 问答题数量anum,考试时间pageDate字段名称数据类型长度主键描述pidnumber10是主键标识subjectVarchar250否科目(外键)snumnumber2否选择题数量bnumnumber2否判断题数量anumnumber2否问答题数量pageDatedate/否考试时间tidnumber10是学生编号(外键) Create table tb_page( Pid number(10)
9、 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), constraint page_FK2 foreign key(tid) references tb_user(tid) );3.4.5.tb_select:选择题信息表属性:编号id,题目name,选项A optionA,
10、选项B optionB,选项C option C,选项D optionD,所属科目subject字段名称数据类型长度主键描述idnumber10是主键标识nameVarchar250否题目optionAVarchar2100否选项AoptionBVarchar2100否选项BoptionCVarchar2100否选项CoptionDVarchar2100否选项DsubjectVarchar250否科目(外键)pidnumber10是试卷编号(外键) Create table tb_select( Id number(10) primary key, Name varchar2(50), opt
11、ionA 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_subject(subject), constraint select_FK2 foreign key(pid) references tb_page(pid) );3.4.6.tb_boolean:判断题信息表 属性:编号id,题目
12、name,对选项yes,错选项no ,所属科目subject字段名称数据类型长度主键描述idnumber10是主键标识nameVarchar250否题目subjectVarchar250否科目(外键)pidnumber10否试卷编号(外键) Create table tb_boolean( id number(10) primary key, Name varchar2(50), Subject varchar2(50), Pid number(10), constraint boolean_FK foreign key(subject) references tb_subject(subje
13、ct), constraint boolean_FK2 foreign key(pid) references tb_page(pid) );3.4.7.tb_answer:简答题信息表属性:编号id,题目name, 所属科目subject字段名称数据类型长度主键描述idnumber5是主键标识nameVarchar250否题目subjectVarchar250否科目(外键)pidnumber10否试卷编号(外键)Create table tb_answer( Id number(5) primary key, Name varchar2(50), Subject varchar2(50),
14、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,科目subject字段名称数据类型长度主键描述sidnumber10否科目自增编号subjectVarchar250 是主键标识 Create table tb_subject( Sid number(10) , Subj
15、ect varchar2(50) primary key );/备注uid是关键字已被系统占用3.5视图设计3.6索引设计3.7序列设计设计目的:通过序列的设计,可以使得编号可以进行自增,从而达到设计目的。Tb_sq 序列自动进行递增初值为:1递增为:1最大值:无Create sequence tb_sq Increment by 1 Start with 1 Nomaxvalue Nocycle Cache 10;Tb_invigilate_sq监考人员的 tid 自动进行递增Create sequence tb_invigilate_sq Increment by 10 Start wit
16、h 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 Into next_noFrom dual;:=next_no;End;/-insert into tb_user (uname, upassword,uno, uclass, ustate)-values(
17、);Tb_invigilate_triggerCreate or replace trigger tb_invigilate_triggerBefore insert on tb_invigilateFor each rowDeclareNext_no number;BeginSelect Into next_noFrom dual;:=next_no;End;Tb_admin_triggerCreate or replace trigger tb_admin_triggerBefore insert on tb_adminFor each rowDeclareNext_no number;B
18、egin Select Into next_noFrom dual;:=next_no;End;Tb_page_triggerCreate or replace trigger tb_page_triggerBefore insert on tb_pageFor each rowDeclareNext_no number;Begin Select Into next_noFrom dual;:=next_no;End;Tb_select_triggerCreate or replace trigger tb_select_triggerBefore insert on tb_selectFor
19、 each rowDeclareNext_no number;BeginSelect Into next_noFrom dual;:=next_no;End;Tb_boolean_triggerCreate or replace trigger tb_boolean_triggerBefore insert on tb_booleanFor each rowDeclareNext_no number;BeginSelect Into next_noFrom dual;:=next_no;End;Tb_answer_triggerCreate or replace trigger tb_answ
20、er_triggerBefore insert on tb_answerFor each rowDeclareNext_no number;Begin Select Into next_noFrom dual;:=next_no;End;Tb_subject_triggerCreate or replace trigger tb_subject_triggerBefore insert on tb_subjectFor each rowDeclare Next_no number;BeginSelect Into next_noFrom dual;:=next_no;End;3.11存储过程设
21、计3.11.1CRUD中的CUD三个操作-向试卷表中插入信息(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 in %type)as BeginInsert into tb_page(subject,snum,bnum,anum,pageDate,tid) values(p_subject,p_snum,p_bnum,p_anu
22、m,p_pageDate,p_tid);Exception When DUP_VAL_ON_INDEX then(subject primary key error!);When others then(异常);End insert_tb_page;-/执行过程: execute insert_tb_page(,);-向判断题表中插入信息(create操作)Create or replace procedure insert_tb_boolean(b_name in %type,b_subject in %type,b_pid in %type) asbeginInsert into tb_b
23、oolean(name, subject,pid) values(b_name,b_subject,b_pid);End insert_tb_boolean;-/执行过程: execute insert_tb_boolean(,);-向简答题表中插入信息Create or replace procedure insert_tb_answer(a_name in %type,a_subject in %type,a_pid in %type)as BeginInsert into tb_answer(name,subject,pid) values(a_name,a_subject,a_pid)
24、;End insert_tb_answer;-向科目表中插入信息Create or replace procedure insert_tb_subject(s_subject in %type)asBeginInsert into tb_subject(subject) values(s_subject);Exception When DUP_VAL_ON_INDEX then(subject primary key error!);When others then(异常);End insert_tb_subject;-向选择题表中插入信息Create or replace procedure
25、 insert_tb_select(s_name %type,s_optionA %type,s_optionB %type,s_optionC %type,s_optionD %type,s_subject %type,s_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_subject,s_pid);End insert_tb_select
26、;-管理员信息插入Create or replace procedure insert_tb_admin(a_name in %type,a_password in %type)asBeginInsert into tb_admin(name, password) values(a_name,a_password);End insert_tb_admin;3.12数据复制设计暂无4查询记录语句-随机函数Select randnum from(select distinct round(1,60) as randnum from dual Connect by rownum=100Order b
27、y randnum)where rownum=20;-随机查询20条选择题Select * from tb_select where id in(Select * from (select rownum rn from dual Connect by rownum=100 order by whereRownum=20);-随机查询10条判断题记录Select * from tb_boolean where id in(Select * from (select rownum rn from dualConnect by rownum=100 order by whereRownum=10);-随机查询5条简答题记录Select * from tb_answer where id in(Select * from (select rownum rn from dualConnect by rownum=100 order by where Rownum=5);5词汇表Primary key: 主键 foreign key:外键6历史数据处理导出数据库:exp 用户名密码导入数据库:imp 用户名/密码Ms-dos
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1