This project is a relational database implemented in DB2 for World Cup Soccer games. The principle ideas of the tournaments are as follows. There are 32 teams. Matches (also referred to as games) are played in rounds. The first round is called a group round. Each team is put into one of 8 groups (4 teams per group), and each team plays against each other team in the group, i.e., 3 matches per team during the group round. A match has a regular playing time of 90 minutes but typically there are a few extra minutes played to compensate for pauses within the game due to injuries etc. A win brings 3 points, a loss brings 0 points, and a tie (same number of goals at end of game) brings 1 point. Teams are ranked within their group according to the number of points they have. If teams are tied in number of points, the team with the larger goal difference (total number of goals scored in the three matches minus the total number of goals against them) will have the better position. The best two teams of each group advance to the next round while the others are eliminated from the competition. From there, a knockout phase has several further rounds: “round of 16”, “quarterfinals”, “semifinals”, “final / 3rd place”. In the “round of 16”, each of the 16 qualifying teams of the previous round (two best of each group) plays against one other team and the 8 winning teams advance to the next round while the loosing teams are out of the competition. The same holds then for the quarterfinals (8 qualifying teams in 4 matches), leading to 4 qualifying teams for the semifinals. In the semifinals, the winners of the two matches advance to the final to play for the cup, and the losers play each other for 3rd place. In the context of this project, we are not interested in the rules that decide who plays against whom in the round of 16, quarterfinals or semifinals. While matches in the group round can be tied, all other matches must have a winner. Thus, if the scores are tied after the 90 minutes regular time, there are 30 minutes extra time to play, called prolongation, and if the game is still tied after that, there are penalty kicks. Each team has five penalty kicks and if they are still tied after that, the penalty shootout continues, with each team getting an extra penalty kick until tie breaks with the team that is first to have more goals.
Teams(country, status, goal_differential, web_URL, gname)
web_URL foreign key referencing NationalAssociation
web_URL NOT NULL
gname foreign key referencing Groups
gname NOT NULL
Grouped(country, gname, points)
country foreign key referencing Teams
gname foreign key referencing Groups
Groups(gname)
National Associations(web_URL, aname, country)
country foreign key referencing Teams
country NOT NULL
Players(pid, pname, number, position, DOB, country)
country foreign key referencing Teams
country NOT NULL
Coaches(cid, cname, DOB, role, country)
country foreign key referencing Teams
country NOT NULL
Referees(rid, rname, country, experience)
Stadiums(sname, capacity, location)
Matches(mid, match_length, score, start_time, round, date, sname)
sname foreign key referencing Stadiums
sname NOT NULL
Scheduled(country, mid)
country foreign key referencing Teams
mid foreign key referencing Matches
Refereed(rid, mid, role)
rid foreign key referencing Referees
mid foreign key referencing Matches
Played(pid, mid, y_cards, r_card, specific_position, time_in, time_out)
pid foreign key referencing Players
mid foreign key referencing Matches
Goals(gid, pid, mid, penalty, occurrence_order, scorer_name, time)
pid foreign key referencing Players
pid NOT NULL
mid foreign key referencing Matches
mid NOT NULL
Seats(seatNumber, sname, sectionNumber)
sname foreign key referencing Stadiums
sname NOT NULL
Clients(email, name, password)
Tickets(date, seatNumber, sname, mid, price, purchase_status)
seatNumber foreign key referencing Seat
sname foreign key referencing Stadiums
mid foreign key referencing Matches
Buys(email, date, seatNumber, sname, mid)
email foreign key referencing Client
date foreign key referencing Tickets
seatNumber foreign key referencing Seat
sname foreign key referencing Stadiums
mid foreign key referencing Matches
Detailed documentation of the database design including more details about purchasing tickets, assumptions, and restrictions can be found here: Database Design Documentation.
The tables created from the relational translation can be found here: Create Table DDL Statements.
Note that there are some key differences between the relational translation and the relations created in DB2:
1. ‘Scheduled’ relation expressing a participation constraint between ‘Teams’ and ‘Matches’ was not implemented as its own relation table in the database, and was instead implemented as 2 attributes in the ‘Matches’ entities. They must be NOT NULL to ensure a Match doesn’t exist without having 2 teams that will play in it (participation constraint).
2. Score attribute in ‘Matches’ was implemented as separate t1_score and t2_score, as we now have team1 and team2 attributes as defined above. The previously defined score attribute in the Part1 relational schema would have needed to be a string because of our design, which would not be useful in any types of queries. On the other hand, the new separate score attributes serve the same purpose and can be queried appropriately, i.e. the score attribute didn’t store useful data compared to the separate team1 score and team2 score attributes. However, most queries can still use the ‘Goals’ relation to calculate scores of all matches, since the ‘Goals’ relation links all goals to their scorer and respective match.
3. The National Association relation is represented within the Teams relation as name and URL attributes, possible by the one-to-one constraint between them (i.e. no relation table created for National Associations).
The script used to load the tables with data can be found here: Insert Data DML Statements.
The current data is just mock data used for testing our database and application. You can use it as a template for your own data.
Helpful and interesting queries to run on the database can be found here: Query Examples.
Detailed documentation of the database creation process including creating tables, loading tables, and query examples can be found here: Database Creation Documentation.
An application program witten in Java with a simple user-friendly interface can be found here: Soccer Database Application. A sample of the application and possible options is shown below:
Detailed documentation, constraints, and usage of the application can be found here: App Documentation.
- Dominic Weber