Online Course Enrollment System

Online Course Enrollment System

(Many-to-Many Relationship Project)

STEP 1: Create Database

				
					CREATE DATABASE course_db;

-- Verify database
SHOW DATABASES;

STEP 2: Use Database

				
					USE course_db;

-- Check current database
SELECT DATABASE();

STEP 3: Create Students Table

Stores student information.

Fields included:

student_id
student_name
email
phone
registration_date

				
					CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(15),
    registration_date DATE
);

-- Check structure
DESC students;

STEP 4: Insert Students

				
					INSERT INTO students (student_name, email, phone, registration_date) VALUES
('Aman', 'aman@gmail.com', '9876543210', '2026-01-10'),
('Priya', 'priya@gmail.com', '9876501234', '2026-01-12'),
('Rahul', 'rahul@gmail.com', '9876512345', '2026-01-15');

-- View students
SELECT * FROM students;

STEP 5: Create Courses Table

Stores course information.

Fields included:

course_id
course_name
duration_days
course_fee
minimum_attendance (for certificate)

				
					CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    duration_days INT,
    course_fee DECIMAL(10,2),
    minimum_attendance INT
);

-- Check structure
DESC courses;

STEP 6: Insert Courses

				
					INSERT INTO courses (course_name, duration_days, course_fee, minimum_attendance) VALUES
('Web Development', 60, 15000, 75),
('Data Analytics', 45, 12000, 80),
('Graphic Design', 30, 10000, 70);

-- View courses
SELECT * FROM courses;

STEP 7: Create Enrollments Table (Bridge Table)

This table connects students and courses.

Fields included:

enrollment_id
student_id (Foreign Key)
course_id (Foreign Key)
enrollment_date
attendance_percentage
certificate_status (calculated)

Certificate rule:
If attendance ≥ minimum_attendance → Eligible
Otherwise → Not Eligible

				
					CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    attendance_percentage INT,

    certificate_status VARCHAR(20) GENERATED ALWAYS AS (
        CASE
            WHEN attendance_percentage >= 75 THEN 'Eligible'
            ELSE 'Not Eligible'
        END
    ) STORED,

    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- Check structure
DESC enrollments;

STEP 8: Course Registration (Enroll Students)

Students enroll in courses.

				
					INSERT INTO enrollments (student_id, course_id, enrollment_date, attendance_percentage) VALUES
(1, 1, '2026-02-01', 80),
(1, 2, '2026-02-05', 70),
(2, 1, '2026-02-03', 90),
(3, 3, '2026-02-06', 65);

-- View enrollments
SELECT * FROM enrollments;

STEP 9: View Course Registration Details

Show student name and course name together.

				
					SELECT students.student_name,
       courses.course_name,
       enrollments.attendance_percentage,
       enrollments.certificate_status
FROM enrollments
JOIN students ON enrollments.student_id = students.student_id
JOIN courses ON enrollments.course_id = courses.course_id;

-- Registration report displayed

STEP 10: Course-wise Student Count

Count how many students enrolled in each course.

				
					SELECT courses.course_name,
       COUNT(enrollments.student_id) AS total_students
FROM enrollments
JOIN courses ON enrollments.course_id = courses.course_id
GROUP BY courses.course_name;

-- Course-wise student count displayed

STEP 11: Check Certificate Eligible Students

Show only students eligible for certificate.

				
					SELECT students.student_name,
       courses.course_name,
       enrollments.attendance_percentage
FROM enrollments
JOIN students ON enrollments.student_id = students.student_id
JOIN courses ON enrollments.course_id = courses.course_id
WHERE enrollments.certificate_status = 'Eligible';

-- Eligible students displayed

🎯 What Students Learn From This Project

Many-to-Many Relationship
Bridge table concept
Foreign keys
INSERT with relationships
GROUP BY
COUNT
JOIN (3 tables)
Generated columns
Basic business rule implementation
Certificate eligibility logic