人事管理系统.doc

_人事管理系统1.简要的系统需求分析 人事管理系统是非常通用的管理信息系统，是企事业单位实现办公自动化的前提和基础。一个公司要正常运作必定有一个健全的人事管理系统，来管理员工的信息，包括员工编号、姓名、性别、民族工资及奖惩考勤情况。除此之外，系统还供员工查询个人和公共信息，管理员对员工信息进行更改或删除，管理公司的人事变更和管理，大大提高公司内部员工管理的工作效率。1.1业务分析（1）部门管理：对公司的部门进行编号，将每一个部门的部门号、部门名称、部门描述和部门负责人信息统一保存在部门信息档案中。当新增或去除某个部门时，将其信息添加到部门信息档案或或从中删除。当部门信息发生变化时，修改对应和相关的属性值。（2）员工信息管理：对公司里的所有员工进行统一编号；将每一位员工的信息录入到员工信息档案中。对新聘用的员工，将其信息加入到员工信息档案中；对于解雇的员工，将其信息从信息档案中删除。当员工的信息发生变动时，修改员工信息档案中相应的属性。同时供员工自己查询使用。其下还包含有个人家庭关系和个人呢工作学习的经历，各放在一张表格中。（3）员工职务调动管理：同样对公司员工的职务进行统一编号；登记员工调动前后的具体职务，以及调动时间。（4）奖惩管理：根据每个员工的编号登记奖惩原因和奖惩金额及奖惩月份。（5）考勤管理：根据员工编号，登记员工每个月的全勤，出差，请假时日和加班迟到早退信息。（6）工资管理：对所有员工的每笔工资进行编号，记录工资记录表。1.2功能需求分析（1）一般用户可以查询个人信息和修改自己的登录密码；（2）管理员可以查询所有员工的信息并可以做添加、修改、删除的操作。2.概念设计ER模型设计部门部门编号部门名称部门职能描述部门负责人编号用户管理用户名密码用户类型员工员工编号姓名籍贯婚姻状况文化程度政治面貌性别民族身份证号生日考勤考勤编号考勤月份法定休假天数旷工天数员工编号全勤天数出差天数病假天数事假天数员工调动调动编号调动日期员工编号调动前职务调动后职务批复部门编号奖惩员工编号奖惩编号处罚金额奖励事由奖励金额处罚事由家庭关系家庭编号工作单位与本人关系员工编号年龄性别姓名工作经历职务学校/单位名称终止日期开始日期员工编号编号工资工资编号处罚金额奖励金额员工编号基本工资考勤月份管理员员工属于调动查询部门维护工作经历家庭关系附有附有员工调动工资考勤查询查询修改查询修改管理员查询修改查询修改查询修改维护3.逻辑结构设计将ER模型转化为关系模型（1）部门（部门编号+部门名称+部门简介+部门负责人）；PS：部门编号为主码（2）员工信息（员工编号+姓名+性别+民族+生日+政治面貌+文化程度+婚姻状况+籍贯+身份证号+工作证号+办公电话+手机号码+部门编号+职务+状态+工资+备注）Ps：员工编号为主码，部门编号为外码（3）职务调动信息（调动编号+员工编号+调动前职务+调动后职务+调动日期+批复部门号）PS：调动编号为主码，员工编号为外码（4）奖惩信息（奖惩编号+员工编号+奖励事由+奖励金额+处罚事由+处罚金额）PS：奖惩编号为主码+员工编号为外码（5）考勤信息（考勤月份+员工编号+全勤天数+出差天数+病假天数+事假天数+旷工天数+迟到时间+早退时间+）PS:考勤月份为主码，员工编号为外码（6）工资信息（工资编号+员工编号+基本工资+奖励金额+处罚金额+考勤月份）PS：工资编号为主码，员工编号和考勤月份为外码（7）管理员信息（用户名+密码+用户类型）PS：用户名为主码；4.数据库的物理设计（1）具体表的结构设计部门：编号字段名称数据结构约束条件1部门编号intNot nullprimary key2部门名称varchar(40)Not null3部门职能描述Varchar(400)null4部门负责人编号Char(3)Not null员工：编号字段名称数据结构约束条件1员工编号Char(4)Not null primary key2姓名Varchar(50)Not null3性别Char(2)null4民族varchar(40)null5生日datetimenull6政治面貌varchar(40)null7文化程度varchar(40)null8婚姻状况varchar(20)null9籍贯varchar(60)null10身份证号varchar(20)null11办公电话varchar(30)null12手机号码varchar(30)null13部门编号intnull14职务varchar(20)null15员工状态（1-在职，2-兼职，3，试用，4-离职，5-退休）tinyintnull员工调动：编号字段名称数据结构约束条件1调动编号Char(2)Not null primary key2员工编号Char(4)Not null3调动前职务Varchar(20)Null4调动后职务Varchar(20)null5调动日期DatetimeNull6批复部门编号intNot null奖惩：编号字段名称数据结构约束条件1奖惩编号Char(2)Not null primary key2员工编号Char(4)Not null3奖励事由Varchar(200)Null4奖励金额smallintNull5处罚事由Varchar(200)Null6处罚金额smallintnull考勤：编号字段名称数据结构约束条件1考勤编号Char(3)Not null primary key2考勤月份DatetimeNot null3员工编号intNot null4全勤天数Decimal(4,1)Null5出差天数Decimal(4,1)Null6病假天数Decimal(4,1)Null7事假天数Decimal(4,1)Null8旷工天数Decimal(4,1)Null9法定休假天数Decimal(4,1)Null10迟到时间（分）TinyintNull11早退时间(分)TinyintNull12一类加班天数（法定节假日加班）IntNull13二类加班天数（周六或周日加班）IntNull14三类加班天数（日常加班）IntNull工资：编号字段名称数据结构约束条件1工资编号Char(3)Not nullprimary key2员工编号Char(4)Not null3基本工资moneyNull4考勤月份DatetimeNot null5奖励金额moneyNull6处罚金额moneynull用户管理：编号字段名称数据结构约束条件1用户名Varchar(40)Not nullprimary key2密码Varchar(40)Not null3用户类型（1-系统管理员，2-普通用户tinyintNot null家庭关系：编号说明数据结构约束条件1编号tinyintNot nullprimary key2员工编号intNot null3姓名Varchar(50)Null4性别Char(2)Null5年龄tinyintNull6与本人关系Varchar(20)Null7工作单位Varchar(40)null工作经历：编号说明数据结构约束条件1编号tinyintNot nullprimary key2员工编号intNot null3开始日期Char(10)null4终止日期Char(10)null5学校/单位名称Varchar(50)null6职务Varchar(20)null5.T-SQL命令方式创建数据库文件、表、索引、联系和主键、外键等约束（1）创建数据库create database HrSysALTER DATABASE HrSySADD FILE(NAME=HrSys_dat2,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDataHrSys_dat2.ndfSIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)LOG ON ( NAME=HrSys_LOG, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLDataHrSys_log.ldf, SIZE=3, MAXSIZE=20, FILEGROWTH=1 )（2）创建表精品资料use HrSyscreate table 部门(部门编号int primary key,部门名称varchar(40) not null,部门职能描述varchar(400),部门负责人编号char(4)not null,)create table 员工(员工编号char(4) primary key,姓名varchar(50) not null,性别char(2),民族varchar(40),生日varchar(20),政治面貌varchar(40),文化程度varchar(40),婚姻状况varchar(20),籍贯varchar(60),身份证号varchar(20),办公电话varchar(30),手机号码varchar(40), 部门编号int not null,职务varchar(20),状态tinyint,工资decimal(9,2)create table 考勤(考勤编号 int primary key,考勤月份datetime not null,员工编号char(4) not null,全勤天数Decimal(4,1),出差天数Decimal(4,1),病假天数Decimal(4,1),事假天数Decimal(4,1),旷工天数Decimal(4,1),法定休假日Decimal(4,1),迟到时间Tinyint,早退时间Tinyint,法定假日加班Decimal(4,1),周末加班Decimal(4,1),日常加班Decimal(4,1),)create table 员工调动(调动编号char(2) primary key,员工编号char(4) not null,调动前职务varchar(20),调动后职务varchar(20),调动日期char(10),批复部门编号int)create table 工资(工资编号char(3) primary key,员工编号char(4) not null,基本工资money,考勤月份datetime not null,奖励金额money,处罚金额money,)alter table 工资add 总共资as 基本工资+奖励金额-处罚金额create table 用户管理(用户名varchar(40)PRIMARY KEY,密码 varchar(40) NOT NULL,用户类型tinyint NOT NULL)insert into Users values(Admin,8888,1)create table 奖惩(奖惩编号 char(10)primary key,员工编号 char(4) not null,奖励事由 varchar(200),奖励金额 smallint,处罚事由 varchar(200),处罚金额 smallint,)reate table 家庭关系(家庭编号 char(2) primary key, 员工编号 char(4) not null,姓名 varchar(50) not null,性别 char(2),年龄 tinyint,与本人关系 varchar(20),工作单位 varchar(40)create table 工作经历(编号tinyint primary key,员工编号 char(4) not null,开始日期Char(10),终止日期Char(10),学校/单位名称Varchar(50),职务Varchar(20)（3）创建索引（4）修改表来创建外键关系和约束6.利用T-SQL命令增加、删除、修改数据（1）向表中增加属性列（2）修改、添加和删除数据例如：更新工资表的结果为7.利用T-SQL命令进行数据的检索和统计（1）投影和选择（2）多表连接选择在技术部或企划部的员工姓名和所在部门名称选择总共资在4000元以上的员工编号，姓名，职务，工资和考勤月份信息选择有信息存在员工表，家庭关系表，工资表的员工的编号、姓名、籍贯、家庭关系、与本人关系、基本工资信息（3）分组统计与HAVING统计每个部门的男女人数select 部门编号,性别,count(*) as 人数from 员工group by 部门编号,性别选择基本工资在2500以上的员工SELECT 员工编号,avg(基本工资)FROM 工资group by 员工编号having avg(基本工资)2500（4）子查询选择没有信息在家庭关系表中的员工信息SELECT * FROM 员工 WHERE not EXISTS (SELECT * FROM 家庭关系 WHERE 家庭关系.员工编号=员工.员工编号)select 员工编号,姓名,生日,籍贯,身份证号,职务,办公电话from 员工where EXISTS(SELECT * FROM 工作经历 WHERE 员工.员工编号=工作经历.员工编号)（5）查询结果保存select 姓名,职务,办公电话,手机号码,调动前职务into B_INfofrom 员工,员工调动where 员工.员工编号=员工调动.员工编号select *from B_INfo8.利用SQL命令创建数据库视图（1）基于单表的建立视图员工常用信息CREATE VIEW 员工常用信息(员工编号,姓名,职位,办公电话,手机号码,部门编号)AS SELECT 员工编号,姓名,职务,办公电话,手机号码,部门编号FROM dbo.员工sp_helptext 员工常用信息（2）包含多表连接的建立视图员工考勤信息，员工基本信息和2012年考勤情况CREATE VIEW 员工考勤信息(员工姓名,职位,办公电话,旷工天数,事假天数,病假天数)AS SELECT 姓名,职务,办公电话,旷工天数,事假天数,病假天数FROM dbo.员工, dbo.考勤WHERE (datename(yy,考勤月份)=2012)sp_helptext 员工考勤信息（3）包含统计运算的建立视图view1，查询所有职工的职工号、姓名、部门名和2011年份工资，并按部门名顺序排列；create view view1asselect top 50 员工.员工编号,员工.姓名,部门.部门名称,工资.总工资 from 员工,部门,工资 where year(考勤月份)=2011 order by 部门名称 sp_helptext view1建立视图view2,查询所有员工的员工编号、姓名和平均工资；create view view2 as select 员工.员工编号,员工.姓名,avg(基本工资)as 平均工资from 员工,工资where 员工.员工编号=工资.员工编号GROUP BY 员工.员工编号,员工.姓名sp_helptext view2（4）利用SQL对视图进行查询9.对视图进行插入、删除、修改数据操作插入：删除：修改：10.编写一个自定义函数，将一个字符串作为自变量，返回颠倒顺序的字符串（2）带参数的函数，通过员工编号返回员工信息create function 员工情况(个人编号varchar(4)returns tableasreturn(select 姓名,职务,办公电话from 员工where 员工编号=个人编号)select * from 员工情况(4002)11. 编写利用游标进行数据处理的存储过程。并在存储过程中应用事务的概念。自己确定过程的具体要求，然后编写出来。（1）利用游标读取员工表中的每一行数据：declare cur_员工cursorglobal scroll dynamicforselect 员工编号,姓名,职务,文化程度,籍贯,身份证号,手机号码from 员工open cur_员工fetch next from cur_员工while fetch_status=0beginfetch next from cur_员工end（2）创建带参数的存储过程，从表员工，家庭关系，工资的连接中返回输入的员工姓名的员工编号、姓名、籍贯、婚姻状况、基本工资、家人姓名、与自己的关系、工作单位信息。create proc yg_info 姓名varchar(20) asselect 员工.员工编号,员工.姓名,籍贯,婚姻状况,基本工资,家庭关系.姓名,与本人关系,工作单位from 员工inner join 家庭关系on 员工.员工编号=家庭关系.员工编号join 工资on 家庭关系.员工编号=工资.员工编号where 员工.姓名like 姓名exec yg_info 陈礼貌12. 编写一个实现修改表的触发器，实现完整性控制。(1)更新员工表信息use HrSysCREATE TRIGGER workerid ON 员工FOR UPDATEASIF (update(员工编号) BEGINDECLARE BHO CHAR(4),BHN CHAR(4) SELECT BHO=员工编号FROM DELETED SELECT BHN=员工编号FROM INSERTEDUPDATE 工资 SET 工资.员工编号=BHN WHERE 工资.员工编号=BHOUPDATE 考勤 SET 考勤.员工编号=BHN WHERE 考勤.员工编号=BHOUPDATE 奖惩 SET 奖惩.员工编号=BHN WHERE 奖惩.员工编号=BHO UPDATE 工作经历 SET 工作经历.员工编号=BHN wHERE 工作经历.员工编号=BHOUPDATE 员工调动 SET 员工调动.员工编号=BHN WHERE 员工调动.员工编号=BHOUPDATE 家庭关系 set 家庭关系.员工编号=BHN WHERE 家庭关系.员工编号=BHO ENDUPDATE dbo.员工SET 员工编号=1012 WHERE 员工编号=1002（2）删除员工编号CREATE TRIGGER YGDELON dbo.员工FOR DELETEAS BEGIN DECLARE BH CHAR(4) SELECT BH=员工编号FROM DELETED IF EXISTS (SELECT * FROM 部门WHERE 部门负责人编号=BH) UPDATE dbo.部门 SET 部门负责人编号=NULL WHERE 部门负责人编号=BHEnddelete from 员工where 员工编号=200113. 设置服务器身份验证模式。用命令创建若干不同验证模式的登录账户。（1）在控制面板了面创建用户（2）创建Windows身份验证和SQL Server的登录账户14. 将部分登录赋予服务器角色。然后撤消。exec sp_addsrvrolemember 四叶草,sysadminexec sp_addsrvrolemember ZY-20080929TQYO满天星,dbcreatorexec sp_helpsrvrolemember sysadminexec sp_helpsrvrolemember dbcreator撤销服务器角色成员：exec sp_dropsrvrolemember 四叶草,sysadminexec sp_dropsrvrolemember ZY-20080929TQYO满天星,dbcreator15. 将部分登录映射到你的数据库中成为用户。use HrSysgocreate user 四叶草 from login 四叶草create user ZY-20080929TQYO满天星 from login ZY-20080929TQYO满天星16. 创建自定义角色、架构。（1）创建自定义角色create role role1create role role2create role role3 authorization 四叶草（2）创建自定义