Quiz and Assessment Media

Objective

Create a comprehensive quiz and assessment system that supports multimedia questions including images, audio clips, and video segments, with detailed response tracking and analytics.

Step 1: Create Quizzes Table

Define quiz specifications.

CREATE TABLE quizzes (
    id INTEGER PRIMARY KEY,
    course_id INTEGER,
    quiz_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    instructions TEXT,
    quiz_type VARCHAR(50),
    total_points DECIMAL(6, 2),
    passing_score DECIMAL(5, 2),
    time_limit_minutes INTEGER,
    attempts_allowed INTEGER DEFAULT 1,
    shuffle_questions BOOLEAN DEFAULT FALSE,
    shuffle_answers BOOLEAN DEFAULT FALSE,
    show_correct_answers BOOLEAN DEFAULT TRUE,
    show_feedback BOOLEAN DEFAULT TRUE,
    available_from TIMESTAMP,
    available_until TIMESTAMP,
    created_by VARCHAR(100),
    status VARCHAR(50) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Questions Table

Store quiz questions with media.

CREATE TABLE quiz_questions (
    id INTEGER PRIMARY KEY,
    quiz_id INTEGER NOT NULL,
    question_number INTEGER,
    question_type VARCHAR(50),
    question_text TEXT NOT NULL,
    question_image IMAGE(PNG),
    question_audio AUDIO(MP3),
    question_video VIDEO(MP4),
    media_duration_seconds INTEGER,
    points DECIMAL(5, 2) DEFAULT 1.0,
    difficulty_level VARCHAR(20),
    time_limit_seconds INTEGER,
    explanation TEXT,
    hint TEXT,
    tags TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id)
);

Step 3: Create Answer Options Table

Store multiple choice answers.

CREATE TABLE answer_options (
    id INTEGER PRIMARY KEY,
    question_id INTEGER NOT NULL,
    option_letter VARCHAR(5),
    option_text TEXT,
    option_image IMAGE(PNG),
    option_audio AUDIO(MP3),
    is_correct BOOLEAN DEFAULT FALSE,
    partial_credit DECIMAL(3, 2) DEFAULT 0,
    feedback_if_selected TEXT,
    display_order INTEGER,
    FOREIGN KEY (question_id) REFERENCES quiz_questions(id)
);

Step 4: Create Quiz Attempts Table

Track student attempts.

CREATE TABLE quiz_attempts (
    id INTEGER PRIMARY KEY,
    quiz_id INTEGER NOT NULL,
    student_id VARCHAR(50) NOT NULL,
    attempt_number INTEGER DEFAULT 1,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP,
    time_spent_seconds INTEGER,
    score DECIMAL(6, 2),
    percentage DECIMAL(5, 2),
    passed BOOLEAN,
    status VARCHAR(50) DEFAULT 'in_progress',
    ip_address VARCHAR(50),
    browser_info TEXT,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id)
);

Step 5: Create Question Responses Table

Store individual answers.

CREATE TABLE question_responses (
    id INTEGER PRIMARY KEY,
    attempt_id INTEGER NOT NULL,
    question_id INTEGER NOT NULL,
    selected_option_id INTEGER,
    text_response TEXT,
    audio_response AUDIO(MP3),
    video_response VIDEO(MP4),
    response_time_seconds INTEGER,
    points_earned DECIMAL(5, 2),
    is_correct BOOLEAN,
    grader_feedback TEXT,
    graded_by VARCHAR(100),
    graded_at TIMESTAMP,
    answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (attempt_id) REFERENCES quiz_attempts(id),
    FOREIGN KEY (question_id) REFERENCES quiz_questions(id),
    FOREIGN KEY (selected_option_id) REFERENCES answer_options(id)
);

Step 6: Create Question Bank Table

Reusable question pool.

