PMG2022/DB Beleg/Elia

Wiki-Quelltext dieser Seite.

<language>SQL</language>
== Idee & Ziel der Datenbank  ==
 Idee: Online Analytics Tool für das Spiel "League of Legends" nach dem Vorbild von [https://u.gg u.gg]
Ich selbst spiele gerne League of Legends. Bei diesem MOBA (Multiplayer-Online-Battle-Arena) spielen zwei Teams bestehend aus je 5 Spielern in einer Arena, der "Kluft der Beschwörer" gegeneinander.
Um zu gewinnen, muss ein Team den Nexus (eine Art Kristall) des gegnerischen Teams zerstören. [[Bild:Hilseduck_Lanes_cropped.jpg|400px|thumb|left|League of Legends Map]]Jeder Spieler spielt dabei einen der über 150 Champions, und übernimmt eine der 5 Rollen: entweder spielt er auf der "Top-Lane", der "Mid-Lane", im "Jungle", oder auf der "Bot-Lane" (dort spielen Supporter und ADC zusammen). Zudem wählt man vor dem Spiel Runen für seinen Champion aus, die unterschiedliche Vorteile bringen. Über das Spiel hinweg generiert man durch das töten von Minions / Monstern oder durch einen Kill an einem gegnerischem Spieler Gold, mit dem man sich dann Items kaufen kann. Diese verbessern stark die Fähigkeiten des eigenen Champions, z.B. mit mehr Schaden oder Rüstung. Gewinnt man ein Match, steigt man im Ligasysten (Eisen, Bronze, Silber, Gold, Diamant,..) auf. Jetzt ist natürlich interessant zu wissen, z.B. welche Rune extrem gute Spieler in hohen Ligen auf welchem Champion verwenden, oder was sie sich für Items auf dem Champion "XY" kaufen. Das ist der grundlegende Nutzen von Seiten wie [https://u.gg u.gg]. Sie tracken alle Spiele in League of Legends, speisen diese Datenmengen in eine Datenbank, und machen sie jedermann zugänglich. Selbstverständlich können die Datenmengen dann noch sinnvoll analysiert werden, und so z.B. festgestellt werden, welche Items gerade mit welchen Champions eine hohe Winrate (also auf alle Spiele gerechnet prozentuale Erfolgswahrscheinlichkeit) haben. Programme wie "Mobalytics" gehen dann noch einen Schritt weiter, analysieren die Spieler des gegnerischen Teams, und zeigem einem vor dem Spiel ihre Schwächen und Stärken auf. So raffiniert das auch klingen mag, alles basiert auf einer Datenbank, und dieser will ich mich hier annehmen. Um den Aufwand im machbaren Bereich zu halten, werde ich mich bei z.B. Items oder Runen auf wenige, beispielhafte, Parameter beschränken; und den Spieler auch nur ein Item bzw. eine Rune pro Game erlauben.


== ERM-Modell ==
[[Bild:Hilseduck_Erm_modulation(1)SMALL.jpg|1200px]]
<br><b>Entitäten:</b> Player, Game<br>
<b>Relations:</b> spielt<br>
<b>Kardinalitäten:</b><br>
[spielt]:<br>
Ein Player spielt mehrere Games.<br>
In einem Game spielen mehrere Player.<br>
>> Beziehung Game - Player = n:m<br><br>
Ich musste lange nachdenken, bis ich zu einem für mich zufriedenstellenden Ansatz für ein ERM-Modell gekommen bin.
Mein ursprünglicher Plan war, einzelne Entitäten für Player, Game, Item, Champion und Rune zu erstellen. Das daraus resultierende Konstrukt ergab allerdings stets keinen Sinn.
Denn Item, Champion und Runen sind in jedem Game unterschiedlich, aber werden gleichzeitig auch nur von einem Spieler gespielt. Nach langem Rätseln bin ich dann endlich auf die rettende Lösung gekommen:
Champion, Rune, und Item sind Attribute der Beziehung zwischen Player & Game (namens "spielt").
Denn ein Player "spielt" in einem Game Champion xy, kauft sich und somit spielt Item xy & spielt den Runenpfad xy in einem Game; somit ist es für mich völlig logisch, diese als Attribute der Beziehung "spielt" zu werten.
== Relationenmodell ==
[[Bild:Hilseduck_Rm_modell_v1.jpg]]<br>
<em>um das ERM Modell in ein RM Modell zu transferieren, befolgte ich die folgenden Schritte:</em><br>
<b>Grundgerüst:</b><br>
<b>1.</b> 2 Tabellen für die beiden Entitäten Game & Player<br>
<b>2.</b> Tabelle für die n:m-Beziehung "spielt" dieser Entitäten Game & Player >> Name: "Game_Player". game_id & player_id als Fremdschlüssel; bilden zusammen den Primärschlüssel. Wichtig: Attribute der <br>Beziehung "spielt" wie champion_id, champion_name, item_id, rolle, win/team, etc. werden als weitere Nichtschlüsselattribute in die Tabelle Game_Player eingefügt.
<br><br>
<b>Normalformen:</b><br>
<b>1. NF</b>; alle Attribute sind bereits atomisiert (liegen in ihrer kleinstmöglichen Form da); nichts zu verändern<br>
<b>2. NF</b>; alle Nichtschlüsselattribute hängen vom vollständigen Primärschlüssel ab.<br>
<b>3. NF</b>; einige Nichtschlüsselattribute in Tabelle Game_Player hängen voneinenander ab; z.B. champion_name von champion_id. >> Auslagerung von allen Champion-, Item-, und Rune-Attributen in eigene Tabellen (namens Champion, Item, Rune). champion_id, item_id, rune_id als Fremdschlüssel in Game_Player.
<br><br>
<b>letzte Veränderungen:</b><br>
lange Attribute wie z.B. champion_id werden mit c_id abgekürzt, selbiges für item_... & rune_... >> i_... | r_...<br>
Der Einheitlichkeit wegen wurden noch Start_Time bzw. End_Time von Game zu g_start_time bzw. g_end_time.<br>
Der Primärschlüssel für Game_Player ist zusammengesetzt aus g_id und p_id, da ein Spieler in einem Game stets nur einmal spielt >> Einzigartig, keine Dopplungen >> eignet sich für Primärschlüssel.
== SQL ==
<code id="622b63825b0d6">
DROP TABLE IF EXISTS player;
CREATE table player(
 p_id int NOT NULL PRIMARY KEY,
 p_username text NOT NULL, 
 p_level int NOT NULL, 
 p_rank text NOT NULL);

INSERT INTO player(p_id,p_username,p_level,p_rank) values (1,"hilseduck", 101, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (2,"chiefspy007", 34, "iron");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (3,"randalf", 507, "gold");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (4,"gerard_alaste", 55, "bronze");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (5,"96TRS", 98, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (6,"team_siko", 87, "iron");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (7,"skell.reaver", 201, "gold");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (8,"n0ts", 51, "bronze");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (9,"eremois", 156, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (10,"wildwestsr", 177, "bronze");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (11,"teemo_reemo", 156, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (12,"trest", 78, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (13,"livinrat", 126, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (14,"smurfedya", 48, "gold");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (15,"fish_553", 156, "diamond");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (16,"idom_ror", 48, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (17,"wizard", 78, "diamond");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (18,"kled_bane", 106, "silver");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (19,"doublelift", 707, "platin");
INSERT INTO player(p_id,p_username,p_level,p_rank) values (20,"faker", 852, "platin");

DROP TABLE IF EXISTS game;
CREATE table game(
 g_id int NOT NULL PRIMARY KEY,
 g_end_time datetime NOT NULL, 
 g_start_time datetime NOT NULL);
INSERT INTO game(g_id,g_end_time,g_start_time) values (1,'2022-03-18 10:27:32','2022-03-18 10:05:01');
INSERT INTO game(g_id,g_end_time,g_start_time) values (2,'2022-03-19 04:43:12','2022-03-19 04:12:53');
INSERT INTO game(g_id,g_end_time,g_start_time) values (4,'2022-03-20 15:12:59','2022-03-20 14:22:37');

DROP TABLE IF EXISTS rune;
CREATE table rune(
 r_id int NOT NULL PRIMARY KEY,
 r_path_name text NOT NULL);

INSERT INTO rune(r_id,r_path_name) values (1,"Precision");
INSERT INTO rune(r_id,r_path_name) values (2,"Domination");
INSERT INTO rune(r_id,r_path_name) values (3,"Sorcery");
INSERT INTO rune(r_id,r_path_name) values (4,"Resolve");
INSERT INTO rune(r_id,r_path_name) values (5,"Inspiration");

DROP TABLE IF EXISTS champion;
CREATE table champion(
 c_id int NOT NULL PRIMARY KEY,
 c_name text NOT NULL);

INSERT INTO champion(c_id,c_name) values (1,"Talon");
INSERT INTO champion(c_id,c_name) values (2,"Akshan");
INSERT INTO champion(c_id,c_name) values (3,"Jhin");
INSERT INTO champion(c_id,c_name) values (4,"Vi");
INSERT INTO champion(c_id,c_name) values (5,"Ahri");
INSERT INTO champion(c_id,c_name) values (6,"Malphite");
INSERT INTO champion(c_id,c_name) values (7,"Viego");
INSERT INTO champion(c_id,c_name) values (8,"Aatrox");
INSERT INTO champion(c_id,c_name) values (9,"Leona");
INSERT INTO champion(c_id,c_name) values (10,"Jinx");
INSERT INTO champion(c_id,c_name) values (11,"Rengar");
INSERT INTO champion(c_id,c_name) values (12,"Kled");
INSERT INTO champion(c_id,c_name) values (13,"Fizz");
INSERT INTO champion(c_id,c_name) values (14,"Twitch");

DROP TABLE IF EXISTS item;
CREATE table item(
  i_id int NOT NULL PRIMARY KEY,
  i_name text NOT NULL,
  i_attack_damage int NOT NULL,
  i_attack_speed int NOT NULL,
  i_armor int NOT NULL);

INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (1,"Thornnail",5,0,20);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (2,"Rapid Firecannon",35,100,0);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (3,"Bloodthirster",135,0,0);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (4,"Steel-Plated Boots",0,0,15);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (5,"Kraken Slayer",55,85,0);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (6,"Everfrost",100,0,5); 
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (7,"Galeforce",75,75,0);
INSERT INTO item(i_id, i_name, i_attack_damage, i_attack_speed,i_armor) values (8,"Gargoyle Stoneplate",0,0,45);

DROP TABLE IF EXISTS game_player;
CREATE table game_player(
  g_id int NOT NULL, 
  p_id INT NOT NULL,
  c_id int NOT NULL,
  i_id int,
  r_id int NOT NULL,
  win_or_team int NOT NULL,
  role TEXT NOT NULL,
  FOREIGN KEY (g_id) REFERENCES game(g_id),
  FOREIGN KEY (p_id) REFERENCES player(p_id),
  FOREIGN KEY (c_id) REFERENCES champion(c_id),
  FOREIGN KEY (i_id) REFERENCES item(i_id),
  FOREIGN KEY (r_id) REFERENCES rune(r_id),
  PRIMARY KEY (g_id, p_id),
  -- CHECKS
  CHECK (win_or_team <= 1),
  CHECK (win_or_team >= 0)
  -- win_or_team darf nur 0 (für Team Verlierer) oder 1 (für Team Gewinner) sein
);

INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,1,2,5,1,1,"mid");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,20,5,6,2,0,"mid");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,7,3,7,1,1,"adc");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,3,14,2,1,0,"adc");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,4,9,6,5,1,"support");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,9,6,8,5,0,"support");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,17,4,4,2,1,"jungle");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,13,11,3,2,0,"jungle");
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,2,8,1,4,1,"top");  
INSERT INTO game_player(g_id,p_id,c_id,i_id,r_id,win_or_team,role) values (1,8,12,8,3,0,"top");

