数据库综合实训——校园门禁数据库系统

综合实训题目介绍

由于“新冠疫情”原因，学校处于一个半封闭管理的状态，学生请假出校成了学校现阶段学生管理工作中比较繁重的一项工作。先计划开发一个智能门禁系统的功能，请根据需求设计对应的数据库。

需求一

系统集中统一进行学生和教职工信息管理，包含识别所必须的人员（二维码身份信息Java等实现）面部照片，以及学生和教职工相关的姓名、照片、职位、性别等信息，系统可将每名人员进行分区域授权管理通行。

学生（学号、姓名、年龄、所在系、班级、班主任的教师号）

教师（教师号、姓名、年龄、所在系、所管理的班级）

需求二

学生如果有事需要出校需要向班主任请假，班主任审批后方可出校。班主任通过审批后生成一个请假详情报告，包括：（请假编号、学生姓名、班级、请假开始时间、请假结束时间、请假事由、请假总天数、班主任）。

请假单（请假编号、学号、请假开始时间、请假结束时间、请假事由、请假总天数、实际出校时间、实际回校时间、请假单状态）

需求三

学生在校门口出校时，进行二维码识别(人脸识别)是否有权限出校（查询请假单是否存在），请假单存在，在对应的记录中登记出校时间，并对该请假记录标记为“已出校”。学生回校进行登记进校时间，并对该请假记录标记为“已回校”。系统中需要保留历史请假数据。

需求四

学生回校进行扫码登记进校时间，如果进校时间晚于请假结束时间，超过时间在半小时以内，一周不准请假出校，将学生信息记录于《灰名单》；超过时间在半小时以外，两周不准请假出校，将学生信息记录于《黑名单》。这里是一个表，两种不同的状态。

灰黑名单（学号、状态、加入时间）

数据库设计及各表相关信息

基本的师生信息需要三张表进行储存，分别是学生信息表（persons_student）、教师信息表（persons_teacher）、班级信息表（admin_class_infoc）。通过这三张表进行关联即可。

请假单的部分通过新增一张请假单主表（leave_form）、审批状态表（application_status）以及黑、灰名单表（ban_list）来实现，数据库ER图见上图

各表SQL文件

数据库仅包含结构的SQL已导出在主文件夹下的结构sql文件文件夹中

数据库功能的实现

学生提交请假申请

学生提交请假申请时需要的字段为：学生学号（STUDENT_ID）、请假开始时间（START_DATE）、请假结束时间（END_DATE）、请假事由（REASON）以上信息将使用下方的储存过程传递进数据库

delimiter $$
CREATE PROCEDURE student_input_data(IN INPUT_STUDENT_ID VARCHAR(50),IN INPUT_START_DATE DATETIME,IN INPUT_END_DATE DATETIME,IN INPUT_REASON TEXT)
BEGIN

INSERT INTO leave_form
(STUDENT_ID,START_DATE,END_DATE,REASON)
VALUES
(INPUT_STUDENT_ID,INPUT_START_DATE,INPUT_END_DATE,INPUT_REASON);

END $$
delimiter;

由于题中要求：

学生回校进行扫码登记进校时间，如果进校时间晚于请假结束时间，超过时间在半小时以内，一周不准请假出校，将学生信息记录于《灰名单》；超过时间在半小时以外，两周不准请假出校，将学生信息记录于《黑名单》。这里是一个表，两种不同的状态。

在本数据库的设计中，判断学生是否位于黑/灰名单的逻辑是：

如果
    学生学号不在黑/灰名单中 则：
        直接执行传入数据（调用`student_input_data`）
如果
    学生学号在黑/灰名单中 则：
        查询黑/灰名单状态：
            如果
                学生在灰名单中但已过惩罚期（即7天） 则：
                    删除此学生在灰名单中的记录
                    将数据传入数据库（调用`student_input_data`）
                否则：
                    输出提示信息：`你在灰名单中，无法提交申请！`
            如果
                学生在黑名单中但已过惩罚期（即14天）则：
                    删除此学生在黑名单中的记录
                    将数据传入数据库 （调用`student_input_data`）
                否则：
                    输出提示信息：`你在黑名单中，无法提交申请！`  

即封装为下面的存储过程

delimiter $$
CREATE PROCEDURE application_input_data(IN INPUT_STUDENT_ID VARCHAR(50),IN INPUT_START_DATE DATETIME,IN INPUT_END_DATE DATETIME,IN INPUT_REASON TEXT)
BEGIN

