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