大型数据库课程设计.docx

1、大型数据库课程设计 本科生课程设计 课程名称 Oracle大型数据库课程设计 课程编号 s1620025-1 学号 200711621230 学生姓名 王旭浩 所在专业 计算机科学与技术 所在班级 1072 指导教师 杨亚菁 成绩 教师签字 年 月 日 课程设计时间： 2010 年 06 月 15 日至 2010 年 06 月 19 日一、设计总说明员工管理系统数据库设计设计说明：本设计主要是设计数据库，并根据需要设计相关的触发器及存储过程。在设计过程中要根据需求，设计规范的数据库。数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，达到有效地存储数据以满足用户

2、信息要求和处理要求。二、系统设计1.需求分析 员工管理系统数据库设计1.设计说明：本设计主要是设计数据库，并根据需要设计相关的触发器及存储过程。在设计过程中要根据需求，设计规范的数据库。数据库设计是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，达到有效地存储数据以满足用户信息要求和处理要求。 2.概要设计：E-R图设计：步骤如下：1定义实体：岗位实体：岗位（岗位编号、岗位名称、是否作废）民族实体：民族（民族编号、民族名称、是否作废）部门实体：部门（部门编号、部门名称、负责人、上级部门、地址、电话）员工实体：员工（员工编号、员工名称、所属部门、所属岗位、所属民族、性别、

