PMG2022/DB Beleg/Moritz

Wiki-Quelltext dieser Seite.

<language>SQL</language>
 Thema: Datenbank einer Chat-Anwendung

== Ziel der Datenbank ==
Es gibt bereits zahlreiche komplexe Chat-Anwendungen wie WhatsApp und Discord. Mit dieser Datenbank sollen Datensätze aus solchen Anwendungen strukturiert gespeichert werden, sodass man auf diese einfach aber auch effizient zugreifen kann. Die verwendeten Datensätze sind allerdings weitaus weniger komplex als die, die von den oben genannten Anwendungen stammen - sie bieten jedoch trotzdem einen realistischen Einblick.

== Modellierung in einem ERM ==
[[Bild:Chat_Anwendung_ERM.jpg]]

'''Legende (Kardinalität)'''

[[Bild:Chat_Anwendung_Legende.JPG]]

links: n (mehrere) <br />
rechts: 1 (eins)

=== Erklärung ===

Es gibt drei Entitäten: Benutzer, Kanäle und Nachrichten.

Ein Benutzer besitzt eine ID, einen Benutzernamen, die E-Mail Adresse und das Erstellungsdatum des Accounts. Kanäle beinhalten ebenfalls IDs, Namen und Einladungscodes. Nachrichten bestehen aus dem eigentlichen Inhalt, dem Versendungsdatum und einer ID.

Ein Benutzer kann in mehreren Kanälen sein und ein Kanal kann mehrere Benutzer haben, weswegen zwischen diesen eine n : m Beziehung herrscht. Diese Beziehung, also die Mitgliedschaft eines Benutzers in einem Kanal, beinhaltet auch Informationen über potentielle Administratorrechte und könnte auch als separate Entität dargestellt werden. <br />
Zwischen Benutzern und Nachrichten herrscht eine 1 : n Beziehung: ein Benutzer kann mehrere Nachrichten versenden, aber eine Nachricht kann nur von einem Benutzer versendet werden. <br />
Kanäle können mehrere Nachrichten enthalten, aber eine Nachricht kann nur in einem Kanal verschickt werden. Deshalb existiert zwischen diesen beiden Entitäten ebenfalls eine 1 : n Beziehung. <br />
Die "Nachricht" Entität hat zu sich selbst eine 1 : n Beziehung, da eine Nachricht eine Antwort auf eine andere Nachricht sein kann, und eine Nachricht von mehreren anderen Nachrichten beantwortet werden kann.

Die drei Beziehungen unter den verschiedenen Entitäten sind notwendig, obwohl sie einen Kreis bilden:
* Ohne die Beziehung zwischen Benutzer und Kanal kann man nicht herausfinden welchen Kanälen ein Nutzer beigetreten ist und die Information über den Administratorstatus geht verloren.
* Ohne die Beziehung zwischen Benutzer und Nachricht kann man nicht herausfinden von wem die Nachricht versendet wurde.
* Ohne die Beziehung zwischen Kanal und Nachricht kann man nicht herausfinden aus welchem Kanal die Nachricht stammt, da Nutzer in mehreren Kanälen sein können (-> n : m Beziehung).

== Transformation in das Relationenmodell ==

<code id="623455781c9eb">
DROP TABLE IF EXISTS Benutzer;
CREATE TABLE Benutzer (
  ID varchar NOT NULL,
  Benutzername varchar(20) NOT NULL,
  Email varchar NOT NULL,
  Erstellt timestamp NOT NULL,

  PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS Kanal;
CREATE TABLE Kanal (
  ID varchar NOT NULL,
  Name varchar(20) NOT NULL,
  Einladung integer NOT NULL,
  Erstellt timestamp NOT NULL,

  PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS Mitgliedschaft;
CREATE TABLE Mitgliedschaft (
  ID varchar NOT NULL,
  BenutzerID varchar NOT NULL,
  KanalID varchar NOT NULL,
  Administrator bit NOT NULL DEFAULT 0,

  PRIMARY KEY (ID),
  FOREIGN KEY (BenutzerID) REFERENCES Benutzer(ID),
  FOREIGN KEY (KanalID) REFERENCES Kanal(ID)
);

DROP TABLE IF EXISTS Nachricht;
CREATE TABLE Nachricht (
  ID varchar NOT NULL,
  Inhalt varchar NOT NULL,
  Versendet timestamp NOT NULL,
  BenutzerID varchar NOT NULL,
  KanalID varchar NOT NULL,
  AntwortID varchar REFERENCES Nachricht(ID),

  PRIMARY KEY (ID),
  FOREIGN KEY (BenutzerID) REFERENCES Benutzer(ID),
  FOREIGN KEY (KanalID) REFERENCES Kanal(ID)
);

INSERT INTO Benutzer VALUES ('fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', 'LucasAckerman', 'lucasackerman@mail.com', '2017-10-12 21:22:23');
INSERT INTO Benutzer VALUES ('2ba503f9-96b2-487f-8706-d462696f8ce5', 'TomH',          'tomhirsch@mail.com',     '2017-12-11 14:32:01');
INSERT INTO Benutzer VALUES ('52bfe940-b4ae-4503-90d2-af5ad6d6e5dc', 'BrigitteE',     'brigitteengel@mail.com', '2018-02-04 17:57:33');
INSERT INTO Benutzer VALUES ('27a48639-fbd8-41ef-a85c-f6f9479a3120', 'KristianSankt', 'kristiansankt@mail.com', '2018-01-22 01:48:11');
INSERT INTO Benutzer VALUES ('bb375320-6b27-4097-a5c3-f302a407a6a7', 'JuliaS',        'juliascholz@mail.com',   '2017-12-29 12:27:49');

INSERT INTO Kanal VALUES ('6ab25458-d05a-44b9-b095-a790285f1bee', 'Arbeit',      2356, '2017-10-13 11:46:08');
INSERT INTO Kanal VALUES ('706265aa-ef16-44a0-ae93-274020b74382', 'Familie',     5654, '2017-12-11 15:33:51');
INSERT INTO Kanal VALUES ('38effe32-8708-4cc4-8daa-1308dc4bb93e', 'Neuer Kanal', 9085, '2018-01-24 12:57:43');

INSERT INTO Mitgliedschaft VALUES ('a1b96385-687b-4cbb-bca9-68d300e7782b', 'fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', '6ab25458-d05a-44b9-b095-a790285f1bee', 1);
INSERT INTO Mitgliedschaft VALUES ('8dc8234a-a2e5-4711-a60b-14f5f1daf8a5', 'fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', '706265aa-ef16-44a0-ae93-274020b74382', 1);
INSERT INTO Mitgliedschaft VALUES ('bbc254b3-9137-4c01-8185-ab40b479e74d', '2ba503f9-96b2-487f-8706-d462696f8ce5', '6ab25458-d05a-44b9-b095-a790285f1bee', 0);
INSERT INTO Mitgliedschaft VALUES ('b7b20018-38c1-4d5e-9aff-d2e6a000b720', '52bfe940-b4ae-4503-90d2-af5ad6d6e5dc', '38effe32-8708-4cc4-8daa-1308dc4bb93e', 1);
INSERT INTO Mitgliedschaft VALUES ('8c574c64-bd39-498e-9298-9437b27f34c9', '27a48639-fbd8-41ef-a85c-f6f9479a3120', '6ab25458-d05a-44b9-b095-a790285f1bee', 0);
INSERT INTO Mitgliedschaft VALUES ('f8bcbbf5-b085-4668-9e75-eedac942842a', 'bb375320-6b27-4097-a5c3-f302a407a6a7', '706265aa-ef16-44a0-ae93-274020b74382', 1);
INSERT INTO Mitgliedschaft VALUES ('43e74fd5-4aa8-4c4c-9e35-5884f30d4c75', '52bfe940-b4ae-4503-90d2-af5ad6d6e5dc', '706265aa-ef16-44a0-ae93-274020b74382', 0);

INSERT INTO Nachricht VALUES ('fa53dffa-b5b2-49fa-9c5c-ea3bd4be7bb2', 'Inhalt1', '2017-12-11 14:37:43', '2ba503f9-96b2-487f-8706-d462696f8ce5', '6ab25458-d05a-44b9-b095-a790285f1bee', NULL);
INSERT INTO Nachricht VALUES ('d463cf5c-3cf1-43ec-b2d3-38843a0236d6', 'Inhalt2', '2017-12-11 14:54:06', 'fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', '6ab25458-d05a-44b9-b095-a790285f1bee', 'fa53dffa-b5b2-49fa-9c5c-ea3bd4be7bb2');
INSERT INTO Nachricht VALUES ('cfef77fc-8af2-45e9-a275-7a6539d73109', 'Inhalt3', '2017-12-11 14:55:31', 'fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', '6ab25458-d05a-44b9-b095-a790285f1bee', 'fa53dffa-b5b2-49fa-9c5c-ea3bd4be7bb2');
INSERT INTO Nachricht VALUES ('db1c9900-bb15-44f9-89a4-cf040ade86ac', 'Inhalt4', '2018-02-04 15:35:12', 'fea262d1-3a82-4696-bc0a-85c2a7a7c3c3', '706265aa-ef16-44a0-ae93-274020b74382', NULL);
INSERT INTO Nachricht VALUES ('7595eb58-4324-4f90-afb6-b4f6496e6e19', 'Inhalt5', '2018-02-04 18:11:54', '52bfe940-b4ae-4503-90d2-af5ad6d6e5dc', '706265aa-ef16-44a0-ae93-274020b74382', NULL);
INSERT INTO Nachricht VALUES ('4975ab76-dbfc-49ea-9c22-38eb012f4bb1', 'Inhalt6', '2018-02-04 18:14:39', 'bb375320-6b27-4097-a5c3-f302a407a6a7', '706265aa-ef16-44a0-ae93-274020b74382', 'db1c9900-bb15-44f9-89a4-cf040ade86ac');
</code>

<run id="623455bd472e3">
SELECT * FROM Benutzer;
SELECT * FROM Kanal;
SELECT * FROM Mitgliedschaft;
SELECT * FROM Nachricht;
</run>

=== Erklärung ===
Das ERM wurde gemäß den Transformationsregeln in ein Relationenmodell übertragen.
* Da es keine 1 : 1 Beziehungen gibt, konnten keine Entitäten in eine Tabelle zusammengeführt werden. 
* Die 1 : n Beziehungen zwischen Benutzer und Nachrichten, Kanal und Nachrichten sowie Nachricht und Nachrichten wurden mit Fremdschlüsseln in der "n-Tabelle" verwirklicht: die Tabelle "Nachricht" besitzt die Fremdschlüssel "BenutzerID" (= "ID" der Tabelle "Benutzer"), "KanalID" (= "ID" der Tabelle "Kanal") und "AntwortID" (= "ID" der Tabelle "Nachricht" selbst). 
* Die n : m Beziehung zwischen Benutzern und Kanälen wird durch eine 3. Tabelle, "Mitgliedschaft", ermöglicht. Diese beinhaltet je einen Fremdschlüssel der "ID" von "Benutzer" und "Kanal", aber auch - wie im ERM dargestellt - ein "Administrator" Bit, der den Wert 1 (Administrator) oder 0 (nicht Administrator) annehmen kann. 

Die erste Normalform, also die "Atomatisierung" (Aufspaltung in eine Information pro Feld), ist bereits im ERM gegeben und war nach der Übertragung schon vorhanden. Auch die zweite Normalform, in der man Dopplungen in Datensätzen auflöst, war nicht notwendig. Zudem sind keine Datenfelder von Nicht-Primärschlüsseln abhängig, wodurch die dritte Normalform ebenfalls bereits erreicht war.

Der Primärschlüssel jeder Tabelle ist ein Feld mit dem Namen "ID", das eine zufällig erzeugte UUID beinhaltet. Es werden UUIDs anstelle von inkrementierenden Ganzzahlen verwendet. Diese haben den Vorteil, dass sie von verschiedenen Servern unabhängig voneinander generiert werden können, d.h. es ist praktisch unmöglich, dass zwei Server den gleichen Primärschlüssel generieren (und es dadurch bei einem der beiden Server zu einem Fehler kommt), wenn sie etwa zeitgleich einen Eintrag in die gleiche Datenbank einfügen. Sie können auch zufällig erzeugt werden, ohne Informationen über die bereits vorhandenen Daten in der Datenbank zu besitzen.

=== Abfragen ===
''' Simpel '''
<run id="623d7eb99bf48">
-- Alle Nachrichten, die eine Antwort auf eine andere Nachricht sind.
SELECT * FROM Nachricht WHERE AntwortID IS NOT NULL;

-- Alle Benutzer, deren Accounts vor 2018 erstellt wurden.
SELECT * FROM Benutzer WHERE Benutzer.Erstellt < '2017-12-31 23:59:59';
</run>

''' Mittel '''
<run id="623d8a354d2b4">
-- Alle Kanäle, in denen 'LucasAckerman' ein Mitglied ist.
SELECT Kanal.* FROM Kanal, Mitgliedschaft, Benutzer
WHERE Benutzer.Benutzername = 'LucasAckerman' AND Benutzer.ID = Mitgliedschaft.BenutzerID AND Kanal.ID = Mitgliedschaft.KanalID;

-- Alle Benutzer, die im Kanal 'Familie' Administratorrechte besitzen.
SELECT Benutzer.* FROM Kanal, Mitgliedschaft, Benutzer 
WHERE Kanal.Name = 'Familie' AND Kanal.ID = Mitgliedschaft.KanalID 
AND Mitgliedschaft.Administrator = 1 AND Benutzer.ID = Mitgliedschaft.BenutzerID;
</run>

''' Komplex '''
<run id="623d8b810859b">
-- Die Nachricht, die als letztes im Kanal mit dem Einladungscode 2356 gesendet wurde.
SELECT * FROM Nachricht WHERE Nachricht.KanalID = (
  SELECT ID FROM Kanal WHERE Kanal.Einladung = 2356
)
ORDER BY Nachricht.Versendet DESC
LIMIT 1;

-- Alle Antworten auf die erste Nachricht vom Benutzer 'TomH'.
SELECT * FROM Nachricht WHERE Nachricht.AntwortID = (
  SELECT Nachricht.ID 
  FROM Nachricht, Benutzer 
  WHERE Benutzer.Benutzername = 'TomH' AND Nachricht.BenutzerID = Benutzer.ID
  ORDER BY Nachricht.Versendet ASC 
  LIMIT 1
);
</run>

== Reflexion  ==
Ich bin mit der Umsetzung und Modellierung relativ zufrieden und denke, dass sie einen guten Einblick in den grundsätzlichen Aufbau der Datenbanken von Chat-Anwendungen liefert. Allerdings müsste das Modell für eine echte Anwendung um einige Attribute und Entitäten erweitert werden: man müsste z.B. die Passwort-Hashes von Benutzern speichern, und es wären auch zusätzliche Zeitstempel für Dinge wie Mitgliedschaften sinnvoll. Zudem sollte man den Ersteller eines Kanals speichern, um diesem spezielle Berechtigungen - wie das Verwalten von Administratoren - zu gewähren. Je nach Ziel der Anwendung könnte man das Berechtigungssystem über den Administratorstatus hinaus erweitern, sodass man z.B. genauer kontrollieren kann, wer Nachrichten senden, lesen bzw. löschen darf.

Bei der Umsetzung in einem DBMS sollte man wenn möglich anstelle von VARCHARs spezielle Datentypen für UUIDs verwenden, die z.B. bei PostgreSQL über Erweiterungen verfügbar sind. Dadurch würde man die Effizienz erhöhen, da der Datentyp deutlich kompakter gespeichert und schneller verarbeitet werden kann. Außerdem könnte man so UUIDs beim Einfügen von Datensätzen über INSERT vom DBMS automatisch erstellen lassen (anstatt sie auf den Servern zu generieren). Aus Datenschutzgründen müssten Emails, Nachrichteninhalte und andere Daten von Benutzern verschlüsselt gespeichert werden.

== Quellen und Tools ==
* [https://erdplus.com/ ERDplus] (25.03.2022)
* [https://www.w3schools.com/sql/sql_foreignkey.asp Foreign Key Constraints] (25.03.2022)