Einführung in Datenbanken: Teil 1

Im Rahmen des Trainee-Programms haben wir bei Marco Lehmann (Fachhochschule OST) einen Workshop zum Thema Datenbanken besucht. Die wichtigsten Erkenntnisse aus dem ersten Teil haben wir für euch hier zusammengefasst.

Relationale Datenbankmanagementsysteme

Relationale Datenbankmanagementsysteme (RDBMS) sind Datenbankserver, die Tabellen und die darin gespeicherten Daten verwalten. Eine Datenbank kann aus mehreren Tabellen (Relationen) bestehen, die wiederum mehrere Spalten (Columns/Attribute) und Zeilen (Rows/Tupel) haben kann. Dabei bezeichnet man eine Auswahl von Spalten als Projektion. Ausserdem unterscheidet man zwischen den beiden Begriffen „Daten“ und „Datenbankstruktur“. Während sich Ersterer auf den Inhalt einer Tabelle bezieht, bezeichnet Letzterer den Aufbau der gesamten Datenbank und die Relationen der unterschiedlichen Tabellen zueinander. Zur Veranschaulichung sehen wir unten zwei Bilder, die die Tabelle „authors“ mit Daten von verschiedenen Buchautoren (links) und die Datenbankstruktur der gesamten Datenbank (rechts) zeigen. Auf diese Datenbank wird nachfolgend in einigen Beispielen zurückgegriffen.

Die Abfragesprache SQL

Die Abfragesprache SQL (Structured Query Language) ist seit vielen Jahren das „Arbeitspferd“ im Umgang mit Daten. Sie wurde speziell für Datenbanken entwickelt, ist (mehr oder weniger) standardisiert und ermöglicht das Arbeiten mit einem Datenbankserver. Es folgt eine Übersicht über die wichtigsten Operationen und dazugehörigen Befehle in SQL. Die Beispiele beziehen sich auf die Datenbank, die im Bild zur Datenstruktur dargestellt ist.

• Daten selektieren

  • Daten selektieren mit SELECT
    Beispiel: Alle Zeilen und Spalten aus der Tabelle „authors“ selektieren

    SELECT * FROM authors

  • Projektionen
    Beispiel: Projektionen – Nur Spalten „au_fname“ und „au_lname“ selektieren

    SELECT au_fname, au_lname FROM authors

  • Spalte mit Alias selektieren
    Beispiel: Spalte mit Alias selektieren

    SELECT au_fname as Vorname FROM authors

• • Daten ohne Duplikate selektieren mit SELECT DISTINCT
    Beispiel: Nur Autoren selektieren, deren Kombination von Vor- und Nachnamen eindeutig ist
    

    SELECT DISTINCT au_fname, au_lname FROM authors

    Beachte: Der Befehl bezieht sich auf die ganze Projektion, das heisst jedes Tupel muss eindeutig sein, nicht die einzelnen Attribute.

• Daten sortieren

  • Daten sortieren mit ORDER BY
    Beispiel: Spalten „au_fname“ und „au_lname“ selektieren und aufsteigend (absteigend) nach „au_fname“ sortieren

    SELECT au_fname, au_lname FROM authors ORDER BY au_fname ASC (DESC)

• Daten filtern

  • Daten filtern mit WHERE
    Beispiel: Alle Autoren mit Vorname Paddy selektieren

    SELECT * FROM authors WHERE au_fname=‘Paddy‘

    Beachte: Der Filter bezieht sich auf die gesamte Tabelle, nicht auf die SELECT-Auswahl

• Aggregieren

  • Aggregieren mit GROUP BY und COUNT, MIN, MAX, AVG, etc.
    Beispiel: Anzahl Autoren pro Staat auflisten

    SELECT state, COUNT(*) FROM authors GROUP BY state

• Daten kombinieren
  • Daten kombinieren mit JOIN: Mit JOIN können Zeilen aus verschiedenen Tabellen kombiniert werden, sofern sie ein gemeinsames Attribut haben, also z. B. den gleichen „primary/foreign key“.
    Beispiel: Verleger des Buches "Exchange of Platitudes" auflisten
    

    SELECT title_name, pub_name FROM titles JOIN publishers ON titles.pub_id = publishers.pub_id WHERE title_name = ‚Exchange of Platitidues‘

Normalformen

Gute, saubere Daten sind eine Voraussetzung für fehlerfreie Anwendungen, gute Wartbarkeit und effiziente Analysen. Um eine Datenbank so zu modellieren, dass keine Redundanzen auftreten, können wir sie in die sogenannte Normalform bringen. Um die Definition der ersten, zweiten und dritten Normalform zu verstehen, müssen wir zuerst folgende Definitionen betrachten:

