1、人事管理系统项目详细设计说明书1. 概述1.1. 目的本文为教学案例项目SQLServer功能规范说明书。本说明书将: 描述数据库设计的目的 说明数据库设计中的主要组成部分 说明数据库设计中涵盖的教学知识要点1.2. 内容本文档主要内容包括对数据库设计结构的总体描述,对数据库中各种对象的描述(包括对象的名称、对象的属性、对象和其他对象的直接关系)。本文档中包含对以下数据库内容的描述: 数据表 视图 存储过程 用户自定义函数 触发器 约束在数据库主要对象之外,本文还将描述数据库安全性设置、数据库属性设置和数据库备份策略,为数据库管理员维护数据库安全稳定地运行提供参考。1.3. 与其他项目的关联教
2、学案例项目的数据库设计与教学项目(Web部分和Windows部分)功能密切相关。教学案例项目的数据库将按照教学项目程序部分的功能需求而设计,数据库设计将配合教学案例的程序部分,以实现一个功能完备的企业环境内的应用。提示 可通过使用Web应用程序或Windows应用程序来测试数据库。2. 数据库2.1. 数据库名称数据库的名称一定要设为RGB,否则本案例设计的Web部分的应用程序和Windows部分的应用程序将无法使用该数据库。答案:USEmasterGO/*Object:DatabaseRGBScriptDate:06/18/201108:55:58*/CREATEDATABASERGBONP
3、RIMARY(NAME=NRGB,FILENAME=NC:ProgramFilesMicrosoftSQLServerMSSQLDATA,SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=NRGB_log,FILENAME=NC:ProgramFilesMicrosoftSQLServerMSSQLDATA,SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)GOALTERDATABASERGBSETCOMPATIBILITY_LEVEL=100GOIF(1=FULLTEXTSERVICEPRO
4、PERTY(IsFullTextInstalled)beginEXECRGB.dbo.sp_fulltext_databaseaction=enableendGOALTERDATABASERGBSETANSI_NULL_DEFAULTOFFGOALTERDATABASERGBSETANSI_NULLSOFFGOALTERDATABASERGBSETANSI_PADDINGOFFGOALTERDATABASERGBSETANSI_WARNINGSOFFGOALTERDATABASERGBSETARITHABORTOFFGOALTERDATABASERGBSETAUTO_CLOSEOFFGOALT
5、ERDATABASERGBSETAUTO_CREATE_STATISTICSONGOALTERDATABASERGBSETAUTO_SHRINKOFFGOALTERDATABASERGBSETAUTO_UPDATE_STATISTICSONGOALTERDATABASERGBSETCURSOR_CLOSE_ON_COMMITOFFGOALTERDATABASERGBSETCURSOR_DEFAULTGLOBALGOALTERDATABASERGBSETCONCAT_NULL_YIELDS_NULLOFFGOALTERDATABASERGBSETNUMERIC_ROUNDABORTOFFGOAL
6、TERDATABASERGBSETQUOTED_IDENTIFIEROFFGOALTERDATABASERGBSETRECURSIVE_TRIGGERSOFFGOALTERDATABASERGBSETDISABLE_BROKERGOALTERDATABASERGBSETAUTO_UPDATE_STATISTICS_ASYNCOFFGOALTERDATABASERGBSETDATE_CORRELATION_OPTIMIZATIONOFFGOALTERDATABASERGBSETTRUSTWORTHYOFFGOALTERDATABASERGBSETALLOW_SNAPSHOT_ISOLATIONO
7、FFGOALTERDATABASERGBSETPARAMETERIZATIONSIMPLEGOALTERDATABASERGBSETREAD_COMMITTED_SNAPSHOTOFFGOALTERDATABASERGBSETHONOR_BROKER_PRIORITYOFFGOALTERDATABASERGBSETREAD_WRITEGOALTERDATABASERGBSETRECOVERYFULLGOALTERDATABASERGBSETMULTI_USERGOALTERDATABASERGBSETPAGE_VERIFYCHECKSUMGOALTERDATABASERGBSETDB_CHAI
8、NINGOFFGO2.2. 用户除了数据库中自动创建的dbo用户之外,还要创建如下两个用户: RGBWinUser:该用户是Windows应用程序访问数据库所使用的账号,它的权限一般仅限于查看视图和执行存储过程。 RGBASPUser:该用户是Web应用程序访问数据库所使用的账号,它的权限和RGBWinUser用户类似,即仅限于查看视图和执行存储过程。答案:RGBGO/*Object:UserRGBWinUserScriptDate:06/18/201108:29:15*/GOCREATEUSERRGBWinUserFORLOGINWOWITHDEFAULT_SCHEMA=dboGORGBGO
9、/*Object:UserRGBASPUserScriptDate:06/18/201108:30:46*/GOCREATEUSERRGBASPUserFORLOGINniWITHDEFAULT_SCHEMA=dbo GO3. 表3.1. 表设计概述根据教学案例功能,数据库将以员工信息为中心存储相关数据,配合SQLServer数据库系统中提供的数据管理,实现员工考勤、请假、加班管理及系统设置等业务功能。数据库设计将以存储员工信息的员工表为基础,连接多张相关表实现对以下关系的支持: 员工与请假申请 员工与加班申请 员工与考勤记录 员工与部门 员工与部门经理 员工与绩效考核记录 员工与工资此外数据
10、库中还将记录教学案例应用中需要的全局配置信息和事件日志记录。数据库系统主要的实体关系如下图:(部门表中去掉部门经理编号,在数据删除时可能会出现死锁)3.2. 表tblEmployee概述表tblEmployee用于记录员工基本信息,并作为基础表与其他表联接。该表通过DeptID和Title可以确定员工部门和职位信息。当Title的值为“经理”时可以确定此员工为该部门的部门经理。答案:USERGBGO/*Object:Tabledbo.tblEmployeeScriptDate:06/20/201117:13:37*/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERON
11、GOSETANSI_PADDINGONGOCREATETABLEdbo.tblEmployee( EmployeeIDintNOTNULL, Namenvarchar(50)NOTNULL, LoginNamenvarchar(20)NOTNULL, Passwordbinary(20)NULL, Emailnvarchar(50)NOTNULL, DeptIDintNULL, BasicSalaryintNULL, Titlenvarchar(50)NULL, Telephonenvarchar(50)NULL, OnhoardDatedatetimeNOTNULL, Selfintronv
12、archar(200)NULL, VacationRemainintNULL, EmployeeLevelintNULL, PhotoImageimageNULL,CONSTRAINTPK_tblEmployeePRIMARYKEYCLUSTERED( EmployeeIDASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY)ONPRIMARYTEXTIMAGE_ONPRIMARYGOSETANSI_PADDING
13、OFFGOALTERTABLEdbo.tblEmployeeWITHCHECKADDCONSTRAINTFK_tblEmployee_tblDepartmentFOREIGNKEY(DeptID)REFERENCESdbo.tblDepartment(DeptID)GOALTERTABLEdbo.tblEmployeeCHECKCONSTRAINTFK_tblEmployee_tblDepartmentGOALTERTABLEdbo.tblEmployeeWITHCHECKADDCONSTRAINTFK_tblEmployee_tblEmployeeLevelFOREIGNKEY(Employ
14、eeLevel)REFERENCESdbo.tblEmployeeLevel(EmployeeLevel)GOALTERTABLEdbo.tblEmployeeCHECKCONSTRAINTFK_tblEmployee_tblEmployeeLevelGO表定义表tblEmployee定义如下:名称类型可否为空说明备注EmployeeIDint4否员工编号自动生成Namenvarchar50否员工姓名LoginNamenvarchar20否员工登录名建议为英文字符,且与姓名不同Passwordbinary20可员工登录密码Emailnvarchar50否员工电子邮件DeptIDint4可员工所
15、属部门编号BasicSalaryint4可员工基本工资Titlenvarchar50可员工职位名称可取值为“普通员工”、“部门经理”、“总裁”Telephonenvarchar50可员工电话OnboardDatedatetime8否员工报到日期SelfIntronvarchar200可员工自我介绍初始为空,由员工自行输入VacationRemainint4可员工剩余假期小时数EmployeeLevelint4可员工的级别PhotoImageimage16可员工照片主键表tblEmployee的主键是EmployeeID字段,类型为int,设置自动增量。答案:USERGBGO/*Object:I
16、ndexPK_tblEmployeeScriptDate:06/18/201109:00:53*/ALTERTABLEdbo.tblEmployeeADDCONSTRAINTPK_tblEmployeePRIMARYKEYCLUSTERED( EmployeeIDASC)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)ONPRIMARYGO外键表tblEmployee的外键有D
17、eptID,类型为int,用于与表tblDepartment中的DeptID字段关联。DeptID字段可以为空,在此情况下表示员工不在任何部门中。表tblEmployee的外键有EmployeeLevel,类型为int,用于与表tblEmployeeLevel中的EmployeeLevel字段关联。RGBGOALTERTABLEdbo.tblEmployeeWITHCHECKADDCONSTRAINTFK_tblEmployee_tblDepartmentFOREIGNKEY(DeptID)REFERENCESdbo.tblDepartment(DeptID)GOALTERTABLEdbo.t
18、blEmployeeCHECKCONSTRAINTFK_tblEmployee_tblDepartmentGORGBGOALTERTABLEdbo.tblEmployeeWITHCHECKADDCONSTRAINTFK_tblEmployee_tblEmployeeLevelFOREIGNKEY(EmployeeLevel)REFERENCESdbo.tblEmployeeLevel(EmployeeLevel)GOALTERTABLEdbo.tblEmployeeCHECKCONSTRAINTFK_tblEmployee_tblEmployeeLevelGO约束表tblEmployee中的L
19、oginName字段建议为4-8位小写英文字符,且不能与员工姓名相同也不可以为空字符串。索引主键字段EmployeeID具有自动创建的聚集索引。3.3. 表tblDepartment概述表tblDepartment用于记录企业内部的部门信息。每个独立的部门在该表中都对应一条记录。该表通过与tblEmployee表关联可以确定员工所属的部门。答案:USERGBGO/*Object:Tabledbo.tblDepartmentScriptDate:06/20/201117:13:12*/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOSETANSI_PADDING
20、ONGOCREATETABLEdbo.tblDepartment( DeptIDintNOTNULL, DeptNamechar(10)NULL, Descriptionchar(50)NULL,CONSTRAINTPK_tblDepartmentPRIMARYKEYCLUSTERED( DeptIDASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY)ONPRIMARYGOSETANSI_PADDINGOFFGO
21、表定义表tblDepartment定义如下:名称类型可否为空说明备注DeptIDint4否部门编号自动生成DeptNamechar10可部门名称Desciptionchar50可部门描述主键表tblDepartment的主键是DeptID字段,类型为int,设置自动增量。答案:USERGBGO/*Object:IndexPK_tblDepartmentScriptDate:06/18/201109:03:12*/ALTERTABLEdbo.tblDepartmentADDCONSTRAINTPK_tblDepartmentPRIMARYKEYCLUSTERED( DeptIDASC)WITH(
22、PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,SORT_IN_TEMPDB=OFF,IGNORE_DUP_KEY=OFF,ONLINE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARYGO外键无约束无其他约束索引主键字段DeptID具有自动创建的聚集索引。3.4. 表tblSalary概述表tblSalary用于记录员工每月的工资信息,包括工资发放日期、工资组成等。表tblSalary通过字段EmployeeID与表tblEmployee关联。答案:USERGBGO/*Object:Tabledbo.t
23、blSalaryScriptDate:06/20/201117:12:47*/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEdbo.tblSalary( SalaryIDintNOTNULL, EmployeeIDintNOTNULL, SalaryTimedatetimeNOTNULL, BasicLSalaryintNULL, OvertimeSalaryintNULL, AbsenseSalaryintNULL, OtherSalaryintNULL,CONSTRAINTPK_tblSalaryPRIMARYKEYCLUSTERE
24、D( SalaryIDASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ONPRIMARY)ONPRIMARYGOALTERTABLEdbo.tblSalaryWITHCHECKADDCONSTRAINTFK_tblSalary_tblEmployeeFOREIGNKEY(EmployeeID)REFERENCESdbo.tblEmployee(EmployeeID)GOALTERTABLEdbo.tblSalaryCHECKCO
25、NSTRAINTFK_tblSalary_tblEmployeeGO表定义表tblSalary定义如下:名称类型可否为空说明备注SalaryIDint4否工资编号EmployeeIDint4否员工编号SalaryTimedatetime8否工资发放时间BasicSalaryint4可员工基本工资OvertimeSalaryint4可加班工资AbsenseSalaryint4可缺勤扣除OtherSalaryint4可其他工资主键表tblSalary的主键是SalaryID字段,类型为int,设置自动增量。答案:USERGBGO/*Object:IndexPK_tblSalaryScriptDat
26、e:06/18/201109:03:47*/ALTERTABLEdbo.tblSalaryADDCONSTRAINTPK_tblSalaryPRIMARYKEYCLUSTERED( SalaryIDASC)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)ONPRIMARYGO外键表tblSalary的外键是EmployeeID,类型为int,用于与表tblEmployee中的E
27、mployeeID字段关联。答案:USERGBGOALTERTABLEdbo.tblSalaryWITHCHECKADDCONSTRAINTFK_tblSalary_tblEmployeeFOREIGNKEY(EmployeeID)REFERENCESdbo.tblEmployee(EmployeeID)GOALTERTABLEdbo.tblSalaryCHECKCONSTRAINTFK_tblSalary_tblEmployeeGO约束无其他约束索引主键字段SalaryID具有自动创建的聚集索引。3.5. 表tblAttendance概述表tblAttendance用于记录员工的考勤信息(上
28、下班时间、记录者信息等),通过字段EmployeeID与表tblEmployee关联。答案:USERGBGO/*Object:Tabledbo.tblAttendanceScriptDate:06/20/201117:11:44*/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLEdbo.tblAttendance( AttendIDintNOTNULL, EmployeeIDintNOTNULL, BreachTimedatetimeNULL, RecorderIDintNULL, Typenchar(4)NOTNULL, AskReviewtinyintNULL, DatedatetimeNOTNULL,CONSTRAINTPK_tblAttendancePRIMARYKEYCLUSTE
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2