CREATE TABLE author (
	id INTEGER NOT NULL,
	last VARCHAR,
	first VARCHAR,
	middle VARCHAR,
	sex VARCHAR(1),
	birth_year INTEGER,
	death_year INTEGER,
	PRIMARY KEY (id),
	CONSTRAINT sex CHECK (sex IN ('M', 'F'))
);

CREATE TABLE work (
	id INTEGER NOT NULL,
	uuid VARCHAR,
	book_title VARCHAR,
	age_cat VARCHAR(6),
	author_id INTEGER,
	PRIMARY KEY (id),
	CONSTRAINT agecat CHECK (age_cat IN ('KID', 'JUNIOR', 'TEEN')),
	FOREIGN KEY(author_id) REFERENCES author (id)
);

CREATE TABLE book (
	id INTEGER NOT NULL,
	title VARCHAR,
	start_page INTEGER,
	end_page INTEGER,
	filename VARCHAR,
	year INTEGER,
	city VARCHAR,
	publisher VARCHAR,
	circulation INTEGER,
	work_id INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY(work_id) REFERENCES work (id)
);