13.1.3 Datenbank implementieren

In der folgenden Übung sollen Sie eine Bücherei-Datenbank entwickeln. Das Problem bei der Beschäftigung mit Datenbanken ist oft, dass kaum Daten für die Befüllung der Datenbank zur Verfügung stehen. Diese selbst einzutippen verbietet sich aus Zeitgründen und aufwendig Daten aus öffentlichen Datenbanken aufzubereiten ist mühsam.

Zum Glück gibt es nette ProgrammiererInnen, die Tools zur Verfügung stellen, um Testdatenbanken mit zufällig generierten Daten zu befüllen. Wir verwenden für die folgenden Übungen das Tool Mockaroo. Sie finden es auf folgender Internetseite: https://www.mockaroo.com/. Um alle Funktionen nutzen zu können, müssten Sie sich ein kostenloses Konto auf der Seite anlegen. Sie können aber auch die fertig generierten Dateien weiter unten einfach herunterladen. Entscheiden Sie selbst.

Bevor Sie sich der Bücherei-Datenbank widmen, sollen Sie anhand einer anderen Datenbank die Funktionsweise des Datenbankdatengenerators kennenlernen und die generierten Daten nutzen, um eine Datenbank anzulegen. Als Einstieg wird eine Kunden-Rechnungen-Datenbank angelegt.

Melden Sie sich bei mockaroo an und erstellen Sie ein Datenbankschema, das wie folgt aussieht:

In dieser Tabelle werden zufällige Kundendaten generiert. Wenn Sie das Schema entsprechend der Abbildung angelegt haben, klicken Sie auf "Preview". Sie sehen dann die Tabelle "kunden", die mit 1000 zufälligen Datensätzen befüllt ist. Laden Sie diese Tabelle herunter, indem Sie als Format "CSV" wählen und dann auf "Download Data" klicken.

In einem zweiten Schritt soll eine Tabelle "rechnungen" angelegt werden und jede Rechnung mit einem Kunden-Datensatz verknüpft werden. Dazu muss in jeder Rechnung als Kundennummer eine Kundennummer eingefügt werden, die in der Tabelle "kunden" vorhanden ist. Mit Hilfe von Mockaroo können die zufällig generierten Tabellen verknüpft werden.

Nachdem Sie die generierte Datei "kunden.csv" heruntergeladen haben, wechseln Sie in Mockaroo auf Datasets und laden diese Datei wieder hoch. Sie sollten folgendes sehen:

Legen Sie ein neues Tabellen-Schema "rechnungen" an. Um die beiden Tabellen zu verbinden fügen Sie ein Attribut "kundennummer" ein, das vom Typ "Dataset Column" ist und fügen Sie aus der importierten Datei "kunden.csv" die Spalte "kundennummer" als Fremdschlüssel in die Tabelle "rechnungen" ein. Das Schema sollte wie folgt aussehen:

Laden Sie die Tabelle "rechnungen" als Datei "rechnungen.csv" herunter.

Die beiden von Mockaroo erzeugten Tabellen sollen jetzt in eine SQL-Datenbank eingefügt werden. Falls Sie die Datendateien nicht selbst mit Mockaroo erzeugen wollten/konnten, können Sie diese hier herunterladen:

• kunden.csv
• rechnungen.csv

Gehen Sie dann wie folgt vor:

• Starten Sie den DB Browser wie im letzten Kapitel erklärt und legen Sie eine neue Datenbank "StartUp" an.
• Importieren Sie über "Datei - Import - Tabelle aus CSV-Datei" die Datei "kunden.csv"
• Importieren Sie über "Datei - Import - Tabelle aus CSV-Datei" die Datei "rechnungen.csv"

Im DB Browser sollte folgende Datenbankstruktur zu sehen sein:

Jede Tabelle soll einen Primärschlüssel haben, über den jeder Datensatz eindeutig identifiziert werden kann. In der Tabelle "kunden" soll das Attribut "kundennummer" der Primärschlüssel sein, in der Tabelle "rechnungen" soll der Primärschlüssel das Attribut "rechnungsnummer" sein. Die Primärschlüssel können Sie wie folgt festlegen:

• Klicken Sie mit der rechten Maustaste auf die Tabelle "kunden", dann auf "Tabelle verändern" und legen Sie die "kundennummer" als Primärschlüssel fest (NN, PK)
• Klicken Sie mit der rechten Maustaste auf die Tabelle "rechnungen", dann auf "Tabelle verändern" und legen Sie "rechnungsnummer" als Primärschlüssel fest (NN, PK)

Der Primärschlüssel "kundennummer" aus der Tabelle "kunden" ist als Fremdschlüssel in der Tabelle "rechnungen" festgelegt. Damit ist es möglich, mit einer geeigneten SQL-Abfrage über die Kombination von Fremd- und Primärschlüssel für einen Kunden alle Rechnungen herauszusuchen, die zu diesem Kunden gehören:

SELECT kunden.vorname, kunden.nachname, rechnungen.rechnungsnummer, rechnungen.rechnungsdatum, rechnungen.rechnungsbetrag
FROM kunden JOIN rechnungen 
ON kunden.kundennummer = rechnungen.kundennummer
WHERE kunden.kundennummer = "10"

Die Abfrage wurde zusätzlich eingegrenzt, indem nur die Rechnungen herausgesucht werden, die zur Kundennummer "10" gehören:

Sie können in der SQL-Abfrage auch andere Suchkriterien festlegen, z.B. den "Kundennamen":

SELECT kunden.vorname, kunden.nachname, rechnungen.rechnungsnummer, rechnungen.rechnungsdatum, rechnungen.rechnungsbetrag
FROM kunden JOIN rechnungen 
ON kunden.kundennummer = rechnungen.kundennummer
WHERE kunden.nachname = "Raynes"

