Oracle员工工资管理系统.doc

大型数据库技术设计报告
题目：员工工资管理系统数据库设计
学生姓名:李龙
学号: 201317030120
专业班级：网工13101班
指导教师:易明
设计时间: 2015年下学期
指导老师意见:

签名：
日期：
评定成绩:

目录
1. 需求分析1
2. 概念模型设计1
1. 员工信息E-R图（如图1所示）1
2. 部门信息E-R图（如图2所示）2
3。 工资实体局部E-R模型设计(如同3所示）2
4. 职务信息E—R图（如图4所示）3
5. 考勤信息E—R图（如图5所示）3
6. 整体简洁E—R图(如图6所示）4
7. 用户信息E-R图（如图7所示）4
3。 逻辑结构设计5
1。 关系模型设计5
2. 数据库表结构的设计5
4. 数据库的实现7
1。 创建用户7
2. 为用户分配权限7
3。 用新创建的用户登录数据库7
4. 创建表空间7
5. 在表空间上创建员工信息表7
6. 向员工信息表中插入记录8
7。 查询员工信息表中的记录9
8. 在表空间上创建部门信息表9
9。 向部门信息表中插入记录9
10。 查询部门信息表中的记录10
11. 创建工资信息表10
12. 向工资信息表中插入记录10
13. 查询工资信息表中的记录11
14。 创建职务信息表11
15。 向职务信息表中插入记录11
16。 查询职务信息表中的数据13
17. 创建考勤信息表13
18. 向考勤信息表中插入记录13
19. 查询考勤信息表中的记录15
20。 创建用户信息表15
21. 向用户信息表中插入记录15
22. 向用户信息表中的性别建立B树索引15
23。 给员工信息表建立外键和CHCEK约束16
24。 给职务信息表建立外键和CHCEK约束16
25. 给考勤信息表建立外键约束16
5。 主要代码17
1。 查询代码17
2。 创建视图20
3. 创建存储过程21
4. 创建触发器当某员工辞职后删除该员工记录23
6. 权限设置及数据库备份23
1. 创建用户(lilong）并为其分配所有权限23
2。 创建用户（lixiaolong)并为其分配查询权限24
3。 导出用户数据24
7. 心得体会25
8。 参考文献26

1. 需求分析
实现员工基本信息管理、工资信息管理、部门信息管理等功能，包括
员工信息的添加、修改、删除及查询；工资信息的添加、修改、删除及查询等。
2. 概念模型设计
1. 员工信息E-R图（如图1所示）

图Error! Bookmark not defined.员工信息E-R图
2. 部门信息E-R图(如图2所示）

图Error! Bookmark not defined.部门信息E-R图
3. 工资实体局部E—R模型设计（如同3所示)

图1工资信息E-R图
4. 职务信息E-R图（如图4所示）

图2职务信息E-R图
5. 考勤信息E-R图(如图5所示）

图3考勤信息E—R图
6. 整体简洁E-R图(如图6所示）

图Error! Bookmark not defined.整体简洁E-R图
7. 用户信息E—R图(如图7所示）

图 7用户信息E—R图

3. 逻辑结构设计
1. 关系模型设计
1) 员工信息对应的关系模型
员工（员工编号(主键）,姓名，性别,联系电话，入厂日期，部门编号（外键），工资等级（外键））
2) 部门信息对应的关系模型
部门(部门编号(主键)，部门名称，部门经理）
3) 工资信息对应的关系模型
工资（工资等级（主键），基本工资,年终奖)
4) 职务信息对应的关系模型
管理人员(职务编号（主键），联系电话，职位，工资级别(外键),性别）
5) 考勤信息对应的关系模型
考勤（员工编号（主键一部分），日期（主键一部分)，请假次数，迟到次数，缺勤次数）
6) 用户信息对应的关系模型
用户(用户名，密码，权限）
2. 数据库表结构的设计
1) 员工信息表结构

2) 部门信息表结构

3) 工资信息表结构

4) 职务人员信息表结构

5) 考勤信息表结构

