一种关系型数据库的自动测评以及评分算法

关系型数据库在如今已经是一门比较常用以及重要的技术，现在的大部分应用程序系统都构建于关系型数据库系统之上，数据库技能也是每个IT从业人员的必备技能之一，因此一些高校、培训学校等机构都把数据库课程作为必修课程之一。这就牵涉到考核的问题了，对于学生是否掌握该门技术，常见的考核方式就是出题做试卷了。现在大部分学校基本都是上机考试，那对于数据库这种计算机类的考核，显然用纸质试卷来弄显然不太好的样子，最好还要支持上自动测评。

一、题目示例

对于数据库操作的题目，一般长如下的样子：

现有一个考试安排数据库db_exam，该数据库中包含t_room（考场表）、t_examinee（考生表）和t_teacher（监考老师表）数据表。其表结构如下：

t_room包含字段：rid（考场编号）、rname（考场名称）、site（考场地址）。

t_examinee包含字段：eid（考生编号）、ename（考生姓名）、sex（性别）、etel（联系方式）、examnum（准考证号）、rid（考场编号）。

t_teacher包含字段：tid（监考老师编号）、tname（老师姓名）、tel（联系方式）、rid（考场编号）。

请点击题目下方的答题按钮，按要求进行操作。

1.设置t_examinee表的字段sex为char类型，长度为2。

2.添加一条考场信息：考场名称为“205教室”、考场地址为“3号教学楼”。

3.使用SQL语句删除t_teacher表中考场编号为空的老师信息。注意：请将该语句以gf1.sql为文件名保存在考试文件夹中，否则不得分。

4.使用SQL语句将考生“曾静”的联系方式改为“13211112222”。注意：请将该语句以gf2.sql为文件名保存在考试文件夹中，否则不得分。

5.使用SQL语句查询考生“陶宝”的考场信息，要求依次显示字段：ename、rname、site。注意：请将该语句以gf3.sql为文件名保存在考试文件夹中，否则不得分。

6.使用SQL语句查询“102教室”考场的监考老师信息，要求依次显示字段：rname、tname、tel。注意：请将该语句以gf4.sql为文件名保存在考试文件夹中，否则不得分。

7.使用SQL语句创建视图v_exam(ename,sex,etel,examnum,rname,site)，显示考试安排信息。注意：请将该语句以gf5.sql为文件名保存在考试文件夹中，否则不得分。

对于如上的一个题目信息，学生作答流程大概可以简化为：

浏览题目：
点击作答：一般题目下方会有一个作答按钮，点击作答，程序会自动连接到一个考生数据库上
进行作答：学生在其对应的库上进行作答操作。一般可能会使用mysql终端进行作答，也可以使用一些可视化工具进行作答，该算法流程可以不限制工具。其中某些操作步骤中需要其编写的sql已文件形式保存到对应的考生文件夹中。这样其实会产生两个结果，一个为学生保存提交的sql文件，一个为学生操作完后的结果数据库。因此测评可以按照产生的结果分为两大类，一类是对sql语句进行测评，一类是对结果数据库进行测评。

测评分类：

sql语句
结果数据库

二、关系型数据库SQL语句分类

对于关系型数据库的sql语句，可以分为DDL，DML，DQL，DCL四大类。

DDL：数据库定义语言数据库定义语言用来定义数据库的对象，比如对数据库、表、索引、视图、函数、存储过程、游标、触发器、架构等进行管理。操作语句包括create, alter, drop等语句。
DML：数据库操作语言数据库操作语言用来对数据库中的数据进行新增、删除、修改等操作，如insert, delete, update等语句。
DQL：数据查询语言数据查询语言用来对数据库中的数据进行擦汗寻操作，如select, from, where, order by, group by, having等语句。
DCL：数据查询语言数据查询语言用来控制用户对数据库的操作权限，用来授予或回收访问数据库的权限，控制数据库事务发生的时间以及效果，如grant, revoke等语句。

三、初始化环境

