ITECH 2004 DATA MODELLING SQL Individual Assignment help

Solution.pdf

ITECH 2004 DATA MODELLING

 

CRICOS Provider No. 00103D itech 2004 ass 2 SQL 2018-20.docx Page 1 of 12

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%

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:

 

1 https://en.wikipedia.org/wiki/Major_League_Soccer

 

https://en.wikipedia.org/wiki/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

1. 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.

 

 

 

 

1. 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.

Table: VideoDataView

 

VIDEODATAVIEW
VID_ID	GAME_ID	VID_FirstHalf	VID_SecondHalf
1	G1	“C:\dropbox\g1-fh.mp4”	“C:\dropbox\g1-sh.mp4”
2	G2	“C:\dropbox\g2-fh.mp4”	“C:\dropbox\g2-sh.mp4”
3	G3	“C:\dropbox\g3-fh.mp4”	“C:\dropbox\g3-sh.mp4”
4	G4	“C:\dropbox\g4-fh.mp4”	“C:\dropbox\g4-sh.mp4”
5	G5	“C:\dropbox\g5-fh.mp4”	“C:\dropbox\g5-sh.mp4”

 

Table:   Game

 

Details of team names and the location of their home grounds can be found here:

https://en.wikipedia.org/wiki/2019_Major_League_Soccer_season

GAME_ID	SEA_ID	GAME_Location	GAME_Date	GAME_Conference
G1	3	Mapfre Stadium	13/05/2017	Eastern
G2	3	Audi Field	20/05/2017	Eastern
G3	3	Rio Tinto Stadium	13/05/2017	Western
G4	3	Children's Mercy Park	20/05/2017	Western
G5	3	Red Bull Arena	27/05/2017	Eastern
G6	3	Saputo Stadium	03/06/2017	Eastern
G7	3	Talen Energy Stadium	10/06/2017	Eastern
G8	3	Rio Tinto Stadium	27/05/2017	Western
G9	3	Children's Mercy Park	03/06/2017	Western

 

Table:   Team

 

TEAM
TEAM _ID	TEAM _Name	TEAM _Location
T1	Columbus Crew SC	Mapfre Stadium
T2	D.C. United	Audi Field
T3	Real Salt Lake	Rio Tinto Stadium
T4	Sporting Kansas City	Children's Mercy Park
T5	New York Red Bulls	Red Bull Arena
T6	Montreal Impact	Saputo Stadium
T7	Philadelphia Union	Talen Energy Stadium

 

Table:   Season

SEASON
SEA _ID	SEA_YEAR
1	2015
2	2016
3	200017
4	2018
5	2019
6	2020

 

Table:   Player

 

PLAYER
PLAY _ID	PLAY _Name	PLAY _DateOfBirth
P1	Joe Jordan	20/05/1998
P2	Martin Buchan	04/01/1997
P3	Stevie Coppell	17/12/1999
P4	Lou Macari	08/10/1999
P5	Jimmy Greenhoff	25/04/1995
P6	Norman Whiteside	13/09/1998
P7	Eric Cantona	10/02/1995

 

Table:   Referee

 

REFEREE
REF _ID	REF _Name	REF _DateOfBirth
R1	David Barkham	21/03/1988
R2	Ron Arkinsaw	09/09/1987
R3	Paolo Schools	15/10/1989
R4	Ray Keen	02/12/1989
R5	Teddy Bear	29/04/1985
R6	Ollie Goon	12/04/1988
R7	Rudie Nistlebrush	08/07/1985

 

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.

 

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

 

1. 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.

 

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

 

1. 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.

 

1. 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.

 

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

 

1. 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.”

 

1. 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.

 

1. 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.

 

1. 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.

 

1. 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.

 

1. 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.