Lecture Recording Management

Objective

Create a system for managing university lecture recordings, including scheduling, multi-camera capture, automatic transcription, and content searchability.

Step 1: Create Academic Terms Table

Define academic calendar.

CREATE TABLE academic_terms (
    id INTEGER PRIMARY KEY,
    term_code VARCHAR(20) NOT NULL UNIQUE,
    term_name VARCHAR(100),
    start_date DATE,
    end_date DATE,
    academic_year VARCHAR(20),
    is_current BOOLEAN DEFAULT FALSE
);

Step 2: Create Courses Table

Define course offerings.

CREATE TABLE lecture_courses (
    id INTEGER PRIMARY KEY,
    course_code VARCHAR(20) NOT NULL,
    course_name VARCHAR(200),
    department VARCHAR(100),
    term_id INTEGER NOT NULL,
    instructor_id VARCHAR(50),
    instructor_name VARCHAR(200),
    credits INTEGER,
    enrolled_students INTEGER,
    recording_enabled BOOLEAN DEFAULT TRUE,
    auto_publish BOOLEAN DEFAULT FALSE,
    retention_days INTEGER DEFAULT 365,
    FOREIGN KEY (term_id) REFERENCES academic_terms(id)
);

Step 3: Create Lecture Rooms Table

Define recording-enabled classrooms.

CREATE TABLE lecture_rooms (
    id INTEGER PRIMARY KEY,
    room_code VARCHAR(50) NOT NULL UNIQUE,
    building VARCHAR(100),
    room_number VARCHAR(20),
    capacity INTEGER,
    has_presenter_camera BOOLEAN DEFAULT TRUE,
    has_audience_camera BOOLEAN DEFAULT FALSE,
    has_document_camera BOOLEAN DEFAULT TRUE,
    has_screen_capture BOOLEAN DEFAULT TRUE,
    microphone_type VARCHAR(50),
    status VARCHAR(50) DEFAULT 'active'
);

Step 4: Create Lecture Schedule Table

Schedule recurring lectures.

CREATE TABLE lecture_schedule (
    id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    room_id INTEGER NOT NULL,
    day_of_week INTEGER,
    start_time TIME,
    end_time TIME,
    recording_preset VARCHAR(50) DEFAULT 'standard',
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (course_id) REFERENCES lecture_courses(id),
    FOREIGN KEY (room_id) REFERENCES lecture_rooms(id)
);

Step 5: Create Lecture Recordings Table

Store recorded lectures.

CREATE TABLE lecture_recordings (
    id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    room_id INTEGER NOT NULL,
    lecture_date DATE NOT NULL,
    lecture_number INTEGER,
    title VARCHAR(200),
    description TEXT,
    presenter_video VIDEO(MP4),
    screen_capture VIDEO(MP4),
    combined_video VIDEO(MP4),
    audio_only AUDIO(MP3),
    duration_seconds INTEGER,
    file_size_mb DECIMAL(10, 2),
    recording_quality VARCHAR(20),
    status VARCHAR(50) DEFAULT 'processing',
    published_at TIMESTAMP,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES lecture_courses(id),
    FOREIGN KEY (room_id) REFERENCES lecture_rooms(id)
);

Step 6: Create Transcripts Table

Store searchable transcripts.

CREATE TABLE lecture_transcripts (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    full_transcript TEXT,
    transcript_language VARCHAR(20) DEFAULT 'en',
    word_count INTEGER,
    confidence_score DECIMAL(3, 2),
    processing_time_seconds INTEGER,
    is_reviewed BOOLEAN DEFAULT FALSE,
    reviewed_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (recording_id) REFERENCES lecture_recordings(id)
);

Step 7: Create Transcript Segments Table

Enable timestamp navigation.

CREATE TABLE transcript_segments (
    id INTEGER PRIMARY KEY,
    transcript_id INTEGER NOT NULL,
    segment_number INTEGER,
    start_time_seconds INTEGER,
    end_time_seconds INTEGER,
    speaker_label VARCHAR(50),
    text_content TEXT,
    confidence DECIMAL(3, 2),
    FOREIGN KEY (transcript_id) REFERENCES lecture_transcripts(id)
);

Step 8: Create Lecture Slides Table

Track presentation materials.

CREATE TABLE lecture_slides (
    id INTEGER PRIMARY KEY,
    recording_id INTEGER NOT NULL,
    slide_number INTEGER,
    timestamp_seconds INTEGER,
    slide_image IMAGE(PNG),
    slide_text TEXT,
    FOREIGN KEY (recording_id) REFERENCES lecture_recordings(id)
);

Step 9: Insert Sample Data

Add academic structure.

