delete from contact_details;

delete from order;

delete from product;

drop table contact_details cascade constraint purge;

drop table order cascade constraint purge;

drop table product cascade constraint purge;

create table contact_details (
  contact_name varchar(20) not null, /* primary key */
  b_name varchar(20) not null, /* primary key */
  address varchar(60),
  pcode number(4),
  phone number(10),
  fax number(12),
  email varchar(40),
  PRIMARY KEY (contact_name,b_name)
);

create table order (
  o_id varchar(6) not null, /* primary key */
  prev_id varchar(6) 
  press char(10),
  o_date DATE,
  o_qty varchar(30),
  o_desc varchar(100),
  PRIMARY KEY (o_id)
);

create table product (
  o_id char(6) not null, /* foreign key constraint */
  stckall_qty1 varchar(10),
  stckall_qty2 varchar(10),
  stckall_gsm1 varchar(5),
  stckall_gsm2 varchar(5),
  stckall_desc1 varchar(30),
  PRIMARY KEY (o_id), 
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table stock1 (
  o_id char(6) not null, /* foreign key constraint */
  stck_desc1 varchar(20),
  stck_desc2 varchar(20),
  stck_desc3 varchar(20),
  stck_desc4 varchar(20),
  stckop01 varchar(5),
  stckqty1 varchar(5),
  stckqty2 varchar(5),
  stckqty3 varchar(5),
  stckqty4 varchar(5),
  stck_cost1 varchar(10),
  PRIMARY KEY (o_id),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table stock2 (
  o_id char(6) not null, /* foreign key constraint */
  stck_desc1 varchar(20),
  stck_desc2 varchar(20),
  stck_desc3 varchar(20),
  stck_desc4 varchar(20),
  stckop01 varchar(5), /* primary key */
  stckqty01 varchar(5),
  stckqty02 varchar(5),
  stckqty03 varchar(5),
  stckqty04 varchar(5),
  stck_cost1 varchar(10),
  PRIMARY KEY (o_id, stckop01),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table stock2 (
  o_id char(6) not null, /* foreign key constraint */
  stck_desc1 varchar(20),
  stck_desc2 varchar(20),
  stck_desc3 varchar(20),
  stck_desc4 varchar(20),
  stckop02 varchar(5), /* primary key */
  stckqty11 varchar(5),
  stckqty12 varchar(5),
  stckqty13 varchar(5),
  stckqty14 varchar(5),
  stck_cost2 varchar(10),
  PRIMARY KEY (o_id,stckop02),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table stock3 (
  o_id char(6) not null, /* foreign key constraint */
  stck_desc1 varchar(20),
  stck_desc2 varchar(20),
  stck_desc3 varchar(20),
  stck_desc4 varchar(20),
  stckop03 varchar(5), /* primary key */
  stckqty21 varchar(5),
  stckqty22 varchar(5),
  stckqty23 varchar(5),
  stckqty24 varchar(5),
  stck_cost3 varchar(10),
  PRIMARY KEY (o_id,stckop03),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table stock4 (
  o_id char(6) not null, /* foreign key constraint */
  stck_desc1 varchar(20),
  stck_desc2 varchar(20),
  stck_desc3 varchar(20),
  stck_desc4 varchar(20),
  stckop04 varchar(5), /* primary key */
  stckqty31 varchar(5),
  stckqty32 varchar(5),
  stckqty33 varchar(5),
  stckqty34 varchar(5),
  stck_cost4 varchar(10),
  PRIMARY KEY (o_id,stckop04),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table print (
  o_id char(6) not null, /* foreign key constraint */
  mkrdy10 char(6), 
  mkrdy20 char (6),
  plates char(7),
  runfc char(8),
  run2col char(8),
  wshup10 char(8),
  wshup25 char(8),
  pms10 char (8),
  pms25 char (8),
  metallic10 char(8),
  metallic25 char(8),
  score_no_perf varchar(10),
  sprunFC char(8),
  sprun2Col char(8),
  subprint varchar(15),
  cellomatt varchar(15),
  cellogloss varchar(15),
  PRIMARY KEY (o_id),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table finishing (
  o_id char(6) not null, /* foreign key constraint */
  trim varchar(7),
  shrink varchar(7),
  boxes varchar(7),
  folding varchar(8),
  folding_desc varchar(20),
  horizon varchar(20),
  pad varchar(10),
  ncrPad varchar(10),
  drill varchar(10),
  pad_desc varchar(20),
  freight varchar(15),
  post varchar(15),
  deliver varchar(15)
  delivery_desc varchar(20)
  staple varchar(15),
  prfctbnd varchar(10),
  sctnswn varchar(10),
  dbllpwre varchar(15),
  staple_desc varchar(20),
  subttl_Op1 varchar(8),
  subttl_Op2 varchar(8),
  subttl_Op3 varchar(8),
  subttl_Op4 varchar(8),
  PRIMARY KEY (o_id),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table cost1 (
  o_id char(6) not null, /* foreign key constraint */
  subttl_op1 varchar(7), /* foreign key constraint */
  overhd_agnt1 char(4),
  overhd_nrml1 char(4),
  prepress1 varchar(10),
  ignite_artwrk1 varchar(10),
  GST_op1 varchar (7),
  total_op1 varchar (7), /* primary key */
  PRIMARY KEY (o_id, total_op1),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
  FOREIGN KEY (subttl1) REFERENCES finishing1 (subttl1)
);

create table cost2 (
  o_id char(6) not null, /* foreign key constraint */ 
  subttl_op2 varchar(7),
  overhd_agnt2 char(4),
  overhd_nrml2 char(4),
  prepress2 varchar(10),
  ignite_artwrk2 varchar(10),
  GST_op2 varchar (7),
  total_op2 varchar (7), /* primary key */
  PRIMARY KEY (o_id, total_op2),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table cost3 (
  o_id char(6) not null, /* foreign key constraint */ 
  subttl_op3 varchar(7),
  overhd_agnt3 char(4),
  overhd_nrml3 char(4),
  prepress3 varchar(10),
  ignite_artwrk3 varchar(10),
  GST_op3 varchar (7),
  total_op3 varchar (7), /* primary key */
  PRIMARY KEY (o_id, total_op3),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);

create table cost4 (
  o_id char(6) not null, /* foreign key constraint */ 
  subttl_op4 varchar(7),
  overhd_agnt4 char(4),
  overhd_nrml4 char(4),
  prepress4 varchar(10),
  ignite_artwrk4 varchar(10),
  GST_op4 varchar (7),
  total_op4 varchar (7), /* primary key */
  PRIMARY KEY (o_id, total_op4),
  FOREIGN KEY (o_id) REFERENCES order (o_id)
);


insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Roz', 'Alex Somlyay MP', 'PO Box 351', '', , , '');

insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Christine Kuhnert', 'Alpha Dynamic', 'PO Box 799', '', , , '');

insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Peter & Mary', 'Alpha Dynamic', 'PO Box 700', '', , , '');

insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Loretta', 'Biolytix Technologies', 'PO Box 591', '', , , '');

insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Sam', 'Blacklaw Civil Contractors', '100 Sandalwood Lane', '', , , '');

insert into contact_details (contact_name, b_name, address, pcode, phone, fax, email)
values ('Helen', 'Bundilla Pest Control', 'PO Box 5194', '', , , '');


set line 150;
set pagesize 20;

commit;