Virtual Classroom Recordings

Objective

Create a system for managing virtual classroom session recordings, including main room and breakout room recordings, screen shares, chat logs, and participant engagement tracking.

Step 1: Create Virtual Classes Table

Define online class sessions.

CREATE TABLE virtual_classes (
    id INTEGER PRIMARY KEY,
    course_id INTEGER,
    class_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    instructor_id VARCHAR(50),
    instructor_name VARCHAR(200),
    scheduled_start TIMESTAMP,
    scheduled_end TIMESTAMP,
    actual_start TIMESTAMP,
    actual_end TIMESTAMP,
    platform VARCHAR(50),
    meeting_url VARCHAR(500),
    meeting_password VARCHAR(100),
    max_participants INTEGER,
    recording_enabled BOOLEAN DEFAULT TRUE,
    waiting_room_enabled BOOLEAN DEFAULT TRUE,
    breakout_rooms_enabled BOOLEAN DEFAULT FALSE,
    status VARCHAR(50) DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Session Recordings Table

Store class recordings.

CREATE TABLE session_recordings (
    id INTEGER PRIMARY KEY,
    class_id INTEGER NOT NULL,
    recording_type VARCHAR(50),
    recording_name VARCHAR(200),
    main_video VIDEO(MP4),
    audio_only AUDIO(MP3),
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    resolution VARCHAR(20),
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    speaker_view_video VIDEO(MP4),
    gallery_view_video VIDEO(MP4),
    processing_status VARCHAR(50) DEFAULT 'processing',
    playback_url VARCHAR(500),
    download_url VARCHAR(500),
    transcript TEXT,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES virtual_classes(id)
);

Step 3: Create Breakout Rooms Table

Track breakout sessions.

CREATE TABLE breakout_rooms (
    id INTEGER PRIMARY KEY,
    class_id INTEGER NOT NULL,
    room_name VARCHAR(100),
    room_number INTEGER,
    topic VARCHAR(200),
    max_participants INTEGER,
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    duration_seconds INTEGER,
    recording VIDEO(MP4),
    FOREIGN KEY (class_id) REFERENCES virtual_classes(id)
);

Step 4: Create Screen Shares Table

Track screen sharing segments.

CREATE TABLE screen_shares (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    sharer_id VARCHAR(50),
    sharer_name VARCHAR(200),
    share_type VARCHAR(50),
    application_name VARCHAR(100),
    screen_recording VIDEO(MP4),
    start_timestamp_seconds INTEGER,
    end_timestamp_seconds INTEGER,
    duration_seconds INTEGER,
    FOREIGN KEY (recording_id) REFERENCES session_recordings(id)
);

Step 5: Create Participants Table

Track attendee information.

CREATE TABLE class_participants (
    id INTEGER PRIMARY KEY,
    class_id INTEGER NOT NULL,
    participant_id VARCHAR(50),
    participant_name VARCHAR(200),
    participant_email VARCHAR(200),
    participant_role VARCHAR(50),
    joined_at TIMESTAMP,
    left_at TIMESTAMP,
    duration_minutes INTEGER,
    device_type VARCHAR(50),
    connection_quality VARCHAR(20),
    camera_on_minutes INTEGER,
    mic_on_minutes INTEGER,
    raised_hand_count INTEGER DEFAULT 0,
    chat_message_count INTEGER DEFAULT 0,
    reaction_count INTEGER DEFAULT 0,
    FOREIGN KEY (class_id) REFERENCES virtual_classes(id)
);

Step 6: Create Chat Messages Table

Store chat history.

CREATE TABLE class_chat_messages (
    id INTEGER PRIMARY KEY,
    class_id INTEGER NOT NULL,
    sender_id VARCHAR(50),
    sender_name VARCHAR(200),
    recipient_type VARCHAR(20),
    recipient_id VARCHAR(50),
    message_text TEXT,
    message_type VARCHAR(50),
    attachment_url VARCHAR(500),
    timestamp_seconds INTEGER,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES virtual_classes(id)
);

Step 7: Create Polls Table

Track interactive polls.

CREATE TABLE class_polls (
    id INTEGER PRIMARY KEY,
    class_id INTEGER NOT NULL,
    poll_question TEXT,
    poll_type VARCHAR(50),
    options TEXT,
    correct_answer VARCHAR(100),
    launched_at TIMESTAMP,
    closed_at TIMESTAMP,
    total_responses INTEGER DEFAULT 0,
    response_data TEXT,
    timestamp_seconds INTEGER,
    FOREIGN KEY (class_id) REFERENCES virtual_classes(id)
);

Step 8: Insert Sample Virtual Classes

Add scheduled sessions.

INSERT INTO virtual_classes (id, course_id, class_code, title, description, instructor_id, instructor_name, scheduled_start, scheduled_end, actual_start, actual_end, platform, max_participants, recording_enabled, breakout_rooms_enabled, status) VALUES
    (1, 1, 'VC-CS101-001', 'Introduction to Python - Live Session', 'Interactive coding session on Python basics', 'PROF-001', 'Dr. Alice Chen', '2024-01-20 10:00:00', '2024-01-20 11:30:00', '2024-01-20 10:02:00', '2024-01-20 11:35:00', 'Zoom', 150, TRUE, TRUE, 'completed'),
    (2, 1, 'VC-CS101-002', 'Data Structures Workshop', 'Hands-on workshop on lists and dictionaries', 'PROF-001', 'Dr. Alice Chen', '2024-01-27 10:00:00', '2024-01-27 11:30:00', '2024-01-27 10:00:00', '2024-01-27 11:28:00', 'Zoom', 150, TRUE, TRUE, 'completed'),
    (3, 2, 'VC-MATH201-001', 'Integration Problem Solving', 'Live problem solving session', 'PROF-002', 'Dr. Robert Brown', '2024-01-22 14:00:00', '2024-01-22 15:30:00', '2024-01-22 14:01:00', '2024-01-22 15:32:00', 'Teams', 80, TRUE, FALSE, 'completed'),
    (4, 3, 'VC-PHYS101-001', 'Physics Lab Virtual Demo', 'Demonstration of motion experiments', 'PROF-003', 'Dr. Emily Wilson', '2024-01-25 09:00:00', '2024-01-25 10:30:00', '2024-01-25 09:00:00', '2024-01-25 10:25:00', 'Zoom', 120, TRUE, FALSE, 'completed'),
    (5, 1, 'VC-CS101-003', 'Office Hours - Q&A Session', 'Open Q&A for midterm preparation', 'PROF-001', 'Dr. Alice Chen', '2024-02-01 15:00:00', '2024-02-01 16:00:00', NULL, NULL, 'Zoom', 50, TRUE, FALSE, 'scheduled');

Step 9: Insert Session Recordings

Add recorded sessions.

INSERT INTO session_recordings (id, class_id, recording_type, recording_name, duration_seconds, file_size_mb, resolution, start_time, end_time, processing_status, view_count) VALUES
    (1, 1, 'main_room', 'CS101 Python Intro - Full Recording', 5580, 1450.5, '1080p', '2024-01-20 10:02:00', '2024-01-20 11:35:00', 'ready', 142),
    (2, 1, 'speaker_view', 'CS101 Python Intro - Speaker View', 5580, 980.2, '1080p', '2024-01-20 10:02:00', '2024-01-20 11:35:00', 'ready', 85),
    (3, 2, 'main_room', 'CS101 Data Structures Workshop', 5280, 1380.8, '1080p', '2024-01-27 10:00:00', '2024-01-27 11:28:00', 'ready', 128),
    (4, 3, 'main_room', 'MATH201 Integration Session', 5460, 1250.4, '1080p', '2024-01-22 14:01:00', '2024-01-22 15:32:00', 'ready', 76),
    (5, 4, 'main_room', 'PHYS101 Lab Demo', 5100, 1580.2, '1080p', '2024-01-25 09:00:00', '2024-01-25 10:25:00', 'ready', 118);

INSERT INTO session_recordings (id, class_id, recording_type, recording_name, duration_seconds, file_size_mb, processing_status) VALUES
    (6, 1, 'audio_only', 'CS101 Python Intro - Audio', 5580, 85.4, 'ready'),
    (7, 2, 'audio_only', 'CS101 Data Structures - Audio', 5280, 78.2, 'ready');

Step 10: Insert Breakout Room Data

Add breakout sessions.

INSERT INTO breakout_rooms (id, class_id, room_name, room_number, topic, max_participants, started_at, ended_at, duration_seconds) VALUES
    (1, 1, 'Group 1', 1, 'Variables Exercise', 6, '2024-01-20 10:30:00', '2024-01-20 10:50:00', 1200),
    (2, 1, 'Group 2', 2, 'Variables Exercise', 6, '2024-01-20 10:30:00', '2024-01-20 10:50:00', 1200),
    (3, 1, 'Group 3', 3, 'Variables Exercise', 6, '2024-01-20 10:30:00', '2024-01-20 10:50:00', 1200),
    (4, 2, 'Team Alpha', 1, 'List Operations Challenge', 5, '2024-01-27 10:35:00', '2024-01-27 10:55:00', 1200),
    (5, 2, 'Team Beta', 2, 'List Operations Challenge', 5, '2024-01-27 10:35:00', '2024-01-27 10:55:00', 1200),
    (6, 2, 'Team Gamma', 3, 'Dictionary Exercises', 5, '2024-01-27 10:35:00', '2024-01-27 10:55:00', 1200);

Step 11: Insert Participants

Add attendance data.

INSERT INTO class_participants (id, class_id, participant_id, participant_name, participant_role, joined_at, left_at, duration_minutes, device_type, camera_on_minutes, mic_on_minutes, chat_message_count) VALUES
    (1, 1, 'PROF-001', 'Dr. Alice Chen', 'host', '2024-01-20 09:58:00', '2024-01-20 11:35:00', 97, 'desktop', 97, 97, 15),
    (2, 1, 'STU-001', 'John Doe', 'participant', '2024-01-20 10:00:00', '2024-01-20 11:35:00', 95, 'desktop', 45, 12, 8),
    (3, 1, 'STU-002', 'Jane Smith', 'participant', '2024-01-20 10:02:00', '2024-01-20 11:30:00', 88, 'laptop', 88, 5, 12),
    (4, 1, 'STU-003', 'Mike Johnson', 'participant', '2024-01-20 10:05:00', '2024-01-20 11:35:00', 90, 'tablet', 0, 3, 5),
    (5, 1, 'STU-004', 'Sarah Wilson', 'participant', '2024-01-20 10:01:00', '2024-01-20 11:35:00', 94, 'desktop', 60, 8, 10),
    (6, 1, 'TA-001', 'Alex Turner', 'co-host', '2024-01-20 09:55:00', '2024-01-20 11:35:00', 100, 'desktop', 100, 45, 25),
    (7, 2, 'PROF-001', 'Dr. Alice Chen', 'host', '2024-01-27 09:58:00', '2024-01-27 11:28:00', 90, 'desktop', 90, 90, 12),
    (8, 2, 'STU-001', 'John Doe', 'participant', '2024-01-27 10:00:00', '2024-01-27 11:28:00', 88, 'desktop', 50, 15, 6);

Step 12: Insert Chat Messages

Add chat history.

INSERT INTO class_chat_messages (id, class_id, sender_id, sender_name, recipient_type, message_text, message_type, timestamp_seconds, sent_at) VALUES
    (1, 1, 'PROF-001', 'Dr. Alice Chen', 'everyone', 'Welcome everyone! We will start in 2 minutes.', 'text', 0, '2024-01-20 10:00:00'),
    (2, 1, 'STU-001', 'John Doe', 'everyone', 'Good morning!', 'text', 60, '2024-01-20 10:01:00'),
    (3, 1, 'STU-002', 'Jane Smith', 'everyone', 'Excited for today session!', 'text', 90, '2024-01-20 10:01:30'),
    (4, 1, 'PROF-001', 'Dr. Alice Chen', 'everyone', 'Link to today code: https://github.com/example/cs101', 'link', 300, '2024-01-20 10:05:00'),
    (5, 1, 'STU-003', 'Mike Johnson', 'everyone', 'Can you repeat that last part?', 'text', 1200, '2024-01-20 10:20:00'),
    (6, 1, 'TA-001', 'Alex Turner', 'private', 'I can help you with that question', 'text', 1250, '2024-01-20 10:20:50'),
    (7, 1, 'PROF-001', 'Dr. Alice Chen', 'everyone', 'Great question! Let me clarify...', 'text', 1260, '2024-01-20 10:21:00'),
    (8, 1, 'STU-004', 'Sarah Wilson', 'everyone', 'Thank you, that makes sense now!', 'text', 1500, '2024-01-20 10:25:00');

Step 13: Class Session Summary

View session overview.

SELECT
    vc.class_code,
    vc.title,
    vc.instructor_name,
    vc.scheduled_start,
    vc.actual_start,
    COUNT(DISTINCT cp.id) as total_participants,
    AVG(cp.duration_minutes) as avg_attendance_minutes,
    SUM(cp.chat_message_count) as total_chat_messages,
    sr.duration_seconds / 60 as recording_minutes,
    sr.view_count
FROM virtual_classes vc
LEFT JOIN class_participants cp ON vc.id = cp.class_id
LEFT JOIN session_recordings sr ON vc.id = sr.class_id AND sr.recording_type = 'main_room'
WHERE vc.status = 'completed'
GROUP BY vc.id, vc.class_code, vc.title, vc.instructor_name, vc.scheduled_start, vc.actual_start, sr.duration_seconds, sr.view_count
ORDER BY vc.scheduled_start DESC;

Step 14: Participant Engagement Report

Analyze student engagement.

SELECT
    cp.participant_name,
    cp.participant_role,
    cp.duration_minutes,
    cp.camera_on_minutes,
    cp.mic_on_minutes,
    cp.chat_message_count,
    cp.raised_hand_count,
    CASE
        WHEN cp.camera_on_minutes > 60 AND cp.chat_message_count > 5 THEN 'High'
        WHEN cp.camera_on_minutes > 30 OR cp.chat_message_count > 2 THEN 'Medium'
        ELSE 'Low'
    END as engagement_level
FROM class_participants cp
WHERE cp.class_id = 1
  AND cp.participant_role = 'participant'
ORDER BY cp.duration_minutes DESC;

Step 15: Recording Analytics

Track viewing patterns.

SELECT
    vc.course_id,
    vc.title,
    sr.recording_type,
    sr.duration_seconds / 60 as duration_minutes,
    sr.file_size_mb,
    sr.view_count,
    COUNT(DISTINCT br.id) as breakout_recordings,
    SUM(br.duration_seconds) / 60 as breakout_total_minutes
FROM virtual_classes vc
INNER JOIN session_recordings sr ON vc.id = sr.class_id
LEFT JOIN breakout_rooms br ON vc.id = br.class_id
WHERE sr.recording_type = 'main_room'
GROUP BY vc.id, vc.course_id, vc.title, sr.recording_type, sr.duration_seconds, sr.file_size_mb, sr.view_count
ORDER BY sr.view_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS class_polls;
DROP TABLE IF EXISTS class_chat_messages;
DROP TABLE IF EXISTS class_participants;
DROP TABLE IF EXISTS screen_shares;
DROP TABLE IF EXISTS breakout_rooms;
DROP TABLE IF EXISTS session_recordings;
DROP TABLE IF EXISTS virtual_classes;

Expected Outcomes

• Virtual classes organized
• Recordings captured
• Breakout rooms tracked
• Chat history preserved
• Engagement analyzed

Key Concepts Learned

• Virtual classroom management
• Multi-view recordings
• Breakout room tracking
• Participation analytics
• Chat log preservation