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);