You will create a conceptual schema database design using the EER model.

The database that you will design will keep track of the Soccer World Cup teams, match results, players, stadium, cards, and goals among other information. Here are the requirements for the database:

1. We want to store and query information for the SOCCER database for Mens World Cup, which is held once every 4 years. This tournament is divided into 6 confederations roughly divided by continents: AFC (Asia), CAF (Africa), CONCACAF (North and Central America and the Caribbean), CONMEBOL (South America), OFC (Oceania New Zealand and Pacific Island Countries), and UEFA (Europe). Most world countries are members of this tournament through one of its confederations, and the countries in each confederation play qualifying tournaments to determine which countries will participate in the world cup tournament, held every 4 years.

2. Thirty-two countries participated in the world cup tournament, held in Russia. The SOCCER database will store information on each participating COUNTRY and PLAYER, as well as each match_result. It will also include information about each STADIUM, as well as which players scored goals in which matches and which players received cards (yellow warning, or red dismissal) in which games.

3. For each TEAM from 32 countries, we shall keep track of the country name (CName unique), Continent, Confederation, Population, and another unique attribute (surrogate key) called Cid. (The Cid will be determined by which group the country was placed in during the world cup draw see item 7 a) below).

4. Each country has a roster of 23 players, with jersey numbers 1, 2, , 23 for each country. For each PLAYER, we shall keep track of the following: Country (team) the player plays for, player number PNo (this is the jersey number 1 through 23), playing Position (possible values are: GK goalkeeper, DF defender, MF midfielder, or FW forward), the player full name PName, player jersey name PJName (this is the name that appears on the back of the player jersey (shirt) along with the player number), BirthDate, Club (the name of the club team the player currently plays for), Height, and Weight. If a country is not participating in the tournament, its players will not be playing, and our database will not hold information about the countrys players.

5. The SOCCER database shall keep information about the stadiums used as venues for the matches. Each STADIUM has a stadium id (SId a surrogate key), stadium name (SName), City, and Capacity (max. no. of spectators).

6. For each MATCH (game) played, we will keep track of the following information: match/game id (GId a surrogate key), game type (GType see description below in item 7), date the match was played (GDate), the venue where the game was played (Stadium), first team (Team1), second team (Team2), first team score (Score1), and second team score (Score2).

7. The game type GType indicates if it is a group game or knockout game. It can be either a group game or a knockout game as described in a) and b) below. a) Initially, each of the 32 teams are assigned during the world cup draw to one of 8 groups: group A, B, C, D, E, F, G, or H. Each group has 4 teams and they play each other round-robin manner so each group has a total of 6 games played (for example, group A has Russia, Saudi Arabia, Egypt, and Uruguay so they play the following 6 matches: Russia v Saudi Arabia, Egypt v Uruguay, Russia v Egypt, Saudi Arabia v Uruguay, Russia v Uruguay, Saudi Arabia v Egypt). The GType for a group game will be a single letter indicating the group name (A, B, C, D, E, F, G, or H) to which the match belonged. The team country id (surrogate key Cid see item 1) will be A1, A2, A3, A4, for teams in group A, and similarly for other groups. b) The top two teams in each group will play in the knockout games round of sixteen (X), Quarterfinals (Q), Semifinals (S), FinaL (L), Third place game (T). The letters X, Q, S, L, T will indicate the GType for the knockout games. Although tie games (draws) are allowed for group games, they are not allowed for knockout games. So, three subtypes of knockout games are possible: regular (R game was determined in regular time of 90 minutes); extra time (E an extra 30 minutes were played to determine the winner), or penalty kicks (P if the game is tied after extra time, it is determined by alternating penalty kicks). The database will keep track of the subtype of each knockout game.

8. The SOCCER database will also keep track of statistics on which players played in which matches (each match has 2 teams, and each team starts with 11 of the 23 players they can substitute up to 3 players during the 90-minute game, and 1 more player if the game is a knockout game that goes into an extra 30 minutes). So, in each game, a team starts with 11 players and may have between 0 and 3 (or 4) players substitutes. If a substitution is made, the minute of the game when the substitution is made is stored along with the substitution info (which player was taken out and which player replaced him).

9. The SOCCER database will also keep track of which players scored goals during which matches, and which players received disciplinary cards within which matches. For each goal scored, the player who scored it and the time of the goal is recorded in the database. See item 10 below for different types of goals.

10. There are 3 types pf goals scored: regular goal (R), penalty kick goal (P), and own goal (O) (an own goal is scored by a player into his own teams net by mistake).

11. The SOCCER database will also keep track of which players received disciplinary cards within which matches. For each card issued by the referee, the player who received the card and the time of the card is recorded in the database, as well as the type of the card: red card (R results in player expulsion) or yellow card (Y player warning)