Fachlicher Anwendungsbezug

Szenario

Die Datenbank repräsentiert eine Online-Videospielbibliothek, etwa so wie Steam oder Epic Games Launcher. Hier sollen Nutzer Spiele finden, kaufen, herunterladen und letztlich spielen können.

Nutzen und Sinn

Spiele physisch, also in Form einer CD oder Diskette zu besitzen, rückt immer mehr in den Hintergrund. Stattdessen dominieren digitale Anbieter, welche lediglich eine Spielelizenz anbieten. Dem Nutzer, welcher ein Spiel gekauft hat, wird somit der Besitz des Spiels zugewiesen.

Abgrenzungen

Die entwickelte Datenbank soll keine vollständige Infrastruktur einer Videospielbibliothek beinhalten, wie es große Anbieter wie Steam besitzen. Der Fokus liegt auf der Verwaltung von Spielen. Hierzu gehören die Spiele selbst, ihre Kategorisierungen und Metadaten sowie alle möglichen Erfolge innerhalb jedes Spiels. Es soll ein vereinfachtes Nutzersystem integriert werden, bei welchem einem Nutzer mit Basis-Daten seine Spiele im Besitz zugeteilt werden können.

Anforderungsanalyse

Die Datenbank stellt eine Online-Spielbibliothek dar, in welcher Nutzer ihre erworbenen Spiele spielen können und Entwickler/Publisher Spiele veröffentlichen können. Demnach werden verschiedene Rollen benötigt:

Developer
User
Publisher

Spiele haben viele verschiedene Eigenschaften. Dazu gehören Name, Preis, verliehene Tags und darin enthaltene Errungenschaften:

Achievements
GameTotalDownloads
Tags
Franchises ⇒ Achievements sind stetig im Umlauf, da User diese erhalten können. Ebenso sind Tags stark im Umlauf, da einem Spiel Tags hinzugefügt oder vorhandene wieder entfernt werden können

Nutzer sollen zusätzlich Reviews für Spiele schreiben können, welche eine positive/negative Bewertung + einen Review-Text beinhalten.

GameReview

Die allgemeine Struktur der Datenbank gleicht der Struktur des Online-Anbieters “Steam”, wie zuvor beschrieben. Die Grundfunktionen, wie bspw. die Filterung von Spielen, soll mit unserer Datenbank ebenfalls möglich sein. Die zuvor beschriebenen Abgrenzungen sind keine Anforderungen für dieses Projekt. Hauptbestandteil unserer Datenbank sind also der User und das Game als Objekte. Alle weiteren Tabellen/Objekte sind in jeglicher Art mit diesen beiden Objekten verbunden und Nutzen als Zusatzinformationen. Diese waren nicht weiter aufspaltbar und benötigten somit eigene Tabellen bzw. fanden wir es sinnig, sie in eigene Tabellen zu verfrachten.

Ideenfindung

Vorerst sollen Ideen gesammelt werden, welche Spalten/Tabellen die Datenbank beinhalten könnte. Nach und nach wird dann sinnvoll normalisiert.

Zwei Nutz-Beispiele

-- Wähle alle Spiele, die vom Developer X sind, dem Franchise Y angehören und das Tag Z besitzen.
SELECT * FROM Game AS G
INNER JOIN GameToDeveloper AS GTD
	ON G.game_id = GTD.game_id
INNER JOIN GameToFranchise AS GTF
	ON G.game_id = GTF.game_id
INNER JOIN GameToTag AS GTT
	ON G.game_id = GTT.game_id
INNER JOIN Developer AS D
	ON GTD.developer_id = D.developer_id
INNER JOIN Franchise AS F
	ON GTF.franchise_id = F.franchise_id
INNER JOIN Tag AS T
	ON GTT.tag_id = T.tag_id
WHERE D.developer_name = ""
	AND F.franchise_name = ""
	AND T.tag_name = "";
 
-- Wähle alle Reviews für das Spiel X.
SELECT * FROM GameReview AS GR
INNER JOIN Game AS G
	ON GR.game_id = G.game_id
WHERE G.game_name = "A Hat In Time";

ERM-Diagramm und Struktur

Data Dictionaries

User

attribute	datatype	format	default	is_nullable	key	description
user_account_name	varchar(32)	-	-	false	primary	unique identification for every user
user_name	varchar(32)	-	-	false	-	a user name chosen by the user and visible to other users. Duplicates are allowed.
user_email	TEXT	EMAIL	-	false	-	the email address linked to the user account. Duplicates are allowed.
user_password	TEXT	PASSWORD(str)	-	false	-	the hashed value of the user account password
user_profile_picture_url	TEXT	URL	NULL	true	-	URL to the user profile picture file
user_is_email_verified	BOOLEAN	-	false	false	-	whether the user has verified his email address

Game

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for every game
game_name	varchar(128)	-	-	false	unique	full name of the game
game_price	SMALLINT UNSIGNED	-	0	false	-	the price of a game stored as American cents
game_size	BIGINT UNSIGNED	-	-	false	-	the size of the game files in bytes
game_cover_url	TEXT	URL	NULL	true	-	URL to the games cover image file
game_discount	TINYINT	{0, 1, 2, …, 100}	0	false	-	the current discount on the game price in percent
game_release_date	TIMESTAMP	`YYYY-MM-DD HH:MM:SS.ffffff`	-	false	-	represents the time the game was released
game_controller_compatibility	ENUM(‘none’, ‘basic’, ‘full’)	-	‘none’	false	-	enum: 0 not compatible, 1 basic implementation, 2 full compatiblity

UserToGame

attribute	datatype	format	default	is_nullable	key	description
user_account_name	varchar(32)	-	-	false	foreign	unique identification for the owning user account
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for the game owned by a user

Achievement

attribute	datatype	format	default	is_nullable	key	description
achievement_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for every achievement
achievement_name	TEXT	-	-	false	-	full name of the achievement
achievement_cover_url	TEXT	URL	NULL	true	-	URL to the achievement cover image file
achievement_description	TEXT	-	-	false	-	a short description of how to unlock the achievement
achievement_is_spoiler	BOOLEAN	-	false	false	-	true if the achievements description is hidden until unlocked
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every game

UserToAchievement

attribute	datatype	format	default	is_nullable	key	description
user_account_name	varchar(32)	-	-	false	foreign	unique identification for a user being granted the achievement
achievement_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for the unlocked achievement

GameReview

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for reviewed game
user_account_name	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for reviewing user
review_sentiment	BOOLEAN	-	-	false	-	false for negative, true for positive reviews
review_text	TEXT	-	NULL	true	-	additional comment for each review

GameBuild

attribute	datatype	format	default	is_nullable	key	description
game_build_id	INT UNSIGNED AUTO_INCREMENT	-	uses the global auto-incremented BUILD_ID variable	false	primary	a unique identification for a game build across all games
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for the builds game
game_build_date	TIMESTAMP	`YYYY-MM-DD HH:MM:SS.ffffff`	CURRENT_TIMESTAMP	false	-	datetime of the builds release

GameTotalDownloads

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for the downloaded game
game_total_downloads	BIGINT UNSIGNED	-	0	false	-	number of total downloads a game currently has

Publisher

attribute	datatype	format	default	is_nullable	key	description
publisher_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for every publisher
publisher_name	varchar(64)	-	-	false	-	full publisher name
publisher_profile_picture_url	TEXT	URL	NULL	true	-	link to the profile picture file
publisher_description	TEXT	-	NULL	true	-	description for the publisher

GameToPublisher

attribute	datatype	format	default	is_nullable	key	description
publisher_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every publisher
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every game

Developer

attribute	datatype	format	default	is_nullable	key	description
developer_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for every developer
developer_name	varchar(64)	-	-	false	-	full developer name
developer_profile_picture_url	TEXT	URL	NULL	true	-	link to the profile picture file
developer_description	TEXT	-	NULL	true	-	description for the developer

GameToDeveloper

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every game
developer_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every publisher

Tag

attribute	datatype	format	default	is_nullable	key	description
tag_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for a tag
tag_name	varchar(64)	-	-	false	unique	name of the tag, e.g. Racing, Action, Horror etc.

Franchise

attribute	datatype	format	default	is_nullable	key	description
franchise_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	primary	unique identification for a franchise
franchise_name	varchar(64)	-	-	false	unique	name of the franchise which groups many games of the same main game together, e.g. Resident Evil, Battlefield etc.

GameToFranchise

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every game
franchise_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for a franchise

GameToTag

attribute	datatype	format	default	is_nullable	key	description
game_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for every game
tag_id	UUID	`aaaaaaaa-bbbb-1ccc-dddd-eeeeeeeeeeee`	-	false	foreign	unique identification for a tag

ERM

