Data Modelling Individual Assignment 2: SQL Database Implementation

This assignment provides a comprehensive solution to a data modelling task, focusing on SQL implementation for a Major League Soccer (MLS) database. The student demonstrates proficiency in designing and implementing a relational database, including creating tables, inserting and updating data, and using SQL queries to manipulate the data. The assignment covers various SQL commands, such as CREATE TABLE, INSERT INTO, UPDATE, and SELECT statements, as well as the use of surrogate keys, altering tables, and joining tables to create relationships. The solution also addresses specific requirements outlined in the assignment brief, such as updating data, creating surrogate keys, cloning tables, adding and removing columns, and creating a statistics table. The assignment showcases the student's ability to interpret entity-relationship diagrams and translate them into a functional database using a relational database management system (MySQL). The student utilizes both Data Definition Language (DDL) and Data Manipulation Language (DML) to accomplish the tasks, demonstrating a solid understanding of SQL principles and database design concepts.

Student Name
Data Modelling
Tutor
Tutor name

Introduction
To create well-working database design, we first need to do data
modeling. Data modeling is the process of analyzing objects and
their relationship with other objects. To model data we can use
UML diagrams such as class diagram, Entity-relationship Diagram,
use-case diagrams, etc.The entity-relationship diagram shows how
data relate and interact with each other. It consists of tables
connected together showing how they compare with each other.
After completing designing the ERD diagram the next step is to
develop a relational schema of your database. The relational
schema is a tabular representation of your tables. It contains
all the fields and the constraints of your entities. Using
relational schema, we can jump into development process of the
database.
SQL language.
The assignment was done using the SQL language in a relational
database management system. According to (Virender, 2018) SQL is
the standard language for data manipulation in a DBMS. In simple
words, it's used to talk to the data in a DBMS. There are two
types of SQL languages that were used in accomplishing this
assignment.
 Data definition language (DDL) used for creating the tables
in the database.
 Data manipulation language(DML) used for inserting,
selecting, updating, and deleting data.
The first step was creating a database using a relational
database management system. The database management system is a
software used in running the SQL queries.
Mysql was the suitable DBMS for this assignment because it is
free software that is downloadable on the internet. Mysql comes
with a shell that allows users to write and query database.
Creating TABLES
The tables were created using the CREATE TABLE statement
followed by the table name. The table is made up of different
columns where each column belongs to a datatype.
The syntax for creating a table

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);
When creating tables is advisable to start with a table that
doesn’t have a foreign key. In our case we began by creating the
seasonal table followed by the game table. The seasonal table
doesn’t have a reference table.
Inserting data to tables.
The insert statement was used in adding data to the tables. TO
insert data we need to start entering data for the table that is
not referenced in any other table in order to avoid insertion
error.
Below is the syntax for inserting the data
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
The statement can be used to insert multiples data to the table
by defining multiple values.
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),
(value1, value2, value3, ...), (value1, value2, value3, ...);
solutions
For the first solution we will use the update statement to
update the year from 200017 to 2017.
MariaDB [mls_league]> select * from season;
+--------+----------+
| sea_id | sea_year |
+--------+----------+
| 1 | 2015 |
| 2 | 2016 |
| 3 | 200017 |
| 4 | 2018 |

| 5 | 2019 |
+--------+----------+
The above is the season table before updating. To change the
year below query will be used.
update season
set sea_year="2017" where
sea_id='3';
After updating, the updated result will be
A surrogate key is a unique system-generated key. The key is
mainly created by combining two fields to create a single field
(Ramez & Shamkant, 2018). To create the key we will first merge
the date and the location using some of their characters. I have
used the first 2 letters for location and the year for date. We
will use the concat() function to merge the two and the
substring function to select a length of character from game
location field. To choose a specific character we will use the
year function to select the year of the game.
Below is the code for creating a surrogate key.
SELECT DISTINCT
concat(substring(game_location,1,2),year(game_date)) AS
Game_Date_Key,
GM.game_id,
GM.ref_id,
GM.sea_id,
GM.game_location,

GM.game_date,
GM.game_conference
FROM game GM
Creating a table and populating it with data from another table
is like cloning the table. To achieve that we need to create an
empty table based o definition of the original table (Raghu &
Johannes, 2012). We will use create table statement followed by
select statement specifying the column to clone.
The create table EventTypes select E_type from eventdataview;
statement will be written followed by select * FROM EventTypes;
statement to confirm the clone was made successfully

To alter a table is adding or removing columns from a table. We
will add a new column on the trackviewdata using the ALTER TABLE
statement. The statement allows you to define the action to
perform which in our case is ADD column.
To the game table, we will add two columns to represent team 1
scores and team 2 scores. We will then update the table to add
new data. The columns will accept integer values only and cannot
be null. We will update the data using the update statement. Eg
update game set team1_scores='3', team2_scores='1' where
game_id='G3';
For question 9 we will alter the tables by adding a new column
pitch area, the column will be of an enum data type to allow the
column to have a constraint of four values. The unknown value
will be added to the enum to act as a default value for the
column.
ALTER TABLE eventdataview ADD pitch_area
ENUM('Area1','Area2','area3','area4','unknown') DEFAULT
'unknown';
ALTER TABLE TracksDataView ADD pitch_area
ENUM('Area1','Area2','area3','area4','unknown') DEFAULT
'unknown';
Question ten we use the joint to create relationship between
different tables.

select E_from, ref_name from referee R, eventDataview E, game G
where E.game_id = G.game_id and G.ref_id=R.ref_id and
E.E_subtype='red';
+-------------+---------------+
| E_from | ref_name |
+-------------+---------------+
| Player6(T1) | David Barkham |
+-------------+---------------+
Question 12 alters the player_stats table by adding two columns
Creating a statistics table

The last question we will start by adding some columns to the
EventDataView table. Then we will update the columns for the
value that is shot only.
References
Raghu, R., & Johannes, G. (2012). Database Management System.
McGraw-Hill.
Ramez, E., & Shamkant, B. N. (2018, FEB 19). FUNDAMENTALS OF
DATABASE SYSTEM (6th ed.). Boston: Addison-Wesley.
Virender, S. (2018, February 16). Database Design Using Entity-
Relationship Diagrams. Virender Singh.