4. 数据库的实现
1. 创建用户
以管理员身份登录数据库，创建新用户
create user lilong identified by 123;
2. 为用户分配权限
grant create session,resource,dba to lilong；
3. 用新创建的用户登录数据库
conn lilong/123
4. 创建表空间
create tablespace test
datafile ' D:\李龙课程设计\test' size 10m
autoextend on next 1m maxsize 100m
extent management local；
5. 在表空间上创建员工信息表
create table emp(
empno number（4) primary key,
ename varchar2(10) not null，
sex char（2) not null，
tel nvarchar2(11) ,
hiredate date not null，
deptno number（4) not null,
rank char(2) not null
)tablespace test
6. 向员工信息表中插入记录
insert into emp values（1,’刘备’,'男’，'11711602970',TO_DATE('2009—02—12’,'yyyy—mm—dd')，10,'三')；
insert into emp values(2,'吕布'，’男’，'12307366965',TO_DATE('2010—03-02','yyyy-mm—dd’)，10，'六’);
insert into emp values(3，'貂蝉',’女','18107348365’，TO_DATE（'2011—11—15','yyyy—mm—dd'），10，'五’);
insert into emp values(4,'赵云',’男’，'13307304515’，TO_DATE（'2012—11-11’,'yyyy-mm-dd'）,11,’六'）;
insert into emp values（5,'诸葛亮’，'男',’18711602970',TO_DATE(’2014-04—19’,’yyyy—mm—dd’），11,’四’）；
insert into emp values(6，’关羽’，'男’,’18107304565’，TO_DATE('2013—05-14’,’yyyy—mm—dd'),11，’五');
insert into emp values（7，’黄忠’,'男'，'13307302265’,TO_DATE（’2014—02-12',’yyyy-mm-dd'),12，'二');
insert into emp values(8,'鲁肃’，'男’,'13407304565’，TO_DATE（’2014-02—13'，'yyyy-mm—dd')，12，'九')；
insert into emp values(9，’周瑜','男’，'18117304765',TO_DATE（’2012—04-14’，’yyyy—mm-dd’）,12，’十’）;
insert into emp values（10，'曹操',’男'，'12307304165'，TO_DATE（'2012-02—15'，’yyyy-mm-dd’），13,’六')；
insert into emp values(11,'颜良’,'女','1307304465',TO_DATE（’2014—06-13'，’yyyy-mm—dd'）,13，’七’）;
insert into emp values(12,’文丑'，’男'，’13027304565’,TO_DATE（'2013—03-10'，’yyyy-mm-dd’）,13，'八');
7. 查询员工信息表中的记录

8. 在表空间上创建部门信息表
create table dept
（deptno number（4) primary key，
dname varchar2(10) not null，
manager varchar2（10） not null
）tablespace test
9. 向部门信息表中插入记录
insert into dept(deptno,dname,manager,mtel）values(10,'项目部’,’李龙','17711602970');
insert into dept（deptno,dname,manager，mtel)values(11,'人事部','李梦龙','18307304565’）；
insert into dept(deptno，dname，manager,mtel）values（12，’财务部'，’李欣龙',’13116023970’);
insert into dept（deptno，dname,manager,mtel)values（13,’IT部’，’李龙龙’,’18707602750’)；
10. 查询部门信息表中的记录

11. 创建工资信息表
create table sal(
rank char（2) primary key,
basalary number(4） not null,
bonus number（4) default 0
)tablespace test
12. 向工资信息表中插入记录
insert into sal(rank,basalary，bonus)values（'一',9800，3000）；
insert into sal（rank，basalary，bonus)values（'二’,9500,2500）；
insert into sal（rank，basalary,bonus)values(’三’,9200,2300）；
insert into sal(rank,basalary,bonus)values（’四’，9000，2200)；
insert into sal（rank,basalary,bonus)values(’五’，8000，2100）;
insert into sal（rank,basalary，bonus)values（'六’,7000,2000);
insert into sal(rank，basalary,bonus）values('七’,6000，1800）;
insert into sal(rank，basalary，bonus)values（'八',5000，1600)；
insert into sal（rank,basalary，bonus）values（'九’，4000，1400);
insert into sal（rank，basalary，bonus）values（'十’,3000，1200);
13. 查询工资信息表中的记录

14. 创建职务信息表
create table duty
(dutyno number（4) primary key，
dname varchar2（10） not null，
job varchar2（10) not null，
tel nvarchar2(11） ，
sex char（2) not null，
rank char(2)
）tablespace test
15. 向职务信息表中插入记录
insert into duty(dutyno，duname，job，tel，sex，rank) values（101,'李龙’，'总经理'，’17711602970',’男’，'一’）；
insert into duty（dutyno,duname,job，tel,sex,rank) values（102,’李小龙',’副经理'，'18307366965’,’男',’二');
insert into duty（dutyno，duname,job，tel，sex,rank） values（103,'李子龙','秘书'，'18107346365’,'女’,’三’);
insert into duty(dutyno，duname,job,tel，sex，rank） values（104,'李梦龙’，’总经理’，’18307304565','女'，’一’);
insert into duty（dutyno,duname,job,tel，sex，rank） values(105,'李泽龙','助理’,'18711602970’,'女'，’四'）；
insert into duty(dutyno，duname，job，tel，sex，rank) values（106，'李云龙','秘书'，’13661602970’，’女’,'五'）;
insert into duty(dutyno，duname，job,tel,sex，rank） values（107,’李欣龙'，’总经理','13116023970','女’，’二')；
insert into duty（dutyno，duname，job,tel，sex,rank） values(108,'李好龙'，’副经理',’13765602950’,'男',’三’)；
insert into duty(dutyno,duname，job，tel,sex,rank) values（109，'李坏龙’,’秘书','18712602570'，’男’,’七’);
insert into duty(dutyno,duname，job,tel，sex，rank) values(110，’李龙龙',’总经理'，'18707602750’,'男’,'一'）;
insert into duty(dutyno,duname，job，tel，sex,rank） values（111,'李章龙’,'秘书’,'18707362710',’女’，'八’）;
insert into duty(dutyno,duname，job，tel,sex，rank） values(112,'李宇龙',’助理’，'18711812720’,’女',’九’)；
16. 查询职务信息表中的数据

17. 创建考勤信息表
create table attend
(day date ，
empno number（4），
leave number（4） default 0，
late number（4) default 0，
absence number（4) default 0,
primary key (day，empno）
）tablespace test
18. 向考勤信息表中插入记录
insert into attend (day,empno，leave,late,absence）values(to_date('2010—12-3’，’yyyy—mm—dd’）,1，1,0，0)；
insert into attend （day,empno,leave，late，absence)values(to_date(’2011-10-2’，'yyyy-mm-dd’),4,0,1,0);
insert into attend (day,empno，leave，late,absence)values（to_date（’2012-10—12'，'yyyy-mm-dd'），2，1,0,1);
insert into attend (day，empno，leave，late,absence)values（to_date(’2014-11—11','yyyy-mm—dd’）,3，0,0，0）;
insert into attend （day，empno，leave，late，absence)values(to_date（'2013—7—14’,’yyyy-mm-dd'），4,1,0，0）;
insert into attend (day，empno，leave，late,absence）values（to_date（'2012—11—12'，'yyyy-mm—dd’），5，1,1，0）；
insert into attend (day，empno,leave,late，absence)values（to_date（’2013-11—14'，’yyyy—mm—dd’),6，1,0，0）；
insert into attend （day,empno，leave,late,absence)values(to_date（’2014—6—21',’yyyy-mm-dd'）,7,0,0,0);
insert into attend （day,empno，leave,late，absence）values(to_date（'2015—1—22’，'yyyy-mm-dd’)，8,1，0，0）；
insert into attend （day，empno，leave，late,absence)values(to_date('2014-4-24'，’yyyy-mm—dd'）,9,0，1,0）；
insert into attend （day，empno，leave，late,absence)values（to_date（’2015-10—11’，'yyyy-mm—dd'），10，1，0,0);
insert into attend (day，empno,leave,late,absence)values(to_date（'2010—12-1’，'yyyy—mm-dd'）,11,0,0,1）;
19. 查询考勤信息表中的记录

20. 创建用户信息表
create table users
(username char（10） not null，
password char（30) not null,
juris char（30）
)tablespace test；
21. 向用户信息表中插入记录
insert into users(username,password,juris)values（’lilong’，'123’,'所有权限’）；
insert into users(username,password，juris)values（'lixiaolong',’123'，'查询'）;
22. 向用户信息表中的性别建立B树索引
create index empshow_index ON emp(sex)
cablespace test

23. 给员工信息表建立外键和CHCEK约束
alter table emp
add constraint emp_dept foreign key （deptno)
references dept(deptno);
alter table emp
add constraint emp_sal foreign key （rank）
references sal（rank);
alter table emp
add constraint check_sex
check(sex=’男'or sex='女');
24. 给职务信息表建立外键和CHCEK约束
alter table duty
add constraint duty_sal foreign key （rank）
references sal(rank)；
alter table duty
add constraint check_sex1
check（sex='男’or sex=’女'）;
25. 给考勤信息表建立外键约束
alter table attend
add constraint attend_emp foreign key (empno)
references emp（empno)；
5. 主要代码
1. 查询代码
1) 查询员工号为1的部门号
select dname from emp,dept
where empno=1 and emp。deptno=dept.deptno;

2) 查询员工‘赵云'的部门经理是谁
select manager from emp,dept
where ename='赵云' and emp.deptno=dept。deptno；

3) 查询所有员工早退情况并按早退次数降序排列
select leave，ename from emp,attend
where emp。empno=attend。empno
order by leave desc

4) 查询员工工资等级为二的员工编号、姓名和部门名称
select empno 员工编号，ename 员工姓名，dname 部门名称
from emp,dept
where rank=’二' and emp。deptno=dept.deptno；

5) 查询11号部门工资最高的员工编号，员工姓名，工资和年终奖金
select empno 员工编号，ename 姓名，basalary 工资,bonus 年终奖金
from emp,sal
where deptno=11 and basalary=(select max（basalary)
from emp，sal
where deptno=11）

6) 查询所有员工的工资并按升序排列
select basalary 工资，ename 员工姓名
from emp,sal
where emp.rank=sal.rank
order by basalary asc

