Classroom Scheduling System

A Python-based classroom scheduling system using SQLite to automate timetable management for educational institutions. Prevents scheduling conflicts, enforces capacity limits, respects projector requirements, and optimizes room assignments.

Features

• Conflict Detection: Prevents room double-booking, teacher overlaps, and course time conflicts
• Capacity Management: Validates room capacity against enrolled student counts
• Resource Planning: Ensures rooms with projectors are assigned to courses requiring them
• Auto-Assignment: Intelligent room recommendation using best-fit algorithm
• Schedule Management: Create, read, update, and delete schedule entries
• Reporting: Room utilization metrics and teacher workload analysis
• Transaction Safety: Foreign key constraints and data integrity enforcement

Project Structure

classroom_scheduling_system_de/
├── db.py                 # Database setup and connection management
├── crud.py              # Create/Read/Update/Delete operations for all entities
├── scheduler.py         # Conflict detection and auto-assignment logic
├── demo.py              # Sample data and demonstration of all features
├── test_scheduler.py    # Comprehensive test suite
├── README.md            # This file
└── scheduling.db        # SQLite database (created on first run)

Installation

Prerequisites

• Python 3.9+
• pip or conda

Setup

1. Clone or download the repository:

cd classroom_scheduling_system_de

2. Install dependencies (if using a virtual environment):

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate
pip install -r requirements.txt  # or just pytest for testing

3. Initialize the database:

python db.py

Quick Start

Basic Example

from db import init_db
from crud import add_room, add_teacher, add_course, add_timeslot
from scheduler import schedule_class

# Initialize database
init_db()

# Add resources
room = add_room("Lecture Hall 101", capacity=50, building="Main", has_projector=True)
teacher = add_teacher("Dr. Smith", "smith@university.edu", dept="Computer Science")
course = add_course("CS101", "Intro to Programming", enrolled=35, requires_projector=True)
timeslot = add_timeslot("Mon", "09:00", "10:00")

# Schedule a class
result = schedule_class(course, teacher, room, timeslot)
if result.success:
    print(f"Schedule created: #{result.schedule_id}")
    print(result.message)
else:
    print(f"Scheduling failed: {result.message}")

Run the Demo

python demo.py

This will:

1. Initialize the database
2. Add sample rooms, teachers, courses, and timeslots
3. Demonstrate manual scheduling with conflict detection
4. Show auto-assignment in action
5. Display room utilization and teacher workload reports

API Reference

Database (db.py)

• init_db(): Initialize SQLite database with all required tables
• get_connection(): Get a database connection with proper settings

CRUD Operations (crud.py)

Rooms

add_room(name, capacity, building="", has_projector=False) -> int
get_rooms() -> list[Row]
update_room(room_id, **kwargs)
delete_room(room_id)

Teachers

add_teacher(name, email, dept="") -> int
get_teachers() -> list[Row]
update_teacher(teacher_id, **kwargs)
delete_teacher(teacher_id)

Courses

add_course(code, title, enrolled=0, requires_projector=False) -> int
get_courses() -> list[Row]
update_course(course_id, **kwargs)
delete_course(course_id)

Timeslots

add_timeslot(day, start_time, end_time) -> int
get_timeslots() -> list[Row]
delete_timeslot(timeslot_id)

Schedules

get_schedules() -> list[Row]
delete_schedule(schedule_id)

Scheduling (scheduler.py)

Conflict Detection

check_conflicts(course_id, teacher_id, room_id, timeslot_id, 
                exclude_schedule_id=None) -> ConflictReport

Returns a ConflictReport with:

• has_conflict: bool - Whether any conflicts exist
• reasons: list[str] - Detailed conflict messages

Checks:

1. Room double-booking in same timeslot
2. Teacher double-booking in same timeslot
3. Course already scheduled in this slot
4. Room capacity vs enrolled students
5. Projector requirement satisfaction

Manual Scheduling

schedule_class(course_id, teacher_id, room_id, timeslot_id) -> ScheduleResult

Schedules a class after full conflict checking.

Returns:

• success: bool - Whether scheduling succeeded
• schedule_id: int - ID of created schedule (if successful)
• message: str - Status message

Schedule Updates

update_schedule(schedule_id, course_id, teacher_id, room_id, 
                timeslot_id) -> ScheduleResult

Updates an existing schedule entry with conflict checking.

Auto-Assignment

auto_assign(course_id, teacher_id, preferred_days=None) -> ScheduleResult

Automatically assigns a course to a free timeslot and suitable room.

Algorithm:

• Finds free timeslots for the teacher (optionally filtered by preferred days)
• Selects the smallest room that fits (best-fit algorithm)
• Respects projector requirements
• Returns first valid assignment or failure

Reporting

room_utilisation() -> list[dict]

Returns room utilization statistics:

[
    {
        'room': str,           # Room name
        'booked': int,         # Number of booked slots
        'total_slots': int,    # Total available slots
        'utilisation_pct': float  # Percentage booked
    },
    ...
]

teacher_load() -> list[Row]

Returns number of classes per teacher.

Database Schema

tables.rooms

CREATE TABLE rooms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    capacity INTEGER NOT NULL CHECK(capacity > 0),
    building TEXT,
    has_projector INTEGER NOT NULL DEFAULT 0
);

tables.teachers

CREATE TABLE teachers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    dept TEXT
);

tables.courses

CREATE TABLE courses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    enrolled INTEGER NOT NULL DEFAULT 0,
    requires_projector INTEGER NOT NULL DEFAULT 0
);

tables.timeslots

CREATE TABLE timeslots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    day TEXT NOT NULL CHECK(day IN ('Mon','Tue','Wed','Thu','Fri','Sat')),
    start_time TEXT NOT NULL,
    end_time TEXT NOT NULL,
    UNIQUE(day, start_time, end_time)
);

tables.schedules

CREATE TABLE schedules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
    teacher_id INTEGER NOT NULL REFERENCES teachers(id) ON DELETE CASCADE,
    room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    timeslot_id INTEGER NOT NULL REFERENCES timeslots(id) ON DELETE CASCADE,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(room_id, timeslot_id),
    UNIQUE(teacher_id, timeslot_id),
    UNIQUE(course_id, timeslot_id)
);

Constraints:

• A room cannot be booked twice in the same timeslot
• A teacher cannot teach two classes simultaneously
• A course cannot be scheduled twice in the same slot
• Foreign keys enforce referential integrity with cascading deletes

Testing

Run the test suite using pytest:

pytest test_scheduler.py -v

Test Coverage

Tests validate:

• ✓ Successful schedule creation
• ✓ Room double-booking prevention
• ✓ Teacher conflict detection
• ✓ Course duplicate scheduling prevention
• ✓ Room capacity validation
• ✓ Projector requirement enforcement
• ✓ Auto-assignment with various constraints
• ✓ Conflict reporting accuracy

All 13 tests should pass:

test_scheduler.py::TestManualScheduling::test_successful_schedule PASSED
test_scheduler.py::TestManualScheduling::test_room_double_booking PASSED
test_scheduler.py::TestManualScheduling::test_teacher_double_booking PASSED
test_scheduler.py::TestManualScheduling::test_course_double_slot PASSED
test_scheduler.py::TestManualScheduling::test_capacity_violation PASSED
test_scheduler.py::TestManualScheduling::test_projector_violation PASSED
test_scheduler.py::TestManualScheduling::test_no_conflict_different_slots PASSED
test_scheduler.py::TestAutoAssign::test_auto_assign_success PASSED
test_scheduler.py::TestAutoAssign::test_auto_assign_respects_projector PASSED
test_scheduler.py::TestAutoAssign::test_auto_assign_no_room_available PASSED
test_scheduler.py::TestAutoAssign::test_auto_assign_teacher_busy PASSED
test_scheduler.py::TestConflictReport::test_no_conflicts_report PASSED
test_scheduler.py::TestConflictReport::test_multiple_conflicts_reported PASSED

Usage Examples

Example 1: Schedule a Single Course

from db import init_db
from crud import add_room, add_teacher, add_course, add_timeslot
from scheduler import schedule_class, check_conflicts

init_db()

# Create entities
room_id = add_room("Lab-A", capacity=25, has_projector=False)
teacher_id = add_teacher("Dr. Johnson", "johnson@uni.edu", dept="Physics")
course_id = add_course("PHY201", "Physics II", enrolled=20)
timeslot_id = add_timeslot("Tue", "14:00", "15:00")

# Check for conflicts before scheduling
conflicts = check_conflicts(course_id, teacher_id, room_id, timeslot_id)
if conflicts.has_conflict:
    print(f"Cannot schedule: {conflicts}")
else:
    result = schedule_class(course_id, teacher_id, room_id, timeslot_id)
    print(result.message)

Example 2: Auto-Assign with Preferred Days

from scheduler import auto_assign

# Let the system find the best slot on Mon/Wed/Fri
result = auto_assign(
    course_id=course_id,
    teacher_id=teacher_id,
    preferred_days=["Mon", "Wed", "Fri"]
)

if result.success:
    print(f"Successfully auto-assigned: {result.message}")
else:
    print(f"Could not find suitable slot: {result.message}")

Example 3: View Room Utilization

from scheduler import room_utilisation

for stat in room_utilisation():
    utilization = stat['utilisation_pct']
    booked = stat['booked']
    total = stat['total_slots']
    print(f"{stat['room']:20s} {booked:2d}/{total} ({utilization:5.1f}%)")

Example 4: Update a Schedule

from scheduler import update_schedule

# Move a class to different room/time
result = update_schedule(
    schedule_id=42,
    course_id=course_id,
    teacher_id=teacher_id,
    room_id=new_room_id,
    timeslot_id=new_timeslot_id
)
print(result.message)

License

This project is provided as-is for educational purposes.

Contributors

• Ahan Mondal