Educational Resource Library

Objective

Create a comprehensive educational resource library that organizes learning materials across multiple formats, with categorization, tagging, access control, and recommendation features.

Step 1: Create Resource Categories Table

Define hierarchical categories.

CREATE TABLE resource_categories (
    id INTEGER PRIMARY KEY,
    category_code VARCHAR(50) NOT NULL UNIQUE,
    category_name VARCHAR(200) NOT NULL,
    parent_category_id INTEGER,
    description TEXT,
    icon_image IMAGE(PNG),
    display_order INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_category_id) REFERENCES resource_categories(id)
);

Step 2: Create Learning Resources Table

Store resource metadata.

CREATE TABLE learning_resources (
    id INTEGER PRIMARY KEY,
    resource_code VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INTEGER,
    resource_type VARCHAR(50),
    difficulty_level VARCHAR(20),
    target_audience TEXT,
    duration_minutes INTEGER,
    author_id VARCHAR(50),
    author_name VARCHAR(200),
    source VARCHAR(200),
    license_type VARCHAR(50),
    language VARCHAR(20) DEFAULT 'en',
    thumbnail IMAGE(JPEG),
    preview_image IMAGE(JPEG),
    is_featured BOOLEAN DEFAULT FALSE,
    is_premium BOOLEAN DEFAULT FALSE,
    view_count INTEGER DEFAULT 0,
    download_count INTEGER DEFAULT 0,
    average_rating DECIMAL(3, 2),
    rating_count INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES resource_categories(id)
);

Step 3: Create Resource Files Table

Store actual media files.

CREATE TABLE resource_files (
    id INTEGER PRIMARY KEY,
    resource_id INTEGER NOT NULL,
    file_type VARCHAR(50),
    file_name VARCHAR(255),
    video_file VIDEO(MP4),
    audio_file AUDIO(MP3),
    document_file PDF,
    image_file IMAGE(JPEG),
    file_size_mb DECIMAL(10, 2),
    duration_seconds INTEGER,
    page_count INTEGER,
    resolution VARCHAR(20),
    quality_level VARCHAR(20),
    is_primary BOOLEAN DEFAULT FALSE,
    download_allowed BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (resource_id) REFERENCES learning_resources(id)
);

Step 4: Create Resource Tags Table

Enable flexible tagging.

