🌟 KnowledgeStar Learning Management System

A Database-Driven E-Learning Platform with AI-Powered Content Generation
16+ Normalized Tables | Complex Relationships | Transaction-Safe Operations | AI Integration

---

📋 Table of Contents

• 🎯 Platform Features
• 🗄️ Database Architecture
• 🤖 AI-Powered Tools
• 🛠️ Tech Stack
• 🚦 System Architecture
• 🚀 Quick Start

---

🎯 Platform Features

📚 Course Discovery

Database Query:

SELECT c.*, u.first_name || ' ' || u.last_name as instructor_name,
       COUNT(DISTINCT e.student_id) as enrollment_count
FROM courses c
JOIN instructors i ON c.instructor_id = i.instructor_id
JOIN users u ON i.user_id = u.user_id
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE c.is_published = true
GROUP BY c.course_id, u.first_name, u.last_name
ORDER BY enrollment_count DESC;

---

📊 Student Dashboard - Real-Time Analytics

Powered by:

• Aggregate queries across enrollments, student_progress, quiz_attempts
• Automatic completion_percentage calculation
• JOIN operations across 5+ tables
• Real-time data synchronization

---

📖 Course Hierarchy View & 🎥 Interactive Lessons

4-Level Normalized Structure:

courses (1) → course_modules (N) → module_lessons (N) → lesson_content (N)

• Prevents data redundancy
• Enables flexible content organization
• CASCADE deletes maintain integrity

Progress Tracking:

-- Insert/Update progress with transaction
BEGIN;
  INSERT INTO student_progress (enrollment_id, lesson_id, is_completed, time_spent_minutes)
  VALUES ($1, $2, true, $3)
  ON CONFLICT (enrollment_id, lesson_id) 
  DO UPDATE SET is_completed = true, 
                time_spent_minutes = student_progress.time_spent_minutes + $3,
                completed_at = NOW();
  
  -- Recalculate completion percentage
  UPDATE enrollments SET completion_percentage = (
    SELECT (COUNT(*) FILTER (WHERE is_completed)::DECIMAL / COUNT(*) * 100)
    FROM student_progress WHERE enrollment_id = $1
  ) WHERE enrollment_id = $1;
COMMIT;

---

📝 Quiz System with Transaction Safety

ACID Compliance:

// Atomic quiz submission
BEGIN TRANSACTION
  → INSERT quiz_attempt
  → For each answer: INSERT quiz_answers
  → Calculate total score
  → UPDATE quiz_attempt with final score
  → IF any error: ROLLBACK
COMMIT

Database Schema:

quizzes (quiz_id, course_id FK, title, passing_score, time_limit, max_attempts)
quiz_questions (question_id, quiz_id FK CASCADE, question_text, 
                question_type, points, correct_answer)
quiz_attempts (attempt_id, quiz_id FK, enrollment_id FK, 
               attempt_number, score, started_at, submitted_at)
quiz_answers (answer_id, attempt_id FK CASCADE, question_id FK, 
              student_answer, is_correct, points_earned)

-- Prevent duplicate attempts
UNIQUE(quiz_id, enrollment_id, attempt_number)

---

🤖 AI-Powered Tools

🧠 Gemini AI Integration for Instructors

AI-Generated Question Bank

• Topic-Based Questions: Instructors input a topic → Gemini generates multiple-choice, true/false, and short-answer questions
• Automatic Question Bank Population: AI-generated questions stored directly in quiz_questions table
• Quality Control: Instructors can review and edit before adding to quizzes

// Gemini API Integration
const generateQuestions = async (topic, questionCount, difficulty) => {
  const prompt = `Generate ${questionCount} ${difficulty} level questions about ${topic}
                  with answers and explanations in JSON format`;
  
  const response = await geminiAPI.generateContent(prompt);
  const questions = parseAIResponse(response);
  
  // Store in database
  BEGIN TRANSACTION
    questions.forEach(q => {
      INSERT INTO quiz_questions (quiz_id, question_text, question_type, 
                                   correct_answer, points)
      VALUES (...)
    });
  COMMIT
}

---

AI-Powered Syllabus Generator & AI-Generated Lesson Content

• Automated Content Creation: Generate lesson text, explanations, and examples
• Database Storage: Content saved to lesson_content table with type='text'
• Template-Based: Instructors provide outline → AI fills in details
• Course Outline Creation: Input course name + difficulty → Get complete syllabus
• Module Structure: AI suggests module breakdown and lesson topics
• Database Population: Generates hierarchical data for course_modules and module_lessons

-- AI-generated lesson stored in database
INSERT INTO lesson_content (lesson_id, content_type, content_text, content_order)
VALUES ($1, 'text', $gemini_generated_content, $2);

