UFC Database Application

Project Overview

This Python application allows users to interact with a UFC (Ultimate Fighting Championship) database. Users can load raw fight data into a SQLite database and execute predefined SQL queries to analyze fighter statistics and match outcomes.

Features

Load data from CSV files into a SQLite database.
Execute predefined SQL queries for data analysis.
User-friendly interface for running queries and viewing results.

Prerequisites

Python 3.x
pandas library
sqlite3 library

Ensure you have Python installed on your system. You can install the required Python libraries using pip:

pip install pandas sqlite3

Installation

Clone the repository to your local machine:

git clone https://github.com/adrianoamalfi/ufc.git
cd ufc

Usage

Place your raw_fighter_details.csv and raw_total_fight_data.csv files in the same directory as the script.
Load data:

python 4_load_data.py

Run the application:

python 5_query_interface.py

Follow the on-screen instructions to interact with the database and execute queries.

Available Queries

Show the top 10 fighters with the most fights.
Show average significant strike percentages by corner color.
Show the top 10 fighters with the most wins.
Show the top 10 fight types by average last round duration.

Entity-Relationship Diagram (ERD)

The ERD illustrates the database schema and the relationships between tables.

Mermaid Format

To view the ERD in Mermaid format, check the ERD.mermaid file in this repository.

erDiagram
    Fighter {
        string Name PK "Primary Key"
        string Height "Nullable, in format ft'in''"
        string Weight "Nullable, in lbs"
        string Reach "Nullable, in inches"
        string Stance "Nullable, can be Orthodox, Southpaw, etc."
        date DOB "Nullable, Date of Birth"
    }

    Fight {
        string ID PK "Primary Key, Composite of fighters' names and date"
        date Date "Date when the fight took place"
        string Location "Location where the fight took place"
        string Format "Format of the fight, e.g., number of rounds"
        string Referee "Name of the referee"
        string Fight_type "Type of the fight, e.g., weight class"
        string R_fighter FK "Red corner fighter, references Fighter(Name)"
        string B_fighter FK "Blue corner fighter, references Fighter(Name)"
        string Winner FK "Name of the winner, references Fighter(Name)"
        string win_by "Method of victory"
        int last_round "The last round of the fight"
        string last_round_time "Time when the last round ended"
    }

    PerformanceMetrics {
        string ID PK "Primary Key, Composite of Fight ID and fighter"
        string Fight_ID FK "Foreign Key, references Fight(ID)"
        string Fighter_Name FK "Foreign Key, references Fighter(Name)"
        string Color "The fighter's corner color in this fight, 'R' for Red or 'B' for Blue"
        int KD "Knockdowns"
        string SIG_STR "Significant Strikes"
        string SIG_STR_pct "Significant Strikes Percentage"
        string TOTAL_STR "Total Strikes"
        string TD "Takedowns"
        string TD_pct "Takedown Percentage"
        int SUB_ATT "Submission Attempts"
        int REV "Reversals"
        string CTRL "Control Time"
    }

    Fighter ||--o{ Fight : "participates_in"
    Fight ||--o{ PerformanceMetrics : "is_recorded_in"
    Fighter ||--o{ PerformanceMetrics : "has_performance_metrics"

PNG Format

ERD PNG

DEMO

python .\5_query_interface.py

Choose an option:
1. Show top 10 fighters with the most fights
2. Show average significant strike percentages by corner color
3. Show top 10 fighters with the most wins
4. Show top 10 fight types by average last round duration
5. Exit
Enter your choice (1-5): 1

Top 10 fighters with the most fights:
       Fighter_Name  Total_Fights
0        Jim Miller            36
1    Donald Cerrone            36
2   Andrei Arlovski            34
3   Jeremy Stephens            33
4     Diego Sanchez            32
5       Demian Maia            32
6  Rafael Dos Anjos            30
7        Clay Guida            30
8   Michael Bisping            29
9     Gleison Tibau            28

Choose an option:
1. Show top 10 fighters with the most fights
2. Show average significant strike percentages by corner color
3. Show top 10 fighters with the most wins
4. Show top 10 fight types by average last round duration
5. Exit
Enter your choice (1-5): 5
Exiting program.

Contributing

Contributions to the UFC Database Application are welcome! Please ensure that your pull requests provide a clear description of what they add or fix.

License

MIT

Acknowledgements

This project was inspired by the rich dataset provided by the UFC and aims to provide meaningful insights into fight statistics and outcomes.