生产管理系统实验报告.docx

《生产管理系统实验报告.docx》由会员分享，可在线阅读，更多相关《生产管理系统实验报告.docx（41页珍藏版）》请在三一办公上搜索。

1、生产管理系统1 E-R图2数据字典1.EMP Table：字段名称数据元素名称字段类型字段长度键值empid员工编号VARCHAR210PKempname员工姓名VARCHAR210empadd员工地址VARCHAR220empphone员工电话VARCHAR212email电子邮件VARCHAR220mgr上司VARCHAR26hiredate雇用日期DATEsal薪资NUMBER10comm佣金NUMBER7,2pro职称VARCHAR212deptno部门编号NUMBER2FKeduca学历VARCHAR212exper经历VARCHAR2202.DEPART Table：字段名称数据元

2、素名称字段类型字段长度键值deptno部门编号NUMBER2PKdeptname部门名称VARCHAR210loc地点VARCHAR2133.PROSTOCK Table：字段名称数据元素名称字段类型字段长度键值proid产品编号VARCHAR210PKproname产品名称VARCHAR220stiqty尚有数量NUMBER10insqty需补数量NUMBER10Val产品单价NUMBER104.CUSTOMER Table：字段名称数据元素名称字段类型字段长度键值custid客户编号VARCHAR210PKcustname客户姓名VARCHAR222custadd客户地址VARCHAR22

3、0custphone客户电话VARCHAR212custfax客户传真VARCHAR2125.ORD Table：字段名称数据元素名称字段类型字段长度键值ordid订单编号VARCHAR210PKproid产品编号VARCHAR210FKcustid客户编号VARCHAR210FKo_qty订购数量NUMBER20orddate订购日期DATE8d_date交货日期DATE8Is_delivery是否出货NUMBER1Is_invoice是否需要发票NUMBER16.WIP Table：字段名称数据元素名称字段类型字段长度键值wipid工令编号VARCHAR210PKproid产品编号VARC

4、HAR210FKw_qty数量NUMBER10Is_finish是否完成NUMBER17.INVOICE Table：字段名称数据元素名称字段类型字段长度键值ordid订单编号VARCHAR210FKinvid发票编号VARCHAR210PKproid产品编号VARCHAR210FKmoney金额NUMBER108.DELIVERY Table：字段名称数据元素名称字段类型字段长度键值proid产品编号VARCHAR210FKordid订单编号VARCHAR210FKcarno出车编号VARCHAR210delino出货编号VARCHAR210PK3授权情况一览（为对此人授权）1. EMP T

5、able:权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科2.DEPART Table：权限人员SelectInsertUpdateAlterDel

6、eteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科3.PROSTOCK Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督

7、室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科4.CUSTOMER Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Bea

8、u管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科5.ORD Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部

9、Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科6.WIP Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Da

10、nny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科7.INVOICE Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton

11、 品保科Ian制造部Hank制造部Ira压合科Abner压合科Jack内务科Ade内务科8.DELIVERY Table：权限人员SelectInsertUpdateAlterDeleteAbel总经理室Ivan总经理室Alan监督室Henry监督室Bard管理部Harry管理部Bart人事科Eden管理部Beau管理部Carl会计科Ed会计科Cliff会计科Dan业务部Frank业务部Hermes生管科Des生管科Gale生管科Beck生管科Gary生管科Danny生管科Ford生管科Cash品保科Eddy 品保科Barton 品保科Ian制造部Hank制造部Ira压合科Abner压合科Ja

