Question
PLEASE I NEED HELP FOR THIS ASSIGNMENT AND USE XAMPP In this homework, you will get started on how to create and use a relational
PLEASE I NEED HELP FOR THIS ASSIGNMENT AND USE XAMPP
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___
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 players 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.
COUNTRYCountry_NamePopulationNo_of_Worldcup_wonManagerPLAYERSPlayer_idNameFnameLnameDOBCountryHeightClubPositionCaps_for_countryIs_captainMATCH_RESULTSMatch_idDateStart_timeTeam1Team2Team1_scoreTeam2_scoreStadiumHost_cityPLAYER_CARDPlayer_idNo_of_Yellow_cardsNo_of_Red_cardsPLAYER_ASSISTS_GOALSPlayer_idNo_of_MatchesGoalsAssistsMinutes_Played
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
INPUT_Data:
country.csv
player.csv
player_assists_goals.csv
playercards.csv
match_results.csv
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Multidimensional Array Data Management In Databases
Authors: Florin Rusu
1st Edition
1638281483, 978-1638281481
