School Database Project in MySQL

The project

A school database is the classic DBMS mini project. It brings together everything: entities, primary and foreign keys, one-to-many and many-to-many relationships, and real queries like report cards and fee summaries. We will design it, create it, and query it end to end.

Designing the tables

• classes — id, class_name.
• students — id, name, class_id (FK → classes).
• subjects — id, subject_name.
• marks — id, student_id (FK), subject_id (FK), marks.
• fees — id, student_id (FK), amount, status.

One class has many students (one-to-many). Marks link students and subjects (many-to-many via the marks table).

Creating the schema

CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(20)
);
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);
CREATE TABLE subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(30)
);
CREATE TABLE marks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT, subject_id INT, marks INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Sample data

INSERT INTO classes (class_name) VALUES ('Class 10');
INSERT INTO students (name, class_id) VALUES ('Aman', 1), ('Diya', 1);
INSERT INTO subjects (subject_name) VALUES ('Math'), ('Science');
INSERT INTO marks (student_id, subject_id, marks) VALUES
    (1, 1, 88), (1, 2, 76), (2, 1, 95), (2, 2, 90);

Now we have a small but complete dataset to query.

Useful queries

-- students in a class
SELECT s.name, c.class_name
FROM students s JOIN classes c ON s.class_id = c.id;

-- number of students per class
SELECT c.class_name, COUNT(s.id) AS total
FROM classes c LEFT JOIN students s ON s.class_id = c.id
GROUP BY c.id;

-- average marks per subject
SELECT sub.subject_name, AVG(m.marks) AS avg_marks
FROM marks m JOIN subjects sub ON m.subject_id = sub.id
GROUP BY sub.id;

A report card query

SELECT s.name,
    SUM(m.marks) AS total,
    ROUND(AVG(m.marks), 2) AS average,
    CASE WHEN AVG(m.marks) >= 90 THEN 'A'
         WHEN AVG(m.marks) >= 75 THEN 'B'
         ELSE 'C' END AS grade
FROM students s
JOIN marks m ON m.student_id = s.id
GROUP BY s.id, s.name;

Common mistakes

• Storing all subjects in the students table. Use a separate marks table.
• No foreign keys. Link tables so data stays consistent.
• Repeating class details per student. Keep them in the classes table.
• Forgetting LEFT JOIN for empty classes. Use it to count classes with no students.

Summary

• A school database uses students, classes, subjects, marks and fees.
• Link them with foreign keys (student → class, marks → student and subject).
• Store marks one row per student per subject.
• Query with JOIN, GROUP BY and CASE for real reports.
• It demonstrates all the core DBMS concepts in one project.

Next, build the report card query in depth, or try the library database project.