📘 Online Learning Management System (LMS) – MySQL Project
This project is a fully structured Learning Management System (LMS) database built using MySQL. It simulates how modern e-learning platforms manage courses, students, instructors, module progress, quizzes, scoring, and certification.
The project includes normalized database design, inserted sample data, analytical SQL queries, stored procedures, and custom functions.
🚀 Features
- User Management
Students table
Instructors table
Sign-up dates, departments, emails
- Course & Module Structure
Each course contains ordered modules
Module prerequisites
Module progress tracking per student
- Enrollments & Progress
Students can enroll in multiple courses
Tracks per-module progress (Completed / Ongoing / Dropped)
Automatically computes course completion patterns
- Quiz Management
Quizzes mapped to modules
Questions & correct answers
Tracks quiz attempts and scores
- Certificates
Auto-generate certificates only when: ✔ All modules completed ✔ All quiz scores ≥ 5
Certificates include unique certificate IDs and issue dates
- Analytics & Reports (Advanced SQL Queries)
Module completion rates
Average quiz scores (course-wise, module-wise, quiz-wise)
Students who didn’t finish courses
Complete student → course → module → quiz join view
- Stored Procedure: issueCertificates()
Automatically issues certificates for eligible students.
- Function: getStudentProgress()
Returns overall course progress (%) for any student.
🏗 Database Schema Overview Tables Included
students
instructors
courses
modules
enrollments
moduleprogress
quiz
questions
quizattempts
certificates
All tables include primary keys, foreign keys, and necessary relationships to maintain database integrity.
📂 Project Structure /lms_database.sql → Complete MySQL schema, sample data, queries, stored procedure, and function README.md → Project documentation
📝 How to Use
Open MySQL Workbench / XAMPP / phpMyAdmin
Create the database:
CREATE DATABASE lms_db; USE lms_db;
Run the entire SQL script (lms_database.sql).
Test the built-in queries, procedure, and function.
📊 Included Reports (Examples)
Module ordering per course
Completion rate per module
Average quiz scores
Students who dropped modules
Full joined LMS overview
🏅 Certificate Generation Example
Run:
CALL issueCertificates(); SELECT * FROM certificates;
🎯 Purpose of This Project
This project is ideal for:
Database portfolio building
College or university database assignments
Demonstration of MySQL joins, constraints, procedures, and functions
Understanding real LMS data architecture
👩💻 Technologies Used
MySQL
SQL (Joins, Subqueries, Grouping, Procedures, and Functions)