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

生产管理系统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','xxx',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','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,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','大学','三年');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)values ('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','专科','二年');Insert 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 ('00305','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 EMP (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','上海市南京路','(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,EmpName,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)-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