CREATE TABLE question_bank (
    id INTEGER PRIMARY KEY,
    bank_code VARCHAR(50) NOT NULL UNIQUE,
    category VARCHAR(100),
    subcategory VARCHAR(100),
    question_type VARCHAR(50),
    question_text TEXT NOT NULL,
    question_image IMAGE(PNG),
    question_audio AUDIO(MP3),
    correct_answer TEXT,
    answer_options TEXT,
    difficulty_level VARCHAR(20),
    times_used INTEGER DEFAULT 0,
    avg_correct_rate DECIMAL(5, 2),
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 7: Insert Sample Quizzes

Add quiz definitions.

INSERT INTO quizzes (id, course_id, quiz_code, title, description, quiz_type, total_points, passing_score, time_limit_minutes, attempts_allowed, shuffle_questions, show_correct_answers, available_from, available_until, status) VALUES
    (1, 1, 'CS101-Q1', 'Programming Basics Quiz', 'Test your understanding of basic programming concepts', 'practice', 50.0, 70.0, 30, 3, TRUE, TRUE, '2024-01-20 00:00:00', '2024-02-20 23:59:00', 'published'),
    (2, 1, 'CS101-MID', 'Midterm Exam', 'Covers weeks 1-7 material', 'exam', 100.0, 60.0, 90, 1, TRUE, FALSE, '2024-03-01 09:00:00', '2024-03-01 12:00:00', 'published'),
    (3, 2, 'MATH201-Q1', 'Integration Quiz', 'Practice integration techniques', 'practice', 40.0, 75.0, 45, 2, FALSE, TRUE, '2024-01-25 00:00:00', '2024-02-25 23:59:00', 'published'),
    (4, 3, 'PHYS101-LAB', 'Lab Safety Quiz', 'Required before lab participation', 'certification', 20.0, 100.0, 20, 5, TRUE, TRUE, '2024-01-10 00:00:00', NULL, 'published'),
    (5, 4, 'BIO150-ID', 'Cell Identification Quiz', 'Identify cell structures from images', 'practice', 30.0, 70.0, 25, 3, TRUE, TRUE, '2024-01-28 00:00:00', '2024-02-28 23:59:00', 'published');

Step 8: Insert Questions with Media

Add multimedia questions.

INSERT INTO quiz_questions (id, quiz_id, question_number, question_type, question_text, points, difficulty_level, explanation) VALUES
    (1, 1, 1, 'multiple_choice', 'What is a variable in programming?', 5.0, 'easy', 'A variable is a named storage location in memory.'),
    (2, 1, 2, 'multiple_choice', 'Which of the following is a valid Python variable name?', 5.0, 'easy', 'Variable names cannot start with numbers or contain spaces.'),
    (3, 1, 3, 'true_false', 'Python is a compiled language.', 5.0, 'easy', 'Python is an interpreted language, not compiled.'),
    (4, 1, 4, 'multiple_choice', 'What will be the output of: print(type(3.14))?', 5.0, 'medium', 'The type() function returns the data type of the argument.'),
    (5, 1, 5, 'short_answer', 'Write a line of code that prints "Hello World" in Python.', 10.0, 'easy', 'The correct syntax is: print("Hello World")');

-- Image-based questions
INSERT INTO quiz_questions (id, quiz_id, question_number, question_type, question_text, points, difficulty_level, explanation) VALUES
    (6, 5, 1, 'image_identify', 'Identify the organelle shown in this microscope image:', 6.0, 'medium', 'This is the mitochondria, known as the powerhouse of the cell.'),
    (7, 5, 2, 'image_identify', 'What type of cell is shown in this image?', 6.0, 'easy', 'Plant cells have cell walls and chloroplasts.'),
    (8, 5, 3, 'image_label', 'Label all visible structures in this cell diagram:', 10.0, 'hard', 'Key structures include nucleus, mitochondria, ER, Golgi apparatus.');

-- Audio-based questions
INSERT INTO quiz_questions (id, quiz_id, question_number, question_type, question_text, points, difficulty_level, media_duration_seconds) VALUES
    (9, 4, 1, 'audio_response', 'Listen to this lab safety scenario. What is the correct response?', 4.0, 'medium', 45),
    (10, 4, 2, 'audio_multiple_choice', 'In this audio clip, which safety violation is occurring?', 4.0, 'easy', 30);

Step 9: Insert Answer Options

Add choices for questions.

INSERT INTO answer_options (id, question_id, option_letter, option_text, is_correct, feedback_if_selected, display_order) VALUES
    (1, 1, 'A', 'A named storage location in memory', TRUE, 'Correct! Variables store data values.', 1),
    (2, 1, 'B', 'A type of function', FALSE, 'Incorrect. Functions are blocks of reusable code.', 2),
    (3, 1, 'C', 'A programming language', FALSE, 'Incorrect. Variables exist within programming languages.', 3),
    (4, 1, 'D', 'A hardware component', FALSE, 'Incorrect. Variables are software concepts.', 4),
    (5, 2, 'A', 'my_variable', TRUE, 'Correct! Uses underscores and starts with a letter.', 1),
    (6, 2, 'B', '2nd_variable', FALSE, 'Incorrect. Variable names cannot start with numbers.', 2),
    (7, 2, 'C', 'my variable', FALSE, 'Incorrect. Variable names cannot contain spaces.', 3),
    (8, 2, 'D', 'class', FALSE, 'Incorrect. "class" is a reserved keyword in Python.', 4),
    (9, 3, 'A', 'True', FALSE, 'Python is interpreted, not compiled.', 1),
    (10, 3, 'B', 'False', TRUE, 'Correct! Python code is executed line by line by an interpreter.', 2),
    (11, 4, 'A', '<class ''int''>', FALSE, 'Integers are whole numbers without decimals.', 1),
    (12, 4, 'B', '<class ''float''>', TRUE, 'Correct! 3.14 is a floating-point number.', 2),
    (13, 4, 'C', '<class ''str''>', FALSE, 'Strings are text enclosed in quotes.', 3),
    (14, 4, 'D', '<class ''bool''>', FALSE, 'Booleans are True or False values.', 4);

-- Image answer options
INSERT INTO answer_options (id, question_id, option_letter, option_text, is_correct, display_order) VALUES
    (15, 6, 'A', 'Nucleus', FALSE, 1),
    (16, 6, 'B', 'Mitochondria', TRUE, 2),
    (17, 6, 'C', 'Ribosome', FALSE, 3),
    (18, 6, 'D', 'Golgi Apparatus', FALSE, 4),
    (19, 7, 'A', 'Animal Cell', FALSE, 1),
    (20, 7, 'B', 'Plant Cell', TRUE, 2),
    (21, 7, 'C', 'Bacteria', FALSE, 3),
    (22, 7, 'D', 'Virus', FALSE, 4);

Step 10: Insert Quiz Attempts

Add student attempts.

INSERT INTO quiz_attempts (id, quiz_id, student_id, attempt_number, started_at, submitted_at, time_spent_seconds, score, percentage, passed, status) VALUES
    (1, 1, 'STU-001', 1, '2024-01-21 14:00:00', '2024-01-21 14:25:00', 1500, 45.0, 90.0, TRUE, 'submitted'),
    (2, 1, 'STU-002', 1, '2024-01-21 15:00:00', '2024-01-21 15:28:00', 1680, 40.0, 80.0, TRUE, 'submitted'),
    (3, 1, 'STU-003', 1, '2024-01-22 10:00:00', '2024-01-22 10:22:00', 1320, 30.0, 60.0, FALSE, 'submitted'),
    (4, 1, 'STU-003', 2, '2024-01-23 09:00:00', '2024-01-23 09:20:00', 1200, 40.0, 80.0, TRUE, 'submitted'),
    (5, 5, 'STU-004', 1, '2024-01-29 11:00:00', '2024-01-29 11:18:00', 1080, 24.0, 80.0, TRUE, 'submitted'),
    (6, 4, 'STU-005', 1, '2024-01-15 09:00:00', '2024-01-15 09:12:00', 720, 20.0, 100.0, TRUE, 'submitted');

Step 11: Insert Question Responses

Add individual answers.

INSERT INTO question_responses (id, attempt_id, question_id, selected_option_id, response_time_seconds, points_earned, is_correct) VALUES
    (1, 1, 1, 1, 45, 5.0, TRUE),
    (2, 1, 2, 5, 60, 5.0, TRUE),
    (3, 1, 3, 10, 30, 5.0, TRUE),
    (4, 1, 4, 12, 90, 5.0, TRUE),
    (5, 2, 1, 1, 52, 5.0, TRUE),
    (6, 2, 2, 6, 75, 0.0, FALSE),
    (7, 2, 3, 10, 28, 5.0, TRUE),
    (8, 2, 4, 12, 85, 5.0, TRUE),
    (9, 3, 1, 2, 60, 0.0, FALSE),
    (10, 3, 2, 5, 45, 5.0, TRUE),
    (11, 5, 6, 16, 45, 6.0, TRUE),
    (12, 5, 7, 20, 38, 6.0, TRUE);

INSERT INTO question_responses (id, attempt_id, question_id, text_response, points_earned, is_correct, grader_feedback, graded_by, graded_at) VALUES
    (13, 1, 5, 'print("Hello World")', 10.0, TRUE, 'Perfect syntax!', 'PROF-001', '2024-01-21 16:00:00'),
    (14, 2, 5, 'print(Hello World)', 5.0, FALSE, 'Missing quotes around the string.', 'PROF-001', '2024-01-21 16:30:00');

Step 12: Quiz Results Summary

View attempt results.

SELECT
    q.quiz_code,
    q.title,
    qa.student_id,
    qa.attempt_number,
    qa.score,
    qa.percentage,
    qa.passed,
    qa.time_spent_seconds / 60 as minutes_spent,
    qa.submitted_at
FROM quiz_attempts qa
INNER JOIN quizzes q ON qa.quiz_id = q.id
WHERE q.id = 1
ORDER BY qa.percentage DESC;

Step 13: Question Performance Analysis

Analyze question difficulty.

SELECT
    qq.question_number,
    qq.question_text,
    qq.difficulty_level,
    qq.points,
    COUNT(qr.id) as total_responses,
    COUNT(CASE WHEN qr.is_correct THEN 1 END) as correct_count,
    AVG(CASE WHEN qr.is_correct THEN 100.0 ELSE 0.0 END) as correct_percentage,
    AVG(qr.response_time_seconds) as avg_response_time
FROM quiz_questions qq
LEFT JOIN question_responses qr ON qq.id = qr.question_id
WHERE qq.quiz_id = 1
GROUP BY qq.id, qq.question_number, qq.question_text, qq.difficulty_level, qq.points
ORDER BY correct_percentage;

Step 14: Student Quiz History

View student performance.

SELECT
    q.quiz_code,
    q.title,
    q.quiz_type,
    qa.attempt_number,
    qa.score,
    q.total_points,
    qa.percentage,
    qa.passed,
    qa.started_at,
    qa.time_spent_seconds / 60 as minutes_used,
    q.time_limit_minutes
FROM quiz_attempts qa
INNER JOIN quizzes q ON qa.quiz_id = q.id
WHERE qa.student_id = 'STU-003'
ORDER BY qa.started_at DESC;

Step 15: Quiz Statistics Dashboard

Overall quiz analytics.

SELECT
    q.quiz_code,
    q.title,
    COUNT(qa.id) as total_attempts,
    COUNT(DISTINCT qa.student_id) as unique_students,
    AVG(qa.percentage) as avg_score,
    MIN(qa.percentage) as min_score,
    MAX(qa.percentage) as max_score,
    COUNT(CASE WHEN qa.passed THEN 1 END) as pass_count,
    AVG(qa.time_spent_seconds) / 60 as avg_time_minutes
FROM quizzes q
LEFT JOIN quiz_attempts qa ON q.id = qa.quiz_id AND qa.status = 'submitted'
WHERE q.status = 'published'
GROUP BY q.id, q.quiz_code, q.title
ORDER BY total_attempts DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS question_responses;
DROP TABLE IF EXISTS quiz_attempts;
DROP TABLE IF EXISTS answer_options;
DROP TABLE IF EXISTS quiz_questions;
DROP TABLE IF EXISTS question_bank;
DROP TABLE IF EXISTS quizzes;

Expected Outcomes

• Quizzes with multimedia questions
• Multiple attempt tracking
• Auto and manual grading
• Performance analytics
• Question effectiveness measured

Key Concepts Learned

• Multimedia question types
• Answer option management
• Attempt tracking
• Response analysis
• Quiz statistics