INSERT INTO academic_terms (id, term_code, term_name, start_date, end_date, academic_year, is_current) VALUES
    (1, 'FALL2023', 'Fall 2023', '2023-09-01', '2023-12-15', '2023-2024', FALSE),
    (2, 'SPR2024', 'Spring 2024', '2024-01-15', '2024-05-15', '2023-2024', TRUE),
    (3, 'SUM2024', 'Summer 2024', '2024-06-01', '2024-08-15', '2023-2024', FALSE);

INSERT INTO lecture_rooms (id, room_code, building, room_number, capacity, has_presenter_camera, has_audience_camera, has_document_camera, has_screen_capture, microphone_type) VALUES
    (1, 'SCI-101', 'Science Building', '101', 150, TRUE, TRUE, TRUE, TRUE, 'ceiling_array'),
    (2, 'SCI-205', 'Science Building', '205', 80, TRUE, FALSE, TRUE, TRUE, 'lapel'),
    (3, 'ENG-301', 'Engineering Hall', '301', 200, TRUE, TRUE, TRUE, TRUE, 'ceiling_array'),
    (4, 'LIB-AUDIT', 'Library', 'Auditorium', 500, TRUE, TRUE, TRUE, TRUE, 'wireless_handheld'),
    (5, 'ART-110', 'Arts Center', '110', 60, TRUE, FALSE, FALSE, TRUE, 'desk');

INSERT INTO lecture_courses (id, course_code, course_name, department, term_id, instructor_id, instructor_name, credits, enrolled_students, recording_enabled) VALUES
    (1, 'CS101', 'Introduction to Computer Science', 'Computer Science', 2, 'PROF-001', 'Dr. Alice Chen', 4, 145, TRUE),
    (2, 'MATH201', 'Calculus II', 'Mathematics', 2, 'PROF-002', 'Dr. Robert Brown', 4, 78, TRUE),
    (3, 'PHYS101', 'Physics I', 'Physics', 2, 'PROF-003', 'Dr. Emily Wilson', 4, 120, TRUE),
    (4, 'BIO150', 'Introduction to Biology', 'Biology', 2, 'PROF-004', 'Dr. Michael Lee', 3, 95, TRUE),
    (5, 'ENG102', 'English Composition', 'English', 2, 'PROF-005', 'Dr. Sarah Davis', 3, 55, TRUE);

Step 10: Insert Lecture Schedule

Add class times.

INSERT INTO lecture_schedule (id, course_id, room_id, day_of_week, start_time, end_time, recording_preset) VALUES
    (1, 1, 1, 1, '09:00:00', '10:30:00', 'standard'),
    (2, 1, 1, 3, '09:00:00', '10:30:00', 'standard'),
    (3, 2, 2, 2, '11:00:00', '12:30:00', 'document_focus'),
    (4, 2, 2, 4, '11:00:00', '12:30:00', 'document_focus'),
    (5, 3, 3, 1, '14:00:00', '15:30:00', 'standard'),
    (6, 3, 3, 3, '14:00:00', '15:30:00', 'standard'),
    (7, 4, 1, 2, '09:00:00', '10:30:00', 'standard'),
    (8, 5, 5, 1, '13:00:00', '14:30:00', 'presenter_focus');

Step 11: Insert Lecture Recordings

Add recorded lectures.

INSERT INTO lecture_recordings (id, course_id, room_id, lecture_date, lecture_number, title, description, duration_seconds, file_size_mb, recording_quality, status, published_at, view_count) VALUES
    (1, 1, 1, '2024-01-15', 1, 'Course Introduction', 'Welcome and syllabus overview', 5400, 1250.5, '1080p', 'published', '2024-01-15 12:00:00', 142),
    (2, 1, 1, '2024-01-17', 2, 'Variables and Data Types', 'Introduction to programming concepts', 5280, 1180.2, '1080p', 'published', '2024-01-17 12:00:00', 138),
    (3, 1, 1, '2024-01-22', 3, 'Control Structures', 'If statements and loops', 5350, 1210.8, '1080p', 'published', '2024-01-22 12:00:00', 135),
    (4, 2, 2, '2024-01-16', 1, 'Review of Calculus I', 'Fundamental concepts review', 5400, 1150.3, '1080p', 'published', '2024-01-16 14:00:00', 75),
    (5, 2, 2, '2024-01-18', 2, 'Integration Techniques', 'Advanced integration methods', 5320, 1125.6, '1080p', 'published', '2024-01-18 14:00:00', 72),
    (6, 3, 3, '2024-01-15', 1, 'Mechanics Introduction', 'Newton laws overview', 5400, 1320.4, '1080p', 'published', '2024-01-15 17:00:00', 118),
    (7, 3, 3, '2024-01-17', 2, 'Motion in One Dimension', 'Velocity and acceleration', 5280, 1285.2, '1080p', 'processing', NULL, 0),
    (8, 4, 1, '2024-01-16', 1, 'The Cell', 'Cell structure and function', 5400, 1180.8, '1080p', 'published', '2024-01-16 12:00:00', 92);

