SET client_encoding TO 'UTF-8';

BEGIN;

DROP TABLE Books;
DROP TABLE Authors;

CREATE TABLE Authors (
   id SERIAL UNIQUE NOT NULL,
   name TEXT NOT NULL);

CREATE TABLE Books (
   id SERIAL UNIQUE NOT NULL,
   title TEXT NOT NULL,
   author INTEGER REFERENCES Authors(id)); -- Oversimplified, since a book
                                           -- can have several authors
   -- author can be NULL (for instance for anonymous work)

INSERT INTO Authors (id, name) VALUES (1, 'Stéphane Bortzmeyer');
INSERT INTO Authors (id, name) VALUES (2, 'Fred Vargas');
INSERT INTO Authors (id, name) VALUES (3, 'Ève Demazière');
INSERT INTO Authors (id, name) VALUES (4, 'Alain Damasio');

INSERT INTO Books (title, author) VALUES ('Les cultures noires d''Amérique Centrale', 3);
INSERT INTO Books (title, author) VALUES ('La horde du contrevent', 4);
INSERT INTO Books (title, author) VALUES ('Pars vite et reviens tard', 2);
INSERT INTO Books (title, author) VALUES ('L''homme à l''envers', 2);
INSERT INTO Books (title) VALUES ('Bible');

-- Author 1 wrote no book. The bible's author is unknown.

SELECT * FROM Authors;
SELECT * FROM Books;

COMMIT;