HIER ABBILDUNG

ERM (roher Mermaid-Code)

flowchart LR



User[User]
User.user_account_name(["<u>user_account_name</u>"])
User.user_name([user_name])
User.user_email([user_email])
User.user_password([user_password])
User.user_profile_picture_url([user_profile_picture_url])
User.user_is_email_verified([user_is_email_verified])

Achievement[Achievement]
Achievement.achievement_id(["<u>achievement_id</u>"])
Achievement.achievement_name([achievement_name])
Achievement.achievement_cover_url([achievement_cover_url])
Achievement.achievement_description([achievement_description])
Achievement.achievement_is_spoiler([achievement_is_spoiler])
Achievement.game_id(["<span style="color: blue;">game_id</span>"])

Publisher[Publisher]
Publisher.publisher_id(["<u>publisher_id</u>"])
Publisher.publisher_name([publisher_name])
Publisher.publisher_profile_picture_url([publisher_profile_picture_url])
Publisher.publisher_description([publisher_description])

Game[Game]
Game.game_id(["<u>game_id</u>"])
Game.game_name([game_name])
Game.game_price([game_price])
Game.game_size([game_size])
Game.game_cover_url([game_cover_url])
Game.game_discount([game_discount])
Game.game_release_date([game_release_date])
Game.game_controller_compatiblity([game_controller_compatiblity])

GameReview[GameReview]
GameReview.game_id(["<span style="color: blue;">game_id</span>"])
GameReview.user_id(["<span style="color: blue;">user_id</span>"])
GameReview.review_sentiment([review_sentiment])
GameReview.review_text([review_text])

Developer[Developer]
Developer.developer_id(["<u>game_id</u>"])
Developer.developer_name([developer_name])
Developer.developer_description([developer_description])

Franchise[Franchise]
Franchise.franchise_id([<u>franchise_id</u>])
Franchise.franchise_name([franchise_name])

GameBuild[GameBuild]
GameBuild.game_build_id([<u>game_build_id</u>])
GameBuild.game_build_date([game_build_date])

Tag[Tag]
Tag.tag_id([<u>tag_id</u>])
Tag.tag_name([tag_name])

GameTotalDownloads[GameTotalDownloads]
GameTotalDownloads.game_id(["<span style="color: blue;">game_id</span>"])
GameTotalDownloads.game_total_downloads([game_total_downloads])



UserToAchievement{UserToAchievement}
UserToAchievement.user_account_name([user_account_name])
UserToAchievement.achievement_id([achievement_id])

UserToGame{UserToGame}
UserToGame.user_account_name([user_account_name])
UserToGame.game_id([game_id])

GameToPublisher{GameToPublisher}
GameToPublisher.publisher_id([publisher_id])
GameToPublisher.game_id([game_id])

GameToDeveloper{GameToDeveloper}
GameToDeveloper.game_id([game_id])
GameToDeveloper.developer_id([developer_id])

GameToFranchise{GameToFranchise}
GameToFranchise.game_id([game_id])
GameToFranchise.franchise_id([franchise_id])

GameToTag{GameToTag}
GameToTag.game_id([game_id])
GameToTag.tag_id([tag_id])

Game.GameTotalDownloads{total downloads of}



User --> User.user_account_name
User --> User.user_name
User --> User.user_email
User --> User.user_password
User --> User.user_profile_picture_url
User --> User.user_is_email_verified

Game --> Game.game_id
Game --> Game.game_name
Game --> Game.game_price
Game --> Game.game_size
Game --> Game.game_cover_url
Game --> Game.game_discount
Game --> Game.game_release_date
Game --> Game.game_controller_compatiblity

Achievement --> Achievement.achievement_id
Achievement --> Achievement.achievement_name
Achievement --> Achievement.achievement_cover_url
Achievement --> Achievement.achievement_description
Achievement --> Achievement.achievement_is_spoiler
Achievement --> Achievement.game_id

Publisher --> Publisher.publisher_id
Publisher --> Publisher.publisher_name
Publisher --> Publisher.publisher_profile_picture_url
Publisher --> Publisher.publisher_description

GameReview --> GameReview.game_id
GameReview --> GameReview.user_id
GameReview --> GameReview.review_sentiment
GameReview --> GameReview.review_text

Developer --> Developer.developer_id
Developer --> Developer.developer_name
Developer --> Developer.developer_description

Franchise --> Franchise.franchise_id
Franchise --> Franchise.franchise_name

GameBuild --> GameBuild.game_build_id
GameBuild --> GameBuild.game_build_date

Tag --> Tag.tag_id
Tag --> Tag.tag_name

GameTotalDownloads --> GameTotalDownloads.game_id
GameTotalDownloads --> GameTotalDownloads.game_total_downloads



UserToAchievement --> UserToAchievement.user_account_name
UserToAchievement --> UserToAchievement.achievement_id
User -->|n achieves| UserToAchievement
Achievement -->|m granted to| UserToAchievement

UserToGame --> UserToGame.user_account_name
UserToGame --> UserToGame.game_id
User -->|n owns| UserToGame
Game -->|m owned by| UserToGame

GameToPublisher --> GameToPublisher.publisher_id
GameToPublisher --> GameToPublisher.game_id
Game -->|n published by| GameToPublisher
Publisher -->|m publishes| GameToPublisher

GameToDeveloper --> GameToDeveloper.game_id
GameToDeveloper --> GameToDeveloper.developer_id
Game -->|n developed by| GameToDeveloper
Developer -->|m develops| GameToDeveloper

GameToFranchise --> GameToFranchise.game_id
GameToFranchise --> GameToFranchise.franchise_id
Game -->|n part of| GameToFranchise
Franchise -->|m includes| GameToFranchise

Game -->|1 has| Game.GameTotalDownloads
GameTotalDownloads -->|1 has| Game.GameTotalDownloads

GameToTag --> GameToTag.game_id
GameToTag --> GameToTag.tag_id
Game -->|n has| GameToTag
Tag -->|m appended to| GameToTag

Datenbankskripte / Befüllung

Für die Befüllung der Datenbank haben wir Beispieldaten von einer externen API herangezogen. So wird sichergestellt, dass genug Daten für sinnvolle Beispiel-Abfragen existieren.

Der untenstehende Shell-Code ist lediglich zu unserer eigenen Prüfung der .sql-Datei in einem Docker-Container. Die Installation unserer Datenbank ist in der beigefügten README.md zu finden.

# Load the sql file
docker exec -i mariadb-server mariadb -uroot -proot < "path"
 
# Create a new bash in MariaDB
docker exec -it <CONTAINER> bash

SQL-Abfragen

Diese Anfragen sollen beispielhaft zeigen, welche Verknüpfungen zwischen den Tabellen existieren. Im vorherigen Kapitel “Nutzen und Sinn” wurden ebenfalls bereits zwei beispielhafte Nutzbeispiele unserer Datenbank gezeigt. Hier haben wir uns weitere vier überlegt:

-- Wähle alle Nutzer, die ein Spiel mit dem Tag X besitzen und ein Review in mindestens einem Spiel geschrieben haben.
SELECT U.user_account_name, GTT.game_id, T.tag_id
FROM User AS U
INNER JOIN GameReview AS GR
	ON U.user_account_name = GR.user_account_name
INNER JOIN UserToGame AS UTG
	ON U.user_account_name = UTG.user_account_name
INNER JOIN GameToTag AS GTT
	ON UTG.game_id = GTT.game_id
INNER JOIN Tag AS T
	ON GTT.tag_id = T.tag_id
WHERE T.tag_name = "Action"
GROUP BY U.user_account_name;
 
-- Wähle alle Nutzer, die mindestens ein Game aus einem Franchise X besitzen.
SELECT DISTINCT U.user_account_name, UTG.game_id FROM User AS U
INNER JOIN UserToGame AS UTG
	ON U.user_account_name = UTG.user_account_name
INNER JOIN GameToFranchise AS GTF
	ON UTG.game_id = GTF.game_id
INNER JOIN Franchise AS F
	ON GTF.franchise_id = F.franchise_id
WHERE F.franchise_name = "Portal";
 
-- Wähle alle Nutzer, die mindestens ein Spiel mit mehr als 50.000 Downloads besitzen.
SELECT DISTINCT U.user_account_name FROM User AS U
INNER JOIN UserToGame as UTG
	ON U.user_account_name = UTG.user_account_name
INNER JOIN GameTotalDownloads AS GTD
	ON GTD.game_id = UTG.game_id
WHERE GTD.game_total_downloads > 50000;
 
-- Wähle die Top 3 Publisher nach durchschnittlichen Spieldownloads.
SELECT P.publisher_name, AVG(GTD.game_total_downloads) AS GTD_AVG FROM Game AS G
INNER JOIN GameTotalDownloads AS GTD
	ON GTD.game_id = G.game_id
