2024-11-20-数据库课程设计-工资管理系统-MySQL
数据库课程设计-工资管理系统-MySQL
目录
第一节 需求分析
1.1 需求分析 概述
随着当今企业人员数量的不断增加，企业的工资管理工作也就变得越来越复杂。对于一个现代化的企业来说，信息化管理是必须的，而财务管理部门作为事业单位的重点部门，同样需要加强信息化管理。设计工资管理系统的目的就是为了帮助财务部门能更好地管理本单位的职工工资，提高工作效率，实现职工工资信息管理的规范化和自动化。明确查询公司职工某年某月的工资情况，通过职工工资管理系统，也能查询到职工本身的一些基本信息。如何对职工工资进行信息化的管理，减轻财务部门的劳动强度，并且确保相关数据的安全，信息处理的高效，正是本数据库设计目的所在。
1.2 功能需求分析
以我国某国有企业为例设计数据库，通过网上查询资料和询问相关经验人士，了解此国企的职位等级制度和工资分配制度。为此一共总结归纳出使用的四大模块，分别为人事数据管理模块、考勤数据管理模块、工资数据管理模块以及工资计算公式设置模块。
1.2.1 人事数据管理模块
人事数据管理模块是 企业基础性资料信息的体现,它主要功能是统计、管理员工个人和相关部门的信息
。员工种类分为在职人员和退休人员。企业内部的人事档案等相关材料都是依靠人事数据管理模块来进行的，每当公司有新招聘的员工入职，都需要对该名新员工进行人事数据的登记与记录，并记录入职日期，每年应自动更新员工工龄。每个员工都应具有唯一员工编号，员工编号由两部分组成，第一部分为入职年份，第二部分为首次入职部门代号，第三部分为顺序号，例如2000010001表示为该员工为2000年入职，首次入职的部门编号为01，是第一位员工。员工的编号保持不变，直到退休。当员工处于离职、退休等工作状态，需要对人事数据做到及时更新、修改、删除等操作，并加以记录。员工的个人信息应真实可靠，只有人事部管理员有修改信息权限。但修改权限不可随意使用，需经过层层审批批准才可使用。当员工从在职状态处于退休状态时，应记录下退休日期，以便工资数据管理使用。该模块功能如图所示：
图 1 人事数据管理模块结构图
1.2.2 考勤数据管理模块
考勤数据管理模块是记录员工每日出勤状态，根据签到与签退时间来判断是否为早退、迟到或缺勤等违规状态 [5]
。与此同时，也将记录员工的加班时间、请假时间，以此为依据，对员工进行奖罚。本数据库设计只针对月考勤数据做相关分析，以月考勤数据来继续展开设计。其中加班时间以小时为计数单位，请假时间以天为计数单位，其中规定每月可有两天带薪休班机会 [6]
。
1.2.3 工资数据管理模块
工资数据管理模块是主要包括在职员工与退休员工的工资的数据录入、查询显示和工资打印。由财务部管理员统一统计与录入。在职员工每月工资主要由基本工资、工龄补贴、绩效工资、考勤奖罚、五险一金和个人所得税构成 [2]
。工龄补贴主要规则为：
1.在本公司连续工作满一年的员工每月工龄工资为￥50元整。
2.在本公司连续工作满两年的员工每月工龄工资为￥100元整。
3.在本公司连续工作满三年的员工每月工龄工资为￥150元整。
4.在本公司连续工作满四年的员工每月工龄工资为￥180元整。
5.以此类推，之后在本公司工作每增加一年，每月工龄工资相应增加￥30元整。累计十年封顶
，十年及十年以上每月工龄工资为
￥
360
元整。
在扣除项目中五险一金包括基本养老保险、基本医疗保险、失业保险、工伤保险和生育保险，及住房公积金。采取缴纳比例如图所示：
图 2 五险一金缴纳比例图
个人所得税按月
换算，以5000元为起征点，换算规则如下表所示：
| 级数 | 全月应纳税所得额 | 税率（%） | 速算扣除数 |
| 1 | 不超过 3000 元的 | 3 | 0 |
| 2 | 超过3000元至12000元的部分 | 10 | 210 |
| 3 | 超过12000元至25000元的部分 | 20 | 1410 |
| 4 | 超过25000元至35000元的部分 | 25 | 2660 |
| 5 | 超过35000元至55000元的部分 | 30 | 4410 |
在 考勤奖罚中规定每次迟到早退每次扣25元，无故旷工一天100元，在规定休假次数外，请假一天扣50元，加班每小时奖励20元。根据职位设置基本工资，以三个职称为例部门经理基础工资每月8500元，部门总管基础工资每月7500元，普通员工基础工资每月6500元。
退休员工工资由基础养老金和个人账户养老金构成。住房公积金默认为退休的第一个月已全部领取。根据调查，全省上年度在岗职工月平均工资定为7000元，为了简便计算则退休员工以此标准计算。根据国家延迟退休政策统一规定男女65岁退休，个人账户存储额计发月数统一为120。
所有员工的薪酬都是公司通过银行支付到员工银行卡的方式发放的，并且需要录入到数据库中。在对工资数据管理层面上，相关管理工作人员必须及时将职员的工资数据录入到数据库，系统则会根据员工该月的加班、迟到等实际情况运用固定的公式来对工资展开计算，财务工作人员则需要对工资数据进行审核。保证数据的严谨性。同时财务人员具有对工资数据修改的权限，对发现的错误及时修改，而公司的普通员工只有浏览查询、打印工资明细的权限。该模块功能如图所示：
图 4 工资数据管理模块结构图
1.2.4 工资计算公式设置模块
（1）在职员工工资计算公式：
个人所得税计算公式
个人所得税=全月应纳税所得额*税率-速算扣除数
全月应纳税所得额=税前工资-五险一金-个人专项扣除项目
五险一金计算公式：
五险一金缴纳=税前工资*（8%+0.2%+2%+12%）
其中五险一金中工伤保险和生育保险费率由单位全额缴纳，个人不需要缴费。
考勤奖罚计算公式：
奖罚金额=20a-10b-100c-50b
其中a为加班小时数,b为早退迟到累计小时数，c为旷工天数，d为请假天数（总请假天数-休班天数）。
（2）退休员工工资计算公式：
基础养老金=（全省上年度在岗职工月平均工资+本人指数化月平均缴费工资）/2×缴费年限×1%
本人指数化月平均缴费工资=全省上年度在岗职工月平均工资×本人平均缴费指数
本人平均缴费指数=Σ（在职时月均工资/全省平均工资）/缴费年数
个人账户养老金=个人账户存储额/计发月数
1.3 数据需求分析
1.3.1 数据项定义
表 2 员工信息表
| 数据项名 | 别名 | 数据类型 | 取值范围 |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 姓名 | ename | varchar(11) | 汉字 |
| 性别 | sex | varchar(11) | 取值：男或女 |
| 出生日期 | birthday | Date | （1945-01-01， 2003-01-01） |
| 联系方式 | phone | varchar(11) | 规定标准11位且唯一 |
| 入职时间 | intime | Date | （1998-01-01，2023-12-01） |
| 退休时间 | outtime | Date | 默认为空 |
| 任职状态 | state | varchar(11) | 取值：在职或退休 |
表 3 个人专项附加扣除项目表
| 数据项名 | 别名 | 数据类型 | 取值范围 |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 子女教育 | pro1 | double | 0或1000的整数倍 |
| 继续教育 | pro2 | double | 0或400 |
| 贷款利息 | pro3 | double | 0或1000 |
| 租房 | pro4 | double | （0，800，1100，1500） |
| 赡养老人 | pro5 | double | （0，1000，2000，4000） |
| 大病医疗 | pro6 | double | 0到8万 |
表 4 部门表
| 数据项名 | 字段名 | 数据类型 | 长度 |
| 部门编号 | did | varchar(11) | 11 |
| 部门名 | dname | varchar(11) | 11 |
| 部门地址 | address | varchar(11) | 11 |
表 5 任职表
| 数据项名 | 别名 | 数据类型 | 取值范围 |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 部门编号 | did | varchar(11) | 依据部门表 |
| 职称 | zhicheng | varchar | 普通员工或部门经理或部门总管 |
| 任职时间 | rtime | Date |
表 6 月考勤表
| 数据项名 | 别名 | 数据类型 | 取值范围 |
| 考勤月份 | k_month | varchar(11) | 格式“年份-月份” |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 加班时间 | over_hour | int | 默认为零 |
| 请假天数 | leave_day | int | 默认为零 |
| 迟到次数 | late_time | int | 默认为零 |
| 早退次数 | early_time | int | 默认为零 |
| 缺勤次数 | absent_time | int | 默认为零 |
表 7 在职员工工资表
| 数据项名 | 别名 | 数据类型 | 取值范围 |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 工资月份 | gmonth | varchar(11) | 格式“年份-月份” |
| 基本工资 | basic_pay | double | 默认为零 |
| 工龄补贴 | seniority_pay | double | 默认为零 |
| 绩效工资 | jixiao_pay | double | 默认为零 |
| 考勤奖惩 | kaoqin_pay | double | 默认为零 |
| 五险一金 | wuxian | double | 默认为零 |
| 个人所得税 | tax | double | 默认为零 |
| 实发工资 | shifa_pay | double | 默认为零 |
| 发放状态 | gstate | varchar(11) | 已发或未发 |
表 8 退休员工工资表
| 数据项名 | 别名 | 数据类型 | 说明 |
| 员工编号 | eid | varchar(11) | 由入职年份+部门号+顺序号组成 |
| 工资月份 | tmonth | varchar(11) | 格式“年份-月份” |
| 在职月均工资 | av_pay | double | 默认为零 |
| 基础养老金 | basic_yl | double | 默认为零 |
| 个人账户养老金 | person_yl | double | 默认为零 |
| 实发工资 | shifa_yl | double | 默认为零 |
| 发放状态 | tstate | varchar(11) | 取值：已发或未发 |
1.3.2 数据结构定义
表 9 数据结构表
| 数据结构名 | 数据结构组成 | 含义说明 |
| 员工信息表 | 员工编号+姓名+性别+出生日期+联系方式+入职时间+退休时间+任职状态 | 存储员工相关的个人信息 |
| 部门表 | 部门编号+部门名+部门地址 | 存储部门相关信息 |
| 任职表 | 员工编号+部门编号+职称+任职时间 | 存储员工的职位信息 |
| 月考勤表 | 考勤月份+员工编号+加班时间+请假天数+迟到次数+早退次数+缺勤次数 | 存储员工的每月的考勤记录 |
| 个人专项附加扣除项目表 | 员工编号+子女教育+继续教育+贷款利息+租房+赡养老人+大病医疗 | 存储员工个人情况 |
| 在职员工工资表 | 员工编号+工资月份+基本工资+工龄补贴+绩效工资+考勤奖惩+个人所得税+五险一金+实发工资+发放状态 | 存储在职员工每月工资状态 |
| 退休员工工资表 | 员工编号+工资月份+在职月均工资+基础养老金+个人账户养老金+实发工资+发放状态 | 存储退休员工工资状态 |
第二节 概念结构设计
2.1 分E-R图
由需求分析可得共有六个实体，分别是员工、部门、月考勤、在职工资、退休工资和个人专项附加扣除项目。一个员工在一个部门，一个部门可以有多个员工，部门与员工的关系是一对多的关系，每个员工在自己部门担任不同职位。一个员工只有一份月考勤，每份月考勤与每个员工一一对应。职工分为在职职工和退休职工，在职职工与在职工资是一对一关系，退休职工与退休工资也是一对一关系，员工与个人专项扣除项目是一对一关系。
由以上信息得到下面的分E-R图：
图 5 员工-部门分E-R图
图 6 员工-月考勤分E-R图
图 7 员工-工资分E-R图
2.2 基本E-R图
图 8 基本E-R图
第三节 逻辑结构设计
3.1关系模式设计（下划线“ ”表示主码 ）
员工（
员工编号 ，姓名，性别，出生日期，联系方式，入职时间，退休时间，任职状态） 无外码
个人专项附加扣除项目（
员工编号 ，子女教育，继续教育，贷款利息，租房，赡养老人，大病医疗） 外码：员工编号
部门（
部门编号 ，部门名，部门地址） 无外码
任职（
员工编号 ，部门编号，职称，任职时间）外码：员工编号，部门编号
考勤（
考勤月份，员工编号 ，加班时间，请假天数，迟到次数，早退次数，缺勤次数） 外码：员工编号
在职员工工资（
员工编号，工资月份 ，基本工资，工龄补贴，绩效工资，考勤奖惩，个人所得税，五险一金，实发工资，发放状态）外码：员工编号
退休员工工资（
员工编号，工资月份 ，在职月均工资，基础养老金，个人账户养老金，实发工资，发放状态） 外码：员工编号
3.2存储过程和触发器的设计
在录入员工工资时，应由系统根据考勤表中的考勤情况和考勤奖惩规则自动计算出员工在考勤奖罚方面的工资，从而创建奖惩存储函数。根据入职年份和补贴规则来计算员工的工龄补贴，从而创建工龄补贴的存储函数。根据个人专项附加扣除项目和员工的税前工资来计算员工的个人所得税，从而创建税收存储函数。
在在职员工工资表插入或修改数据前，应根据管理员所输入的员工编号，工资月份，基础工资和绩效工资，分别调用奖惩存储函数来计算员工的考勤奖惩，调用员工工龄的存储函数来计算员工工龄补贴，调用税收存储函数来计算员工的个人所得税，根据公式来计算员工的五险一金，并综合计算出应发工资，从而创建触发器。在退休员工工资表插入或修改数据前，应根据管理员所输入的员工编号，工资月份,在职月均工资来计算出基础养老金、个人账户养老金和实发工资并填入表中，从而创建触发器。
第四节 物理结构设计
员工信息表（employee）如下表所示：
表 11 员工信息表
| 字段名 | 数据类型 | 含义 | 是否允许为null | 说明 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| ename | varchar(11) | 姓名 | 否 | |
| sex | varchar(11) | 性别 | 否 | 取值：男或女 |
| birthday | Date | 出生日期 | 否 | （1945-01-01， 2003-01-01） |
| phone | varchar(11) | 联系方式 | 否 | 规定标准11位且唯一 |
| intime | Date | 入职时间 | 否 | |
| outtime | Date | 退休时间 | 是 | 默认为空 |
| state | varchar(11) | 任职状态 | 否 | 取值：在职或退休 |
个人专项附加扣除项目表(
items
)如下表所示：
表 12 个人专项附加扣除项目表
| 字段名 | 数据类型 | 含义 | 是否允许为null | 说明 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| pro1 | double | 子女教育 | 否 | 默认为零 |
| pro2 | double | 继续教育 | 否 | 默认为零 |
| pro3 | double | 贷款利息 | 否 | 默认为零 |
| pro4 | double | 租房 | 否 | 默认为零 |
| pro5 | double | 赡养老人 | 否 | 默认为零 |
| pro6 | double | 大病医疗 | 否 | 默认为零 |
部门表（department）如下表所示：
表 13 部门表
| 字段名 | 数据类型 | 含义 | 是否允许为null | 说明 |
| did | varchar(11) | 部门编号 | 否 | 主码 |
| dname | varchar(11) | 部门名 | 否 | |
| address | varchar(11) | 部门地址 | 否 |
任职表(
job )如下表所示：
表 14 任职表
| 字段名 | 数据类型 | 含义 | 是否允许为null | 说明 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| did | varchar(11) | 部门编号 | 否 | |
| zhicheng | varchar | 职称 | 否 | 默认：普通员工 |
| rtime | Date | 任职时间 | 是 |
月考勤表（attendance）如下表所示：
表 15 月考勤表
| 字段名 | 数据类型 | 含义 | 是否允许为null | 说明 |
| k_month | varchar(11) | 考勤月份 | 否 | 主码 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| over_hour | int | 加班时间 | 否 | 默认为零 |
| leave_day | int | 请假天数 | 否 | 默认为零 |
| late_time | int | 迟到次数 | 否 | 默认为零 |
| early_time | int | 早退次数 | 否 | 默认为零 |
| absent_time | int | 缺勤次数 | 否 | 默认为零 |
在职员工工资表(
salary
1 )如下表所示：
表 16 在职员工工资表
| 字段名 | 数据类型 | 含义 | 是否为空 | 说明 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| gmonth | varchar(11) | 工资月份 | 否 | 主码 |
| basic_pay | double | 基本工资 | 否 | 默认为零 |
| seniority_pay | double | 工龄补贴 | 否 | 默认为零 |
| jixiao_pay | double | 绩效工资 | 否 | 默认为零 |
| kaoqin_pay | double | 考勤奖惩 | 否 | 默认为零 |
| tax | double | 个人所得税 | 否 | 默认为零 |
| wuxian | double | 五险一金 | 否 | 默认为零 |
| shifa_pay | double | 实发工资 | 否 | 默认为零 |
| gstate | varchar(11) | 发放状态 | 否 | 取值：已发或未发 |
退休员工工资表(
salary
2 )如下表所示：
表 17 退休员工工资表
| 字段名 | 数据类型 | 含义 | 是否为空 | 说明 |
| eid | varchar(11) | 员工编号 | 否 | 主码 |
| tmonth | varchar(11) | 工资月份 | 否 | 主码 |
| av_pay | double | 在职月均工资 | 否 | 默认为零 |
| basic_yl | double | 基础养老金 | 否 | 默认为零 |
| person_yl | double | 个人账户养老金 | 否 | 默认为零 |
| shifa_yl | double | 实发工资 | 否 | 默认为零 |
| tstate | varchar(11) | 发放状态 | 否 | 取值：已发或未发 |
第五节 数据库实施
5.1 创建基本表
创建工资管理系统数据库（gzgl）：
图 9 创建数据库图
创建员工表（employee）：
图 10 创建员工表图
创建个人专项附加扣除项目表（items）：
图 11 创建个人专项附加扣除项目表图
创建部门表（department）：
图 12创建部门表图
创建任职表（job）：
图 13 创建任职表图
创建月考勤表（attendance）：
图 14 创建任职表图
创建在职员工工资表（salary1）：
图 15 创建在职员工工资表图
创建退休员工工资表（salary2）
图 16 创建退休员工工资表图
5.2 插入测试数据
插入数据：
图 17 员工数据图
图 18部门数据图
图 19员工任职数据图
图 20 月考勤数据图
图 21 items表数据图
图 22 salary1表数据图
图 23 salary2表数据图
5.3 创建业务处理的存储过程并测试
创建奖惩存储函数jiangcheng（），根据月考勤表考勤情况和考勤奖惩规则来计算员工在考勤奖罚方面的工资。
图 24 奖惩存储函数创建图
测试：运用奖惩存储函数来计算员工编号为2000020001日期为2023-11的考勤奖罚方面的工资。
图 25 奖惩存储函数测试图
创建员工工龄的存储函数gongling(),来根据入职年份计算工龄，运用工龄补贴规则来计算员工的工龄方面的工资。
图 26 员工工龄的存储函数创建图
测试：运用员工工龄的存储函数，来计算员工编号为2000020001今年的工龄补贴方面的工资 。
图 27 员工工龄的存储函数测试图
创建税收存储函数tax（），来计算员工的个人所得税。调用此存储函数需有基本工资等输入，故与下面的触发器tr_s一同测试。
图 28 税收存储函数创建图
5. 4 创建业务处理的视图并测试
创建视图salary1_view，用来直观显示出在职员工工资表的信息。
图 29 salary1_view视图创建图
测试：用视图salary1_view查询员工编号为2000020001的工资情况
图 30 视图salary1_view测试图
创建视图salary2_view，用来直观显示出退休员工工资表的信息。
图 31 视图salary2_view创建图
测试：用视图salary2_view查询员工编号为1980010001的工资情况。
图 32 视图salary2_view测试图
5. 5 创建业务处理的触发器并测试
创建触发器tr_s,在表salary1插入数据前，根据管理员所输入的员工编号，工资月份，基础工资和绩效工资，分别调用奖惩存储函数jiangcheng（）来计算员工的考勤奖惩，调用员工工龄的存储函数gongling()来计算员工工龄补贴，调用税收存储函数tax（）来计算员工的个人所得税，根据公式来计算员工的五险一金，并综合计算出应发工资。
图 33 触发器tr_s创建图
测试：插入员工工资基本数据：
图 34 触发器tr_s数据插入测试图
查看员工工资 ：
图 35 触发器tr_s测试图
创建触发器tr_s1,在表salary2插入数据前，根据管理员所输入的员工编号，工资月份,在职月均工资来计算出基础养老金、个人账户养老金和实发工资并填入表中 。
图 36 触发器tr_s1创建图
图 37 触发器tr_s1数据插入图
图 38 触发器tr_s1测试图
68747470733a2f2f62:6c6f672e6373646e2e6e65742f6d305f36363434313131352f:61727469636c652f64657461696c732f313337313935313435