CREATE TABLE "chapters"
(
    id          INTEGER
        primary key autoincrement,
    Act         INTEGER not null,
    Scene       INTEGER not null,
    Description TEXT    not null,
    work_id     INTEGER not null
        references works
);
chapters
CREATE TABLE "characters"
(
    id          INTEGER
        primary key autoincrement,
    CharName    TEXT not null,
    Abbrev      TEXT not null,
    Description TEXT not null
);
characters
CREATE TABLE "paragraphs"
(
    id           INTEGER
        primary key autoincrement,
    ParagraphNum INTEGER           not null,
    PlainText    TEXT              not null,
    character_id INTEGER           not null
        references characters,
    chapter_id   INTEGER default 0 not null
        references chapters
);
paragraphs
CREATE TABLE "works"
(
    id        INTEGER
        primary key autoincrement,
    Title     TEXT    not null,
    LongTitle TEXT    not null,
    Date      INTEGER not null,
    GenreType TEXT    not null
);
works
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying the characters,integer,
CharName,char name,character name,text,
Abbrev,abbreviation,abbreviation. An abbreviation is a shortened form of a word or phrase.,text,
Description,,description of the character.,text,
characters
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying the chapter,integer auto_increment,
Act,,"An act is a major division of a theatre work, including a play, film, opera, or musical theatre",integer,"commonsense evidence:
An act can consist of one or more scenes"
Scene,,"A scene is a dramatic part of a story, at a specific time and place, between specific characters.",integer,
Description,,textual description of the chapter.,text,
work_id,work id,id number identifying the work,integer,
chapters
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying the paragraphs,integer ,
ParagraphNum,paragraph number,unique id number identifying the paragraph number,integer,
PlainText,Plain Text,main content of the paragraphs,text,
character_id,character id,unique id number identifying the mentioned character,integer,
chapter_id,chapter id,unique id number identifying the related chapter,integer,"commonsense evidence:
if number of the paragraphs is > 150, then it means this is a long chapter"
paragraphs
original_column_name,column_name,column_description,data_format,value_description
id,id,unique id number identifying the work,integer,
Title,,title of the work,text," commonsense evidence:
the short title or abbreviated title
"
LongTitle,Long Title,full title of the work,text,
Date,character id,date of the work,integer,
GenreType,genre type,the type of the genere,text,
works