IF
    (SELECT BAN_STUDENT_ID FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID) IS NULL THEN
        CALL student_input_data(INPUT_STUDENT_ID,INPUT_START_DATE,INPUT_END_DATE,INPUT_REASON);
    ELSE
        IF (SELECT BAN_STATUS FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID) = '2' THEN  
            IF (NOW() > DATE_ADD((SELECT ADD_TIME FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID),INTERVAL 7 DAY)) THEN
                DELETE FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID;
                CALL student_input_data(INPUT_STUDENT_ID,INPUT_START_DATE,INPUT_END_DATE,INPUT_REASON);
            ELSE
                SIGNAL SQLSTATE '22012' SET
                    MESSAGE_TEXT = '你在灰名单中，无法提交申请！';
            END IF;
        ELSE
            IF (NOW() > DATE_ADD((SELECT ADD_TIME FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID),INTERVAL 14 DAY)) THEN
                DELETE FROM ban_list WHERE BAN_STUDENT_ID = INPUT_STUDENT_ID;
                CALL student_input_data(INPUT_STUDENT_ID,INPUT_START_DATE,INPUT_END_DATE,INPUT_REASON);
            ELSE
                SIGNAL SQLSTATE '22013' SET
                    MESSAGE_TEXT = '你在黑名单中，无法提交申请！';
            END IF;
        END IF;
END IF;

END $$
delimiter;

封装自动计算请假天数

由于要求存储学生请假天数信息，所以可以使用DATEDIFF函数，使用请假开始的时间和结束的时间计算出学生请假的数据并传入数据库。此处使用两个触发器实现此项数据填写的自动化。

DELIMITER $$

CREATE TRIGGER number_of_days_when_create AFTER INSERT ON leave_form FOR EACH ROW

BEGIN

UPDATE leave_form SET LEAVE_DAY = (DATEDIFF(new.END_DATE,new.START_DATE));

END $$
DELIMITER;

DELIMITER $$

CREATE TRIGGER number_of_days_when_update AFTER UPDATE ON leave_form FOR EACH ROW

BEGIN

UPDATE leave_form SET LEAVE_DAY = (DATEDIFF(new.END_DATE,new.START_DATE));

END $$
DELIMITER;

判断学生是否有出校资格

在学生提交申请后，审核状态（APPLICATION_STATUS）将自动赋值为2即处于“等待审批”的状态，在此状态下或值为0（即处于“审批不通过”的状态）时学生不可以出校，只有当审核状态处于1即处于“审批通过”的状态时才可出校，由此可以封装为下面的存储过程：

delimiter $$
CREATE PROCEDURE judgment_leave_form_status(IN INPUT_STUDENT_ID VARCHAR(50),OUT OUTPUT_INFO TINYINT)
BEGIN

SELECT IF(STUDENT_ID IS NOT NULL,'1','0') INTO OUTPUT_INFO 
    FROM leave_form 
    WHERE STUDENT_ID = INPUT_STUDENT_ID 
        AND leave_form.LEAVE_FORM_ID = (SELECT MAX(LEAVE_FORM_ID) FROM leave_form WHERE STUDENT_ID = INPUT_STUDENT_ID) 
        AND leave_form.APPLICATION_STATUS = '1'
        AND NOW() BETWEEN START_DATE AND END_DATE;

END $$
delimiter;

获取学生最新的请假单信息

由于系统要求储存历史请假信息，所以一个学生可能会有多个请假单，此时我们需要获取最新请假单的ID作为变量来限制后续出入校登记时间时所修改的请假单。如果不获取使用最新请假单ID，仅通过学生学号来限制修改条件的话将会同时修改此学生历史上所提交并通过审批的所有记录。 由此，将查询学生最新请假单信息的存储过程封装为：

delimiter $$
CREATE PROCEDURE get_latest_form(IN INPUT_STUDENT_ID VARCHAR(50),OUT OUT_ID INT)
BEGIN

SELECT LEAVE_FORM_ID INTO OUT_ID FROM leave_form 
    WHERE STUDENT_ID = INPUT_STUDENT_ID 
        AND leave_form.LEAVE_FORM_ID = (SELECT MAX(LEAVE_FORM_ID) FROM leave_form WHERE STUDENT_ID = INPUT_STUDENT_ID);

END $$
delimiter;

注意：由于此判断逻辑，假设学生有两个分别是今天和下周的请假，那么此存储过程将仅会返回下周的请假单信息，导致学生今天的请假无法打卡，无法出校门！

