drop table sp;
drop table s;
drop table p;


CREATE TABLE p (
    n_det character(6) PRIMARY KEY,
    name character(20),
    cvet character(20),
    ves integer,
    town character(20)
);

CREATE TABLE j (
    n_izd character(6) PRIMARY KEY,
    name character(20),
    town character(20)
);

CREATE TABLE s (
    n_post character(6) PRIMARY KEY,
    name character(20),
    reiting integer,
    town character(20)
);

CREATE TABLE spj (
    n_post character(6) NOT NULL,
    n_det character(6) NOT NULL,
    n_izd character(6) NOT NULL,
    kol integer NOT NULL CHECK (kol > 0)
) WITH OIDS;;

INSERT INTO s VALUES('S1','Cмит',20,'Лондон');
INSERT INTO s VALUES('S2','Джонс',10,'Париж');
INSERT INTO s VALUES('S3','Блейк',30,'Париж'); 
INSERT INTO s VALUES('S4','Кларк',20,'Лондон');
INSERT INTO s VALUES('S5','Адамс',30,'Афины');

INSERT INTO p VALUES('P1','Гайка','Красный',12,'Лондон');
INSERT INTO p VALUES('P2','Болт','Зеленый',17,'Париж');
INSERT INTO p VALUES('P3','Винт','Голубой',17,'Рим');
INSERT INTO p VALUES('P4','Винт','Красный',14,'Лондон');
INSERT INTO p VALUES('P5','Кулачок','Голубой',12,'Париж');
INSERT INTO p VALUES('P6','Блюм','Красный',19,'Лондон');

INSERT INTO j VALUES('J1','Жесткий диск','Париж');
INSERT INTO j VALUES('J2','Перфоратор','Рим');
INSERT INTO j VALUES('J3','Cчитыватель','Афины');
INSERT INTO j VALUES('J4','Принтер','Афины');
INSERT INTO j VALUES('J5','Флоппи-диск','Лондон');
INSERT INTO j VALUES('J6','Терминал','Осло');
INSERT INTO j VALUES('J7','Лента','Лондон');

INSERT INTO spj VALUES('S1','P1','J1',200 );
INSERT INTO spj VALUES('S1','P1','J4',700 );
INSERT INTO spj VALUES('S2','P3','J1',400 );
INSERT INTO spj VALUES('S2','P3','J2',200 );
INSERT INTO spj VALUES('S2','P3','J3',200 );
INSERT INTO spj VALUES('S2','P3','J4',500 );
INSERT INTO spj VALUES('S2','P3','J5',600 );
INSERT INTO spj VALUES('S2','P3','J6',400 );
INSERT INTO spj VALUES('S2','P3','J7',800 );
INSERT INTO spj VALUES('S2','P5','J2',100 );
INSERT INTO spj VALUES('S3','P3','J1',200 );
INSERT INTO spj VALUES('S3','P4','J2',500 );
INSERT INTO spj VALUES('S4','P6','J3',300 );
INSERT INTO spj VALUES('S4','P6','J7',300 );
INSERT INTO spj VALUES('S5','P2','J2',200 );
INSERT INTO spj VALUES('S5','P2','J4',100 );
INSERT INTO spj VALUES('S5','P5','J5',500 );
INSERT INTO spj VALUES('S5','P5','J7',100 );
INSERT INTO spj VALUES('S5','P6','J2',200 );
INSERT INTO spj VALUES('S5','P1','J4',100 );
INSERT INTO spj VALUES('S5','P3','J4',200 );
INSERT INTO spj VALUES('S5','P4','J4',800 );
INSERT INTO spj VALUES('S5','P5','J4',400 );
INSERT INTO spj VALUES('S5','P6','J4',500 );