</code>
<run id="622b638c37b67">
-- Schwierigkeitsstufe 1 : welchen Rang (engl. rank) hat der Spieler mit dem username "chiefspy007"?
SELECT player.p_username, player.p_rank  FROM player WHERE p_username = "chiefspy007";

-- Schwierigkeitsstufe 1 : welche Stats hat das Item "Galeforce"?
SELECT item.i_id, item.i_name, item.i_attack_damage, item.i_attack_speed, item.i_armor  FROM item WHERE item.i_name = "Galeforce";

-- Schwierigkeitsstufe 2: Wie lange dauerte das Game mit der Game-ID 1 in Minuten?
SELECT ROUND((JULIANDAY(g_end_time) - JULIANDAY(g_start_time)) * 1440,2) FROM game  WHERE g_id = 1;

-- Schwierigkeitsstufe 2: Welches Games dauerte am längsten, und wie lange?
SELECT g_id, MAX(ROUND((JULIANDAY(g_end_time) - JULIANDAY(g_start_time)) * 1440,2)) FROM game;

-- Schwierigkeitsstufe 2: Wie viele Games hat der Spieler mit dem username "randalf" bereits gewonnen?
SELECT SUM(game_player.win_or_team) FROM game_player, player, game WHERE player.p_id = game_player.p_id AND game.g_id = game_player.g_id AND player.p_username = "randalf";

