在线考试系统数据库设计

数据库设计文档成员:指导老师:日期：2011-5-22目录数据库设计文档 (1)目录 (2)数据库设计文档 (3)1 引言 (3)1.1 编写目的 (3)1.2 项目来源 (3)1.3 文档约定 (3)1.4 预期读者和阅读建议 (3)1.5 参考资料 (3)2 数据库命名规则 (3)3 数据库设计说明 (5)3.1 数据库逻辑设计 (5)数据词典 (5)实体关系 (5)E_R 图 (9)SR关系： (9)3.2 数据库物理设计 (10)3.3 数据库分布 (10)3.4 基表设计 (10)3.4.1. tb_user: 考生信息表 (11)3.4.2. tb_invigilate: 监考人员信息表 (12)3.4.3. tb_admin: 管理员信息表 (12)3.44 tb_page:考试试卷信息表 (13)345. tb_select:选择题信息表 (14)3.4.6. tb_boolean:判断题信息表 (15)3.4.7. tb_a nswer:简答题信息表 (16)3.4.8. tb_subject:考试科目信息表 (17)3.5 视图设计 (18)3.6 索引设计 (18)3.7 序列设计 (18)3.8 完整性设计 (19)3.9 授权设计 (19)3.10 触发器设计 (19)3.11 存储过程设计 (24)3.11.1 CRUD中的CUD三个操作 (24)3.12 数据复制设计 (28)4 查询记录语句 (28)5 词汇表 (29)6 历史数据处理 (30)数据库设计文档1 引言1.1 编写目的本文档是本项目开发中至关重要的一部分，通过本档编写，对本项目中相关数据存储信息进行明确定位，对数据结构等相关信息进行明确说明。

本文档尽可能的详细说明各个实体的属性及相互联系，使开发人员的开发效率尽可能增加。

