当前位置：搜档网 › 生产管理系统实验报告

生产管理系统实验报告

生产管理系统1．E-R图

2．数据字典

3．授权情况一览（★为对此人授权）1.EMP Table:

2.DEPART Table：

3.PROSTOCK Table：

4.CUSTOMER Table：

5.ORD Table：

6.WIP Table：

7.INVOICE Table：

8.DELIVERY Table：

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_DeptNo

FOREIGN 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_proid

foreign key(proid) REFERENCES prostock(proid), constraint fk_ord_custid

foreign 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_proid

foreign 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_proid

foreign key(proid) REFERENCES prostock(proid), constraint fk_delivery_ordid

foreign 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_proid

foreign key(proid) REFERENCES prostock(proid), constraint fk_invoice_ordid

foreign key(ordid) REFERENCES ord(ordid)

);

4.2序列

实现表主键自动增长的对应表的序列

Customer_seq:

create sequence customer_seq

start with 1

increment by 1

nomaxvalue

nocycle

nocache;

delivery_seq:

create sequence delivery_seq

start with 1

increment by 1

nomaxvalue

nocycle

nocache;

invoice；

create sequence invoice_seq

start with 1

increment by 1

nomaxvalue

nocycle

nocache;

prostock_seq:

create sequence prostock_seq

start with 1

increment by 1

nomaxvalue

nocycle

nocache;

wip_seq；

create sequence wip_seq

start with 1

increment by 1

nomaxvalue

nocycle

nocache;

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: