Student Management Database Example with Enrollment and Payment (video in side)

byHann -
0

How Make Student Management Database with MySQL Easy

Hello friend. Today we create database for student management using MySQL. All sql statements specificially for MySQL database, it may little difference if you are using SQL Server Database (I will have another post for SQL Server). Now let me show you step by step from the beginning.



Step 1: Create Database

Firstly, We create a database for holding our tables called StudentManagementDB


    CREATE DATABASE StudentManagementDB;

Step 2: Create Student Table

Now, we make table for keep student information. Like name, gender, birthday. We call tbStudents. 


    CREATE TABLE tbStudents(
        student_id INT PRIMARY KEY,
        first_name VARCHAR(100),
        last_name VARCHAR(100),
        gender CHAR(1),
        birth_date DATE,
        province VARCHAR(100)
    );
Create Students table And Insert Data

Now insert some data

    INSERT INTO tbStudents (student_id, first_name,last_name,gender,birth_date,province) 
    VALUES(1,'sok','sao','M','2000-1-23','Kampot');

    INSERT INTO tbStudents 
    VALUES(2,'sokheng','Sou','M','2002-4-13','Takeo');

    INSERT INTO tbStudents VALUES
    (3, 'Vanna', 'Sokha', 'M', '2003-01-10', 'Takeo'),
    (4, 'Srey', 'Pich', 'F', '2002-11-05', 'Kampong Cham'),
    (5, 'Rithy', 'Chantha', 'M', '2000-06-30', 'Battambang'),
    (6, 'Sokun', 'Sophea', 'F', '2001-09-12', 'Siem Reap'),
    (7, 'Dara', 'Vichea', 'M', '2002-04-18', 'Prey Veng'),
    (8, 'Sreynich', 'Ly', 'F', '2003-02-25', 'Svay Rieng'),
    (9, 'Borey', 'Kim', 'M', '2001-12-01', 'Kampot'),
    (10, 'Sreyneang', 'Huot', 'F', '2002-08-09', 'Kep'),
    (11, 'Piseth', 'Meng', 'M', '2000-05-20', 'Kampong Speu'),
    (12, 'Sokunthea', 'Chea', 'F', '2001-10-14', 'Kampong Thom'),
    (13, 'Narin', 'Phan', 'M', '2002-07-07', 'Pursat'),
    (14, 'Sreymao', 'Tan', 'F', '2003-03-11', 'Koh Kong'),
    (15, 'Visal', 'Heng', 'M', '2001-01-29', 'Banteay Meanchey'),
    (16, 'Sopheap', 'Oun', 'F', '2002-06-16', 'Oddar Meanchey'),
    (17, 'Kosal', 'Lim', 'M', '2000-09-03', 'Pailin'),
    (18, 'Sreyka', 'Sin', 'F', '2003-05-22', 'Ratanakiri'),
    (19, 'Chenda', 'Long', 'M', '2002-12-19', 'Mondulkiri'),
    (20, 'Sreylen', 'Keo', 'F', '2001-04-27', 'Kratie'),
    (21, 'Vichea', 'Touch', 'M', '2000-11-13', 'Stung Treng'),
    (22, 'Sokha', 'Yim', 'F', '2002-02-08', 'Preah Vihear'),
    (23, 'Ratha', 'Nhem', 'M', '2001-06-17', 'Kampong Chhnang'),
    (24, 'Sreypov', 'Chun', 'F', '2003-09-01', 'Tbong Khmum'),
    (25, 'Bunna', 'Sam', 'M', '2002-10-10', 'Takeo'),
    (26, 'Sreychan', 'Chhim', 'F', '2001-03-23', 'Kandal'),
    (27, 'Dalin', 'Sok', 'F', '2002-07-30', 'Phnom Penh'),
    (28, 'Sothea', 'Mean', 'M', '2000-08-05', 'Battambang'),
    (29, 'Sreyroth', 'Prak', 'F', '2003-01-14', 'Siem Reap'),
    (30, 'Phalla', 'Nuon', 'M', '2001-12-28', 'Kampot'),
    (31, 'Sok', 'Dara', 'M', '2002-03-15', 'Phnom Penh'),
    (32, 'Chan', 'Sreymom', 'F', '2001-07-21', 'Kandal');

Boost your HTML skills: 🚀 HTML Tutorials in Khmer (with Video)


Step 3: Create Academic Year Table

Now we create academic year like 2025 to 2026, 2026 to 2027.


CREATE TABLE tbAcademic_years(
	academic_id TINYINT AUTO_INCREMENT PRIMARY KEY,
	start_year VARCHAR(4), -- 2026
	end_year VARCHAR(4), -- 2027
);
Create Academic Year table And Insert Data

Let insert some data:

    INSERT INTO tbAcademic_years (start_year,end_year)
    VALUES (2025, 2026),(2026, 2027),(2027, 2028),(2028, 2029);

Step 4: Create Department Table

What departmet student study? Computer Science? Math? We make tbDepartments table.


    CREATE TABLE tbDepartments(
        department_id SMALLINT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );
Create Department table And Insert Data

Let insert sample data:


    INSERT INTO tbDepartments (name)
    VALUES ('Computer Science'),('Khmer Literature'),
	   ('Enligh Language'),('Mathematics'),('Physics'),('Chemistry');

Step 5: Create Enrollment Table

Now we create enrollment table which record students, department and academic year with relationship to student, department and academic year table.


    CREATE TABLE tbEnrollments(
        enroll_id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL, -- FK
        academic_id TINYINT NOT NULL, -- FK
        department_id SMALLINT NOT NULL, -- FK
        study_year TINYINT NOT NULL,
        is_active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES tbStudents (student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (academic_id) REFERENCES tbAcademic_years (academic_id) ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (department_id) REFERENCES tbDepartments (department_id) ON DELETE CASCADE ON UPDATE CASCADE
    );
Create Enrollment table And Insert Data

Now let insert sample data:


    INSERT INTO tbEnrollments (student_id,academic_id,department_id,study_year)
    VALUES (1,1,1,1),(2,1,1,2),(3,1,2,1),(4,2,1,1),(5,1,1,2),(6,1,1,1),(7,1,1,2),(8,1,2,2),(9,1,2,2);

    INSERT INTO tbEnrollments (student_id,academic_id,department_id,study_year)
    VALUES (1,1,3,1),(2,1,2,2),(3,1,1,1);

Step 6: Create Payment Table

After student enrolled in any department, then they need to pay for it. It can be pay for semester or full year of tuition fee, certificate or other. 


    CREATE TABLE tbPayments(
        payment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
        enroll_id INT NOT NULL, -- FK
        student_id INT NOT NULL, -- FK
        semester ENUM('first', 'second', 'full'),
        amount DECIMAL(10,2) CHECK (amount >0) NOT NULL,
        payment_methods ENUM('cash', 'bank', 'KHQR','Other') NOT NULL,
        payment_types ENUM('tuition','exam','transcript','other') NOT NULL,
        status ENUM('paid','pending','refund') DEFAULT 'pending',
        remark VARCHAR(255),
        is_deletd TINYINT(1) DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES tbStudents (student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
        FOREIGN KEY (enroll_id) REFERENCES tbEnrollments (enroll_id)
        ON DELETE CASCADE ON UPDATE CASCADE
    );
Create Payment table And Insert Data

Now insert some data:


INSERT INTO tbPayments
(enroll_id, student_id,semester, amount, payment_methods, payment_types, status, remark)VALUES
(1, 1, 'first', 250.00, 'cash', 'tuition', 'paid', 'First semester tuition fee'),
(2, 2, null, 10, 'cash', 'transcript', 'paid',null );

Let insert more sample data:


INSERT INTO tbPayments (enroll_id, student_id, semester, amount, payment_methods, payment_types, status, remark) VALUES
(1, 1, 'first', 250.00, 'bank', 'tuition', 'paid', 'First semester tuition fee'),
(2, 2, 'first', 250.00, 'KHQR', 'tuition', 'paid', 'First semester tuition fee'),
(3, 3, 'first', 250.00, 'cash', 'tuition', 'pending', 'Awaiting payment'),
(4, 4, 'first', 250.00, 'bank', 'tuition', 'paid', 'Full payment'),
(5, 5, 'first', 250.00, 'KHQR', 'tuition', 'paid', 'Full payment'),
(1, 1, NULL, 15.00, 'cash', 'transcript', 'paid', 'Official transcript request'),
(2, 2, NULL, 20.00, 'cash', 'other', 'paid', 'ID card replacement fee'),
(6, 6, 'first', 250.00, 'bank', 'tuition', 'paid', 'Full payment'),
(7, 7, 'first', 100.00, 'cash', 'tuition', 'pending', 'Partial payment'),
(8, 8, NULL, 5.00, 'cash', 'exam', 'paid', 'Resit exam fee'),
(3, 3, 'first', 500.00, 'bank', 'tuition', 'paid', 'First semester tuition fee'),
(4, 4, 'first', 250.00, 'KHQR', 'tuition', 'pending', 'First installment'),
(5, 5, 'first', 500.00, 'bank', 'tuition', 'paid', 'Full payment for first semester'),
(6, 6, 'first', 500.00, 'cash', 'tuition', 'paid', 'Full payment'),
(7, 7, 'second', 500.00, 'KHQR', 'tuition', 'paid', 'Second semester tuition fee'),
(8, 8, NULL, 25.00, 'cash', 'exam', 'paid', 'Resit exam fee'),
(9, 9, 'first', 500.00, 'bank', 'tuition', 'paid', 'Full payment'),
(10, 1, 'second', 500.00, 'KHQR', 'tuition', 'pending', 'Awaiting payment for second semester'),
(11, 2, NULL, 15.00, 'cash', 'other', 'paid', 'Library fine'),
(12, 3, NULL, 10.00, 'cash', 'transcript', 'paid', 'Transcript request');

Some Questions for SQL Query for Testing

១. បង្ហាញឈ្មោះសិស្ស និងឆ្នាំសិក្សា (study_year) របស់ពួកគេតាមរយៈតារាង enrollments
២. បង្ហាញឈ្មោះសិស្សដែលរៀនក្នុងដេប៉ាតឺម៉ងលេខ ២ (department_id = 2)
៣. បង្ហាញឈ្មោះសិស្ស និងព័ត៌មានបង់ប្រាក់សម្រាប់សិស្សដែលមានស្ថានភាពបង់ប្រាក់ជា 'pending'
៤. បង្ហាញឈ្មោះសិស្ស និងឆ្នាំសិក្សា (Study Year) សម្រាប់សិស្សដែលរៀនឆ្នាំទី ១ ក្នុងកូដឆ្នាំសិក្សាទី ១ (academic_id = 1)
៥. បង្ហាញឈ្មោះសិស្ស និងព័ត៌មានបង់ប្រាក់សម្រាប់អ្នកដែលបង់តាមរយៈ 'cash'
៦. បង្ហាញឈ្មោះសិស្ស និងប្រភេទនៃការបង់ប្រាក់ (pay_types) សម្រាប់សិស្សទាំងអស់
៧. បង្ហាញឈ្មោះសិស្សដែលបានចុះឈ្មោះក្នុងឆ្នាំសិក្សាណាមួយ (ប្រើ enrollments)
៨. បង្ហាញព័ត៌មានសិស្សដែលមានឈ្មោះខ្លួន (first_name) ចាប់ផ្តើមដោយពាក្យ "Srey" ហើយរស់នៅក្នុងខេត្ត "Takeo" ឬ "Kandal"
៩. បង្ហាញបញ្ជីសិស្សដែលរស់នៅក្នុងខេត្ត "Takeo", "Kandal" ឬ "Phnom Penh"
១០. បង្ហាញព័ត៌មានបង់ប្រាក់ទាំងអស់ដែល មិនមាន ស្ថានភាពជា 'paid' ឬ 'pending' (មានន័យថាបង្ហាញតែ 'refund')

Tags

You may like these posts

Previous Post Next Post