Objective
Create a comprehensive system for managing student assignment submissions that support multiple media types including documents, videos, audio recordings, and images, with version control and instructor feedback.
Step 1: Create Assignments Table
Define assignment specifications.
CREATE TABLE assignments (
id INTEGER PRIMARY KEY,
course_id INTEGER NOT NULL,
assignment_code VARCHAR(50) NOT NULL,
title VARCHAR(200) NOT NULL,
description TEXT,
instructions TEXT,
rubric TEXT,
assignment_type VARCHAR(50),
allowed_formats TEXT,
max_file_size_mb INTEGER DEFAULT 100,
max_submissions INTEGER DEFAULT 1,
due_date TIMESTAMP,
late_due_date TIMESTAMP,
late_penalty_percent DECIMAL(5, 2) DEFAULT 10.0,
points_possible DECIMAL(6, 2),
is_group_assignment BOOLEAN DEFAULT FALSE,
peer_review_enabled BOOLEAN DEFAULT FALSE,
plagiarism_check BOOLEAN DEFAULT TRUE,
created_by VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
published_at TIMESTAMP
);
Step 2: Create Students Table
Store student information.
CREATE TABLE students (
id INTEGER PRIMARY KEY,
student_id VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(200),
first_name VARCHAR(100),
last_name VARCHAR(100),
major VARCHAR(100),
year_level INTEGER,
enrollment_status VARCHAR(50) DEFAULT 'active'
);
Step 3: Create Submissions Table
Track submitted work.
CREATE TABLE submissions (
id INTEGER PRIMARY KEY,
assignment_id INTEGER NOT NULL,
student_id INTEGER NOT NULL,
submission_number INTEGER DEFAULT 1,
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) DEFAULT 'submitted',
is_late BOOLEAN DEFAULT FALSE,
late_hours DECIMAL(6, 2),
word_count INTEGER,
plagiarism_score DECIMAL(5, 2),
plagiarism_report_url VARCHAR(500),
submission_notes TEXT,
ip_address VARCHAR(50),
FOREIGN KEY (assignment_id) REFERENCES assignments(id),
FOREIGN KEY (student_id) REFERENCES students(id)
);
Step 4: Create Submission Files Table
Store submitted media files.
CREATE TABLE submission_files (
id INTEGER PRIMARY KEY,
submission_id INTEGER NOT NULL,
file_name VARCHAR(255),
file_type VARCHAR(50),
document_file PDF,
video_file VIDEO(MP4),
audio_file AUDIO(MP3),
image_file IMAGE(JPEG),
file_size_mb DECIMAL(10, 2),
duration_seconds INTEGER,
page_count INTEGER,
upload_order INTEGER,
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (submission_id) REFERENCES submissions(id)
);
Step 5: Create Feedback Table
Store instructor feedback.
CREATE TABLE submission_feedback (
id INTEGER PRIMARY KEY,
submission_id INTEGER NOT NULL,
grader_id VARCHAR(100),
grader_name VARCHAR(200),
points_earned DECIMAL(6, 2),
letter_grade VARCHAR(5),
overall_feedback TEXT,
feedback_video VIDEO(MP4),
feedback_audio AUDIO(MP3),
rubric_scores TEXT,
graded_at TIMESTAMP,
published_at TIMESTAMP,
is_final BOOLEAN DEFAULT FALSE,
FOREIGN KEY (submission_id) REFERENCES submissions(id)
);
Step 6: Create Inline Comments Table
Track specific feedback on files.
CREATE TABLE inline_comments (
id INTEGER PRIMARY KEY,
file_id INTEGER NOT NULL,
grader_id VARCHAR(100),
comment_type VARCHAR(50),
page_number INTEGER,
timestamp_seconds INTEGER,
x_position DECIMAL(5, 2),
y_position DECIMAL(5, 2),
comment_text TEXT,
annotation_image IMAGE(PNG),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (file_id) REFERENCES submission_files(id)
);
Step 7: Create Peer Reviews Table
Support peer assessment.
CREATE TABLE peer_reviews (
id INTEGER PRIMARY KEY,
submission_id INTEGER NOT NULL,
reviewer_student_id INTEGER NOT NULL,
assigned_at TIMESTAMP,
due_date TIMESTAMP,
completed_at TIMESTAMP,
rating INTEGER,
feedback_text TEXT,
is_anonymous BOOLEAN DEFAULT TRUE,
helpfulness_score INTEGER,
FOREIGN KEY (submission_id) REFERENCES submissions(id),
FOREIGN KEY (reviewer_student_id) REFERENCES students(id)
);
Step 8: Insert Sample Assignments
Add assignment definitions.
INSERT INTO assignments (id, course_id, assignment_code, title, description, assignment_type, allowed_formats, max_file_size_mb, due_date, late_due_date, points_possible, plagiarism_check, published_at) VALUES
(1, 1, 'CS101-A1', 'Programming Basics Essay', 'Write a 1000-word essay on programming fundamentals', 'essay', 'pdf,docx', 20, '2024-02-01 23:59:00', '2024-02-03 23:59:00', 100.0, TRUE, '2024-01-15 00:00:00'),
(2, 1, 'CS101-A2', 'Code Demo Video', 'Record a 5-minute video demonstrating your code', 'video', 'mp4,mov', 500, '2024-02-15 23:59:00', '2024-02-17 23:59:00', 150.0, FALSE, '2024-01-20 00:00:00'),
(3, 2, 'MATH201-A1', 'Problem Set 1', 'Solve integration problems 1-20', 'problem_set', 'pdf,jpg,png', 50, '2024-02-05 23:59:00', NULL, 80.0, FALSE, '2024-01-18 00:00:00'),
(4, 3, 'PHYS101-LAB1', 'Lab Report: Motion', 'Write up your motion experiment results', 'lab_report', 'pdf', 30, '2024-02-10 23:59:00', '2024-02-12 23:59:00', 120.0, TRUE, '2024-01-22 00:00:00'),
(5, 4, 'BIO150-PRES', 'Cell Biology Presentation', 'Create a narrated presentation on cell organelles', 'presentation', 'mp4,pdf', 200, '2024-02-20 23:59:00', '2024-02-22 23:59:00', 100.0, FALSE, '2024-01-25 00:00:00');
INSERT INTO students (id, student_id, email, first_name, last_name, major, year_level) VALUES
(1, 'STU-2024-001', 'john.doe@university.edu', 'John', 'Doe', 'Computer Science', 2),
(2, 'STU-2024-002', 'jane.smith@university.edu', 'Jane', 'Smith', 'Computer Science', 2),
(3, 'STU-2024-003', 'mike.johnson@university.edu', 'Mike', 'Johnson', 'Mathematics', 3),
(4, 'STU-2024-004', 'sarah.wilson@university.edu', 'Sarah', 'Wilson', 'Physics', 2),
(5, 'STU-2024-005', 'emily.brown@university.edu', 'Emily', 'Brown', 'Biology', 1);