EduPortal – Student Course Management System

🎓 Full-Stack DBMS Mini-Project for 2nd Year Engineering Students

EduPortal is a fully functional, premium Student Course Management System designed specifically to showcase relational database concepts, entity constraints, complex query structures, and interactive data analysis.

The system features three distinct user roles (Admin, Teacher, Student) and integrates professional responsive interfaces with dynamic dashboards, statistical charting, and full dark-theme capabilities.

---

🚀 Key Architectural Features

1. Relational Integrity: Integrates primary keys, composite keys, and foreign keys with cascading trigger responses (ON DELETE CASCADE, ON DELETE SET NULL).
2. Dual-Mode Database Connector (Examiner-Proof): Standard MySQL configuration (mysql2) backed by a seamless automatic SQLite fallback file database (eduportal.sqlite) if MySQL is not running on the local host. This guarantees that your project is immediately runnable on any computer without manual database setups during vivas, while retaining 100% equivalent schema, syntax, and functionality!
3. Express RESTful APIs: Organized modular controllers parsing logins, student CRUD, teacher CRUD, and enrollment rosters.
4. Vibrant Responsive UI: Modern glassmorphic dashboards, linear card gradients, and collapsible sidebars built on React + Tailwind CSS + Lucide Icons.
5. Interactive Charts: SVG-based Recharts visualization modules graphing proportions and enrollment splits dynamically.

---

📂 Project Folder Structure

eduportal/
├── backend/
│   ├── config/
│   │   └── db.js                 # Dual-Mode database connector (MySQL / SQLite Fallback)
│   ├── routes/
│   │   ├── auth.js               # Multi-role credentials checks
│   │   ├── admin.js              # Student/Teacher/Subject CRUD + analytics
│   │   ├── student.js            # Enrolled course browsing, drop, edit profile
│   │   └── teacher.js            # Subject overview, student tracking, edit profile
│   ├── database_queries.sql      # Advanced SQL script containing schemas & joins
│   ├── seed.js                   # Seed script to auto-generate sample data
│   ├── server.js                 # Express app bootstrap
│   └── package.json
├── frontend/
│   ├── public/
│   ├── src/
│   │   ├── components/           # Sidebar, Navbar, ThemeToggle, Modal
│   │   ├── context/              # AuthContext for session management
│   │   ├── pages/
│   │   │   ├── Login.jsx         # Elegant tabbed login screen with pre-filled inputs
│   │   │   ├── admin/            # Dashboards & list management pages
│   │   │   ├── student/          # Enrollment registries & student profiles
│   │   │   └── teacher/          # Rosters & teacher profiles
│   │   ├── App.jsx               # Navigation router and role guards
│   │   ├── main.jsx              # Application bootstrap
│   │   └── index.css             # Tailwind base & custom gradients
│   ├── package.json
│   ├── tailwind.config.js
│   └── vite.config.js
├── package.json                  # Root package enabling single-point dev commands
└── README.md                     # Setup instructions & Viva preparation guidelines

---

🛠️ Installation & Setup Instructions

We have pre-configured a root package.json that automates installations and starts both the React frontend and Express backend concurrently!

Prerequisites

• Node.js installed (v16.0 or higher recommended).
• Optional: MySQL Server running locally. If not present, the system will automatically create a local eduportal.sqlite file inside the backend directory, so it runs 100% successfully out-of-the-box!

Step-by-Step Launch

1. Open your terminal in the eduportal root directory.

2. Run the automated configuration command to install all dependencies and seed the database with sample records:

   npm run setup

   (This will run npm install at the root, in the backend/, in the frontend/, and then invoke the seeder script automatically.)

3. Launch both the frontend client and API server concurrently with one command:

   npm run dev

4. Open your browser and navigate to:

   • Client: http://localhost:3000
   • Backend Status Health Check: http://localhost:5000/api/status (Shows active DB Mode: MySQL vs SQLite Fallback).

---

🔑 Default Credentials for Demonstration

To make the live examination completely seamless, the login screen includes Quick-fill Buttons at the bottom. Alternatively, you can enter the following:

• Admin Account:
  • Username: admin
  • Password: admin
• Teacher Account:
  • Username: ramesh
  • Password: password (Lecturer of CS courses)
• Student Account:
  • Username: aarav
  • Password: password (Student of CS courses)

---

📊 Relational Database Schema & Entity Relationships

The project's tables are highly normalized and mapped in third normal form (3NF):

