Moritz

Aus ProgrammingWiki

< PMG2022 < DB Beleg

Archivierte Version vom 2022-03-25T09:29:38+00:00. Aktuelle Version

Inhaltsverzeichnis

1 Ziel der Datenbank
2 Modellierung in einem ERM
- 2.1 Erklärung
3 Transformation in das Relationenmodell
- 3.1 Erklärung
- 3.2 Abfragen
4 Quellen und Tools

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

Legende (Kardinalität)

links: n (mehrere)
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

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');

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

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

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

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

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

Quellen und Tools

ERDplus

Moritz

Inhaltsverzeichnis

Ziel der Datenbank

Modellierung in einem ERM

Erklärung

Transformation in das Relationenmodell

Erklärung

Abfragen

Quellen und Tools

Suche