College Management AI Assistant (v5)

An intelligent college administrative assistant powered by Google Gemini and the Model Context Protocol (MCP). The assistant interacts with a relational SQLite database through structured FastMCP tools to answer inquiries regarding students, faculty, courses, enrollments, and academic performance.

Architecture Overview

+-------------------------------------------------------------+
|                      User / Client                          |
+-------------------------------------------------------------+
                              |
                              v
+-------------------------------------------------------------+
|                   Gemini LLM Assistant                      |
|                         (bot.py)                            |
+-------------------------------------------------------------+
                              |
                     MCP Protocol (stdio)
                              |
                              v
+-------------------------------------------------------------+
|                     FastMCP Server                          |
|                      (tooling.py)                           |
+-------------------------------------------------------------+
                              |
                        SQLite Queries
                              |
                              v
+-------------------------------------------------------------+
|                   College SQLite Database                   |
|                        (college.db)                         |
|   - persons (students, faculty, staff)                      |
|   - courses                                                 |
|   - student_enrollments                                     |
+-------------------------------------------------------------+

Related MCP server: College Scorecard MCP Server

Features

• Synthetic Data Generation: Creates realistic college records using Faker including students, faculty, courses, and grade histories.

• FastMCP Tooling: Exposes robust, schema-compliant MCP tools for querying academic records, courses, departments, and enrollment statistics.

• Interactive Multi-turn Bot: Chat interface leveraging the Google GenAI SDK (google-genai) with automatic tool call orchestration via MCP.

• Secure Configuration: Environment-based API key management with .env support.

Project Structure

.
├── bot.py                   # Interactive Gemini chatbot integrating MCP tools via stdio
├── tooling.py               # FastMCP Server exposing database querying tools
├── create_data.py           # SQLite database schema initializer & synthetic data generator
├── college.db               # SQLite database file (generated by create_data.py)
├── system_prompt.txt        # System instruction prompt for the AI assistant
├── prompt.txt               # Context and initial project design prompts
├── mcp_config.example.json  # Example configuration for MCP client registration
├── requirements.txt         # Python package dependencies
├── .env                     # Environment variables (GEMINI_API_KEY)
└── .gitignore               # Git ignore file for Python, SQLite, and secrets

Database Schema

The SQLite database (college.db) includes the following relational tables:

1. persons: Stores students, faculty, and staff profiles (ID, name, email, phone, DOB, gender, address, role).

2. courses: Stores course listings (course ID, code, course name, department, credits, assigned instructor).

3. student_enrollments: Stores enrollment details (enrollment ID, student ID, course ID, date, grade, semester).

Available MCP Tools (tooling.py)

• get_student_info(student_id_or_name): Retrieve student profiles by ID, name, or email.

• get_course_details(course_code_or_name): Get course details and instructor info.

• get_student_enrollments(student_id_or_name): List all courses and grades for a student.

• get_students_by_course(course_code_or_name): List students enrolled in a given course.

• list_courses_by_department(department): List all courses in an academic department.

• get_courses_taught_by_faculty(faculty_id_or_name): List courses taught by a faculty member.

• search_persons(query, role): Search persons across roles (student, faculty, staff).

• get_course_statistics(course_code_or_name): Summary stats (total enrolled, grade distribution).

• list_all_courses(): Complete directory of all college courses.

• execute_custom_sql_query(query): Read-only query execution for complex aggregations.

Setup & Installation

1. Prerequisites

• Python 3.10+ installed

• Google Gemini API Key (Google AI Studio)

2. Install Dependencies

pip install -r requirements.txt

3. Configure Environment Variables

Create or edit your .env file in the project root:

GEMINI_API_KEY=your_actual_gemini_api_key_here

4. Generate Synthetic Database

Initialize the database with synthetic records:

python create_data.py

Usage

Run the Interactive AI Bot

python bot.py

Type your academic or administrative questions (e.g., "Show me all courses in Computer Science" or "What grades did John Doe receive?"). Type exit or quit to end the session.

Connect as an MCP Server (e.g. IDE / Claude Desktop / Antigravity)

Reference mcp_config.example.json to register tooling.py in your MCP client configuration:

{
  "mcpServers": {
    "college-tools-mcp": {
      "command": "python",
      "args": [
        "-Wignore",
        "<FULL_PATH_TO>/tooling.py"
      ]
    }
  }
}