13.1.6 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

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

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

Einschub: Tabellen aus ERM erstellen

Gegeben sei beispielsweise folgendes ERM:

Auf Basis dieses Modells soll ein relationales Datenbankschema entwickelt werden. Dabei gelten folgende Regeln:

• Jede Entität erhält eine eigene Tabelle mit den entsprechenden Attributen.
• Eine n:m-Beziehung muss eine eigene Tabelle erhalten.
• Eine n:1-Beziehung
  • kann eine eigene Tabelle erhalten oder
  • der Primärschlüssel der 1-Kardinalität (hier Lieferant) wird als Fremdschlüssel in der n-Kardinalität (hier Artikel) eingetragen.

relationales Datenbankschema Möglichkeit 1:

Praesentkorb(PK_ID, Bezeichnung)
Artikel(A_ID, Name, L_ID)
Lieferant(L_ID, Name, Telefonnummer)
enthaelt(PK_ID, A_ID, Anzahl)

relationales Datenbankschema Möglichkeit 2:

Praesentkorb(PK_ID, Bezeichnung)
Artikel(A_ID, Name)
Lieferant(L_ID, Name, Telefonnummer)
enthaelt(PK_ID, A_ID, Anzahl)
liefert(L_ID, A_ID)

Zurück zum Projekt:

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:

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

Tabelle Kunde

Tabelle Fabrikat

Tabelle ERoller

Tabelle Ausleihe

Tabelle Parken

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

mit folgenden Tabellen:

Die Datenbank können Sie herunterladen: eroller.zip

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:

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:

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:

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:

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:

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.

Im Attribut LGebuehr sind folgende Leihgebühren eingetragen:

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:

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.