人事管理系統(tǒng)項(xiàng)目詳細(xì)設(shè)計(jì)說(shuō)明書

概述 7 內(nèi)容 7與其他項(xiàng)目的關(guān)聯(lián) 72.數(shù)據(jù)庫(kù) 72.1.數(shù)據(jù)庫(kù)名稱 72.2.用戶 83.表 83.1.表設(shè)計(jì)概述 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.存儲(chǔ)過(guò)程 375.1.存儲(chǔ)過(guò)程spwebAddLeaveReq 375.2.存儲(chǔ)過(guò)程spwebAddOTReq 375.3.存儲(chǔ)過(guò)程spwebAskReview 385.4.存儲(chǔ)過(guò)程spwebCancelLeaveReq 39存儲(chǔ)過(guò)程spwebCancelOTReq 395.6.存儲(chǔ)過(guò)程spwebChangeLeaveStatus 405.7.存儲(chǔ)過(guò)程spwebChangeOTStatus 415.8.存儲(chǔ)過(guò)程spwebGetCEOInfo 415.9.存儲(chǔ)過(guò)程spwebGetDeptAttendSummary 425.10.存儲(chǔ)過(guò)程spwebGetDeptLvSummary 425.11.存儲(chǔ)過(guò)程spwebGetDeptOTSummary 435.12.存儲(chǔ)過(guò)程spwebGetEmployeeID 445.13.存儲(chǔ)過(guò)程spwebGetEmpPassword 445.14.存儲(chǔ)過(guò)程spwebGetEmpPasswordByID 45.存儲(chǔ)過(guò)程spwebUpdatePassword 465.16.存儲(chǔ)過(guò)程spwebUpdateSelfIntro 465.17.存儲(chǔ)過(guò)程spwebAddPerformObject 47.存儲(chǔ)過(guò)程spwebDeletePerformItem 485.19.存儲(chǔ)過(guò)程spwebGetDeptSalarySummary 485.20.存儲(chǔ)過(guò)程spwebUpdatePerform 495.21.存儲(chǔ)過(guò)程spwebUpdatePerformItem 495.22.存儲(chǔ)過(guò)程spwinAttendanceQuery 505.23.存儲(chǔ)過(guò)程spwinChangeEmployeeDept 515.24.存儲(chǔ)過(guò)程spwinCreateDepartment 515.25.存儲(chǔ)過(guò)程spwinDeleteDepartment 525.26.存儲(chǔ)過(guò)程spwinDeleteEmployee 525.27.存儲(chǔ)過(guò)程spwinDelLeaveRequest 535.28.存儲(chǔ)過(guò)程spwinDeptAllEmployee 54.存儲(chǔ)過(guò)程spwinDeptLeave 545.30.存儲(chǔ)過(guò)程spwinEmpLeave 555.31.存儲(chǔ)過(guò)程spwinGetAllDepartment 555.32.存儲(chǔ)過(guò)程spwinGetEmpbyDeptName 565.33.存儲(chǔ)過(guò)程spwinMoveEmpBetweenDept 565.34.存儲(chǔ)過(guò)程spwinRejectLeaveRequest 575.35.存儲(chǔ)過(guò)程spwebUpdatePerformItemSelf 585.36.存儲(chǔ)過(guò)程spwebUpdatePerformItemReview 585.37.存儲(chǔ)過(guò)程spwebGetSubmittedPerform 595.38.存儲(chǔ)過(guò)程spwebGetDeptPerformSummary 59.存儲(chǔ)過(guò)程spwinOTbyGroup 605.40.存儲(chǔ)過(guò)程spwebPerformReviewed 61.存儲(chǔ)過(guò)程spwinBasicSalaryByEmpID 615.42.存儲(chǔ)過(guò)程spwinSalaryHistoryByEmpID 625.43.存儲(chǔ)過(guò)程spwinSetBasicSalary 625.44.存儲(chǔ)過(guò)程spwinQueryEventbyTime 63.存儲(chǔ)過(guò)程spwinChangePassword 645.46.存儲(chǔ)過(guò)程spwinAddEvent 645.47.存儲(chǔ)過(guò)程spwebUpdatePerformItemObj 656.用戶自定義函數(shù) 656.1.用戶自定義函數(shù)udfwinDeptAllEmployee 656.2.用戶自定義函數(shù)udfwinGetDeptIDbyDeptName 666.3.用戶自定義函數(shù)udfwinGetDeptNamebyDeptID 667.觸發(fā)器 677.1.觸發(fā)器tRejectRequest 678.數(shù)據(jù)庫(kù)安全性 671.概述 672.數(shù)據(jù)庫(kù)驗(yàn)證方式 671.概述目的本文為教學(xué)案例項(xiàng)目SQLServer功能規(guī)范說(shuō)明書。本說(shuō)明書將：描述數(shù)據(jù)庫(kù)設(shè)計(jì)的目的說(shuō)明數(shù)據(jù)庫(kù)設(shè)計(jì)中的主要組成部分說(shuō)明數(shù)據(jù)庫(kù)設(shè)計(jì)中涵蓋的教學(xué)知識(shí)要點(diǎn)本文檔主要內(nèi)容包括對(duì)數(shù)據(jù)庫(kù)設(shè)計(jì)結(jié)構(gòu)的總體描述，對(duì)數(shù)據(jù)庫(kù)中各種對(duì)象的描述(包括對(duì)象的名稱、對(duì)象的屬性、對(duì)象和其他對(duì)象的直接關(guān)系)。本文檔中包含對(duì)以下數(shù)據(jù)庫(kù)內(nèi)容的描述：數(shù)據(jù)表視圖存儲(chǔ)過(guò)程用戶自定義函數(shù)觸發(fā)器約束略，為數(shù)據(jù)庫(kù)管理員維護(hù)數(shù)據(jù)庫(kù)安全穩(wěn)定地運(yùn)行提供參考。教學(xué)案例項(xiàng)目的數(shù)據(jù)庫(kù)設(shè)計(jì)與教學(xué)項(xiàng)目(Web部分和Windows部分)功能密切相關(guān)。教學(xué)的程序部分，以實(shí)現(xiàn)一個(gè)功能完備的企業(yè)環(huán)境內(nèi)的應(yīng)用。提提示可通過(guò)使用Web應(yīng)用程序或Windows應(yīng)用程序來(lái)測(cè)試數(shù)據(jù)庫(kù)。2.數(shù)據(jù)庫(kù)2.1.數(shù)據(jù)庫(kù)名稱數(shù)據(jù)庫(kù)的名稱一定要設(shè)為RGB，否則本案例設(shè)計(jì)的Web部分的應(yīng)用程序和Windows部分的應(yīng)用程序?qū)o(wú)法使用該數(shù)據(jù)庫(kù)。/******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.用戶除了數(shù)據(jù)庫(kù)中自動(dòng)創(chuàng)建的dbo用戶之外，還要?jiǎng)?chuàng)建如下兩個(gè)用戶：般僅限于查看視圖和執(zhí)行存儲(chǔ)過(guò)程。.RGBASPUser：該用戶是Web應(yīng)用程序訪問(wèn)數(shù)據(jù)庫(kù)所使用的賬號(hào)，它的權(quán)限和RGBWinUser用戶類似，即僅限于查看視圖和執(zhí)行存儲(chǔ)過(guò)程。/******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.表設(shè)計(jì)概述根據(jù)教學(xué)案例功能，數(shù)據(jù)庫(kù)將以員工信息為中心存儲(chǔ)相關(guān)數(shù)據(jù)，配合SQLServer數(shù)據(jù)庫(kù)系統(tǒng)中提供的數(shù)據(jù)管理，實(shí)現(xiàn)員工考勤、請(qǐng)假、加班管理及系統(tǒng)設(shè)置等業(yè)務(wù)功能。數(shù)據(jù)庫(kù)設(shè)計(jì)將以存儲(chǔ)員工信息的員工表為基礎(chǔ)，連接多張相關(guān)表實(shí)現(xiàn)對(duì)以下關(guān)系的支持：?jiǎn)T工與請(qǐng)假申請(qǐng)員工與加班申請(qǐng)員工與考勤記錄員工與部門員工與部門經(jīng)理員工與績(jī)效考核記錄員工與工資此外數(shù)據(jù)庫(kù)中還將記錄教學(xué)案例應(yīng)用中需要的全局配置信息和事件日志記錄。數(shù)據(jù)庫(kù)系統(tǒng)主要的實(shí)體關(guān)系如下圖：(部門表中去掉部門經(jīng)理編號(hào)，在數(shù)據(jù)刪除時(shí)可能會(huì)出現(xiàn)死鎖)EDIDENTIFIERON3.2.表tblEmployeetblEmployeeDeptID和Title可以確定員工部門和職位信息。當(dāng)Title的值為“經(jīng)理”時(shí)可以確定此員工為該部門的部門經(jīng)理。/******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可員工所屬部門編號(hào)cSalarynt可nvarchar50可“總裁”nenvarchar50可Datedatetime8否nvarchar200可初始為空，由員輸入cationRemainnt可yeeLevelnt可oImageimage16可表tblEmployee的主鍵是EmployeeID字段，類型為int，設(shè)置自動(dòng)增量。******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字段關(guān)聯(lián)。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具有自動(dòng)創(chuàng)建的聚集索引。3.3.表tblDepartmentblDepartment該表通過(guò)與tblEmployee表關(guān)聯(lián)可以確定員工所屬的部門。/******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否部門編號(hào)Namechar10可部門名稱iptionchar可部門描述tblDepartment的主鍵是DeptID字段，類型為int，設(shè)置自動(dòng)增量。/******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]無(wú)主鍵字段DeptID具有自動(dòng)創(chuàng)建的聚集索引。3.4.表tblSalary表tblSalary用于記錄員工每月的工資信息，包括工資發(fā)放日期、工資組成等。表tblSalary通過(guò)字段EmployeeID與表tblEmployee關(guān)聯(lián)。/******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否號(hào)yeeIDnt否datetime8否放時(shí)間cSalarynt可Salarynt可資senseSalarynt可除larynt可tblSalary的主鍵是SalaryID字段，類型為int，設(shè)置自動(dòng)增量。/******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]關(guān)聯(lián)。ALTERTABLE[dbo].[tblSalary]WITHCHECKADDCONSTRAINTlarytblEmployeeFOREIGNKEYEmployeeIDREFERENCESdbo[tblEmployee]([EmployeeID])ALTERTABLE[dbo].[tblSalary]CHECKCONSTRAINT[FK_tblSalary_tblEmployee]主鍵字段SalaryID具有自動(dòng)創(chuàng)建的聚集索引。3.5.表tblAttendance表tblAttendance用于記錄員工的考勤信息(上下班時(shí)間、記錄者信息等)，通過(guò)字段EmployeeID與表tblEmployee關(guān)聯(lián)。/******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否號(hào)yeeIDnt否chTimedatetime8可orderIDnt可不可與EmployeeID相同nchar4否型可取值“缺勤”，kReviewTinyint1可eDatetime8否表tblAttendance的主鍵是AttendID字段，類型為int，設(shè)置自動(dòng)增量。/******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字段關(guān)聯(lián)。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字段默認(rèn)值為0，即未請(qǐng)求重新審核ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINT[CK_tblAttendance]CHECKRecorderID<>[EmployeeID]AND([Type]='缺勤'OR[Type]='遲到’'OR[Type]='早退')))ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[CK_tblAttendance]主鍵字段AttendID具有自動(dòng)創(chuàng)建的聚集索引。3.6.表tblLeave表tblLeave用于記錄員工的請(qǐng)假記錄。表tblLeave中的基本信息包括：請(qǐng)假提交時(shí)間、請(qǐng)與表tblEmployee關(guān)聯(lián)。/******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可號(hào)s否nvarchar20可：消”、“已批準(zhǔn)”、“已否決”proverIDnt可Reasonnchar100可請(qǐng)的理由tblLeave的主鍵是LeaveID字段，類型為int，設(shè)置自動(dòng)增量。/******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字段關(guān)聯(lián)。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]請(qǐng)假開始時(shí)間應(yīng)小于請(qǐng)假結(jié)束時(shí)間；主鍵字段LeaveID具有自動(dòng)創(chuàng)建的聚集索引。3.7.表tblOvertimelOvertime班起止時(shí)間、加班原因和加班申請(qǐng)批準(zhǔn)狀態(tài)。通過(guò)字段EmployeeID與表tblEmployee關(guān)聯(lián)。/******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否請(qǐng)編號(hào)yeeID否proverID可編號(hào)e否間e否mee否sonnvarchar100否由nchar10否“已提交”tinyint1可型reasonnchar100可請(qǐng)的理由snt可tblOvertimeOvertimeID段，類型為int，不設(shè)置自動(dòng)增量。/******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]字段關(guān)聯(lián)。表tblOvertime的外鍵是Type，類型為tinyint，用來(lái)與表tblOverTimeType的Type相關(guān)聯(lián)。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]加班開始時(shí)間應(yīng)小于加班結(jié)束時(shí)間。主鍵字段OvertimeID具有自動(dòng)創(chuàng)建的聚集索引。3.8.表tblPerformance表tblPerformance用于保存員工的績(jī)效信息。表tblPerformance中的基本信息包括：?jiǎn)T工編號(hào)、考評(píng)者編號(hào)、提交時(shí)間、考評(píng)時(shí)間、評(píng)語(yǔ)和績(jī)效狀態(tài)。通過(guò)字段EmployeeID與表tblEmployee關(guān)聯(lián)。/******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，設(shè)置自動(dòng)增量。/******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]字段關(guān)聯(lián)。聯(lián)。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無(wú)主鍵字段PerformID具有自動(dòng)創(chuàng)建的聚集索引。3.9.表tblConfig表tblConfig用于保存系統(tǒng)配置信息和全局?jǐn)?shù)據(jù)。表tblSysConfig中的基本信息包括：企業(yè)改這些配置信息和全局設(shè)定。/******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，設(shè)置自動(dòng)增量。/******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]無(wú)主鍵字段具有自動(dòng)創(chuàng)建的聚集索引。事件編號(hào)、事件發(fā)生時(shí)間和事件描述。/******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否號(hào)Timee否生時(shí)間tMessagenvarchar100否述表tblSystemEvent的主鍵是EventID字段，類型為int，設(shè)置自動(dòng)增量。/******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]無(wú)主鍵字段EventID具有自動(dòng)創(chuàng)建的聚集索引。表tblPerformStatus用于保存績(jī)效考核可能有的狀態(tài)名稱。表tblPerformStatus的基本信息包括：業(yè)績(jī)?cè)u(píng)定類型和業(yè)績(jī)?cè)u(píng)定名稱。/******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否績(jī)效考核名稱分“合格”******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]無(wú)主鍵字段Type具有自動(dòng)創(chuàng)建的聚集索引。表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]無(wú)表tblOvertimeType的Description字段，只能取“折算成年假”或“折算成津貼”。主鍵字段Type具有自動(dòng)創(chuàng)建的聚集索引。表tblHoliday用于記錄假期信息。表tblHoliday的基本信息包括：假期編號(hào)、假期名稱、假期具體時(shí)間和是否為法定假期。/******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否假期編號(hào)dayDatedatetime8否假期具體日期dayNamenvarchar50否假期名稱onalHolidaybit1否法定假期段只能取值“0”或“1”表tblHoliday的主鍵是HolidayID字段，類型是int,設(shè)置自動(dòng)增量。/******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]無(wú)無(wú)無(wú)表tblPerformItem用于記錄績(jī)效考核子項(xiàng)目信息。表tblPerformItem包含的注意內(nèi)容有：績(jī)效考核編號(hào)、績(jī)效考核子項(xiàng)目編號(hào)、項(xiàng)目?jī)?nèi)容、自我評(píng)分和審核者評(píng)分。/******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否績(jī)效考核子項(xiàng)目formIDnt否ntentnchar100否tinyint1可ewScoretinyint1可子項(xiàng)目編號(hào)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，用來(lái)與表tblPerformce的PerformID相關(guān)聯(lián)。ALTERTABLE[dbo].[tblPerformItem]WITHCHECKADDCONSTRAINTrformItemtblPerformanceFOREIGNKEYPerformIDREFERENCESdbo[tblPerformance]([PerformID])ALTERTABLE[dbo].[tblPerformItem]CHECKCONSTRAINTtemtblPerformance主鍵字段PerformItemID具有自動(dòng)創(chuàng)建的聚集索引。表tblEmployeeLevel用于記錄員工級(jí)別信息，包括員工級(jí)別和描述。/******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