Creating and manipulating a MySQL schema for soccer Domain Description The attached file contains information on...

 

Transcribed Image Text:

Creating and manipulating a MySQL schema for soccer Domain Description The attached file contains information on the soccer (European football) players for the 2017/2018 Premier League. The Premier league is an English professional soccer league. The Premier League is the most watched sports league in the world, broadcasted in 212 territories to 643 million homes and a potential TV audience of 4.7 billion people. A game of soccer is played with 2 teams. There are 20 teams in the league. Each team plays 2 matches with each of the other 19 opponents, one in the home stadium and the other in an away stadium. Thus, in a season there are 380 matches in total and 38 matchdays. Each match is played in two halves, each half is 45 minutes. The team which scores more goals at the end of the second half wins, if both teams score the same number of goals, the match ends in a draw. Data File Description There are three different csv files. Each row within the match's file is data specific to one match. Each row within the stadium file is specific to each team's home stadium. Each team has only one home stadium. Each row within the manager's file is data specific to one manager. A team may have one to many managers. The data fields in the matches file (EPL_matches.csv) are the following: Match number: Match number of the season Match day: Match day (1 to 38) Date: Date, in which match was played (In YYYY-MM-DD) format (Note: In excel, if you open the csv, you might see a different format, but do not worry. After importing in MySQL you will see the above format) Team 1: Home team Team 2: Away team Half time score team 1: Home team number of goals at the end of first half Half time score team 2: Away team number of goals at the end of first half Full time score team 1: Home team number of goals at the end of second half Full time score team 2: Away team number of goals at the end of second half The data fields in the stadium's file (EPL_stadiums.csv) are the following: Team: the team or the name of the Premier League team Venue: the name of the team's home stadium Each team has one home stadium. The data fields within the manager's file (EPL_managers.csv) are the following: Manager: the name of the manager for the team. Team: the name of the Premier league team Nationality: the nationality of the manager Status: Status of the manager (Active or Sacked) Query creation: When creating the SQL SELECT statements, make sure you do not design queries that are dependent on the data stored in the database or that only work for this specific instance of the schema. Also, please provide the question number in comments before the solution. Assignment Description 1. Design the schema for the Premier Football League. This should be done in the MySQL workbench. Create a pdf of the EER model. (10 points) 2. Create a database for your schema named premierlastnamefirstnameinitial. You may create the tables' schemas using the SQL CREATE command or using the EER modeling tool. (15 points) 3. Import the .csv file into your tables using the Import table wizard. Compose queries to answer the following questions:(5 points) 4. Generate a list of matches from match day 1, in which the home team won. The result should contain match number, home team and the away team name. (5 points) 5. Which teams had more than one manager in the season? The result should contain team name and the number of managers. (5 points) 6. Which manager/managers worked for more than one team? The result should contain manager name and number of teams. (5 points) 7. Generate a list of managers, teams and the number of goals scored by the team in the home stadium for each team for the season. Consider only the active managers. The list should be in descending order of number of goals. The result should contain the manager's name, the team name, and the number of goals. (5 points) 8. Generate a list consisting of a manager's name, total number of matches won by the manager in the season. The list should be in descending order of number of matches. Consider only the active managers. (5 points) 9. Determine the stadium, where the most number of goals were scored. The result should only contain the stadium name. (5 points) 10. Determine the number of matches ended as draw per team. The result should contain the team name and the number of matches. (5 points) Creating and manipulating a MySQL schema for soccer Domain Description The attached file contains information on the soccer (European football) players for the 2017/2018 Premier League. The Premier league is an English professional soccer league. The Premier League is the most watched sports league in the world, broadcasted in 212 territories to 643 million homes and a potential TV audience of 4.7 billion people. A game of soccer is played with 2 teams. There are 20 teams in the league. Each team plays 2 matches with each of the other 19 opponents, one in the home stadium and the other in an away stadium. Thus, in a season there are 380 matches in total and 38 matchdays. Each match is played in two halves, each half is 45 minutes. The team which scores more goals at the end of the second half wins, if both teams score the same number of goals, the match ends in a draw. Data File Description There are three different csv files. Each row within the match's file is data specific to one match. Each row within the stadium file is specific to each team's home stadium. Each team has only one home stadium. Each row within the manager's file is data specific to one manager. A team may have one to many managers. The data fields in the matches file (EPL_matches.csv) are the following: Match number: Match number of the season Match day: Match day (1 to 38) Date: Date, in which match was played (In YYYY-MM-DD) format (Note: In excel, if you open the csv, you might see a different format, but do not worry. After importing in MySQL you will see the above format) Team 1: Home team Team 2: Away team Half time score team 1: Home team number of goals at the end of first half Half time score team 2: Away team number of goals at the end of first half Full time score team 1: Home team number of goals at the end of second half Full time score team 2: Away team number of goals at the end of second half The data fields in the stadium's file (EPL_stadiums.csv) are the following: Team: the team or the name of the Premier League team Venue: the name of the team's home stadium Each team has one home stadium. The data fields within the manager's file (EPL_managers.csv) are the following: Manager: the name of the manager for the team. Team: the name of the Premier league team Nationality: the nationality of the manager Status: Status of the manager (Active or Sacked) Query creation: When creating the SQL SELECT statements, make sure you do not design queries that are dependent on the data stored in the database or that only work for this specific instance of the schema. Also, please provide the question number in comments before the solution. Assignment Description 1. Design the schema for the Premier Football League. This should be done in the MySQL workbench. Create a pdf of the EER model. (10 points) 2. Create a database for your schema named premierlastnamefirstnameinitial. You may create the tables' schemas using the SQL CREATE command or using the EER modeling tool. (15 points) 3. Import the .csv file into your tables using the Import table wizard. Compose queries to answer the following questions:(5 points) 4. Generate a list of matches from match day 1, in which the home team won. The result should contain match number, home team and the away team name. (5 points) 5. Which teams had more than one manager in the season? The result should contain team name and the number of managers. (5 points) 6. Which manager/managers worked for more than one team? The result should contain manager name and number of teams. (5 points) 7. Generate a list of managers, teams and the number of goals scored by the team in the home stadium for each team for the season. Consider only the active managers. The list should be in descending order of number of goals. The result should contain the manager's name, the team name, and the number of goals. (5 points) 8. Generate a list consisting of a manager's name, total number of matches won by the manager in the season. The list should be in descending order of number of matches. Consider only the active managers. (5 points) 9. Determine the stadium, where the most number of goals were scored. The result should only contain the stadium name. (5 points) 10. Determine the number of matches ended as draw per team. The result should contain the team name and the number of matches. (5 points)

Answer rating: 100% (QA)

Designing a MySQL schema for the Premier Football League and performing various database operations based on the provided data involves multiple steps Below Ill outline the steps and SQL queries for e... View the full answer