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

概述 7 內(nèi)容 7與其他項(xiàng)目的關(guān)聯(lián) 72.數(shù)據(jù)庫 72.1.數(shù)據(jù)庫名稱 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.存儲過程 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.用戶自定義函數(shù) 656.1.用戶自定義函數(shù)udfwinDeptAllEmployee 656.2.用戶自定義函數(shù)udfwinGetDeptIDbyDeptName 666.3.用戶自定義函數(shù)udfwinGetDeptNamebyDeptID 667.觸發(fā)器 677.1.觸發(fā)器tRejectRequest 678.數(shù)據(jù)庫安全性 671.概述 672.數(shù)據(jù)庫驗(yàn)證方式 671.概述目的本文為教學(xué)案例項(xiàng)目SQLServer功能規(guī)范說明書。本說明書將：描述數(shù)據(jù)庫設(shè)計(jì)的目的說明數(shù)據(jù)庫設(shè)計(jì)中的主要組成部分說明數(shù)據(jù)庫設(shè)計(jì)中涵蓋的教學(xué)知識要點(diǎn)本文檔主要內(nèi)容包括對數(shù)據(jù)庫設(shè)計(jì)結(jié)構(gòu)的總體描述，對數(shù)據(jù)庫中各種對象的描述(包括對象的名稱、對象的屬性、對象和其他對象的直接關(guān)系)。本文檔中包含對以下數(shù)據(jù)庫內(nèi)容的描述：數(shù)據(jù)表視圖存儲過程用戶自定義函數(shù)觸發(fā)器約束略，為數(shù)據(jù)庫管理員維護(hù)數(shù)據(jù)庫安全穩(wěn)定地運(yùn)行提供參考。教學(xué)案例項(xiàng)目的數(shù)據(jù)庫設(shè)計(jì)與教學(xué)項(xiàng)目(Web部分和Windows部分)功能密切相關(guān)。教學(xué)的程序部分，以實(shí)現(xiàn)一個(gè)功能完備的企業(yè)環(huán)境內(nèi)的應(yīng)用。提提示可通過使用Web應(yīng)用程序或Windows應(yīng)用程序來測試數(shù)據(jù)庫。2.數(shù)據(jù)庫2.1.數(shù)據(jù)庫名稱數(shù)據(jù)庫的名稱一定要設(shè)為RGB，否則本案例設(shè)計(jì)的Web部分的應(yīng)用程序和Windows部分的應(yīng)用程序?qū)o法使用該數(shù)據(jù)庫。/******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ù)庫中自動創(chuàng)建的dbo用戶之外，還要創(chuàng)建如下兩個(gè)用戶：般僅限于查看視圖和執(zhí)行存儲過程。.RGBASPUser：該用戶是Web應(yīng)用程序訪問數(shù)據(jù)庫所使用的賬號，它的權(quán)限和RGBWinUser用戶類似，即僅限于查看視圖和執(zhí)行存儲過程。/******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ù)庫將以員工信息為中心存儲相關(guān)數(shù)據(jù)，配合SQLServer數(shù)據(jù)庫系統(tǒng)中提供的數(shù)據(jù)管理，實(shí)現(xiàn)員工考勤、請假、加班管理及系統(tǒng)設(shè)置等業(yè)務(wù)功能。數(shù)據(jù)庫設(shè)計(jì)將以存儲員工信息的員工表為基礎(chǔ)，連接多張相關(guān)表實(shí)現(xiàn)對以下關(guān)系的支持：員工與請假申請員工與加班申請員工與考勤記錄員工與部門員工與部門經(jīng)理員工與績效考核記錄員工與工資此外數(shù)據(jù)庫中還將記錄教學(xué)案例應(yīng)用中需要的全局配置信息和事件日志記錄。數(shù)據(jù)庫系統(tǒng)主要的實(shí)體關(guān)系如下圖：(部門表中去掉部門經(jīng)理編號，在數(shù)據(jù)刪除時(shí)可能會出現(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可員工所屬部門編號cSalarynt可nvarchar50可“總裁”nenvarchar50可Datedatetime8否nvarchar200可初始為空，由員輸入cationRemainnt可yeeLevelnt可oImageimage16可表tblEmployee的主鍵是EmployeeID字段，類型為int，設(shè)置自動增量。******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具有自動創(chuàng)建的聚集索引。3.3.表tblDepartmentblDepartment該表通過與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否部門編號Namechar10可部門名稱iptionchar可部門描述tblDepartment的主鍵是DeptID字段，類型為int，設(shè)置自動增量。/******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具有自動創(chuàng)建的聚集索引。3.4.表tblSalary表tblSalary用于記錄員工每月的工資信息，包括工資發(fā)放日期、工資組成等。表tblSalary通過字段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否號yeeIDnt否datetime8否放時(shí)間cSalarynt可Salarynt可資senseSalarynt可除larynt可tblSalary的主鍵是SalaryID字段，類型為int，設(shè)置自動增量。/******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具有自動創(chuàng)建的聚集索引。3.5.表tblAttendance表tblAttendance用于記錄員工的考勤信息(上下班時(shí)間、記錄者信息等)，通過字段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否號yeeIDnt否chTimedatetime8可orderIDnt可不可與EmployeeID相同nchar4否型可取值“缺勤”，kReviewTinyint1可eDatetime8否表tblAttendance的主鍵是AttendID字段，類型為int，設(shè)置自動增量。/******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，即未請求重新審核ALTERTABLE[dbo].[tblAttendance]WITHCHECKADDCONSTRAINT[CK_tblAttendance]CHECKRecorderID<>[EmployeeID]AND([Type]='缺勤'OR[Type]='遲到’'OR[Type]='早退')))ALTERTABLE[dbo].[tblAttendance]CHECKCONSTRAINT[CK_tblAttendance]主鍵字段AttendID具有自動創(chuàng)建的聚集索引。3.6.表tblLeave表tblLeave用于記錄員工的請假記錄。表tblLeave中的基本信息包括：請假提交時(shí)間、請與表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可號s否nvarchar20可：消”、“已批準(zhǔn)”、“已否決”proverIDnt可Reasonnchar100可請的理由tblLeave的主鍵是LeaveID字段，類型為int，設(shè)置自動增量。/******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]請假開始時(shí)間應(yīng)小于請假結(jié)束時(shí)間；主鍵字段LeaveID具有自動創(chuàng)建的聚集索引。3.7.表tblOvertimelOvertime班起止時(shí)間、加班原因和加班申請批準(zhǔn)狀態(tài)。通過字段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否請編號yeeID否proverID可編號e否間e否mee否sonnvarchar100否由nchar10否“已提交”tinyint1可型reasonnchar100可請的理由snt可tblOvertimeOvertimeID段，類型為int，不設(shè)置自動增量。/******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，用來與表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具有自動創(chuàng)建的聚集索引。3.8.表tblPerformance表tblPerformance用于保存員工的績效信息。表tblPerformance中的基本信息包括：員工編號、考評者編號、提交時(shí)間、考評時(shí)間、評語和績效狀態(tài)。通過字段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è)置自動增量。/******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無主鍵字段PerformID具有自動創(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è)置自動增量。/******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]無主鍵字段具有自動創(chuàng)建的聚集索引。事件編號、事件發(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否號Timee否生時(shí)間tMessagenvarchar100否述表tblSystemEvent的主鍵是EventID字段，類型為int，設(shè)置自動增量。/******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具有自動創(chuàng)建的聚集索引。表tblPerformStatus用于保存績效考核可能有的狀態(tài)名稱。表tblPerformStatus的基本信息包括：業(yè)績評定類型和業(yè)績評定名稱。/******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具有自動創(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]無表tblOvertimeType的Description字段，只能取“折算成年假”或“折算成津貼”。主鍵字段Type具有自動創(chuàng)建的聚集索引。表tblHoliday用于記錄假期信息。表tblHoliday的基本信息包括：假期編號、假期名稱、假期具體時(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否假期編號dayDatedatetime8否假期具體日期dayNamenvarchar50否假期名稱onalHolidaybit1否法定假期段只能取值“0”或“1”表tblHoliday的主鍵是HolidayID字段，類型是int,設(shè)置自動增量。/******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用于記錄績效考核子項(xiàng)目信息。表tblPerformItem包含的注意內(nèi)容有：績效考核編號、績效考核子項(xiàng)目編號、項(xiàng)目內(nèi)容、自我評分和審核者評分。/******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否績效考核子項(xiàng)目formIDnt否ntentnchar100否tinyint1可ewScoretinyint1可子項(xiàng)目編號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相關(guān)聯(lián)。ALTERTABLE[dbo].[tblPerformItem]WITHCHECKADDCONSTRAINTrformItemtblPerformanceFOREIGNKEYPerformIDREFERENCESdbo[tblPerformance]([PerformID])ALTERTABLE[dbo].[tblPerformItem]CHECKCONSTRAINTtemtblPerformance主鍵字段PerformItemID具有自動創(chuàng)建的聚集索引。表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