3、出生日期、工作日期、照片、简历）定义联系：员工实体和岗位实体、民族实体、部门实体都是M:1关系。员工管理系统的E-R模型图3.详细设计根据E-R设计的数据表如下：1岗位实体：岗位（岗位编号、岗位名称、是否作废）Create table dcpost(PostNo char(3) primary key ,postname varchar2(30) ,ifvoid number(1) default 0 constraint DCPost_Chk2 check (ifvoid = 0 or ifvoid = 1);2民族实体：民族（民族编号、民族名称、是否作废）create table dcna

4、tion(nationno char(3) primary key ,nationname varchar2(30),ifvoid number(1) default 0 constraint DCNation_Chk1 check (ifvoid = 0 or ifvoid = 1);3部门实体：部门（部门编号、部门名称、负责人、上级部门、地址、电话）Create table department ( DepartmentNo char(3) primary key not null,dename varchar2(30) ,section varchar2(30),division var

5、char2(30),address varchar2(30),telephone varchar2(30) ;员工实体：员工（员工编号、员工名称、所属部门、所属岗位、所属民族、性别、出生日期、工作日期、照片、简历）Create table Employee(EmployeeNo varchar2(8) primary key not null,EmployeeName varchar2(30),DepartmentNo char (3),PostNo char(3),NationNo char(3), sex varchar2(2),birthday date,workdate date,ph

6、oto varchar2(200),resume varchar2(200),constraint emp_dep foreign key (DepartmentNo) references department (DepartmentNo),constraint emp_post foreign key (PostNo) references dcpost (PostNo),constraint emp_nation foreign key (NationNo) references dcnation(NationNo);4设计数据字典，触发器，存储过程设计。数据字典：管理信息系统会涉及大量

7、的静态数据，如商品单位、所属民族、所在岗位、这些数据，其数据的组织结构基本一致，有少量的数据可能今后不用，为了便于查询，可以为静态数据设置一个是否作废的属性，该属性用于历史数据查询和统计，而在事务处理中，已作废的数据不被检索和使用。岗位字典DCPost：名称数据类型大小比例不为空默认值约束POSTNOCHAR3主键POSTNAMEVARCHAR230IFVOIDNUMBER10取0或1民族字典dcnation：名称数据类型大小比例不为空默认值约束NATIONNOCHAR3主键NATIONNAMEVARCHAR230IFVOIDNUMBER10取0或1部门字典department：名称数据类型大

8、小比例不为空默认值约束DEPARTMENTNOCHAR3主键DENAMEVARCHAR230SECTIONVARCHAR230DIVISIONVARCHAR230ADDRESSVARCHAR230TELEPHONEVARCHAR230员工字典employee：名称数据类型大小比例不为空默认值约束EMPLOYEENOVARCHAR28主键EMPLOYEENAMEVARCHAR230DEPARTMENTNOCHAR3外键（参照department表）POSTNOCHAR3外键（参照dcpost表）NATIONNOCHAR3外键（参照dcnation表）SEXVARCHAR22BIRTHDAYDAT

9、EWORKDATEDATEPHOTOVARCHAR2200RESUMEVARCHAR2200设计触发器为了保证员工编号的正确性，为员工表的插入和修改设计触发器。触发器，存储过程清单(1) 插入触发器代码： rem * rem * 创建触发器 rem * 功能：当插入员工编号时，编号必须满足为数字 rem *CREATE or REPLACE TRIGGER EmployeeIns before insert ON Employee FOR each row declare myError EXCEPTION; /*出错处理*/ varLeft char(4); /*编号前4位*/ varMid

10、 char(4); /*编号第5位*/ varRight char(3); /*编号后3位*/ i number; begin if LENGTH(:new.EmployeeNo) !=8 then RAISE myError; end if; varLeft := SUBSTR(:new.EmployeeNo,1,4); varMid := SUBSTR(:new.EmployeeNo,5,1); varRight := SUBSTR(:new.EmployeeNo,6,3); if varMid != - then RAISE myError; end if; for i in 1.4 l

11、oop if (SUBSTR(varLeft,i,1)9) then RAISE myError; exit; end if; end loop; for i in 1.3 loop if (SUBSTR(varRight,i,1)9) then RAISE myError; exit; end if; end loop; EXCEPTION WHEN myError THEN :new.EmployeeNo := NULL; end; (2) 修改触发器代码：功能：当修改编号时，编号必须满足为数字 rem *rem * 创建触发器 rem * 功能：当修改编号时，编号必须满足为数字 rem

12、*CREATE or REPLACE TRIGGER EmployeeUpdate before update of EmployeeNo ON Employee FOR each row declare myError EXCEPTION; /*出错处理*/ varLeft char(4); /*编号前4位*/ varMid char(4); /*编号第5位*/ varRight char(3); /*编号后3位*/ i number; begin if LENGTH(:new.EmployeeNo) !=8 then RAISE myError; end if; varLeft := SU

13、BSTR(:new.EmployeeNo,1,4); varMid := SUBSTR(:new.EmployeeNo,5,1); varRight := SUBSTR(:new.EmployeeNo,6,3); if varMid != - then RAISE myError; end if; for i in 1.4 loop if (SUBSTR(varLeft,i,1)9) then RAISE myError; exit; end if; end loop; for i in 1.3 loop if (SUBSTR(varRight,i,1)9) then RAISE myErro

14、r; exit; end if; end loop; EXCEPTION WHEN myError THEN :new.EmployeeNo := :old.EmployeeNo; :new.EmployeeName := :old.EmployeeName; :new.DepartmentNo := :old.DepartmentNo; :new.sex := :old.sex; :new.birthday := :old.birthday; :new.workdate:= :old.workdate; :new.NationNo := :old.NationNo; :new.PostNo

15、:= :old.PostNo; end;设计存储过程：对员工编号采用存储过程的方法输入-创建产生员工编号过程-rem * rem * 产生员工编号过程 rem * 功能：根据输入的年份，自动产生员工编号 rem * 输入参数：sYear 年份 rem * 输出参数：returnEmployeeNo 员工编号 rem * CREATE or REPLACE PROCEDURE proEmployeeNo(sYear varchar2,returnEmployeeNo OUT varchar2) is x number; /*得到员工编号后三位的流水号*/ varEmployeeNo1 varch

16、ar2(8); /*某年份的最大员工编号*/ varEmployeeNo2 varchar2(8); /*新的员工编号*/ begin varEmployeeNo1 :=; select NVL(max(EmployeeNo),0) into varEmployeeNo1 from Employee where EmployeeNo like sYear | %; if varEmployeeNo1 = 0 then /*无该年份的员工，编号从001开始*/ varEmployeeNo2 := sYear | -001; else x := to_number(SUBSTR(varEmploy

17、eeNo1,6,3)+1; if x 10 then varEmployeeNo2 := sYear | -00 | ltrim(to_char(x); elsif x100 then varEmployeeNo2 := sYear | -0 | ltrim(to_char(x); else varEmployeeNo2 := sYear | - | ltrim(to_char(x); end if; end if; returnEmployeeNo := varEmployeeNo2; end proEmployeeNo;插入数据：-插入的数据-岗位表Insert into dcpost(P

18、ostNo, postname, ifvoid)Values (1,生产员,1);Insert into dcpost(PostNo, postname, ifvoid)Values (2,销售员,1);-民族表Insert into dcnation(NationNo, nationname, ifvoid)Values (1,汉族,0);Insert into dcnation(NationNo, nationname, ifvoid)Values (2,蒙古,0);-部门表Insert into department(DepartmentNo,dename,section,divisio

19、n,address,telephone)Values(1,销售部,市场部,小宝,安B710,2475089);Insert into department(DepartmentNo,dename,section,division,address,telephone)Values(1,销售部,市场部,小猫,安B710,2475088);-员工表Insert into Employee(EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,sex,birthday,workdate,photo,resume)Values(2010-001,小王,

20、1,1,1,男,date1988-02-01,date2010-02-01,001.jpg,001.doc);insert into Employee (EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,Sex,EmployeeDate,WorkDate,Photo,Resume) values (:id,小风,1,1,1,男,date1979-02-01,date2006-02-01,002.jpg,002.doc);5.调试与测试：测试插入触发器：如果插入员工表的编号不是*-*格式会出现错误，插入不进数据：如果是数字则能正常插入：se

21、lect * from employee测试修改触发器：如果当修改编号时，编号不为数字则修改不了编号：如果当修改编号时，编号格式正确则能正常修改编号：测试产生员工编号过程：variable id varchar2(10);exec proEmployeeNo (2008,:id);print id;insert into Employee (EmployeeNo,EmployeeName,DepartmentNo,PostNo,NationNo,Sex,Birthday,WorkDate,Photo,Resume) values (:id,小风,1,1,1,男,date1979-02-01,date2006-02-01,002.jpg,002.doc);