CREATE TABLE PUBLIC.CATEGORY(
    CATEGORY VARCHAR PRIMARY KEY,
    SORT_ORDER SMALLINT
);

CREATE TABLE PUBLIC.CRITERION(
    CRITERION VARCHAR PRIMARY KEY,
    CATEGORY VARCHAR NOT NULL,
    DESCRIPTION VARCHAR NOT NULL,
    SORT_ORDER SMALLINT,
    CONSTRAINT FK_CRITERION_CATEGORY FOREIGN KEY(CATEGORY) REFERENCES CATEGORY(CATEGORY)
);

CREATE TABLE PUBLIC.SUBMISSION(
    STUDENT VARCHAR PRIMARY KEY
);

CREATE CACHED TABLE PUBLIC.RESULT(
    STUDENT VARCHAR NOT NULL,
    CRITERION VARCHAR NOT NULL,
    RESULT REAL NOT NULL,
    COMMENT VARCHAR,
    CONSTRAINT PK_RESULT PRIMARY KEY(STUDENT, CRITERION),
    CONSTRAINT FK_RESULT_STUDENT FOREIGN KEY(STUDENT) REFERENCES PUBLIC.SUBMISSION(STUDENT),
    CONSTRAINT FK_RESULT_CRITERION FOREIGN KEY(CRITERION) REFERENCES PUBLIC.CRITERION(CRITERION)
);