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

概述 7 内容 7与其他项目的关联 72.数据库 72.1.数据库名称 72.2.用户 83.表 83.1.表设计概述 83.2.表tblEmployee 9.表tblDepartment 113.4.表tblSalary 113.5.表tblAttendance 123.6.表tblLeave 143.7.表tblOvertime 153.8.表tblPerformance 163.9.表tblConfig 173.10.表tblSystemEvent 181.表tblPerformStatus 193.12.表tblOvertimeType 203.表tblHoliday 213.14.表tblPerformItem 224.视图 244.1.视图viwwebEmpCommonInfo 244.2.视图viwwebAllAttend 254.3.视图viwwebAllLeaveReg 254.4.视图viwwebAllOTReg 264.5.视图viwwebCompanyPolicy 274.6.视图viwwebDepartment 284.7.视图viwwebHoliday 284.8.视图viwwebManagerInfo 294.9.视图viwwebSubmittedLvReg 294.10.视图viwwebSubmittedOTReg 304.11.视图viwwebHistorySalary 314.12.视图viwwebPerformance 324.13.视图viwwebPerformItem 334.14.视图viwwinEmployeeList 334.15.视图viwwinAttendance 344.16.视图viwwinLeaveList 354.17.视图viwwinOTList 354.18.视图viwwinEmpBasicSalary 365.存储过程 375.1.存储过程spwebAddLeaveReq 375.2.存储过程spwebAddOTReq 375.3.存储过程spwebAskReview 385.4.存储过程spwebCancelLeaveReq 39存储过程spwebCancelOTReq 395.6.存储过程spwebChangeLeaveStatus 405.7.存储过程spwebChangeOTStatus 415.8.存储过程spwebGetCEOInfo 415.9.存储过程spwebGetDeptAttendSummary 425.10.存储过程spwebGetDeptLvSummary 425.11.存储过程spwebGetDeptOTSummary 435.12.存储过程spwebGetEmployeeID 445.13.存储过程spwebGetEmpPassword 445.14.存储过程spwebGetEmpPasswordByID 45.存储过程spwebUpdatePassword 465.16.存储过程spwebUpdateSelfIntro 465.17.存储过程spwebAddPerformObject 47.存储过程spwebDeletePerformItem 485.19.存储过程spwebGetDeptSalarySummary 485.20.存储过程spwebUpdatePerform 495.21.存储过程spwebUpdatePerformItem 495.22.存储过程spwinAttendanceQuery 505.23.存储过程spwinChangeEmployeeDept 515.24.存储过程spwinCreateDepartment 515.25.存储过程spwinDeleteDepartment 525.26.存储过程spwinDeleteEmployee 525.27.存储过程spwinDelLeaveRequest 535.28.存储过程spwinDeptAllEmployee 54.存储过程spwinDeptLeave 545.30.存储过程spwinEmpLeave 555.31.存储过程spwinGetAllDepartment 555.32.存储过程spwinGetEmpbyDeptName 565.33.存储过程spwinMoveEmpBetweenDept 565.34.存储过程spwinRejectLeaveRequest 575.35.存储过程spwebUpdatePerformItemSelf 585.36.存储过程spwebUpdatePerformItemReview 585.37.存储过程spwebGetSubmittedPerform 595.38.存储过程spwebGetDeptPerformSummary 59.存储过程spwinOTbyGroup 605.40.存储过程spwebPerformReviewed 61.存储过程spwinBasicSalaryByEmpID 615.42.存储过程spwinSalaryHistoryByEmpID 625.43.存储过程spwinSetBasicSalary 625.44.存储过程spwinQueryEventbyTime 63.存储过程spwinChangePassword 645.46.存储过程spwinAddEvent 645.47.存储过程spwebUpdatePerformItemObj 656.用户自定义函数 656.1.用户自定义函数udfwinDeptAllEmployee 656.2.用户自定义函数udfwinGetDeptIDbyDeptName 666.3.用户自定义函数udfwinGetDeptNamebyDeptID 667.触发器 677.1.触发器tRejectRequest 678.数据库安全性 671.概述 672.数据库验证方式 671.概述目的本文为教学案例项目SQLServer功能规范说明书。本说明书将：描述数据库设计的目的说明数据库设计中的主要组成部分说明数据库设计中涵盖的教学知识要点本文档主要内容包括对数据库设计结构的总体描述，对数据库中各种对象的描述(包括对象的名称、对象的属性、对象和其他对象的直接关系)。本文档中包含对以下数据库内容的描述：数据表视图存储过程用户自定义函数触发器约束略，为数据库管理员维护数据库安全稳定地运行提供参考。教学案例项目的数据库设计与教学项目(Web部分和Windows部分)功能密切相关。教学的程序部分，以实现一个功能完备的企业环境内的应用。提提示可通过使用Web应用程序或Windows应用程序来测试数据库。2.数据库2.1.数据库名称数据库的名称一定要设为RGB，否则本案例设计的Web部分的应用程序和Windows部分的应用程序将无法使用该数据库。/******Object:Database[RGB]ScriptDate:06/18/201108:55:58******/CREATEDATABASERGBONPRIMARYNAMEN'RGB',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServerMSSQLMSSQLSERVERMSSQLDATARGBmdfSIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGONNAMENRGBlogFILENAME=N'C:\ProgramFiles\MicrosoftSQLServerMSSQLMSSQLSERVERMSSQLDATARGBlogldf,SIZE=1024KB,MAXSIZE=,10%)048GB,10%)ALTERDATABASE[RGB]SETCOMPATIBILITY_LEVEL=100beginEXECRGB[dbo].[sp_fulltext_database]@action='enable'ALTERDATABASE[RGB]SETANSI_NULL_DEFAULTOFFALTERDATABASE[RGB]SETANSI_NULLSOFFALTERDATABASE[RGB]SETANSI_PADDINGOFFALTERDATABASE[RGB]SETANSI_WARNINGSOFFALTERDATABASE[RGB]SETARITHABORTOFFALTERDATABASE[RGB]SETAUTO_CLOSEOFFALTERDATABASE[RGB]SETAUTO_CREATE_STATISTICSONALTERDATABASE[RGB]SETAUTO_SHRINKOFFALTERDATABASE[RGB]SETAUTO_UPDATE_STATISTICSONALTERDATABASE[RGB]SETCURSOR_CLOSE_ON_COMMITOFFALTERDATABASE[RGB]SETCURSOR_DEFAULTGLOBALALTERDATABASE[RGB]SETCONCAT_NULL_YIELDS_NULLOFFALTERDATABASE[RGB]SETNUMERIC_ROUNDABORTOFFALTERDATABASE[RGB]SETQUOTED_IDENTIFIEROFFALTERDATABASE[RGB]SETRECURSIVE_TRIGGERSOFFALTERDATABASE[RGB]SETDISABLE_BROKERALTERDATABASE[RGB]SETAUTO_UPDATE_STATISTICS_ASYNCOFFALTERDATABASE[RGB]SETDATE_CORRELATION_OPTIMIZATIONOFFALTERDATABASE[RGB]SETTRUSTWORTHYOFFALTERDATABASE[RGB]SETALLOW_SNAPSHOT_ISOLATIONOFFALTERDATABASE[RGB]SETPARAMETERIZATIONSIMPLEALTERDATABASE[RGB]SETREAD_COMMITTED_SNAPSHOTOFFALTERDATABASE[RGB]SETHONOR_BROKER_PRIORITYOFFALTERDATABASE[RGB]SETREAD_WRITEALTERDATABASE[RGB]SETRECOVERYFULLALTERDATABASE[RGB]SETMULTI_USERALTERDATABASE[RGB]SETPAGE_VERIFYCHECKSUMALTERDATABASE[RGB]SETDB_CHAININGOFF2.2.用户除了数据库中自动创建的dbo用户之外，还要创建如下两个用户：般仅限于查看视图和执行存储过程。.RGBASPUser：该用户是Web应用程序访问数据库所使用的账号，它的权限和RGBWinUser用户类似，即仅限于查看视图和执行存储过程。/******Object:User[RGBWinUser]ScriptDate:06/18/201108:29:15******/CREATEUSERRGBWinUserFORLOGINWO]WITHDEFAULT_SCHEMA=[dbo]USERGB]/******Object:User[RGBASPUser]ScriptDate:06/18/201108:30:46******/CREATEUSERRGBASPUserFORLOGINni]WITHDEFAULT_SCHEMA=[dbo]3.表3.1.表设计概述根据教学案例功能，数据库将以员工信息为中心存储相关数据，配合SQLServer数据库系统中提供的数据管理，实现员工考勤、请假、加班管理及系统设置等业务功能。数据库设计将以存储员工信息的员工表为基础，连接多张相关表实现对以下关系的支持：员工与请假申请员工与加班申请员工与考勤记录员工与部门员工与部门经理员工与绩效考核记录员工与工资此外数据库中还将记录教学案例应用中需要的全局配置信息和事件日志记录。数据库系统主要的实体关系如下图：(部门表中去掉部门经理编号，在数据删除时可能会出现死锁)EDIDENTIFIERON3.2.表tblEmployeetblEmployeeDeptID和Title可以确定员工部门和职位信息。当Title的值为“经理”时可以确定此员工为该部门的部门经理。/******Object:Table[dbo].[tblEmployee]ScriptDate:06/20/201117:13:37******/NULLSONPADDINGONCREATETABLEdbotblEmployeeDintNOTNULLvarcharNOTNULLNamenvarcharNOTNULLrdbinaryNULLnvarcharNOTNULLULLyintNULLvarcharNULLonenvarcharNULLtedatetimeNOTNULLntronvarcharNULLmainintNULLvelintNULLimageNULLONSTRAINTPKtblEmployeePRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYTEXTIMAGE_ON[PRIMARY]DINGOFFALTERTABLE[dbo].[tblEmployee]WITHCHECKADDCONSTRAINTployeetblDepartmentFOREIGNKEYDeptIDREFERENCESdbo[tblDepartment]([DeptID])ALTERTABLE[dbo].[tblEmployee]CHECKCONSTRAINT[FK_tblEmployee_tblDepartment]ALTERTABLE[dbo].[tblEmployee]WITHCHECKADDCONSTRAINTployeetblEmployeeLevelFOREIGNKEYEmployeeLevelREFERENCESdbo[tblEmployeeLevel]([EmployeeLevel])ALTERTABLE[dbo].[tblEmployee]CHECKCONSTRAINT[FK_tblEmployee_tblEmployeeLevel]表tblEmployee定义如下：yeeIDnt否Namenvarchar50否nNamenvarchar20否建议为英文字符，且与姓名不同swordbinary20可nvarchar50否tIDnt可员工所属部门编号cSalarynt可nvarchar50可“总裁”nenvarchar50可Datedatetime8否nvarchar200可初始为空，由员输入cationRemainnt可yeeLevelnt可oImageimage16可表tblEmployee的主键是EmployeeID字段，类型为int，设置自动增量。******Object:Index[PK_tblEmployee]ALTERTABLE[dbo].[tblEmployee]ADDED(ScriptDate1109:00:53******/ONSTRAINTPKtblEmployeePRIMARYKEYWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]表tblEmployee的外键有DeptID，类型为int，用于与表tblDepartment中的DeptID字段表tblEmployee的外键有EmployeeLevel，类型为int，用于与表tblEmployeeLevel中的EmployeeLevel字段关联。ALTERTABLE[dbo].[tblEmployee]WITHCHECKADDCONSTRAINTployeetblDepartmentFOREIGNKEYDeptIDREFERENCESdbo[tblDepartment]([DeptID])ALTERTABLE[dbo].[tblEmployee]CHECKCONSTRAINT[FK_tblEmployee_tblDepartment]USE[RGB]ALTERTABLE[dbo].[tblEmployee]WITHCHECKADDCONSTRAINTployeetblEmployeeLevelFOREIGNKEYEmployeeLevelREFERENCESdbo[tblEmployeeLevel]([EmployeeLevel])ALTERTABLE[dbo].[tblEmployee]CHECKCONSTRAINT[FK_tblEmployee_tblEmployeeLevel]表tblEmployee中的LoginName字段建议为4-8位小写英文字符，且不能与员工姓名相同也不可以为空字符串。主键字段EmployeeID具有自动创建的聚集索引。3.3.表tblDepartmentblDepartment该表通过与tblEmployee表关联可以确定员工所属的部门。/******Object:Table[dbo].[tblDepartment]ScriptDate:06/20/201117:13:12******/NULLSONEDIDENTIFIERONPADDINGONCREATETABLEdbotblDepartmentNOTNULLmecharNULLptioncharNULLONSTRAINTPKtblDepartmentPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYDINGOFF表tblDepartment定义如下：tIDnt否部门编号Namechar10可部门名称iptionchar可部门描述tblDepartment的主键是DeptID字段，类型为int，设置自动增量。/******Object:Index[PK_tblDepartment]ScriptDate:06/18/201109:03:12******/ALTERTABLE[dbo].[tblDepartment]ADDCONSTRAINT[PK_tblDepartment]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无主键字段DeptID具有自动创建的聚集索引。3.4.表tblSalary表tblSalary用于记录员工每月的工资信息，包括工资发放日期、工资组成等。表tblSalary通过字段EmployeeID与表tblEmployee关联。/******Object:Table[dbo].[tblSalary]ScriptDate:06/20/201117:12:47******/NULLSONEDIDENTIFIERONCREATETABLEdbotblSalaryntNOTNULLDintNOTNULLedatetimeNOTNULLryintNULLlaryintNULLaryintNULLyintNULLCONSTRAINTPKtblSalaryPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblSalary]WITHCHECKADDCONSTRAINTlarytblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblSalary]CHECKCONSTRAINT[FK_tblSalary_tblEmployee]表tblSalary定义如下：nt否号yeeIDnt否datetime8否放时间cSalarynt可Salarynt可资senseSalarynt可除larynt可tblSalary的主键是SalaryID字段，类型为int，设置自动增量。/******Object:Index[PK_tblSalary]ScriptDate:06/18/201109:03:47******/ALTERTABLE[dbo].[tblSalary]ADDCONSTRAINT[PK_tblSalary]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]关联。ALTERTABLE[dbo].[tblSalary]WITHCHECKADDCONSTRAINTlarytblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblSalary]CHECKCONSTRAINT[FK_tblSalary_tblEmployee]主键字段SalaryID具有自动创建的聚集索引。3.5.表tblAttendance表tblAttendance用于记录员工的考勤信息(上下班时间、记录者信息等)，通过字段EmployeeID与表tblEmployee关联。/******Object:Table[dbo].[tblAttendance]ScriptDate:06/20/201117:11:44******/NULLSONEDIDENTIFIERONCREATETABLEdbotblAttendancentNOTNULLDintNOTNULLdatetimeNULLintNULLharNOTNULLinyintNULLimeNOTNULLONSTRAINTPKtblAttendancePRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINTtendancetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[FK_tblAttendance_tblEmployee]ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINTttendancetblEmployeeFOREIGNKEYRecorderIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[FK_tblAttendance_tblEmployee2]ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINT[CK_tblAttendance]CHECKRecorderID<>[EmployeeID]AND([Type]='缺勤'OR[Type]='迟到’'OR[Type]='早退')))ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[CK_tblAttendance]表tblAttendance定义如下：tendIDnt否号yeeIDnt否chTimedatetime8可orderIDnt可不可与EmployeeID相同nchar4否型可取值“缺勤”，kReviewTinyint1可eDatetime8否表tblAttendance的主键是AttendID字段，类型为int，设置自动增量。/******Object:Index[PK_tblAttendance]ScriptDate:06/18/201109:04:51******/ALTERTABLE[dbo].[tblAttendance]ADDCONSTRAINT[PK_tblAttendance]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]表tblAttendance的外键是EmployeeID和RecorderID，其中EmployeeID类型为int，用tblEmployee中的EmployeeID字段关联。USE[RGB]ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINTtendancetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[FK_tblAttendance_tblEmployee]USE[RGB]ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINTttendancetblEmployeeFOREIGNKEYRecorderIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[FK_tblAttendance_tblEmployee2]表tblAttendance中的RecorderID不可与EmployeeID相同；表tblAttendance中的AskReview字段默认值为0，即未请求重新审核ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINT[CK_tblAttendance]CHECKRecorderID<>[EmployeeID]AND([Type]='缺勤'OR[Type]='迟到’'OR[Type]='早退')))ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[CK_tblAttendance]主键字段AttendID具有自动创建的聚集索引。3.6.表tblLeave表tblLeave用于记录员工的请假记录。表tblLeave中的基本信息包括：请假提交时间、请与表tblEmployee关联。/******Object:Table[dbo].[tblLeave]ScriptDate:06/20/201117:14:23******/NULLSONEDIDENTIFIERONCREATETABLEdbotblLeavetNOTNULLDintNOTNULLedatetimeNOTNULLdatetimeNOTNULLtetimeNOTNULLnnvarcharNULLULLtNOTNULLnvarcharNULLintNULLeasonncharNULLONSTRAINTPKtblLeavePRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblLeave]WITHCHECKADDCONSTRAINTavetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblLeave]CHECKCONSTRAINT[FK_tblLeave_tblEmployee]ALTERTABLE[dbo].[tblLeave]WITHCHECKADDCONSTRAINTeavetblEmployeeFOREIGNKEYApproverIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblLeave]CHECKCONSTRAINT[FK_tblLeave_tblEmployee1]表tblLeave定义如下：ID否yeeID否e否间e否mee否sonnvarchar100可明D可号s否nvarchar20可：消”、“已批准”、“已否决”proverIDnt可Reasonnchar100可请的理由tblLeave的主键是LeaveID字段，类型为int，设置自动增量。/******Object:Index[PK_tblLeave]ScriptDate:06/18/201109:06:35******/ALTERTABLE[dbo].[tblLeave]ADDCONSTRAINT[PK_tblLeave]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]表tblLeave的外键是EmployeeID和ApproverID，其中EmployeeID类型为int，用于与verIDinttblEmployee中的EmployeeID字段关联。ALTERTABLE[dbo].[tblLeave]WITHCHECKADDCONSTRAINTavetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblLeave]CHECKCONSTRAINT[FK_tblLeave_tblEmployee]USE[RGB]ALTERTABLE[dbo].[tblLeave]WITHCHECKADDCONSTRAINTeavetblEmployeeFOREIGNKEYApproverIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblLeave]CHECKCONSTRAINT[FK_tblLeave_tblEmployee1]请假开始时间应小于请假结束时间；主键字段LeaveID具有自动创建的聚集索引。3.7.表tblOvertimelOvertime班起止时间、加班原因和加班申请批准状态。通过字段EmployeeID与表tblEmployee关联。/******Object:Table[dbo].[tblOvertime]ScriptDate:06/20/201117:14:53******/NULLSONEDIDENTIFIERONCREATETABLEdbotblOvertimeDintNOTNULLDintNOTNULLtNULLedatetimeNOTNULLdatetimeNOTNULLtetimeNOTNULLonnvarcharNOTNULLsncharNOTNULLtNULLeasonsncharNULLLLONSTRAINTPKtblOvertimeIDPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblOvertime]WITHCHECKADDCONSTRAINTertimetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblOvertime]CHECKCONSTRAINT[FK_tblOvertime_tblEmployee]ALTERTABLE[dbo].[tblOvertime]WITHCHECKADDCONSTRAINTertimetblOvertimeTypeFOREIGNKEYTypeREFERENCESdbo[tblOvertimeType]([Type])ALTERTABLE[dbo].[tblOvertime]CHECKCONSTRAINT[FK_tblOvertime_tblOvertimeType]表tblOvertime定义如下：ID否请编号yeeID否proverID可编号e否间e否mee否sonnvarchar100否由nchar10否“已提交”tinyint1可型reasonnchar100可请的理由snt可tblOvertimeOvertimeID段，类型为int，不设置自动增量。/******Object:Index[PK_tblOvertimeID]ScriptDate:06/18/201109:08:04******/ALTERTABLE[dbo].[tblOvertime]ADDCONSTRAINT[PK_tblOvertimeID]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]字段关联。表tblOvertime的外键是Type，类型为tinyint，用来与表tblOverTimeType的Type相关联。ALTERTABLE[dbo].[tblOvertime]WITHCHECKADDCONSTRAINTertimetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblOvertime]CHECKCONSTRAINT[FK_tblOvertime_tblEmployee]USE[RGB]ALTERTABLE[dbo].[tblOvertime]WITHCHECKADDCONSTRAINTertimetblOvertimeTypeFOREIGNKEYTypeREFERENCESdbo[tblOvertimeType]([Type])ALTERTABLE[dbo].[tblOvertime]CHECKCONSTRAINT[FK_tblOvertime_tblOvertimeType]加班开始时间应小于加班结束时间。主键字段OvertimeID具有自动创建的聚集索引。3.8.表tblPerformance表tblPerformance用于保存员工的绩效信息。表tblPerformance中的基本信息包括：员工编号、考评者编号、提交时间、考评时间、评语和绩效状态。通过字段EmployeeID与表tblEmployee关联。/******Object:Table[dbo].[tblPerformance]ScriptDate:06/20/201117:15:21******/NULLSONEDIDENTIFIERONCREATETABLEdbotblPerformanceintNOTNULLDintNOTNULLintNULLedatetimeNOTNULLarintNOTNULLasontinyintNOTNULLintNULLmedatetimeNULLinyintNULLetinyintNULLommentncharNULLwCommentncharNULLONSTRAINTPKtblPerformancePRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblPerformance]WITHCHECKADDCONSTRAINTrformancetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblPerformance]CHECKCONSTRAINT[FK_tblPerformance_tblEmployee]ALTERTABLE[dbo].[tblPerformance]WITHCHECKADDCONSTRAINTrformancetblPerformStatusFOREIGNKEYStatusREFERENCESdbo[tblPerformStatus]([Type])ALTERTABLE[dbo].[tblPerformance]CHECKCONSTRAINTncetblPerformStatus表tblPerformance定义如下：formIDnt否yeeIDnt否iewerIDnt可datetime8否间formYearnt否ormSeasontinyint1否tinyint1可ditTimeDatetime8可Tinyint1可ewScoreTinyint1可nchar200可ewCommentnchar200可tblPerformancePerformID段，类型为int，设置自动增量。/******Object:Index[PK_tblPerformance]ScriptDate:06/18/201109:09:21******/ALTERTABLE[dbo].[tblPerformance]ADDCONSTRAINT[PK_tblPerformance]PRIMARYYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]字段关联。联。ALTERTABLE[dbo].[tblPerformance]WITHCHECKADDCONSTRAINTrformancetblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblPerformance]CHECKCONSTRAINT[FK_tblPerformance_tblEmployee]USE[RGB]ALTERTABLE[dbo].[tblPerformance]WITHCHECKADDCONSTRAINTrformancetblPerformStatusFOREIGNKEYStatusREFERENCESdbo[tblPerformStatus]([Type])ALTERTABLE[dbo].[tblPerformance]CHECKCONSTRAINTncetblPerformStatus无主键字段PerformID具有自动创建的聚集索引。3.9.表tblConfig表tblConfig用于保存系统配置信息和全局数据。表tblSysConfig中的基本信息包括：企业改这些配置信息和全局设定。/******Object:Table[dbo].[tblConfig]ScriptDate:06/20/201117:15:43******/NULLSONEDIDENTIFIERONPADDINGONCREATETABLEdbotblConfigntNOTNULLarNULLharNOTNULLarNULLCONSTRAINTPKtblConfigIDPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYDINGOFF表tblSysConfig定义如下：Dnt否char10可Namechar否achar可tblConfig的主键是ConfigID字段，类型为int，设置自动增量。/******Object:Index[PK_tblConfigID]ScriptDate:06/18/201109:12:03******/ALTERTABLE[dbo].[tblConfig]ADDCONSTRAINT[PK_tblConfig]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无主键字段具有自动创建的聚集索引。事件编号、事件发生时间和事件描述。/******Object:Table[dbo].[tblSystemEvent]ScriptDate:06/20/201117:16:17******/NULLSONEDIDENTIFIERONCREATETABLEdbotblSystemEventtNOTNULLdatetimeNOTNULLtMessagenvarcharNOTNULLONSTRAINTPKtblSystemEventPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARY表tblSystemEvent定义如下：tID否号Timee否生时间tMessagenvarchar100否述表tblSystemEvent的主键是EventID字段，类型为int，设置自动增量。/******Object:Index[PK_tblSystemEvent]ScriptDate:06/18/201109:13:37******/ALTERTABLE[dbo].[tblSystemEvent]ADDCONSTRAINT[PK_tblSystemEvent]PRIMARYYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无主键字段EventID具有自动创建的聚集索引。表tblPerformStatus用于保存绩效考核可能有的状态名称。表tblPerformStatus的基本信息包括：业绩评定类型和业绩评定名称。/******Object:Table[dbo].[tblPerformStatus]ScriptDate:06/20/201117:16:42******/NULLSONEDIDENTIFIERONCREATETABLEdbotblPerformStatusntNOTNULLcharNOTNULLCONSTRAINTPKtblPerformStatusPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYnyint否Namenchar10否绩效考核名称分“合格”******Object:Index[PK_tblPerformStatus]ALTERTABLE[dbo].[tblPerformStatus]ADDYCLUSTERED(ScriptDate109:14:16******/NSTRAINTPKtblPerformStatusPRIMARYWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无主键字段Type具有自动创建的聚集索引。表tblOvertimeType用于记录加班类型。表tblOvertimeType的基本信息包括：加班类型和加班描述。/******Object:Table[dbo].[tblOvertimeType]ScriptDate:06/20/201117:17:02******/NULLSONEDIDENTIFIERONCREATETABLEdbotblOvertimeTypentNOTNULLiptionncharNOTNULLONSTRAINTPKtblOvertimeTypePRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYtinyint1否型riptionnchar10否加班类型的名称算成年假”或“折算成津贴”/******Object:Index[PK_tblOvertimeType]ScriptDate:06/18/201109:15:04******/ALTERTABLE[dbo].[tblOvertimeType]ADDCONSTRAINT[PK_tblOvertimeType]PRIMARYYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无表tblOvertimeType的Description字段，只能取“折算成年假”或“折算成津贴”。主键字段Type具有自动创建的聚集索引。表tblHoliday用于记录假期信息。表tblHoliday的基本信息包括：假期编号、假期名称、假期具体时间和是否为法定假期。/******Object:Table[dbo].[tblHoliday]ScriptDate:06/20/201117:17:17******/NULLSONEDIDENTIFIERONCREATETABLEdbotblHolidayintNOTNULLtedatetimeNOTNULLayNamenvarcharNOTNULLlHolidaybitNOTNULLCONSTRAINTPKtblHolidayPRIMARYKEYCLUSTERED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYidayIDnt否假期编号dayDatedatetime8否假期具体日期dayNamenvarchar50否假期名称onalHolidaybit1否法定假期段只能取值“0”或“1”表tblHoliday的主键是HolidayID字段，类型是int,设置自动增量。/******Object:Index[PK_tblHoliday]ScriptDate:06/18/201109:15:51******/ALTERTABLE[dbo].[tblHoliday]ADDCONSTRAINT[PK_tblHoliday]PRIMARYKEYED(WITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]无无无表tblPerformItem用于记录绩效考核子项目信息。表tblPerformItem包含的注意内容有：绩效考核编号、绩效考核子项目编号、项目内容、自我评分和审核者评分。/******Object:Table[dbo].[tblPerformItem]ScriptDate:06/20/201117:17:44******/NULLSONEDIDENTIFIERONCREATETABLEdbotblPerformItememIDintNOTNULLintNOTNULLctContentncharNOTNULLinyintNULLetinyintNULLONSTRAINTPKtblPerformItemPRIMARYKEYCLUSTERED(ASCWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYALTERTABLE[dbo].[tblPerformItem]WITHCHECKADDCONSTRAINTrformItemtblPerformanceFOREIGNKEYPerformIDREFERENCESdbo[tblPerformance]([PerformID])ALTERTABLE[dbo].[tblPerformItem]CHECKCONSTRAINTtemtblPerformanceormItemIDnt否绩效考核子项目formIDnt否ntentnchar100否tinyint1可ewScoretinyint1可子项目编号PerformItemID。/******Object:Index[PK_tblPerformItem]ScriptDate:06/18/201109:16:31******/ALTERTABLE[dbo].[tblPerformItem]ADDCONSTRAINT[PK_tblPerformItem]PRIMARYYCLUSTERED(ASCWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOWPAGELOCKS=ON)ON[PRIMARY]表tblPerformItem的外键有PerformID，用来与表tblPerformce的PerformID相关联。ALTERTABLE[dbo].[tblPerformItem]WITHCHECKADDCONSTRAINTrformItemtblPerformanceFOREIGNKEYPerformIDREFERENCESdbo[tblPerformance]([PerformID])ALTERTABLE[dbo].[tblPerformItem]CHECKCONSTRAINTtemtblPerformance主键字段PerformItemID具有自动创建的聚集索引。表tblEmployeeLevel用于记录员工级别信息，包括员工级别和描述。/******Object:Table[dbo].[tblEmployeeLevel]ScriptDate:06/20/201117:18:01******/NULLSONEDIDENTIFIERONPADDINGONCREATETABLEdbotblEmployeeLevelevelintNOTNULLitionvarbinaryNOTNULLCONSTRAINTPKtblEmployeeLevelPRIMARYKEYCLUSTERED(ASCWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFFALLOWROWLOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY]ONPRIMARYDINGOFF表tblSalary定义如下：yeeLevelnt否iptionvarchar否表tblEmployeeLevel的主键是EmployeeLevel字段，类型为int。/******Object:Index[PK_tblEmployeeLevel]ScriptDate:06/18/201109:17:19******/ALTERTABLE[dbo].[tblEmployeeLevel]ADDCONSTRAINT[PK_tblEmployeeLevel]IMARYKEYCLUSTERED(ASCWITHPADINDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFFIGNORE_DUP_KEY=OFF