Setting Moodle Untuk Administrator atau Programmer

Analisis Butir Soal Per Soal

Moodle menyediakan fitur untuk Analisis Butir Soal melalui Item Analysis yang komprehensif, berfokus pada dua metrik utama: Indeks Diskriminasi dan Tingkat Kesulitan soal [baca di sini]. Indeks Diskriminasi secara khusus membantu pendidik mengidentifikasi seberapa efektif soal membedakan mahasiswa berprestasi tinggi dan rendah. Data statistik ini memungkinkan perbaikan cepat untuk menemukan dan merevisi soal yang bermasalah (terlalu mudah, terlalu sulit, atau ambigu), sehingga meningkatkan validitas kuis. Namun, analisis ini bersifat per kuis; hasil statistik dari soal yang digunakan di beberapa kuis tidak dapat digabungkan secara otomatis, sehingga memerlukan perhitungan manual jika analisis gabungan diperlukan.

Untuk mengatasi keterbatasan Moodle dalam menggabungkan hasil analisis butir soal yang digunakan di berbagai kuis, solusi yang diusulkan melibatkan dua langkah utama: modifikasi pada struktur tabel basis data Moodle untuk memungkinkan agregasi data soal lintas-kuis, diikuti dengan pengembangan prosedur perhitungan kustom (berupa script atau stored procedure) yang bertugas menghitung ulang dan menyajikan statistik butir soal gabungan secara akurat.

ALTER TABLE `dbmdl`.`mdl_question_attempt_steps` 
ADD COLUMN `isgraded` TINYINT(1) UNSIGNED NULL AFTER `userid`,
ADD INDEX `mdl_quesattestep_isgrad_ix` (`isgraded` ASC);
;
DROP TRIGGER IF EXISTS `dbmdl`.`mdl_question_attempt_steps_BEFORE_UPDATE`;

DELIMITER $$
USE `dbmdl`$$
CREATE DEFINER = CURRENT_USER TRIGGER `dbmdl`.`mdl_question_attempt_steps_BEFORE_UPDATE` BEFORE UPDATE ON `mdl_question_attempt_steps` FOR EACH ROW
BEGIN
  IF NEW.state <> OLD.state THEN
    SET NEW.isgraded = if(NEW.state = "gradedright" OR NEW.state = "gradedwrong", 1, 0);
  END IF;
END$$
DELIMITER ;

UPDATE dbmdl.mdl_question_attempt_steps SET isgraded = if(state = "gradedright" OR state = "gradedwrong", 1, 0);

ALTER TABLE `dbmdl`.`mdl_question_attempts` 
ADD COLUMN `questionanswerid` BIGINT(10) NULL AFTER `timemodified`,
ADD INDEX `mdl_quessatte_answ_ix` (`questionanswerid` ASC);
;
DROP TRIGGER IF EXISTS `dbmdl`.`mdl_question_attempts_BEFORE_INSERT`;

DELIMITER $$
USE `dbmdl`$$
CREATE DEFINER = CURRENT_USER TRIGGER `dbmdl`.`mdl_question_attempts_BEFORE_INSERT` BEFORE INSERT ON `mdl_question_attempts` FOR EACH ROW
BEGIN
  SET NEW.questionanswerid = (select id from dbmdl.mdl_question_answers where replace(answer, char(9), ' ')=replace(responsesummary, char(9), ' ') and question=NEW.questionid limit 1);
END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `dbmdl`.`mdl_question_attempts_BEFORE_UPDATE`;

DELIMITER $$
USE `dbmdl`$$
CREATE DEFINER = CURRENT_USER TRIGGER `dbmdl`.`mdl_question_attempts_BEFORE_UPDATE` BEFORE UPDATE ON `mdl_question_attempts` FOR EACH ROW
BEGIN
  IF NEW.responsesummary <> OLD.responsesummary THEN
    SET NEW.questionanswerid = (select id from dbmdl.mdl_question_answers where replace(answer, char(9), ' ')=replace(responsesummary, char(9), ' ') and question=NEW.questionid limit 1);
  END IF;
END$$
DELIMITER ;

UPDATE dbmdl.mdl_question_attempts SET questionanswerid=(SELECT id FROM dbmdl.mdl_question_answers WHERE replace(answer, char(9), ' ')=replace(responsesummary, char(9), ' ') AND question=questionid limit 1);

USE `dbmdl`;
DROP function IF EXISTS `calc_discrimination_index`;

DELIMITER $$
USE `dbmdl`$$
CREATE FUNCTION `calc_discrimination_index` (questionid_ BIGINT)
RETURNS DOUBLE
BEGIN
  set @id_=0;
  RETURN 
  (  SELECT (sum(if(id>round(@id_)*0.73 and fraction_score>=0.99999999999999, 1, 0))/(round(@id_)*0.27) - sum(if(id<round(@id_)*0.27 and fraction_score>=0.99999999999999, 1, 0))/(round(@id_)*0.27)) * 100 as discrimination_index FROM 
    (
      SELECT *, @id_:=@id_+1 as id FROM
      (
        SELECT    
          qas.fraction AS fraction_score
        FROM
          mdl_quiz_attempts qa
        JOIN
          mdl_question_usages qu ON qu.id = qa.uniqueid
        JOIN
          mdl_question_attempts qatm ON qatm.questionusageid = qu.id
        JOIN
          mdl_question q ON q.id = qatm.questionid
        JOIN
          mdl_question_attempt_steps qas ON qas.questionattemptid = qatm.id
        LEFT JOIN 
          mdl_question_answers qans ON questionanswerid=qans.id
        WHERE
          q.id=questionid_
          AND qa.state = 'finished' AND qas.isgraded=1
        order by sumgrades
      ) order_bygrades
    ) numbering
  );