CREATE TABLE resource_tags (
    id INTEGER PRIMARY KEY,
    tag_name VARCHAR(100) NOT NULL UNIQUE,
    tag_category VARCHAR(50),
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE resource_tag_mapping (
    id INTEGER PRIMARY KEY,
    resource_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (resource_id) REFERENCES learning_resources(id),
    FOREIGN KEY (tag_id) REFERENCES resource_tags(id)
);

Step 5: Create User Collections Table

Allow personal collections.

CREATE TABLE user_collections (
    id INTEGER PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    collection_name VARCHAR(200),
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE collection_items (
    id INTEGER PRIMARY KEY,
    collection_id INTEGER NOT NULL,
    resource_id INTEGER NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    display_order INTEGER,
    FOREIGN KEY (collection_id) REFERENCES user_collections(id),
    FOREIGN KEY (resource_id) REFERENCES learning_resources(id)
);

Step 6: Create Resource Ratings Table

Track user ratings.

CREATE TABLE resource_ratings (
    id INTEGER PRIMARY KEY,
    resource_id INTEGER NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    rating INTEGER,
    review_text TEXT,
    helpful_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (resource_id) REFERENCES learning_resources(id)
);

Step 7: Create Access Log Table

Track resource usage.

CREATE TABLE resource_access_log (
    id INTEGER PRIMARY KEY,
    resource_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    access_type VARCHAR(50),
    duration_seconds INTEGER,
    completion_percent DECIMAL(5, 2),
    device_type VARCHAR(50),
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (resource_id) REFERENCES learning_resources(id)
);

Step 8: Insert Sample Categories

Add category structure.

INSERT INTO resource_categories (id, category_code, category_name, parent_category_id, description, display_order) VALUES
    (1, 'PROG', 'Programming', NULL, 'Programming and software development resources', 1),
    (2, 'PROG-PY', 'Python', 1, 'Python programming language', 1),
    (3, 'PROG-JS', 'JavaScript', 1, 'JavaScript and web development', 2),
    (4, 'PROG-JAVA', 'Java', 1, 'Java programming', 3),
    (5, 'DATA', 'Data Science', NULL, 'Data science and analytics', 2),
    (6, 'DATA-ML', 'Machine Learning', 5, 'ML algorithms and models', 1),
    (7, 'DATA-VIZ', 'Data Visualization', 5, 'Charts and visual analytics', 2),
    (8, 'MATH', 'Mathematics', NULL, 'Math concepts and tutorials', 3),
    (9, 'MATH-CALC', 'Calculus', 8, 'Differential and integral calculus', 1),
    (10, 'MATH-STAT', 'Statistics', 8, 'Statistical methods', 2),
    (11, 'SCIENCE', 'Science', NULL, 'Natural sciences', 4),
    (12, 'SCI-PHYS', 'Physics', 11, 'Physics concepts and experiments', 1),
    (13, 'SCI-BIO', 'Biology', 11, 'Life sciences', 2);

Step 9: Insert Learning Resources

Add resource content.

INSERT INTO learning_resources (id, resource_code, title, description, category_id, resource_type, difficulty_level, duration_minutes, author_name, source, language, is_featured, view_count, download_count, average_rating, rating_count, status, published_at) VALUES
    (1, 'RES-PY-001', 'Python Basics Video Tutorial', 'Complete introduction to Python programming', 2, 'video', 'beginner', 120, 'Dr. Alice Chen', 'Internal', 'en', TRUE, 5420, 2150, 4.8, 342, 'published', '2023-06-01 00:00:00'),
    (2, 'RES-PY-002', 'Python Cheat Sheet', 'Quick reference for Python syntax', 2, 'document', 'beginner', 10, 'Dr. Alice Chen', 'Internal', 'en', FALSE, 8920, 6540, 4.9, 521, 'published', '2023-06-15 00:00:00'),
    (3, 'RES-JS-001', 'JavaScript Fundamentals', 'Learn JavaScript from scratch', 3, 'video', 'beginner', 180, 'Prof. Bob Williams', 'Internal', 'en', TRUE, 4280, 1820, 4.7, 285, 'published', '2023-07-01 00:00:00'),
    (4, 'RES-ML-001', 'Introduction to Machine Learning', 'ML concepts and algorithms overview', 6, 'video', 'intermediate', 240, 'Dr. Sarah Lee', 'Internal', 'en', TRUE, 3150, 1240, 4.6, 198, 'published', '2023-08-01 00:00:00'),
    (5, 'RES-ML-002', 'ML Algorithms Handbook', 'Comprehensive guide to ML algorithms', 6, 'document', 'intermediate', 60, 'Dr. Sarah Lee', 'Internal', 'en', FALSE, 2840, 1920, 4.8, 156, 'published', '2023-08-15 00:00:00'),
    (6, 'RES-CALC-001', 'Calculus Explained', 'Visual guide to calculus concepts', 9, 'video', 'beginner', 150, 'Dr. Robert Brown', 'Internal', 'en', FALSE, 2420, 980, 4.5, 142, 'published', '2023-09-01 00:00:00'),
    (7, 'RES-PHYS-001', 'Physics Lab Simulations', 'Interactive physics experiments', 12, 'interactive', 90, 'Dr. Emily Wilson', 'Internal', 'en', TRUE, 1890, 450, 4.7, 98, 'published', '2023-10-01 00:00:00'),
    (8, 'RES-PY-003', 'Python Audio Course', 'Learn Python while commuting', 2, 'audio', 'beginner', 300, 'Dr. Alice Chen', 'Internal', 'en', FALSE, 1520, 890, 4.4, 76, 'published', '2023-11-01 00:00:00');

Step 10: Insert Resource Files

Add media files.

INSERT INTO resource_files (id, resource_id, file_type, file_name, file_size_mb, duration_seconds, resolution, quality_level, is_primary, download_allowed) VALUES
    (1, 1, 'video', 'python_basics_1080p.mp4', 2450.5, 7200, '1080p', 'high', TRUE, TRUE),
    (2, 1, 'video', 'python_basics_720p.mp4', 1280.2, 7200, '720p', 'medium', FALSE, TRUE),
    (3, 1, 'video', 'python_basics_480p.mp4', 680.8, 7200, '480p', 'low', FALSE, TRUE),
    (4, 3, 'video', 'js_fundamentals_1080p.mp4', 3250.4, 10800, '1080p', 'high', TRUE, TRUE),
    (5, 4, 'video', 'ml_intro_1080p.mp4', 4120.6, 14400, '1080p', 'high', TRUE, TRUE),
    (6, 6, 'video', 'calculus_explained.mp4', 2850.2, 9000, '1080p', 'high', TRUE, TRUE);

INSERT INTO resource_files (id, resource_id, file_type, file_name, file_size_mb, page_count, is_primary, download_allowed) VALUES
    (7, 2, 'document', 'python_cheatsheet.pdf', 2.5, 4, TRUE, TRUE),
    (8, 5, 'document', 'ml_handbook.pdf', 15.8, 120, TRUE, TRUE);

INSERT INTO resource_files (id, resource_id, file_type, file_name, file_size_mb, duration_seconds, is_primary, download_allowed) VALUES
    (9, 8, 'audio', 'python_audio_course.mp3', 285.4, 18000, TRUE, TRUE);

Step 11: Insert Tags and Mappings

Add resource tags.

INSERT INTO resource_tags (id, tag_name, tag_category, usage_count) VALUES
    (1, 'python', 'language', 3),
    (2, 'beginner-friendly', 'difficulty', 5),
    (3, 'video-tutorial', 'format', 4),
    (4, 'cheat-sheet', 'format', 1),
    (5, 'javascript', 'language', 1),
    (6, 'machine-learning', 'topic', 2),
    (7, 'algorithms', 'topic', 2),
    (8, 'data-science', 'topic', 2),
    (9, 'calculus', 'topic', 1),
    (10, 'physics', 'topic', 1);

INSERT INTO resource_tag_mapping (id, resource_id, tag_id) VALUES
    (1, 1, 1), (2, 1, 2), (3, 1, 3),
    (4, 2, 1), (5, 2, 2), (6, 2, 4),
    (7, 3, 5), (8, 3, 2), (9, 3, 3),
    (10, 4, 6), (11, 4, 7), (12, 4, 3),
    (13, 5, 6), (14, 5, 7), (15, 5, 8),
    (16, 6, 9), (17, 6, 2), (18, 6, 3),
    (19, 7, 10), (20, 8, 1), (21, 8, 2);

Step 12: Insert User Data

Add collections and ratings.

INSERT INTO user_collections (id, user_id, collection_name, description, is_public) VALUES
    (1, 'STU-001', 'Python Learning Path', 'My Python study materials', FALSE),
    (2, 'STU-001', 'Data Science Resources', 'Resources for DS course', FALSE),
    (3, 'STU-002', 'Programming Favorites', 'Best programming tutorials', TRUE);

INSERT INTO collection_items (id, collection_id, resource_id, display_order) VALUES
    (1, 1, 1, 1), (2, 1, 2, 2), (3, 1, 8, 3),
    (4, 2, 4, 1), (5, 2, 5, 2),
    (6, 3, 1, 1), (7, 3, 3, 2), (8, 3, 4, 3);

INSERT INTO resource_ratings (id, resource_id, user_id, rating, review_text, helpful_count, created_at) VALUES
    (1, 1, 'STU-001', 5, 'Excellent tutorial! Clear explanations and great examples.', 24, '2024-01-10 14:00:00'),
    (2, 1, 'STU-002', 5, 'Perfect for beginners. Highly recommend.', 18, '2024-01-12 10:00:00'),
    (3, 2, 'STU-001', 5, 'Very handy reference. I use it daily.', 45, '2024-01-11 09:00:00'),
    (4, 3, 'STU-003', 4, 'Good content but a bit long.', 8, '2024-01-15 16:00:00'),
    (5, 4, 'STU-002', 5, 'Great introduction to ML concepts.', 12, '2024-01-18 11:00:00');

Step 13: Browse Resources by Category

View category contents.

SELECT
    rc.category_name,
    lr.title,
    lr.resource_type,
    lr.difficulty_level,
    lr.duration_minutes,
    lr.author_name,
    lr.average_rating,
    lr.view_count,
    lr.is_featured
FROM learning_resources lr
INNER JOIN resource_categories rc ON lr.category_id = rc.id
WHERE rc.parent_category_id = 1
  AND lr.status = 'published'
ORDER BY lr.is_featured DESC, lr.average_rating DESC;

Step 14: Popular Resources Report

Analyze resource popularity.

SELECT
    lr.title,
    lr.resource_type,
    rc.category_name,
    lr.view_count,
    lr.download_count,
    lr.average_rating,
    lr.rating_count,
    lr.view_count + lr.download_count as total_engagement
FROM learning_resources lr
INNER JOIN resource_categories rc ON lr.category_id = rc.id
WHERE lr.status = 'published'
ORDER BY total_engagement DESC
LIMIT 10;

Step 15: User Learning Activity

Track user engagement.

SELECT
    uc.collection_name,
    COUNT(ci.id) as items_count,
    COUNT(DISTINCT lr.category_id) as categories_covered,
    SUM(lr.duration_minutes) as total_duration_minutes,
    AVG(lr.average_rating) as avg_quality_rating
FROM user_collections uc
INNER JOIN collection_items ci ON uc.id = ci.collection_id
INNER JOIN learning_resources lr ON ci.resource_id = lr.id
WHERE uc.user_id = 'STU-001'
GROUP BY uc.id, uc.collection_name
ORDER BY items_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS resource_access_log;
DROP TABLE IF EXISTS resource_ratings;
DROP TABLE IF EXISTS collection_items;
DROP TABLE IF EXISTS user_collections;
DROP TABLE IF EXISTS resource_tag_mapping;
DROP TABLE IF EXISTS resource_tags;
DROP TABLE IF EXISTS resource_files;
DROP TABLE IF EXISTS learning_resources;
DROP TABLE IF EXISTS resource_categories;

Expected Outcomes

• Resources organized by category
• Multiple file formats supported
• Tags enable discovery
• Collections personalize learning
• Analytics track usage

Key Concepts Learned

• Resource categorization
• Multi-format media storage
• Tagging and search
• User collections
• Engagement analytics