corpus

create.sql

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)
);