drop table suppliers cascade constraints;
create table suppliers (
  sid     number(4),
  sname   varchar2(30),
  address varchar2(50),
  primary key (sid)
);
drop table parts cascade constraints;
create table parts (
  pid   number(6),
  pname varchar2(30),
  color varchar2(20),
  primary key (pid)
);
drop table catalogue cascade constraints;
create table catalogue (
  sid   number(4),
  pid   number(6),
  cost  number(10,2),
  primary key (sid,pid),
  foreign key (sid) references suppliers,
  foreign key (pid) references parts
);

delete from catalogue;
delete from suppliers;
delete from parts;
insert into suppliers values (1000,'Jones','221 Packer Street');
insert into suppliers values (1001,'Yosemite Sham','100 Green Street');
insert into suppliers values (1002,'Blake','111 Bay Street');
insert into suppliers values (1003,'Smith','110 Cheese Street');
insert into parts values (100000,'nut','red');
insert into parts values (100001,'bolt','blue');
insert into parts values (100002,'screw','red');
insert into parts values (100003,'flange','green');
insert into parts values (111111,'flange1','green');
insert into parts values (222222,'flange2','green');
insert into parts values (333333,'flange3','green');
insert into parts values (444444,'flange4','green');
insert into catalogue values(1000,100001,10.50);
insert into catalogue values(1000,100003,8.50);
insert into catalogue values(1001,100000,9.50);
insert into catalogue values(1001,100001,7.50);
insert into catalogue values(1001,100002,9.50);
insert into catalogue values(1001,100003,8.50);
insert into catalogue values(1002,100000,8.50);
insert into catalogue values(1002,100002,8.50);
insert into catalogue values(1003,100002,8.50);
insert into catalogue values(1000,111111,88.50);
insert into catalogue values(1001,111111,78.50);
insert into catalogue values(1002,111111,98.50);
insert into catalogue values(1003,111111,188.50);
insert into catalogue values(1000,222222,108.50);
insert into catalogue values(1001,222222,208.50);
insert into catalogue values(1002,222222,128.50);
insert into catalogue values(1003,222222,138.50);
insert into catalogue values(1000,333333,108.50);
insert into catalogue values(1001,333333,198.50);
insert into catalogue values(1000,444444,118.50);
insert into catalogue values(1002,444444,398.50);
