急！求助高手帮帮忙，偶是SQL新手遇到问题

gzhxiaoshi 2009-06-05 02:25:45

题在下面附录了已经，在其中有一个存储的过程 2-5-3

即第三个要求的存储过程我不知道应该怎么写，谢谢了，可以QQ指导下M QQ：709152

本人是C#的新手，现在学习SQL，各位前辈高人请不吝赐教

谢谢了谢谢了

人事管理系统案例
1 项目背景
1.1 公司现状
蓝山科技股份有限公司的主营业务是软件开发，公司目前规模为70人，其组织结构如图1所示：
图1
公司的人员类型有以下几种：普通员工、部门经理、人事部成员和总经理。其中人事部有1个人事经理、3个人事助理。目前，公司尚无人事管理系统。薪资、考勤、业绩评定等业务流程都是由人事经理和3位人事助理手工完成。
最近公司运作、发展良好，业务规模、人员规模呈迅速扩张趋势。随着公司的发展，人力资源部门的压力变得越来越大，工作负荷日趋繁重。人事管理系统缺乏所带来的问题日益显现出来，例如：
• 每到月底，人事部门总是忙得不可开交。他们要对本月的请假、出勤情况进行统计、记录，并依次计算每位员工的薪资。有时，甚至会由于请假条、出勤记录丢失、有误，导致薪资核算有误，并因此造成人事纠纷
• 随着人员的增多，员工之间的相互了解、部门间的合作也变得较为困难。新来的员工通常要花费很长一段时间，才能熟悉公司的组织结构，了解其他部门的业务情况、人员情况
所有这一切都与公司的迅速发展不相协调，业务流程的手工操作也与公司的软件开发背景格格不入。
1.2 解决方案
为迅速改变现状，规范公司的管理，降低人事部的工作负荷，提高工作效率，公司管理层经过仔细研究、分析，决定开发一套完善的人事管理系统。
该管理系统的主要功能是管理员薪资料、管理员工考勤、计算员工薪资等。大部分涉及对敏感数据修改的工作都仅由人事部完成，如计算薪资、修改考勤记录；并且有些只有人事经理才有权处理，如定制部门、制定员工的基本薪资等。普通员工可以通过 Web 浏览自己的基本资料、考勤信息、薪资信息和请假记录等。员工也可以通过 Web 提出请假和加班申请，如果所属部门的经理审批通过，人事部就可以登记在案。人事经理默认拥有人事助理的所有权限，部门经理默认拥有普通员工的所有权限，总经理默认拥有部门经理的所有权限。
1.2.1 项目范围
由于人事管理系统功能全面、丰富，流程相对复杂、工作量大，因此，为便于系统开发管理，降低风险，公司将人事管理系统拆分为三个子系统：
• Web 系统，主要实现信息查询功能。其中所涉及的功能主要覆盖人事部之外的工作内容，如员工查询、经理审批等
• Windows 系统，主要实现信息管理功能。其中所涉及的功能主要覆盖人事部的工作内容，如员薪资料管理、薪资管理等
• SQL 系统，主要实现数据管理功能。其中所涉及的功能主要是设计数据库中的对象，如表、视图、存储过程等。涉及界面操作的功能由 Web 子系统和 Windows 子系统实现
本次课程设计仅实现SQL系统，有关SQL系统实现的具体功能，请参见课程设计部分
2 课程设计
2.1 概念模型
系统分析人员、应用程序数据人员、维护人员和用户根据背景分析和目标需求制定概念模型（ER图）如图1

图1
2.2 数据库
 数据库名称设为 BlueHill。
 创建两个文件组，主文件组存储系统信息，用户定义文件组存储用户数据。
2.3 表
 根据ER图设计表如下：
 请分析以下各表是否是3NF
 请分析各表主键、外键
 请为各表属性性选择合适的数据类型、是否允许空值等
 请根据业务规则创建各表
2.3.1 表tblEmployee
 概述
表 tblEmployee 用于记录员工基本信息，每个员工在表中对应一条记录。
 表属性
表 tblEmployee 包括如下属性：
名称说明
EmployeeID 员工编号
Name 员工姓名
LoginName 员工登录名
Password 员工登录密码
Email 员工电子邮件
DeptID 员工所属部门编号
BasicSalary 员工基本工资
TitleID 员工职位编号
Telephone 员工电话
OnboardDate 员工报到日期
SelfIntro 员工自我介绍
VacationRemain 员工剩余假期
PhotoImage 员工照片
 约束
1. 新添加一个员工时，员工剩余假期（VacationRemain）为0
2.3.2 表tblTitle
 概述
表tblTitle用于记录公司员工各种职位，每一种职位在表中对应于一条记录。本公司职位有以下几种：
（1）普通员工
（2）部门经理
（3）人事助理
（4）人事经理
（5）总经理
 表属性
表 tblTitle 包括如下属性：
名称说明
TitleID 职位编号
Title 职位

2.3.3 表tblDepartment
 概述
表tblDepartment用于记录企业内部的部门信息。每个独立的部门在该表中都对应一条记录。该表中还记录了部门经理的员工编号，可以确定每个部门的部门经理。
 表属性
表tblDepartment包括如下属性：
名称说明
DeptID 部门编号
DeptName 部门名称
Desciption 部门描述
ManagerID 部门经理编号
 约束
（1）当一个部门存在员工时不能删除该部门，但如果修改了一个部门，该部门所属员工的部门编号也应该自动更改为新的部门编号
2.3.4 表tblSalary
 概述
表tblSalary用于记录员工每月的工资信息，每个员工每次发工资对应表中一条记录。
 表定义
表tblSalary包括如下属性：
名称说明
EmployeeID 员工编号
SalaryTime 工资发放时间
BasicSalary 员工基本工资
OvertimeSalary 加班工资
AbsenseSalary 缺勤扣除
OtherSalary 其他工资
2.3.5 表tblAttendance
 概述
表tblAttendance用于记录员工的缺勤信息，员工每天每一种缺勤情况对应表中一条记录
 表属性
表tblAttendance包括如下属性：
名称说明
EmployeeID 员工编号
Date 考勤日期
RecorderID 记录者编号
Type 缺勤类型
Hour 缺勤小时数
AskReview 请求重新审核
AskReviewReason 请求重新审核原因
 约束
（1）考勤记录者编号和员工编号不能相同。
2.3.6 表tblLeave
 概述
表 tblLeave 用于登记员工的请假记录。员工每一次请假对应表中一条记录。员工请假以小时为单位，由开始时间与结束时间计算得到。请假类型（Type）为“折算成年假”或“折算成津贴”。申请状态（Status）为“已提交”、“已批准”、“已拒绝”和“已取消”之一。
 表属性
表tblLeave包括如下属性：
名称说明
EmployeeID 员工编号
SubmitTime 提交时间
StartTime 开始时间
EndTime 结束时间
Reason 请假理由
Type 请假类型
Hours 请假小时数
Status 申请状态
ApproverID 审核者编号
DenyReason 拒绝申请的理由
 约束
（1）审核者编号（ApproverID）不能与员工编号（EmployeeID）相同。
（2）请假结束时间（EndTime）必须大于请假开始时间（StartTime）
2.3.7 表tblOvertime
 概述
表tblOvertime用于记录员工的加班记录。员工每次申请加班对应表中一条记录。员工加班以小时为单位，由开始时间与结束时间计算得到。加班类型（Type）为“折算成年假”或“折算成津贴”。申请状态（Status）为“已提交”、“已批准”、“已拒绝”和“已取消”之一。
 表属性
表tblOvertime包括如下属性：
名称说明
EmployeeID 员工编号
ApproverID 批准者编号
SubmitTime 提交时间
StartTime 开始时间
EndTime 结束时间
Reason 加班理由
Status 申请状态
Type 加班类型
Denyreason 拒绝申请的理由
Hours 加班小时数
 约束