1.2 项目来源实训要求开发人员：全体成员文档使用者：oracle 开发人员、程序设计人员1.3 文档约定1.4 预期读者和阅读建议开发人员：对oracle 和java 熟悉测试人员：对java 及oracle 有一定的了解1.5 参考资料1 《在线考试系统可行性分析》2 《在线考试系统需求分析》3《项目（bbs）数据库设计文档一刘维毅》2 数据库命名规则db_examonline: 在线考试系统数据库tb_invigilate: 监考人员tb_user: 学生或用户信息表tb_select选择题相关信息表tb_an swer简答题相关信息tb_boolea n判断题相关信息tb_admin:管理员信息表tb_page数据库测试试题3 数据库设计说明3.1 数据库逻辑设计数据字典考生属性：编号tid,姓名uname登录密码upassword,学号uno, 班级uclass, 考试状态ustate监考人员属性：编号tid,姓名name登录密码password 管理员属性：编号aid,姓名name登录密码password 试卷属性：编号pid, 科目subject, 选择题数量snum, 判断题数量bnum,问答题数量anun,考试时间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图S —R关系:Stb_user(usid, un ame,upassword,uno,uclass,ustate)监考人员关系表：Stb_invigilate(tid,name,password) 管理员信息关系：Stb_admin(aid,name,password)考试试卷信息关系：Stb_page(us£,pid,subject,s nu m,b nu m,a nu m,pageDate) 选择题信息关系：Stb_select(id,pid,subject, name,opti on A,opti on B,opti on C,opti onD)判断题信息关系：Stb_boolean(id,pid,subje[t,name,yes,no) 简答题信息关系:Stb_answer(id,pid,subject,name) 科目信息关系：Stb_subject(sid,subje[t)3.2数据库物理设计暂无3.3数据库分布暂无3.4基表设计在线考试系统数据库:3・4・1・ tb_user:考生信息表属性：编号Tid,姓名name,登录密码password,学号number,班级class,考试状态stateCreate table tb_user(Tid nu mber(10) primary key,Un ame varchar2(50),Upassword char(16),Uno number(10),Uclass varchar2(50),Ustate nu mber(1));3・4・2・ tb_invigilate:监考人员信息表属性：编号tid,姓名name,登录密码passwordCreate table tb_i nvigilate(Tid nu mber(10) primary key,Name varchar2 (50),Password char(16));3・4.3・ tb_admin:管理员信息表属性：编号aid,姓名name,登录密码passwordCreate table tb_adm in(Aid nu mber(10) primary key,Name varchar2(50),Password char(16)）；3・4・4. tb_page:考试试卷信息表属性：编号pid,科目subject,选择题数量snum,判断题数量bnum,问答题数量anun,考试时间pageDateCreate table tb_page(Pid nu mber(10) primary key,Subject varchar2(50),Snum nu mber(2),Bnum nu mber(2),Anum nu mber(2),pageDate date,tid number(10),constraint page_FK foreign key (subject) referencestb_subject(subject),con stra int page_FK2 foreig n key(tid) referen ces tb_user(tid));3・4.5・ tb_select选择题信息表属性：编号id,题目name选项A optionA,选项B optionB,选项C option C,选项D optionD,所属科目subjectCreate table tb_select(Id nu mber(10) primary key,Name varchar2(50),optio nA varchar2(100),optio nB varchar2(100),optio nC varchar2(100),optio nD varchar2(100),subject varchar2(50),pid nu mber(10),con stra int select_FK foreig n key(subject) refere ncestb_subject(subject),con stra int select_FK2 foreig n key(pid) refere nces tb_page(pid) );3・4.6・ tb_boolean:判断题信息表属性：编号id,题目name对选项yes,错选项no ,所属科目subjectCreate table tb_boolea n(id number(10) primary key,Name varchar2(50),Subject varchar2(50),Pid number(10),con stra int boolea n_FK foreig n key(subject) refere nces tb_subject(subject),con stra int boolea n_FK2 foreig n key(pid) refere nces tb_page(pid) );3・4・7. tb_answer:简答题信息表属性：编号id,题目name,所属科目subjectId nu mber(5) primary key,Name varchar2(50),Subject varchar2(50),Pid number(10),con stra int an swer_FK foreig n key(subject) referen cestb_subject(subject),con stra int an swer_FK2 foreig n key(pid) refere nces tb_page(pid) );3・4.8・ tb_subject:考试科目信息表属性：编号sid,科目subjectCreate 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 自动进行递增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:=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_triggerCreate 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_trigger Before insert on tb_pageFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;:new.pid:=next_no;End;Tb_select_triggerCreate or replace trigger tb_select_trigger Before 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_trigger Before 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_trigger Before insert on tb_answerFor each rowDeclareNext_no number;BeginSelect tb_sq.nextvalInto next_noFrom dual;: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_page(p_subject in tb_page.subject%type, p_snum intb_page.snum%type, p_bnum in tb_page.bnum%type,p_anum in tb_page.anum%type, p_pageDate intb_page.pageDate%type, --插入系统时间sysdate p_tid intb_page.tid%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 Dbms_output.put_line('subject primary key error!'); When others thenDbms_output.put_li ne(异常');End insert_tb_page;--// 执行过程: executeinsert_tb_page(:'，'-);--向判断题表中插入信息(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;--//执行过程：execute insert_tb_boolean(:' ,'••);--向简答题表中插入信息Create or replace procedure insert_tb_answer( a_name intb_%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 intb_subject.subject%type)asBeginInsert into tb_subject(subject) values(s_subject);ExceptionWhen DUP_VAL_ON_INDEX then Dbms_output.put_line('subject primary key error!');When others thenDbms_output.put_li ne(异常');End insert_tb_subject;--向选择题表中插入信息Create or replace procedure insert_tb_select(s_name tb_%type,s_optionA tb_select.optionA%type, s_optionBtb_select.optionB%type, s_optionCtb_select.optionC%type, s_optionDtb_select.optionD%type, s_subjecttb_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;--管理员信息插入Create or replace procedure insert_tb_admin( a_name in tb_%type, a_password intb_admin.password%type)asBeginInsert into tb_admin(name, password) values(a_name,a_password);End insert_tb_admin;3.12 数据复制设计暂无4 查询记录语句--随机函数Select randnum from(select distinctround(dbms_random.value(1,60)) as randnum from dualConnect by rownum<=100Order by randnum)where rownum<=20;--随机查询20 条选择题Select * from tb_select where id in(Select * from (select rownum rn from dualConnect by rownum<=100 order byDbms_random.value) whereRownum<=20);--随机查询10 条判断题记录Select * from tb_boolean where id in(Select * from (select rownum rn from dualConnect by rownum<=100 order byDbms_random.value) whereRownum<=10);--随机查询5 条简答题记录Select * from tb_answer where id in(Select * from (select rownum rn from dualConnect by rownum<=100 order byDbms_random.value) whereRownum<=5);5 词汇表P rimary key: 主键foreign key: 外键6 历史数据处理导出数据库:exp 用户名／密码导入数据库：imp 用户名/密码Ms-dos。