annotations_schema.sql

CREATE TABLE family(
    id INTEGER PRIMARY KEY,
    size INTEGER,
    nbseq INTEGER,
    LCAtaxid INTEGER
);

CREATE TABLE keyword(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    str CHAR(30) UNIQUE
);

CREATE TABLE fam_kw_unsorted(
    famId INTEGER,
    kwId INTEGER,
    freq INTEGER,
    FOREIGN KEY(famId) REFERENCES family(id),
    FOREIGN KEY(kwId) REFERENCES keyword(id),
    PRIMARY KEY(famId,kwId)
);

CREATE TABLE fam_kw(
    famId INTEGER,
    kwId INTEGER,
    freq INTEGER,
    FOREIGN KEY(famId) REFERENCES family(id),
    FOREIGN KEY(kwId) REFERENCES keyword(id),
    PRIMARY KEY(famId,kwId)
);

CREATE TABLE fam_tax(
    famId INTEGER,
    taxid INTEGER,
    freq INTEGER,
    PRIMARY KEY(famId,taxid),
    FOREIGN KEY(famId) REFERENCES family(id)
);