-- Schwierigkeitsstufe 3: Überblick über das Game mit der ID 1. 
SELECT game_player.g_id,win_or_team,game_player.role,player.p_username,player.p_level,player.p_rank,champion.c_name,item.i_name,rune.r_path_name,game.g_start_time, game.g_end_time FROM game_player,
game,player,champion,item,rune WHERE game_player.g_id = game.g_id AND game_player.p_id = player.p_id AND game_player.c_id = champion.c_id AND game_player.i_id = item.i_id 
AND game_player.r_id = rune.r_id AND game_player.g_id = 1 
ORDER BY win_or_team DESC, CASE 
WHEN role='top' THEN 1 
WHEN role='jungle' THEN 2 
WHEN role='mid' THEN 3 
WHEN role='adc' THEN 4
WHEN role='support' THEN 5
ELSE role
END;

-- Schwierigkeitsstufe 3 Welchen Champion und welches Item spielte der Spieler mit dem höchsten level zuletzt in einem Spiel, und in welcher Role?
SELECT MAX(game_player.g_id), game_player.p_id, game_player.role,player.p_username,MAX(player.p_level),player.p_rank,champion.c_name,item.i_name FROM game_player,player,champion,item WHERE game_player.p_id = player.p_id 
AND game_player.c_id = champion.c_id AND game_player.i_id = item.i_id;

