生产管理知识_生产管理系统实验报告

生产管理系统1 E-R图2数据字典1.EMP Table：字段名称数据元素名称字段类型字段长度键值empid员工编号VARCHAR210PKempname员工姓名VARCHAR210empadd员工地址VARCHAR220empphone员工电话VARCHAR212email电子邮件VARCHAR220mgr上司VARCHAR26hiredate雇用日期DATEsal薪资NUMBER10comm佣金NUMBER7,2pro职称VARCHAR212deptno部门编号NUMBER2FKeduca学历VARCHAR212exper经历VARCHAR2202.DEPART Table：字段名称数据元素名称字段类型字段长度键值deptno部门编号NUMBER2PKdeptname部门名称VARCHAR210loc地点VARCHAR2133.PROSTOCK Table：字段名称数据元素名称字段类型字段长度键值proid产品编号VARCHAR210PKproname产品名称VARCHAR220stiqty尚有数量NUMBER10insqty需补数量NUMBER10Val产品单价NUMBER104.CUSTOMER Table：字段名称数据元素名称字段类型字段长度键值custid客户编号VARCHAR210PKcustname客户姓名VARCHAR222custadd客户地址VARCHAR220custphone客户电话VARCHAR212custfax客户传真VARCHAR2125.ORD Table：字段名称数据元素名称字段类型字段长度键值ordid订单编号VARCHAR210PKproid产品编号VARCHAR210FKcustid客户编号VARCHAR210FKo_qty订购数量NUMBER20orddate订购日期DATE8d_date交货日期DATE8Is_delivery是否出货NUMBER1Is_invoice是否需要发票NUMBER16.WIP Table：字段名称数据元素名称字段类型字段长度键值wipid工令编号VARCHAR210PKproid产品编号VARCHAR210FKw_qty数量NUMBER10Is_finish是否完成NUMBER17.INVOICE Table：字段名称数据元素名称字段类型字段长度键值ordid订单编号VARCHAR210FKinvid发票编号VARCHAR210PKproid产品编号VARCHAR210FKmoney金额NUMBER108.DELIVERY Table：字段名称数据元素名称字段类型字段长度键值proid产品编号VARCHAR210FKordid订单编号VARCHAR210FKcarno出车编号VARCHAR210delino出货编号VARCHAR210PK3授权情况一览（为对此人授权）1. EMP 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压合科Jack内务科Ade内务科2.DEPART 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压合科Jack内务科Ade内务科3.PROSTOCK 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压合科Jack内务科Ade内务科4.CUSTOMER 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压合科Jack内务科Ade内务科5.ORD 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压合科Jack内务科Ade内务科6.WIP 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压合科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 品保科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压合科Jack内务科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),MGR 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,custname 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 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 customer(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,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_invoice 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 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 1nomaxvaluenocyclenocache;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 into 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 Depart(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,,null,to_date(06/05/90,dd/mm/yy),null,11,总经理,180000,研究所,五年); Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00102,Ivan,上海市四川路,(021)-111111,,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,Exper)values (00201,Alan,郑州市人民路,(037)-111111,,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,,Alan,to_date(05/02/92,dd/mm/yy),600,12,监督员,40000,大学,三年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00301,Bard,上海市南京路,(021)-11112,,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)values (00302,Harry,南京市六合区,(021)-111112,,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,,Harry,to_date(15/12/91,dd/mm/yy),400,21,人事,30000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00304,Eden,广州市中山路,(020)-111112,,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 (00305,Beau,北京市海淀区,(010)-111111,,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,,Bard,to_date(18/04/91,dd/mm/yy),null,31,经理,70000,大学,四年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00402,Ed,石家庄市人民路,(031)-11111,,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,上海市南京路,(021)-11112,,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,,Abel,to_date(24/07/91,dd/mm/yy),null,22,经理,80000,大学,三年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00502,Frank,广州市中山路,(020)-111112,,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)-111111,,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,,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,,Hermes,to_date(02/12/93,dd/mm/yy),700,32,生管,40000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00604,Beck,石家庄市人民路,(031)-11111,,Hermes,to_date(25/03/93,dd/mm/yy),500,32,助理,25000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00605,Gary,上海市四川路,(021)-111111,,Hermes,to_date(03/09/94,dd/mm/yy),800,32,仓管,25000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00606,Danny,北京市海淀区,(010)-111111,,Hermes,to_date(18/05/93,dd/mm/yy),400,32,外务,30000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00607,Ford,上海市四川路,(021)-111111,,Hermes,to_date(23/02/94,dd/mm/yy),600,32,外务,25000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00701,CASH,石家庄市人民路,(031)-11111,,Abel,to_date(15/11/93,dd/mm/yy),null,33,科长,40000,专科,四年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00702,Eddy,上海市南京路,(021)-11112,,CASH,to_date(09/07/94,dd/mm/yy),300,33,QA,25000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00703,Barton,南京市六合区,(021)-111112,,CASH,to_date(02/06/95,dd/mm/yy),700,33,QC,23000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00801,Ian,北京市海淀区,(010)-111111,,Abel,to_date(10/12/92,dd/mm/yy),null,23,副理,60000,专科,五年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00802,Hank,上海市南京路,(021)-11112,,Ian,to_date(06/03/95,dd/mm/yy),1000,23,工务,32000,专科,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00803,Ira,上海市四川路,(021)-111111,,Ian,to_date(26/08/94,dd/mm/yy),null,34,科长,38000,高中,八年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00804,Abner,郑州市人民路,(037)-111111,,Ira,to_date(10/09/95,dd/mm/yy),900,34,领班,33000,高中,二年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00805,Jack,郑州市中山路,(037)-111112,,Ian,to_date(16/10/93,dd/mm/yy),null,35,科长,39000,专科,六年);Insert INTO EMP (EmpID,EmpName,EmpAdd,EmpPhone,EMail,MGR,HireDate,COMM,DeptNo,Pro,Sal,Educa,Exper)values (00806,Ade,石家庄市人民路,(031)-11111,,Jack,to_date(19/02/95,dd/mm/yy),800,35,领班,32000,高中,二年);prostock；insert into prostock(ProName,Val,Stiqty,Insqty) values (主板,400,0,20);insert into prostock(ProName,Val,Stiqty,Insqty) values (处理器,1000,10,0);insert into prostock(ProName,Val,Stiqty,Insqty) values (内存,300,20,0);insert into prostock(ProName,Val,Stiqty,Insqty) values (硬盘,500,30,0);insert into prostock(ProName,Val,Stiqty,Insqty) values (显卡,100