（1）审核者编号（ApproverID）不能与员工编号（EmployeeID）相同。
（2）加班结束时间（EndTime）必须大于加班开始时间（StartTime）
2.4 视图
请创建以下视图
2.4.1 视图viwEmpCommonInfo
 概述
此视图通过左外联接表 tblEmployee 和 tblDepartment 得到了员工的详细信息，其中包括员工的基本信息、员工的部门信息和员工经理信息。
 基表
 tblEmployee
 tblDepartment
 视图包含字段
 EmployeeID（员工编号）
 Name（姓名）
 Email（电子邮件）
 Telephone（电话）
 LoginName（登录名）
 Password（密码）
 BasicSalary（基本工资）
 OnboardDate（报道日期）
 ManagerName（部门经理姓名）
 ManagerEmail（部门经理电子邮件）
 DeptName（部门名称）
 SelfIntro（自述）
 VacationRemain（剩余假期小时数）
2.4.2 视图viwAllAttend
 概述
此视图通过 EmployeeID 内联接表 tblAttendance 和 tblEmployee，得到员工的姓名、员工所属部门 ID和考勤情况。
 基表
 tblAttendance
 tblEmployee
 视图包含字段
 EmployeeID（员工编号）
 DeptID（部门编号）
 BreacherName（缺勤者姓名）
 Type（缺勤类型）
 Breachtime（缺勤小时数）
 Date（缺勤日期）
2.4.3 视图viwManagerInfo
 概述
此视图通过内联接表 tblDepartment 和 tblEmployee，得到经理的所有基本信息。
 基表
 tblDepartment
 tblEmployee
 视图包含的字段
 ManagerName（经理姓名）
 ManagerEmail（经理电子邮件）
 DeptName（部门名称）
 ManagerID（理解雇员编号）
 DeptID（部门编号）
2.5 存储过程
2.5.1 存储过程spAddLeaveReq
 概述
此存储过程向表 tblLeave 插入一条已提交的请假申请。
2.5.2 存储过程spAskReview
 概述
此存储过程通过更新表 tblAttendance 的 AskReview（请求重新审核）字段值为1，并更新tblAttendance 的AskReviewReason（请求重新审核原因）来提交要求复查一条考勤记录的信息。
2.5.3 存储过程 spAccountAbsenceSalary
此存储过程根据tblAttendance（考勤）表信息按月更新tblSalary（薪资）表的AbsenceSalary（缺勤工资）列。
请灵活应用系统函数、事务、游标、存储过程等知识点。
2.6 触发器
2.6.1 trUpdateOvertime
此触发器在部门经理接受员工加班申请时触发。当员工请求加班类型为“折算成津贴”，按员工加班小时数、每小时津贴计算员工加班工资更新tblSalary（工资）表的OvertimeSalary（加班工资）字段。当员工请求加班类型为“折算成年假”，计算员工加班小时数更新tblEmployee（员工信息）表的VacationRemain（剩余假期小时数）字段。
2.6.2 trUpdateLeave
此触发器在部门经理接受员工请假申请时触发。当员工请求请假类型为“折算成津贴”，按员工请假小时数、每小时扣款数计算员工请假扣款更新tblSalary（工资）表的AbsenceSalary（缺勤工资）字段。当员工请求请假类型为“折算成年假”，计算员工请假小时数更新tblEmployee（员工信息）表的VacationRemain（剩余假期小时数）字段。
2.6.3 tr DenyLeave
当员工请求请假类型为“折算成年假”时，检查tblEmployee（员工信息）表的VacationRemain（剩余假期小时数）字段，如果剩余假期小时数小于员工请假小时数，拒绝员工请假，并给出拒绝请假信息。
2.7 数据库的安全性
2.7.1 创建角色
分别为普通员工、人事助理和部门经理创建应用程序角色，并授予适当的权限。
2.7.2 创建用户
分别为普通员工、人事助理、部门经理和人事经理创建数据库用户，并授予适当的权限。
2.8 日常维护备份
请做一份合适的数据库备份计划。

...全文