CREATE TABLE ASSESSMENT
  (NAME         VARCHAR PRIMARY KEY,
   BONUS_WEIGHT DOUBLE NOT NULL,
   OUTOF        DOUBLE NOT NULL)


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


CREATE TABLE 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 SUBMISSION
  (STUDENT VARCHAR PRIMARY KEY);


CREATE TABLE 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 SUBMISSION(STUDENT),
   CONSTRAINT FK_RESULT_CRITERION FOREIGN KEY(CRITERION) REFERENCES CRITERION(CRITERION));


CREATE VIEW WEIGHTED_MARK
AS
  SELECT STUDENT,
         SUM(WEIGHTED) * (SELECT OUTOF
                          FROM   ASSESSMENT) AS MARK
  FROM   (SELECT STUDENT,
                 RESULT,
                 WEIGHT,
                 MAX,
                 (RESULT / MAX * (WEIGHT / (SELECT SUM(WEIGHT)
                                            FROM   CRITERION))) AS WEIGHTED
          FROM   RESULT
                 INNER JOIN CRITERION ON RESULT.CRITERION = CRITERION.CRITERION)
  GROUP  BY STUDENT;


CREATE VIEW WEIGHTED_SPECIAL
AS
  SELECT STUDENT,
         COALESCE(SUM(RESULT) * (SELECT BONUS_WEIGHT
                                 FROM   ASSESSMENT), 0) AS SPECIAL
  FROM   RESULT
         INNER JOIN CRITERION ON CRITERION.CRITERION = RESULT.CRITERION
  WHERE  SPECIAL


CREATE VIEW MARK
AS
  SELECT WEIGHTED_MARK.STUDENT,
         CASEWHEN(MARK + SPECIAL <=
                  (SELECT OUTOF
                   FROM   ASSESSMENT), MARK + SPECIAL, (SELECT OUTOF
                                                        FROM   ASSESSMENT)) AS MARK
  FROM   WEIGHTED_MARK
         INNER JOIN WEIGHTED_SPECIAL ON WEIGHTED_MARK.STUDENT = WEIGHTED_SPECIAL.STUDENT


CREATE VIEW MARKING_SCHEDULE
AS
  SELECT CRITERION.CATEGORY,
         DESCRIPTION,
         WEIGHT,
         MAX AS OUTOF
  FROM   CRITERION
         INNER JOIN CATEGORY ON CATEGORY.CATEGORY = CRITERION.CATEGORY
  ORDER  BY CATEGORY.SORT_ORDER,CRITERION.SORT_ORDER;


CREATE VIEW FEEDBACK
AS
  SELECT STUDENT,
         CATEGORY.CATEGORY,
         COMMENT
  FROM   CRITERION
         INNER JOIN RESULT ON RESULT.CRITERION = CRITERION.CRITERION
         INNER JOIN CATEGORY ON CRITERION.CATEGORY = CATEGORY.CATEGORY
  WHERE  COMMENT <> ''
  ORDER  BY CATEGORY.SORT_ORDER,CRITERION.SORT_ORDER;


CREATE VIEW ALLMARKS("Student", "Weighted Mark")
AS
  SELECT SUBMISSION.STUDENT,
         COALESCE(CAST(ROUND(MARK, 2) AS VARCHAR), '')
  FROM   SUBMISSION
         LEFT JOIN MARK
                ON SUBMISSION.STUDENT = MARK.STUDENT