AI-Powered Syllabus Generator

// Generate and store syllabus
const aiSyllabus = await geminiAPI.generateSyllabus(courseTitle, difficulty);

BEGIN TRANSACTION
  // Create course
  INSERT INTO courses (instructor_id, title, description, ...) 
  VALUES (...) RETURNING course_id;
  
  // Create AI-generated modules
  aiSyllabus.modules.forEach((module, index) => {
    INSERT INTO course_modules (course_id, title, module_order)
    VALUES (course_id, module.title, index);
    
    // Create AI-generated lessons
    module.lessons.forEach((lesson, lessonIndex) => {
      INSERT INTO module_lessons (module_id, title, lesson_order)
      VALUES (module_id, lesson.title, lessonIndex);
    });
  });
COMMIT

Database Impact:

Gemini API → JSON Response → Parse → Transaction-Safe INSERT
                                          ↓
                         courses → course_modules → module_lessons
                                          ↓
                                   lesson_content

---

⚙️ Admin Panel with System Analytics

Complex Aggregate Queries:

-- Platform-wide statistics
SELECT 
  (SELECT COUNT(*) FROM users) as total_users,
  (SELECT COUNT(*) FROM enrollments) as total_enrollments,
  (SELECT AVG(completion_percentage) FROM enrollments) as avg_completion,
  (SELECT AVG(score) FROM quiz_attempts WHERE submitted_at IS NOT NULL) as platform_avg_score,
  (SELECT COUNT(*) FROM courses WHERE is_published = true) as published_courses;

---

🗄️ Database Architecture

Complete ERD - Normalized Schema Design

16+ Tables in 3NF/BCNF | Zero Redundancy | Full Referential Integrity

---

Database Design Philosophy

This project was built with an existing normalized database - no further normalization was required. The schema demonstrates industry-standard database design principles:

graph TB
    subgraph "👥 User Management"
        A[users] --> B[user_emails 1:N]
        A --> C[user_phones 1:N]
        A --> D[students 1:1]
        A --> E[instructors 1:1]
        A --> F[admins 1:1]
    end
    
    subgraph "📚 Course Hierarchy"
        G[courses] --> H[course_modules 1:N]
        H --> I[module_lessons 1:N]
        I --> J[lesson_content 1:N]
    end
    
    subgraph "📈 Progress Tracking"
        K[enrollments M:N] --> L[student_progress]
        K --> M[quiz_attempts]
        M --> N[quiz_answers]
    end
    
    E --> G
    D --> K
    G --> K
    
    style A fill:#E0E7FF,stroke:#4F46E5,stroke-width:3px
    style G fill:#D1FAE5,stroke:#059669,stroke-width:3px
    style K fill:#FEF3C7,stroke:#D97706,stroke-width:3px

Loading

---

Relationship Logical Mapping & Cardinality

Foreign key relationships with ON DELETE CASCADE for data integrity

Relationship	Type	Implementation
Users ↔ Emails/Phones	1:N	Multi-valued attributes in separate tables
Users ↔ Role Tables	1:1	ISA relationship pattern
Instructors ↔ Courses	1:N	One instructor teaches many courses
Students ↔ Courses	M:N	Junction table: enrollments
Course → Module → Lesson → Content	1:N	4-level hierarchy with CASCADE

---

Core Schema Structure

🔹 User Authentication & Roles

-- Base user entity
users (user_id, username, password_hash, first_name, last_name, created_at)

-- Multi-valued attributes (1NF compliance)
user_emails (email_id, user_id FK CASCADE, email UNIQUE, is_primary)
user_phones (phone_id, user_id FK CASCADE, phone, is_primary)

-- Role specialization (3NF - eliminates transitive dependencies)
students (student_id, user_id FK UNIQUE CASCADE, enrollment_date, major)
instructors (instructor_id, user_id FK UNIQUE CASCADE, hire_date, department, bio)
admins (admin_id, user_id FK UNIQUE CASCADE, admin_level)

Design Rationale:

• ✅ 1NF: No repeating groups (emails/phones in separate tables)
• ✅ 3NF: No transitive dependencies (role data separated)

---

🔹 Course Content Hierarchy

courses (course_id, instructor_id FK, title, description, 
         category, difficulty_level, is_published, created_at)
    ↓
course_modules (module_id, course_id FK CASCADE, title, 
                module_order, description)
    ↓
module_lessons (lesson_id, module_id FK CASCADE, title, 
                lesson_order, duration_minutes)
    ↓
lesson_content (content_id, lesson_id FK CASCADE, content_type, 
                content_url, content_text, content_order)

Key Constraints:

