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.

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.

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.

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.

== 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">
-- mehr timestamps, z.B. Mitgliedschaften
-- Verschlüsselung von Emails/Nachrichteninhalten
-- UUID Erweiterung für mehr Effizienz (eigener/kompakterer Datentyp) und automatische Generierung bei INSERT

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", 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 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 ===
<run id="623d7eb99bf48">
-- 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;

-- 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>