数据库系统的设计与实现Oracle

编号：学号：课程性质小学期实践教学环节专业软件工程班级软件工程姓名同组人员小组序号指导教师2019 年秋学期目录一.、数据库概念结构设计 (2)1、E-R图 (2)2、数据项说明 (2)二、数据库逻辑结构设计 (5)三、数据库物理结构设计 (6)1、完整性设计 (6)2 、物理存储设计 (6)3 、用户及安全设计 (7)4、视图设计 (7)5、远程连接设计 (8)6、触发器设计 (8)7、同义词设计 (8)四、数据库实施中的重难点 (9)1、数据库实施环境 (9)2、数据库实施中的重难点 (9)2、1创建触发器 (9)2、2创建存储过程 (10)2、3数据备份与恢复 (14)五总结 (16)一.、数据库概念结构设计1、E-R图2、数据项说明关系名称项目关系代码t_xm序属性名属性代码数据类型属性描述备注号1 项目号Xmh Varchar2(20) 主码2 项目预算Ys Number3 开工日期Kgdate Date关系名称供应关系代码t_gy属性名属性代码数据类型属性描述备注序号1 供应商号Gysh Varchar2(20) 外码2 项目号Xmh Varchar2(20) 外码3 零件号Ljh Varchar2(20) 外码4 供应量gyl Number关系名称供应商关系代码T_gys 属性名属性代码数据类型属性描述备注序号1 供应商号Gysh Varchar2(20) 主码2 供应商姓名Xm Varchar2(8)3 供应商地址Dz Varchar2(50)4 电话号码Dhhm Varchar2(11)5 账号zh Varchar2(20)关系名称零件关系代码T_lj属性名属性代码数据类型属性描述备注序号1 零件号Ljh Varchar2(20) 主码2 名称Mc Varchar2(10)3 规格Gg Varchar2(8)4 单价Dj Number5 描述ms Varchar2(50)关系名称库存关系代码T_kc属性名属性代码数据类型属性描述备注序号1 仓库号Ckh Varchar2(20) 外码2 零件号Ljh Varchar2(20) 外码3 库存量kcl Number关系名称仓库关系代码t_ck属性名属性代码数据类型属性描述备注序号1 仓库号Ckh Varchar2(20) 主码2 面积Mj Number3 电话号码Dhhm Varchar2(11)关系名称职工关系代码t_zg属性名属性代码数据类型属性描述备注序号1 职工号zgh Varchar2(20) 主码2 姓名xm Varchar2(8)3 年龄nl Number4 职称Zc Varchar2(8)5 领导职工号Ld Varchar2(20) 外码6 仓库号ckh Varchar2(20) 外码二、数据库逻辑结构设计关系名称关系代码属性名属性代码数据类型属性描述备注序号1 职工号zgh Varchar2(20) 主码2 姓名xm Varchar2(8)3 年龄nl Number4 职称Zc Varchar2(8)5 领导Ld Varchar2(20)6 仓库号ckh Varchar2(20)在函数依赖范围内，最高属于范式：因为主码职工号可以确定任意的非主属性，而且不存在传递函数依赖，所以职工表是3NF三、数据库物理结构设计1、完整性设计表名主键非空或唯一性约束外键参照表职工表（t_zg）职工号（xmh）xmh，not null 仓库号（ckh）领导职工号（ld）仓库表（t_ck）职工表（t_zg）仓库表（t_ck）仓库号（ckh）ckh，not null库存表（t_kc）仓库号（ckh）+零件号（ljh）ckh，not nullljh，not null零件号（ljh）仓库号（ckh）零件表（t_lj）仓库表（t_ck）零件表（t_lj）零件号（ljh）ljh，not null 项目表（t_xm）项目号（xmh）xmh，not null 供应商表（t_gys）供应商号（gysh）gysh，not null供应表（t_gy）零件号（ljh）+项目号（xmh）+供应商号（gysh）ljh，not nullxmh，not nullgysh，not null零件号（ljh）项目号（zmh）供应商号（gysh）零件表（t_lj）项目表（t_xm）供应商表（t_gys）1.2自定义完整性供应表：供应商号设置为非空，项目号设置为非空，零件号设置为非空库存表：仓库号设置为非空，零件号设置为非空2 、物理存储设计2.1对常用查询创建索引如：项目表中项目号，供应表中供应商号和项目号，零件表中零件号，库存表中仓库号和零件号，职工表中职工号A机器项目表（t_xm），供应表（t_gy），供应商表（t_gys），零件表（t_lj）B机器库存表（t_kc），仓库表（t_ck），职工表（t_zg）C机器（备份机）项目表（t_xm），供应商表（t_gys），供应表（t_gy），零件表（t_lj）职工表（t_zg），仓库表（t_ck），库存表（t_kc）3 、用户及安全设计数据表用户项目（t_xm）供应（t_gy）供应商（t_gys）零件（t_lj）库存（t_kc）仓库（t_ck）职工（t_zg）职工（work）无无无无增，查查增，查，改领导（lead）All All 查All All All All供应商（supp）无增，查All 无无无无密码错误多次账户锁定create profile lock_account limitfailed_login_attempts 3password_lock_time 1;密码的过期时间create profile password_life_time limitpassword_life_time 30password_grace_time 10;4、视图设计--1.职工视图create or replace view zg_st asselect * from t_zg;--2.仓库职工视图create or replace view ck_zg_st asselect t_zg.zgh 职工号, t_ck.ckh 仓库号, t_ck.mj 面积, t_ck.dhhm 电话号码from t_ck,t_zgwhere t_ck.ckh=t_zg.ckh;--3.仓库零件视图create or replace view ck_lj_st asselect t_kc.ckh 仓库号,t_lj.mc 名称, t_lj.gg 规格,t_kc.kcl 库存量from t_kc,t_ljwhere t_kc.ljh=t_lj.ljh;--4.项目视图create or replace view xmst asselect t_xm.xmh,t_gys.gysh,t_lj.ljh,t_xm.ys,t_gy.gylfrom t_gys,t_xm,t_lj,t_gywhere t_gy.gysh=t_gys.gysh and t_gy.ljh=t_lj.ljh and t_gy.xmh=t_xm.xmh;5、远程连接设计A机器创建连接Create database link hg conne ct to gly identified by “123” using ’10.65.247.164:1521/dba’;Create database link hmw connect to gly identified by “123” using ‘10.65.242.34:1521/orcl’;B机器创建连接Create database link hmw connect to gly identified by “123” using ‘10.65.242.34:1521/orcl’;Create database link wd connect to gly identified by “123” using ‘10.65.252.123:1521/orcl’;6、触发器设计A机器创建项目表，供应表，供应商表，零件表的（增，删，改）触发器，触发到C机器实现对A机器数据表数据备份到C机器B器创建库存表，仓库表，职工表的（增，删，改）触发器，触发到C机器实现对B机器数据表数据备份到C机器创建触发器实现A机器和B机器数据与C机器数据同步。