UNIQUE(course_id, module_order)  -- No duplicate ordering
UNIQUE(module_id, lesson_order)  -- Sequential lessons
CHECK (difficulty_level IN ('Beginner', 'Intermediate', 'Advanced'))
CHECK (content_type IN ('video', 'document', 'text', 'quiz', 'code'))

---

🔹 Junction Tables & Progress Tracking

-- M:N relationship: Students ↔ Courses
enrollments (
    enrollment_id PRIMARY KEY,
    student_id FK CASCADE,
    course_id FK CASCADE,
    enrollment_date TIMESTAMP,
    completion_percentage DECIMAL(5,2),
    status VARCHAR(20),
    UNIQUE(student_id, course_id)  -- Prevent duplicate enrollments
)

-- Granular progress tracking
student_progress (
    progress_id PRIMARY KEY,
    enrollment_id FK CASCADE,
    lesson_id FK CASCADE,
    is_completed BOOLEAN,
    completed_at TIMESTAMP,
    time_spent_minutes INT,
    last_accessed TIMESTAMP,
    UNIQUE(enrollment_id, lesson_id)  -- One progress per lesson
)

---

Database Constraints & Integrity

Constraint Type	Purpose	Examples
PRIMARY KEY	Unique identification	All tables use SERIAL auto-increment
FOREIGN KEY CASCADE	Referential integrity	Delete course → delete modules → delete lessons
UNIQUE	Business rules	(student_id, course_id), (enrollment_id, lesson_id)
CHECK	Data validation	Percentages 0-100, valid enums
NOT NULL	Required fields	Email, username, course title

Example Cascade Effect:

DELETE FROM courses WHERE course_id = 1;
  ↓ CASCADE
  ✓ Deletes course_modules
  ↓ CASCADE  
  ✓ Deletes module_lessons
  ↓ CASCADE
  ✓ Deletes lesson_content
  ↓ CASCADE
  ✓ Deletes enrollments
  ↓ CASCADE
  ✓ Deletes student_progress & quiz_attempts

---

Performance Optimization

Strategic Indexing

-- Foreign key indexes for JOIN performance
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);

-- Composite indexes for common multi-column queries
CREATE INDEX idx_enrollments_student_course 
  ON enrollments(student_id, course_id);
CREATE INDEX idx_progress_enrollment_lesson 
  ON student_progress(enrollment_id, lesson_id);

-- Partial indexes for filtered queries (smaller, faster)
CREATE INDEX idx_published_courses 
  ON courses(course_id) WHERE is_published = true;
CREATE INDEX idx_active_enrollments 
  ON enrollments(enrollment_id) WHERE status = 'active';

Query Performance Example

-- Complex analytics query with CTEs
WITH lesson_stats AS (
  SELECT 
    enrollment_id,
    COUNT(*) as total_lessons,
    COUNT(*) FILTER (WHERE is_completed) as completed_lessons,
    SUM(time_spent_minutes) as total_time
  FROM student_progress
  GROUP BY enrollment_id
)
SELECT 
  c.title,
  e.completion_percentage,
  ls.completed_lessons || '/' || ls.total_lessons as progress,
  ls.total_time as minutes_spent,
  AVG(qa.score) as avg_quiz_score
FROM enrollments e
JOIN courses c ON e.course_id = c.course_id
LEFT JOIN lesson_stats ls ON e.enrollment_id = ls.enrollment_id
LEFT JOIN quiz_attempts qa ON e.enrollment_id = qa.enrollment_id
WHERE e.student_id = $1
GROUP BY c.title, e.completion_percentage, ls.completed_lessons, 
         ls.total_lessons, ls.total_time;

---

🛠️ Tech Stack

Layer	Technologies
Database	14+
Backend
Frontend
AI Integration
Authentication
Tools	pgAdmin, DBeaver, Postman, Git

---

🚦 System Architecture

Transaction Flow: Course Enrollment

graph TD
    A[Student Clicks Enroll] --> B[API: Verify JWT]
    B --> C[BEGIN TRANSACTION]
    C --> D{Already<br/>Enrolled?}
    D -->|Yes| E[ROLLBACK]
    E --> F[409 Conflict]
    D -->|No| G[INSERT enrollment]
    G --> H[UPDATE course count]
    H --> I[COMMIT]
    I --> J[201 Success]
    
    style C fill:#FEF3C7,stroke:#D97706,stroke-width:3px
    style D fill:#E0E7FF,stroke:#4F46E5
    style E fill:#FEE2E2,stroke:#DC2626
    style I fill:#D1FAE5,stroke:#059669,stroke-width:3px

Loading

---

Database Connection Architecture