• Schlüsselkandidat: Eine Menge von Attributen einer Tabelle heisst Schlüsselkandidat, wenn jede Zeile der Tabelle sich durch die Werte dieser Attribute eindeutig identifizieren lässt. Es kann mehrere Schlüsselkandidaten geben.
• Schlüssel: Ein Schlüsselkandidat S heisst Schlüssel, wenn keine echte Teilmenge von S Schlüsselkandidat ist. Ein Schlüssel heisst echt, wenn er nicht aus allen Attributen der Tabelle besteht.
• Primärschlüssel: Von den möglichen Schlüsseln wählt man einen als Primärschlüssel. Alle anderen Schlüssel sind alternative Schlüssel. Primärschlüssel aus mehreren Attributen sind oft unpraktisch, weshalb in der Praxis fast immer jeder Tabelle ein künstlicher Schlüssel (ID) hinzugefügt wird.

Erste Normalform

Eine Tabelle ist in erster Normalform (1NF), wenn alle Attributwerte atomar sind, d. h. nicht mehr weiter aufteilbar sind.

Beispiel: Ein Attribut namens „Adresse“ mit Werten der Form XXXX Ort (z. B. 8001 Zürich) kann aufgeteilt werden in zwei Attribute „PLZ“ und „Ort“.

Folgende Tabelle ist dann in 1NF.

Zweite Normalform

Eine Tabelle ist in zweiter Normalform (2NF), wenn sie in 1NF ist und folgendes zutrifft:

• Jedes Nichtschlüsselattribut (= nicht teil eines Schlüssels) ist jeweils vom ganzen Schlüssel abhängig, nicht nur von einem Teil eines Schlüssels.
• Eine Tabelle ist noch nicht in 2NF, wenn sie einen zusammengesetztem Primärschlüssel hat und ein Nichtschlüssel-Attribut nicht vom ganzen Primärschlüssel, sondern nur von einem Teilschlüssel abhängt.
• Relationen in der 1NF, deren Schlüsselkandidat(en) nicht zusammengesetzt sind, sondern lediglich aus jeweils (einem) einzelnen Attribut(en) bestehen, erfüllen automatisch die 2NF.

Beispiel: Bank hängt nicht vom ganzen Schlüssel {Konto_Nr, BankID} ab, sondern nur vom Teilschlüssel {BankID}.

Die zweite Normalform wird erreicht, indem die Tabelle in diese Entitäten zerlegt wird.

Redundanz wurde entfernt: Raiffeisen kommt nur noch einmal vor.

Dritte Normalform

Die dritte Normalform ist genau dann erreicht, wenn sich das Relationsschema in der 2NF befindet und kein Nichtschlüsselattribut von einem Schlüsselkandidaten transitiv abhängt. Anders ausgedrückt: ein Nichtschlüsselattribut darf nicht von einer Menge aus Nichtschlüsselattributen abhängig sein. Ein Nichtschlüsselattribut darf also nur direkt von einem Primärschlüssel (bzw. einem Schlüsselkandidaten) abhängig sein.

Beispiel: Das Nichtschlüsselattribut „Inhaber_PLZ“ hängt vom PK (primary key) ab, das Nichtschlüsselattribut „Inhaber_Ort“ hängt vom Nichtschlüsselattribut „Inhaber_PLZ“ ab und „Inhaber_Ort“ hängt somit transitiv (indirekt) vom PK ab.

Die 3NF wird erreicht, indem die Relation aufgeteilt wird, wobei die voneinander abhängigen Daten in eine eigene Tabelle ausgelagert werden. Der Schlüssel der neuen Tabelle muss als Fremdschlüssel in der alten Tabelle erhalten bleiben.

Jede der drei Tabellen ist in 1NF, 2NF und 3NF.

Praxistipps zu Normalformen

• Spalten-ID sind die Regel
• Gutes Datenbankdesign beinhaltet 3NF, aber mit Kompromissen:
  • z. B. für Perfomance (weniger JOINs)
  • z. B. redundante, flache Tabelle für häufige Suche

ERM: Entity-Relationship-Model

