Free Sample
ITECH 2004 DATA MODELLING SQL Individual Assignment help
Solution.pdfITECH 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.
- 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.
- Write the SQL command to change the season year for season number 3 to 2017.
- 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.
- Write SQL command(s) to create a new table called EventTypes that contains a single listing of all Event types from EventsDataView.
- 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.
- 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.
- Alter the Game table to include attributes that represent the final score of the game, and update with some simulated values.
- 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.”
- 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.
- 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.
- 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.
- 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.
- 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.

Get Assignment Help Now...!
Subjects We Offer
- Engineering Assignment Help
- MATLAB Assignment Help
- Mechanical Engineering Assignment Help
- Civil Engineering Assignment Help
- Computer Science Assignment Help
- Electrical Engineering Assignment Help
- Electronics Assignment Help
- Economics Assignment Help
- Management Assignment Help
- Do My Assignment
- Cheap Assignment Help
- Programming Assignment Help
- Law Assignment Help
- Assignment Provider
- Finance Assignment Help
- Python Assignment help
- Healthcare Management Assignment Help
- Computer Network Assignment Help
- History Assignment Help
- Industrial Engineering Homework Help
- IT Management Assignment Help
- Nursing Assignment Help
- Operating System Assignment Help
- Statistics Assignment Help
- Material Science Assignment Help
- Mechanical Engineering Homework Help
- University Assignment Help
- Agriculture Engineering Homework Help
- ATHE Courses Assignment Help
- Capital Budgeting Assignment Help
- BTEC Assignment Help
- HND Assignment Help
- Material Science Assignment Help
- Psychology Assignment Help
- Resit Assignment Help
- Computer Architecture Assignment Help
- Data Structure Assignment Help
- Database Assignment Help
- PHP Assignment Help