diff --git a/src/main/java/schema.sql b/src/main/java/schema.sql index e0e98ca..e11f8f9 100644 --- a/src/main/java/schema.sql +++ b/src/main/java/schema.sql @@ -1,177 +1,230 @@ -CREATE TABLE assessment - ( - name VARCHAR PRIMARY KEY, - out_of DOUBLE NOT NULL - ); +CREATE TABLE assessment ( + name varchar PRIMARY KEY, + out_of double NOT NULL +); +CREATE TABLE category ( + category varchar PRIMARY KEY, + sort_order smallint +); -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 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 + 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 + 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) + 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; -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; --- 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, --- Round(mark + special, 2) AS MARK --- FROM weighted_mark --- INNER JOIN weighted_special --- ON weighted_mark.submission = weighted_special.submission; - - --- 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); - +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_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 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; -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); +-- 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);