LMS Backend System

Overview

This project is a backend database system for a Learning Management System (LMS). It manages users, courses, enrollments, lessons, and categories, along with analytics and performance optimization.

---

Tech Stack

• PostgreSQL (Database)
• Prisma ORM
• Node.js

---

Database Schema

The system consists of the following tables:

• User – stores user information (students/admins)
• Course – stores course details
• Category – groups courses into categories
• Enrollment – tracks which users are enrolled in which courses
• Lesson – contains course lessons and video content

---

Relationships

• A user can enroll in multiple courses
• A course can have multiple lessons
• Each course belongs to one category
• Enrollments act as a bridge between users and courses

---

Features Implemented

Database Design

• Designed ER-based relational schema
• Implemented using Prisma models

Seed Data

• Added dummy data for testing:

  • Users (e.g., Pardeep Yadav)
  • Courses
  • Lessons
  • Enrollments

Stored Procedures

• get_enrollment_count(courseId) → Returns total enrollments in a course
• get_course_progress(userId, courseId) → Returns user progress in a course

Indexing (Performance Optimization)

• Indexed frequently queried fields:

  • email (User table)
  • courseId (Enrollment table)
  • userId (Enrollment table)

---

▶ How to Run the Project

1. Clone the repository

2. Install dependencies:

   npm install

3. Setup environment variables: Create a .env file and add:

   DATABASE_URL="your_postgresql_connection_string"

4. Push database schema:

   npx prisma db push

5. Run seed script:

   npx prisma db seed

6. Open Prisma Studio (optional):

   npx prisma studio

---

Testing

• Use Prisma Studio to verify data

• Stored procedures can be tested using SQL queries:

  SELECT get_enrollment_count(1);
  SELECT get_course_progress(1, 1);

---

Future Improvements

• Build REST APIs (Courses, Enrollment, Progress)
• Add authentication (JWT)
• Add pagination and search
• Role-based access control

---

👨Author

Pardeep Yadav

---