FIFA World Cup Database Query System

A command-line interface for querying and managing the FIFA World Cup database.

Requirements

• Python 3.6+
• pymssql library
• Microsoft SQL Server database access

Installation & Running

Use the provided Makefile:

make install   # Install pymssql
make run       # Run the application
make clean     # Clean up __pycache__ and .pyc files
make           # Install and run

Or manually:

pip install pymssql
python main.py

Configuration

Set database credentials in interface/config.py (already set):

DB_CONFIG = {
    "server": "uranium.cs.umanitoba.ca",
    "database": "cs3380",
    "user": "YOUR_USERNAME",      # <-- Update this
    "password": "YOUR_PASSWORD",  # <-- Update this
}

Also ensure the 20 CSV files are in the ./csv/ directory for database repopulation.

Features

Main Menu

1. Run predefined queries - Execute analytical queries with pagination
2. View database tables - Browse data from any table
3. Database management - Reset, delete, or repopulate the database
4. Help menu - Instructions and navigation help
5. Quit - Exit the application

Predefined Queries

#	Name	Description
1a	Players with Most Top Finishes	Players with most WC finishes at standing ≤ N
1b	Teams with Most Top Finishes	Teams with most WC finishes at standing ≤ N
1c	Managers with Most Top Finishes	Managers with most WC finishes at standing ≤ N
2a	Players with Most Card Points	Players ranked by card points (red=1, yellow=0.5)
2b	Teams with Most Card Points	Teams ranked by card points
2c	Managers with Most Card Points	Managers ranked by team card points
3a	Players with Consecutive Top Finishes	Players with most consecutive WCs at standing ≤ N
3b	Teams with Consecutive Top Finishes	Teams with most consecutive WCs at standing ≤ N
3c	Managers with Consecutive Top Finishes	Managers with most consecutive WCs at standing ≤ N
4a	Players with Best Win Rate	Players ranked by match win percentage
4b	Teams with Best Win Rate	Teams ranked by match win percentage
4c	Managers with Best Win Rate	Managers ranked by match win percentage
5	Matches Between Recent Top Teams	Matches where both teams had top-4 in 2 most recent WCs
6	Strongest Squads by Prior Goals	Squads with most total goals by players in prior tournaments
7	Stadium Home Win Rate	Top stadiums by home team win rate
8	All Awards to Champions	Tournaments where every award went to champion team
9	Referees by Knockout Stage	Referees by matches officiated at stage level ≥ N
10	Referees with Most Card Points	Referees by total card points given
11	Penalty Shootout Record	Teams with best shootout win rate
12	Best Comeback Matches	Matches where winner overcame 2+ goal deficit
13	Stoppage Time Goals	Tournaments with most stoppage time goals
14	Goals by Position	Positions with most goals scored
15	Goals by Shirt Number	Shirt numbers with most goals
16	Golden Generation Players	Players with most consecutive WC appearances for a country
17	Confederations by Qualifications	Confederations by total team qualifications
18	Awards to Non-Champions	Tournaments with most awards to non-winning teams
19	Squads with Most Substitutions	Squads with most substitutions made
20	Substitute Scorers	Players who scored after being substituted in
21	Host Advantage by Stage	Hosts that performed better when hosting

Navigation

• Enter numbers or letters shown in brackets to select options
• [n] - Next page
• [p] - Previous page
• [e] - Export to CSV (saved to exports/ folder)
• [a] - Show all results
• [b] - Back to previous menu
• [m] - Main menu
• [q] - Quit

File Structure

project/
├── main.py                  # CLI entry point
├── Makefile                 # Build automation (install, run, clean)
├── creation.sql             # SQL script to create all tables (run on server)
├── populate_worldcup_db.py  # Standalone script to populate database from CSVs
├── report.pdf               # Project report detailing progress and design
├── README.md                # This documentation file
├── csv/                     # CSV data files directory
│   ├── awards.csv
│   ├── bookings.csv
│   ├── goals.csv
│   ├── ... (other CSV files)
├── exports/                 # Exported query results (CSV files)
└── interface/               # CLI application package
    ├── __init__.py          # Package initializer, exports main classes
    ├── app.py               # WorldCupApp main controller with menu systems
    ├── config.py            # Database credentials and settings
    ├── db.py                # DatabaseConnection class for query execution
    ├── db_manager.py        # DatabaseManager for reset/repopulate operations
    ├── display.py           # ASCII table formatting and pagination display
    └── queries.py           # 29 predefined analytical queries

File Descriptions

File	Description
main.py	Entry point for the CLI application. Initializes and runs WorldCupApp.
Makefile	Build automation script with targets for install, run, and clean.
creation.sql	SQL DDL script containing CREATE TABLE statements for all 22 tables. Run directly on the database server to create the schema.
populate_worldcup_db.py	Standalone Python script that reads CSV files and populates all database tables. Can be run independently or used by the CLI.
COMP 3380 Project Final Report.pdf	Project report documenting details.
README.md	This file - project documentation and usage instructions.
interface/__init__.py	Package initializer that exports WorldCupApp, DatabaseConnection, and QueryManager.
interface/app.py	Main application class containing all menu handlers, query execution, and user interaction logic.
interface/config.py	Configuration file storing database connection parameters (server, database, user, password).
interface/db.py	Database connection wrapper providing execute_query() and get_table_data() methods.
interface/db_manager.py	Database management utilities for dropping tables, resetting schema, and repopulating data.
interface/display.py	Output formatting functions for ASCII tables, pagination controls, and CSV export.
interface/queries.py	Dictionary of 29 predefined SQL queries with metadata (name, description, parameters, sort info).

Security

• No direct SQL input allowed
• All queries use parameterized statements
• User inputs are validated and sanitized
• SQL injection prevention built-in

Authors

COMP3380 - Group 44

• Adam Sameh
• Duc Do
• Minh Do