</run>
<run id="623dd7d4bba5c">
-- Überblick über alle Tabelleninhalte:
SELECT * FROM game;
SELECT * FROM player;
SELECT * FROM item;
SELECT * FROM champion;
SELECT * FROM rune;
SELECT * FROM game_player;
</run><br>
<b>Anmerkungen:</b><br>
i_id in Game_Player darf NULL sein, da ja der Fall eintreten kann, dass ein Spieler nicht genügend Gold erwirtschaftet, um sich ein Item zu kaufen.<br>
win_or_team darf lediglich 0 (für das Loosing Team) bzw. 1 (für das Winning Team) sein, daher die CHECKs.<br>
== Reflexion ==
 Ich bin grundsätzlich zufrieden mit dem Ergebnis; das Ziel, welches ich mir zu Beginn gesteckt habe, wurde erreicht.
<b>Verbesserungsvorschläge:</b><br>
Würde man jetzt die Datenbank mit massenhaft Daten befüllen, könnte man tatsächlich nützliche Informationen gewinnen.<br>
Selbstverständlich wurde alles stark vereinfacht; so kann ein Spieler in einem echten League of Legends Spiel bis zu 6 Items haben, ebenso fehlen in meiner Datenbank Parameter wie die Kills, Deaths und Assists in jedem Spiel, sowie das Gold, welches der Spieler im Spiel erwirtschaftet hat, und das Level, was er mit seinem Champion im Spiel erreicht hat.
Die zusätzlichen Items könnte man mit 5 zusätzlichen Tabellen (Item_Slot_2, Item_Slot_3,...) lösen, die dann wieder via Fremdschlüssel in die Tabelle Game_Player eingebunden werden.
Statistiken wie Kills, Deaths, Assists, oder auch das erwirtschaftete Gold bzw. das erreichte Champion Level im Spiel ließen sich sogar noch einfacher implementieren, indem man diese einfach als Nicht-Schlüssel-Attribute in die Tabelle Game_Player einfügt.<br>
Selbstverständlich müssten dann noch die Millionen League of Legends-Accounts in die "Player" Tabelle gespeist werden, die 140 Champions in die Champion-Tabelle, und die sehr vielen Items in die Item-Tabelle.<br>
Ein weiterer wichtiger Parameter, der für die Player wichtig wäre, ist die Region, also auf welchem Server die Spieler spielen (z.B. North-America, EU-West, Korea,...). Das wäre mittels einem einfachen Nicht-Schlüssel-Attribut namens "region" in der "Player" Tabelle realisierbar.<br>
<b>Vergleich</b> <br>
<em>Das was die Datenbank des Projekts über ein game ausgibt:</em><br>
[[Bild:Hilseduck_Game_overview.JPG | 1150px ]]<br>
<em>Das was u.gg über ein echtes League of Legends game ausgibt:</em><br> 
[[Bild:Hilseduck_Original_overview_ugg.jpg | 550px]]<br>
<em>See no difference</em>
<br>
<b>Selbstkritik</b> <br>
Ich weiß nicht wie sinnvoll es war, z.B. den Item Namen auch bereits in der Item Tabelle i_name zu nennen, und in der Tabelle Game_Player denselben Namen i_name zu verwenden. 
Das führt nämlich dazu, dass man in den Abfragen stets z.B. item.i_name = game_player.i_name schreiben muss - etwas mehr Arbeit.
Andererseits sieht man dadurch auch gleich, aus welchen Tabellen die Daten der Abfrage genommen werden, von daher ist es vielleicht auch gar nicht so schlecht.
<br>
Zudem sieht das ERM-Modell zugegeben etwas speziell aus - aber meinen Überlegungen zufolge gibt es keinen anderen sinnvollen Weg dafür, daher nehme ich das jetzt einfach mal so hin. 
<br>
Zu guter Letzt ist noch zu sagen, dass ich die Tabelle Game_Player nur mit einem Game befüllt habe, des Aufwands wegen; selbstverständlich wäre es schön gewesen, evtl. 2 Game-Datensätze in dieser Tabelle zu haben.
== Quellen ==
<b>Verwendete Software:</b><br>
[https://sqliteonline.com/ sqlite.com] >> schöner online sql editor/compiler, mit nützlichen Features wie Vervollständigungsvorschlägen<br>
[https://erdplus.com/ ERDplus.com]<br>
<b>SQL-Tutorials:</b><br>
[https://www.tutorialspoint.com/sqlite/sqlite_constraints.htm sqlite check constraints] - 18.03.2022<br>
[https://sqlandme.com/2013/11/18/sql-server-custom-sorting-in-order-by-clause/ sql order by case] - 24.03.2022<br>
[https://stackoverflow.com/questions/50117301/how-do-i-subtract-two-columns-with-datetime-in-sqlite 2 datetime werte voneinander abziehen >> via temporärer umwandlung ins julianday format] - 24.03.2022<br>
<b>Projekt-Inspirationen:</b><br>
[https://u.gg u.gg] - 18.03.2022<br>
<br>