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

红绿蓝公司文档信息文档名称红绿蓝公司人事管理系统详细设计说明书描述该文档包括对功能规范中每个模块所涉及的类、网页或窗体的具体定义。学生可以根据详细设计说明书来编写代码。负责人张无忌状态最终版项目组成测试主管佐佐开发主管佑佑项目经理张无忌文档变更历史时间修改人章节描述2009520张无忌所有章节创建文档初稿200961张无忌2009610张无忌2009620张无忌所有章节文档路径/RGB/HR/DOCUMENTS/相关文档文档路径测试案例/RGB/HR/DOCUMENTS/测试案例DOC详细设计/RGB/HR/DOCUMENTS/功能规范DOC审核结果审核人意见签名档全体团队通过佐佐佑佑张无忌测试团队通过佐佐目录1概述711目的712内容713与其他项目的关联72数据库721数据库名称722用户83表831表设计概述832表TBLEMPLOYEE933表TBLDEPARTMENT1134表TBLSALARY1135表TBLATTENDANCE1236表TBLLEAVE1437表TBLOVERTIME1538表TBLPERFORMANCE1639表TBLCONFIG17310表TBLSYSTEMEVENT18311表TBLPERFORMSTATUS19312表TBLOVERTIMETYPE20313表TBLHOLIDAY21314表TBLPERFORMITEM224视图2441视图VIWWEBEMPCOMMONINFO2442视图VIWWEBALLATTEND2543视图VIWWEBALLLEAVEREG2544视图VIWWEBALLOTREG2645视图VIWWEBCOMPANYPOLICY2746视图VIWWEBDEPARTMENT2847视图VIWWEBHOLIDAY2848视图VIWWEBMANAGERINFO2949视图VIWWEBSUBMITTEDLVREG29410视图VIWWEBSUBMITTEDOTREG30411视图VIWWEBHISTORYSALARY31412视图VIWWEBPERFORMANCE32413视图VIWWEBPERFORMITEM33414视图VIWWINEMPLOYEELIST33415视图VIWWINATTENDANCE34416视图VIWWINLEAVELIST35417视图VIWWINOTLIST35418视图VIWWINEMPBASICSALARY365存储过程3751存储过程SPWEBADDLEAVEREQ3752存储过程SPWEBADDOTREQ3753存储过程SPWEBASKREVIEW3854存储过程SPWEBCANCELLEAVEREQ3955存储过程SPWEBCANCELOTREQ3956存储过程SPWEBCHANGELEAVESTATUS4057存储过程SPWEBCHANGEOTSTATUS4158存储过程SPWEBGETCEOINFO4159存储过程SPWEBGETDEPTATTENDSUMMARY42510存储过程SPWEBGETDEPTLVSUMMARY42511存储过程SPWEBGETDEPTOTSUMMARY43512存储过程SPWEBGETEMPLOYEEID44513存储过程SPWEBGETEMPPASSWORD44514存储过程SPWEBGETEMPPASSWORDBYID45515存储过程SPWEBUPDATEPASSWORD46516存储过程SPWEBUPDATESELFINTRO46517存储过程SPWEBADDPERFORMOBJECT47518存储过程SPWEBDELETEPERFORMITEM48519存储过程SPWEBGETDEPTSALARYSUMMARY48520存储过程SPWEBUPDATEPERFORM49521存储过程SPWEBUPDATEPERFORMITEM49522存储过程SPWINATTENDANCEQUERY50523存储过程SPWINCHANGEEMPLOYEEDEPT51524存储过程SPWINCREATEDEPARTMENT51525存储过程SPWINDELETEDEPARTMENT52526存储过程SPWINDELETEEMPLOYEE52527存储过程SPWINDELLEAVEREQUEST53528存储过程SPWINDEPTALLEMPLOYEE54529存储过程SPWINDEPTLEAVE54530存储过程SPWINEMPLEAVE55531存储过程SPWINGETALLDEPARTMENT55532存储过程SPWINGETEMPBYDEPTNAME56533存储过程SPWINMOVEEMPBETWEENDEPT56534存储过程SPWINREJECTLEAVEREQUEST57535存储过程SPWEBUPDATEPERFORMITEMSELF58536存储过程SPWEBUPDATEPERFORMITEMREVIEW58537存储过程SPWEBGETSUBMITTEDPERFORM59538存储过程SPWEBGETDEPTPERFORMSUMMARY59539存储过程SPWINOTBYGROUP60540存储过程SPWEBPERFORMREVIEWED61541存储过程SPWINBASICSALARYBYEMPID61542存储过程SPWINSALARYHISTORYBYEMPID62543存储过程SPWINSETBASICSALARY62544存储过程SPWINQUERYEVENTBYTIME63545存储过程SPWINCHANGEPASSWORD64546存储过程SPWINADDEVENT64547存储过程SPWEBUPDATEPERFORMITEMOBJ656用户自定义函数6561用户自定义函数UDFWINDEPTALLEMPLOYEE6562用户自定义函数UDFWINGETDEPTIDBYDEPTNAME6663用户自定义函数UDFWINGETDEPTNAMEBYDEPTID667触发器6771触发器TREJECTREQUEST678数据库安全性6781概述6782数据库验证方式671概述11目的本文为教学案例项目SQLSERVER功能规范说明书。本说明书将描述数据库设计的目的说明数据库设计中的主要组成部分说明数据库设计中涵盖的教学知识要点12内容本文档主要内容包括对数据库设计结构的总体描述，对数据库中各种对象的描述（包括对象的名称、对象的属性、对象和其他对象的直接关系）。本文档中包含对以下数据库内容的描述数据表视图存储过程用户自定义函数触发器约束在数据库主要对象之外，本文还将描述数据库安全性设置、数据库属性设置和数据库备份策略，为数据库管理员维护数据库安全稳定地运行提供参考。13与其他项目的关联教学案例项目的数据库设计与教学项目（WEB部分和WINDOWS部分）功能密切相关。教学案例项目的数据库将按照教学项目程序部分的功能需求而设计，数据库设计将配合教学案例的程序部分，以实现一个功能完备的企业环境内的应用。提示可通过使用WEB应用程序或WINDOWS应用程序来测试数据库。2数据库21数据库名称数据库的名称一定要设为RGB，否则本案例设计的WEB部分的应用程序和WINDOWS部分的应用程序将无法使用该数据库。答案USEMASTERGO/OBJECTDATABASERGBSCRIPTDATE06/18/2011085558/CREATEDATABASERGBONPRIMARYNAMENRGB,FILENAMENCPROGRAMFILESMICROSOFTSQLSERVERMSSQL10MSSQLSERVERMSSQLDATARGBMDF,SIZE3072KB,MAXSIZEUNLIMITED,FILEGROWTH1024KBLOGONNAMENRGB_LOG,FILENAMENCPROGRAMFILESMICROSOFTSQLSERVERMSSQL10MSSQLSERVERMSSQLDATARGB_LOGLDF,SIZE1024KB,MAXSIZE2048GB,FILEGROWTH10GOALTERDATABASERGBSETCOMPATIBILITY_LEVEL100GOIF1FULLTEXTSERVICEPROPERTYISFULLTEXTINSTALLEDBEGINEXECRGBDBOSP_FULLTEXT_DATABASEACTIONENABLEENDGOALTERDATABASERGBSETANSI_NULL_DEFAULTOFFGOALTERDATABASERGBSETANSI_NULLSOFFGOALTERDATABASERGBSETANSI_PADDINGOFFGOALTERDATABASERGBSETANSI_WARNINGSOFFGOALTERDATABASERGBSETARITHABORTOFFGOALTERDATABASERGBSETAUTO_CLOSEOFFGOALTERDATABASERGBSETAUTO_CREATE_STATISTICSONGOALTERDATABASERGBSETAUTO_SHRINKOFFGOALTERDATABASERGBSETAUTO_UPDATE_STATISTICSONGOALTERDATABASERGBSETCURSOR_CLOSE_ON_COMMITOFFGOALTERDATABASERGBSETCURSOR_DEFAULTGLOBALGOALTERDATABASERGBSETCONCAT_NULL_YIELDS_NULLOFFGOALTERDATABASERGBSETNUMERIC_ROUNDABORTOFFGOALTERDATABASERGBSETQUOTED_IDENTIFIEROFFGOALTERDATABASERGBSETRECURSIVE_TRIGGERSOFFGOALTERDATABASERGBSETDISABLE_BROKERGOALTERDATABASERGBSETAUTO_UPDATE_STATISTICS_ASYNCOFFGOALTERDATABASERGBSETDATE_CORRELATION_OPTIMIZATIONOFFGOALTERDATABASERGBSETTRUSTWORTHYOFFGOALTERDATABASERGBSETALLOW_SNAPSHOT_ISOLATIONOFFGOALTERDATABASERGBSETPARAMETERIZATIONSIMPLEGOALTERDATABASERGBSETREAD_COMMITTED_SNAPSHOTOFFGOALTERDATABASERGBSETHONOR_BROKER_PRIORITYOFFGOALTERDATABASERGBSETREAD_WRITEGOALTERDATABASERGBSETRECOVERYFULLGOALTERDATABASERGBSETMULTI_USERGOALTERDATABASERGBSETPAGE_VERIFYCHECKSUMGOALTERDATABASERGBSETDB_CHAININGOFFGO22用户除了数据库中自动创建的DBO用户之外，还要创建如下两个用户RGBWINUSER该用户是WINDOWS应用程序访问数据库所使用的账号，它的权限一般仅限于查看视图和执行存储过程。RGBASPUSER该用户是WEB应用程序访问数据库所使用的账号，它的权限和RGBWINUSER用户类似，即仅限于查看视图和执行存储过程。答案1USERGBGO/OBJECTUSERRGBWINUSERSCRIPTDATE06/18/2011082915/GOCREATEUSERRGBWINUSERFORLOGINWOWITHDEFAULT_SCHEMADBOGO2USERGBGO/OBJECTUSERRGBASPUSERSCRIPTDATE06/18/2011083046/GOCREATEUSERRGBASPUSERFORLOGINNIWITHDEFAULT_SCHEMADBOGO3表31表设计概述根据教学案例功能，数据库将以员工信息为中心存储相关数据，配合SQLSERVER数据库系统中提供的数据管理，实现员工考勤、请假、加班管理及系统设置等业务功能。数据库设计将以存储员工信息的员工表为基础，连接多张相关表实现对以下关系的支持员工与请假申请员工与加班申请员工与考勤记录员工与部门员工与部门经理员工与绩效考核记录员工与工资此外数据库中还将记录教学案例应用中需要的全局配置信息和事件日志记录。数据库系统主要的实体关系如下图（部门表中去掉部门经理编号，在数据删除时可能会出现死锁）32表TBLEMPLOYEE概述表TBLEMPLOYEE用于记录员工基本信息，并作为基础表与其他表联接。该表通过DEPTID和TITLE可以确定员工部门和职位信息。当TITLE的值为“经理”时可以确定此员工为该部门的部门经理。答案USERGBGO/OBJECTTABLEDBOTBLEMPLOYEESCRIPTDATE06/20/2011171337/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLEDBOTBLEMPLOYEEEMPLOYEEIDINTNOTNULL,NAMENVARCHAR50NOTNULL,LOGINNAMENVARCHAR20NOTNULL,PASSWORDBINARY20NULL,EMAILNVARCHAR50NOTNULL,DEPTIDINTNULL,BASICSALARYINTNULL,TITLENVARCHAR50NULL,TELEPHONENVARCHAR50NULL,ONHOARDDATEDATETIMENOTNULL,SELFINTRONVARCHAR200NULL,VACATIONREMAININTNULL,EMPLOYEELEVELINTNULL,PHOTOIMAGEIMAGENULL,CONSTRAINTPK_TBLEMPLOYEEPRIMARYKEYCLUSTEREDEMPLOYEEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYTEXTIMAGE_ONPRIMARYGOSETANSI_PADDINGOFFGOALTERTABLEDBOTBLEMPLOYEEWITHCHECKADDCONSTRAINTFK_TBLEMPLOYEE_TBLDEPARTMENTFOREIGNKEYDEPTIDREFERENCESDBOTBLDEPARTMENTDEPTIDGOALTERTABLEDBOTBLEMPLOYEECHECKCONSTRAINTFK_TBLEMPLOYEE_TBLDEPARTMENTGOALTERTABLEDBOTBLEMPLOYEEWITHCHECKADDCONSTRAINTFK_TBLEMPLOYEE_TBLEMPLOYEELEVELFOREIGNKEYEMPLOYEELEVELREFERENCESDBOTBLEMPLOYEELEVELEMPLOYEELEVELGOALTERTABLEDBOTBLEMPLOYEECHECKCONSTRAINTFK_TBLEMPLOYEE_TBLEMPLOYEELEVELGO表定义表TBLEMPLOYEE定义如下名称类型可否为空说明备注EMPLOYEEIDINT4否员工编号自动生成NAMENVARCHAR50否员工姓名LOGINNAMENVARCHAR20否员工登录名建议为英文字符，且与姓名不同PASSWORDBINARY20可员工登录密码EMAILNVARCHAR50否员工电子邮件DEPTIDINT4可员工所属部门编号BASICSALARYINT4可员工基本工资TITLENVARCHAR50可员工职位名称可取值为“普通员工”、“部门经理”、“总裁”TELEPHONENVARCHAR50可员工电话ONBOARDDATEDATETIME8否员工报到日期SELFINTRONVARCHAR200可员工自我介绍初始为空，由员工自行输入VACATIONREMAININT4可员工剩余假期小时数EMPLOYEELEVELINT4可员工的级别PHOTOIMAGEIMAGE16可员工照片主键表TBLEMPLOYEE的主键是EMPLOYEEID字段，类型为INT，设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLEMPLOYEESCRIPTDATE06/18/2011090053/ALTERTABLEDBOTBLEMPLOYEEADDCONSTRAINTPK_TBLEMPLOYEEPRIMARYKEYCLUSTEREDEMPLOYEEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键表TBLEMPLOYEE的外键有DEPTID，类型为INT，用于与表TBLDEPARTMENT中的DEPTID字段关联。DEPTID字段可以为空，在此情况下表示员工不在任何部门中。表TBLEMPLOYEE的外键有EMPLOYEELEVEL，类型为INT，用于与表TBLEMPLOYEELEVEL中的EMPLOYEELEVEL字段关联。1USERGBGOALTERTABLEDBOTBLEMPLOYEEWITHCHECKADDCONSTRAINTFK_TBLEMPLOYEE_TBLDEPARTMENTFOREIGNKEYDEPTIDREFERENCESDBOTBLDEPARTMENTDEPTIDGOALTERTABLEDBOTBLEMPLOYEECHECKCONSTRAINTFK_TBLEMPLOYEE_TBLDEPARTMENTGO2USERGBGOALTERTABLEDBOTBLEMPLOYEEWITHCHECKADDCONSTRAINTFK_TBLEMPLOYEE_TBLEMPLOYEELEVELFOREIGNKEYEMPLOYEELEVELREFERENCESDBOTBLEMPLOYEELEVELEMPLOYEELEVELGOALTERTABLEDBOTBLEMPLOYEECHECKCONSTRAINTFK_TBLEMPLOYEE_TBLEMPLOYEELEVELGO约束表TBLEMPLOYEE中的LOGINNAME字段建议为48位小写英文字符，且不能与员工姓名相同也不可以为空字符串。索引主键字段EMPLOYEEID具有自动创建的聚集索引。33表TBLDEPARTMENT概述表TBLDEPARTMENT用于记录企业内部的部门信息。每个独立的部门在该表中都对应一条记录。该表通过与TBLEMPLOYEE表关联可以确定员工所属的部门。答案USERGBGO/OBJECTTABLEDBOTBLDEPARTMENTSCRIPTDATE06/20/2011171312/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDINGONGOCREATETABLEDBOTBLDEPARTMENTDEPTIDINTNOTNULL,DEPTNAMECHAR10NULL,DESCRIPTIONCHAR50NULL,CONSTRAINTPK_TBLDEPARTMENTPRIMARYKEYCLUSTEREDDEPTIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYGOSETANSI_PADDINGOFFGO表定义表TBLDEPARTMENT定义如下名称类型可否为空说明备注DEPTIDINT4否部门编号自动生成DEPTNAMECHAR10可部门名称DESCIPTIONCHAR50可部门描述主键表TBLDEPARTMENT的主键是DEPTID字段，类型为INT，设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLDEPARTMENTSCRIPTDATE06/18/2011090312/ALTERTABLEDBOTBLDEPARTMENTADDCONSTRAINTPK_TBLDEPARTMENTPRIMARYKEYCLUSTEREDDEPTIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键无约束无其他约束索引主键字段DEPTID具有自动创建的聚集索引。34表TBLSALARY概述表TBLSALARY用于记录员工每月的工资信息，包括工资发放日期、工资组成等。表TBLSALARY通过字段EMPLOYEEID与表TBLEMPLOYEE关联。答案USERGBGO/OBJECTTABLEDBOTBLSALARYSCRIPTDATE06/20/2011171247/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEDBOTBLSALARYSALARYIDINTNOTNULL,EMPLOYEEIDINTNOTNULL,SALARYTIMEDATETIMENOTNULL,BASICLSALARYINTNULL,OVERTIMESALARYINTNULL,ABSENSESALARYINTNULL,OTHERSALARYINTNULL,CONSTRAINTPK_TBLSALARYPRIMARYKEYCLUSTEREDSALARYIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYGOALTERTABLEDBOTBLSALARYWITHCHECKADDCONSTRAINTFK_TBLSALARY_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLSALARYCHECKCONSTRAINTFK_TBLSALARY_TBLEMPLOYEEGO表定义表TBLSALARY定义如下名称类型可否为空说明备注SALARYIDINT4否工资编号EMPLOYEEIDINT4否员工编号SALARYTIMEDATETIME8否工资发放时间BASICSALARYINT4可员工基本工资OVERTIMESALARYINT4可加班工资ABSENSESALARYINT4可缺勤扣除OTHERSALARYINT4可其他工资主键表TBLSALARY的主键是SALARYID字段，类型为INT，设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLSALARYSCRIPTDATE06/18/2011090347/ALTERTABLEDBOTBLSALARYADDCONSTRAINTPK_TBLSALARYPRIMARYKEYCLUSTEREDSALARYIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键表TBLSALARY的外键是EMPLOYEEID，类型为INT，用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联。答案USERGBGOALTERTABLEDBOTBLSALARYWITHCHECKADDCONSTRAINTFK_TBLSALARY_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLSALARYCHECKCONSTRAINTFK_TBLSALARY_TBLEMPLOYEEGO约束无其他约束索引主键字段SALARYID具有自动创建的聚集索引。35表TBLATTENDANCE概述表TBLATTENDANCE用于记录员工的考勤信息（上下班时间、记录者信息等），通过字段EMPLOYEEID与表TBLEMPLOYEE关联。答案USERGBGO/OBJECTTABLEDBOTBLATTENDANCESCRIPTDATE06/20/2011171144/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEDBOTBLATTENDANCEATTENDIDINTNOTNULL,EMPLOYEEIDINTNOTNULL,BREACHTIMEDATETIMENULL,RECORDERIDINTNULL,TYPENCHAR4NOTNULL,ASKREVIEWTINYINTNULL,DATEDATETIMENOTNULL,CONSTRAINTPK_TBLATTENDANCEPRIMARYKEYCLUSTEREDATTENDIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYGOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEEGOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEE2FOREIGNKEYRECORDERIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEE2GOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTCK_TBLATTENDANCECHECKRECORDERIDEMPLOYEEIDANDTYPE缺勤ORTYPE迟到ORTYPE早退GOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTCK_TBLATTENDANCEGO表定义表TBLATTENDANCE定义如下名称类型可否为空说明备注ATTENDIDINT4否考勤编号EMPLOYEEIDINT4否员工编号BREACHTIMEDATETIME8可到达时间RECORDERIDINT4可记录者编号不可与EMPLOYEEID相同TYPENCHAR4否考勤类型可取值“缺勤”，“迟到”，“早退”ASKREVIEWTINYINT1可请求重新审核可取值0,1DATEDATETIME8否日期主键表TBLATTENDANCE的主键是ATTENDID字段，类型为INT，设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLATTENDANCESCRIPTDATE06/18/2011090451/ALTERTABLEDBOTBLATTENDANCEADDCONSTRAINTPK_TBLATTENDANCEPRIMARYKEYCLUSTEREDATTENDIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键表TBLATTENDANCE的外键是EMPLOYEEID和RECORDERID，其中EMPLOYEEID类型为INT，用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联；RECORDERID类型为INT,用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联。答案1USERGBGOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEEGO2USERGBGOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEE2FOREIGNKEYRECORDERIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTFK_TBLATTENDANCE_TBLEMPLOYEE2GO约束表TBLATTENDANCE中的RECORDERID不可与EMPLOYEEID相同；表TBLATTENDANCE中的TYPE字段只能取“缺勤”、“迟到”、“早退”。表TBLATTENDANCE中的ASKREVIEW字段默认值为0，即未请求重新审核答案USERGBGOALTERTABLEDBOTBLATTENDANCEWITHCHECKADDCONSTRAINTCK_TBLATTENDANCECHECKRECORDERIDEMPLOYEEIDANDTYPE缺勤ORTYPE迟到ORTYPE早退GOALTERTABLEDBOTBLATTENDANCECHECKCONSTRAINTCK_TBLATTENDANCEGO索引主键字段ATTENDID具有自动创建的聚集索引。36表TBLLEAVE概述表TBLLEAVE用于记录员工的请假记录。表TBLLEAVE中的基本信息包括请假提交时间、请假开始时间、请假终止时间、请假原因、请假的类别和请假申请状态等。通过字段EMPLOYEEID与表TBLEMPLOYEE关联。答案USERGBGO/OBJECTTABLEDBOTBLLEAVESCRIPTDATE06/20/2011171423/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEDBOTBLLEAVELEAVEIDINTNOTNULL,EMPLOYEEIDINTNOTNULL,SUBMITTIMEDATETIMENOTNULL,STARTTIMEDATETIMENOTNULL,ENDTIMEDATETIMENOTNULL,REASONNVARCHAR100NULL,TYPEIDINTNULL,HOURSFLOATNOTNULL,STATUSNVARCHAR20NULL,APPROVERIDINTNULL,DENYREASONNCHAR100NULL,CONSTRAINTPK_TBLLEAVEPRIMARYKEYCLUSTEREDLEAVEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYGOALTERTABLEDBOTBLLEAVEWITHCHECKADDCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLLEAVECHECKCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEEGOALTERTABLEDBOTBLLEAVEWITHCHECKADDCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEE1FOREIGNKEYAPPROVERIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLLEAVECHECKCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEE1GO表定义表TBLLEAVE定义如下名称类型可否为空说明备注LEAVEIDINT否请假申请编号EMPLOYEEIDINT否员工编号SUBMITTIMEDATETIME否提交时间STARTTIMEDATETIME否开始时间ENDTIMEDATETIME否结束时间大于开始时间REASONNVARCHAR100可理由说明TYPEIDINT可类型编号HOURSFLOAT否小时数STATUSNVARCHAR20可申请状态取值范围可以是“已提交”、“已取消”、“已批准”、“已否决”APPROVERIDINT4可审核者编号DENYREASONNCHAR100可拒绝申请的理由主键表TBLLEAVE的主键是LEAVEID字段，类型为INT，设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLLEAVESCRIPTDATE06/18/2011090635/ALTERTABLEDBOTBLLEAVEADDCONSTRAINTPK_TBLLEAVEPRIMARYKEYCLUSTEREDLEAVEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键表TBLLEAVE的外键是EMPLOYEEID和APPROVERID，其中EMPLOYEEID类型为INT，用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联；APPROVERID类型为INT，用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联。答案1USERGBGOALTERTABLEDBOTBLLEAVEWITHCHECKADDCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLLEAVECHECKCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEEGO2USERGBGOALTERTABLEDBOTBLLEAVEWITHCHECKADDCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEE1FOREIGNKEYAPPROVERIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLLEAVECHECKCONSTRAINTFK_TBLLEAVE_TBLEMPLOYEE1GO约束请假开始时间应小于请假结束时间；STATUS字段的取值可以是“已提交”、“已取消”、“已批准”、“已否决”。索引主键字段LEAVEID具有自动创建的聚集索引。37表TBLOVERTIME概述表TBLOVERTIME用于记录员工的加班记录。表TBLOVERTIME中的基本信息包括加班时间、加班起止时间、加班原因和加班申请批准状态。通过字段EMPLOYEEID与表TBLEMPLOYEE关联。答案USERGBGO/OBJECTTABLEDBOTBLOVERTIMESCRIPTDATE06/20/2011171453/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEDBOTBLOVERTIMEOVERTIMEIDINTNOTNULL,EMPLOYEEIDINTNOTNULL,APPROVERINTNULL,SUBMITTIMEDATETIMENOTNULL,STARTTIMEDATETIMENOTNULL,ENDTIMEDATETIMENOTNULL,REASONNVARCHAR100NOTNULL,STATUSNCHAR10NOTNULL,TYPETINYINTNULL,DENYREASONSNCHAR100NULL,HOURSINTNULL,CONSTRAINTPK_TBLOVERTIMEIDPRIMARYKEYCLUSTEREDOVERTIMEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,IGNORE_DUP_KEYOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYONPRIMARYGOALTERTABLEDBOTBLOVERTIMEWITHCHECKADDCONSTRAINTFK_TBLOVERTIME_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLOVERTIMECHECKCONSTRAINTFK_TBLOVERTIME_TBLEMPLOYEEGOALTERTABLEDBOTBLOVERTIMEWITHCHECKADDCONSTRAINTFK_TBLOVERTIME_TBLOVERTIMETYPEFOREIGNKEYTYPEREFERENCESDBOTBLOVERTIMETYPETYPEGOALTERTABLEDBOTBLOVERTIMECHECKCONSTRAINTFK_TBLOVERTIME_TBLOVERTIMETYPEGO表定义表TBLOVERTIME定义如下名称类型可否为空说明备注OVERTIMEIDINT否加班申请编号EMPLOYEEIDINT否员工编号APPROVERIDINT可批准者编号SUBMITTIMEDATETIME否提交时间STARTTIMEDATETIME否开始时间ENDTIMEDATETIME否结束时间REASONNVARCHAR100否加班理由STATUSNCHAR10否申请状态取值可以为“已取消”、“已批准”、“已否决”、“已提交”TYPETINYINT1可加班类型DENYREASONNCHAR100可拒绝申请的理由HOURSINT4可小时数主键表TBLOVERTIME的主键是OVERTIMEID字段，类型为INT，不设置自动增量。答案USERGBGO/OBJECTINDEXPK_TBLOVERTIMEIDSCRIPTDATE06/18/2011090804/ALTERTABLEDBOTBLOVERTIMEADDCONSTRAINTPK_TBLOVERTIMEIDPRIMARYKEYCLUSTEREDOVERTIMEIDASCWITHPAD_INDEXOFF,STATISTICS_NORECOMPUTEOFF,SORT_IN_TEMPDBOFF,IGNORE_DUP_KEYOFF,ONLINEOFF,ALLOW_ROW_LOCKSON,ALLOW_PAGE_LOCKSONONPRIMARYGO外键表TBLOVERTIME的外键是EMPLOYEEID，类型为INT，用于与表TBLEMPLOYEE中的EMPLOYEEID字段关联。表TBLOVERTIME的外键是TYPE，类型为TINYINT，用来与表TBLOVERTIMETYPE的TYPE相关联。答案1USERGBGOALTERTABLEDBOTBLOVERTIMEWITHCHECKADDCONSTRAINTFK_TBLOVERTIME_TBLEMPLOYEEFOREIGNKEYEMPLOYEEIDREFERENCESDBOTBLEMPLOYEEEMPLOYEEIDGOALTERTABLEDBOTBLOVERTIMECHECKCONSTRAINTFK_TBLOVERTIME_TBLEMPLOYEEGO2USERGBGOALTERTABLEDBOTBLOVERTIMEWITHCHECKADDCONSTRAINTFK_TBLOVERTIME_TBLOVERTIMETYPEFOREIGNKEYTYPEREFERENCESDBOTBLOVERTIMETYPETYPEGOALTERTABLEDBOTBLOVERTIMECHECKCONSTRAINTFK_TBLOVERTIME_TBLOVERTIMETYPEGO约束加班开始时间应小于加班结束时间。STATUS字段取值“已取消”、“已批准”、“已否决”、“已提交”索引主键字段OVERTIMEID具有自动创建的聚集索引。38表TBLPERFORMANCE概述表TBLPERFORMANCE用于保存员工的绩效信息。表TBLPERFORMANCE中的基本信息包括员工编号、考评者编号、提交时间、考评时间、评语和绩效状态。通过字段EMPLOYEEID与表TBLEMPLOYEE关联。答案USERGBGO/OBJECTTABLEDBOTBLPERFORMANCESCRIPTDATE06/20/2011171521/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEDBOTBLPERFORMANCEPERFORMIDINTNOTNULL,EMPLOYEEIDINTNOTNULL,REVIEWERIDINTNULL,SUBMITTIMEDATETIMENOTNULL,PERFORMYEARINTNOTNULL,PERFORMSEASONTINYINTNOTNULL,STATUSTINYINTNULL,LASTEDITTIMEDATETIMENULL,SELFSORCETINYINTNULL,REVIEWSCORETINYINTNULL,SELFCOMMENTNCHAR200NULL,REVIEWCOMMENTNCHAR200NULL,CONSTRAINTPK_TBLPERFORMANCEPRIMARYKEYCLUSTER