SQL-Server数据库管理-期末论文-袁瑞萍.doc

SQL Server数据库管理 课程论文（报告、案例分析）院 系 信息学院 专 业 电子商务 班 级 学生姓名 学 号 任课教师 袁瑞萍 2013 年 12 月 30 日员工管理系统数据库管理与开发一、 需求分析(1) 系统目标设计出具有员工管理、保险管理、工资管理和考勤管理等功能的系统(2) 系统功能划分及描述1、 信息输入功能（1） 输入员工的基本信息。包括：员工编号、姓名、性别、出生年月、参加工作时间、所属部门、职务、职称、政治面貌、婚姻状况等基本信息。（2） 输入员工的工资信息。包括：基本工资、岗位工资、住房补贴、津贴、工会会费、水电费、住房公积金、养老保险、奖惩。（3） 输入员工的部门信息。包括：部门编号、部门名称、部门负责人、部门人数。2、 数据修改删除功能（1） 修改和删除员工的基本信息。当单位人员的信息发生变化，如职称的变化，工作部门变动，或调离单位等，系统应能修改员工的信息或将其从员工信息表中删除。（2） 修改和删除员工的工资信息。员工升职加薪、工资普掉是企业中常见的事情，这就需要系统能方便对员工工资进行个别及批量的修改；如员工调离本单位，就应当从员工信息表中删除这个员工信息，将其工资信息表中相应的数据删除。（3） 修改和删除部门信息。当撤销一个部门时，能从部门信息表中将其删除。而当一个部门的名称发生改变时，系统中所有该项部门的名称应一致的改变。3、 查询和统计功能（1） 能够查询指定员工的基本信息。（2） 能够查询指定某个部门或全部部门的基本信息。（3） 查询某个员工的工资信息。（4） 统计、汇总指定年份企业各部门或某个部门每个月或全年工资总额，汇总各部门的人数或本单位的总人数。（5） 工资表月工资的生成功能。生成当月所有员工或某个部门的工资记录，同时能进行员工工资的计算，即计算应发金额、应扣金额及实发金额。二、数据库概念结构设计（1） 系统概念模型图一 E-R总图图二 员工基本信息图三 部门实体与考勤实体E-R图图四 津贴实体与月工资实体E-R图（2） 详细说明1、 系统涉及的实体集l 部门实体集：具有属性部门号、部门名称、部长和人数l 员工实体集：具有属性职工编号、姓名、性别、出生日期、参加工作日期、所属部门、职务、政治面貌、婚姻状况和电话号码l 出勤实体集：职工编号、月份、缺勤天数和奖惩工资l 月工资实体集：惩奖、津贴、工会会费、住房公积金、养老保险和基本工资l 津贴实体集：职工编号、月份、加班天数和津贴工资2、 系统涉及的联系l 一个部门能有多名员工，一个员工只能属于一个部门，所以部门和员工的联系是1:M的联系l 一个员工可以有多次出勤记录但一个出勤记录只对应着一个员工，所以员工和出勤的联系是1:M的联系l 一个员工可以有多中津贴但一个津贴只对应着一个员工，所以员工和津贴的联系是1:M的联系l 一个员工可以有多分工资但一分工资只对应着一个员工，所以员工和工资的联系是1:M的联系l 一个人的出勤影响着一个人的工资，一个人的工资也因一个人的出勤变动而变动，所以出勤和工资的联系时1：1l 一个人的津贴影响着一个人的工资，一个人的工资也因一个人的津贴变动而变动，所以津贴和工资的联系时1：1三、逻辑结构设计将E-R模型转换为关系模式（1） 员工实体集员工（emp_no,emp_name,sex,Department_id,Emp_time,Marry,Telephon）分别表示（员工号，员工名称，性别，员工部门，入公司时间，婚姻状况，电话号码）（2） 员工津贴实体集津贴（emp_no,Jiaban_month,jbtianshu,Jiaban_jiangjin）分别表示（员工号，加班时间，加班天数，加班工资）（三）考勤实体集考勤（emp_no,Kaoqin_month,queqin,Kaoqin_gongzi）分别表示（员工号，时间，缺勤天数，考勤工资）（四）工资实体集工资（Zw,Jibengongzi）分别表示（职位，基本工资）（五）员工月工资实体集员工月工资（emp_no,jbgz,jt,Kq,ygz_total,ygz_data）分别表示（员工号，基本工资，津贴，缺勤扣除，总月工资，月工资时间）（6） 部门实体集部门（department_id,department_name,manager,dep_renshu）分别表示（部门号，部门名称，部长，人数）四、数据库物理结构设计（一）把关系模型转化为表结构列明数据类型可否为空说明emp_novarchar(8)NOT NULL员工号（主键）emp_namevarchar(10)NULL员工名称sexchar(2)NULL性别department_idchar(4)NOT NULL员工部门emp_timevarchar2(6)NOT NULL入公司时间marryvarchar(4)NULL婚姻状况telephonchar(11)NULL电话号码表（1）员工基本情况表列明数据类型可否为空说明department_idchar(4)not null部门号department_namevarchar(6)not null部门名称managervarchar(6)null部长dep_renshuvarchar(4)null人数表（2） 部门列明数据类型可否为空说明emo_novarcher(8)not null职工编号 kaoqin_monthvarchar(10)not null月份queqinchar(2)null缺勤天数kaoqin_gongzifloatnull奖惩工资表（3） 考勤列明数据类型可否为空说明emp_novarchar(8)not null职工编号jiaban_monthvarchar(10)not null月份jiabantianshuchar(2)null加班天数jiaban_jiangjinfloatnull津贴工资表（4） 津贴列明数据类型可否为空说明zhiwu varchar(10)not null职务jibengongzi float defaultnull基本工资表（5） 基本工资列明数据类型可否为空说明emp_novarchar(8)not nulldeparment_idchar(4)not nulljibengognzifloatnullzhufangbutie floatnull住房补贴gz_monthvarchar(10)not nulljiaban_jiangjinfloatnull奖惩kaoqin_gongzifloatnull津贴gonghuihuifeifloatnull工会会费zhfgjjin floatnull住房公积金yanglaobaoxian floatnull养老保险wagecount floatnull表（6） 月工资5、 数据库实施-创建数据库create database 工资管理on(name = sales_dat,filename = D:sql_datasales.mdf)go-创建表create table 员工(emp_no varchar(8) not null primary key, -职工编号emp_name varchar(10) not null, -姓名sex char(2) -性别check(sex = 男 or sex = 女),birth_date datetime not null, -出生日期hir_date datetime not null, -参加工作时间department_id char(4) not null, -所属部门zhiwu varchar(10) not null, -职务zhengzhimm varchar(10), -政治面貌marry varchar(4), -婚姻状况telephon char(11), -电话号码constraint old check(datediff(year,birth_date,hir_date)18)create table 部门(department_id char(4) not null primary key, -部门号department_name varchar(6) not null, -部门名称manager varchar(6), -部长dep_renshu varchar(4) -人数)create table 考勤(emp_no varcher(8) not null,-职工编号kaoqin_month varchar(10) not null,-月份queqin char(2),-缺勤天数kaoqin_gongzi float,-奖惩工资constraint e_k primary key(emp_no,kaoqin_month)create table 津贴(emp_no varchar(8) not null,-职工编号jiaban_month varchar(10) not null,-月份jiabantianshu char(2),-加班天数jiaban_jiangjin float,-津贴工资constraint e_j primary key(emp_no,jiaban_month) create table 基本工资(zhiwu varchar(10) not null primary key,-职务jibengongzi float default 1500, -基本工资)create table 月工资(emp_no varchar(8) not null,deparment_id char(4) not null,jibengognzi float default 1500,zhufangbutie float, -住房补贴gz_month varchar(10) not null,jiaban_jiangjin float, -奖惩kaoqin_gongzi float, -津贴gonghuihuifei float, -工会会费zhfgjjin float, -住房公积金yanglaobaoxian float, -养老保险wagecount as jibengongzi+zhufangbutie+jiaban_jiangjin+kaoqin_gongzi+gonghuihuifei+zhfgjjin+yanglaobaoxianconstraint e_g primary key (emp_no,gz_month)go-插入数据insert 员工 values(20070101,狄一,男,1985-1-1,2007-1-1,01,部长,团员,否,1234567890)insert 员工 values(20070102,狄二,女,1985-1-2,2007-1-2,01,员工,党员,否,1234567891)insert 部门 values(01,人事部,狄一,20)insert 部门 values(02,财务部,狄六,10)insert 考勤 values(20070101,200905,0,0)insert 考勤 values(20070101,200906,2,-60)insert 津贴 values(20070101,20000905,05,15)insert 津贴 values(20070101,20000906,0,0)insert 基本工资 values(员工,1500)insert 基本工资 values(组长,2000) insert 月工资 values(20070101,01,2000,0,20000905,0,15,0,0,0)insert 月工资 values(20070102,01,1500,0,20000905,-30,0,0,0,0) go-创建、绑定默认值create default gongzi_defa as 0exec sp_bindefault gongzi_defa,考勤.kaoqin_gongziexec sp_bindefault gongzi_defa,津贴.jiaban_jiangjinexec sp_bindefault gongzi_defa,月工资.gonghuihuifeiexec sp_bindefault gongzi_defa,月工资.zhfgjjinexec sp_bindefault gongzi_defa,月工资.yanglaobaoxianexec sp_bindefault gongzi_defa,月工资.zhufangbutie六、 设计和创建存储过程-创建存储过程-a.存储过程查询员工基本信息create proc ygxx2yg_id varchar(10)asselect * from 员工 where emp_no = yg_id-b.存储过程查询指定某个部门或全部部门的基本信息create proc bumen2bumen_id2 varchar(4)asselect * from 部门 where department_id = bumen_id2create proc bumenqasselect * from 部门-c.存储过程查询某个员工的工资信息create proc gzxxyg_id varchar(10)asselect * from 月工资where epm_no = yg_id-d.汇总部门月份工资create proc gzhzdep_id varchar(4),month_year varchar(10)asselect department_id,gz_month from 月工资where department_id = dep_id and gz_month = month_yearorder by department_id ,gz_month-e.汇总工资总和create proc sum_sumasselect department_name,gz_month,sum(wagecount) from 部门,月工资where 部门.department_id = 月工资.department_id group by department_name,gz_month七、设计和创建函数-创建函数-a.函数查询员工基本信息create function ygxx(yg_id varchar(10)returns tableasreturn(select * from 员工 where emp_no = yg_id)-b.函数查询部门信息create function bumen(bumen_id varchar(4)returns tableasreturn(select * from 部门 where department_id = bumen_id)八、设计和创建触发器-创建触发器-a.员工表中删除一条信息，其他表相应员工信息删除create trigger em on 员工for deleteasdelete from 考勤 where emp_no in(select emp_no from deleted)delete from 津贴 where emp_no in(select emp_no from deleted)delete from 月工资 where emp_no in(select emp_no from deleted)-b.一个部门删除，此部门中的员工信息删除create trigger bm on 部门for deleteasdelete from 员工 where department_id in (select department_id from deleted)-c.员工职务变动，基本工资改变create trigger jbgz on 员工for update