由于时间关系，此处不再进一步优化判断逻辑。

学生的出入校登记

出入校登记的基本逻辑相同，通过调用并查询最新的请假单是否已审批通过来判断学生的出入校的合法性，并登记

出校登记

delimiter $$
CREATE PROCEDURE out_of_school_registration(IN INPUT_STUDENT_ID VARCHAR(50))
BEGIN

CALL judgment_leave_form_status(INPUT_STUDENT_ID,@form_status);
CALL get_latest_form(INPUT_STUDENT_ID,@get_form_id);

UPDATE leave_form SET LEAVE_START_DATE = NOW() WHERE LEAVE_FORM_ID = @get_form_id AND STUDENT_ID = INPUT_STUDENT_ID AND @form_status = 1;

END $$
delimiter;

回校登记

delimiter $$
CREATE PROCEDURE enter_school_registration(IN INPUT_STUDENT_ID VARCHAR(50))
BEGIN

CALL get_latest_form(INPUT_STUDENT_ID,@form_id);
CALL get_latest_form(INPUT_STUDENT_ID,@get_form_id);

UPDATE leave_form SET LEAVE_END_DATE = NOW() WHERE STUDENT_ID = INPUT_STUDENT_ID AND LEAVE_FORM_ID = @form_id AND @form_status = 1;

END $$
delimiter;

生成请假单详细信息

通过视图来生成相对适合人类阅读的表单信息

CREATE VIEW leave_form_info AS SELECT leave_form.LEAVE_FORM_ID AS `请假编号`,
    persons_student.`NAME` AS `学生姓名`,
    admin_class_infoc.CLASS_TITLE AS `班级`,
    leave_form.START_DATE AS `请假开始时间`,
    leave_form.END_DATE AS `请假结束时间`,
    leave_form.REASON AS `请假事由`,
    leave_form.LEAVE_DAY AS `请假总天数`,
    persons_teacher.`NAME` AS `班主任`,
    application_status.APPLICATION_TITLE AS `审批状态`,
    leave_form.LEAVE_START_DATE AS `实际出校时间`,
    leave_form.LEAVE_END_DATE AS `实际回校时间`,
    leave_form.TEACHER_NOTES AS `教师审批备注`,
    (CASE
        WHEN leave_form.APPLICATION_STATUS = '1' THEN
            (CASE
                WHEN leave_form.LEAVE_START_DATE IS NULL THEN '未出校'
                WHEN leave_form.LEAVE_START_DATE IS NOT NULL THEN
                    (CASE
                        WHEN leave_form.LEAVE_END_DATE IS NULL THEN '已出校'
                        ELSE '已回校'
                    END
                    )
            END
            )
        ELSE '审批状态不为通过'
        END
    ) AS `学生请假状态`
 FROM leave_form
 INNER JOIN persons_student ON persons_student.STUDENT_ID = leave_form.STUDENT_ID
 INNER JOIN admin_class_infoc ON persons_student.ADMIN_CLASS_ID = admin_class_infoc.CLASS_ID
 INNER JOIN persons_teacher ON persons_teacher.TEACHER_ID = admin_class_infoc.TEACHER_ID
 INNER JOIN application_status ON application_status.APPLICATION_ID = leave_form.APPLICATION_STATUS

封装添加到黑/灰名单

下方储存过程用于将违规学生添加到黑/灰名单

delimiter $$
CREATE PROCEDURE add_to_ban_list(IN INPUT_STUDENT_ID VARCHAR(50),IN INPUT_BAN_STATUS TINYINT)
BEGIN

INSERT INTO ban_list
(BAN_STUDENT_ID,BAN_STATUS,ADD_TIME)
VALUES
(INPUT_STUDENT_ID,INPUT_BAN_STATUS,NOW());

END $$
delimiter;

下方的触发器在学生回校登记时自动判断是否违规并将违规学生记录进黑/灰名单

DELIMITER $$

CREATE TRIGGER add_to_ban_list_trigger AFTER UPDATE ON leave_form FOR EACH ROW

BEGIN

IF
    (NOW() NOT BETWEEN new.START_DATE AND new.END_DATE) THEN
    IF
        (NOW() < DATE_ADD(new.END_DATE,INTERVAL 30 MINUTE)) THEN
            CALL add_to_ban_list(new.STUDENT_ID,'2'); #2代表灰名单
        ELSE 
            CALL add_to_ban_list(new.STUDENT_ID,'1'); #1代表黑名单
    END IF;
END IF;

END $$
DELIMITER;