人事管理系统项目详细设计说明书.docx

目录1.概述71.1.目的71.2.内容71.3.与其他项目的关联72.数据库72.1.数据库名称72.2.用户83.表83.1.表设计概述83.2.表tblEmployee93.3.表tblDepartment113.4.表tblSalary113.5.表tblAttendance123.6.表tblLeave143.7.表tblOvertime153.8.表tblPerformance163.9.表tblConfig173.10.表tblSystemEvent183.11.表tblPerformStatus193.12.表tblOvertimeType203.13.表tblHoliday213.14.表tblPerformItem224.视图244.1.视图viwwebEmpCommonInfo244.2.视图viwwebAllAttend254.3.视图viwwebAllLeaveReg254.4.视图viwwebAllOTReg264.5.视图viwwebCompanyPolicy274.6.视图viwwebDepartment284.7.视图viwwebHoliday284.8.视图viwwebManagerInfo294.9.视图viwwebSubmittedLvReg294.10.视图viwwebSubmittedOTReg304.11.视图viwwebHistorySalary314.12.视图viwwebPerformance324.13.视图viwwebPerformItem334.14.视图viwwinEmployeeList334.15.视图viwwinAttendance344.16.视图viwwinLeaveList354.17.视图viwwinOTList354.18.视图viwwinEmpBasicSalary365.存储过程375.1.存储过程spwebAddLeaveReq375.2.存储过程spwebAddOTReq375.3.存储过程spwebAskReview385.4.存储过程spwebCancelLeaveReq395.5.存储过程spwebCancelOTReq395.6.存储过程spwebChangeLeaveStatus405.7.存储过程spwebChangeOTStatus415.8.存储过程spwebGetCEOInfo415.9.存储过程spwebGetDeptAttendSummary425.10.存储过程spwebGetDeptLvSummary425.11.存储过程spwebGetDeptOTSummary435.12.存储过程spwebGetEmployeeID445.13.存储过程spwebGetEmpPassword445.14.存储过程spwebGetEmpPasswordByID455.15.存储过程spwebUpdatePassword465.16.存储过程spwebUpdateSelfIntro465.17.存储过程spwebAddPerformObject475.18.存储过程spwebDeletePerformItem485.19.存储过程spwebGetDeptSalarySummary485.20.存储过程spwebUpdatePerform495.21.存储过程spwebUpdatePerformItem495.22.存储过程spwinAttendanceQuery505.23.存储过程spwinChangeEmployeeDept515.24.存储过程spwinCreateDepartment515.25.存储过程spwinDeleteDepartment525.26.存储过程spwinDeleteEmployee525.27.存储过程spwinDelLeaveRequest535.28.存储过程spwinDeptAllEmployee545.29.存储过程spwinDeptLeave545.30.存储过程spwinEmpLeave555.31.存储过程spwinGetAllDepartment555.32.存储过程spwinGetEmpbyDeptName565.33.存储过程spwinMoveEmpBetweenDept565.34.存储过程spwinRejectLeaveRequest575.35.存储过程spwebUpdatePerformItemSelf585.36.存储过程spwebUpdatePerformItemReview585.37.存储过程spwebGetSubmittedPerform595.38.存储过程spwebGetDeptPerformSummary595.39.存储过程spwinOTbyGroup605.40.存储过程spwebPerformReviewed615.41.存储过程spwinBasicSalaryByEmpID615.42.存储过程spwinSalaryHistoryByEmpID625.43.存储过程spwinSetBasicSalary625.44.存储过程spwinQueryEventbyTime635.45.存储过程spwinChangePassword645.46.存储过程spwinAddEvent645.47.存储过程spwebUpdatePerformItemObj656.用户自定义函数656.1.用户自定义函数udfwinDeptAllEmployee656.2.用户自定义函数udfwinGetDeptIDbyDeptName666.3.用户自定义函数udfwinGetDeptNamebyDeptID667.触发器677.1.触发器tRejectRequest678.数据库安全性678.1.概述678.2.数据库验证方式671. 概述1.1. 目的本文为教学案例项目SQL Server功能规范说明书。本说明书将：l 描述数据库设计的目的l 说明数据库设计中的主要组成部分l 说明数据库设计中涵盖的教学知识要点1.2. 内容本文档主要内容包括对数据库设计结构的总体描述，对数据库中各种对象的描述（包括对象的名称、对象的属性、对象和其他对象的直接关系）。本文档中包含对以下数据库内容的描述：l 数据表l 视图l 存储过程l 用户自定义函数l 触发器l 约束在数据库主要对象之外，本文还将描述数据库安全性设置、数据库属性设置和数据库备份策略，为数据库管理员维护数据库安全稳定地运行提供参考。1.3. 与其他项目的关联教学案例项目的数据库设计与教学项目（Web部分和Windows部分）功能密切相关。教学案例项目的数据库将按照教学项目程序部分的功能需求而设计，数据库设计将配合教学案例的程序部分，以实现一个功能完备的企业环境内的应用。提示可通过使用 Web 应用程序或 Windows 应用程序来测试数据库。2. 数据库2.1. 数据库名称数据库的名称一定要设为 RGB，否则本案例设计的 Web 部分的应用程序和 Windows 部分的应用程序将无法使用该数据库。答案：USE masterGO/* Object: Database RGB Script Date: 06/18/2011 08:55:58 */CREATE DATABASE RGB ON PRIMARY ( NAME = N'RGB', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATARGB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'RGB_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATARGB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOALTER DATABASE RGB SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')beginEXEC RGB.dbo.sp_fulltext_database action = 'enable'endGOALTER DATABASE RGB SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE RGB SET ANSI_NULLS OFF GOALTER DATABASE RGB SET ANSI_PADDING OFF GOALTER DATABASE RGB SET ANSI_WARNINGS OFF GOALTER DATABASE RGB SET ARITHABORT OFF GOALTER DATABASE RGB SET AUTO_CLOSE OFF GOALTER DATABASE RGB SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE RGB SET AUTO_SHRINK OFF GOALTER DATABASE RGB SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE RGB SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE RGB SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE RGB SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE RGB SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE RGB SET QUOTED_IDENTIFIER OFF GOALTER DATABASE RGB SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE RGB SET DISABLE_BROKER GOALTER DATABASE RGB SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE RGB SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE RGB SET TRUSTWORTHY OFF GOALTER DATABASE RGB SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE RGB SET PARAMETERIZATION SIMPLE GOALTER DATABASE RGB SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE RGB SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE RGB SET READ_WRITE GOALTER DATABASE RGB SET RECOVERY FULL GOALTER DATABASE RGB SET MULTI_USER GOALTER DATABASE RGB SET PAGE_VERIFY CHECKSUM GOALTER DATABASE RGB SET DB_CHAINING OFF GO2.2. 用户除了数据库中自动创建的 dbo 用户之外，还要创建如下两个用户： RGBWinUser：该用户是 Windows 应用程序访问数据库所使用的账号，它的权限一般仅限于查看视图和执行存储过程。 RGBASPUser：该用户是 Web 应用程序访问数据库所使用的账号，它的权限和 RGBWinUser 用户类似，即仅限于查看视图和执行存储过程。答案：1.USE RGBGO/* Object: User RGBWinUser Script Date: 06/18/2011 08:29:15 */GOCREATE USER RGBWinUser FOR LOGIN WO WITH DEFAULT_SCHEMA=dboGO2. USE RGBGO/* Object: User RGBASPUser Script Date: 06/18/2011 08:30:46 */GOCREATE USER RGBASPUser FOR LOGIN ni WITH DEFAULT_SCHEMA=dbo GO3. 表3.1. 表设计概述根据教学案例功能，数据库将以员工信息为中心存储相关数据，配合 SQL Server 数据库系统中提供的数据管理，实现员工考勤、请假、加班管理及系统设置等业务功能。数据库设计将以存储员工信息的员工表为基础，连接多张相关表实现对以下关系的支持：l 员工与请假申请l 员工与加班申请l 员工与考勤记录l 员工与部门l 员工与部门经理l 员工与绩效考核记录l 员工与工资此外数据库中还将记录教学案例应用中需要的全局配置信息和事件日志记录。数据库系统主要的实体关系如下图：（部门表中去掉部门经理编号，在数据删除时可能会出现死锁）3.2. 表tblEmployee概述表 tblEmployee 用于记录员工基本信息，并作为基础表与其他表联接。该表通过DeptID和Title可以确定员工部门和职位信息。当Title的值为“经理”时可以确定此员工为该部门的部门经理。答案：USE RGBGO/* Object: Table dbo.tblEmployee Script Date: 06/20/2011 17:13:37 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.tblEmployee(EmployeeID int NOT NULL,Name nvarchar(50) NOT NULL,LoginName nvarchar(20) NOT NULL,Password binary(20) NULL,Email nvarchar(50) NOT NULL,DeptID int NULL,BasicSalary int NULL,Title nvarchar(50) NULL,Telephone nvarchar(50) NULL,OnhoardDate datetime NOT NULL,Selfintro nvarchar(200) NULL,VacationRemain int NULL,EmployeeLevel int NULL,PhotoImage image NULL, CONSTRAINT PK_tblEmployee PRIMARY KEY CLUSTERED (EmployeeID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARY TEXTIMAGE_ON PRIMARYGOSET ANSI_PADDING OFFGOALTER TABLE dbo.tblEmployee WITH CHECK ADD CONSTRAINT FK_tblEmployee_tblDepartment FOREIGN KEY(DeptID)REFERENCES dbo.tblDepartment (DeptID)GOALTER TABLE dbo.tblEmployee CHECK CONSTRAINT FK_tblEmployee_tblDepartmentGOALTER TABLE dbo.tblEmployee WITH CHECK ADD CONSTRAINT FK_tblEmployee_tblEmployeeLevel FOREIGN KEY(EmployeeLevel)REFERENCES dbo.tblEmployeeLevel (EmployeeLevel)GOALTER TABLE dbo.tblEmployee CHECK CONSTRAINT FK_tblEmployee_tblEmployeeLevelGO表定义表 tblEmployee 定义如下：名称类型可否为空说明备注EmployeeIDint 4否员工编号自动生成Namenvarchar 50否员工姓名LoginNamenvarchar 20否员工登录名建议为英文字符，且与姓名不同Passwordbinary 20可员工登录密码Emailnvarchar 50否员工电子邮件DeptIDint 4可员工所属部门编号BasicSalaryint 4可员工基本工资Titlenvarchar 50可员工职位名称可取值为“普通员工”、“部门经理”、“总裁”Telephonenvarchar 50可员工电话OnboardDatedatetime 8否员工报到日期SelfIntronvarchar 200可员工自我介绍初始为空，由员工自行输入VacationRemainint 4可员工剩余假期小时数EmployeeLevelint 4可员工的级别PhotoImageimage 16可员工照片主键表 tblEmployee 的主键是EmployeeID 字段，类型为 int，设置自动增量。答案：USE RGBGO/* Object: Index PK_tblEmployee Script Date: 06/18/2011 09:00:53 */ALTER TABLE dbo.tblEmployee ADD CONSTRAINT PK_tblEmployee PRIMARY KEY CLUSTERED (EmployeeID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYGO外键表 tblEmployee 的外键有DeptID，类型为int，用于与表 tblDepartment 中的 DeptID 字段关联。DeptID 字段可以为空，在此情况下表示员工不在任何部门中。表 tblEmployee 的外键有EmployeeLevel，类型为int，用于与表tblEmployeeLevel中的EmployeeLevel字段关联。1. USE RGBGOALTER TABLE dbo.tblEmployee WITH CHECK ADD CONSTRAINT FK_tblEmployee_tblDepartment FOREIGN KEY(DeptID)REFERENCES dbo.tblDepartment (DeptID)GOALTER TABLE dbo.tblEmployee CHECK CONSTRAINT FK_tblEmployee_tblDepartmentGO2. USE RGBGOALTER TABLE dbo.tblEmployee WITH CHECK ADD CONSTRAINT FK_tblEmployee_tblEmployeeLevel FOREIGN KEY(EmployeeLevel)REFERENCES dbo.tblEmployeeLevel (EmployeeLevel)GOALTER TABLE dbo.tblEmployee CHECK CONSTRAINT FK_tblEmployee_tblEmployeeLevelGO约束表 tblEmployee 中的 LoginName 字段建议为4-8位小写英文字符，且不能与员工姓名相同也不可以为空字符串。索引主键字段EmployeeID 具有自动创建的聚集索引。3.3. 表tblDepartment概述表tblDepartment用于记录企业内部的部门信息。每个独立的部门在该表中都对应一条记录。该表通过与 tblEmployee 表关联可以确定员工所属的部门。答案：USE RGBGO/* Object: Table dbo.tblDepartment Script Date: 06/20/2011 17:13:12 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.tblDepartment(DeptID int NOT NULL,DeptName char(10) NULL,Description char(50) NULL, CONSTRAINT PK_tblDepartment PRIMARY KEY CLUSTERED (DeptID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGO表定义表tblDepartment定义如下：名称类型可否为空说明备注DeptIDint 4否部门编号自动生成DeptNamechar 10可部门名称Desciptionchar 50可部门描述主键表tblDepartment的主键是DeptID 字段，类型为 int，设置自动增量。答案：USE RGBGO/* Object: Index PK_tblDepartment Script Date: 06/18/2011 09:03:12 */ALTER TABLE dbo.tblDepartment ADD CONSTRAINT PK_tblDepartment PRIMARY KEY CLUSTERED (DeptID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYGO外键无约束无其他约束索引主键字段DeptID具有自动创建的聚集索引。3.4. 表tblSalary概述表tblSalary用于记录员工每月的工资信息，包括工资发放日期、工资组成等。表 tblSalary 通过字段 EmployeeID 与表tblEmployee 关联。答案：USE RGBGO/* Object: Table dbo.tblSalary Script Date: 06/20/2011 17:12:47 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.tblSalary(SalaryID int NOT NULL,EmployeeID int NOT NULL,SalaryTime datetime NOT NULL,BasicLSalary int NULL,OvertimeSalary int NULL,AbsenseSalary int NULL,OtherSalary int NULL, CONSTRAINT PK_tblSalary PRIMARY KEY CLUSTERED (SalaryID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.tblSalary WITH CHECK ADD CONSTRAINT FK_tblSalary_tblEmployee FOREIGN KEY(EmployeeID)REFERENCES dbo.tblEmployee (EmployeeID)GOALTER TABLE dbo.tblSalary CHECK CONSTRAINT FK_tblSalary_tblEmployeeGO表定义表tblSalary定义如下：名称类型可否为空说明备注SalaryIDint 4否工资编号EmployeeIDint 4否员工编号SalaryTimedatetime 8否工资发放时间BasicSalaryint 4可员工基本工资OvertimeSalaryint 4可加班工资AbsenseSalaryint 4可缺勤扣除OtherSalaryint 4可其他工资主键表tblSalary的主键是SalaryID字段，类型为 int，设置自动增量。答案：USE RGBGO/* Object: Index PK_tblSalary Script Date: 06/18/2011 09:03:47 */ALTER TABLE dbo.tblSalary ADD CONSTRAINT PK_tblSalary PRIMARY KEY CLUSTERED (SalaryID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARYGO外键表tblSalary的外键是EmployeeID，类型为int，用于与表tblEmployee中的EmployeeID字段关联。答案：USE RGBGOALTER TABLE dbo.tblSalary WITH CHECK ADD CONSTRAINT FK_tblSalary_tblEmployee FOREIGN KEY(EmployeeID)REFERENCES dbo.tblEmployee (EmployeeID)GOALTER TABLE dbo.tblSalary CHECK CONSTRAINT FK_tblSalary_tblEmployeeGO约束无其他约束索引主键字段SalaryID具有自动创建的聚集索引。3.5. 表tblAttendance概述表tblAttendance用于记录员工的考勤信息（上下班时间、记录者信息等），通过字段 EmployeeID 与表tblEmployee 关联。答案：USE RGBGO/* Object: Table dbo.tblAttendance Script Date: 06/20/2011 17:11:44 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE dbo.tblAttendance(AttendID int NOT NULL,EmployeeID int NOT NULL,BreachTime datetime NULL,RecorderID int NULL,Type nchar(4) NOT NULL,AskReview tinyint NULL,Date datetime NOT NULL, CONSTRAINT PK_tblAttendance PRIMARY KEY CLUSTERED (AttendID ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOALTER TABLE dbo.tblAttendance WITH CHECK ADD CONSTRAINT FK_tblAttendance_tblEmployee FOREIGN KEY(EmployeeID)REFERENCES dbo.tblEmployee (EmployeeID)GOALTER TABLE dbo.tblAttendance CHECK CONSTRAINT FK_tblAttendance_tblEmployeeGOALTER TABLE dbo.tblAttendance WITH CHECK ADD CONSTRAINT FK_tblAttendance_tblEmployee2 FOREIGN KEY(RecorderID)REFERENCES dbo.tblEmployee (EmployeeID)GOALTER TABLE dbo.tblAttendance CHECK CONSTRAINT FK_tblAttendance_tblEmployee2GOALTER TABLE dbo.tblAttendance WITH CHECK ADD CONSTRAINT CK_tblAttendance CHECK (RecorderID<>EmployeeID AND (Type='缺勤' OR Type='迟到' OR Type='早退')GOALTER TABLE dbo.tblAttendance CHECK CONSTRAINT CK_tblAttendanceGO表定义表tblAttendance定义如下：名称类型可否为空说明备注AttendIDint 4否考勤编号EmployeeIDint 4否员工编号BreachTimedatet