7) 查询工资级别等于五的姓名和该人的部门经理的电话号码
select ename 员工名字,mtel 经理电话
from emp,dept
where rank='五’ and emp。deptno=dept。deptno

8) 查询缺勤次数最少的员工名字,电话和他的部门经理
select ename 员工姓名，tel 电话号码, manager 部门经理
from emp,dept，attend
where absence in (select min（absence）from attend)
and emp.deptno=dept。deptno
and emp.empno=attend。empno

9) 查询职务编号为104的姓名,职务，工资
select duname 姓名，job 职务，basalary 工资
from duty，sal
where dutyno=104 and duty.rank=sal.rank

10) 查询性别为男的入厂时间并按从早到晚排序
select hiredate 入厂时间，ename 姓名 from emp
where sex='男’
order by hiredate asc;

2. 创建视图
1) 创建视图(sal_avg）查询各部门平均工资
create or replace view sal_avg
as select deptno,avg（basalary) 平均工资
from emp,sal
where emp。rank=sal.rank
group by deptno

2) 调用视图（sal_avg）中的记录
select ＊ from sal_avg；

3. 创建存储过程
1) 为员工表增加一个字段用于调整员工工资

2) 创建存储过程，调整某一员工的工资（chang_sal）
create or replace procedure addsalary（empnos in
emp.empno％type，
addsal in emp.change_sal%type)
as
begin
update emp set change_sal=change_sal+addsal
where empno=empnos；
end addsalary；

