CREATE TABLE article (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	date DATE, 
	author_id SMALLINT NOT NULL,
	publication_id TINYINT NOT NULL,
	album_id MEDIUMINT NOT NULL,
	rating_id TINYINT,
	INDEX index_rating_id (rating_id), 
	INDEX index_author_id (author_id) 
	);
CREATE TABLE author (
	id SMALLINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name TEXT NOT NULL );
CREATE TABLE publication (
	id TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name TEXT NOT NULL,
	url TEXT NOT NULL );
CREATE TABLE rating (
	id TINYINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	rating TINYINT UNSIGNED NOT NULL);
CREATE TABLE album (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name TEXT NOT NULL,
	year YEAR,
	band_id MEDIUMINT NOT NULL,
	label_id MEDIUMINT NOT NULL );
CREATE TABLE band (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name TEXT NOT NULL );
CREATE TABLE label (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	name TEXT NOT NULL );
CREATE TABLE paragraph (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	position TINYINT UNSIGNED NOT NULL,
	text TEXT NOT NULL,
	article_id MEDIUMINT NOT NULL,
	INDEX index_article_id (article_id)
	);
CREATE TABLE sentence (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	beginchar SMALLINT NOT NULL,
	length SMALLINT NOT NULL,
	paragraph_id MEDIUMINT NOT NULL, 
	INDEX index_paragraph_id (paragraph_id)
	);
CREATE TABLE word (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	word VARCHAR(30) BINARY NOT NULL,
	INDEX index_word (word) 
	);
CREATE TABLE quote (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	beginchar SMALLINT NOT NULL,
	wordcount SMALLINT NOT NULL,
	contains_slash TINYINT NOT NULL,
	paragraph_id MEDIUMINT NOT NULL,
	INDEX index_paragraph_id (paragraph_id) );
CREATE TABLE word_sentence (
	word_id MEDIUMINT NOT NULL,
	sentence_id MEDIUMINT NOT NULL,
	position TINYINT UNSIGNED NOT NULL,
	part_of_speech TINYTEXT,
	INDEX index_position (position),
	INDEX index_word_id (word_id),
	INDEX index_sentence_id (sentence_id) );
CREATE TABLE word_paragraph (
	word_id MEDIUMINT NOT NULL,
	paragraph_id MEDIUMINT NOT NULL,
	position TINYINT UNSIGNED NOT NULL,
	INDEX index_word_id (word_id),
	INDEX index_paragraph_id (paragraph_id)
	);
CREATE TABLE word_quote (
	word_id MEDIUMINT NOT NULL,
	quote_id MEDIUMINT NOT NULL,
	INDEX index_word_id (word_id),
	INDEX index_quote_id (quote_id)
	);
CREATE TABLE configuration (
	variable TINYTEXT NOT NULL,
	value TINYTEXT NOT NULL );
CREATE TABLE wordscore (
	word_id MEDIUMINT NOT NULL PRIMARY KEY,
	score FLOAT(2) 
);
CREATE TABLE statistics (
	word_id MEDIUMINT NOT NULL PRIMARY KEY,
	tot MEDIUMINT NOT NULL,
	pos MEDIUMINT NOT NULL,
	neg MEDIUMINT NOT NULL,
	neu MEDIUMINT NOT NULL,
	pwr FLOAT(2),
	nwr FLOAT(2),
	avg FLOAT(2),
	art MEDIUMINT NOT NULL,
	aut SMALLINT NOT NULL );
CREATE TABLE class (
        id SMALLINT NOT NULL PRIMARY KEY,
        name TEXT NOT NULL, 
	description TEXT );
CREATE TABLE word_class (
        word_id MEDIUMINT NOT NULL,
	class_id SMALLINT NOT NULL,
	INDEX index_word_id (word_id),
        INDEX index_class_id (class_id) );
CREATE TABLE htmlcache (
	id MEDIUMINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	url MEDIUMTEXT NOT NULL,
	html MEDIUMTEXT NOT NULL
);
INSERT INTO class (id, name, description) VALUES (1, "good", 
	"positive aesthetic value judgement words");
INSERT INTO class (id, name, description) VALUES (2, "bad", 
	"negative aesthetic value judgement words");
INSERT INTO class (id, name, description) VALUES (3, "genre", 
	"references to musical genres");
INSERT INTO class (id, name, description) VALUES (4, "mood", 
"mood words that describe the music or the music's effect on the listener");
INSERT INTO class (id, name, description) VALUES (5, "transition", 
"descriptions of the transitions in the music");
INSERT INTO class (id, name, description) VALUES (6, "dynamics", 
"descriptions of musical dynamics");
INSERT INTO class (id, name, description) VALUES (7, "instrument", 
"instruments and words describing instrument techniques");
INSERT INTO class (id, name, description) VALUES (8, "vocal", 
"words referencing the vocals or vocal style");
INSERT INTO class (id, name, description) VALUES (9, "structure", 
"words describing compositional structure");
INSERT INTO class (id, name, description) VALUES (10, "sound", 
"words directly describing sounds");
INSERT INTO class (id, name, description) VALUES (11, "metaphor", 
"metaphors that don't fit into other categories");
INSERT INTO class (id, name, description) VALUES (12, "complex",
"words which fall into multiple categories or are otherwise hard to categorize"
);
INSERT INTO class (id, name, description) VALUES (13, "consumerism",
"words which reference buying, advertising, and corporations");
INSERT INTO class (id, name, description) VALUES (14, "subject matter",
"words which reference subject matter");
INSERT INTO class (id, name, description) VALUES (15, "intelligence",
"words describing the intelligence (or lack of it) of the artists or audience");