数据库系统概论——程序员网上报名系统设计
计算机程序员网上报名系统设计
1需求分析
1.1设计背景
1.1.1系统开发背景
随着计算机技术的迅猛发展，学校教学和管理的信息化发展也有长足的进步，这就 要求各个环节都均衡发展，从软硬件两方面把学校建设成一流的信息管理、教育教学的平台该系统本着减轻教师工作负担、提高工作效率，增强参加学生的身份识别，比传统的报名模式节省人力财力和时间。
计算机程序员网上报名系统可以使用户进行信息注册和项目的报名，计算机程序员网上报名系统还增加了新闻和公告等信息，使得考生在进行网上报名的同时可以了解相关考试信息，考生可以根据相关信息对考试科目更加了解。
1.1.2系统分析
计算机程序员网上报名系统的目的是对计算机程序员考试网站信息进行管理。建立该管理系统的主要目标是为该考试提供全面的管理的解决方案，以解决传统的人工管理方式效率低，保密性差，不利于查找，更新和维护的问题，极大地提高考试管理的效率，节约教育经费，以适应考试信息化建设的发展趋势。
报名系统分为三大部分：考生、考试、管理员。考生注册系统账户后进行系统登陆，填写信息（所在地、性别、账号密码、姓名、证件号），查询考试信息，查看公式公告表，考试报名，缴费，打印准考证，修改信息，成绩查询，打印成绩单。考试发布（添加）考试信息（如：地点、科目、人数容量、考试编号起始时间、终止时间、已选人数），修改考试信息，删除考试信息。以及考试相关人员如监考人员、阅卷人员。管理员通过提交管理员信息，登录成功后可选择是否成为超级管理员，是则显示超级管理员界面，可进行考生管理、考试管理、管理员管理、系统设置；否则显示招生人员界面，可进行考生管理、考试管理、系统设置。
系统的数据输入信息包括学院、系别、管理员、班级、考生、成绩、电话、地址等。系统的数据输出信息包括计算机汇总输出信息，如考试成绩、考生证件号、考场等。
该系统可实现：
公告栏的告示（包括注册公告栏和考生登录公告栏）；
实现用户的注册和登录；实现用户对注册信息进行修改；
实现用户查看系统中已发布考试；
实现用户报名考试；
实现用户查看自己已报名的考试；
实现用户对自身已报名的考试进行退选；
实现管理员对考生进行添加；
实现管理员对考生进行管理（编辑，删除）；
实现管理员对考试进行添加即发布考试；
实现管理员查看已发布的考试并可以对相应考试进行删除；
实现管理员查看某门考试的已报名该考试的考生列表；
实现超级管理员对管理员进行添加，以及编辑管理员信息，删除管理员；
实现管理员进行公告栏的修改及发布。
1.1.3用户需求分析
（1）管理员类型 管理员可以成功登陆系统，系统管理员有权限进行如下操作：1.添加删除用户；2.修改已有用户密码； 3.修改学生的成绩；4.查询学生成绩；5.删除学生成绩；6.查看系统信息 等等。
（2）阅卷员类型 阅卷员可以成功登录系统，教师有权限进行如下操作：1.录入考生成绩；2.查询学生的成绩 3.修改个人密码 等等。
（3）学生类型 学生可以成功登录系统，学生有权限进行如下操作：1.查询成绩2.查询考场、考试证件号等3.修改个人密码以及个人基本信息 等等。
1.2 数据流图（或功能模块图）
图 1 整体设计流程
图 2 数据流程顶层图
图 3 数据流程一层图
1.3 数据字典
1.3.1数据结构
1.3.2数据项定义
1.4 安全性和完整性要求
系统应满足实体完整性、参照完整性和用户自定义的完整性规则。对不同的用户赋予不同的权限。
2 概念结构设计
2.1 实体的设计
1、管理员实体，其属性有管理员账号、密码、姓名、性别。
2、考生实体，其属性有考生账号、密码、姓名、性别、考生证件号、考生所在地。
3、考试信息内容实体，其属性有考试科目编码、考生准考证号、考生报名开始&截止日期、考试举办方、考试地点、考试试室、考试日期、考试时间。
4、成绩查询实体，其属性有成绩单证件号、考试报名科目、考试成绩。
5、报名费用实体，其属性有费用金额、费用支付方式。
6、公示公告实体，其属性有公告编号、公告标题、公告内容、考生账号、考生证件号。
7、阅卷老师实体，其属性有阅卷老师证件号、阅卷老师姓名、阅卷老师性别、阅卷老师账号、阅卷老师密码。
8、试卷成绩实体，其属性有准考证号、阅卷老师。
9、监考人员实体，其属性有监考人员证件号、监考人员姓名、监考人员性别。
2.2 联系的设计
一对多联系：
1、考生档案表和成绩查询表之间的属于联系：一个考生可能有多个成绩单，一个成绩单只能属于一个考生。
2、考生档案表和考试信息内容表之间的属于联系：一个考生可能有多场考试，因此一份考生档案有多个考试信息内容，而一个考试信息内容只能属于一个考生档案。
3、考生档案表与报名费用表之间的联系：一个考生有很多个报名费用表，一个报名费用表只能属于一个考生。
4、考生档案表与阅卷老师表之间的联系：一个阅卷老师有很多个考生档案，一个考生只能属于一个阅卷老师。
5、管理员表和公示公告表之间的属于联系：一个管理员有多个公示公告，一个公示公告只能由一个管理员来发布。
6、阅卷老师表与考试信息内容表的联系：一个阅卷老师可以有多个考试信息内容表，一个考试信息内容表只能属于一个阅卷老师。
7、阅卷老师表与试卷成绩表之间的联系：一个阅卷老师有很多份试卷成绩，一试卷成绩只能是一个阅卷老师批改。
8、监考人员表与考试信息内容表之间的联系：一个监考人员表有很多个考试信息内容表，一个考试信息内容表只能是一个监考人员表的。
2.3 概念模型（ER图）
图 4 计算机程序员网上报名系统ER图
3 数据库物理设计
3.1 表的设计
由于表较多，以下列举出较重要的表格进行举例说明。
管理员档案表：
（1）满足三范式：第一范式满足的条件是元组中的每一个分量都必须是不可分割的数据项，管理员档案表中没有出现重复项，满足第一范式；第二范式要求在第一范式的基础上，要求所有的非主属性完全依赖于其主码，管理员账户是主码，非主属性都完全依赖于管理员账户，故满足第二范式；第三范式在第二范式的基础上，任何一个非主属性都不传递依赖于任何主关字，该管理员档案表中的非主属性都不传递依赖于主关键字，故满足第三范式。
（2）满足三类完整性规则：
实体完整性：主码管理员账户不为空。
参照完整性：设置管理员证件号为外键，且不为空。
用户自定义完整性：设置性别默认男为“1”。
考生档案表：
（1）满足三范式：第一范式满足的条件是元组中的每一个分量都必须是不可分割的数据项，考生档案表中没有出现重复项，满足第一范式；第二范式要求在第一范式的基础上，要求所有的非主属性完全依赖于其主码，考生账号、考生证件号是主码，非主属性都完全依赖于考生账号、考生证件号，故满足第二范式；第三范式在第二范式的基础上，任何一个非主属性都不传递依赖于任何主关字，该考生档案表中的非主属性都不传递依赖于主关键字，故满足第三范式。
（2）满足三类完整性规则：
实体完整性：主码考生账号不为空。
参照完整性：考生表有外键考生证件号，且考生证件号不为空。
用户自定义完整性：设置性别默认男为“1”。
考试信息内容表：
（1）满足三范式：第一范式满足的条件是元组中的每一个分量都必须是不可分割的数据项，考试信息内容表中没有出现重复项，满足第一范式；第二范式要求在第一范式的基础上，要求所有的非主属性完全依赖于其主码，考生科目编码是主码，非主属性都完全依赖于考生科目编码，故满足第二范式；第三范式在第二范式的基础上，任何一个非主属性都不传递依赖于任何主关字，该考试信息内容表中的非主属性都不传递依赖于主关键字，故满足第三范式。
（2）满足三类完整性规则：
实体完整性：主码考试科目编码不为空。
参照完整性：考试信息内容表有外键考生准考证号，且考生准考证号不为空。
用户自定义完整性：时间格式为Timestamp，MySQL会自动将其存储为格林威治时间（GMT）的时间戳，在检索时，会将其转换为当前连接的时区。
成绩查询表：
（1）满足三范式：第一范式满足的条件是元组中的每一个分量都必须是不可分割的数据项，成绩查询表中没有出现重复项，满足第一范式；第二范式要求在第一范式的基础上，要求所有的非主属性完全依赖于其主码，成绩单证件号是主码，非主属性都完全依赖于成绩单证件号，故满足第二范式；第三范式在第二范式的基础上，任何一个非主属性都不传递依赖于任何主关字，该成绩查询表中的非主属性都不传递依赖于主关键字，故满足第三范式。
（2）满足三类完整性规则：
实体完整性：主码成绩单证件号不为空。
参照完整性：成绩查询表设置外键考试报名科目，且不为空。
用户自定义完整性：考试成绩默认“四舍五入”。
阅卷老师表：
（1）满足三范式：第一范式满足的条件是元组中的每一个分量都必须是不可分割的数据项，阅卷老师档案表中没有出现重复项，满足第一范式；第二范式要求在第一范式的基础上，要求所有的非主属性完全依赖于其主码，阅卷老师证件号是主码，非主属性都完全依赖于阅卷老师证件号，故满足第二范式；第三范式在第二范式的基础上，任何一个非主属性都不传递依赖于任何主关字，该阅卷老师表中的非主属性都不传递依赖于主关键字，故满足第三范式。
（2）满足三类完整性规则：
实体完整性：主码阅卷老师证件号不为空。
参照完整性：设置外键为阅卷老师账号，且不为空。
用户自定义完整性：设置性别属性默认为男“1”.
图 5 SQL中的表
3.2 索引的设计
为常作为查询条件的字段建立聚集索引，分别为查询和报名表、成绩结果查询表、发布和修改公示公告表、发布和修改考试信息表、费用数据统计表、公布和修改成绩信息表、公示公告表、管理员表、监考工作任务安排表、监考人员表、考生表、考试报名数据统计表、考试信息内容表、上传成绩表、试卷成绩表、试卷成绩汇总表、阅卷工作任务安排表、阅卷老师表。
图 6 SQL中显示的聚集索引
3.3 视图的设计
图 7 SQL中显示的视图
3.4 存储过程的设计
程序员网上报名系统中设计了6个存储过程，分别是系统存储过程、管理员存储过程、监考人员过程、考生存储过程、试卷成绩存储过程、阅卷老师存储过程。这些存储过程用来查询整个程序员网上报名系统。
图 8 SQL中显示的存储过程
3.5 触发器的设计
计算机程序员网上报名系统中涉及到的数据在视图中都可以得到更新，因此只在考生表设置了插入触发器、删除触发器和更新触发器。当考生表有变动时，考试信息内容的相关信息会相应变化。
图 9 SQL中显示的触发器
3.6 角色和用户的设计
设计四种用户，分别为管理员、监考人员、考生、阅卷人员。监考人员的权限主要是select查询功能，考生、管理员、阅卷人员的权限主要是select查询功能和更新update功能。
图 10 SQL中的用户和角色
图 11 查看考生的权限
4 数据库实施
4.1 代码的生成
见附录
4.2 数据库实施过程问题
在PowerDesinger逆向生成SQL sever代码，表格属性以及长度设置注意避免与SQL sever发生冲突，避免后期大量修改。
4.3 数据测试
4.3.1视图的测试
图 12 所有视图测试结果
4.3.2存储过程测试
图 13 存储过程测试结果
4.3.3触发器测试
4.3.3.1考生插入触发器测试
图 14 考生表插入触发器测试效果
4.3.3.2考生删除触发器测试
图 15 考生表删除触发器测试效果
4.3.3.3考生更新触发器测试
图 16 考生表更新触发器测试效果
附录
/*==============================================================*/
/* DBMS name: Sybase SQL Anywhere 11 */
/* Created on: 2023/6/13 10:03:40 */
/*==============================================================*/
if exists(select 1 from sys.sysforeignkey where role='FK_ASSOCIAT_查询和报名_考试信息内容') then
alter table Association_13
delete foreign key FK_ASSOCIAT_查询和报名_考试信息内容
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_ASSOCIAT_查询和报名2_考生') then
alter table Association_13
delete foreign key FK_ASSOCIAT_查询和报名2_考生
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_上传成绩_上传成绩_试卷成绩') then
alter table 上传成绩
delete foreign key FK_上传成绩_上传成绩_试卷成绩
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_上传成绩_上传成绩2_阅卷老师') then
alter table 上传成绩
delete foreign key FK_上传成绩_上传成绩2_阅卷老师
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_公布&修改成绩信_公布和修改成绩信息_成绩结果查询') then
alter table 公布&修改成绩信息
delete foreign key FK_公布&修改成绩信_公布和修改成绩信息_成绩结果查询
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_公布&修改成绩信_公布和修改成绩信息_管理员') then
alter table 公布&修改成绩信息
delete foreign key FK_公布&修改成绩信_公布和修改成绩信息_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_公示公告表_查看公示公告表_考生') then
alter table 公示公告表
delete foreign key FK_公示公告表_查看公示公告表_考生
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_发布&修改公示公_发布&修改公示公告_公示公告表') then
alter table 发布&修改公示公告表
delete foreign key FK_发布&修改公示公_发布&修改公示公告_公示公告表
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_发布&修改公示公_发布和修改公示公告_管理员') then
alter table 发布&修改公示公告表
delete foreign key FK_发布&修改公示公_发布和修改公示公告_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_发布&修改考试信_发布和修改考试信息_考试信息内容') then
alter table 发布&修改考试信息
delete foreign key FK_发布&修改考试信_发布和修改考试信息_考试信息内容
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_发布&修改考试信_发布和修改考试信息_管理员') then
alter table 发布&修改考试信息
delete foreign key FK_发布&修改考试信_发布和修改考试信息_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_成绩查询&打印_成绩查询和打印_成绩结果查询') then
alter table 成绩查询&打印
delete foreign key FK_成绩查询&打印_成绩查询和打印_成绩结果查询
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_监考工作任务安排_监考工作任务安排_管理员') then
alter table 监考工作任务安排
delete foreign key FK_监考工作任务安排_监考工作任务安排_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_监考工作任务安排_监考工作任务安排2_监考人员') then
alter table 监考工作任务安排
delete foreign key FK_监考工作任务安排_监考工作任务安排2_监考人员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_缴费_缴费_报名费用') then
alter table 缴费
delete foreign key FK_缴费_缴费_报名费用
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_考生_成绩查询和打印3_成绩查询&打印') then
alter table 考生
delete foreign key FK_考生_成绩查询和打印3_成绩查询&打印
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_考生_缴费3_缴费') then
alter table 考生
delete foreign key FK_考生_缴费3_缴费
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_考试报名数据统计_考试报名数据统计_考试信息内容') then
alter table 考试报名数据统计
delete foreign key FK_考试报名数据统计_考试报名数据统计_考试信息内容
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_考试报名数据统计_考试报名数据统计2_管理员') then
alter table 考试报名数据统计
delete foreign key FK_考试报名数据统计_考试报名数据统计2_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_试卷成绩汇总_试卷成绩汇总_试卷成绩') then
alter table 试卷成绩汇总
delete foreign key FK_试卷成绩汇总_试卷成绩汇总_试卷成绩
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_试卷成绩汇总_试卷成绩汇总2_管理员') then
alter table 试卷成绩汇总
delete foreign key FK_试卷成绩汇总_试卷成绩汇总2_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_费用数据统计_费用数据统计_报名费用') then
alter table 费用数据统计
delete foreign key FK_费用数据统计_费用数据统计_报名费用
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_费用数据统计_费用数据统计2_管理员') then
alter table 费用数据统计
delete foreign key FK_费用数据统计_费用数据统计2_管理员
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_阅卷工作任务安排_阅卷工作任务安排_阅卷老师') then
alter table 阅卷工作任务安排
delete foreign key FK_阅卷工作任务安排_阅卷工作任务安排_阅卷老师
end if;
if exists(select 1 from sys.sysforeignkey where role='FK_阅卷工作任务安排_阅卷工作任务安排2_管理员') then
alter table 阅卷工作任务安排
delete foreign key FK_阅卷工作任务安排_阅卷工作任务安排2_管理员
end if;
if exists(
select 1 from sys.systable
where table_name='Association_13'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table Association_13
end if;
if exists(
select 1 from sys.systable
where table_name='上传成绩'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 上传成绩
end if;
if exists(
select 1 from sys.systable
where table_name='公布&修改成绩信息'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 公布&修改成绩信息
end if;
if exists(
select 1 from sys.systable
where table_name='公示公告表'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 公示公告表
end if;
if exists(
select 1 from sys.systable
where table_name='发布&修改公示公告表'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 发布&修改公示公告表
end if;
if exists(
select 1 from sys.systable
where table_name='发布&修改考试信息'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 发布&修改考试信息
end if;
if exists(
select 1 from sys.systable
where table_name='成绩查询&打印'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 成绩查询&打印
end if;
if exists(
select 1 from sys.systable
where table_name='成绩结果查询'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 成绩结果查询
end if;
if exists(
select 1 from sys.systable
where table_name='报名费用'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 报名费用
end if;
if exists(
select 1 from sys.systable
where table_name='监考人员'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 监考人员
end if;
if exists(
select 1 from sys.systable
where table_name='监考工作任务安排'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 监考工作任务安排
end if;
if exists(
select 1 from sys.systable
where table_name='管理员'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 管理员
end if;
if exists(
select 1 from sys.systable
where table_name='缴费'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 缴费
end if;
if exists(
select 1 from sys.systable
where table_name='考生'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 考生
end if;
if exists(
select 1 from sys.systable
where table_name='考试信息内容'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 考试信息内容
end if;
if exists(
select 1 from sys.systable
where table_name='考试报名数据统计'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 考试报名数据统计
end if;
if exists(
select 1 from sys.systable
where table_name='试卷成绩'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 试卷成绩
end if;
if exists(
select 1 from sys.systable
where table_name='试卷成绩汇总'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 试卷成绩汇总
end if;
if exists(
select 1 from sys.systable
where table_name='费用数据统计'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 费用数据统计
end if;
if exists(
select 1 from sys.systable
where table_name='阅卷工作任务安排'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 阅卷工作任务安排
end if;
if exists(
select 1 from sys.systable
where table_name='阅卷老师'
and table_type in ('BASE', 'GBL TEMP')
) then
drop table 阅卷老师
end if;
/*==============================================================*/
/* Table: 查询&报名 */
/*==============================================================*/
create table 查询和报名
(
考试科目编号 integer not null,
考生准考证号码 integer not null,
考生账号 integer not null,
考生证件号 integer not null,
考试信息查询 char(10) null,
考试报名 char(10) null,
准考证打印打印 char(10) null,
constraint PK_查询和报名 primary key (考试科目编号, 考生准考证号码, 考生账号, 考生证件号)
);
alter table 查询和报名 DROP CONSTRAINT FK_ASSOCIAT_查询和报名_考试信息内容
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
INSERT INTO 查询和报名 (考试科目编号, 考生准考证号码, 考生账号, 考生证件号, 考试信息查询, 考试报名,准考证打印打印)
VALUES (1, 2100507219, 549949,10004, NULL , NULL, NULL);
/*==============================================================*/
/* Table: 上传成绩 */
/*==============================================================*/
create table 上传成绩
(
准考证号 integer not null,
阅卷老师证件号 integer not null,
constraint PK_上传成绩 primary key (准考证号, 阅卷老师证件号)
);
/*==============================================================*/
/* Table: 公布&修改成绩信息 */
/*==============================================================*/
create table 公布和修改成绩信息
(
管理员账号 integer not null,
成绩单证件号 integer not null,
成绩公布日期 timestamp not null,
成绩公布修改日期 DATETIME null,
constraint PK_公布和修改成绩信息 primary key (管理员账号, 成绩单证件号)
);
/*==============================================================*/
/* Table: 公示公告表 */
/*==============================================================*/
create table 公示公告表
(
公告编号 integer not null,
考生账号 integer not null,
考生证件号 integer not null,
公告标题 varchar not null,
公告内容 varchar not null,
constraint PK_公示公告表 primary key (公告编号)
);
alter table 公示公告表
alter column 公告标题 varchar(20) not null
alter table 公示公告表
alter column 公告内容 varchar(100) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到公示公告表
INSERT INTO 公示公告表 (公告编号, 考生账号, 考生证件号, 公告标题, 公告内容)
VALUES
(1001, 123456, 10001, '考试公告', '本次考试将于6月10日举行，请考生做好准备。'),
(1002, 123456, 10001, '成绩公示', '考试成绩已经公示，请考生前往查询自己的成绩。'),
(1003, 987654, 10002, '补考安排', '未通过科目的考生请注意，补考将于6月15日进行，请提前做好准备。');
/*==============================================================*/
/* View:公告公示表 */
/*==============================================================*/
create view 公告公示表
as
select 公告标题,公告内容
from 公示公告表
/*==============================================================*/
/* Table: 发布&修改公示公告表 */
/*==============================================================*/
create table 发布和修改公示公告表
(
管理员账号 integer not null,
公告编号 integer not null,
发布公告公示表日期 timestamp not null,
修改公告公示表日期 DATETIME null,
constraint PK_发布和修改公示公告表 primary key (管理员账号, 公告编号)
);
/*==============================================================*/
/* Table: 发布&修改考试信息 */
/*==============================================================*/
create table 发布和修改考试信息
(
管理员账号 integer not null,
考试科目编号 integer not null,
考生准考证号码 integer not null,
发布考试信息时间 timestamp not null,
修改考试信息时间 DATETIME null,
constraint PK_发布和修改考试信息 primary key (管理员账号, 考试科目编号, 考生准考证号码)
);
/*==============================================================*/
/* Table: 成绩查询&打印 */
/*==============================================================*/
create table 成绩查询和打印
(
成绩单证件号 integer not null,
成绩结果质疑反馈 varchar null,
成绩单日期 timestamp null
);
/*==============================================================*/
/* Table: 成绩结果查询 */
/*==============================================================*/
create table 成绩结果查询
(
成绩单证件号 integer not null,
考试报名科目 varchar not null,
考试成绩 float(3) not null,
constraint PK_成绩结果查询 primary key (成绩单证件号)
);
alter table 成绩结果查询
alter column 考试报名科目 varchar(20) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到成绩结果查询表
INSERT INTO 成绩结果查询 (成绩单证件号, 考试报名科目, 考试成绩)
VALUES
(20002,'计算机二级',95.5)
(20001, '计算机二级', 85.5)
/*==============================================================*/
/* View:成绩结果查询表 */
/*==============================================================*/
create view 成绩结果查询表
as
select 成绩单证件号,考试报名科目,考试成绩
from 成绩结果查询
/*==============================================================*/
/* Table: 报名费用 */
/*==============================================================*/
create table 报名费用
(
费用金额 float(5) not null,
费用支付方式 varchar not null
);
alter table 报名费用
alter column 费用支付方式 varchar(10) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到报名费用表
INSERT INTO 报名费用 (费用金额, 费用支付方式)
VALUES
(30.00, '支付宝'),
(30.00, '微信支付'),
(30.00, '银行转账');
/*==============================================================*/
/* View:报名费用表 */
/*==============================================================*/
create view 报名费用表
as
select 费用金额,费用支付方式
from 报名费用
/*==============================================================*/
/* Table: 监考人员 */
/*==============================================================*/
create table 监考人员
(
监考人员证件号 integer not null,
监考人员姓名 varchar not null,
监考人员性别 smallint not null,
constraint PK_监考人员 primary key (监考人员证件号)
);
ALTER TABLE 监考人员
ALTER COLUMN 监考人员姓名 varchar(8) NOT NULL;
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到监考人员表
INSERT INTO 监考人员 (监考人员证件号, 监考人员姓名, 监考人员性别)
VALUES
(123486, '王五', 1),
(654421, '花花', 2);
/*==============================================================*/
/* PROCEDURE: 监考人员过程 */
/*==============================================================*/
CREATE PROCEDURE 监考人员过程
@InvigilatorIDCard integer,
@InvigilatorName VARCHAR(10),
@InvigilatorGender smallint
AS
BEGIN
INSERT INTO 监考人员 (监考人员证件号, 监考人员姓名, 监考人员性别)
VALUES (@InvigilatorIDCard, @InvigilatorName, @InvigilatorGender)
END
/*==============================================================*/
/* View:监考人员表 */
/*==============================================================*/
create view 监考人员表
as
select 监考人员证件号,监考人员姓名,监考人员性别
from 监考人员
/*==============================================================*/
/* Table: 监考工作任务安排 */
/*==============================================================*/
create table 监考工作任务安排
(
监考人员证件号 integer not null,
管理员账号 integer not null,
监考地点 varchar not null,
监考时间 timestamp not null,
监考试室地点 varchar not null,
考生信息名单 varchar not null,
试卷领取地点 varchar not null,
试卷收集提交地点 varchar not null,
constraint PK_监考工作任务安排 primary key (监考人员证件号, 管理员账号)
);
alter table 监考工作任务安排
alter column 监考地点 varchar(10) not null
alter table 监考工作任务安排
alter column 监考试室地点 varchar(50) not null
alter table 监考工作任务安排
alter column 考生信息名单 varchar(100) not null
alter table 监考工作任务安排
alter column 试卷领取地点 varchar(50) not null
alter table 监考工作任务安排
alter column 试卷收集提交地点 varchar(50) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到监考工作任务安排表，排除时间戳列
INSERT INTO 监考工作任务安排 (监考人员证件号, 管理员账号, 监考地点, 监考试室地点, 考生信息名单, 试卷领取地点, 试卷收集提交地点)
VALUES
(123486, 8888, '考场A', 'A101', '考生名单A', '办公室A', '办公室B'),
(654421, 8888, '考场C', 'C301', '考生名单C', '办公室E', '办公室F');
/*==============================================================*/
/* View:监考工作任务安排表 */
/*==============================================================*/
create view 监考工作任务安排表
as
select 监考人员证件号 as 监考人员,监考地点,监考时间,监考试室地点,考生信息名单,试卷领取地点,试卷收集提交地点
from 监考工作任务安排
/*==============================================================*/
/* Table: 管理员 */
/*==============================================================*/
create table 管理员
(
管理员账号 integer not null,
管理员姓名 varchar not null,
管理员性别 smallint not null,
管理员账户密码 varchar not null,
constraint PK_管理员 primary key (管理员账号)
);
alter table 管理员
alter column 管理员姓名 varchar (10) not null
alter table 管理员
alter column 管理员账户密码 varchar (8) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到管理员表
INSERT INTO 管理员 (管理员账号, 管理员姓名, 管理员性别, 管理员账户密码)
VALUES
(8888, '黄帝', 1, '11223344')
/*==============================================================*/
/* PROCEDURE: 管理员存储过程 */
/*==============================================================*/
CREATE PROCEDURE 管理员存储过程
@AdminAccount integer,
@AdminName VARCHAR(10),
@AdminGender smallint,
@AdminPassword VARCHAR(8)
AS
BEGIN
INSERT INTO 管理员 (管理员账号, 管理员姓名, 管理员性别, 管理员账户密码)
VALUES (@AdminAccount, @AdminName, @AdminGender, @AdminPassword)
END
/*==============================================================*/
/* Table: 缴费 */
/*==============================================================*/
create table 缴费
(
费用发票电子版下载 char(10) null
);
/*==============================================================*/
/* Table: 考生 */
/*==============================================================*/
create table 考生
(
考生账号 integer not null,
考生账户密码 varchar not null,
考生姓名 varchar not null,
考生证件号 integer not null,
考生性别 smallint not null,
考生所在地 varchar not null,
constraint PK_考生 primary key (考生账号, 考生证件号)
);
ALTER TABLE 考生
alter COLUMN 考生账户密码 varchar(8) NOT NULL;
ALTER TABLE 考生
alter COLUMN 考生姓名 varchar(10) NOT NULL;
ALTER TABLE 考生
alter COLUMN 考生所在地 varchar(10) NOT NULL;
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
INSERT INTO 考生 (考生账号, 考生账户密码, 考生姓名, 考生证件号, 考生性别, 考生所在地)
VALUES
(123456, '12345678', '张三', 10001, 1, '北京'),
(987654, '12345678', '李四', 10002, 2, '上海');
/*==============================================================*/
/* PROCEDURE: 考生存储过程 */
/*==============================================================*/
CREATE PROCEDURE 考生存储过程
@ExamineeAccount integer,
@ExamineePassword VARCHAR(8),
@ExamineeName varchar(10),
@ExamineeIDCard integer,
@ExamineeGender smallint,
@ExamineeLocation varchar(10)
AS
BEGIN
INSERT INTO 考生 (考生账号, 考生账户密码, 考生姓名, 考生证件号, 考生性别, 考生所在地)
VALUES (@ExamineeAccount, @ExamineePassword, @ExamineeName, @ExamineeIDCard, @ExamineeGender, @ExamineeLocation)
END
/*==============================================================*/
/* View:考生表 */
/*==============================================================*/
create view 考生表
as
select 考生账号 ,考生账户密码,考生姓名,考生证件号,考生性别,考生所在地
from 考生
/*==============================================================*/
/* Table: 考试信息内容 */
/*==============================================================*/
create table 考试信息内容
(
考试科目编号 integer not null,
考生准考证号码 integer not null,
考试报名开始和截止日期 DATETIME not null,
考试举办方 varchar not null,
考试地点 varchar not null,
考试试室 varchar not null,
考试日期 timestamp not null,
考试时间 DATETIME not null,
constraint PK_考试信息内容 primary key (考试科目编号, 考生准考证号码)
);
ALTER TABLE 考试信息内容
ALTER COLUMN 考试举办方 varchar(50) NOT NULL;
ALTER TABLE 考试信息内容
ALTER COLUMN 考试地点 varchar(50) NOT NULL;
ALTER TABLE 考试信息内容
ALTER COLUMN 考试试室 varchar(10) NOT NULL;
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
INSERT INTO 考试信息内容 (考试科目编号, 考生准考证号码, 考试报名开始和截止日期, 考试举办方, 考试地点, 考试试室, 考试时间)
VALUES
(1, 10001, '2023-06-20 00:00:00', 'ABC考试机构', '考试中心1', 'A101', '2023-06-21 09:00:00'),
(1, 10002, '2023-06-20 00:00:00', 'ABC考试机构', '考试中心1', 'A102', '2023-06-21 13:00:00'),
(2, 10001, '2023-06-22 00:00:00', 'XYZ考试机构', '考试中心2', 'B201', '2023-06-23 10:00:00');
/*==============================================================*/
/* View:考试信息内容表 */
/*==============================================================*/
create view 考试信息内容表
as
select 考试科目编号 as 考试科目 ,考生准考证号码,考试报名开始和截止日期,考试举办方,考试地点,考试试室,考试日期,考试时间
from 考试信息内容
/*==============================================================*/
/* Table: 考试报名数据统计 */
/*==============================================================*/
create table 考试报名数据统计
(
管理员账号 integer not null,
考试科目编号 integer not null,
考生准考证号码 integer not null,
考生数量 integer null,
报名科目统计 integer null,
constraint PK_考试报名数据统计 primary key (管理员账号, 考试科目编号, 考生准考证号码)
);
ALTER TABLE 考试报名数据统计 DROP CONSTRAINT FK_考试报名数据统计_考试报名数据统计_考试信息内容;
ALTER TABLE 考试报名数据统计 DROP CONSTRAINT PK_考试报名数据统计;
ALTER TABLE 考试报名数据统计 DROP COLUMN 考生准考证号码;
ALTER TABLE 考试报名数据统计 ADD CONSTRAINT PK_考试报名数据统计 PRIMARY KEY (管理员账号, 考试科目编号);
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
INSERT INTO 考试报名数据统计 (管理员账号, 考试科目编号, 考生数量, 报名科目统计)
VALUES
(8888, 1, 2, 2)
*==============================================================*/
/* View:考试报名数据统计表 */
/*==============================================================*/
CREATE VIEW 考试报名数据统计表
AS
SELECT 考试科目编号 AS 考试科目, 考生数量, 报名科目统计
FROM 考试报名数据统计;
/*==============================================================*/
/* Table: 试卷成绩 */
/*==============================================================*/
create table 试卷成绩
(
准考证号 integer not null,
阅卷成绩 float(3) not null,
constraint PK_试卷成绩 primary key (准考证号)
);
/*==============================================================*/
/* PROCEDURE: 试卷成绩存储过程 */
/*==============================================================*/
CREATE PROCEDURE 试卷成绩存储过程
@ExamID int,
@Score float(3)
AS
BEGIN
INSERT INTO 试卷成绩信息 (准考证号, 阅卷成绩)
VALUES (@ExamID, @Score)
END
/*==============================================================*/
/* Table: 试卷成绩汇总 */
/*==============================================================*/
create table 试卷成绩汇总
(
准考证号 integer not null,
管理员账号 integer not null,
试卷科目分类 varchar not null,
试卷成绩汇总 float(4) not null,
constraint PK_试卷成绩汇总 primary key (准考证号, 管理员账号)
);
alter table 试卷成绩汇总 DROP CONSTRAINT FK_试卷成绩汇总_试卷成绩汇总_试卷成绩
alter table 试卷成绩汇总 DROP CONSTRAINT PK_试卷成绩汇总
alter table 试卷成绩汇总
alter column 试卷科目分类 varchar(10) not null
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
INSERT INTO 试卷成绩汇总 (准考证号, 管理员账号, 试卷科目分类, 试卷成绩汇总)
VALUES
(10001, 8888, '计算机二级', 85.5),
(10002, 8888, '计算机二级', 95.5)
*==============================================================*/
/* View:试卷成绩汇总表 */
/*==============================================================*/
create view 试卷成绩汇总表
as
select 试卷科目分类 ,试卷成绩汇总
from 试卷成绩汇总
/*==============================================================*/
/* Table: 费用数据统计 */
/*==============================================================*/
create table 费用数据统计
(
管理员账号 integer not null,
费用金额总数 float(20) null,
constraint PK_费用数据统计 primary key (管理员账号)
);
*==============================================================*/
/* View:费用数据统计表 */
/*==============================================================*/
INSERT INTO 费用数据统计 (管理员账号, 费用金额总数)
VALUES
(8888, 60)
*==============================================================*/
/* View:费用数据统计表 */
/*==============================================================*/
create view 费用数据统计表
as
select 费用金额总数
from 费用数据统计
/*==============================================================*/
/* Table: 阅卷工作任务安排 */
/*==============================================================*/
create table 阅卷工作任务安排
(
阅卷老师证件号 integer not null,
管理员账号 integer not null,
阅卷科目 varchar null,
阅卷板块 varchar null,
阅卷截止时间 timestamp null,
阅卷量 integer null,
constraint PK_阅卷工作任务安排 primary key (阅卷老师证件号, 管理员账号)
);
alter table 阅卷工作任务安排
alter column 阅卷科目 varchar(10) null
alter table 阅卷工作任务安排
alter column 阅卷板块 varchar(10) null
*==============================================================*/
/* View:阅卷工作任务安排表 */
/*==============================================================*/
INSERT INTO 阅卷工作任务安排 (阅卷老师证件号, 管理员账号, 阅卷科目, 阅卷板块, 阅卷截止时间, 阅卷量)
VALUES (12345, 8888, '计算机二级', 'A卷', DEFAULT, 50);
/*==============================================================*/
/* View:阅卷工作任务安排表 */
/*==============================================================*/
create view 阅卷工作任务安排表
as
select 阅卷老师证件号,阅卷科目,阅卷板块,阅卷截止时间
from 阅卷工作任务安排
/*==============================================================*/
/* Table: 阅卷老师 */
/*==============================================================*/
create table 阅卷老师
(
阅卷老师证件号 integer not null,
阅卷考试姓名 varchar not null,
阅卷老师性别 smallint not null,
阅卷老师账号 integer not null,
阅卷老师账号密码 varchar not null,
constraint PK_阅卷老师 primary key (阅卷老师证件号)
);
ALTER TABLE 阅卷老师
ALTER COLUMN 阅卷老师姓名 varchar(4) NOT NULL;
ALTER TABLE 阅卷老师
ALTER COLUMN 阅卷老师账号密码 varchar(6) NOT NULL;
EXEC sp_rename '阅卷老师.阅卷考试姓名', '阅卷老师姓名', 'COLUMN';
/*==============================================================*/
/* 数据录入 */
/*==============================================================*/
-- 插入测试数据到阅卷老师表
INSERT INTO 阅卷老师 (阅卷老师证件号, 阅卷老师姓名, 阅卷老师性别, 阅卷老师账号, 阅卷老师账号密码)
VALUES
(12345, '张三', 1, 10001, '000000'),
(67890, '李梅', 2, 10002, '000000');
/*==============================================================*/
/* PROCEDURE: 阅卷老师存储过程 */
/*==============================================================*/
CREATE PROCEDURE 阅卷老师存储过程
@GPteacherID integer,
@GPteacherName varchar (4),
@GPteacherSex smallint,
@GPteacherAccount integer,
@GPteacherPassword varchar(6)
AS
BEGIN
INSERT INTO 阅卷老师 (阅卷老师证件号, 阅卷老师姓名,阅卷老师性别,阅卷老师账号,阅卷老师账号密码)
VALUES (@GPteacherID, @GPteacherName,@GPteacherSex,@GPteacherAccount,@GPteacherPassword)
END
EXEC 阅卷老师存储过程
@GPteacherID = 520121,
@GPteacherName = '黄培滇',
@GPteacherSex = 1,
@GPteacherAccount = 10003,
@GPteacherPassword = '000000';
*==============================================================*/
/* View:阅卷老师表 */
/*==============================================================*/
create view 阅卷老师表
as
select 阅卷老师证件号,阅卷老师姓名,阅卷老师性别,阅卷老师账号,阅卷老师账号密码
from 阅卷老师
*==============================================================*/
/* KEY：设置 */
/*==============================================================*/
alter table 查询和报名
add constraint FK_ASSOCIAT_查询和报名_考试信息内容 foreign key (考试科目编号, 考生准考证号码)
references 考试信息内容 (考试科目编号, 考生准考证号码)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 查询和报名
add constraint FK_ASSOCIAT_查询和报名2_考生 foreign key (考生账号, 考生证件号)
references 考生 (考生账号, 考生证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 上传成绩
add constraint FK_上传成绩_上传成绩_试卷成绩 foreign key (准考证号)
references 试卷成绩 (准考证号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 上传成绩
add constraint FK_上传成绩_上传成绩2_阅卷老师 foreign key (阅卷老师证件号)
references 阅卷老师 (阅卷老师证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 公布和修改成绩信息
add constraint FK_公布和修改成绩信_公布和修改成绩信息_成绩结果查询 foreign key (成绩单证件号)
references 成绩结果查询 (成绩单证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 公布和修改成绩信息
add constraint FK_公布和修改成绩信_公布和修改成绩信息_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 公示公告表
add constraint FK_公示公告表_查看公示公告表_考生 foreign key (考生账号, 考生证件号)
references 考生 (考生账号, 考生证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 发布和修改公示公告表
add constraint FK_发布和修改公示公_发布和修改公示公告_公示公告表 foreign key (公告编号)
references 公示公告表 (公告编号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 发布和修改公示公告表
add constraint FK_发布和修改公示公_发布和修改公示公告_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 发布和修改考试信息
add constraint FK_发布和修改考试信_发布和修改考试信息_考试信息内容 foreign key (考试科目编号, 考生准考证号码)
references 考试信息内容 (考试科目编号, 考生准考证号码)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 发布和修改考试信息
add constraint FK_发布和修改考试信_发布和修改考试信息_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 成绩查询和打印
add constraint FK_成绩查询和打印_成绩查询和打印_成绩结果查询 foreign key (成绩单证件号)
references 成绩结果查询 (成绩单证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 监考工作任务安排
add constraint FK_监考工作任务安排_监考工作任务安排_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 考试报名数据统计
add constraint FK_考试报名数据统计_考试报名数据统计_考试信息内容 foreign key (考试科目编号, 考生准考证号码)
references 考试信息内容 (考试科目编号, 考生准考证号码)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 考试报名数据统计
add constraint FK_考试报名数据统计_考试报名数据统计2_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 试卷成绩汇总
add constraint FK_试卷成绩汇总_试卷成绩汇总_试卷成绩 foreign key (准考证号)
references 试卷成绩 (准考证号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 试卷成绩汇总
add constraint FK_试卷成绩汇总_试卷成绩汇总2_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 费用数据统计
add constraint FK_费用数据统计_费用数据统计2_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 阅卷工作任务安排
add constraint FK_阅卷工作任务安排_阅卷工作任务安排_阅卷老师 foreign key (阅卷老师证件号)
references 阅卷老师 (阅卷老师证件号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
alter table 阅卷工作任务安排
add constraint FK_阅卷工作任务安排_阅卷工作任务安排2_管理员 foreign key (管理员账号)
references 管理员 (管理员账号)
ON UPDATE NO ACTION
ON DELETE NO ACTION;
*==============================================================*/
/* 用户：考生 */
/*==============================================================*/
-- 创建用户 '考生'，密码为 '000000'
CREATE LOGIN 考生 WITH PASSWORD = '000000';
-- 创建用户 '考生'
CREATE USER 考生 FOR LOGIN 考生;
-- 赋予用户 '考生' 对表 '报名费用' 的 SELECT 权限
GRANT SELECT ON [dbo].[报名费用] TO 考生;
-- 赋予用户 '考生' 对表 '公示公告表' 的 SELECT 权限
GRANT SELECT ON [dbo].[查询和报名] TO 考生;
-- 赋予用户 '考生' 对表 '成绩结果查询' 的 SELECT 权限
GRANT SELECT ON [dbo].[成绩结果查询] TO 考生;
-- 赋予用户 '考生' 对表 '公示公告表' 的 SELECT 权限
GRANT SELECT ON [dbo].[公示公告表] TO 考生;
-- 赋予用户 '考生' 对表 '缴费' 的 SELECT 权限
GRANT SELECT ON [dbo].[缴费] TO 考生;
-- 赋予用户 '考生' 对表 '考生' 的 SELECT 权限
GRANT SELECT ON [dbo].[考生] TO 考生;
-- 赋予用户 '考生' 对表 '考试信息' 的 SELECT 权限
GRANT SELECT ON [dbo].[考试信息内容] TO 考生;
-- 赋予用户 '考生' 对表 '考生' 的 UPDATE 权限
GRANT UPDATE ON [dbo].[考生] TO 考生;
*==============================================================*/
/* 用户：管理员 */
/*==============================================================*/
-- 创建用户 '管理员'，密码为 'VIP888'
CREATE LOGIN 管理员 WITH PASSWORD = 'VIP888';
-- 创建用户 '管理员'
CREATE USER 管理员 FOR LOGIN 管理员;
-- 赋予用户 '管理员' 对表 '报名费用' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[报名费用] TO 管理员;
-- 赋予用户 '管理员' 对表 '费用数据统计' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[费用数据统计] TO 管理员;
-- 赋予用户 '管理员' 对表 '公示公告表' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[公示公告表] TO 管理员;
-- 赋予用户 '管理员' 对表 '管理员' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[管理员] TO 管理员;
-- 赋予用户 '管理员' 对表 '监考工作任务安排' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[监考工作任务安排] TO 管理员;
-- 赋予用户 '管理员' 对表 '监考人员' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[监考人员] TO 管理员;
-- 赋予用户 '管理员' 对表 '考生' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[考生] TO 管理员;
-- 赋予用户 '管理员' 对表 '考试报名数据统计' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[考试报名数据统计] TO 管理员;
-- 赋予用户 '管理员' 对表 '考试信息内容' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[考试信息内容] TO 管理员;
-- 赋予用户 '管理员' 对表 '试卷成绩' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[试卷成绩] TO 管理员;
-- 赋予用户 '管理员' 对表 '试卷成绩汇总' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[试卷成绩汇总] TO 管理员;
-- 赋予用户 '管理员' 对表 '阅卷工作任务安排' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[阅卷工作任务安排] TO 管理员;
-- 赋予用户 '管理员' 对表 '阅卷老师' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[阅卷老师] TO 管理员;
*==============================================================*/
/* 用户：监考老师 */
/*==============================================================*/
-- 创建用户 '监考人员'，密码为 'watcher'
CREATE LOGIN 监考人员 WITH PASSWORD = 'watcher';
-- 创建用户 '监考人员'
CREATE USER 监考人员 FOR LOGIN 监考人员;
-- 赋予用户 '监考人员' 对表 '监考人员' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[监考人员] TO 监考人员;
-- 赋予用户 '监考人员' 对表 '监考工作任务安排' 的 SELECT 权限
GRANT SELECT ON [dbo].[监考工作任务安排] TO 监考人员;
*==============================================================*/
/* 用户：阅卷老师 */
/*==============================================================*/
-- 创建用户 '阅卷人员'，密码为 'LOVEteac'
CREATE LOGIN 阅卷人员 WITH PASSWORD = 'LOVEteac';
-- 创建用户 '阅卷人员'
CREATE USER 阅卷人员 FOR LOGIN 阅卷人员;
-- 赋予用户 '阅卷人员' 对表 '阅卷老师' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[阅卷老师] TO 阅卷人员;
-- 赋予用户 '阅卷人员' 对表 '上传成绩' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[上传成绩] TO 阅卷人员;
-- 赋予用户 '阅卷人员' 对表 '试卷成绩' 的 SELECT 和 UPDATE 权限
GRANT SELECT, UPDATE ON [dbo].[试卷成绩] TO 阅卷人员;
-- 赋予用户 '阅卷人员' 对表 '阅卷工作任务安排' 的 SELECT 权限
GRANT SELECT ON [dbo].[阅卷工作任务安排] TO 阅卷人员;
*==============================================================*/
/* 触发器：考生 */
/*==============================================================*/
CREATE TRIGGER 插入触发器
ON [dbo].[考生]
AFTER INSERT
AS
BEGIN
-- 生成6位数的自增数字作为学号
DECLARE @max_id INT, @id INT;
SELECT @max_id = MAX(CAST(RIGHT(考生账号, 6) AS INT)) FROM [dbo].[考生];
SET @max_id = ISNULL(@max_id, 0);
SET @id = @max_id + 1;
-- 自动生成学生学号
UPDATE [dbo].[考生] SET
考生账号 = RIGHT('000000' + CAST(@id AS VARCHAR(6)), 6)
WHERE 考生账号 IS NULL;
END;
CREATE TRIGGER 删除触发器
ON [dbo].[考生]
INSTEAD OF DELETE
AS
BEGIN
DECLARE @exist_exam INT;
SELECT @exist_exam = COUNT(*) FROM [dbo].[查询和报名] WHERE 考生账号 IN (SELECT 考生账号 FROM deleted);
IF @exist_exam > 0
BEGIN
RAISERROR ('该考生已报名考试，不允许删除',16,1);
ROLLBACK TRANSACTION;
RETURN;
END
ELSE
BEGIN
DELETE FROM 考生 WHERE 考生账号 IN (SELECT 考生账号 FROM deleted);
END
END;
-- 创建更新触发器
CREATE TRIGGER 更新触发器
ON [dbo].[考生]
FOR UPDATE
AS
BEGIN
IF UPDATE(考生账户密码)
BEGIN
PRINT '考生账户密码发生了修改';
END
IF UPDATE(考生所在地)
BEGIN
PRINT '考生所在地发生了修改';
END
END;
*==============================================================*/
/* 测试 */
/*==============================================================*/
--视图
select * from [dbo].[报名费用表]
select * from[dbo].[成绩结果查询表]
select * from[dbo].[费用数据统计表]
select * from[dbo].[公告公示表]
select * from[dbo].[监考工作任务安排表]
select * from[dbo].[监考人员表]
select * from[dbo].[考生表]
select * from[dbo].[考试报名数据统计表]
select * from[dbo].[考试信息内容表]
select * from[dbo].[试卷成绩汇总表]
select * from[dbo].[阅卷工作任务安排表]
select * from[dbo].[阅卷老师表]
--存储过程
EXEC 监考人员过程
@InvigilatorIDCard = 664455,
@InvigilatorName = '易*',
@InvigilatorGender = 2;
EXEC 管理员存储过程
@AdminAccount = 9999,
@AdminName = '郑**',
@AdminGender = 1,
@AdminPassword = '52sb6688'
EXEC 考生存储过程
@ExamineeAccount = 122446,
@ExamineePassword = '12345678',
@ExamineeName = '黄**',
@ExamineeIDCard = 10003,
@ExamineeGender = 2,
@ExamineeLocation = '广东'
EXEC 阅卷老师存储过程
@GPteacherID = 520121,
@GPteacherName = '黄*',
@GPteacherSex = 1,
@GPteacherAccount = 10003,
@GPteacherPassword = '000000';
select * from[dbo].[管理员]
select * from[dbo].[监考人员表]
select * from[dbo].[考生表]
select * from[dbo].[阅卷老师表]
--触发器
--插入
INSERT INTO [dbo].[考生] (考生账号, 考生账户密码, 考生姓名, 考生证件号,考生性别, 考生所在地)
VALUES (549949, '123456', '陈琳', 10004 ,2,'广西');
select * from[dbo].[考生]
--删除
DELETE FROM [dbo].[考生] WHERE 考生账号 = '10004'
select * from[dbo].[考生]
--更新
INSERT INTO 考生 (考生账号, 考生账户密码, 考生姓名, 考生证件号, 考生性别, 考生所在地)
VALUES (666888, '12345678', '江夏', 10006, 1, '南京');
UPDATE 考生
SET 考生账户密码 = '654321', 考生所在地 = '厦门'
WHERE 考生账号 = 666888;
select * from[dbo].[考生]
