CREATE TABLE author (
    id serial NOT NULL,
    fname varchar,
    lname varchar NOT NULL
);


CREATE TABLE book_author (
    bid integer NOT NULL,
    aid integer NOT NULL,
    ordr smallint NOT NULL
);


CREATE TABLE famous_author (
    id integer NOT NULL,
    prizes smallint
);

CREATE TABLE book (
    id serial NOT NULL,
    title varchar NOT NULL,
    editor varchar NOT NULL,
    "year" smallint
);


INSERT INTO author (id, fname, lname) VALUES (1, NULL, 'Doe');
INSERT INTO author (id, fname, lname) VALUES (2, 'Jim', 'Brown');

INSERT INTO book_author (bid, aid, ordr) VALUES (1, 1, 1);
INSERT INTO book_author (bid, aid, ordr) VALUES (2, 2, 1);
INSERT INTO book_author (bid, aid, ordr) VALUES (2, 1, 2);
INSERT INTO book_author (bid, aid, ordr) VALUES (3, 2, 1);

INSERT INTO famous_author (id, prizes) VALUES (1, 1);

INSERT INTO book (id, title, editor, "year") VALUES (1, 'OWL', 'Smith', 2004);
INSERT INTO book (id, title, editor, "year") VALUES (2, 'P2P', 'Smith', 2002);
INSERT INTO book (id, title, editor, "year") VALUES (3, 'My life', 'Doe Ed.', 2005);


CREATE INDEX fki_ ON book_author USING btree (aid);

ALTER TABLE author
    ADD CONSTRAINT pk_a PRIMARY KEY (id);

ALTER TABLE book_author
    ADD CONSTRAINT pk_ba PRIMARY KEY (bid, aid);

ALTER TABLE book_author
    ADD CONSTRAINT bid_ordr UNIQUE (bid, ordr);

ALTER TABLE famous_author
    ADD CONSTRAINT pk_fa PRIMARY KEY (id);

ALTER TABLE book
    ADD CONSTRAINT pk_b PRIMARY KEY (id);


ALTER TABLE famous_author
    ADD CONSTRAINT fka_fa FOREIGN KEY (id) REFERENCES author(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE book_author
    ADD CONSTRAINT fka_ba FOREIGN KEY (aid) REFERENCES author(id) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE book_author
    ADD CONSTRAINT fkb_ba FOREIGN KEY (bid) REFERENCES book(id) ON UPDATE CASCADE ON DELETE CASCADE;
