Oracle员工工资管理系统.doc

大型数据库技术设计报告题 目: 员工工资管理系统数据库设计 学生姓名: 李龙 学 号: 201317030120 专业班级: 网工13101班 指导教师: 易明 设计时间: 2015年下学期 指导老师意见:签名:日期:评定成绩:目录1. 需求分析12. 概念模型设计11. 员工信息E-R图（如图1所示）12. 部门信息E-R图（如图2所示）23. 工资实体局部E-R模型设计（如同3所示）24. 职务信息E-R图（如图4所示）35. 考勤信息E-R图（如图5所示）36. 整体简洁E-R图（如图6所示）47. 用户信息E-R图（如图7所示）43. 逻辑结构设计51. 关系模型设计52. 数据库表结构的设计54. 数据库的实现71. 创建用户72. 为用户分配权限73. 用新创建的用户登录数据库74. 创建表空间75. 在表空间上创建员工信息表76. 向员工信息表中插入记录87. 查询员工信息表中的记录98. 在表空间上创建部门信息表99. 向部门信息表中插入记录910. 查询部门信息表中的记录1011. 创建工资信息表1012. 向工资信息表中插入记录1013. 查询工资信息表中的记录1114. 创建职务信息表1115. 向职务信息表中插入记录1116. 查询职务信息表中的数据1317. 创建考勤信息表1318. 向考勤信息表中插入记录1319. 查询考勤信息表中的记录1520. 创建用户信息表1521. 向用户信息表中插入记录1522. 向用户信息表中的性别建立B树索引1523. 给员工信息表建立外键和CHCEK约束1624. 给职务信息表建立外键和CHCEK约束1625. 给考勤信息表建立外键约束165. 主要代码171. 查询代码172. 创建视图203. 创建存储过程214. 创建触发器当某员工辞职后删除该员工记录236. 权限设置及数据库备份231. 创建用户（lilong）并为其分配所有权限232. 创建用户（lixiaolong）并为其分配查询权限243. 导出用户数据247. 心得体会258. 参考文献261. 需求分析实现员工基本信息管理、工资信息管理、部门信息管理等功能，包括员工信息的添加、修改、删除及查询；工资信息的添加、修改、删除及查询等。2. 概念模型设计1. 员工信息E-R图（如图1所示）图 1员工信息E-R图2. 部门信息E-R图（如图2所示）图 2部门信息E-R图3. 工资实体局部E-R模型设计（如同3所示）图 3工资信息E-R图4. 职务信息E-R图（如图4所示）图 4职务信息E-R图5. 考勤信息E-R图（如图5所示）图 5考勤信息E-R图6. 整体简洁E-R图（如图6所示）图 6整体简洁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/1234. 创建表空间create tablespace testdatafile D:李龙课程设计test size 10mautoextend on next 1m maxsize 100mextent 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 test6. 向员工信息表中插入记录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,貂蝉,女TO_DATE(2011-11-15,yyyy-mm-dd),10,五);insert into emp values(4,赵云,男TO_DATE(2012-11-11,yyyy-mm-dd),11,六);insert into emp values(5,诸葛亮,男TO_DATE(2014-04-19,yyyy-mm-dd),11,四);insert into emp values(6,关羽,男TO_DATE(2013-05-14,yyyy-mm-dd),11,五);insert into emp values(7,黄忠,男TO_DATE(2014-02-12,yyyy-mm-dd),12,二);insert into emp values(8,鲁肃,男TO_DATE(2014-02-13,yyyy-mm-dd),12,九);insert into emp values(9,周瑜,男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,文丑,男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 test9. 向部门信息表中插入记录insert into dept(deptno,dname,manager,mtel)values(10,项目部,李龙;insert into dept(deptno,dname,manager,mtel)values(11,人事部,李梦龙;insert into dept(deptno,dname,manager,mtel)values(12,财务部,李欣龙;insert into dept(deptno,dname,manager,mtel)values(13,IT部,李龙龙;10. 查询部门信息表中的记录11. 创建工资信息表create table sal(rank char(2) primary key,basalary number(4) not null,bonus number(4) default 0)tablespace test12. 向工资信息表中插入记录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,李龙,总经理男,一);insert into duty(dutyno,duname,job,tel,sex,rank) values(102,李小龙,副经理男,二);insert into duty(dutyno,duname,job,tel,sex,rank) values(103,李子龙,秘书女,三);insert into duty(dutyno,duname,job,tel,sex,rank) values(104,李梦龙,总经理女,一);insert into duty(dutyno,duname,job,tel,sex,rank) values(105,李泽龙,助理女,四);insert into duty(dutyno,duname,job,tel,sex,rank) values(106,李云龙,秘书女,五);insert into duty(dutyno,duname,job,tel,sex,rank) values(107,李欣龙,总经理女,二);insert into duty(dutyno,duname,job,tel,sex,rank) values(108,李好龙,副经理男,三);insert into duty(dutyno,duname,job,tel,sex,rank) values(109,李坏龙,秘书男,七);insert into duty(dutyno,duname,job,tel,sex,rank) values(110,李龙龙,总经理男,一);insert into duty(dutyno,duname,job,tel,sex,rank) values(111,李章龙,秘书女,八);insert into duty(dutyno,duname,job,tel,sex,rank) values(112,李宇龙,助理女,九);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 test18. 向考勤信息表中插入记录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 test23. 给员工信息表建立外键和CHCEK约束alter table empadd constraint emp_dept foreign key (deptno)references dept(deptno);alter table empadd constraint emp_sal foreign key (rank)references sal(rank);alter table empadd constraint check_sexcheck(sex=男or sex=女);24. 给职务信息表建立外键和CHCEK约束alter table dutyadd constraint duty_sal foreign key (rank)references sal(rank);alter table dutyadd constraint check_sex1check(sex=男or sex=女);25. 给考勤信息表建立外键约束alter table attendadd constraint attend_emp foreign key (empno)references emp(empno);5. 主要代码1. 查询代码1) 查询员工号为1的部门号select dname from emp,deptwhere empno=1 and emp.deptno=dept.deptno;2) 查询员工赵云的部门经理是谁select manager from emp,deptwhere ename=赵云 and emp.deptno=dept.deptno; 3) 查询所有员工早退情况并按早退次数降序排列select leave，ename from emp，attendwhere emp.empno=attend.empnoorder by leave desc4) 查询员工工资等级为二的员工编号、姓名和部门名称select empno 员工编号,ename 员工姓名,dname 部门名称from emp,deptwhere rank=二 and emp.deptno=dept.deptno;5) 查询11号部门工资最高的员工编号，员工姓名，工资和年终奖金select empno 员工编号，ename 姓名,basalary 工资,bonus 年终奖金from emp,salwhere deptno=11 and basalary=(select max(basalary) from emp,sal where deptno=11)6) 查询所有员工的工资并按升序排列select basalary 工资，ename 员工姓名 from emp,salwhere emp.rank=sal.rankorder by basalary asc7) 查询工资级别等于五的姓名和该人的部门经理的电话号码select ename 员工名字,mtel 经理电话from emp,deptwhere rank=五 and emp.deptno=dept.deptno8) 查询缺勤次数最少的员工名字，电话和他的部门经理select ename 员工姓名,tel 电话号码, manager 部门经理from emp,dept,attendwhere absence in (select min(absence)from attend) and emp.deptno=dept.deptno and emp.empno=attend.empno9) 查询职务编号为104的姓名，职务，工资select duname 姓名,job 职务,basalary 工资from duty,salwhere dutyno=104 and duty.rank=sal.rank10) 查询性别为男的入厂时间并按从早到晚排序select hiredate 入厂时间,ename 姓名 from empwhere sex=男order by hiredate asc;2. 创建视图1) 创建视图（sal_avg）查询各部门平均工资create or replace view sal_avgas select deptno，avg(basalary) 平均工资from emp，salwhere emp.rank=sal.rankgroup 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)asbeginupdate emp set change_sal=change_sal+addsal where empno=empnos;end addsalary;给2号员工的工资增加200exec change_sal(2,200);3) 创建一个调整员工部门的存储过程create or replace procedure change_empdept(empnos in emp.empno%type,deptnos in dept.deptno%type)asbeginupdate emp set deptno=deptnos where empno=empnos;exceptionwhen no_data_found thendbms_output.put_line(没有找到数据);end change_empdept;执行存储过程exec change_empdept(3,12);查看结果4. 创建触发器当某员工辞职后删除该员工记录create or replace trigger removeafter delete on empfor each rowbegin delete emp where emp.empno=:old.empno;end;6. 权限设置及数据库备份1. 创建用户（lilong）并为其分配所有权限create user lilong identified by 123;grant create session,resource,dba to lilong;gran