END$$

DELIMITER ;

USE `dbmdl`;
DROP function IF EXISTS `count_answer`;

DELIMITER $$
USE `dbmdl`$$
CREATE FUNCTION `count_answer` (questionid_ BIGINT)
RETURNS INTEGER
BEGIN
RETURN (
      SELECT
        count(1) as jumlah
      FROM
        mdl_quiz_attempts qa
      JOIN
        mdl_question_usages qu ON qu.id = qa.uniqueid
      JOIN
        mdl_question_attempts qatm ON qatm.questionusageid = qu.id
      JOIN
        mdl_question q ON q.id = qatm.questionid
      JOIN
        mdl_question_attempt_steps qas ON qas.questionattemptid = qatm.id
      LEFT JOIN 
        mdl_question_answers qans ON questionanswerid=qans.id
      WHERE
        q.id = questionid_
        AND qa.state = 'finished' AND qas.isgraded=1      
        );
END$$

DELIMITER ;

USE `dbmdl`;
DROP function IF EXISTS `examined_for_distractor`;

USE `dbmdl`;
DROP function IF EXISTS `dbmdl`.`examined_for_distractor`;
;

DELIMITER $$
USE `dbmdl`$$
CREATE DEFINER=`root`@`%` FUNCTION `examined_for_distractor`(questionid_ BIGINT, distractor_threshold_percent_ DOUBLE) RETURNS tinyint(1)
BEGIN
  RETURN
    (
    SELECT sum(check_distractor) FROM
    (
      SELECT
        if((count(1)/count_answer(q.id)*100)<distractor_threshold_percent_,1,0) as check_distractor
      FROM
        mdl_quiz_attempts qa
      JOIN
        mdl_question_usages qu ON qu.id = qa.uniqueid
      JOIN
        mdl_question_attempts qatm ON qatm.questionusageid = qu.id
      JOIN
        mdl_question q ON q.id = qatm.questionid
      JOIN
        mdl_question_attempt_steps qas ON qas.questionattemptid = qatm.id
      LEFT JOIN 
        mdl_question_answers qans ON questionanswerid=qans.id
      WHERE
        q.id = questionid_
        AND qa.state = 'finished' AND qas.isgraded=1
        AND qas.fraction<0.99999999999999
      GROUP BY q.id, questionanswerid
    ) data
    );
END$$

DELIMITER ;
;

USE `dbmdl`;
DROP procedure IF EXISTS `answer_analysis`;

DELIMITER $$
USE `dbmdl`$$
CREATE PROCEDURE `answer_analysis` (categoryid_ BIGINT)
BEGIN
  #per answer
  SELECT
    q.id, answer,
    qas.fraction AS fraction_score, -- The Normalized Score: 0 - 1 
    count(1) as peranswer, count_answer(q.id) as perquestion, count(1)/count_answer(q.id)*100 as percent_answer
  FROM
    mdl_quiz_attempts qa
  JOIN
    mdl_question_usages qu ON qu.id = qa.uniqueid
  JOIN
    mdl_question_attempts qatm ON qatm.questionusageid = qu.id
  JOIN
    mdl_question q ON q.id = qatm.questionid
  JOIN
    mdl_question_attempt_steps qas ON qas.questionattemptid = qatm.id
  LEFT JOIN 
    mdl_question_answers qans ON questionanswerid=qans.id
  WHERE
    q.category = categoryid_
    AND qa.state = 'finished' AND qas.isgraded=1
  GROUP BY id, questionanswerid, fraction_score
  ORDER BY id, fraction_score desc;
END$$

DELIMITER ;

USE `dbmdl`;
DROP procedure IF EXISTS `question_analysis`;

DELIMITER $$
USE `dbmdl`$$
CREATE PROCEDURE `question_analysis` (categoryid_ BIGINT)
BEGIN
  SELECT
    q.id, questiontext,
    sum(if(qas.fraction>=0.99999999999999, 1, 0))/count(1)*100 AS facility_index, -- The Normalized Score: 0 - 1 
    calc_discrimination_index(q.id) as discrimination_index,
    sum(if(qas.fraction>=0.99999999999999,1,0))/count(1) as full_score,
    examined_for_distractor(q.id, 5) as distactor
  FROM
    mdl_quiz_attempts qa
  JOIN
    mdl_question_usages qu ON qu.id = qa.uniqueid
  JOIN
    mdl_question_attempts qatm ON qatm.questionusageid = qu.id
  JOIN
    mdl_question q ON q.id = qatm.questionid
  JOIN
    mdl_question_attempt_steps qas ON qas.questionattemptid = qatm.id
  LEFT JOIN 
    mdl_question_answers qans ON questionanswerid=qans.id
  WHERE
    q.category = categoryid_
    AND qa.state = 'finished' AND qas.isgraded=1
  GROUP BY id;
END$$

DELIMITER ;