Individual Assignment 2 : 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.

Timelines and Expectations

Percentage Value of Task : 20% (95 marks)
Minimum time expectation : Preparation for this task will take approximately 20 hours

Learning Outcomes Assessed

The following course learning outcomes are assessed by completing this assessment :
K5. Describe relational algebra and its relationship to Structured Query Language (SQL).
S1. Interpret entity-relationship diagrams to implement a relational database.
S2. Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
S3. Use a query language for data manipulation.
A1. Design and implement a relational database using a database management system.
A2. Utilise a query language tools and techniques to obtain data and information from a database.

Assessment Details

Background

Major League Soccer (MLS)1 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 this Wikipedia page :
2019_Major_League_Soccer_season
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.

Requirements

Write the SQL code to create the table structures for the entities shown in Figures 1 and 2. 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.

Figure 1

Figure 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.
To Continue Click Here > ITECH2004 Assignment2 Data Modelling.pdf