INNER JOIN GameToPublisher AS GTP
	ON GTP.game_id = GTD.game_id
INNER JOIN Publisher AS P
	ON P.publisher_id = GTP.publisher_id
GROUP BY P.publisher_name
ORDER BY GTD_AVG DESC
LIMIT 3;

Berechtigungskonzept

Beschreibung

Beim Berechtigungsprojekt haben wir uns an eine echte Applikation mit einer API und mehreren Endpunkten bezogen. Je nach Endpunkt sollte hierbei ein anderer Datenbank-Nutzer verwendet werden, der unterschiedliche Berechtigungen für die jeweiligen Tabellen besitzt. Beim Erstellen der Datenbank werden drei Nutzer erstellt: logged_out_client, logged_in_client und server. Die Namen sind hierbei selbsterklärend. Zudem werden drei Rollen angelegt:

public_data: Kann alle Tabellen bis auf User, UserToGame und UserToAchievement selektieren
private_data: Kann die User, UserToGame und UserToAchievement selektieren
server: Kann in alle Tabellen einfügen, einträge updaten und löschen

Der logged_out_client-Nutzer erhält die public_data-Rolle, kann also nur auf öffentliche Daten zugreifen. Der logged_in_client kann durch die private_data-Rolle zudem Tabellen auslesen, die mit Nutzerprofilen etwas zu tun haben, erhält aber ebenfalls die public_data-Rolle, um auf andere Tabellen zuzugreifen. Der server erhält als einzigster Nutzer die server-Rolle, um Änderungen an den Tabellen vorzunehmen. Auch der server-Nutzer erhält die anderen beiden Rollen.

SQL-Code zur Implementierung

CREATE USER IF NOT EXISTS 'logged_out_client'@'localhost' IDENTIFIED BY "PASSWORD_LOGGED_OUT_CLIENT";
CREATE USER IF NOT EXISTS 'logged_in_client'@'localhost' IDENTIFIED BY "PASSWORD_LOGGED_IN_CLIENT";
CREATE USER IF NOT EXISTS 'server'@'localhost' IDENTIFIED BY "PASSWORD_SERVER";
 
CREATE ROLE IF NOT EXISTS public_data;
CREATE ROLE IF NOT EXISTS private_data;
CREATE ROLE IF NOT EXISTS server;
 
-- ======================================
-- Role: public_data
-- ======================================
GRANT SELECT ON Game TO public_data;
GRANT SELECT ON Achievement TO public_data;
GRANT SELECT ON GameReview TO public_data;
GRANT SELECT ON GameBuild TO public_data;
GRANT SELECT ON GameTotalDownloads TO public_data;
GRANT SELECT ON Publisher TO public_data;
GRANT SELECT ON PublisherToGame TO public_data;
GRANT SELECT ON Developer TO public_data;
GRANT SELECT ON DeveloperToGame TO public_data;
GRANT SELECT ON Tag TO public_data;
GRANT SELECT ON Franchise TO public_data;
GRANT SELECT ON GameToFranchise TO public_data;
GRANT SELECT ON GameToTag TO public_data;
 
-- ======================================
-- Role: private_data
-- ======================================
GRANT SELECT ON User TO private_data;
GRANT SELECT ON UserToGame TO private_data;
GRANT SELECT ON UserToAchievement TO private_data;
 
-- ======================================
-- Role: server
-- ======================================
GRANT INSERT, UPDATE, DELETE ON Vaporo.* TO server;
 
GRANT public_data TO 'logged_out_client'@'localhost';
GRANT public_data TO 'logged_in_client'@'localhost';
GRANT public_data TO 'server'@'localhost';
GRANT private_data TO 'logged_in_client'@'localhost';
GRANT private_data TO 'server'@'localhost';
GRANT server TO 'server'@'localhost';

Dokumentation

Der SQL-Code ist dokumentiert und zeigt die Funktion aller Spalten in der Vaporo-Datenbank. Hierfür konnten wir die Beschreibungen aus den DataDictionaries wiederverwenden. Die Datei [[Projekt-README|README.md]] zeigt den Installationsprozess sowie die Struktur und Funktion aller Tabellen und der Datenbank an sich.

Made by Louis Escher (TIM24) & Dominic Brauer (TIM24)

📚 Lou's DHBW-Archiv

Explorer

Videospielbibliothek