Educational Slide Decks

Objective

Create a system for managing educational slide decks with version control, slide-level annotations, student note-taking, and sharing capabilities.

Step 1: Create Slide Decks Table

Store presentation metadata.

CREATE TABLE slide_decks (
    id INTEGER PRIMARY KEY,
    course_id INTEGER,
    deck_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    topic VARCHAR(100),
    author_id VARCHAR(50),
    author_name VARCHAR(200),
    deck_file PDF,
    thumbnail IMAGE(JPEG),
    total_slides INTEGER,
    file_size_mb DECIMAL(10, 2),
    version_number INTEGER DEFAULT 1,
    language VARCHAR(20) DEFAULT 'en',
    license_type VARCHAR(50),
    is_downloadable BOOLEAN DEFAULT TRUE,
    view_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

Step 2: Create Individual Slides Table

Store slide images and content.

CREATE TABLE slides (
    id INTEGER PRIMARY KEY,
    deck_id INTEGER NOT NULL,
    slide_number INTEGER NOT NULL,
    slide_image IMAGE(PNG),
    slide_title VARCHAR(200),
    content_text TEXT,
    speaker_notes TEXT,
    layout_type VARCHAR(50),
    has_animation BOOLEAN DEFAULT FALSE,
    has_video BOOLEAN DEFAULT FALSE,
    video_url VARCHAR(500),
    duration_seconds INTEGER,
    FOREIGN KEY (deck_id) REFERENCES slide_decks(id)
);

Step 3: Create Deck Versions Table

Track version history.

CREATE TABLE deck_versions (
    id INTEGER PRIMARY KEY,
    deck_id INTEGER NOT NULL,
    version_number INTEGER NOT NULL,
    deck_file PDF,
    change_summary TEXT,
    changed_by VARCHAR(100),
    total_slides INTEGER,
    file_size_mb DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (deck_id) REFERENCES slide_decks(id)
);

Step 4: Create Slide Annotations Table

Store instructor annotations.

CREATE TABLE slide_annotations (
    id INTEGER PRIMARY KEY,
    slide_id INTEGER NOT NULL,
    annotator_id VARCHAR(50),
    annotator_name VARCHAR(200),
    annotation_type VARCHAR(50),
    x_position DECIMAL(5, 2),
    y_position DECIMAL(5, 2),
    width DECIMAL(5, 2),
    height DECIMAL(5, 2),
    content TEXT,
    color VARCHAR(20),
    annotation_image IMAGE(PNG),
    is_visible_to_students BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (slide_id) REFERENCES slides(id)
);

Step 5: Create Student Notes Table

Allow personal notes.

CREATE TABLE student_slide_notes (
    id INTEGER PRIMARY KEY,
    slide_id INTEGER NOT NULL,
    student_id VARCHAR(50) NOT NULL,
    note_text TEXT,
    highlight_color VARCHAR(20),
    is_bookmarked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (slide_id) REFERENCES slides(id)
);

Step 6: Create Deck Access Log Table

Track viewing activity.

CREATE TABLE deck_access_log (
    id INTEGER PRIMARY KEY,
    deck_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    user_type VARCHAR(50),
    access_type VARCHAR(50),
    slides_viewed INTEGER,
    time_spent_seconds INTEGER,
    downloaded BOOLEAN DEFAULT FALSE,
    access_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    device_type VARCHAR(50),
    FOREIGN KEY (deck_id) REFERENCES slide_decks(id)
);

Step 7: Insert Sample Slide Decks

Add presentations.

INSERT INTO slide_decks (id, course_id, deck_code, title, description, topic, author_id, author_name, total_slides, file_size_mb, version_number, status, published_at, view_count, download_count) VALUES
    (1, 1, 'CS101-W1', 'Introduction to Programming', 'Week 1 lecture slides covering basics', 'Programming Basics', 'PROF-001', 'Dr. Alice Chen', 45, 12.5, 3, 'published', '2024-01-10 08:00:00', 324, 156),
    (2, 1, 'CS101-W2', 'Variables and Data Types', 'Week 2 lecture on data types', 'Data Types', 'PROF-001', 'Dr. Alice Chen', 38, 8.2, 2, 'published', '2024-01-17 08:00:00', 298, 142),
    (3, 1, 'CS101-W3', 'Control Structures', 'Conditionals and loops', 'Control Flow', 'PROF-001', 'Dr. Alice Chen', 52, 15.8, 1, 'published', '2024-01-24 08:00:00', 276, 128),
    (4, 2, 'MATH201-W1', 'Review of Calculus I', 'Refresher on derivatives', 'Calculus Review', 'PROF-002', 'Dr. Robert Brown', 35, 6.4, 2, 'published', '2024-01-15 08:00:00', 185, 92),
    (5, 2, 'MATH201-W2', 'Integration Techniques', 'Methods of integration', 'Integration', 'PROF-002', 'Dr. Robert Brown', 48, 9.8, 1, 'published', '2024-01-22 08:00:00', 172, 86),
    (6, 3, 'PHYS101-W1', 'Introduction to Mechanics', 'Newton laws and motion', 'Mechanics', 'PROF-003', 'Dr. Emily Wilson', 42, 18.5, 2, 'published', '2024-01-12 08:00:00', 245, 118);

Step 8: Insert Individual Slides

Add slide content.

INSERT INTO slides (id, deck_id, slide_number, slide_title, content_text, speaker_notes, layout_type, has_animation) VALUES
    (1, 1, 1, 'Welcome to CS101', 'Introduction to Computer Science', 'Welcome students, introduce yourself', 'title', FALSE),
    (2, 1, 2, 'Course Overview', 'What we will learn this semester', 'Review syllabus highlights', 'bullet_list', FALSE),
    (3, 1, 3, 'What is Programming?', 'Programming is the process of creating instructions for computers', 'Start with analogy of recipes', 'content', FALSE),
    (4, 1, 4, 'Why Learn Programming?', 'Career opportunities, problem-solving skills, automation', 'Emphasize practical applications', 'bullet_list', TRUE),
    (5, 1, 5, 'Programming Languages', 'Different languages for different purposes', 'Show language popularity chart', 'two_column', FALSE),
    (6, 2, 1, 'Variables', 'Named storage locations in memory', 'Use box analogy', 'title', FALSE),
    (7, 2, 2, 'Data Types Overview', 'Integer, Float, String, Boolean', 'Show examples of each', 'bullet_list', FALSE),
    (8, 2, 3, 'Integers', 'Whole numbers without decimal points', 'Examples: 1, 42, -17', 'content', FALSE),
    (9, 2, 4, 'Floating Point', 'Numbers with decimal places', 'Mention precision limitations', 'content', FALSE),
    (10, 2, 5, 'Strings', 'Text data enclosed in quotes', 'Show string operations', 'content', TRUE);

Step 9: Insert Version History

Track deck updates.

INSERT INTO deck_versions (id, deck_id, version_number, change_summary, changed_by, total_slides, file_size_mb, created_at) VALUES
    (1, 1, 1, 'Initial version', 'Dr. Alice Chen', 40, 10.2, '2024-01-05 10:00:00'),
    (2, 1, 2, 'Added 3 slides on history of computing', 'Dr. Alice Chen', 43, 11.8, '2024-01-08 14:00:00'),
    (3, 1, 3, 'Fixed typos, updated diagrams', 'Dr. Alice Chen', 45, 12.5, '2024-01-10 08:00:00'),
    (4, 2, 1, 'Initial version', 'Dr. Alice Chen', 35, 7.5, '2024-01-12 09:00:00'),
    (5, 2, 2, 'Added code examples', 'Dr. Alice Chen', 38, 8.2, '2024-01-17 08:00:00'),
    (6, 4, 1, 'Initial version', 'Dr. Robert Brown', 32, 5.8, '2024-01-10 11:00:00'),
    (7, 4, 2, 'Added practice problems', 'Dr. Robert Brown', 35, 6.4, '2024-01-15 08:00:00');

Step 10: Insert Annotations and Notes

Add markup and student notes.

INSERT INTO slide_annotations (id, slide_id, annotator_id, annotator_name, annotation_type, x_position, y_position, content, color, is_visible_to_students) VALUES
    (1, 3, 'PROF-001', 'Dr. Alice Chen', 'highlight', 20.0, 30.0, 'Key concept - emphasize this', 'yellow', TRUE),
    (2, 3, 'PROF-001', 'Dr. Alice Chen', 'note', 80.0, 50.0, 'Common exam question topic', 'blue', TRUE),
    (3, 5, 'PROF-001', 'Dr. Alice Chen', 'circle', 50.0, 60.0, 'Python highlighted as course language', 'red', TRUE),
    (4, 8, 'PROF-001', 'Dr. Alice Chen', 'underline', 30.0, 45.0, 'Memory representation important', 'green', TRUE);

INSERT INTO student_slide_notes (id, slide_id, student_id, note_text, highlight_color, is_bookmarked) VALUES
    (1, 3, 'STU-001', 'Programming = giving instructions to computers. Remember the recipe analogy!', NULL, TRUE),
    (2, 4, 'STU-001', 'Career paths: web dev, data science, AI/ML, game dev', 'yellow', FALSE),
    (3, 6, 'STU-001', 'Variable = box with a name that holds data', 'blue', TRUE),
    (4, 8, 'STU-002', 'Integers cant have decimals - use float for decimals', NULL, FALSE),
    (5, 3, 'STU-002', 'Review this before midterm', 'red', TRUE);

Step 11: Insert Access Logs

Track viewing activity.

INSERT INTO deck_access_log (id, deck_id, user_id, user_type, access_type, slides_viewed, time_spent_seconds, downloaded, device_type, access_time) VALUES
    (1, 1, 'STU-001', 'student', 'view', 45, 1800, TRUE, 'desktop', '2024-01-15 14:30:00'),
    (2, 1, 'STU-002', 'student', 'view', 30, 1200, FALSE, 'tablet', '2024-01-15 15:00:00'),
    (3, 1, 'STU-003', 'student', 'view', 45, 2400, TRUE, 'desktop', '2024-01-16 10:00:00'),
    (4, 2, 'STU-001', 'student', 'view', 38, 1500, TRUE, 'desktop', '2024-01-20 16:00:00'),
    (5, 2, 'STU-002', 'student', 'view', 25, 900, FALSE, 'mobile', '2024-01-21 09:00:00'),
    (6, 1, 'PROF-001', 'instructor', 'edit', 5, 600, FALSE, 'desktop', '2024-01-22 08:00:00');

Step 12: Course Materials Overview

View all deck materials.

SELECT
    sd.deck_code,
    sd.title,
    sd.author_name,
    sd.total_slides,
    sd.version_number,
    sd.view_count,
    sd.download_count,
    sd.published_at
FROM slide_decks sd
WHERE sd.course_id = 1
  AND sd.status = 'published'
ORDER BY sd.published_at;

Step 13: Student Notes Summary

View student's notes across decks.

SELECT
    sd.title as deck_title,
    s.slide_number,
    s.slide_title,
    ssn.note_text,
    ssn.is_bookmarked,
    ssn.created_at
FROM student_slide_notes ssn
INNER JOIN slides s ON ssn.slide_id = s.id
INNER JOIN slide_decks sd ON s.deck_id = sd.id
WHERE ssn.student_id = 'STU-001'
ORDER BY sd.published_at, s.slide_number;

Step 14: Deck Engagement Analytics

Analyze viewing patterns.

SELECT
    sd.deck_code,
    sd.title,
    COUNT(dal.id) as total_views,
    COUNT(DISTINCT dal.user_id) as unique_viewers,
    AVG(dal.slides_viewed) as avg_slides_viewed,
    AVG(dal.time_spent_seconds) / 60 as avg_minutes_spent,
    COUNT(CASE WHEN dal.downloaded THEN 1 END) as downloads
FROM slide_decks sd
LEFT JOIN deck_access_log dal ON sd.id = dal.deck_id
WHERE dal.user_type = 'student'
GROUP BY sd.id, sd.deck_code, sd.title
ORDER BY total_views DESC;

Step 15: Version Comparison

Track deck evolution.

SELECT
    sd.title,
    dv.version_number,
    dv.total_slides,
    dv.file_size_mb,
    dv.change_summary,
    dv.changed_by,
    dv.created_at
FROM slide_decks sd
INNER JOIN deck_versions dv ON sd.id = dv.deck_id
WHERE sd.id = 1
ORDER BY dv.version_number DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS deck_access_log;
DROP TABLE IF EXISTS student_slide_notes;
DROP TABLE IF EXISTS slide_annotations;
DROP TABLE IF EXISTS deck_versions;
DROP TABLE IF EXISTS slides;
DROP TABLE IF EXISTS slide_decks;

Expected Outcomes

• Slide decks organized by course
• Versions tracked
• Annotations visible
• Student notes saved
• Usage analytics available

Key Concepts Learned

• Presentation management
• Version control
• Collaborative annotations
• Personal note-taking
• Access analytics