Der Entwurf der Datenbank spielt bei der Entwicklung von Informationssystemen eine zentrale Rolle. Der erste Analyseschritt beinhaltet die Modellierung der Information. Das sogenannte Entity-Relationship-Model (ERM) ist eine Darstellung, mit der Daten semantisch beschrieben werden können. Diese Beschreibung ist unabhängig von der eingesetzten Technologie; das ERM wird erst in einem nächsten Schritt auf das relationale Modell (RM) abgebildet (siehe nächstes Kapitel).

Ein ERM beantwortet folgende Fragen:

• Welche «Gegenstände» sind für die Applikation wichtig (Entitäten)?
• Welche Daten oder Eigenschaften (Attribute) haben diese Entitäten?
• Wie stehen die Entitäten zueinander in Beziehung (Relation)?
• Welche quantitativen Eigenschaften haben diese Beziehungen (Kardinalitäten)?

An folgendem Diagramm können die Grundbegriffe des ERMs veranschaulicht werden.

Beachte jeweils die spezifische Form des Objekts. Wir können Folgendes definieren:

• Entität: Ein individuelles Exemplar der realen oder der Vorstellungswelt.
• Entitätsmenge: Eine Gruppierung von Entitäten mit gleichen Merkmalen, aber unterschiedlichen Merkmalswerten, hier «Firmen» und «Produkte».
• Beziehung/Relation: Zwischen Entitätsmengen bestehen Beziehungen, hier «Lieferung».
• Attribut: Sowohl Entitätsmengen als auch Relationen können Attribute haben, hier keine dargestellt, normalerweise als Ellipse.
• Rollenmodellierung: Die Entitäten haben in Relationen Rollen, hier «liefert» und «stammt von».
• Kardinalität: Beziehungen betreffen immer eine gewisse Anzahl Entitäten, was durch Kardinalitäten beschrieben wird, hier «1» und «mc». Kardinalitäten bedeuten:
  • c: mindestens 0, höchstens 1
  • 1: mindestens 1, höchstens 1 = genau 1
  • m: mindestens 1, höchstens *
  • mc: mindestens 0, höchstens * = beliebig viele

Vom ERM zum RM

Im Folgenden wird es darum gehen, wie man nach der Informationsmodellierung mit dem ERM, letzteres in eine relationale Datenbank (RM) implementiert. Während die Informationsmodellierung, also das Erstellen des ERMs, anspruchsvoll und nicht immer eindeutig ist, ist die Abbildung desselben in ein relationales Datenbankmodell einfach und kann kochbuchartig erfolgen. Es gelten folgende Regeln:

• Entitätsmenge ⇒ Tabelle: Primärschlüssel (PK) definieren
• Relation ⇒ Tabelle mit Fremdschlüsseln (oft Relationstabelle genannt)
  • Der Primärschlüssel der Relationstabelle bestimmt sich aus den Kardinalitäten:
    • 1:1 ⇒ PK = eine der beiden beteiligten PKs
    • 1:m, 1:mc ⇒ der PK der «m-Entität» wird als PK der Relationstabelle übernommen
    • n:m ⇒ die Relationstabelle erhält einen zusammengesetzten PK aus den beiden beteiligten PKs
  • Bei bestimmten Kardinalitäten kann die Relationstabelle «wegoptimiert» werden.

DB-Programmierung

Datenbanken können nicht nur Daten speichern und SQL ausführen, sondern es kann auch datenbankseitig programmiert werden, beispielsweise um Regeln zu implementieren oder Daten zu normalisieren. Nachfolgend werden einige DB-Objekte erklärt.

Sequenzen (SEQUENCE)

Sequenzen sind Zähler, die bei jedem Aufruf garantiert einen eindeutigen Wert zurückgeben. Beispielsweise wird bei einem INSERT in eine Tabelle mit einem künstlichen Schlüssel der neue Schlüssel aus einer Sequenz eingelesen, wie in folgendem Beispiel dargestellt.

Erstellen eines SEQUENCE

CREATE SEQUENCE SEQ_PK_Club START WITH 1
 
SELECT NEXT VALUE FOR SEQ_PK_Club
SELECT NEXT VALUE FOR SEQ_PK_Club
 
-- DROP SEQUENCE SEQ_PK_Club
 
INSERT INTO Club (id, name, logo) VALUES (NEXT VALUE FOR SEQ_PK_Club, 'Olympique Lyonnais', 'some link')

Views

Eine View ist eine gespeicherte Abfrage (SELECT Statement), welche das Abspeichern und einfaches Wiederverwenden häufig verwendeter Abfragen oder komplizierter JSONs ermöglicht.

Betrachte folgendes SELECT-Statement:

Beispiel eines SELECT-Statements