• admin: Tracks admins (id, name, username, password).
• teachers: Tracks lecturers (id, name, username, password, specialization).
• students: Tracks students (id, name, usn [Unique], username, password, department, semester).
• subjects: Tracks courses (id, name, code [Unique], teacher_id [FK referencing teachers(id) on delete set null]).
• enrollments: Bridge table mapping many-to-many relationships between students and subjects (student_id [FK], subject_id [FK], enrolled_at). Composite Primary Key on (student_id, subject_id).

erDiagram
    ADMIN {
        INT id PK
        VARCHAR name
        VARCHAR username UK
        VARCHAR password
    }
    STUDENTS {
        INT id PK
        VARCHAR name
        VARCHAR usn UK
        VARCHAR username UK
        VARCHAR password
        VARCHAR department
        INT semester
    }
    TEACHERS {
        INT id PK
        VARCHAR name
        VARCHAR username UK
        VARCHAR password
        VARCHAR specialization
    }
    SUBJECTS {
        INT id PK
        VARCHAR name
        VARCHAR code UK
        INT teacher_id FK
    }
    ENROLLMENTS {
        INT student_id PK, FK
        INT subject_id PK, FK
        TIMESTAMP enrolled_at
    }

    TEACHERS ||--o{ SUBJECTS : "teaches"
    STUDENTS ||--o{ ENROLLMENTS : "enrolls in"
    SUBJECTS ||--o{ ENROLLMENTS : "has students"

Loading

---

📑 Core SQL Relational Queries Mapped in the Project

The backend queries utilize advanced relational constructs which are excellent for your project report:

1. Enrollment Monitoring: Students Enrolled in at least One Course (INNER JOIN + GROUP BY)

SELECT DISTINCT s.name, s.usn, s.department, s.semester,
       GROUP_CONCAT(sub.name SEPARATOR ', ') as courses_enrolled
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN subjects sub ON e.subject_id = sub.id
GROUP BY s.id
ORDER BY s.name ASC;

2. Inactive Student Checklist: Students Not Enrolled Anywhere (Subquery with NOT IN)

SELECT name, usn, department, semester 
FROM students 
WHERE id NOT IN (SELECT DISTINCT student_id FROM enrollments)
ORDER BY name ASC;

3. Subject Distribution: Total Registrations per Course (LEFT JOIN + GROUP BY)

SELECT s.name AS course_name, s.code AS course_code, COUNT(e.student_id) AS student_count
FROM subjects s
LEFT JOIN enrollments e ON s.id = e.subject_id
GROUP BY s.id, s.name, s.code
ORDER BY student_count DESC;

---

🎓 Viva Voce Preparation Cheat Sheet

Be prepared to answer these 5 classic questions from your DBMS external examiner:

1. Q: What is the purpose of the enrollments table?

   • A: The enrollments table acts as a bridge table (or junction table). It resolves the Many-to-Many relationship between students and subjects (as one student can register in multiple subjects, and one subject can have multiple students enrolled) into two separate One-to-Many relationships.

2. Q: What integrity constraints did you use in the enrollments table?

   • A: We used two major foreign key constraints: student_id pointing to students(id) and subject_id pointing to subjects(id). Both have ON DELETE CASCADE triggers, meaning that if a student or course is deleted, their corresponding enrollment links are automatically purged, preventing orphaned rows and preserving referential integrity.

3. Q: What happens if a teacher is deleted? Does their subject get deleted?

   • A: No, because the subjects.teacher_id foreign key is declared with ON DELETE SET NULL. If a teacher record is removed, the courses they taught will remain in the database but will simply display as "Unassigned (None)" in the frontend, allowing another teacher to be assigned.

4. Q: Why did you use a LEFT JOIN in the course registrations distribution query instead of an INNER JOIN?

   • A: We used a LEFT JOIN to ensure that all subjects registered in the database are returned in the result set, even if they have zero students enrolled. An INNER JOIN would filter out subjects with no enrollments, which would make our dashboard charts incomplete and inaccurate.

5. Q: How does your system prevent a student from enrolling in the same course twice?

   • A: We implement a dual-layer check:
     • Database Layer: The enrollments table has a Composite Primary Key on (student_id, subject_id). The database engine will reject any duplicate insertions and throw a unique constraint error.
     • Application Layer: In routes/student.js, we run a SELECT check prior to inserting to assert if the student has already enrolled, returning a user-friendly error response if true.