Datenbankentwurf


Elektro-Roller waren ein Hype, mit dem die Mobilität in Großstädten revolutioniert werden sollte. Ein Start-Up, das Elektro-Roller verleihen will, gibt einer IT-Firma den Auftrag eine Software zu programmieren, mit welcher der Ausleihvorgang verwaltet werden kann. Die anfallenden Daten sollen in einer relationalen Datenbank gespeichert werden. In dieser Übung soll dazu eine vereinfachte Datenbank entworfen werden. Dazu sollen folgende Schritte durchgeführt werden:

  1. es wird ein Dialog zwischen Auftraggeber und Auftragnehmer über die Anforderungen an die Datenbank geführt
  2. auf der Grundlage des Gesprächsprotokolls wird ein Entity-Relationship-Diagram entworfen
  3. aus dem ER-Diagram wird die Datenbankstruktur abgeleitet
  4. die Datenbankstruktur wird in eine relationale Datenbank umgesetzt
  5. es werden geeignete SQL-Abfragen zur Bereitstellung gewünschter Daten entworfen

Schritt 1: Planungsgespräch führen

In einem Gespräch der IT-Firma mit dem Start-Up werden die Anforderungen an die Datenbank in einem Fließtext notiert:

Das ERoller-Start-Up stellt Elektro-Roller bereit, die an einer Leihstation ausgeliehen werden können. Es gibt mehrere Leihstationen in der Stadt, an denen ERoller geparkt sind. Für jeden geparkten ERoller wird eine Park-ID generiert. Es gibt verschiedene Fabrikate, wobei jedem ERoller eine ERoller-ID und eine Seriennummer zugeordnet wird. Ein Kunde, der mit seiner Kunden-ID und seinem Namen gespeichert ist, kann einen ERoller ausleihen, indem er an einer Leihstation seine Benutzer-ID eingibt. Für die Ausleihe wird eine Leih-ID generiert.

In einer Datenbank gibt es drei unterschiedliche Strukturen:

  • Tabellen
  • Attribute
  • Relationen

Eine Tabelle soll für eine Person oder ein Objekt stehen. Allgemein wird eine Person oder ein Objekt eine Entität genannt. In einer Tabelle werden Eigenschaften der Entität gespeichert, also die Attribute der Entität. Eine Datenbank sollte so in kleinere Tabellen aufgeteilt sein, dass die 3. Normalform erfüllt ist. Die Beziehungen zwischen den Entitäten werden als Relationen der Tabellen mit Hilfe von Fremdschlüsseln festgelegt.

Um die Entitäten, Eigenschaften und Beziehungen zu finden, wird das Besprechungsprotokoll analysiert.

  • Die Entitäten und Eigenschaften werden im Besprechungsprotokoll durch Nomen (Hauptwörter) beschrieben.
  • Die Beziehungen werden oft durch Verben (Tunwörter) beschrieben oder müssen aus dem Kontext geschlossen werden.

Im Gesprächsprotokoll werden zielgerichtet Nomen und Verben markiert:

Das ERoller-Start-Up stellt Elektro-Roller bereit, die an einer Leihstation ausgeliehen werden können. Es gibt mehrere Leihstationen in der Stadt, an denen ERoller geparkt sind. Für jeden geparkten ERoller wird eine Park-ID generiert. Es gibt verschiedene Fabrikate, wobei jedem ERoller eine ERoller-ID und eine Seriennummer zugeordnet wird. Ein Kunde, der mit seiner Kunden-ID und seinem Namen gespeichert ist, kann einen ERoller ausleihen, indem er an einer Leihstation seine Benutzer-ID eingibt. Für die Ausleihe wird eine Leih-ID generiert.


Schritt 2: ER-Diagramm zeichnen

Das markierte Gesprächsprotokoll ist unübersichtlich. Daher ist es üblich die Entitäten, Eigenschaften und Beziehungen in einem Diagramm darzustellen, dem ER-Modell (Entity-Relationship-Model = ERM). Für die Datenbank ERoller-Start-Up wird ein ERM erstellt.

Es gibt drei Entitäten:

  • Kunde
  • ERoller
  • Leihstation

Die Entitäten haben folgende Eigenschaften:

  • Kunde: Kunden-ID, KName
  • ERoller: ERoller-ID, Seriennummer, Fabrikat
  • Leihstation: Leihstation-ID, LName, LAdresse

Zwischen den Entitäten gibt es folgende Beziehungen:

  • Ein Kunde gibt an einer Leihstation seine ID ein, um einen ERoller auszuleihen
  • Ein ERoller ist an einer Leihstation geparkt

