数据库课程设计：职工考勤系统的ER图、范式与SQL实现

简介一份面向数据库课程设计的职工考勤管理信息系统设计文档适合计算机、软件工程等专业学生用于课程设计或毕业设计参考。文档以考勤业务为背景从需求分析出发依次给出数据流图、功能模块图、系统数据流程图、局部与整体E-R图、关系模式和数据关系图并涵盖存储记录结构、索引创建、建库建表、存储过程与触发器等内容形成较完整的数据库设计闭环。资源包共1个doc文件大小约316KB便于直接查阅和按需修改。目前已有67人学习下载可作为课程设计报告的结构范本也可帮助读者理解数据库设计各阶段文档如何撰写。对于需要完成类似信息管理系统设计的同学这份文档能提供从概念结构到逻辑结构再到物理实施的具体示例具有较强的参考价值。1. 数据库课程设计选职工考勤练的不只是增删改查数据库课程设计里职工考勤管理信息系统是被选得最多的题目之一需求看得懂、表能建出来、代码能跑通但它恰恰也是翻车率最高的课设题目。很多人拿到“推荐文档.doc”后第一反应是照着功能模块图做员工增删改查、打卡记录、考勤统计交上去才发现老师真正盯的是数据库设计本身ER 图是否规范、关系模式有没有达到第三范式、并发打卡时数据会不会冲突、跨月统计的 SQL 能不能扛住。这篇笔记按我平时带课设的做法把从 ER 图到建表、从打卡接口到统计 SQL、从并发踩坑到演示数据的完整路径拆开讲清楚。适合正在做这个题目、想把数据库部分做出区分度的同学也适合刚入职需要快速上手考勤类系统开发的工程师。2. 从业务到表结构职工考勤系统的 ER 图与第三范式落地2.1 考勤的四个核心实体先画 ER 图再动手建表常见做法是拿到题目先写界面写到一半才回头补数据库这是课程设计最亏的时序。考勤系统的业务其实很收敛核心实体就四类部门、员工、考勤流水、请假申请。加班可以作为考勤流水的一种类型处理也可以单独拆实体我一般建议单独拆因为加班要记录时间段和审批状态跟上下班打卡的数据结构不一样。ER 图关系是这样一个部门有多名员工员工与部门是多对一一名员工有多条考勤记录考勤记录与员工是多对一一名员工可以有多条请假申请同样是多对一。实体属性按推荐文档里的功能需求拆实体核心属性说明部门部门编号、部门名称名称要加唯一约束防止导入数据时重复员工员工编号、姓名、所属部门、入职日期、手机号员工编号是业务工号和自增主键分开考勤记录打卡日期、上班时间、下班时间、状态一天一条记录是硬性约束请假申请请假类型、开始时间、结束时间、时长、原因、审批状态时长用小时数方便汇总这里有个容易犯的错把员工姓名直接存进考勤记录表。第三范式要求消除传递依赖考勤记录只存员工主键查姓名时再去 JOIN 员工表。表面上看多了一次关联查询实际上让考勤表体积可控否则几万条打卡记录里全是冗余的姓名和部门名称统计 SQL 也会因为数据冗余变得不可信。2.2 关系模式到 MySQL 建表主键、外键与唯一索引画完 ER 图就把关系模式转成建表语句。数据库我默认用 MySQL这也是课程设计里最常见的选型如果学校指定了达梦、人大金仓这类国产数据库下面的建表语句需要把自增列和日期函数的写法稍作调整但表结构设计思路不变。先建部门表再建员工表最后建考勤记录表外键依赖顺序不能乱。CREATE DATABASE IF NOT EXISTS attendance DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE attendance; CREATE TABLE department ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(50) NOT NULL UNIQUE ); CREATE TABLE employee ( emp_id INT PRIMARY KEY AUTO_INCREMENT, emp_no VARCHAR(20) NOT NULL UNIQUE, emp_name VARCHAR(50) NOT NULL, dept_id INT NOT NULL, hire_date DATE NOT NULL, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id) ); CREATE TABLE attendance_record ( att_id INT PRIMARY KEY AUTO_INCREMENT, emp_id INT NOT NULL, work_date DATE NOT NULL, clock_in DATETIME, clock_out DATETIME, status TINYINT DEFAULT 0 COMMENT 0正常 1迟到 2早退 3缺勤, UNIQUE KEY uk_emp_date (emp_id, work_date), CONSTRAINT fk_att_emp FOREIGN KEY (emp_id) REFERENCES employee(emp_id) );主键用自增 INT业务上的员工工号 emp_no 单独加唯一索引这两个不要混在一起。原因很简单工号是给人看的可能因为部门调整重新编号而自增主键只负责标识一行记录不受业务变更影响。外键的作用是保证数据完整性比如打卡记录里的 emp_id 必须真实存在于员工表。唯一索引 uk_emp_date 是后面处理并发打卡的关键先在这里埋下第四节还会展开。建表时字符集一定要显式指定默认的 latin1 在插入中文姓名时会直接乱码这是最常见的低级翻车点。utf8mb4 比 utf8 多支持一部分特殊字符课程设计里用它最省事。2.3 流水表加月度汇总表为什么统计不能只靠一条 SQL考勤记录是典型的流水数据一个月几千条很正常一年就是几万条。如果每次做月度报表都直接对 attendance_record 做全表聚合数据量上来后查询会明显变慢而且课程设计答辩时老师大概率会问“你的报表是怎么算出来的”。这里我一般会再加一张月度汇总表用存储过程或定时任务在月末生成。CREATE TABLE attendance_summary ( summary_id INT PRIMARY KEY AUTO_INCREMENT, emp_id INT NOT NULL, stat_month CHAR(7) NOT NULL, work_days INT DEFAULT 0, late_count INT DEFAULT 0, early_count INT DEFAULT 0, absent_count INT DEFAULT 0, overtime_hours DECIMAL(5,1) DEFAULT 0, UNIQUE KEY uk_emp_month (emp_id, stat_month) );为什么不直接查流水表考勤统计报表通常要按员工展示“本月出勤天数、迟到次数、早退次数、缺勤天数、加班时长”这条 SQL 要同时 JOIN 员工表、考勤记录表还要对日期区间做条件过滤。流水表数据越多聚合越慢。汇总表把计算结果固化下来查询界面只做简单 SELECT响应速度会快很多。汇总表的填充需要用“存在则更新、不存在则插入”的逻辑MySQL 里可以直接写成一条带 ON DUPLICATE KEY UPDATE 的 INSERT 语句具体写法放在下一章统计 SQL 里。这里的重点在于设计阶段就要把“流水表存明细、汇总表存结果”的双层结构定下来后面所有报表功能都基于汇总表开发代码会干净很多。3. 用 Flask 加 MySQL 跑通最小闭环打卡、请假与考勤统计3.1 环境选型与数据库连接池配置课程设计的技术栈不需要炫技但也不能老掉牙。常见做法是 Java Servlet 加 JSP或者 Python Flask 加原生 SQL我倾向推荐 Flask理由只有一个单文件就能把后端和页面逻辑跑起来环境出问题的概率最低能把时间省下来打磨数据库设计。工程结构上先准备依赖文件再写数据库连接工具。连接池用的是 DBUtils 的 PooledDB网上很多教程只写 pymysql 裸连接答辩一被问并发就没法解释。from dbutils.pooled_db import PooledDB import pymysql pool PooledDB( creatorpymysql, maxconnections10, mincached2, blockingTrue, host127.0.0.1, port3306, userroot, passwordyour_password, databaseattendance, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor ) def query(sql, params()): conn pool.connection() try: with conn.cursor() as cur: cur.execute(sql, params) return cur.fetchall() except Exception: return None finally: conn.close()maxconnections 是连接池最大连接数设 10 对课设规模完全够不要贪大连接数过多反而会增加 MySQL 端线程切换开销。mincached 是启动时预创建的连接数设 2 能让第一次请求快一点。blockingTrue 表示连接被占满时请求排队等待而不是直接报错。finally 里的 conn.close() 不是真的关闭连接是把连接归还给连接池这一步漏掉就是第四节要讲的“连接池耗尽”事故。3.2 打卡与请假流程的核心 CRUD 实现打卡接口要处理两条路径上班打卡写 clock_in下班打卡写 clock_out。同一个员工同一天只能有一条记录SQL 用唯一索引配合 ON DUPLICATE KEY UPDATE 实现这是比“先查后插”更可靠的写法。from flask import Flask, request, jsonify from datetime import datetime app Flask(__name__) app.route(/api/clock, methods[POST]) def clock(): data request.get_json() emp_id data[emp_id] clock_type data.get(type) now datetime.now() if clock_type in: sql INSERT INTO attendance_record (emp_id, work_date, clock_in, clock_out) VALUES (%s, CURDATE(), %s, NULL) ON DUPLICATE KEY UPDATE clock_in VALUES(clock_in) query(sql, (emp_id, now)) elif clock_type out: sql INSERT INTO attendance_record (emp_id, work_date, clock_in, clock_out) VALUES (%s, CURDATE(), NULL, %s) ON DUPLICATE KEY UPDATE clock_out VALUES(clock_out) query(sql, (emp_id, now)) return jsonify({code: 0, message: 打卡成功})这段代码的关键是 ON DUPLICATE KEY UPDATE如果 uk_emp_date 索引冲突说明当天已经有记录此时不插入新行而是更新对应的上班或下班时间。所有 SQL 都用 %s 占位符传参不要拼字符串这是防止 SQL 注入的底线也是答辩时老师喜欢问的点。请假流程比打卡多一层审批状态。学生做的课设一般不需要完整审批流但至少要有“提交申请”和“审批通过/驳回”两个动作。提交请假时插入一条申请记录审批通过后才把时间区间写入考勤汇总这样请假和缺勤不会重复计算。INSERT INTO leave_request (emp_id, leave_type, start_time, end_time, hours, reason, status) VALUES (%s, %s, %s, %s, %s, %s, PENDING); UPDATE leave_request SET status %s, approver %s WHERE leave_id %s;3.3 迟到早退缺勤加班四条统计 SQL 的边界写法考勤统计是这门课设的评分重心。迟到、早退、缺勤、加班四条 SQL 各有各的边界先说迟到以 9 点上班时间为准当天打卡时间大于 9 点算迟到。SELECT emp_id, COUNT(*) AS late_days FROM attendance_record WHERE work_date BETWEEN %s AND %s AND clock_in TIMESTAMP(work_date, 09:00:00) GROUP BY emp_id;这里必须用 TIMESTAMP(work_date, 09:00:00) 把日期和固定时间拼成 DATETIME再跟 clock_in 比较。如果你拆成 DATE(clock_in) work_date 或者用 HOUR(clock_in) 判断都会把跨天打卡和日期边界搞混。早退同理下班时间早于 18 点算早退但要注意 clock_out 可能为空空值直接排除。SELECT emp_id, COUNT(*) AS early_days FROM attendance_record WHERE work_date BETWEEN %s AND %s AND clock_out IS NOT NULL AND clock_out TIMESTAMP(work_date, 18:00:00) GROUP BY emp_id;缺勤是这四条里最容易写错的。缺勤的定义是当天没有打卡记录所以要从员工表 LEFT JOIN 考勤记录找出没有匹配记录的日期而不能在考勤记录表里查 status 字段因为你可能根本没插入那条流水。SELECT e.emp_id, e.emp_name, COUNT(*) AS absent_days FROM employee e LEFT JOIN attendance_record a ON a.emp_id e.emp_id AND a.work_date BETWEEN %s AND %s WHERE a.att_id IS NULL GROUP BY e.emp_id, e.emp_name;加班时长的统计更麻烦一点。我一般把加班单独建表记录加班的起始时间和结束时间用 TIMESTAMPDIFF 算小时数。要注意的是加班可能跨天比如从 21 点到次日凌晨 1 点单纯按日期分组会把加班时长拆成两天需要跟需求方确认口径。课程设计里我建议在加班表里直接存 hours 字段提交时算好避免在统计 SQL 里做跨天处理。月度汇总表的填充也在这里完成用 INSERT 加 ON DUPLICATE KEY UPDATE 把统计结果写进 attendance_summary这样报表页只查汇总表就够了。汇总的统计区间用 %s 和 %s 传入月份首尾日期不要用 DATE_FORMAT 对整列做函数运算那样索引会失效。4. 数据库课程设计常见问题排查并发、时区与 SQL 模式4.1 同一天重复打卡先看唯一索引而不是去重查询现象考勤打卡高峰时日志里出现 Duplicate entry 1-2024-06-18 for key uk_emp_date或者员工预览里看到同一天两条打卡记录统计迟到次数直接翻倍。 原因前端按钮被重复点击或者两个请求同时进来。代码里的逻辑是“先 SELECT 判断今天有没有记录没有就 INSERT”两个并发请求都查不到记录于是都执行了 INSERT最终插入两条或其中一条撞唯一索引报错。 解决靠程序判断不可靠把唯一索引当作并发安全的第一道防线。attendance_record 上已经建了 uk_emp_date配合 INSERT ... ON DUPLICATE KEY UPDATE数据库会自己决定插入还是更新。如果坚持用“先查后插”必须把查询和插入放进同一个事务并且把查询改成 SELECT ... FOR UPDATE但这样锁粒度大并发一高就排队远不如唯一索引加 UPSERT 干净。4.2 only_full_group_by 报错与考勤统计的 SQL 兼容性现象运行统计 SQL 时MySQL 报“Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column”这条错误在 5.7 及以上版本非常常见“SQL 模式”也会出现在答辩提问里。 原因MySQL 5.7 起默认开启 ONLY_FULL_GROUP_BY要求 SELECT 里出现的非聚合字段必须全部出现在 GROUP BY 中。比如按 emp_id 分组统计迟到次数同时又 SELECT emp_nameemp_name 与分组字段没有函数依赖关系直接报错。 解决两个方向。第一个是改 SQL把 emp_name 也加进 GROUP BY或者先按 emp_id 聚合得到结果集合再 JOIN 员工表补名字。第二个是改 sql_mode执行 SET sql_modeSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION临时解决问题但答辩时容易被追问为什么改数据库配置而不改 SQL。我建议走第一条用子查询加 JOIN 的写法任何 MySQL 版本都能跑。SELECT t.emp_id, e.emp_name, t.late_days FROM ( SELECT emp_id, COUNT(*) AS late_days FROM attendance_record WHERE work_date BETWEEN %s AND %s AND clock_in TIMESTAMP(work_date, 09:00:00) GROUP BY emp_id ) t JOIN employee e ON e.emp_id t.emp_id;4.3 连接池耗尽导致页面转圈不是 SQL 慢现象系统刚启动时一切正常跑了几百次打卡或查询后页面全部卡住转圈过几分钟又自动恢复。很多人以为是查询 SQL 慢用 EXPLAIN 查半天没发现问题。 原因代码里获取数据库连接后没有释放。最典型的是写了 conn pymysql.connect() 但是异常路径里漏掉 conn.close()连接数很快达到 MySQL 的 max_connections 上限新请求全部挂起。恢复是因为部分连接被数据库超时断开看起来很“玄学”。 解决用连接池管理连接DBUtils 的 PooledDB 已经集成了回收机制。关键点是把获取连接和释放连接写在 try/finally 里finally 中调用 conn.close() 归还连接。还要检查连接池参数maxconnections 不要超过 MySQL 的 max_connections默认 151池设 10 到 20 就够了。出了问题先用 SHOW PROCESSLIST 看大量 Sleep 连接就明白了。4.4 数据库死锁先批量提交还是先查后插现象批量导入上月考勤流水时程序报 Deadlock found when trying to get lock; try restarting transaction而且每次卡的位置不一样。 原因两个事务同时更新多条记录但更新顺序不同。假设事务 A 先更新 emp_id1 再更新 emp_id2事务 B 先更新 emp_id2 再更新 emp_id1两者互相持有对方需要的行锁形成循环等待。考勤导入通常会把一个月的打卡记录分批次写入撞概率很高。 解决让事务内多条记录的加锁顺序全局一致比如所有更新都按 emp_id 升序执行事务范围尽量缩小一批 500 条拆成 50 条一组缩短持锁时间还可以调低 innodb_lock_wait_timeout默认 50 秒太长设为 30 秒让冲突快速暴露。最重要的教训是批量导入脚本一定要能断点续跑导入前先查一下目标月是否已存在记录用汇总表的唯一索引拦住重复执行。5. 让考勤数据可复查演示数据脚本与 EXPLAIN 检查5.1 用存储过程生成六个月考勤演示数据答辩前最怕的就是系统里只有一两条测试数据老师想看月度统计报表界面上空荡荡。手写几十条 INSERT 太累用存储过程一次性生成半年的演示数据既省时间又显得设计完整。DELIMITER $$ CREATE PROCEDURE fill_attendance(IN start_date DATE, IN end_date DATE) BEGIN DECLARE cur_date DATE; SET cur_date start_date; WHILE cur_date end_date DO IF WEEKDAY(cur_date) 5 THEN INSERT INTO attendance_record (emp_id, work_date, clock_in, clock_out) SELECT emp_id, cur_date, TIMESTAMP(cur_date, 08:40:00), TIMESTAMP(cur_date, 18:10:00) FROM employee; END IF; SET cur_date DATE_ADD(cur_date, INTERVAL 1 DAY); END WHILE; END$$ DELIMITER ; CALL fill_attendance(2024-01-01, 2024-06-30); DROP PROCEDURE IF EXISTS fill_attendance;WEEKDAY 返回 0 到 60 到 4 是周一到周五这里直接跳过周末符合工作日考勤的常见口径。TIMESTAMP 函数把日期和固定时间拼成 DATETIME写入后打卡记录是规范的。存储过程用完 DROP 掉避免系统里留着一堆测试用的过程对象。生成完数据后记得跑一下月度汇总填充 SQL让 attendance_summary 里有对应记录报表页面才能真正显示统计结果。5.2 答辩前用 EXPLAIN 验证慢查询课设快完成时花十分钟用 EXPLAIN 检查核心查询的执行计划能直接堵住“为什么查这么慢”的追问。把统计 SQL 前面加 EXPLAIN看输出里的 type 和 rows 字段。EXPLAIN SELECT emp_id, COUNT(*) FROM attendance_record WHERE work_date BETWEEN 2024-01-01 AND 2024-06-30 GROUP BY emp_id;正常应该走索引type 列为 range 或 refrows 在一个合理范围内。如果出现 ALL 全表扫描并且 rows 等于整表行数说明 work_date 上没有索引加一个普通索引就能解决。考勤记录表创建时只建了 (emp_id, work_date) 的联合唯一索引按日期范围统计会跨越多个 emp_id所以最好额外单独建一个 work_date 索引让范围查询走索引。CREATE INDEX idx_att_date ON attendance_record(work_date);这是这套方案里我最后悔没早做的事。带课设时有个学生全程没踩坑唯一的问题是日期字段用了 VARCHAR 存储月度排序和区间比较看起来都对直到跨年时才出现“2024-01-01”排在“2023-12-31”后面这种错乱查了半晚上才意识到数据类型的锅。那次以后我凡是考勤类系统日期一律用 DATE 或 DATETIME绝不用字符串存。希望这些边界能帮你在课程设计里少走几步弯路祝顺利。本文还有配套的精品资源点击获取