Die Abfrage liefert:

Nachdem Sie gelernt haben, wie Sie zufällig generierte Datenbankdaten anlegen können, sollen Sie das Gelernte anwenden, indem Sie eine Bücherei-Datenbank entwickeln. Um die Schwierigkeit etwas zu erhöhen, soll die Datenbank aus 3 Tabellen bestehen.

• Erzeugen Sie mit Mackaroo 3 Tabellen für die Datenbank einer Bücherei. Die Tabellen sollen die folgenden sein: "kunden", "buecher", "ausleihe".

In der Tabelle "kunden" soll die kundennummer der Primärschlüssel sein, in der Tabelle "buecher" die isbnnummer und in der Tabelle "ausleihe" die leihnummer.

In der Tabelle "ausleihe" sollen in einem Datensatz die kundennummer und die isbnnummer als Fremdschlüssel eingetragen sein.

• Erzeugen Sie SQL-Abfragen, mit der folgende Daten ausgegeben werden:
  • alle jemals ausgeliehenen Bücher
  • alle jemals ausgeliehenen Bücher eines Kunden
  • die Kunden, die ein bestimmtes Buch ausgeliehen haben
  • die Kunden, die zwei bestimmte Bücher ausgeliehen haben

Hinweise:

• Die ISBN-Nummer ist eine weltweit einzigartige Nummer, die einem Buch zugeordnet ist. In Mockaroo können Sie diese automatisiert erzeugen lassen: "Basic - ISBN".
• Der Buchtitel kann in Mockaroo durch die Aneinanderreihung von Zufallsworten erzeugt werden: "Basic - Words". In den Eigenschaften: "at least 1 but no more than 5" (Buchtitel mit 1 - 5 Wörtern).
• Denken Sie daran für die Ausleihe die erzeugten Tabellen "kunden" und "buecher" wieder zu importieren, um die Fremdschlüssel in die Tabelle "ausleihe" einzufügen.
• Drei Tabellen können gleichzeitig abgefragt werden, indem Sie in der SQL-Abfrage zwei Mal "JOIN" hintereinander setzen.

Bearbeiten Sie die Aufgabe bitte selbstständig und vergleichen Sie Ihre Lösung mit dem folgenden Lösungsvorschlag. Bei der Erzeugung von automatisch generierten Daten wurden in diesem Lösungsvorschlag teilweise fortgeschrittene Techniken verwendet.

Schema "kunden":

Schema "buecher":

Schema "ausleihe":

Erzeugte Tabellen:

• kunden_bib.csv
• buecher_bib.csv
• ausleihe_bib.csv

Datenbank "buecherei":

Abfrage 1: Alle ausgeliehene Bücher

SELECT kunden.vorname, kunden.nachname, buecher.buchtitel, ausleihe.ausleihdatum
FROM ausleihe JOIN kunden JOIN buecher
ON ausleihe.kundennr = kunden.kundennummer AND ausleihe.isbn = buecher.isbn

Nach "SELECT" werden die Felder aus den 3 Tabellen angegeben, die ausgegeben werden sollen. Mit "FROM" und "JOIN" verknüpft man die Tabellen, aus denen die Daten geholt werden sollen. Nach "ON" werden die Fremdschlüssel mit den Primärschlüsseln verknüpft: "ausleihe.kundennr" ist der als Fremdschlüssel in die Tabelle "ausleihe" eingetragene Primärschlüssel "kunden.kundennummer" aus der Tabelle "kunden", "ausleihe.isbn" ist der als Fremdschlüssel in die Tabelle "ausleihe" eingetragene Primärschlüssel "buecher.isbn" aus der Tabelle "buecher". Dann werden jeweils die aus den 3 Tabellen zusammengehörenden Attribute zu einem Datensatz zusammengesetzt. Die Abfrage liefert folgende Datensätze:

Abfrage 2: Alle von einem Kunden ausgeliehenen Bücher

SELECT kunden.vorname, kunden.nachname, buecher.buchtitel, ausleihe.ausleihdatum
FROM ausleihe JOIN kunden JOIN buecher
ON ausleihe.kundennr = kunden.kundennummer AND ausleihe.isbn = buecher.isbn
WHERE kunden.nachname = "Gudge"

Mit der Abfrage 'WHERE kunden.nachname = "Gudge"' wird die Auswahl der ausgegebenen Datensätze begrenzt auf die Datensätze der Kundin "Gudge".

Abfrage 3: Wer hat ein bestimmtes Buch ausgeliehen

SELECT kunden.vorname, kunden.nachname, buecher.buchtitel, ausleihe.ausleihdatum
FROM ausleihe JOIN kunden JOIN buecher
ON ausleihe.kundennr = kunden.kundennummer AND ausleihe.isbn = buecher.isbn
WHERE buecher.buchtitel = "luctus et"

Mit der Abfrage 'WHERE buecher.buchtitel = "luctus et"' wird die Auswahl der ausgegebenen Datensätze begrenzt auf die Datensätze des Buches "luctus et".

Abfrage 4: Wer hat bestimmte Bücher ausgeliehen

SELECT kunden.vorname, kunden.nachname, buecher.buchtitel, ausleihe.ausleihdatum
FROM ausleihe JOIN kunden JOIN buecher
ON ausleihe.kundennr = kunden.kundennummer AND ausleihe.isbn = buecher.isbn
WHERE buecher.buchtitel = "luctus et" OR buecher.buchtitel = "vestibulum"

Mit der Abfrage 'WHERE buecher.buchtitel = "luctus et" OR buecher.buchtitel = "vestibulum"' wird die Auswahl der ausgegebenen Datensätze begrenzt auf die Datensätze der Bücher "luctus et" oder "vestibulum".