Student Assignment Submissions

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

Step 9: Insert Submissions

Add student submissions.

INSERT INTO submissions (id, assignment_id, student_id, submission_number, submitted_at, status, is_late, word_count, plagiarism_score) VALUES
    (1, 1, 1, 1, '2024-01-31 22:45:00', 'graded', FALSE, 1050, 8.5),
    (2, 1, 2, 1, '2024-02-01 23:30:00', 'graded', FALSE, 980, 12.3),
    (3, 1, 3, 1, '2024-02-02 10:15:00', 'graded', TRUE, 1120, 5.2),
    (4, 2, 1, 1, '2024-02-14 20:00:00', 'graded', FALSE, NULL, NULL),
    (5, 2, 2, 1, '2024-02-15 22:00:00', 'submitted', FALSE, NULL, NULL),
    (6, 3, 3, 1, '2024-02-04 18:30:00', 'graded', FALSE, NULL, NULL),
    (7, 4, 4, 1, '2024-02-09 21:00:00', 'graded', FALSE, 2500, 3.1),
    (8, 5, 5, 1, '2024-02-19 16:00:00', 'submitted', FALSE, NULL, NULL);

Step 10: Insert Submission Files

Add media files.

INSERT INTO submission_files (id, submission_id, file_name, file_type, file_size_mb, page_count, upload_order) VALUES
    (1, 1, 'programming_essay_johndoe.pdf', 'pdf', 2.5, 5, 1),
    (2, 2, 'cs101_essay_janesmith.pdf', 'pdf', 2.1, 4, 1),
    (3, 3, 'essay_mikejohnson.pdf', 'pdf', 3.2, 6, 1),
    (6, 6, 'math_problemset_mike.pdf', 'pdf', 4.5, 8, 1),
    (7, 7, 'motion_lab_report.pdf', 'pdf', 5.8, 12, 1),
    (8, 7, 'lab_data_graphs.pdf', 'pdf', 1.2, 3, 2);

INSERT INTO submission_files (id, submission_id, file_name, file_type, file_size_mb, duration_seconds, upload_order) VALUES
    (4, 4, 'code_demo_johndoe.mp4', 'video', 285.6, 312, 1),
    (5, 5, 'my_code_walkthrough.mp4', 'video', 342.1, 298, 1),
    (9, 8, 'cell_presentation.mp4', 'video', 156.8, 480, 1);

Step 11: Insert Feedback

Add grading results.

INSERT INTO submission_feedback (id, submission_id, grader_id, grader_name, points_earned, letter_grade, overall_feedback, graded_at, published_at, is_final) VALUES
    (1, 1, 'PROF-001', 'Dr. Alice Chen', 92.0, 'A-', 'Excellent analysis of programming concepts. Well-structured essay with good examples. Minor grammatical errors.', '2024-02-03 14:00:00', '2024-02-03 16:00:00', TRUE),
    (2, 2, 'PROF-001', 'Dr. Alice Chen', 85.0, 'B+', 'Good understanding demonstrated. Could benefit from more specific code examples.', '2024-02-03 15:00:00', '2024-02-03 16:00:00', TRUE),
    (3, 3, 'PROF-001', 'Dr. Alice Chen', 78.0, 'C+', 'Late submission (-10%). Content is good but lacks depth in some areas.', '2024-02-04 10:00:00', '2024-02-04 12:00:00', TRUE),
    (4, 4, 'PROF-001', 'Dr. Alice Chen', 145.0, 'A', 'Excellent video presentation. Clear explanation of code functionality.', '2024-02-18 11:00:00', '2024-02-18 14:00:00', TRUE),
    (5, 6, 'PROF-002', 'Dr. Robert Brown', 75.0, 'B', 'Correct solutions. Show more work for partial credit eligibility.', '2024-02-07 09:00:00', '2024-02-07 10:00:00', TRUE),
    (6, 7, 'PROF-003', 'Dr. Emily Wilson', 115.0, 'A-', 'Thorough lab report with excellent data analysis. Conclusion could be stronger.', '2024-02-12 16:00:00', '2024-02-12 17:00:00', TRUE);