首先题目得设置好一些初始化的sql语句，比如对于该示例的题来说，显然首先得先初始化一个名为db_exam的数据库，然后继续在该数据库中初始化需要的表以及数据，则该题目的初始化sql可以如下设置：


CREATE TABLE `t_room`  (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `rname` varchar(255) NULL DEFAULT NULL,
  `site` varchar(255) NULL DEFAULT NULL,
  PRIMARY KEY (`rid`) USING BTREE
);

INSERT INTO `t_room` VALUES (10001, '101教室', '1号教学楼');
INSERT INTO `t_room` VALUES (10002, '102教室', '1号教学楼');


CREATE TABLE `t_examinee`  (
  `eid` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(255) NULL DEFAULT NULL,
  `sex` varchar(5) NULL DEFAULT NULL,
  `tel` varchar(20) NULL DEFAULT NULL,
  `examnum` varchar(20) NULL DEFAULT NULL,
  `rid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`eid`) USING BTREE
)；

INSERT INTO `t_examinee` VALUES (10001, '陶宝', '男', '13522221111', '240901', 10001);
INSERT INTO `t_examinee` VALUES (10002, '曾静', '女', '13222221111', '240902', 10002);


CREATE TABLE `t_teacher`  (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) NULL DEFAULT NULL,
  `tel` varchar(20) NULL DEFAULT NULL,
  `rid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);

INSERT INTO `t_teacher` VALUES (10001, '宋思', '13544441111', 10001);
INSERT INTO `t_teacher` VALUES (10002, '刘芳', '13544442222', 10002);
INSERT INTO `t_teacher` VALUES (10003, '李明', '13544443333', NULL);

则学生在开始作答时执行如上的初始化sql语句，学生在该环境中进行答题。

如果有些题目需要由学生来建库，也就是建库也是一个测评的点，则该题目就得特殊处理，不需要有初始化sql语句，并且数据也得作为测评点由学生进行操作。

四、SQL语句的测评

对于学生提交的一条sql语句，可以作为一个点来进行测评，具体测评方法可以将学生提交的sql语句插入到一系列的sql语句中运行。然后使用最后一条语句的返回结果最为预期结果比对。具体方法如下：

设置测评sql语句：

测评示例sql1
evalSql1
evalSql2
evalSql3
evalSql4
#(studentSql)
evalSql5
预期返回：
res1

测评示例sql2：
#(studentSql)
evalSql1
evalSql2
evalSql3
evalSql4
evalSql5
预期返回：
res2

测评示例sql3：
evalSql1
evalSql2
evalSql3
evalSql4
evalSql5
#(studentSql)
预期返回：
res3

其中 evalSql为设置的系列测评sql语句， #(studentSql)为学生提交的sql语句，这里在设置测评点的时候是一个变量，在执行测评的时候替换为学生提交的sql，可以看到，#(studentSql)可以插入到evalSql的任意位置，这个是根据实际情况来插入的。

测评方法步骤如下：

初始化测评临时库: dbEvalTemp1。
将#(studentSql)替换为学生所作答的sql语句。
执行这一堆sql。
读取最后一条sql语句返回的结果为作答结果，然后与设置的预期返回做比较，一致则该点正确，不一致则不正确。

该测评流程主要核心就是测评语句evalSql的编写了，evalSql重点主要是为了测试studentSql正确与否，因此evalSql的质量将影响到测评的正确性，如下示例：

示例一， “删除t_teacher表中考场编号为空的老师信息” 对于该点来说，其测评的sql语句可以如下设置:

# 测评sql语句
CREATE TABLE `t_teacher`  (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(255) NULL DEFAULT NULL,
  `tel` varchar(20) NULL DEFAULT NULL,
  `rid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE
);

INSERT INTO `t_teacher` VALUES (10001, '宋思', '13544441111', 10001);
INSERT INTO `t_teacher` VALUES (10002, '刘芳', '13544442222', 10002);
INSERT INTO `t_teacher` VALUES (10003, '李明', '13544443333', NULL);

#(studentSql);

select * from t_teacher;

#预期结果
tid|tname|tel|rid
10001|宋思|13544441111|10001
10002|刘芳|13544442222|10002

第一条测评的语句为创建t_teacher表，接下来为3条插入数据的测评语句，接下来为学生提交的作答语句，最后为一条查询的测评语句。如果学生提交的作答sql语句是正确的，能够进行正确的删除，则在执行下来后测评库里的数据就会和预期结果一致，则正确，否则就不正确。

示例二，“更改考生“曾静”的联系方式”。对于该点来说，其测评的sql语句可以如下设置:

# 测评sql语句
CREATE TABLE `t_examinee`  (
  `eid` int(11) NOT NULL AUTO_INCREMENT,
  `ename` varchar(255) NULL DEFAULT NULL,
  `sex` varchar(5) NULL DEFAULT NULL,
  `tel` varchar(20) NULL DEFAULT NULL,
  `examnum` varchar(20) NULL DEFAULT NULL,
  `rid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`eid`) USING BTREE
);

INSERT INTO `t_examinee` VALUES (10001, '陶宝', '男', '13522221111', '240901', 10001);
INSERT INTO `t_examinee` VALUES (10002, '曾静', '女', '13222221111', '240902', 10002);

#(STUDENT_SQL);

SELECT tel FROM t_examinee WHERE ename='曾静';

# 预期结果
tel
13211112222

对于这种提交sql文件的测评方式，如果测评sql语句设置恰当，则该方式基本支持DDL、DML、DQL以及DCL语句。

五、结果数据库的测评

有些操作不需要提交sql语句，则只能通过操作后的库进行评测，因此该方式仅仅针对于对数据库有影响的测评点，对数据库没影响的点无法进行评测，比如DQL类操作就无法评测。

学生连接到考生数据库后，然后按照题目要求进行一顿操作，最后得到一个结果库，将该结果库提交进行测评。

该测评方式和提交sql评测方式类似，基本也是设置测评sql语句，设置预期结果, 区别为没有studentSql, 只有evalSql, 并且evalSql基本大多为一条DQL类的语句。

测评示例sql1
evalSql1
evalSql2
evalSql3
evalSql4
evalSql5
预期返回：
res1

测评示例sql2
evalSql1
evalSql2
evalSql3
evalSql4
evalSql5
预期返回：
res2

测评方法步骤如下：

在结果库中执行测评sql。
读取最后一条sql语句返回的结果为作答结果，然后与设置的预期返回做比较，一致则该点正确，不一致则不正确。

该测评流程主要核心也还是测评语句evalSql的编写，evalSql重点主要是为了测试结果库某些指标正确与否，因此evalSql的质量也将影响到测评的正确性，如下示例：

示例一， “设置t_examinee表的字段sex为char类型，长度为2” 对于该点来说，其测评的sql语句可以如下设置:

# 测评sql语句
SELECT data_type,character_maximum_length FROM information_schema.columns WHERE table_schema='db_exam2' AND table_name='t_examinee' AND column_name='sex';

# 预期结果
data_type|character_maximum_length
char|2

示例二， “添加一条考场信息：考场名称为“205教室”、考场地址为“3号教学楼”” 对于该点来说，其测评的sql语句可以如下设置:

# 测评sql语句
SELECT rname,site FROM t_room WHERE rname='205教室';


# 预期结果
rname|site
205教室|3号教学楼

六、测评指标点的设置

根据前面的测评流程，对于一个题目来说，关键数据信息有初始化的sql语句，指标点数据：指标点1、指标点2、指标点3等等，其指标点数据的主要结构包括，指标点名、是否限制sql作答、测评sql语句，预期结果，分数。如果限制sql作答还可以增加提交的sql文件名等等。

最终的测评流程思路大概如下：

初始化作答数据库：执行初始化sql语句
循环指标点数据
- 限制sql作答
  - 创建测评临时库
  - 提交作答的sql插入到测评sql语句中
  - 执行测评sql
  - 取最后一条测评sql语句，比对结果
  - 销毁测评临时库
- 不限制作答
  - 执行测评sql
  - 比对结果
保存作答结果以及评测结果
销毁作答数据库