Reference no: EM132375302

Data Modelling Individual Assignment - SQL

Overview - The purpose of this task is to develop student's skills in designing and implementing a relational database for a given case study.

Learning Outcomes Assessed - The following course learning outcomes are assessed by completing this assessment:

• Describe relational algebra and its relationship to Structured Query Language (SQL).
• Interpret entity-relationship diagrams to implement a relational database.
• Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
• Use a query language for data manipulation.
• Design and implement a relational database using a database management system.
• Utilise a query language tools and techniques to obtain data and information from a database.

Assessment Details -

Background - Major League Soccer (MLS) is a men's professional soccer league played in the United States. It is equivalent to European (and South American) football. The league comprises 24 teams - 21 in the U.S. and 3 in Canada.

There are many games played in each season by many teams. You can find the details of the team names and home locations listed at Wikipedia page.

You are part of a SPORTS PERFORMANCE team and you currently gather the following information:

• Video footage of each game - several frames per second, and recorded and stored in MP4 format.
• GPS track logs of each player for each game, including the three referee staff, and even the location of the ball
• Transcription of the "game play" by professional auditors, who have recorded all the important events during a game: when a pass was made, and between which people; whether there was a tackle; shots on goal; corners; fouls and any resultant cards; and so on.

You have been tasked to start analysing this data, and the following SQL commands are a mixture of preprocessing and the beginnings of some useful statistics.

The complete ERD is provided in Figure 1 (attached).

Requirements -

1. Write the SQL code to create the table structures for the entities shown in Figures 1 and 2 (attached). The structures should contain the attributes specified in the ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by the ERD.

2. The following descriptions and files (XLSX) provide a very small portion of the data that will be kept in the database. The data needs to be inserted into the database for testing purposes. Write the INSERT commands necessary to place the following data in the tables that were created in Question 1 (If required by your DBMS, be certain to save the rows permanently).

Table: EventsDataView

Refer to accompanying data file: "EventsDataView.xlsx".

• There are two teams listed in this example data - Team1 and Team2. Team2 rows are highlighted in red for ease of viewing.
• Type and Subtype refer to the different kinds of events that can occur in a game - for instance there are several ways a ball can be lost, one of which is by interception (see row 13); for instance there are several different kinds of set pieces, such as "Throw In's" (see row 16), and also "Corners" (not listed).
• Period is either 1 or 2, i.e. first half or second half of the game.
• "Start Video Frame" and "End Video Frame" refer to the segment of video footage where this Event is found. "Start Time [s]" and "End Time [s]" refer to the time period for this same Event.
• "From" refers to the first player involved in the Event, sometimes there is only a first player and not a second ("To"), but if the event is a PASS then there are two players listed.
• StartX,Y and EndX,Y refer to the geographic positions of the beginning and end of this Event.

Table: TracksDataView

Refer to accompanying data file: "TracksDataView.xlsx".

• Period is either 1 or 2, i.e. first half or second half of the game.
• Time is seconds from beginning of game.
• Ball-X,Y are the coordinates of the ball.
• Ref1-X,Y are the coordinates of Referee number one. There are usually three referees but we have included just one.
• Player1(T1) refers to Player one from Team one.
• Player1(T1)-X,Y are the coordinates of this player.
• There are two players included for each team (T1 & T2). Team T2 players have been highlighted in red for ease of viewing. There are usually 18-players in a game-day squad, with not all getting time on the pitch. Additional players can be added as desired - if you want to leave the table as just two players versus two players, that is fine.

For Questions 3-14, use the tables that were created in Question 1 and the data that was loaded into those tables in Question 2.

3. Write the SQL command to change the season year for season number 3 to 2017.

4. Write SQL command(s) to create a derived attribute that represents a new surrogate primary key for the Game table. It should be comprised of character data, created from the location and date attributes.

5. Write SQL command(s) to create a new table called EventTypes that contains a single listing of all Event types from EventsDataView.

6. Write SQL command(s) to create a new table for player statistics. The table will contain the Players name or ID, and include a derived attribute which represents the average position played on the pitch. The attribute should accept null values.

7. Write SQL command(s) alter the TracksDataView table to include a derived attribute named DistanceFromBall which represents the Euclidean distance between each player and the ball.. The attribute should accept null values.

8. Alter the Game table to include attributes that represent the final score of the game, and update with some simulated values.

9. Alter the EventsDataView and TracksDataView tables to include an attribute named PitchArea to store character data up to ten characters long. The attribute should have a constraint to enforce the domain ("Area1," "Area2,", "Area3" and "Area4") and have a default value of "Unknown."

10. Write SQL command(s) to cerate a new table called TrackLogs and extract all tracklog data into this. The attributes will be the new primary key from Q2, and also Date, PlayerID, Xposition, Yposition, Time.

11. Write SQL command(s) to see if any particular players have been sent off (Type="CARD", Subtype="RED") more than once by a particular referee.

12. Alter the player statistics table to include attributes named "YellowCards" and "RedCards" which represent the count of these cards issued to a player. The attribute should not accept null values, and it should have a default value of 0.

13. Create a new table to contain the following statistics about each game: the number of Cards issued; the number of passes; and, the number of scoring attempts.

14. For each scoring event calculate derived attributes which represent the distance of the scorer from the goal line, and the distance from them to the nearest opponent.

Attachment:- Data Modelling Assignment File.rar