Step 12: Insert Inline Comments

Add detailed feedback.

INSERT INTO inline_comments (id, file_id, grader_id, comment_type, page_number, comment_text) VALUES
    (1, 1, 'PROF-001', 'positive', 1, 'Great introduction that clearly states your thesis.'),
    (2, 1, 'PROF-001', 'suggestion', 2, 'Consider adding a code example here to illustrate your point.'),
    (3, 1, 'PROF-001', 'correction', 3, 'This statement is not entirely accurate. See course notes on variables.'),
    (4, 1, 'PROF-001', 'positive', 5, 'Strong conclusion that ties back to your introduction.'),
    (5, 7, 'PROF-003', 'positive', 1, 'Excellent abstract that summarizes your findings well.'),
    (6, 7, 'PROF-003', 'suggestion', 5, 'Your error analysis could be more detailed.'),
    (7, 7, 'PROF-003', 'positive', 10, 'Outstanding data visualization in your graphs.');

INSERT INTO inline_comments (id, file_id, grader_id, comment_type, timestamp_seconds, comment_text) VALUES
    (8, 4, 'PROF-001', 'positive', 45, 'Good explanation of the function purpose.'),
    (9, 4, 'PROF-001', 'suggestion', 120, 'You could improve performance here by using a dictionary.'),
    (10, 4, 'PROF-001', 'positive', 280, 'Excellent debugging demonstration.');

Step 13: Assignment Submission Status

View submission overview.

SELECT
    a.assignment_code,
    a.title,
    a.due_date,
    COUNT(DISTINCT sub.student_id) as submissions_received,
    COUNT(CASE WHEN sub.is_late THEN 1 END) as late_submissions,
    COUNT(CASE WHEN sf.status = 'graded' THEN 1 END) as graded,
    AVG(sf.points_earned) as avg_score
FROM assignments a
LEFT JOIN submissions sub ON a.id = sub.assignment_id
LEFT JOIN submission_feedback sf ON sub.id = sf.submission_id
GROUP BY a.id, a.assignment_code, a.title, a.due_date
ORDER BY a.due_date;

Step 14: Student Grade Report

View individual grades.

SELECT
    s.first_name || ' ' || s.last_name as student_name,
    a.assignment_code,
    a.title,
    sub.submitted_at,
    sub.is_late,
    sf.points_earned,
    a.points_possible,
    sf.letter_grade,
    sf.overall_feedback
FROM students s
INNER JOIN submissions sub ON s.id = sub.student_id
INNER JOIN assignments a ON sub.assignment_id = a.id
LEFT JOIN submission_feedback sf ON sub.id = sf.submission_id
WHERE s.student_id = 'STU-2024-001'
ORDER BY a.due_date;

Step 15: Grading Workload Report

Track grading progress.

SELECT
    a.assignment_code,
    a.title,
    COUNT(sub.id) as total_submissions,
    COUNT(sf.id) as graded_count,
    COUNT(sub.id) - COUNT(sf.id) as pending_grading,
    AVG(sf.points_earned) as current_avg,
    MIN(sf.graded_at) as first_graded,
    MAX(sf.graded_at) as last_graded
FROM assignments a
LEFT JOIN submissions sub ON a.id = sub.assignment_id
LEFT JOIN submission_feedback sf ON sub.id = sf.submission_id AND sf.is_final = TRUE
WHERE a.due_date < CURRENT_TIMESTAMP
GROUP BY a.id, a.assignment_code, a.title
ORDER BY pending_grading DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS peer_reviews;
DROP TABLE IF EXISTS inline_comments;
DROP TABLE IF EXISTS submission_feedback;
DROP TABLE IF EXISTS submission_files;
DROP TABLE IF EXISTS submissions;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS assignments;

Expected Outcomes

• Assignments defined with requirements
• Multiple file types supported
• Feedback with annotations
• Grades tracked
• Reports generated

Key Concepts Learned

• Multi-format submissions
• Version control
• Inline feedback
• Plagiarism tracking
• Grade management