imdb-movies-sql

A group database project building a normalised MySQL database from a raw IMDb 2024 CSV dataset (provided by the professor, likely this one from Kaggle which is Public Domain), then running 16 SQL queries with Matplotlib visualisations. The project was completed as part of Prof. Renaud Verin's Bases de Données course at CY Tech.

The raw data needed substantial cleaning before it was usable: budgets and revenues came in mixed currencies (converted to USD), vote counts used shorthand like "1.2K" or "3M", runtimes were stored as strings like "1 hour 42 minutes", and dates included country annotations in parentheses. All of this was handled in Python with pandas before insertion into MySQL via SQLAlchemy.

Database structure

The normalised schema has five main tables — MOVIE, GENRE, ACTOR, MOVIE_LANGUAGE, and PRODUCTION_COMPANY — with junction tables (HAS_GENRE, FEATURES_ACTOR, IS_SPOKEN_IN) handling many-to-many relationships. Cardinalités (0,N) throughout, to accommodate partially filled entries in the real dataset.

Queries

16 SQL queries covering: films and average ratings by genre, revenue and budget by genre, top 10 most expensive productions, revenue/budget ratio (most profitable films), top 20 most prolific actors, genre breakdown per actor (Lily-Rose Depp, Selena Gomez, Eva Green), runtime vs. rating correlation, films per country, average runtime by country, and most-spoken languages.

One mildly embarrassing moment: we built a query to visualise average budget evolution over time before realising the entire dataset is from 2024. We pivoted to monthly granularity, which turned out to be more interesting anyway.

To run

1. Install dependencies:

pip install mysql-connector-python sqlalchemy pandas matplotlib

2. Set up a local MySQL instance and update credentials in BDDConnexion.py
3. Run BDDConnexion.py — this creates the database, cleans and imports the CSV, builds the schema, and runs all queries and graphs

Files

• BDDConnexion.py — main pipeline (cleaning, import, schema, queries, graphs)
• MoviesSQL.sql — SQL schema
• MoviesSQL.mwb — MySQL Workbench model
• MCD.png — entity-relationship diagram
• IMDb_2024_Movies_TV_Shows.csv — source data
• IMDb_2024_Movies_TV_Shows.MetaData.csv — field definitions

---

Group project (ID3A, CY Tech / CY école de design, 2024–25). Full report with query results and graphs in the PDF (in French).