Diese Entitäten, Eigenschaften und Beziehungen können in einem Diagramm wie folgt dargestellt werden. Die unterstrichenen Attribute sollen die eindeutigen Primärschlüssel der Tabellen sein. Zwischen den Beziehungen ist die Zahl "1" und das Symbol "*" eingetragen. Die Bedeutung ist:

  • jeder Kunde kann an einer Leihstation einen ERoller ausleihen, eine Leihstation kann viele Kunden haben
  • jeder Kunde kann genau einen E-Roller ausleihen und jeder E-Roller ist an genau einen Kunden ausgeliehen
  • Ein ERoller ist genau bei einer Leihstation geparkt, an einer Leihstation können viele ERoller geparkt sein

erd 01


Schritt 3: Tabellen erstellen

Auf der Basis des ERM werden Tabellen erstellt, die in der 3. Normalform sein sollten, damit die Anomalieanfälligkeit der Datenbank gering ist. Die Tabellen sollen also folgende Forderungen erfüllen:

  • Datensätze sollen nur atomare Attribute enthalten, bei denen ein Attribut nur eine Information enthält.
  • Nur Attribute, die untrennbar zusammengehören sollen in einer eigene Tabelle gespeichert sein.
  • Daten, die in einer Beziehung zueinander stehen, werden in weiteren Tabellen über Fremdschlüssel miteinander verbunden.

In der Datenbank darf in den Attributnamen nicht das Symbol "-" verwendet werden, da sonst die SQL-Abfragen nicht funktionieren. Daher ersetzen wir das Symbol"-" mit einem Unterstrich "_".

Tabelle Leihstation

Leihstation_ID LName LAdresse
L1 Berliner-Platz Berliner Platz 3
L2 Zoo Tiergartenstraße 5
L3 Bahnhof Bahnhofsplatz 1
L4 ZOB Eckernplatz 5

Tabelle Kunde

Kunden_ID KName
K1 Sporti
K2 Bommi
K3 Pullo
K4 Medda
K5 Awwe
K6 Ubbe

Tabelle Fabrikat

Fabrikat_ID FabrikatName
F1 ElektroSpeed
F2 RollerBlitz

Tabelle ERoller

ERoller_ID Seriennummer Fabrikat_ID
E1 ER19-01 F1
E2 ER19-02 F1
E3 ER19-03 F2
E4 ER19-04 F1
E5 ER20-01 F2
E6 ER20-02 F1
E7 ER20-03 F1
E8 ER20-04 F2
E9 ER20-05 F2
E10 ER20-06 F2

Tabelle Ausleihe

Leih_ID Kunden_ID Leihstation_ID ERoller_ID Datum
Leih_8 K3 L1 E1 15-07-20_14:32
Leih_11 K5 L1 E7 15-07-20_15:15
Leih_12 K1 L3 E9 15-07-20_16:04
Leih_15 K2 L2 E3 15-07-20_17:08

Tabelle Parken

Park_ID Leihstations_ID ERoller_ID Datum
P1 L1 E2 15-07-20_12:00
P2 L2 E4 15-07-20_12:00
P5 L4 E5 15-07-20_13:10
P7 L3 E6 15-07-20_13:30
P9 L3 E7 15-07-20_14:08
P11 L2 E10 15-07-20_15:20

Schritt 4: Datenbank erzeugen

Auf Basis dieser Tabellen kann jetzt im DB Browser for SQLite eine Datenbank erzeugt werden. Die Struktur dieser Datenbank sieht wie folgt aus:

DB 01

mit folgenden Tabellen:

DB 02 DB 03 DB 04 DB 05 DB 06 DB 07

Die Datenbank können Sie herunterladen: eroller.zip


Schritt 5: SQL-Datenbankabfragen erzeugen

Abfrage 1: Es sollen alle ausgeliehenen Elektro-Roller in einer Tabelle ausgegeben werden

Die SQL-Abfrage

    SELECT Ausleihe.Leih_ID, Ausleihe.Datum, ERoller.ERoller_ID, Fabrikat.FabrikatName, Kunde.KName, Leihstation.LName
    FROM Ausleihe JOIN ERoller JOIN Fabrikat JOIN Kunde JOIN Leihstation
    ON  Ausleihe.ERoller_ID = ERoller.ERoller_ID AND ERoller.Fabrikat_ID = Fabrikat.Fabrikat_ID 
            AND Ausleihe.Kunden_ID = Kunde.Kunden_ID AND Ausleihe.Leihstation_ID = Leihstation.Leihstation_ID
    ORDER BY Ausleihe.Leih_ID ASC

liefert:

DB 08


Abfrage 2: Es sollen alle geparkten Elektro-Roller in einer Tabelle ausgegeben werden

Die SQL-Abfrage

    SELECT Parken.Park_ID, Parken.Datum, ERoller.ERoller_ID, Fabrikat.FabrikatName, Leihstation.LName
    FROM Parken JOIN ERoller JOIN Fabrikat JOIN Leihstation
    ON Parken.ERoller_ID = ERoller.ERoller_ID AND ERoller.Fabrikat_ID = Fabrikat.Fabrikat_ID 
        AND Parken.Leihstations_ID = Leihstation.Leihstation_ID
    ORDER BY Parken.Park_ID ASC

liefert:

DB 09


Abfrage 3: Es soll die Anzahl der an einer Leihstation geparkten Elektro-Roller ausgegeben werden

Mit Hilfe sogenannter Aggregatfunktionen können z.B. Anzahlen von Datensätzen ermittelt werden oder die Summe von angegebenen Werten ermittelt werden. Wir verwenden die Aggregatfunktion "COUNT" um die Anzahl der geparkten Elektro-Roller zu ermitteln:

    SELECT COUNT(PARK_ID) FROM Parken

liefert die Anzahl 6:

DB 10


Abfrage 4: Es soll die Anzahl der ausgeliehenen Elektro-Roller ausgegeben werden

Die SQL-Abfrage

    SELECT COUNT(PARK_ID) FROM Parken

liefert die Anzahl 4:

DB 11


Abfrage 5: Es soll geprüft werden, ob die Gesamtanzahl der Elektro-Roller mit der Anzahl der ausgeliehenen und geparkten Elektro-Roller übereinstimmt

Die folgende SQL-Abfrage liefert als Ergebnis 0, denn es wird die Anzahl der ausgeliehenen und die Anzahl der geparkten Elektro-Roller addiert und die Anzahl der vorhandenen davon abgezogen:

    SELECT 
          (SELECT COUNT(Leih_ID) FROM Ausleihe) 
        + (SELECT COUNT(Park_ID) FROM Parken) 
        - (SELECT COUNT(ERoller_ID) FROM ERoller)

Man kann sich auch alle Werte anzeigen lassen, indem man mehrere SQL-Abfragen mit dem Schlüsselwort "UNION" kombiniert:

    SELECT 
          (SELECT COUNT(Leih_ID) FROM Ausleihe) 
        + (SELECT COUNT(Park_ID) FROM Parken) 
        - (SELECT COUNT(ERoller_ID) FROM ERoller) UNION
    SELECT COUNT(Leih_ID) FROM Ausleihe UNION
    SELECT COUNT(Park_ID) FROM Parken UNION
    SELECT COUNT(ERoller_ID) FROM ERoller

Diese Abfrage liefert:

DB 12


Abfrage 6: Es soll die Summe aller Einnahmen aus der Ausleihe berechnet werden

Die Tabelle Ausleihe wird mit einem Feld LGebuehr ergänzt, welches das Format "REAL" hat, so dass Kommazahlen darin gespeichert werden können.

DB 13

Im Attribut LGebuehr sind folgende Leihgebühren eingetragen:

DB 14

Die geänderte Datenbank können Sie herunterladen: eroller2.zip

Die folgende SQL-Abfrage

    SELECT SUM(LGebuehr) FROM Ausleihe;

liefert die Summe der Leihgebühren 5.3 + 7.2 + 1.5 + 2.8 = 16.8:

DB 15


Projekt: Datenbank entwerfen

Aufgabe:

  • Bilden Sie ein Team mit mindestens 3 Teammitgliedern
  • Denken Sie sich eine Anwendungssituation für eine Datenbank aus
  • Entwerfen Sie eine Datenbank mit geeigneten SQL-Abfragen, indem Sie Schritt 1 - 5 durchlaufen
  • Erstellen Sie eine Präsentation mit Besprechungstext, ER-Diagramm, Datenbankentwurf und SQL-Abfragen
  • Erzeugen Sie eine relationale Datenbank mit dem DB Browser for SQLite und demonstrieren Sie in Ihrer Präsentation deren Funktionsfähigkeit

Lassen Sie Ihrer Lehrkraft in der vereinbarten Weise die Projektdateien zukommen.