drop table suppliers cascade constraints;
create table suppliers (
  sno varchar2(10),
  sname varchar2(20),
  city varchar2(20),
  primary key (sno)
);
drop table parts cascade constraints;
create table parts (
  pno varchar2(10),
  pname varchar2(20),
  color varchar2(20),
  city varchar2(20),
  primary key (pno)
);
drop table projects cascade constraints;
create table projects (
  jno varchar2(10),
  jname varchar2(20),
  city varchar2(20),
  primary key (jno)
);
drop table spj cascade constraints;
create table spj (
  sno varchar2(10),
  pno varchar2(10),
  jno varchar2(10),
  qty integer,
  primary key (sno,pno,jno),
  foreign key (sno) references suppliers,
  foreign key (pno) references parts,
  foreign key (jno) references projects
);
insert into suppliers values ('S1','Jones','Atlanta');
insert into suppliers values ('S2','Smith','Duluth');
insert into suppliers values ('S3','Blake','Marietta');
insert into suppliers values ('S4','Charles','Marietta');
insert into suppliers values ('S5','David','Marietta');
insert into parts values ('P1','Nut','Red','Duluth');
insert into parts values ('P2','Bolt','Blue','Marietta');
insert into parts values ('P3','Screw','Red','Dunwoody');
insert into parts values ('P4','Axle','Green','Marietta');
insert into projects values ('J1','Bicycles','Duluth');
insert into projects values ('J2','Motorcycles','Dunwoody');
insert into projects values ('J3','Auto','Duluth');
insert into spj values ('S1','P1','J1',100);
insert into spj values ('S1','P2','J2',100);
insert into spj values ('S1','P1','J2',100);
insert into spj values ('S2','P1','J1',100);
insert into spj values ('S2','P3','J1',100);
insert into spj values ('S2','P3','J2',100);
insert into spj values ('S2','P3','J3',100);
insert into spj values ('S3','P1','J1',100);
insert into spj values ('S3','P2','J2',100);
insert into spj values ('S4','P1','J1',100);
insert into spj values ('S4','P2','J3',100);
insert into spj values ('S4','P3','J1',100);
insert into spj values ('S5','P1','J1',100);
insert into spj values ('S5','P1','J2',100);
insert into spj values ('S5','P1','J3',100);