SELECT s.id, s.name, c.club_name
FROM Spieler AS s INNER JOIN Club AS c ON s.club_id = c.id

Diese kann wie folgt als View gespeichert werden:

Erstellen einer VIEW

CREATE VIEW V_SpielerClub
AS
SELECT s.id, s.name, c.club_name
FROM Spieler AS s INNER JOIN Club AS c ON s.club_id = c.id

Und dann wie ein TABLE angewendet werden:

Verwenden der VIEW

SELECT *
FROM V_SpielerClub
WHERE club_name = 'Olympique Lyonnais'

Beachte, dass Abfragen auf eine View langsam sein können, je nach Komplexität der Abfrage, die sich hinter ihr verbirgt. Beispielsweise kann ein komplexer Join zu einer langsamen Abfrage führen.

Materialized View

Die «materialized» View ermöglicht effizienteres Abfragen als die normale View. Im Gegensatz zur Letzteren, wo nur die Abfrage selbst gespeichert wird, nicht aber die Resultate der Abfrage, speichert die «materialized» View auch die Resultate.

Weitere Datenbank-Objekte

Weitere DB-Objekte sind:

• Index
• Constraints
• Stored Procedure
• Trigger

NoSQL

In vielen Situationen sind RDBMS die richtige Lösung und es braucht keinen NoSQL-Ansatz. Aber:

• In verteilten Systemen ist es schwierig, Transaktionen (ACID) zu implementieren.
• RDBMS sind nicht dafür gemacht, auf verteilten Clustern ausgeführt zu werden.
• OR-Mapping

OR-Mapper werden verwendet, um Objekte einer in einer objektorientierten Sprache geschriebene Anwendung in eine relationale Datenbank zu speichern. Das zugrundeliegende Problem, das beim Verbinden der zwei Welten (OOP und RM) auftritt wird als „object-relational impedance mismatch“ bezeichnet. Bezüglich der Probleme mit verteilten Systemen (Konsistenz, Verfügbarkeit) betrachten wir nachfolgend das CAP-Theorem.

CAP-Theorem

CAP steht für

• C : Consistency 
  • z. B. «read-write-Konfilt/inconsistent read»
  • z. B. «replication inconsistency»
• A: Availability
  • Jeder Node (Knoten, Server) in einem Netzwerk ist erreichbar à Availability meint also: jede Anfrage an ein System bekommt eine Antwort.
  • z. B. stürzt ein Server ab, wird er ohne Fehlermeldung innerhalb eines Timeouts ersetzt.
• P: Partition Tolerance
  • z. B. ein Netzwerk mit mehreren Verbindungen wird durch Unterbrüche in zwei Partitionen verteilt à Partition Tolerance bedeutet, dass das System in jeder Partition weiterarbeitet.

Das CAP-Theorem besagt nun, dass es in einem verteilten System nicht möglich ist, alle drei Eigenschaften gleichzeitig zu garantieren – nur zwei dieser drei Eigenschaften können jeweils gleichzeitig garantiert werden. Es können also beispielsweise folgende Kombinationen auftreten:

• CP
  • z. B. bei Bankautomaten, wo Konsistenz sehr wichtig ist, wird lieber auf Availability verzichtet (Automat ausser Betrieb) als auf Consistency (fehlerhafte Buchungen)
• AP
  • z. B. soziale Medien: lieber immer verfügbar (z. B: Facebook) und dafür werden irgendwo ein paar Likes nicht angezeigt
• CA
  • Per Definition nicht möglich auf verteilten Systemen (nur trivialerweise auf Single-Node-Systemen, die keine Partitionen sehen können)

Laut CAP-Theorem muss man also in jedem verteilten System davon ausgehen, dass Nachrichten verloren gehen oder Netzwerkverbindungen ausfallen. Eine verteilte Anwendung muss immer Partition-tolerant gebaut sein, was einem, wie oben erwähnt, die Wahl zwischen CP und AP lässt.

Entscheidet man sich dabei für AP, kann man die ACID-Eigenschaften von Transaktionen nicht mehr einhalten. Das heisst aber nicht, dass man gar keine Konsistenz mehr hat. So orientieren sich verteilte NoSQL-Systeme an den sogenannten BASE-Eigenschaften. Diese stehen für:

• BA: Basically available
• S: Soft state
• E: Eventually consistent

Nützliche Links und Ressourcen

• W3schools
• SQL Fiddle
• Buch: „SQL & NoSQL Databases“, Andreas Meier & Michael Kaufmann