给2号员工的工资增加200
exec change_sal（2,200)；

3) 创建一个调整员工部门的存储过程
create or replace procedure change_empdept
（empnos in emp。empno%type,
deptnos in dept.deptno％type
)as
begin
update emp set deptno=deptnos where empno=empnos;
exception
when no_data_found then
dbms_output。put_line('没有找到数据’);
end change_empdept;
执行存储过程
exec change_empdept（3,12）;

查看结果

4. 创建触发器当某员工辞职后删除该员工记录
create or replace trigger remove
after delete on emp
for each row
begin
delete emp where emp。empno=：old.empno;
end；

6. 权限设置及数据库备份
1. 创建用户(lilong）并为其分配所有权限
create user lilong identified by 123;
grant create session,resource，dba to lilong;
grant create any sequence to lilong;
grant create any table to lilong;
grant delete any table to lilong;
grant insert any table to lilong；
grant select any table to lilong；
grant unlimited tablespace to lilong;
grant execute any procedure to lilong;
grant update any table to lilong；
grant create any view to lilong;

2. 创建用户(lixiaolong)并为其分配查询权限
create user lixiaolong identified by 123；
grant create session to lixiaolong；
grant select any table to lixiaolong；
conn lixiaolong/123;

3. 导出用户数据
c：\〉exp userid=lilong/123 file=c:\data log=c:\log

7. 心得体会
在刚开始做这个题目设计的时候，一片迷茫，不知道从何下手，在看oracle教材相关内容之后,理解了E-R图,实体和属性之间的联系，知道了画E-R图,并且仔细思考各个E—R图之间的相互联系,属性与属性之间的联系，并且按照所画的E—R图,建立表，然后设立主键和外键还有check约束，在做这个课程设计之前，我对这些根本一点概念都不懂，通过一步步编写代码，查找资料,不断的排错，已经能够对这些概念有了初步的了解，和编写能力，第一天建表的时候，插入数据，后来经过查询，发现一系列问题，然后重新画图建表，终于把各个表之间的联系完美的搭建了起来，通过这次课程设计,我学会了一些oracle相关语句,例如添加约束add constraint，多表链接查询，子查询，group by 。.. Having，order by ..。 desc/asc等等。还有在我经过多次排错做完了题目之后,让我掌握了几个排错方法，在帮助其他同学完成课程设计的时候也得到了锻炼,让我对这门技术有了浓厚的学习兴趣和动力，这次课程设计让我懂得了从理论到实践真的很重要，只有用心用力去做，一定会受益良多.
8. 参考文献
马晓玉，《数据库管理 Oracle 10g》,清华大学出版社 ，2007年
王匡胤，《数据库原理与设计》,电子工业出版社，2011年

24