7、同义词设计Create or replace synonym hmw_ck for t_ck@hmw;Create or replace synonym hmw_kc for t_kc@hmw;Create or replace synonym hmw_zg for t_zg@hmw;Create or replace synonym t_xm for t_xm@wd;Create or replace synonym t_gy for t_gy@wd;Create or replace synonym t_gys for t_gys@wd;Create or replace synonym t_lj for t_lj@wd;四、数据库实施中的重难点1、数据库实施环境1.1硬件环境三台windows操作系统环境电脑（酷睿七代以上处理器，8G以上运行内存，2G以上独立显卡，64位操作系统）1.2软件环境Oracle 11g（Sqlplus，Net Aonfiguration Assistant，Net Manager）2、数据库实施中的重难点2、1创建触发器--库存表create or replace trigger tri_kcbeforeinsert or delete or update on t_kcfor each rowbeginif inserting theninsert into hmw_kc (ckh,ljh,kcl) values(:new.ckh,:new.ljh,:new.kcl);elsif deleting thendelete from hmw_kc where ljh=:old.ljh;elsif updating thenupdate hmw_kc set ckh=:new.ckh,kcl=:new.kcl where ljh=:old.ljh;end if;end tri_kc;--仓库表Create or replace trigger tri_ck before insert or update or delete on t_ckfor each rowbeginif inserting theninsert into hmw_ck(ckh,mj,dhhm) values(:new.ckh,:new.mj,:new.dhhm);elsif updating thenupdate hmw_ck set mj=:new.mj,dhhm=:new.dhhm where ckh=:old.ckh;elsif deleting thendelete from hmw_ck where ckh=:old.ckh;end if;end tri_ck;/--职工表create or replace trigger tri_zg before insert or update or delete on t_zgfor each rowbeginif inserting theninsert into hmw_zg values(:new.zgh,:new.xm,:new.nl,:new.zc,:new.ld,:new.ckh);elsif updating thenupdate hmw_zg set xm=:new.xm,nl=:new.nl,zc=:new.zc,ld=:new.ld,ckh=:new.ckh where zgh=:ol d.zgh;elsif deleting thendelete from hmw_zg where zgh=:old.zgh;end if;end tri_ck;/--库存表__零件表（库存表外键）create or replace trigger tri_kc_ljbefore insert on t_kcfor each rowdeclarei number;beginselect count(*) into i from t_lj where ljh=:new.ljh;if i=0 thendelete from t_kc where ljh=:old.ljh;dbms_output.put_line('插入失败！零件号不存在！');end if;end;2、2创建存储过程--仓库--插入create or replace procedure insert_ck(ckh1 in varchar2,mj1 in number,dhhm1 in varchar2) asi number;beginselect count(*) into i from t_ck where ckh=ckh1;if i=1 thendbms_output.put_line('该创库号已存在，插入失败！'); elseinsert into t_ck values(ckh1,mj1,dhhm1);dbms_output.put_line('数据插入成功！');end if;end;--修改create or replace procedure update_ck(ckh1 in varchar2,mj1 in number,ddhm1 in varchar2) asi number;beginselect count(*) into i from t_ck where ckh=ckh1;if i=1 thenupdate t_ck set mj=mj1,dhhm=ddhm1 where ckh=ckh1; commit;dbms_output.put_line('数据修改成功！');elsedbms_output.put_line('该仓库号不存在，更新失败！'); end if;end;--删除create or replace procedure delete_ck(ckh1 in varchar2) asi int;beginselect count(*) into i from t_ck where ckh=ckh1;if i=1 thendelete from t_ck where ckh=ckh1;commit;dbms_output.put_line('数据删除成功！');elsedbms_output.put_line('不存在该仓库号！无法删除！'); end if;end;--职工--插入create or replace procedure insert_zg(zgh1 in varchar2,xm1 in varchar2,nl1 in number,zc1 varchar2,ld1 varchar2,ckh1 varchar2) asi int;j int;beginselect count(*) into i from t_zg where zgh=zgh1;select count(*) into j from t_zg where zgh=ld1;if j=1 and i=0 theninsert into t_zg values(zgh1,xm1,nl1,zc1,ld1,ckh1);commit;dbms_output.put_line('数据插入成功！');elsif j=1 thendbms_output.put_line('职工号已存在，无法插入！');elsedbms_output.put_line('该领导不存在，无法插入！');end if;end;--修改create or replace procedure update_zg(zgh1 in varchar2,xm1 in varchar2,nl1 in number,zc1 varchar2,ld1 varchar2,ckh1 varchar2) asi int;j int;k int;beginselect count(*) into i from t_zg where zgh=zgh1;select count(*) into j from t_zg where zgh=ld1;select count(*) into k from t_ck where ckh=ckh1;if j=1 and i=1 and k=1 thenupdate t_zg set xm=xm1,nl=nl1,zc=zc1,ld=ld1,ckh=ckh1 where zgh=zgh1; commit;dbms_output.put_line('数据修改成功！');elsedbms_output.put_line('修改失败，请检查数据是否准确！'); end if;end;--删除create or replace procedure delete_zg(zgh1 in varchar2) asi int;beginselect count(*) into i from t_zg where zgh=zgh1;if i=1 thendelete from t_zg where zgh=zgh1;commit;dbms_output.put_line('数据删除成功！');elsedbms_output.put_line('该数据不存在，无法删除！');end if;end;--库存create or replace procedure insert_kc(ckh1 in varchar2,ljh1 in varchar2,kcl1 in number) asi int;j int;beginselect count(*) into i from t_ck where ckh=ckh1;select count(*) into j from t_lj where ljh=ljh1;if i=1 and j=1 theninsert into t_kc values(ckh1,ljh1,kcl1);commit;dbms_output.put_line('数据插入成功！');elsif i=1 thendbms_output.put_line('该零件号不存在，无法插入！'); elsif j=1 thendbms_output.put_line('该仓库号不存在，无法插入！'); end if;end;--删除create or replace procedure delete_kc(ckh1 in varchar2,ljh1 in varchar2) asi int;beginselect count(*) into i from t_kc where ckh=ckh1 and ljh=ljh1;if i=1 thendelete from t_kc where ckh=ckh1 and ljh=ljh1;commit;dbms_output.put_line('数据删除成功！');elsedbms_output.put_line('该数据不存在，无法删除！');end if;end;2、3数据备份与恢复项目（t_xm）exp gly/123@wd_orcl file=f:\backup\万代\wd_backup_xm.dmp tables=t_xm (这个是从wd_orcl（链接另外一台服务器在自己电脑上给他命的名）数据库导出表为t_xm 的表保存在file后面的那个dmp文件那个wd_orcl换成orcl表示从本机的数据库导出) imp gly/123@wd_orcl file=f:\backup\万代\wd_backup_xm.dmp fromuser=gly@wd_orcl tables=t_xm commit=y ignore=y;(这个是把来自wd_orcl文件为f:\backup\万代\wd_backup_xm.dmp的文件导入数据库为wd_orcl的表为t_xm里面这里面的wd_orcl表示要导入数据库的名称file与上面导出的文件夹一致fromuser一定要写与上面导出数据库的用户保持一致commit=y表示提交确认后面的ignore=y表示忽略错误)零件（t_lj）exp gly/123@wd_orcl file=f:\backup\wd_backup_lj.dmp tables=t_lj;imp gly/123@wd_orcl file=f:\backup\wd_backup_lj.dmp fromuser=gly@wd_orcl tables=t_lj commit=y ignore=y;供应商（t_gys）exp gly/123@wd_orcl file=f:\backup\万代\wd_backup_gys.dmp tables=t_gys imp gly/123@wd_orcl file=f:\backup\wd_backup_gys.dmp fromuser=gly@wd_orcl tables=t_gys commit=y ignore=y;供应（t_gy）exp gly/123@wd_orcl file=f:\backup\万代\wd_backup_gy.dmp tables=t_gyimp gly/123@wd_orcl file=f:\backup\wd_backup_gy.dmp fromuser=gly@wd_orcl tables=t_gy commit=y ignore=y;仓库（t_ck）exp gly/123@hg_dba file=f:\backup\贺贵\hg_backup_ck.dmp tables=t_ck(这个是从hg_dba（链接另外一台服务器在自己电脑上给他命的名）数据库导出表为t_ck 的表保存在file后面的dmp文件那个hg_dba可以换成orcl表示从本机的数据库导出) imp gly/123@hg_dba file=f:\backup\贺贵\hg_backup_ck.dmp fromuser=gly@hg_dba tables=t_ck commit=y ignore=y;(这个是把来自hg_dba文件为f:\backup\贺贵\hg_backup_ck.dmp的文件导入数据库为hg_dba的表为t_ck里面这里面的hg_dba表示要导入数据库的名称file与上面导出的文件夹一致fromuser一定要写与上面导出数据库的用户保持一致commit=y表示提交确认后面的ignore=y表示忽略错误)库存(t_kc)exp gly/123@hg_dba file=f:\backup\贺贵\hg_backup_kc.dmp tables=t_kcimp gly/123@hg_dba file=f:\backup\贺贵\hg_backup_kc.dmp fromuser=gly@hg_dba tables=t_kc commit=y ignore=y;职工（t_zg）exp gly/123@hg_dba file=f:\backup\贺贵\hg_backup_zg.dmp tables=t_zgimp gly/123@hg_dba file=f:\backup\贺贵\wd_backup_zg.dmp fromuser=gly@hg_dba tables=t_zg commit=y ignore=y;五总结。