CREATE TABLE assessment ( name varchar PRIMARY KEY, out_of 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 ( submission_id varchar PRIMARY KEY ); CREATE TABLE student ( submission_id varchar UNIQUE, student_id varchar UNIQUE, username varchar UNIQUE, first_name varchar, last_name varchar, email varchar UNIQUE, send_feedback boolean, email_sent boolean ); CREATE TABLE result ( submission_id varchar NOT NULL, criterion varchar NOT NULL, result real NOT NULL, comment varchar, CONSTRAINT pk_result PRIMARY KEY (submission_id, criterion), CONSTRAINT fk_result_student FOREIGN KEY (submission_id) REFERENCES submission (submission_id), CONSTRAINT fk_result_criterion FOREIGN KEY (criterion) REFERENCES criterion (criterion) ); CREATE VIEW weighted_mark AS SELECT submission_id, Sum(weighted) * ( SELECT out_of FROM assessment) AS MARK FROM ( SELECT submission_id, result, weight, max, (result / max * (weight / ( SELECT Sum(weight) FROM criterion WHERE NOT special))) AS WEIGHTED FROM result INNER JOIN criterion ON result.criterion = criterion.criterion WHERE NOT special) GROUP BY submission_id; -- special marks are multiplied by the criterion weight and added to the mark - use -- negative weight for penalties CREATE VIEW weighted_special AS SELECT submission_id, Sum(special) AS special FROM ( SELECT submission_id, (result * weight) AS special FROM result INNER JOIN criterion ON result.criterion = criterion.criterion WHERE special UNION ALL SELECT DISTINCT submission_id, 0 FROM result) GROUP BY submission_id -- version to use when bonus contributes to other assessment (maximum mark CAN be -- exceeded) CREATE VIEW mark AS SELECT weighted_mark.submission_id, Round(mark + special, 3) AS MARK FROM weighted_mark INNER JOIN weighted_special ON weighted_mark.submission_id = weighted_special.submission_id; -- version to use when bonus contributes only to this assessment (maximum mark can not -- be exceeded) -- CREATE VIEW mark -- AS -- SELECT weighted_mark.submission_id, -- Round( -- Casewhen( -- mark + special <= (SELECT out_of FROM assessment), -- mark + special, -- (SELECT out_of FROM assessment) -- ), -- 5) AS MARK -- FROM weighted_mark -- INNER JOIN weighted_special -- ON weighted_mark.submission_id = weighted_special.submission_id; 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 allmarks ("Student", "Weighted Mark", "Marking Complete", "Email Sent") AS SELECT submission.submission_id, COALESCE(Cast(Round(mark, 2) AS VARCHAR), ''), COALESCE(Cast(complete AS varchar), ''), COALESCE(Cast(email_sent AS varchar), '') FROM submission LEFT JOIN mark USING (submission_id) LEFT JOIN ( SELECT submission_id, Count(criterion) = ( SELECT Count(criterion) FROM criterion) AS complete FROM result GROUP BY submission_id) USING (submission_id) LEFT JOIN student USING (submission_id); -- view for showing relative weightings as percentages of total mark CREATE VIEW weighted_schedule AS SELECT cat.category, criterion, description, weight, special, round(weight / total_weight * 100, 2) AS relative_weight, round(weight / total_weight * ( SELECT out_of FROM assessment), 2) AS out_of FROM criterion crit, ( SELECT sum(weight) AS total_weight FROM criterion WHERE NOT special) INNER JOIN category cat ON crit.category = cat.category ORDER BY cat.sort_order, crit.sort_order; CREATE VIEW weighted_categories AS SELECT category, round(sum(relative_weight), 1) AS weight FROM WEIGHTED_SCHEDULE WHERE NOT special GROUP BY category; CREATE VIEW report AS SELECT a.name AS assessment, a.out_of, s.student_id, s.first_name || ' ' || s.last_name AS name, s.username, cat.category, cat.sort_order AS cat_order, cr.description AS criterion, cr.max AS max, cr.sort_order AS cr_order, r.result, r.comment, round(m.mark, 3) AS mark FROM assessment a JOIN category cat INNER JOIN criterion cr ON cat.category = cr.category INNER JOIN result r ON r.criterion = cr.criterion INNER JOIN student s ON r.submission_id = s.submission_id INNER JOIN mark m ON r.submission_id = m.submission_id ORDER BY student_id, cat_order, cr_order; -- use this if you want to produce useful category totals (and you don't care -- about weighting for individual criterion) -- UPDATE criterion -- SET weight = Cast(max AS DOUBLE) / (SELECT Count(*) -- FROM criterion);