12、ck内务科Ade内务科4创建表，序列4.1表Depart:create table depart(DeptNo number(2) constraint pk_depart primary key,DeptName varchar2(10),LOC varchar2(13);/Emp：create table emp(EmpId varchar2(10) constraint pk_emp primary key,EmpName varchar2(10) NOT NULL,EmpAdd varchar2(20),EmpPhone varchar(12),Email varchar2(20),M

13、GR varchar2(10),HireDate date,COMM number(7,2),DeptNo number(2) not null,Pro varchar2(12),Sal number(6),Educa varchar2(12),Exper varchar2(20),CONSTRAINT fk_emp_DeptNoFOREIGN KEY (DeptNo) REFERENCES depart(DeptNo);Customer:create table customer(custid varchar2(10) constraint pk_customer primary key,c

14、ustname varchar2(22),custadd varchar2(20),custphone varchar2(12),custfax varchar2(12);Prostock:create table prostock(Proid varchar2(10) constraint pk_prostock primary key,ProName varchar(20),Val number(10),Stiqty number(10),Insqty number(10);Ord:create table ord(ordid varchar2(10) constraint pk_ord

15、primary key,proid varchar2(10) not null,custid varchar2(10) not null,o_qty number(20),orddate date,d_date date,money number(10),is_invoice number(1),is_delivery number(1),constraint fk_ord_proidforeign key(proid) REFERENCES prostock(proid),constraint fk_ord_custidforeign key(custid) REFERENCES custo

16、mer(custid);Wip:create table wip(wipid varchar2(10) constraint pk_wip primary key,proid varchar2(10) not null,w_qty number(20),is_finish number(1),constraint fk_wip_proidforeign key(proid) REFERENCES prostock(proid);Delivery:create table delivery(delino varchar(10) constraint pk_delivery primary key

17、,proid varchar(10) not null,ordid varchar(10) not null,carno varchar(10),is_invoice number(1),constraint fk_delivery_proidforeign key(proid) REFERENCES prostock(proid),constraint fk_delivery_ordidforeign key(ordid) REFERENCES ord(ordid);Invoice:create table invoice(invid varchar2(10) constraint pk_i

18、nvoice primary key,ordid varchar2(10),proid varchar2(10),allmoney number(10),in_date date,constraint fk_invoice_proidforeign key(proid) REFERENCES prostock(proid),constraint fk_invoice_ordidforeign key(ordid) REFERENCES ord(ordid);4.2序列实现表主键自动增长的对应表的序列Customer_seq:create sequence customer_seq start

19、with 1increment by 1nomaxvaluenocyclenocache;delivery_seq:create sequence delivery_seq start with 1increment by 1nomaxvaluenocyclenocache;invoice；create sequence invoice_seq start with 1increment by 1nomaxvaluenocyclenocache;prostock_seq:create sequence prostock_seq start with 1increment by 1nomaxva

20、luenocyclenocache;wip_seq；create sequence wip_seq start with 1increment by 1nomaxvaluenocyclenocache;5插入数据Depart:insert into Depart(DeptNo,DeptName,LOC) values (11,总经理室,北京);insert into Depart(DeptNo,DeptName,LOC) values (12,监督室,北京);insert into Depart(DeptNo,DeptName,LOC) values (21,管理部,郑州);insert in

21、to Depart(DeptNo,DeptName,LOC) values (31,会计科,北京);insert into Depart(DeptNo,DeptName,LOC) values (22,业务部,北京);insert into Depart(DeptNo,DeptName,LOC) values (32,生管科,郑州);insert into Depart(DeptNo,DeptName,LOC) values (33,品保科,郑州);insert into Depart(DeptNo,DeptName,LOC) values (23,制造部,郑州);insert into De

22、part(DeptNo,DeptName,LOC) values (34,压合科,郑州);insert into Depart(DeptNo,DeptName,LOC) values (35,内务科,郑州);emp:Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper) values (00101,Abel,北京市海淀区,(010)-111111,xxx,null,to_date(06/05/90,dd/mm/yy),null,11,总经理,180000

23、,研究所,五年); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00102,Ivan,上海市四川路,(021)-111111,xxx,Abel,to_date(13/01/92,dd/mm/yy),200,11,特助,120000,研究所,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,

24、Exper)values (00201,Alan,郑州市人民路,(037)-111111,xxx,Abel,to_date(23/06/91,dd/mm/yy),null,12,经理,120000,研究所,三年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00202,Henry,郑州市中山路,(037)-111112,xxx,Alan,to_date(05/02/92,dd/mm/yy),600,12,监督员,40000,大学

25、,三年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00301,Bard,上海市南京路,(021)-11112,xxx,Abel,to_date(26/03/91,dd/mm/yy),null,21,经理,70000,大学,三年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)v

26、alues (00302,Harry,南京市六合区,(021)-111112,xxx,Bard,to_date(15/06/91,dd/mm/yy),null,21,科长,50000,大学,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00303,Bart,广州市人民路,(020)-111111,xxx,Harry,to_date(15/12/91,dd/mm/yy),400,21,人事,30000,专科,二年);Inse

27、rt INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00304,Eden,广州市中山路,(020)-111112,xxx,Harry,to_date(08/01/92,dd/mm/yy),500,21,总务,30000,大学,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (0

28、0305,Beau,北京市海淀区,(010)-111111,xxx,Harry,to_date(16/01/92,dd/mm/yy),300,21,采购,30000,大学,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00401,Carl,郑州市中山路,(037)-111112,xxx,Bard,to_date(18/04/91,dd/mm/yy),null,31,经理,70000,大学,四年);Insert INTO E

29、MP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00402,Ed,石家庄市人民路,(031)-11111,xxx,Carl,to_date(28/09/91,dd/mm/yy),500,31,成会,40000,大学,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00403,Cliff,上

30、海市南京路,(021)-11112,xxx,Carl,to_date(17/10/91,dd/mm/yy),300,31,普会,35000,大学,一年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00501,Dan,广州市人民路,(020)-111111,xxx,Abel,to_date(24/07/91,dd/mm/yy),null,22,经理,80000,大学,三年);Insert INTO EMP (EmpID,EmpN

31、ame,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00502,Frank,广州市中山路,(020)-111112,xxx,Dan,to_date(21/03/92,dd/mm/yy),1000,22,业助,33000,专科,一年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00601,Hermes,上海市四川路,(021

32、)-111111,xxx,Abel,to_date(29/04/92,dd/mm/yy),null,32,科长,40000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00602,Des,北京市中山区,(010)-111112,xxx,Hermes,to_date(19/06/92,dd/mm/yy),600,32,生管,31000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00603,Gale,郑州市中山路,(037)-111112,xxx,Hermes,to_date(02/12/93,dd/mm/yy),700,32,生管,40000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireD