SCHOOL REPORT DATABASE

I taught computing at an international school in Brunei.

When I joined the school, the student reports were Word documents on the school server. Only one teacher at a time could work on a student's report.

I volunteered to design a database with an interface, so that many teachers could work on the reports at once, and the data would be stored efficiently.

I made the first version of our school report database with Microsoft Access and Visual Basic.

The first set of reports were very simple - High School reports with just grades and a comment.

The teachers loved being able to enter their reports any time from any computer in the school. Over the next few years, teachers asked for more and more features, and more and more kinds of reports. Eventually all reports across the school (Early Years, Primary School and High School) were on the system.

Teachers wanted to see a photo of the student while writing the report, so I added that feature.

Teachers also wanted to be able to view all previous reports for a student as they wrote the new report, so I added an archive database which could be accessed right from the report writing interface.

Eventually the structure of the application consisted of 4 databases.

• teacher interface
• administrator interface
• current report data
• archive report data

Teachers also wanted to be able to enter their reports from home, so I learned to make interactive web pages and made a whole new system using IIS and SQL Server.

As we stored more and more previous reports in the archive, we started to use data science to query the data for meaningful insights.

Which students are attaining very high or very low results across all subjects?

I used a SQL query to find any students with either high mean and low standard deviation, or low mean and low standard deviation across their results.

    SELECT student_ID, AVG(grade) AS avg, STDDEV_POP(grade) AS stddev
    FROM student_grades WHERE date >= "2025-12-01" GROUP BY student_ID
    HAVING (avg < 60 OR avg > 80) AND stddev < 10;
    

Here's some example output - the query found one student with consistently low results (Cyrus Long) and one student with consistently high results (Mathew Baird).

Which students are suddenly performing higher or lower than usual in one or more subjects?

I used a SQL query to find any students who got a result in a subject which was well above or below their previous average in that subject.

    SELECT previous.student_ID, name, previous.subject, `previous avg`, grade
    FROM (
        SELECT student_ID, name, subject, AVG(grade) AS `previous avg`
        FROM student_grades WHERE date < "2025-12-01" GROUP BY student_ID, subject
    ) AS previous INNER JOIN (
        SELECT student_ID, subject, grade
        FROM student_grades WHERE date >= "2025-12-01"
    ) AS current ON previous.student_ID = current.student_ID AND previous.subject = current.subject
    WHERE grade >= `previous avg` + 20 OR grade <= `previous avg` - 20;
    

Here's some example output - the query found that Derek Brock's result was much lower than his previous average in arts and maths, and Latoya James's result was much higher than her previous average in drama.

Which teachers are producing much higher or lower results than other teachers?

This was something the school leaders really wanted to know, and I wanted to help them find out. I decided to use a test called "ANOVA" or "Analysis of Variance", which has this formula.

$F=\frac{n\Sigma {(}^{{\bar{x}}_K}/(K-1)}{\Sigma {(}^{x_i}/(N-\mathrm{K})}$

I read some advice about using SQL for an ANOVA test, and the advice said it was far too complicated...challenge accepted!

I managed to work out a way to do an ANOVA test with SQL. This took some perseverance!

    SELECT
    MS_between.subject,
    `df between`,
    `df within`,
    `MS between`,
    `MS within`,
    `MS between` / `MS within` AS F,
    ELT(`df between`, 3.84, 3.00, 2.60, 2.37) AS `F critical (5%)`,
    IF (`MS between` / `MS within` > ELT(`df between`, 3.84, 3.00, 2.60, 2.37), "significant difference", "") AS `significant?`
    FROM (
        SELECT
        subject,
        `df between`,
        SUM(classes.product) / `df between` AS `MS between`
        FROM (
            SELECT
            teacher_averages.subject,
            `number of students`,
            `teacher average`,
            `subject grand mean`,
            `number of students` * POWER(`teacher average` - `subject grand mean`, 2) AS `product`,
            `df between`
            FROM (
                SELECT
                subject,
                COUNT(grade) AS `number of students`,
                AVG(grade) AS `teacher average`
                FROM student_grades GROUP BY subject, teacher_ID
            ) AS teacher_averages INNER JOIN (
                SELECT
                subject,
                COUNT(DISTINCT teacher_ID) - 1 AS `df between`,
                AVG(grade) AS `subject grand mean`
                FROM student_grades GROUP BY subject
            ) AS subject_grand_means ON teacher_averages.subject = subject_grand_means.subject
        ) AS classes GROUP BY subject
    ) AS MS_between INNER JOIN (
        SELECT
        subject,
        COUNT(grade) - COUNT(DISTINCT teacher_ID) AS `df within`,
        SUM(square_difference.`square difference`) / (COUNT(grade) - COUNT(DISTINCT teacher_ID)) AS `MS within`
        FROM (
            SELECT
            grades.subject,
            POWER(grades.grade - means.mean, 2) AS `square difference`,
            grade,
            teacher_ID
            FROM (
                SELECT
                subject,
                teacher_ID,
                grade
                FROM student_grades
            ) AS grades INNER JOIN (
                SELECT
                subject,
                AVG(grade) AS mean
                FROM student_grades GROUP BY subject
            ) AS means ON grades.subject = means.subject
        ) AS square_difference GROUP BY subject
    ) AS MS_within ON MS_between.subject = MS_within.subject;
    

Here's some example output - "df" is "degrees of freedom", "MS" is "mean square", and "F" is the measure of variance. If "F" is above "F critical" for that number of teachers, it means there is less than a 5% chance that the variance is random. The query found that in the subject "technology", the teachers' results were significantly different - the technology teachers' results could then be compared in detail.

I helped the school maintain the database for several years after I left, then eventually the school decided to migrate to a commercial solution.

I am very proud of the complete student report application we developed together!