Unit-Test Score Analytics and Parent Report System for a Grade 8-10 Coaching Centre (Python, MySQL)

Unit-Test Score Analytics and Parent Report System is a small web application for a Grade 8–10 coaching centre — one hall, three teachers, eighty students preparing for boards. The centre runs a unit test every fortnight, and marks currently live in a teacher's register and a tangle of WhatsApp messages. Nobody can answer the questions that matter: which chapter of Class 9 Mathematics is the whole batch weak in, which students are slipping over three tests, and what exactly should be told to a parent at the next meeting.

The system, built as an individual IGNOU BCSP-064 project, gives the centre director and teachers a Streamlit web application over a MySQL database. Teachers manage students and batches, define each unit test with its chapters, and enter marks on screen or by uploading the centre's usual marks sheet as a CSV. The analytics layer computes subject averages, batch percentile ranks, improvement trends across tests and a chapter-level weakness heatmap, and flags an at-risk list by plain rules (falling percentile over two consecutive tests).

Parents see a one-page PDF report generated with ReportLab: scores against batch averages, percentile, three weakest chapters per subject and a teacher's remark, handed over at the parent–teacher meeting.

The project deliberately demonstrates the fundamentals IGNOU examines: a normalised MySQL schema, a level-2 DFD, a documented security section (hashed passwords, parameterised queries, role checks) and test cases — in Python and MySQL, which BCSP-064 explicitly allows, instead of the prohibited C/C++ or VB + MS-Access stacks.

A Grade 8–10 coaching centre conducts frequent unit tests, but the marks sit in paper registers and scattered Excel files. As a result the director cannot see which chapter a batch is collectively weak in until board results arrive, teachers cannot track whether an individual student is improving or slipping across tests, and parents receive vague verbal feedback with nothing to refer back to. Hand-calculated averages appear late and occasionally disagree between teachers.

There is a need for a single, secure application in which marks are entered once, stored in a normalised database, and automatically turned into the three things the centre actually needs: chapter-level weakness views for teachers, trend and percentile views for every student, and a clean one-page PDF report for parents. The system must protect student data with authentication, role-based access and safe database access.

Abstract

Small coaching centres track fortnightly unit tests in paper registers, so chapter-level weakness, student trends and parent communication all stay manual and inconsistent. This project delivers a Python + MySQL web application, built with Streamlit, that manages students, batches, subjects, chapters and unit tests; accepts marks on screen or by CSV upload; computes averages, percentile ranks, trends and a chapter-wise weakness heatmap; and generates a one-page PDF report for parents. A security section covers authentication, role-based access and SQL-injection-safe database access.

Introduction

The centre (a fictional but typical example: an 80-student Grade 8–10 coaching centre in Bhopal teaching Mathematics, Science, English and Social Science) conducts two unit tests per subject per month. Each test covers two to four NCERT chapters. Teachers maintain marks in registers; the director tries to spot weak chapters by flipping pages; parents get verbal summaries. The information to make better teaching decisions already exists — it is just not structured.

Existing system

• Marks in paper registers and ad-hoc Excel files; one file per teacher, no common format.
• Class averages calculated by hand occasionally; chapter-level analysis never done.
• Parent communication is verbal at meetings, with no record of what was said.
• Limitations: no single source of truth, no trend over tests, no objective weakness identification, no printed artefact for parents, and registers cannot be searched or backed up.

Proposed system

• A central MySQL database of students, batches, subjects, chapters, tests and marks, in third normal form.
• Marks entry through validated forms or bulk CSV upload with a strict column check.
• Analytics: subject and test averages, batch percentile ranks, per-student trend across the last three tests, chapter-level weakness heatmap (share of students scoring below 40% per chapter), and a rule-based at-risk list.
• One-page parent PDF report per student with scores versus batch average, percentile, weakest chapters and remarks.
• Two roles only — Director (admin) and Teacher — with hashed passwords and role-checked pages.

Feasibility

• Technical: Python, Streamlit, pandas, MySQL and ReportLab are free and run on the centre's existing Windows desktop; the whole application installs with pip in under ten minutes.
• Economic: zero licence cost; hosting is unnecessary because the centre runs it on one office computer.
• Operational: the interface is a set of forms and tables. A teacher needs a 20-minute walkthrough; the director already uses WhatsApp and Excel, so a web table view is familiar.

In scope

