School Bus Route and Pass System

1Introduction: the problem it solves

Every June the school office answers the same question a hundred times: does the bus stop near my house, at what time, and what does it cost? The answer lives on a printed sheet that goes out of date the moment a stop moves.

Two tables answer it in one query, and the same tables can issue the pass and tell the transport in-charge how full each bus is.

Why it is worth computerising

Every June the school office answers the same three questions a hundred times: does the bus come near my house, at what time, and what does it cost. The answers live on a printed sheet that is out of date as soon as a stop is moved, and each enquiry costs somebody five minutes.

Computerising it does more than answer enquiries quickly. Because a pass records the route it was issued for, the school can count how many students board each bus before term starts. An overloaded route currently announces itself in September, when there is nothing to be done; in a table it is visible in June, when there is.

Objectives

1. To hold every route, stop, pickup time and fee in one table
2. To answer a parent's route enquiry with a single query
3. To find the route and fee from the stop, so neither is typed twice
4. To refuse a stop the buses do not serve instead of issuing a wrong pass
5. To report how many students travel on each route

2How the job is done today

Before writing anything it is worth asking how the work is handled at present, and why each of those answers falls short. These were examined:

3Functionalities

• A stops table holding the route, stop name, pickup time and monthly fee
• A route enquiry listing every stop in pickup order
• Pass issue that finds the route and fee from the stop, so neither is typed twice
• Refuses a stop the buses do not serve, instead of issuing a wrong pass
• Quarterly fee worked out from the monthly one
• A per-route headcount, so an overloaded bus shows up before September

The functions that provide them

4Technical details

5How the data is stored

The fee lives in `stops`, not in `passes`. That is a deliberate choice: it means a fee revision is one UPDATE, and a pass can never disagree with the stop it was issued for. The cost is that historical passes show the new fee — worth discussing in your report.

6How it works, step by step

7Source code

# ---------------------------------------------------------------------------
# transport.py
#
# School bus routes and pass issue, using Python with MySQL.
#
# Two tables: the stops each route serves, and the passes issued against them.
# The fee lives with the stop, not on the pass, so a fee revision is a single
# UPDATE and a pass can never disagree with the stop it was issued for.
# ---------------------------------------------------------------------------

import mysql.connector
from datetime import date

db = mysql.connector.connect(host="localhost", user="root",
                             passwd="bus", database="lambdalab_transport")
cur = db.cursor()


def stops_on_route(route_no):
    """Every stop on a route, in the order the bus reaches them."""
    # ORDER BY pickup_time, not by stop name — a parent wants to know what time
    # the bus reaches their stop, and the order along the road is the answer.
    cur.execute("SELECT stop_name, pickup_time, monthly_fee FROM stops"
                " WHERE route_no = %s ORDER BY pickup_time", (route_no,))

    print("LAMBDALAB PUBLIC SCHOOL — ROUTE {}".format(route_no))
    print("{:<20}{:<12}{:>10}".format("STOP", "PICKUP", "FEE/MONTH"))

    for stop, time, fee in cur.fetchall():
        # MySQL's TIME type comes back as a Python object, so str() is used to
        # print it as text.
        print("{:<20}{:<12}{:>10.2f}".format(stop, str(time), fee))


def issue_pass(admission_no, stop_name):
    """Issue a quarterly pass for a student boarding at a given stop."""
    # The route and the fee are looked up FROM the stop, so neither has to be
    # typed by the clerk and neither can be entered wrongly.
    cur.execute("SELECT route_no, monthly_fee FROM stops WHERE stop_name = %s", (stop_name,))
    row = cur.fetchone()

    # A stop the buses do not serve gives no row at all. Saying so is better
    # than issuing a pass that is not valid anywhere.
    if row is None:
        print("No bus stops at {}.".format(stop_name))
        return

    route_no, fee = row

    cur.execute("INSERT INTO passes (admission_no, route_no, stop_name, issued_on, valid_months)"
                " VALUES (%s, %s, %s, %s, %s)",
                (admission_no, route_no, stop_name, date.today(), 3))

    db.commit()

    # Quarterly fee worked out from the monthly one, rather than stored twice.
    print("Pass issued: {} boards Route {} at {}. Quarterly fee Rs {:.2f}".format(
        admission_no, route_no, stop_name, fee * 3))


def route_strength():
    """How many students travel on each route."""
    # COUNT(*) with GROUP BY gives one row per route with its total, which is
    # how an overloaded bus becomes visible before term starts.
    cur.execute("SELECT route_no, COUNT(*) FROM passes GROUP BY route_no ORDER BY route_no")

    print("\nSTUDENTS PER ROUTE")

    for route_no, count in cur.fetchall():
        print("  Route {:<4} {:>3} students".format(route_no, count))


