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,
	 WEIGHT REAL,
	 MIN SMALLINT,
    MAX SMALLINT,
	 SPECIAL BOOLEAN,
    CONSTRAINT FK_CRITERION_CATEGORY FOREIGN KEY(CATEGORY) REFERENCES CATEGORY(CATEGORY)
);

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

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

CREATE VIEW RAWMARKS AS
	SELECT SUBMISSION.STUDENT as "Student" , SUM(RESULT) as "Raw Mark" FROM SUBMISSION
	LEFT OUTER JOIN RESULT ON SUBMISSION.STUDENT = RESULT.STUDENT
	GROUP BY SUBMISSION.STUDENT
	ORDER BY SUBMISSION.STUDENT