// PostgreSQL Connection Pool
const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                      // Maximum connections
  min: 5,                       // Minimum connections
  idleTimeoutMillis: 30000,     // Close idle after 30s
  connectionTimeoutMillis: 2000
});

// All queries use pooled connections
const result = await pool.query('SELECT ...', [params]);

---

🚀 Quick Start

Prerequisites

PostgreSQL 14+  |  Node.js 16+  |  npm  |  Gemini API Key

Installation

# 1. Clone repository
git clone https://github.com/MariamElkholeyy/KnowledgeStar-LMS.git
cd KnowledgeStar-LMS

# 2. Setup PostgreSQL database
createdb elearning_db
psql -d elearning_db -f Database/elearning_db_schema_normalized.sql
psql -d elearning_db -f Database/elearning_sample_data_normalized.sql

# Verify tables created
psql -d elearning_db -c "\dt"

# 3. Backend configuration
cd Backend
npm install
cp .env.example .env

Edit .env:

# Database
DATABASE_URL=postgresql://user:pass@localhost:5432/elearning_db

# Authentication
JWT_SECRET=your_secret_key_minimum_32_characters_long
JWT_EXPIRE=7d

# AI Integration
GEMINI_API_KEY=your_gemini_api_key_here

# Server
PORT=4000
FRONTEND_URL=http://localhost:3000

# Start backend
npm run dev  # → http://localhost:4000

# 4. Frontend setup (new terminal)
cd Frontend
npm install
npm start  # → http://localhost:3000

---

📡 API Endpoints

Authentication

POST /auth/register
POST /auth/login

Courses

GET    /courses                 # List all published courses
GET    /courses/:id             # Course details with hierarchy
POST   /courses/:id/enroll      # Enroll in course (transaction)

Progress Tracking

GET    /enrollments/my-courses  # Student's enrollments
POST   /progress/lesson/:id     # Update lesson progress
GET    /progress/course/:id     # Course progress details

Quiz System

GET    /quizzes/:id             # Get quiz with questions
POST   /quizzes/:id/submit      # Submit quiz (transaction)
GET    /quizzes/:id/attempts    # Quiz attempt history

AI Features (Instructor Only)

POST   /ai/generate-questions   # Generate quiz questions
POST   /ai/generate-lesson      # Generate lesson content
POST   /ai/generate-syllabus    # Generate course syllabus

---

💾 Database Query Examples

Complex Analytics Query

-- Instructor course analytics
SELECT 
  c.title,
  COUNT(DISTINCT e.student_id) as total_students,
  ROUND(AVG(e.completion_percentage), 2) as avg_completion,
  COUNT(DISTINCT qa.attempt_id) as total_quiz_attempts,
  ROUND(AVG(qa.score), 2) as avg_quiz_score,
  COUNT(DISTINCT sp.progress_id) FILTER (WHERE sp.is_completed) as lessons_completed
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
LEFT JOIN quizzes q ON c.course_id = q.course_id
LEFT JOIN quiz_attempts qa ON q.quiz_id = qa.quiz_id AND qa.submitted_at IS NOT NULL
LEFT JOIN student_progress sp ON e.enrollment_id = sp.enrollment_id
WHERE c.instructor_id = $1
GROUP BY c.course_id, c.title
ORDER BY total_students DESC;

---

🔥 Technical Achievements

Database Excellence

✅ 16+ Normalized Tables - Already normalized to 3NF/BCNF
✅ 20+ Foreign Key Relationships - Full CASCADE implementation
✅ Complex Queries - CTEs, window functions, aggregates
✅ Transaction Safety - ACID compliance for critical operations
✅ Strategic Indexing - Composite, partial, and foreign key indexes
✅ Zero Redundancy - Perfect normalization

Backend Architecture

✅ Connection Pooling - Scalable PostgreSQL connections
✅ Prepared Statements - SQL injection prevention
✅ JWT Authentication - Stateless & secure
✅ RESTful API - Clean endpoint design

AI Integration

✅ Gemini API - Question generation, lesson creation, syllabus building
✅ Automated Content - Reduce instructor workload
✅ Database Integration - AI-generated content stored properly

---

👤 Author

Mariam Wael Elkholey
📧 Mariam.wael.elkholey@gmail.com
📍 Alexandria, Egypt

---

---

👤 Author

Mariam Wael Elkholey
📧 Mariam.wael.elkholey@gmail.com
📍 Alexandria, Egypt

---

Contributors

Meet the team behind KnowledgeStar-LMS:

• Mariam Elkholey
• Ahmed Al-Qasaby
• Youssef Wael
• Marawan Moussa
• Mahmoud Harouni

---

⭐ Star this repo if you found it helpful!

Built with ❤️ and ☕
Demonstrating database design mastery + AI integration