⚽ Football Leagues Database System

A relational database project designed to manage football leagues, teams, matches, players, referees, stadiums, tickets, rankings, and match events.

The project demonstrates the complete database design lifecycle, including conceptual modeling, relational modeling, database implementation, and query development.

---

📌 Introduction

This project was developed as part of a Database Design course and focuses on modeling and implementing a football league management system.

The database supports various entities involved in football competitions and captures their relationships through a structured relational design.

The project includes:

• Entity Relationship Diagram (ERD)
• Relational Model
• Database Schema Implementation
• Relational Algebra
• SQL Queries
• Database Backup Package

---

🧑🏻‍💻 Developers

This project was collaboratively developed by:

• Seyyedeh Fargol Nazemzadeh
• Yahya Hosseini
• Elaheh Ferdowsi

---

✨ Features

🏆 League Management

• Store league information
• Track seasons
• Manage league types

👥 Team Management

• Team registration
• Team rankings
• Team participation in matches

⚽ Match Management

• Match scheduling
• Home and away teams
• Match statistics
• Match outcomes

🧍🏻 Personnel Management

• Players
• Referees
• Supervisors
• Technical Staff

🎟️ Ticket Management

• Ticket information
• Purchase records
• Seat information
• Pricing management

🏟️ Stadium Management

• Stadium information
• Capacity tracking
• Stadium locations

📊 Ranking System

• Wins
• Draws
• Losses
• Goals scored
• Goals conceded
• Team rankings

📅 Match Events

• Event tracking
• Event types
• Event timestamps

---

🏗️ Database Design Process

The project follows a standard database design workflow:

Requirements Analysis
        ↓
Conceptual Design (ERD)
        ↓
Relational Model
        ↓
Database Implementation
        ↓
SQL Query Development
        ↓
Testing & Validation

---

📸 Database Design Artifacts

📐 Entity Relationship Diagram (ERD)

The project includes a complete ERD describing entities, attributes, and relationships within the football league system.

🗄️ Relational Model

A relational schema was derived from the ERD and normalized for implementation.

---

📂 Project Structure

Football-Leagues-Database-System/
│
├── Football Leagues ERD.png
│   └── Entity Relationship Diagram
│
├── Football Leagues Relational Model.png
│   └── Relational Schema Design
│
├── Football Leagues Tables Queries.sql
│   └── Table creation scripts
│
├── Football Leagues Retrieval Queries.sql
│   └── Data retrieval queries
│
├── Football Leagues Algebra.pdf
│   └── Relational Algebra Solutions
│
├── Football Leagues Database.bacpac
│   └── Database Backup Package
│
├── Database Project Work Report.pdf
│   └── Project Documentation
│
└── README.md    

---

🛠️ Technologies

Technology	Purpose
SQL	Database Implementation
SQL Server	Database Management
ER Modeling	Conceptual Design
Relational Algebra	Query Design
Database Normalization	Schema Optimization

---

💡 Example Queries

The project supports queries such as:

• Retrieve league standings
• View match information
• List team players
• Track match events
• Display stadium information
• Retrieve ticket sales data
• Analyze team performance statistics

🏆 League Standings

Retrieve teams ranked by total points in a league.

SELECT TeamName, Points, Wins, Draws, Losses
FROM Rankings
ORDER BY Points DESC;

---

⚽ Match Schedule

Display upcoming matches along with participating teams and stadium information.

SELECT
    HT.TeamName AS HomeTeam,
    AT.TeamName AS AwayTeam,
    S.StadiumName,
    M.MatchDate
FROM Matches M
JOIN Teams HT ON M.HomeTeamID = HT.TeamID
JOIN Teams AT ON M.AwayTeamID = AT.TeamID
JOIN Stadiums S ON M.StadiumID = S.StadiumID;

---

🎟️ Ticket Sales Analysis

Calculate the number of tickets sold for each match.

SELECT
    MatchID,
    COUNT(*) AS TicketsSold
FROM Tickets
GROUP BY MatchID
ORDER BY TicketsSold DESC;

---

🏟️ Stadium Utilization

Compare ticket sales against stadium capacity.

SELECT
    S.StadiumName,
    S.Capacity,
    COUNT(T.TicketID) AS SoldTickets,
    (COUNT(T.TicketID) * 100.0 / S.Capacity) AS OccupancyRate
FROM Stadiums S
JOIN Matches M ON S.StadiumID = M.StadiumID
LEFT JOIN Tickets T ON M.MatchID = T.MatchID
GROUP BY S.StadiumName, S.Capacity;

---

🔍 Advanced Query Scenarios

📊 Performance and Statistical Analysis

Generate comprehensive statistical reports for leagues, teams, players, coaches, referees, and matches. The database supports advanced performance analysis through:

• Individual and team performance metrics
• Match-by-match and season-wide statistics
• Ranking and comparative analysis
• Goal, card, and event tracking
• Historical performance evaluation
• Trend and consistency analysis

These capabilities enable detailed sports analytics and data-driven decision making.

---

⚽ Match and Competition Insights

Retrieve detailed information about competitions and matches, including:

• Match schedules and results
• Team participation records
• Stadium assignments
• Match events and timelines
• Referee and supervisor assignments
• Attendance and operational statistics

The database can generate complete reports covering both sporting and organizational aspects of football competitions.

---

👥 Personnel and Contract Management

Analyze and manage information related to players, coaches, technical staff, referees, and supervisors, including:

• Contract history and employment records
• Team affiliations and transfers
• Career progression and participation history
• Staff assignments across competitions
• Contract duration and compensation analysis

These queries support both administrative management and long-term personnel tracking.

---

💰 Financial and Operational Reporting

Produce reports related to the operational and financial aspects of football leagues, such as:

• Ticket sales and revenue analysis
• Stadium utilization and attendance statistics
• Match-related operational metrics
• Financial performance indicators
• Resource allocation and planning insights

This enables evaluation of both sporting success and business performance.

---

🏆 League and Team Evaluation

Support advanced league management through:

• Standings and ranking calculations
• Team comparison reports
• Win/loss and performance analysis
• Goal and disciplinary statistics
• Season summaries and historical comparisons

These reports provide a comprehensive view of competitive performance across leagues and seasons.

---

📈 Analytical Capabilities

The database supports complex analytical queries involving:

• Multi-table joins across interconnected entities
• Aggregation and statistical calculations
• Conditional analysis using business rules
• Historical and longitudinal reporting
• Comparative and ranking-based queries
• Event-driven analytics
• Cross-entity relationship analysis

These capabilities allow the system to answer a wide variety of operational, managerial, and analytical questions beyond standard data retrieval.

---

🎯 Learning Objectives

This project demonstrates:

• Database Design
• ER Modeling
• Relational Modeling
• SQL Development
• Relational Algebra
• Database Documentation
• Sports Information Systems

---

🎓 Academic Project

This project was developed as part of a Database Design course and focuses on applying database modeling principles to a real-world football league management scenario.

---

🙏🏻 Acknowledgments

• Database Design Course
• Faculty of Farabi, University of Tehran
• Academic Year 1402–1403