In this homework, you will get started on how to create and use a relational DBMS. You can either use the MySQL system. You will write SQL (Standard Query Language) to create the relations, insert rows into the relations.
1. Create a database with name SOCCER_< team1_last_name1>_< team2_last_name2>.
2. Create the following tables for your database (World cup 2014 information) whose schema diagram is specified in Page 3 of this document: COUNTRY, PLAYER, MATCH_RESULTS, PLAYER_ASSISTS_GOALS, PLAYER_CARDS. Write your Data definition language Query to create the relations (tables). Specify appropriate key and referential integrity constraints in your data definition language. The data types for each attribute are given after the schema diagram.
3. Write Insert query to load the records to the tables from input file. You can write each insert query manually or you can write a program that will generate the query by reading inputs from file. In the second case, save the file as .sql extension, and import the file into your MySQL service to run the sql.
4. Write down the queries in SQL for the English queries that are listed later (Page 2). Execute each query and display its results. Save the query and results in a text file.
5. Execute 3 more Insert commands in SQL that attempt to insert 3 more records, such that the records violate the integrity constraints. Make each of the 3 records violate a different type of integrity constraint. Save your commands in a text file.
6. Execute a command in SQL to Delete a record that violates a referential integrity constraint. Save your commands in a text file.
7. Repeat 5, but Insert three new records that do not violate any integrity constraints. Save your commands in a text file.
For item 1,2 and 3: export your database. This will generate a file with .sql extension. For item 4,5,6 and 7: save all the commands and results in a text file.
For item 4)
Apply the following queries in SQL and display the result of each query
1. Retrieve the name, club, and position for the players whose country is 'USA'
2. Retrieve the names of countries participating in the 2014 world cup (this database) that have won the world cup at least once.
3. Retrieve the names of countries participating in the 2014 world cup (this database) that have never won the world cup.
4. Retrieve the name and country of the player with the most yellow cards in the 2014 world cup.
5. For each Host city, retrieve the HostCity and the total number of games played in that city.
6. For each country, retrieve the country name and the number of games they played as Team1 in the MATCH_RESULTS table, and the total goals scored (SUM of Team1_score) and the goals against (SUM of Team2_score).
7. For each country, retrieve the country name and the number of games they played as Team2 in the MATCH_RESULTS table, and the total goals scored (SUM of Team12_score) and the goals against (SUM of Team1_score).
8. Find all the matches played with country 'Brazil' as Team1 or Team2.
9. Retrieve the names of the players who have scored at least one goal, the player's country, and the number of goals each player scored. Order the result by number of goals scored in descending order.
10. Repeat 9. but only for the players who have more than 2 goals.
Schema: see image.
COUNTRY table attribute data types:
Country_Name Varchar(20),
Population decimal(10,2),
No_of_Worldcup_won int,
Manager varchar (20),
PLAYERS table attribute data types:
Player_id int,
Name varchar (40),
Fname varchar (20),
Lname varchar (35),
DOB date,
Country varachar(20),
Height(cms) int,
Club varchar(30),
Position varchar(10),
Caps_for_Country int,
IS_CAPTAIN int.
MATCH_RESULTS table attribute data types:
Match_id int,
Date_of_Match date,
Start_Time_Of_Match time,
Team1 varchar(25),
Team2 varchar(25),
Team1_score int,
Team2_score int,
Stadium_Name varchar(35),
Host_City varchar(20).
PLAYER_CARDS table attribute data types:
Player_id int,
Yellow_Cards int,
Red_Cards int.
PLAYER_ASSISTS_GOALS table attribute data types:
Player_id int,
No_of_Matches int,
Goals int,
Assists int,
Minutes_Played int.