Cricket Tournament Database & Operations Engine

A production-grade MySQL relational database system for modeling, managing, and analyzing international cricket tournament data — complete with a Python CRUD interface, stored procedures, triggers, and analytical views.

---

✨ Features

Category	Details
🗃️ Database	MySQL 8.0+, 6 normalized tables (3NF), full referential integrity
🔗 Relationships	Foreign keys, ON DELETE CASCADE, ENUM constraints, CHECK constraints
📊 Analytics	11 hand-crafted SQL queries (JOINs, GROUP BY, HAVING, Subqueries)
⚙️ Procedures	GetTeamPerformance, UpdateMatchWinner
🔔 Triggers	Before_Player_Insert (age validation), Check_Stadium_Capacity
👁️ Views	TournamentSummary, PlayerWithAge
🐍 Python	mysql-connector-python CRUD module with structured error handling

---

🏗️ Architecture Overview

cricket_tournament_db
│
├── Team             ◄──────────────────────────────┐
│   └── Player       (team_id FK, CASCADE DELETE)   │
│                                                    │
├── Tournament       ◄── winner_team_id (FK)         │
│   └── Match        (tournament_id FK)              │ FK refs
│       ├── Score    (match_id, team_id FKs)         │
│       └── Stadium  (stadium_id FK)                 │
│                                                    │
└─────────────────────────────────────────────────── ┘

---

📁 Project Structure

cricket-db-operations-engine/
├── implementation.sql        # Full DB schema: tables, data, queries, procedures, triggers, views
├── Connectivity.py           # Python CRUD module (mysql-connector-python)
├── cricket-database.png      # Entity-Relationship (ER) diagram – Chen's notation
├── DATABASE_DESIGN.md        # Complete database design documentation
├── USER_GUIDE.md             # Step-by-step setup and execution guide
└── README.md                 # This file

---

🚀 Quick Start

1. Prerequisites

# MySQL 8.0+
mysql --version

# Python 3.8+
python3 --version

# Install Python MySQL connector
pip install mysql-connector-python

2. Import the Database

# Log in to MySQL
mysql -u root -p

# Run the full schema script
mysql> SOURCE /path/to/implementation.sql;

3. Verify Setup

SHOW DATABASES;
USE cricket_tournament_db;
SHOW TABLES;

Expected tables:

+----------------------------+
| Tables_in_cricket_tournament_db |
+----------------------------+
| Match                      |
| Player                     |
| Score                      |
| Stadium                    |
| Team                       |
| Tournament                 |
+----------------------------+

4. Run Python Module

# Update credentials in Connectivity.py first
python3 Connectivity.py

Expected output:

✅ Connected to MySQL Database

📋 Players and Their Teams:
   Player: Babar Azam | Team: Pakistan
   Player: Virat Kohli | Team: India
   ...
✅ New player inserted successfully.
✅ Stadium updated successfully.
✅ Player deleted successfully.
📊 Team Performance Data...
🔒 MySQL connection closed.

---

🗺️ Entity-Relationship Diagram

Designed using Chen's notation. Rectangles = entities, ovals = attributes, diamonds = relationships, double lines = total participation.

---

📖 Documentation

• 📐 Database Design — ER model, relational schema, normalization proof, all 11 queries, triggers, procedures, and views explained.
• 🛠️ User Guide — Full installation, configuration, and usage walkthrough.

---

🧩 Sample Queries

-- Players with their team names (JOIN)
SELECT p.name AS Player_Name, t.name AS Team_Name, p.role
FROM PlayerWithAge p
JOIN Team t ON p.team_id = t.team_id
ORDER BY t.name;

-- Highest single-innings score
SELECT MAX(runs) AS highest_score, team_id, match_id
FROM Score GROUP BY team_id, match_id
ORDER BY highest_score DESC LIMIT 1;

-- Teams with more than 1 registered player (HAVING)
SELECT t.name, COUNT(p.player_id) AS total_players
FROM Team t JOIN Player p ON t.team_id = p.team_id
GROUP BY t.name HAVING COUNT(p.player_id) > 1;

---

🛡️ Data Integrity Features

• CHECK constraints on runs (>= 0), wickets (0–10), capacity (> 0), ICC ranking (> 0)
• ENUM types enforce valid formats (T20, ODI, Test), roles, and match stages
• Trigger: Rejects player insertions if age < 14 years
• Trigger: Silently prevents negative stadium capacity updates
• ON DELETE CASCADE: Removing a team automatically removes all associated players
• Safe re-run: DROP DATABASE IF EXISTS + CREATE TABLE IF NOT EXISTS for idempotent execution

---

🔧 Tech Stack