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