• One coaching centre, Grades 8–10, four subjects, chapters mapped per grade and subject.
• Two roles: Director (full access) and Teacher (own subjects' marks entry and analytics).
• Fortnightly unit-test definition, marks entry (form and CSV), analytics dashboards, chapter heatmap, at-risk list, one-page parent PDF reports and Excel export.

Out of scope (v1)

• Fee collection, attendance, SMS/WhatsApp integration and parent logins (reports are printed for PTMs).
• Board (final) exam result analysis and any prediction of board scores.
• Multi-branch or multi-tenant deployment; online payment of any kind.

As an individual beginner project, the waterfall model with a working prototype loop is used: requirements and design are frozen first (as BCSP-064 expects a synopsis with DFDs before implementation), then implementation proceeds module by module with weekly demos to the guide.

Testing approach: unit tests (pytest) for the analytics functions against seeded data; black-box test cases in a table (test-case ID, input, expected, actual, status) covering login failure, marks above the maximum, malformed CSV rows and report generation; two rounds of user acceptance with the director acting as the user.

The application follows a simple three-tier architecture. The Streamlit front end renders forms and tables; a Python service layer holds all business rules (validation, percentile computation, heatmap counting, PDF assembly); MySQL stores the data. Every database call uses parameterised queries, and every page checks the logged-in role before rendering.

flowchart LR
  U["Director / Teacher (browser)"] --> A["Streamlit app (login gate)"]
  A --> S["Service layer: validation, analytics, report builder"]
  S --> Q["Parameterised SQL (mysql-connector)"]
  Q --> DB[("MySQL: users, students, tests, marks")]
  S --> P["ReportLab PDF generator"]
  P --> F["reports/<admission_no>_<test>.pdf"]

ER diagram

erDiagram
  USER ||--o| TEACHER : "may be a teacher"
  BATCH ||--o{ STUDENT : contains
  SUBJECT ||--o{ CHAPTER : "is divided into"
  BATCH ||--o{ UNIT_TEST : "has tests"
  SUBJECT ||--o{ UNIT_TEST : "test per subject"
  UNIT_TEST ||--o{ TEST_CHAPTER : covers
  CHAPTER ||--o{ TEST_CHAPTER : "is covered by"
  UNIT_TEST ||--o{ MARKS : "is scored in"
  STUDENT ||--o{ MARKS : receives

  USER {
    int user_id PK
    string username UK
    string password_hash
    string role
  }
  TEACHER {
    int teacher_id PK
    int user_id FK
    string name
  }
  BATCH {
    int batch_id PK
    int grade
    string board
    string timing
  }
  STUDENT {
    int student_id PK
    int batch_id FK
    string admission_no UK
    string name
    string guardian_phone
  }
  SUBJECT {
    int subject_id PK
    string name
  }
  CHAPTER {
    int chapter_id PK
    int subject_id FK
    int grade
    string title
  }
  UNIT_TEST {
    int test_id PK
    int batch_id FK
    int subject_id FK
    int teacher_id FK
    date test_date
    int max_marks
  }
  TEST_CHAPTER {
    int test_id FK
    int chapter_id FK
  }
  MARKS {
    int mark_id PK
    int test_id FK
    int student_id FK
    int marks_obtained
  }

DFD Level 0 (context)

flowchart LR
  D([Director]) -- student, batch, teacher setup --> SYS((Unit-Test Score<br/>Analytics System))
  T([Teacher]) -- test definition, marks --> SYS
  SYS -- dashboards, heatmap, at-risk list --> T
  SYS -- parent PDF reports --> D

DFD Level 1

flowchart TD
  D([Director]) --> P1[1.0 Manage users, batches, students]
  T([Teacher]) --> P2[2.0 Define tests and enter marks]
  P2 --> D1[(D1 Marks)]
  P1 --> D2[(D2 Students & Batches)]
  P2 --> D3[(D3 Tests & Chapters)]
  P3[3.0 Compute analytics] --> D1
  P3 --> D4[(D4 Analytics results)]
  P3 --> T
  P4[4.0 Generate parent report] --> D1
  P4 --> D4
  P4 -- PDF --> D

DFD Level 2 — decomposition of process 4.0 (Generate parent report)

flowchart TD
  IN([Report request: student, test]) --> P41[4.1 Fetch scores and batch statistics]
  P41 --> D1[(D1 Marks)]
  P41 --> P42[4.2 Compute percentile and weakest chapters]
  P42 --> D4[(D4 Analytics results)]
  P42 --> P43[4.3 Render one-page PDF via ReportLab]
  P43 --> OUT([PDF saved and logged])

Level 2 is included deliberately: the BCSP-064 synopsis guidelines expect at least a second-level DFD, and the report-generation process is the natural candidate because its steps are sequential and easy to explain in the viva.

• Parent login or WhatsApp report delivery with an OTP-verified guardian phone number.
• Attendance module feeding the same at-risk rules (low attendance plus falling marks).
• Fee dues reminder list for the director, separate from academic data.
• Question-paper blueprint mapping so each test's blueprint aligns with the board's design.
• Multi-branch support for centres running several locations with one database.
• Export-ready board-analytics pack comparing unit-test trends with pre-board results.