# --- the program itself ----------------------------------------------------

stops_on_route(2)              # answer an enquiry about route 2
route_strength()               # then show how full each bus is

db.close()

CREATE DATABASE IF NOT EXISTS lambdalab_transport;
USE lambdalab_transport;

CREATE TABLE stops (
    stop_id     INT PRIMARY KEY AUTO_INCREMENT,
    route_no    INT NOT NULL,
    stop_name   VARCHAR(20) NOT NULL,
    pickup_time TIME,
    monthly_fee DECIMAL(8,2)
);

CREATE TABLE passes (
    pass_id      INT PRIMARY KEY AUTO_INCREMENT,
    admission_no VARCHAR(8) NOT NULL,
    route_no     INT,
    stop_name    VARCHAR(20),
    issued_on    DATE,
    valid_months INT DEFAULT 3
);

INSERT INTO stops (route_no, stop_name, pickup_time, monthly_fee) VALUES
    (2, 'Bazaar Gate',    '07:05:00', 700.00),
    (2, 'Hospital Chowk', '07:20:00', 650.00),
    (2, 'Nehru Park',     '07:35:00', 600.00),
    (1, 'Station Road',   '07:10:00', 750.00);

INSERT INTO passes (admission_no, route_no, stop_name, issued_on, valid_months) VALUES
    ('A2201', 2, 'Bazaar Gate',  '2026-07-01', 3),
    ('A2202', 2, 'Nehru Park',   '2026-07-01', 3),
    ('A2203', 1, 'Station Road', '2026-07-05', 3);

8Sample output

This is a real run, not a mock-up — the transcript below is what the program actually printed.

C:\LambdaLab\Projects\Bus-Transport> python transport.py
LAMBDALAB PUBLIC SCHOOL — ROUTE 2
STOP                PICKUP       FEE/MONTH
Bazaar Gate         07:05           700.00
Hospital Chowk      07:20           650.00
Nehru Park          07:35           600.00

STUDENTS PER ROUTE
  Route 1      1 students
  Route 2      2 students

9Testing

Every case below was actually run and the result recorded as it appeared — including the ones expected to fail.

Two kinds of case are in there on purpose. The boundary cases test the edge of a rule, where a program is most often wrong by one. The failure cases check that it refuses cleanly and says why, instead of quietly producing a wrong answer.

10Advantages

Set against the ways the job is done today:

• The same enquiry is answered identically however often it is asked
• A pass cannot be issued for a stop the buses do not serve
• The fee comes from the stop, so it can never be typed wrongly
• An overloaded route is visible in June rather than in September
• Route timings live in one table and are changed in one place

11Future scope

What this version does not do

A report should be honest about its own limits, and each of these is the reason for one of the additions below:

• A fee revision changes what old passes appear to have cost
• The bus's seating capacity is not stored, so overloading is reported but not prevented
• Fee payment and pass expiry are not tracked
• One stop belongs to one route only; shared stops are not supported

Proposed enhancements

This is also where you make the project yours. Pick one or two of these, or something nobody here thought of:

• Print the pass with the student's photo
• Warn when a route is booked past the bus's seat count
• Record fee payments and flag passes about to expire
• Show the stops on a map
• A parent-facing page showing where the bus has reached

12What you may have to teach yourself

CBSE expects some self-learning in a project, and says so. For this one, that means:

• GROUP BY with COUNT(*), which is the whole headcount feature
• Why storing the fee once is better than copying it into every pass
• MySQL's TIME type, and how it comes back into Python

13Conclusion

The office can answer a route enquiry in seconds, issue a pass at the right fee without looking anything up, and count how many students are on each bus before term begins.

Storing the fee against the stop rather than copying it onto every pass keeps the data honest, at the cost of history. Deciding that trade-off deliberately, and writing it down, was part of the work.

14References

Every report needs a bibliography. This one used:

• Computer Science with Python, Class XII — the NCERT / CBSE prescribed textbook, for the chapters on database concepts, SQL and interfacing Python with a database
• Computer Science with Python, Class XI — for functions, lists, dictionaries and string handling
• Python 3 documentation — https://docs.python.org/3/
• MySQL 8.0 Reference Manual and the Connector/Python Developer Guide — https://dev.mysql.com/doc/
• CBSE Senior School Curriculum, Computer Science (Subject Code 083) — the project guidelines this report follows
• LambdaLab — https://www.lambdalab.in