Reference no: EM132533556
ITECH2004 Data Modelling - Federation University Australia
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.
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.
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).
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.rar