Final Group 2 - Database Design and SQL Implementation Report | Môn Database - Trường Đại học Quốc tế, Đại học Quốc gia Thành phố Hồ Chí Minh

lOMoAR cPSD| 23136115 Downloaded by
26_0082 Nguy?n Nh?t Giang Linh (nguyennhutgianglinh99@gmail.com) lOMoAR cPSD| 23136115 TABLE OF CONTENT CONTENT PAGE 1. Abstract 2 2. Introduction 2 2.1. About the orgarnization 2 2.2. Requirement analysis 3 3. Body 3
3.1. Analyzing and design the database 3 3.1.2. E-R diagram 3 3.1.3. Relational schema 5 3.2. SQL Server code 5 3.3. Query assumption 18 4. 20 Conclusion DISTRIBUTION Name – ID Assignment Progress Vũ Phương Anh – 20070676 SQL Server code 100% Vũ Thanh Hải – 20070705 SQL Server code 100%
Edit report (Outline, Intro, Conclu) E-
R diagram and Relational schema Lê Chi Mai – 20070746 100%
Đỗ Thu Phương – 20070772 100% Query assumption Slide 1. ABSTRACT
Player data management system is a database management system to manage both player
parameters and influence on football clubs, helping Onsport to reduce the work of
searching and analyzing data. players to include in their simulation game system. The main
purpose of this system is to apply technology to support Onsport and reduce human
resources in searching and automating the calculation of the value of players. 2. INTRODUCTION
2.1. About the organization:
• Fantasy sports are online gaming platforms where participants draft and manage
virtual teams of real professional sports players. Based on the performance of the
players in the real world, players are allotted points in the fantasy sports platform
every match. OnSports is a fantasy sports platform that has fantasy leagues for lOMoAR cPSD| 23136115
many different sports and has witnessed an increasing number of participants
globally over the past 5 years.
• Mission: The objective is to create the best possible team with a fixed budget to
score maximum fantasy points, and users compete against each other over an
entire sports league or season. Some of these fantasy sports require actual financial
investments for participation, with the chances of winning monetary rewards as
well as free matchday tickets on a periodic basis.
• Statistical: The fantasy sports market has seen tremendous growth over the past
few years, with a valuation of \\$18.6 billion in 2019. The football (soccer)
segment led in terms of market share in 2019, with over 8 million participants
worldwide, and is expected to retain its dominance over the next couple of years.
• Vision: With an increase in smartphone usage and availability of fantasy sports
apps, this market is expected to witness a globe surge and reach a \\$48.6 billion valuation by 2027.
• The main purpose of the project: With the new English Premier League season
about to start, they have collected data from the past season and want to analyze
it to determine the price of each player for the start of the new season. We
conducted a cluster analysis to identify players with different potential for each
player based on last season's performance. This will help Onsport understand
patterns in player performance and imagined returns, and determine the exact price
to be set for each player during the upcoming football season.
2.2. Requirement analysis:
• The following are the types of information and parameters of the players playing
in the EPL season 20/21, we will rely on these parameters to create query and
analysis subdivisions for Onsport to evaluate players in the most comprehensive way.
• Player Name: This is the name that includes the full name or registration name of the players.
• Club: Is a professional football club that our players statistically play for.
• Position: This is a required parameter, is the position that the players are registered
to play in that season, each player must register a playing position.
• Goal Scored: Total number of goals scored by the player in the previous season.
• Assists: Number of passes by player leading to a goal last season.
• Total Points: The total number of fantasy points the player scored in the previous
season. Will be calculated based on the parameters considered.
• Minutes: Number of minutes played by the player in the previous season.
• Goals Conceded: Number of goals conceded by the player in the previous season.
• Creativity: A score, computed using a range of stats, that assesses player
performance in terms of producing goalscoring opportunities for other players.
• Influence: A score, computed using a range of stats, that evaluates a player's
impact on a match, taking into account actions that could directly or indirectly affect the match outcome. lOMoAR cPSD| 23136115
• Threat: A score, computed using a range of stats, that gauges players who are most likely to score goals.
• Bonus: Total bonus points received. The three best performing players in each
match receive additional bonus points based on a score computed using a range of
stats. 3 points are awarded to the highest scoring player, 2 to the second best, and 1 to the third.
• Clean_Sheets: Number of matches without conceding a goal in the previous season. 3. BODY
3.1. Analyzing and design the database 3.1.1. E-R diagram Picture 1: E-R diagram
• Entity relationship diagram can be considered as the design drawing of the
database. ERD provides visualization for database design, and thus it plays the following roles:
o Helps define information system requirements across the organization and helps
users plan how to organize data. It supports preparation before starting to build
the tables. o The ERD diagram can act as a document to make others
understand the core of the database.
o The ERD diagram reflects the logical structure of the database for users to understand.
o Once the relational database is deployed, the ERD can still serve as a point of
reference, should the debug or business process need to be re-established later. • About Entities: lOMoAR cPSD| 23136115
o Club: The Club table includes the names of the clubs and the IDs assigned to
those clubs. Club plays the role of managing the players who play for that club,
in addition to categorizing them to get information from the players' rankings.
o Club_Player: The Club_Player table shows the arrangement of players, the
category shows which clubs they play for.
o Position:The Position table plays the role of displaying information about the
positions of the players and the ID specified to distinguish those positions. o
Position_Player:The Positon_Player table is used to sort the players who play
which position corresponds to the ID of that position.
o Player: The Player table includes the names of the players and the IDs that
distinguish the respective players, to sort by the position and club the player plays for.
o Player_Objective The Player_Objective table provides statistics on actual
effects and impacts, showing prestige and challenges such as the number of
goals lost in each game a player plays and matches kept clean sheets.
o Player_Goal: The Player_Goal table includes goals scored and assists, it is
primarily a representation of more personal stats and directly contributes to the
club's full season, in fact This parameter is also considered the best individual award for that season.
o Player_Influence: The Player_Influence table includes certain player influences
such as minutes played which indicates a player's confidence, physical fitness
and fit with the coach's tactics, and an index that represents the direct impact of
the player. and indirectly of that player up the match. o Player_Creation: The
Player_Creation table includes scoring performance ratings - direct influence
and reputation and a statistical rating of the scoring chances that player creates
for other players. Of course, if you create a lot of opportunities and the
corresponding player enjoys a pass that doesn't score, it won't affect the goals
and assists, but still shows the creativity and vision of the player who creates chances. festival.
o Player_Rank: The Player_Rank table will arrange the rankings of the players in
the most realistic and pragmatic way based on the parameters and bonus points
to sum up the final total score.
3.1.2. Relational Schema
The Relational Schema is converted from the ERD, showing the elements in the
table corresponding to the entities, helping table creators in SQL server have a
more specific view of the table implementation. lOMoAR cPSD| 23136115
Picture 2: Relational Schema 3.2. SQL Server code
create database FantasySportApp; use FantasySportApp; /*CREATE TABLE PLAYER*/ /*CREATE TABLE create table player PLAYER_INFLUENCES*/ (
create table player_influences ID int PRIMARY KEY, ( Playername varchar(30), ID int, ) Minutes int, insert into player Influence float, (Playername,ID) values ('Alex ) Runnarsson',1); Insert into player_influences insert into player
(Minutes,Influence,ID) values (15,16.6,2);
(Playername,ID) values ('Alexandre Insert into player_influences
Lacazette',2); insert into player (Minutes,Influence,ID) values (1916,602.4,3); lOMoAR cPSD| 23136115
(Playername,ID) values ('Bernd Leno',3); Insert into player_influences insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Bukayo Saka',4); (3131,702.2,4); insert into player Insert into player_influences
(Playername,ID) values ('Calum (Minutes,Influence,ID) values Chambers',5); (2554,493.0,5); insert into player Insert into player_influences
(Playername,ID) values ('Ahmed (Minutes,Influence,ID) values Mohamady',6); (751,171.8,6); insert into player Insert into player_influences
(Playername,ID) values ('Anwar Ghazi',7); insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Bertand Traore',8); (833,170.4,32); insert into player Insert into player_influences
(Playername,ID) values ('Emiliano (Minutes,Influence,ID) values Martinez',9); (1604,500.4,33); insert into player Insert into player_influences
(Playername,ID) values ('Keinan Davis',10); (Minutes,Influence,ID) values insert into player (2315,528.4,34);
(Playername,ID) values ('Aaron Insert into player_influences Conolly',11); (Minutes,Influence,ID) values insert into player (3420,1067.2,38);
(Playername,ID) values ('Adam Lallana',12); Insert into player_influences Insert into player (Minutes,Influence,ID) values (Playername,ID) values ('Adam (265,77.2,46); Webster',13); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Danny (783,104.2,58); Welbeck',14); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Jason Steele',15); (1584,255.2,59); Insert into player Insert into player_influences
(Playername,ID) values ('Anthony Driscoll (Minutes,Influence,ID) values Glennon',16); (2594,505.4,60); Insert into player Insert into player_influences
(Playername,ID) values ('Ashley Barnes',17); (Minutes,Influence,ID) values Insert into player (1541,319.8,67);
(Playername,ID) values ('Ashley Insert into player_influences Westwood',18);
(Minutes,Influence,ID) values (0,0.0,70); Insert into player Insert into player_influences
(Playername,ID) values ('Bailey Peacock
(Minutes,Influence,ID) values (0,0.0,86); Farrell',19); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Ben Mee',20); (1324,153.8,87); Insert into player Insert into player_influences
(Playername,ID) values ('Andreas (Minutes,Influence,ID) values Christense',21); (3410,559.2,88); lOMoAR cPSD| 23136115 Insert into player Insert into player_influences
(Playername,ID) values ('Antonio (Minutes,Influence,ID) values Rudiger',22); (360,82.6,89); Insert into player_influences lOMoAR cPSD| 23136115 lOMoAR cPSD| 23136115 Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Faustino (2693,691.0,90); Anjorin',23); Insert into player_influences Insert into player (Minutes,Influence,ID) values (Playername,ID) values ('Kepa (1371,293.4,113); Arizabalag',24); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Olivier (1710,368.6,114); Giroud',25); Insert into player_influences Insert into player
(Minutes,Influence,ID) values (0,0.0,122);
(Playername,ID) values ('Andros Insert into player_influences Townsend',26); (Minutes,Influence,ID) values Insert into player (585,113.6,128);
(Playername,ID) values ('Cheikhou Insert into player_influences Kouyate',27); (Minutes,Influence,ID) values Insert into player (740,161.4,134);
(Playername,ID) values ('Christian Insert into player_influences Benteke',28); (Minutes,Influence,ID) values Insert into player (2250,511.4,141);
(Playername,ID) values ('James Insert into player_influences Tomkins',29); (Minutes,Influence,ID) values Insert into player (3121,574.8,142);
(Playername,ID) values ('Stephen Insert into player_influences Henderson',30); (Minutes,Influence,ID) values Insert into player (1808,512.6,143);
(Playername,ID) values ('Abdoulaye Insert into player_influences Doucoure',31); (Minutes,Influence,ID) values Insert into player (551,101.2,151);
(Playername,ID) values ('Ben Godfrey',32); Insert into player_influences Insert into player
(Minutes,Influence,ID) values (0,0.0,167);
(Playername,ID) values ('Bernard Caldeira Insert into player_influences Duarte',33); (Minutes,Influence,ID) values Insert into player (2443,433.8,171);
(Playername,ID) values ('Joao Virginia',34); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Jonjoe Kenny',35); (2683,610.4,176); Insert into player Insert into player_influences
(Playername,ID) values ('Moise Ken',36); (Minutes,Influence,ID) values Insert into player (447,104.8,177); (Playername,ID) values ('Adam Insert into player_influences Forshaw',37); (Minutes,Influence,ID) values Insert into player (47,25.0,183);
(Playername,ID) values ('Diego Insert into player_influences Llorente',38); (Minutes,Influence,ID) values Insert into player (78,11.0,184);
(Playername,ID) values ('Ezgjan Insert into player_influences lOMoAR cPSD| 23136115 Alioski',39);
(Minutes,Influence,ID) values (13,0.0,190); Insert into player Insert into player_influences (Playername,ID) values
('Francisco (Minutes,Influence,ID) values (0,0.0,198); Casilla',40); Insert into player_influences lOMoAR cPSD| 23136115 lOMoAR cPSD| 23136115 Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Niall Huggins',41); (1203,293.0,199); Insert into player Insert into player_influences
(Playername,ID) values ('Ayoze Perez',42); (Minutes,Influence,ID) values Insert into player (2456,477.8,200);
(Playername,ID) values ('Calgar Insert into player_influences Soyuncu',43); (Minutes,Influence,ID) values Insert into player (270,68.0,201);
(Playername,ID) values ('Jamie Vardy',44); Insert into player_influences Insert into player
(Minutes,Influence,ID) values (37,2.6,218);
(Playername,ID) values ('JonnyEvans',45); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Kasper (1322,255.6,227); Schmeichel',46); Insert into player_influences Insert into player (Minutes,Influence,ID) values (Playername,ID) values ('Marc (1816,403.6,228); Albrighton',47); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Nampalys (2838,819.2,240); Mendy',48); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Alex Oxlade (2473,532.4,241); Chamberlai',49); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Alisson (3420,769.0,242); Becker',50); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Andrew (1735,412.6,245); Robertson',51); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Divock Origi',52); (1452,153.0,246); Insert into player Insert into player_influences
(Playername,ID) values ('Joel Matip',53); (Minutes,Influence,ID) values Insert into player (243,70.8,257);
(Playername,ID) values ('Aymeric Insert into player_influences Laporte',54); (Minutes,Influence,ID) values Insert into player (2970,776.4,258);
(Playername,ID) values ('Benjamin Insert into player_influences Mendy',55); (Minutes,Influence,ID) values Insert into player (3384,754.4,259);
(Playername,ID) values ('Bernardo Insert into player_influences Silva',56); (Minutes,Influence,ID) values Insert into player (180,17.8,264);
(Playername,ID) values ('Ederson Insert into player_influences Moares',57); (Minutes,Influence,ID) values Insert into player (690,179.0,268); lOMoAR cPSD| 23136115
(Playername,ID) values ('Gabriel Insert into player_influences Fernandode Jesus',58); (Minutes,Influence,ID) values Insert into player (1343,233.4,283);
(Playername,ID) values ('Allan Saint Insert into player_influences lOMoAR cPSD| 23136115 lOMoAR cPSD| 23136115 Maximin',59); (Minutes,Influence,ID) values Insert into player (951,179.8,284);
(Playername,ID) values ('Andy Carroll',60); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Ciaran Clark',61); (2063,399.6,285); Insert into player Insert into player_influences
(Playername,ID) values ('Joseph (Minutes,Influence,ID) values Willock',62); (3240,586.0,286); Insert into player Insert into player_influences
(Playername,ID) values ('Karl Darlow',63); (Minutes,Influence,ID) values Insert into player (2056,501.0,290);
(Playername,ID) values ('Alex Carthy',64); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Caleb Watts',65); (1554,376.0,338); Insert into player Insert into player_influences
(Playername,ID) values ('Che Adams',66); (Minutes,Influence,ID) values Insert into player (380,64.0,339);
(Playername,ID) values ('Daniel Insert into player_influences Lundulu',67); (Minutes,Influence,ID) values Insert into player (1890,402.6,341);
(Playername,ID) values ('Jack Stephens',68); Insert into player_influences Insert into player (Minutes,Influence,ID) values
(Playername,ID) values ('Bamidele Alli',69); (1210,436.6,356); Insert into player Insert into player_influences
(Playername,ID) values ('Ben Davies',70); (Minutes,Influence,ID) values Insert into player (2250,682.4,357);
(Playername,ID) values ('Carlos Vini Alves Insert into player_influences Morais',71); (Minutes,Influence,ID) values Insert into player (2700,741.2,367);
(Playername,ID) values ('Hugo Lloris',72); Insert into player_influences Insert into player
(Minutes,Influence,ID) values (36,5.4,368);
(Playername,ID) values ('Japhet Insert into player_influences Tanganga',73); (Minutes,Influence,ID) values Insert into player (2662,581.8,369);
(Playername,ID) values ('Harvey',74); Insert into player_influences (Minutes,Influence,ID) values /*CREATE TABLE (102,3.2,370); PLAYER_OBJECTIVE*/ Insert into player_influences create table player_objective (Minutes,Influence,ID) values ( (1537,351.0,374); ID int, Insert into player_influences Goal_Conceded int, (Minutes,Influence,ID) values Clean_Sheets int, (613,103.4,394); ) Insert into player_influences Insert into player_objective (Minutes,Influence,ID) values
(Goal_Conceded,Clean_Sheets,ID) values (1340,232.8,395); lOMoAR cPSD| 23136115 (0,0,2); Insert into player_influences Insert into player_objective (Minutes,Influence,ID) values
(Goal_Conceded,Clean_Sheets,ID) values (305,52.6,397); (21,7,3); Insert into player_influences lOMoAR cPSD| 23136115 lOMoAR cPSD| 23136115 Insert into player_objective (Minutes,Influence,ID) values
(Goal_Conceded,Clean_Sheets,ID) values (3420,868.0,408); (37,11,4); Insert into player_influences Insert into player_objective (Minutes,Influence,ID) values
(Goal_Conceded,Clean_Sheets,ID) values (487,68.8,409); (31,8,5); Insert into player_objective
-- Create table Club ID varchar pk,
(Goal_Conceded,Clean_Sheets,ID) values Club_Name varchar (10,2,6); CREATE TABLE Club Insert into player_objective (
(Goal_Conceded,Clean_Sheets,ID) values ID varchar(10) NOT NULL, (9,3,32);
Club_Name varchar(50) NOT NULL, Insert into player_objective PRIMARY KEY (ID)
(Goal_Conceded,Clean_Sheets,ID) values ); (22,5,33); Insert into player_objective
-- Create table Club_Player with Player_ID
(Goal_Conceded,Clean_Sheets,ID) values int fk, Club_ID varchar fk (31,11,34); CREATE TABLE Club_Player Insert into player_objective (
(Goal_Conceded,Clean_Sheets,ID) values Player_ID int NOT NULL, (46,15,38);
Club_ID varchar(10) NOT NULL, Insert into player_objective
PRIMARY KEY (Player_ID, Club_ID),
(Goal_Conceded,Clean_Sheets,ID) values FOREIGN KEY (Player_ID) (1,1,46); REFERENCES Player(ID), Insert into player_objective FOREIGN KEY (Club_ID)
(Goal_Conceded,Clean_Sheets,ID) values REFERENCES Club(ID) (17,1,58); ); Insert into player_objective
(Goal_Conceded,Clean_Sheets,ID) values
-- Table Position with ID varchar pk, (18,5,59); Position_name varchar Insert into player_objective /*CREATE TABLE
(Goal_Conceded,Clean_Sheets,ID) values PLAYER_CREATION*/ (35,9,60); create table player_creation Insert into player_objective (
(Goal_Conceded,Clean_Sheets,ID) values ID int, (18,6,89); Creativity float, Insert into player_objective Threat int,
(Goal_Conceded,Clean_Sheets,ID) values ) (0,0,70); Insert into player_creation Insert into player_objective
(Creativity,Threat,ID) values (0.0,0,2);
(Goal_Conceded,Clean_Sheets,ID) values Insert into player_creation (0,0,86); (Creativity,Threat,ID) values Insert into player_objective (307.4,797,3);
(Goal_Conceded,Clean_Sheets,ID) values Insert into player_creation (19,6,87);
(Creativity,Threat,ID) values (0.0,2,4); Insert into player_objective Insert into player_creation