Step 12: Insert Transcripts

Add lecture transcripts.

INSERT INTO lecture_transcripts (id, recording_id, full_transcript, transcript_language, word_count, confidence_score, is_reviewed) VALUES
    (1, 1, 'Welcome to CS101 Introduction to Computer Science. I am Dr. Alice Chen and I will be your instructor this semester...', 'en', 4250, 0.94, TRUE),
    (2, 2, 'Today we will discuss variables and data types. A variable is a named storage location in memory...', 'en', 4180, 0.92, TRUE),
    (3, 3, 'Control structures allow us to control the flow of program execution. The if statement...', 'en', 4320, 0.93, FALSE),
    (4, 4, 'Let us begin with a review of the fundamental concepts from Calculus I. Remember that...', 'en', 4150, 0.91, TRUE),
    (5, 6, 'Physics is the study of matter and energy. Newton first laws state that an object at rest...', 'en', 4280, 0.95, TRUE);

INSERT INTO transcript_segments (id, transcript_id, segment_number, start_time_seconds, end_time_seconds, speaker_label, text_content, confidence) VALUES
    (1, 1, 1, 0, 30, 'Instructor', 'Welcome to CS101 Introduction to Computer Science.', 0.96),
    (2, 1, 2, 30, 60, 'Instructor', 'I am Dr. Alice Chen and I will be your instructor this semester.', 0.94),
    (3, 1, 3, 60, 120, 'Instructor', 'Let me start by going over the syllabus and course expectations.', 0.93),
    (4, 1, 4, 120, 180, 'Instructor', 'This course will cover programming fundamentals using Python.', 0.95),
    (5, 2, 1, 0, 45, 'Instructor', 'Today we will discuss variables and data types.', 0.94),
    (6, 2, 2, 45, 90, 'Instructor', 'A variable is a named storage location in memory.', 0.92);

Step 13: Course Recording Overview

View all recordings.

SELECT
    lc.course_code,
    lc.course_name,
    lc.instructor_name,
    COUNT(lr.id) as total_recordings,
    SUM(lr.duration_seconds) / 3600 as total_hours,
    SUM(lr.view_count) as total_views,
    COUNT(CASE WHEN lr.status = 'published' THEN 1 END) as published
FROM lecture_courses lc
LEFT JOIN lecture_recordings lr ON lc.id = lr.course_id
WHERE lc.term_id = 2
GROUP BY lc.id, lc.course_code, lc.course_name, lc.instructor_name
ORDER BY total_recordings DESC;

Step 14: Search Transcripts

Find content in lectures.

SELECT
    lc.course_code,
    lr.lecture_number,
    lr.title,
    lr.lecture_date,
    ts.start_time_seconds,
    ts.text_content
FROM transcript_segments ts
INNER JOIN lecture_transcripts lt ON ts.transcript_id = lt.id
INNER JOIN lecture_recordings lr ON lt.recording_id = lr.id
INNER JOIN lecture_courses lc ON lr.course_id = lc.id
WHERE ts.text_content LIKE '%variable%'
ORDER BY lr.lecture_date, ts.start_time_seconds;

Step 15: Room Utilization Report

Analyze recording room usage.

SELECT
    rm.room_code,
    rm.building,
    rm.capacity,
    COUNT(DISTINCT lr.id) as recordings_count,
    SUM(lr.duration_seconds) / 3600 as total_hours,
    AVG(lr.file_size_mb) as avg_file_size_mb
FROM lecture_rooms rm
LEFT JOIN lecture_recordings lr ON rm.id = lr.room_id
    AND lr.lecture_date >= '2024-01-01'
GROUP BY rm.id, rm.room_code, rm.building, rm.capacity
ORDER BY recordings_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS lecture_slides;
DROP TABLE IF EXISTS transcript_segments;
DROP TABLE IF EXISTS lecture_transcripts;
DROP TABLE IF EXISTS lecture_recordings;
DROP TABLE IF EXISTS lecture_schedule;
DROP TABLE IF EXISTS lecture_rooms;
DROP TABLE IF EXISTS lecture_courses;
DROP TABLE IF EXISTS academic_terms;

Expected Outcomes

• Courses organized by term
• Recordings captured automatically
• Transcripts searchable
• Slides synchronized
• Usage tracked

Key Concepts Learned

• Academic scheduling
• Multi-source recording
• Transcript segmentation
• Content searchability
• Room utilization