简介面向数据库系统课程设计的人事管理后台数据库完整设计方案以公司人事管理为业务场景完整覆盖需求分析、概念设计、逻辑设计、物理设计、数据库实施、功能实现与总结各阶段。报告给出部门、员工、考勤、调动等核心实体关系并结合职工信息录入、修改、删除、模糊查询按年月统计出勤按日期统计迟到早退按年统计部门调动等典型功能展开。资源共1个doc文档压缩包大小1.23MB适合高校数据库课程设计或自学实践作为参考蓝本。目前已有1109人学习可帮助读者理解ER模型到关系模式的转换掌握SQL Server中的表结构设计、约束建立及统计查询实现为完成同类人事管理系统设计提供直接范本。1. 人事管理的数据库课程设计难点从来不在建表拿到人事管理系统这个数据库课程设计任务书的时候多数人的第一反应是“不就是建几张表、写几个增删改查吗”。等真正动手才发现任务书里六个功能要求里有一半是统计类需求按年月统计某个职工的出勤情况、按某年某月某日统计某部门的迟到和早退人数、按年统计各部门调入调出人数——这些才是整个设计真正卡住人的地方。建表只要半天把统计口径理清楚、把表之间的关系设计到能支撑这些统计才是这份设计的主体工作量。这篇方案按课程设计报告的完整流程走一遍从需求分析到数据库实施再到验收答辩适合正在做数据库课程设计的学生直接参考也适合需要快速出一套人事管理系统后台方案的人改一改用。2. 需求分析与概念设计先把六类统计口径钉死在任务书上2.1 从任务书的六个功能要求反推数据需求课程设计任务书给的基本功能很明确信息自动化管理支持修改、删除、插入支持模糊查询按年月统计职工出勤按日期统计部门迟到早退人数按年统计各部门调动人数。这六条不能一句带过每一条都要反推出对应的数据需求。信息增删改查对应的是员工基本信息、部门信息、工作信息和工资信息这几类基础数据需要设计主键来唯一定位一条记录。模糊查询要求姓名、部门名称这类字符串字段要有明确的长度和类型定义否则查询条件写出来性能会很差。按年月统计职工出勤意味着考勤表里必须有日期字段并且一条记录表示的是一天、一次还是一个月这个口径必须在需求阶段定死。按日期统计部门迟到早退人数说明考勤表里迟到、早退必须用可枚举的标记值表示不能存成自由文本。按年统计各部门调入调出人数则要求调动记录同时保留原部门编号和新部门编号并且两个时间字段调离时间、调入时间都要有——如果只设计“当前所在部门”一个字段历史调动信息就彻底丢了。这六条功能要求直接决定了实体范围员工、部门、考勤、工资、调动、工作信息六个实体一个都不能少。少了调动实体第三项统计做不了少了考勤实体第四第五项做不了。2.2 数据字典每个字段的类型、长度、取值范围都要写死需求分析阶段最容易偷懒的地方就是数据字典。很多同学直接跳过这一节去画E-R图结果后面建表的时候字段类型来回改改一次牵动一堆SQL最后只能吃“后悔药”。数据字典是概念设计的基础它的作用就是把每个字段的物理属性先定下来。员工相关的核心数据项整理如下数据项类型长度取值范围/说明员工编号int-10000000 ~ 99999999主键姓名varchar10四个汉字以内性别varchar4男 / 女年龄int-18 ~ 99身份证号varchar1818 位数字入职时间date-记录入职日期所属部门varchar20十个汉字以内联系电话varchar12电话格式基本工资float-6000 ~ 999999考勤和调动的数据项要单独注意。考勤表里缺勤、迟到、早退三个字段在任务书对应的范围是 0 和 1这就是标准的是非标记一天一条记录1 表示当天发生0 表示未发生。调动表里原部门编号、新部门编号是三位数自由组合调离时间和调入时间分别记录两个时间点。设计数据字典的时候把“0/1标记”“按年月统计”“按年统计”这类口径写清楚后面写SQL聚合的时候就不用纠结。2.3 E-R 图设计实体边界和关系基数怎么划概念设计的核心是把六个局部E-R图画出来再合并成全局E-R图。这个项目里实体边界比较清晰员工基本信息只管员工个人属性员工工作信息管职称和工龄部门信息管部门自身属性考勤信息管出勤记录工资信息管薪资构成调动信息管部门变更历史。关系基数需要仔细想的是员工与部门之间。一个部门有若干个职工和一个部门经理每个职工只能在一个部门这是典型的 1:n 关系。部门经理是一个岗位角色可以用部门表里的“部门经理”字段表示也可以理解成员工实体的一个属性约束但不需要单独建一张“经理表”。员工与调动记录之间是 1:n 关系一个员工可以发生多次调动所以调动表必须冗余员工编号作为外键。员工与考勤记录之间也是 1:n一个员工有多天的考勤记录。员工与工作信息、工资信息则是 1:1 关系因为一个员工同时只有一条当前岗位记录和一条当前工资记录。全局E-R图合并的时候有个容易漏的点调动记录同时关联“原部门”和“新部门”这意味着调动表要跟部门表发生两次关联。在E-R图上要用两个联系来表示不能只画一条线连到部门实体。这个细节在逻辑设计阶段会决定外键怎么建立画图的时候漏掉后面SQL就写不对。3. 逻辑设计与物理设计六张表、三条转换规则、一组索引3.1 E-R 图转关系模式的三个落地原则逻辑设计阶段的任务是把E-R图转换成关系模式。课程里讲了三条原则这个项目里全部用得上。一个实体型转换为一个关系模式实体的属性就是关系的属性实体的码就是关系的码。六个实体对应六张表员工基本信息、员工工作信息、部门信息、员工考勤信息、员工工资信息、员工调动信息。一个联系转换为一个关系模式。1:n 的联系码取 n 端的实体码所以员工与部门的联系并入员工表员工表里用“所属部门”字段表示。员工与调动的 1:n 联系并入调动表调动表里带员工编号。1:1 的联系任意一端并入都可以员工工作信息和工资信息都以员工编号为外键不需要额外的中间表。这里没有 m:n 联系所以不需要出现第三张关联表。具有相同码的关系模式可以合并。员工基本信息和员工工作信息的码都是员工编号理论上是能合并的但这个项目里分开更有价值工作信息里的职称、工龄变动频率高单独成表可以避免每次职称调整都去更新员工主表也减少锁竞争。这个取舍可以在报告里写清楚答辩时是加分项。转换完成后得到六个关系模式员工基本信息员工编号姓名性别年龄入职时间所属部门联系电话身份证号员工工作信息员工编号部门编号职称工龄部门信息部门编号部门名称部门经理部门电话员工考勤信息员工编号缺勤迟到早退日期员工工资信息员工编号实发工资底薪扣款补贴奖金加班费员工调动信息员工编号姓名原部门编号新部门编号调离时间调入时间3.2 六张表的字段、类型与约束定义逻辑结构定下来之后物理表结构基本就确定了。员工基本信息表是核心表员工编号是主键不能为空姓名和身份证号也不允许为空年龄允许为空因为有些临时工信息不全。部门信息表相对独立部门编号是三位整数作为主键。部门名称在查询统计里被频繁用到十个汉字的长度来得正好。员工工作信息表里员工编号和部门编号都是外键分别指向员工表和部门表。这里有一个设计细节工作信息表里的“职称”允许为空因为新入职员工可能还没定岗。考勤信息表的时间设计直接服务于统计需求日期是 date 类型缺勤、迟到、早退是 int 型且取值只能为 0 或 1。工资信息表里底薪、补贴、奖金、扣款、加班费、实发工资都是 float实发工资不允许为空其余允许为空。调动信息表的原部门编号和新部门编号都允许为空因为首次入职的员工可能没有原部门。六张表的逻辑和物理属性汇总如下表名主键外键关键约束员工基本信息表员工编号无姓名、身份证号非空员工工作信息表员工编号员工编号、部门编号职称可空部门信息表部门编号无部门名称非空考勤信息表员工编号日期员工编号迟到/早退取值 0 或 1工资信息表员工编号员工编号实发工资非空员工调动信息表员工编号调动时间员工编号、原/新部门编号调离/调入时间可空考勤信息表的主键设计成“员工编号日期”是这里的关键同一天同一个员工只能有一条考勤记录这个复合主键天然保证了数据的唯一性。工资信息表主键直接用员工编号意味着一个员工当前只有一条工资记录如果以后要支持工资历史需要改成“员工编号生效日期”。3.3 物理设计数据文件、日志文件与索引策略物理设计要回答两个问题数据库文件怎么放索引怎么建。文件组织上这套设计用一个主数据文件、一个次要数据文件和一个事务日志文件。主数据文件存初始信息和数据记录次要数据文件放到不同的物理磁盘上把数据横跨存储事务日志文件单独放记录所有更新操作用于恢复。实际建库的时候次要文件是否真的放另一块磁盘取决于机器环境课设环境里至少要把日志文件和数据文件分目录存放。索引策略要对着统计需求来定。员工信息表的员工编号是主键默认建聚簇索引。部门表的部门编号同理。考勤信息表最频繁的查询条件是日期范围和员工编号应该在日期字段上建非聚簇索引否则按年按月统计时全表扫描的数据量会很大。调动信息表的统计条件是原部门编号、新部门编号和年份建议建一个复合索引new_dep_id old_dep_id old_time把统计SQL里最常用的过滤字段都覆盖进去。我一般会额外检查一遍所有外键字段上是否都有索引。外键关联是统计SQL里INNER JOIN的底层依赖外键字段上没有索引关联查询会退化成嵌套循环扫表报告里的“模糊查询”和“按条件统计”功能演示起来就会明显变慢。4. 数据库实施建库、建表、外键与统计查询的完整 SQL4.1 创建数据库主数据文件、次要数据文件与日志文件物理设计落到实施第一步是建库。下面是结合物理设计方案的建库SQL文件路径需要按实际环境调整CREATE DATABASE HRMS ON PRIMARY ( NAME HRMS_Data, FILENAME D:\Data\HRMS_Data.mdf, SIZE 5MB, MAXSIZE 50MB, FILEGROWTH 10% ), FILEGROUP Secondary ( NAME HRMS_Secondary, FILENAME E:\Data\HRMS_Secondary.ndf, SIZE 3MB, MAXSIZE 30MB, FILEGROWTH 10% ) LOG ON ( NAME HRMS_Log, FILENAME D:\Log\HRMS_Log.ldf, SIZE 2MB, MAXSIZE 20MB, FILEGROWTH 10% );FILEGROWTH 设成 10% 而不是固定大小是为了避免数据库扩容时频繁手动干预。MAXSIZE 设上限是为了防止日志文件无限增大把磁盘写满——这在课设环境里是真实存在的坑一个无限增长的日志文件能把磁盘撑爆。4.2 核心表与外键从员工表到考勤、工资、调动表建表顺序有讲究先建部门表再建员工表因为员工表的外键要指向部门表。部门表先建员工表后建子表依赖主表。下面是部门表和员工表的建表语句CREATE TABLE department_info ( department_id INT NOT NULL, department_name VARCHAR(20) NOT NULL, department_tel VARCHAR(11) NOT NULL, department_mgr VARCHAR(8) NOT NULL, CONSTRAINT PK_department PRIMARY KEY (department_id) ); CREATE TABLE staff_info ( staff_id INT NOT NULL, name VARCHAR(10) NOT NULL, gender VARCHAR(4) NOT NULL, age INT NULL, id_card VARCHAR(18) NOT NULL, hire_date DATE NOT NULL, department VARCHAR(20) NOT NULL, phone VARCHAR(12) NOT NULL, basic_salary FLOAT NOT NULL, CONSTRAINT PK_staff PRIMARY KEY (staff_id) );员工编号设成 int 类型范围从 10000000 到 99999999用八位整数可以保证编号位数一致后续做字符串拼接报表时不会出现长短不一的问题。身份证号用 varchar 而不是 int因为身份证号是 18 位超出 int 范围而且可能包含 X任何数值类型都存不下。考勤表和调动表是统计功能的核心外键关系要建完整CREATE TABLE staff_signin ( staff_id INT NOT NULL, absent INT NULL DEFAULT 0, late INT NULL DEFAULT 0, leave_early INT NULL DEFAULT 0, sign_date DATE NOT NULL, CONSTRAINT PK_signin PRIMARY KEY (staff_id, sign_date), CONSTRAINT FK_signin_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id) ); CREATE TABLE staff_transfer ( staff_id INT NOT NULL, name VARCHAR(10) NOT NULL, old_dep_id INT NULL, new_dep_id INT NULL, old_time DATE NULL, new_time DATE NULL, CONSTRAINT PK_transfer PRIMARY KEY (staff_id, old_time), CONSTRAINT FK_transfer_staff FOREIGN KEY (staff_id) REFERENCES staff_info(staff_id), CONSTRAINT FK_transfer_dep_old FOREIGN KEY (old_dep_id) REFERENCES department_info(department_id), CONSTRAINT FK_transfer_dep_new FOREIGN KEY (new_dep_id) REFERENCES department_info(department_id) );考勤表的复合主键staff_id sign_date解决的是“一天一人一条记录”的约束问题这是前面数据字典里定好的口径。调动表同时建了两个外键指向部门表对应E-R图里调动实体和部门实体的两次关联。外键的作用不只是约束它还给查询优化器提供了明确的关联路径统计SQL写作的时候可以少踩很多坑。4.3 三个统计功能出勤汇总、部门迟到早退人数、年度调动任务书里最硬核的三个统计要求全部用GROUP BY配合聚合函数实现。按年月统计某个职工的出勤情况考勤表一天一行缺勤迟到早退都是0/1标记直接求和就是当天数SELECT YEAR(sign_date) AS 统计年份, MONTH(sign_date) AS 统计月份, SUM(absent) AS 缺勤天数, SUM(late) AS 迟到次数, SUM(leave_early) AS 早退次数 FROM staff_signin WHERE staff_id 10000001 AND YEAR(sign_date) 2019 AND MONTH(sign_date) 6 GROUP BY YEAR(sign_date), MONTH(sign_date);这里有个习惯问题WHERE 里用 YEAR() 和 MONTH() 函数包住日期字段会导致索引失效数据量小的时候无所谓但作为课程设计写法不严谨。通常数据量上来之后会改成范围查询sign_date 2019-06-01 AND sign_date 2019-07-01。报告里能主动写这个优化点答辩老师会认为你真的理解统计查询的性能问题。按某年某月某日统计某部门迟到早退人数需要考勤表关联员工表再关联部门表。由于员工表里存的是部门名称关联条件用部门名称匹配SELECT d.department_name AS 部门名称, SUM(CASE WHEN s.late 1 THEN 1 ELSE 0 END) AS 迟到人数, SUM(CASE WHEN s.leave_early 1 THEN 1 ELSE 0 END) AS 早退人数 FROM staff_signin s INNER JOIN staff_info f ON s.staff_id f.staff_id INNER JOIN department_info d ON f.department d.department_name WHERE s.sign_date 2019-06-03 GROUP BY d.department_name;按年统计各部门调入调出人数要从调动表分别按原部门编号和新部门编号聚合SELECT d.department_name AS 部门名称, YEAR(t.old_time) AS 调离年份, COUNT(*) AS 调出人数 FROM staff_transfer t INNER JOIN department_info d ON t.old_dep_id d.department_id WHERE t.old_time IS NOT NULL GROUP BY d.department_name, YEAR(t.old_time) ORDER BY 调离年份, 部门名称;调入人数统计把 old_dep_id 换成 new_dep_id 即可。两个统计SQL可以合并成一个视图或者在存储过程里用参数控制统计维度。我一般会把这类统计封装成存储过程输入参数化之后演示的时候只需要改参数不用反复改SQL文本。5. 这套设计最容易翻车的五个点原因与排查方案5.1 调动表关联部门表查出来是空现象创建“员工个人信息”视图后新入职员工在视图里查不到只有发生过调动的员工能查出来。原因原始视图从 staff_transfer 表出发做 INNER JOIN新员工没有调动记录在调动表里找不到对应行INNER JOIN 就把这些员工过滤掉了。以调动表为主表本质上只覆盖了“有调动历史”的员工子集。解决把主表换成 staff_info用 LEFT JOIN 关联其他表CREATE VIEW v_employee_info AS SELECT f.staff_id AS 员工编号, f.name AS 姓名, f.department AS 所属部门, w.position AS 职位, w.work_age AS 工龄 FROM staff_info f LEFT JOIN staff_work w ON f.staff_id w.staff_id;从这之后我每次写多表关联视图都先确认主表是不是那个“必须全量出现”的表再决定用 INNER JOIN 还是 LEFT JOIN。5.2 考勤统计口径没定死月报对不上现象某员工6月实际迟到3次SQL查出来却是0或者翻了好几倍。原因考勤表设计成一天一行但实施的时候有的同学把缺勤、迟到、早退存成了具体次数而不是0/1标记同一行里写“迟到2”SUM后再按日期去重就乱套了。还有的是忘了在 WHERE 里限定月份把全年的记录都加进来了。解决口径在数据字典阶段就定死absent、late、leave_early 三个字段只能取0或1一天一条记录。统计SQL必须带年份和月份条件用SUM(late)算次数。如果发现考勤数据已经存成多值就先做数据清洗把每行拆成单次记录再统计。5.3 删除员工被外键约束拦住现象执行DELETE FROM staff_info WHERE staff_id 10000001报错提示外键冲突。原因考勤表、工资表、调动表都有外键指向员工表子表里存在引用记录时主表行不能直接删除这是数据库参照完整性的正常保护。解决先删子表再删主表用事务包起来BEGIN TRANSACTION; DELETE FROM staff_signin WHERE staff_id 10000001; DELETE FROM staff_salary WHERE staff_id 10000001; DELETE FROM staff_transfer WHERE staff_id 10000001; DELETE FROM staff_work WHERE staff_id 10000001; DELETE FROM staff_info WHERE staff_id 10000001; COMMIT;生产环境里更稳妥的做法是加一个状态字段做逻辑删除比如status 0表示在职status 1表示离职查询时统一带上WHERE status 0。课设写物理删除可以但要在报告里说明逻辑删除的替代方案。5.4 部门名称变更导致历史统计失效现象某部门从“技术部”改名为“研发中心”调动统计SQL里查不到这个部门的历史调出调入数据。原因员工表里存的是部门名称而不是部门编号调动表也冗余了部门名称部门改名后旧记录里的名称和新名称对不上GROUP BY 就分成了两堆。解决最彻底的办法是把员工表的“所属部门”字段改成 department_id用编号关联部门表名称只存在 department_info 一张表里。这个改动会牵涉所有关联查询SQL所以在逻辑设计阶段就要想清楚。如果报告里已经按名称设计答辩时主动承认这个改进点比被老师问出来要好得多。5.5 中文别名在环境迁移时乱码现象视图和SQL里大量使用中文别名本地SQL Server跑得好好的导出脚本在另一台机器上执行变成乱码。原因字符集和排序规则不一致。中文别名在创建时依赖数据库的默认排序规则迁移到不同排序规则的环境字符串解析直接出错。解决物理设计阶段就用英文做标识符中文语义放到注释里。SQL Server 可以给列加扩展属性做中文说明MySQL 则用 COMMENT 子句。课设报告里中文别名方便阅读但实施脚本建议保留一份英文标识符版本演示时拿英文版跑报告里贴中文可读版。6. 验收与答辩怎样把数据库设计讲成一套完整交付6.1 准备一条贯穿六张表的演示路径验收答辩最忌讳逐个表点开说“这张表存员工那张表存考勤”评委听不到设计的逻辑。我一般会准备一条完整的数据流演示路径从一条业务事件出发串起所有表新员工入职录入员工基本信息表和员工工作信息表月度考勤逐日写入考勤信息表月末工资核算写入工资信息表员工跨部门调动写入调动信息表最后跑三个统计SQL输出报表。每一步都对应一条SQL操作和一张表的展示评委跟着这条线走一遍整个系统是否完整就一目了然。6.2 讲设计依据用三段式答辩环节被问到“为什么这么设计”的时候用三段式回答E-R图怎么从需求反推出来范式怎么检查约束和索引怎么为统计服务。E-R图从六条功能需求反推实体表对应信息管理事实表对应统计查询。范式方面说明员工工作信息单独成表是为了避免职称工龄的更新冗余调动表保留双部门编号是为了避免传递依赖。约束和索引则强调外键保证参照完整性索引覆盖统计SQL的过滤字段。按这个顺序讲评委问不出死角。6.3 主动暴露一个可改进点有一个技巧很实用答辩时主动讲一个自己知道但没做的改进点。比如“员工表目前用部门名称关联部门表规范做法应该改成部门编号外键”、“考勤统计目前全表扫描数据量大时可以按月分区”。主动暴露说明你真思考过比被评委指出来说“这里有问题”要体面得多。这个项目的改进空间是真实存在的讲出来反而证明你对这套设计有完整的认识。从那以后我每次做数据库设计都强制先写完数据字典和统计口径再允许自己碰建表SQL——这个习惯让整个实施阶段几乎没返过工。希望帮到你。本文还有配套的精品资源点击获取