The European Football Cups Database Version 1.0 Release Date: 14 October, 2014 Updated as of 4 June 2017 (after 2016/2017 campaign) -------------------------------------------------------------------------------------------------- CONTENTS 1 Introduction 1.1 Name of the teams 1.2 Old and new nations 2 Sources 3 Formats 4 Using this Database 5 Tables 5.1 main table 5.2 teams table 5.3 competition table 5.4 rounds table 6 Copyright Notice 7 Contact Information -------------------------------------------------------------------------------------------------- 1 - Introduction This database is the result of a data collection made on some sites (listed below), so it is susceptible of errors. If you find any error or have some suggestions, please send your feedback using the contacts stated at the end of this document. The database contains all the matches played in the European Cups, with the results and the type of ending (regular times, extra time, penalty shootout, play-off or a draw). In this first release, the competitions considered are: - Champions League/European Cup - Europa League/UEFA Cup - Cup Winners' Cup - Inter-Cities Fairs Cup - UEFA Supercup In the future, the Intertoto Cup will also be added. In the main table, every match is recorded twice: once from the point of view of a team, and once from the other's. For example, if Team A wins 1-0 a match against Team B, you find the following two records: mainID team opponent gf ga 1 Team A Team B 1 0 2 Team B Team A 0 1 The database will be constantly updated as European Cups matches are played. You will find the updated version on my site (http://nicolacerin.altervista.org) and on my blog (http://footballfinder.blogspot.it). -------------------------------------------------------------------------------------------------- 1.1 - Teams' names The names of the clubs were normalized, using the English transliteration where possibile. -------------------------------------------------------------------------------------------------- 1.2 - Old and new nations Many teams changed the country they played for, mainly because of the variations occurred in Europe during the last decades (e.g. the split of USSR and Yugoslavia). In the table "teams" you find the old and the new country the teams played for, and the year when they started to play the European Cups for the new nation (if they qualified to play them). -------------------------------------------------------------------------------------------------- 2 - Sources www.uefa.com www.rsssf.com kassiesa.home.xs4all.nl/bert/uefa www.sharkscores.com www.eurocups-uefa.ru -------------------------------------------------------------------------------------------------- 3 - Formats The database can be downloaded in two formats. The contents of each version are listed below. SQL Version: EuropeanCups.sql readme.txt Comma Delimited Version: main.csv teams.csv competitions.csv rounds.csv readme.txt -------------------------------------------------------------------------------------------------- 4 - Using the database The database is relational, so it is better to open it with a software that can use this kind of database. The csv files can be opened with a software like MS Excel, MS Access or OpenOffice. The sql file can be opened with a database application, like MySQL. -------------------------------------------------------------------------------------------------- 5 - Tables The database is comprised of the following tables: main teams competitions rounds Sections 5.1 through 5.4 describe the tables and the fields that each contains. -------------------------------------------------------------------------------------------------- 5.1 - main table mainID progressive id of the record (key field) year year when the competition ended competition code of the competition team name of the team round code of the round round_n progressive order of the rounds inside that competition team_round progressive order of the rounds played by the team in that competition round_passed if the team passed the round (1) or not (0) opponent opponent team's name field1 where the team played the first leg: home (h), away (a) or in a neutral field (n) gf1 goals scored by the team in the first leg ga1 goals conceded by the team in the first leg field2 where the team played the second leg: home (h), away (a) or in a neutral field (n) gf2 goals scored by the team in the second leg ga2 goals conceded by the team in the second leg pen penalties shootout result po play-off result et round ended after extratime (0 = no, 1 = yes) w number of wins for the "team in that round d number of draws for the team in that round l number of losses for the team in that round rwae round won after extratime (0 = no, 1 = yes) rlae round lost after extratime (0 = no, 1 = yes) rwape round won after penalties shootout (0 = no, 1 = yes) rlape round lost after penalties shootout (0 = no, 1 = yes) rwapo round won after play-off (0 = no, 1 = yes) rlapo round lost after play-off (0 = no, 1 = yes) rwad round won after a coin draw (0 = no, 1 = yes) rlad round lost after a coin draw (0 = no, 1 = yes) -------------------------------------------------------------------------------------------------- 5.2 - teams table team name of the team (key field) old_nation old country which the team played for in the past (see below) new_nation current country which the team plays for change_year the first year the team should have played (if they qualified) the European Cups for the new country -------------------------------------------------------------------------------------------------- 5.3 - competition table code code used to identify the competition (key field) description description of the competition -------------------------------------------------------------------------------------------------- 5.4 - rounds table code code used to identify the round (key field) description description of the type of round -------------------------------------------------------------------------------------------------- 6 - Copyright Notice & Limited Use License This database is copyright 2014 by Nicola Cerin, and is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. For details see: http://creativecommons.org/licenses/by-sa/3.0/ For licensing information or further information, contact me at: nicolacerin@gmail.com -------------------------------------------------------------------------------------------------- 7 - Contact Information Please send me your feedback using one of the following channels: Web site: http://footballfinder.blogspot.it Facebook: http://www.facebook.com/pages/Football-Finder/939814179379147 Twitter: http://twitter.com/football_finder E-Mail : nicolacerin@gmail.com