USE MYSQL TO WRITE CODE FOR DATABASE MANAGEMENT
Scenario: The island Football Federation wants to model its football teams, the games they play and the players in each team. Each team has an automated ID, main stadium, name and city to which they belong. Players belong to only one team. Each player has a player number comprising of their initials plus a number, name (first,and last), position (forward, midfield, goalkeeper and defender), skill level, dob, start year and shirt number. Teams play matches, in each match there is a host team and a guest team. For each match we need to keep track of the date of the match, final result (host result, guest result), duration in minutes and the players who participated in the match. For each player who participated in the match, how many goals scored, yellow cards and red cards received needs to be stored. Table 2 - Teams Table 3 - Matches Played Task 1 - Implement the ERD shown above. (45 marks) a. Write create and use statements to create the database footBallFed + ID\# b. Create Table Statements [12] (Ensure auto number is used for the Teams table) i. Using appropriate Foreign Keys ii. Ensure that the mandatory participation is implemented iii. Using correct data types [2] c. All skill levels must fall between 1 and 5 . [3] d. Ensure that the duration field has the minimum 90 minutes as default. [2] e. Delete the field from the Team table and add stadium [3] f. Ensure that stadium is unique [2] g. Delete the name field from the Player table and add first_name and last_name fields. [3] h. Remove the fouls field and shirt number from the relevant table [2] i. First and last name fields cannot accept null values. [2] j. Ensure that the salary field can accept 12 digits of precision with a scale of 1 [3] k. Position should only accept values (forward, defender, midfield and goalkeeper) [3] Task 2 -Demonstrate interacting with data with one SQL statement (55 marks) a. Develop appropriate full and partial insert statements for each table [12] (Information in Tables 1, 2 and 3should be inserted as well as other information given) b. Add 2 additional records for all tables except the team table [3] c. The match that occurred on January 23, 2023 Virgil Dike got a yellow card by error. Modify the respective table to correct this. [4] d. Give the Full name, position, salary of all players earning less than US $7000 e. Delete one of the records entered. f. Display the host team id, guest team id, date, home team score and guest team score for All matches where the home team won. [4] g. Sort the results in d ) by skill level in ascending order with last name in descending order. [3] h. Display all players who has a ' d ' as the second to last letter in their last name [5] i. Display players fullname, salary in JMDS (assume rate), income tax (25% of salaries in excess of 1.5 million, and net income for players that belong to Man United or Carsenal. [5] j. Calculate the average salary for each position played (Use one statement) [3] k. How many yellow cards and red cards were received by each team
