Datenbanken

🎯 Lernziele

Nach dieser Einheit sind Sie in der Lage dazu * Tabellen nach den tidy data Prinzipien anzulegen * Elemente einer Tabelle zu benennen * Inhalte einer CSV-Datei erfassen

🧠 Tabelle

• geordnete Anordnung von Zeilen und Spalten
  • Zeile (row): waagerecht
  • Spalte (column): senkrecht (z. B. Feld, Parameter, Eigenschaft, Attribut, Stütze);
  • Zelle (cell): Schnittpunkt zwischen einer Zeile und einer Spalte

Quelle

Tidy Data

Happy families are all alike; every unhappy family is unhappy in its own way - Leo Tolstoy

Like families, tidy datasets are all alike but every messy dataset is messy in its own way. Tidy datasets provide a standardized way to link the structure of a dataset (its physical layout) with its semantics (its meaning). - Hadley Wickham

• Viele Funktionen erwarten ordentliche Daten
• Entspricht der Struktur von Datenbanken (Codd’s 3. Normalform)

Quelle

Tidy Data - Beispiel

Quelle

🧠 Tidy Data - Regeln

• Jede Variable steht in einer eigenen Spalte.
• Jede Beobachtung/jeder Fall steht in einer eigenen Zeile.
• Jeder einzelne Wert steht in einer Zelle.

Quelle

🧠 Beispiel CSV-Datei einer EKG-Aufzeichnung

Messwerte (mV), Zeit (ms),
295, 6
295, 8
295, 10
295, 12
295, 14

• Jede Zeile ist eine Zeile
• Spalten werden durch Kommata getrennt
• Es können anstelle von , auch andere Trennzeichen verwendet werden (;, Tabstopp, )

🤓 Tabellendaten in Python

• auch in Python lässt sich gut mit Tabellen arbeiten
• hierzu müssen wir ein besonderes Paket namens pandas laden
• die Tabellen werden in einem besonderen Datentyp namens DataFrame gespeichert

Datenbanken

Quelle

🎯 Lernziele

Nach dieser Einheit sind Sie in der Lage dazu * relationale Tabellen zu beschreiben * sinnvolle Schlüssel für verknüpfte Relationen zu bestimmen * die Atomarität und referenzielle Integrität einer Relation zu bewerten

🧠 Datenbanken

• Nachteile bei Speicherung von (Tabellen-)Dateien:
  • Kein Zugriffsmanagement (vgl. Aufgabe 8)
  • Verknüpfung von verschiedenen Tabellen unklar (Welche Person hat welchem Test betreut?)
• Datenbanken
  • Middleware, die sich um Zugriffsrechte und Korrektheit der Daten kümmert
  • Andere Software kann darauf zugreifen
  • Damit dies funktioniert, müssen die Daten in einer sauberen Struktur vorliegen

🧠 Relationales Datenmodell

• mit Abstand wichtigste und am weitesten verbreitete Datenbankmodell
• Datensätze (Entities) in Tabellen gespeichert und besitzen einen (Relationen-)Namen und enthalten Attribute
• Beziehungen (Relationships) zwischen verschiedenen Datensätzen werden ebenfalls in Tabellen gespeichert (z.B. wer hat welche Telefonnummern?)

🧠 Schlüssel

• Ein Schlüssel(kandidat) (engl.: key) ist ein Attribut oder eine Kombination mehrerer Attribute, um jedes Objekt (Tupel) eindeutig zu identifizieren.
• Ein Primärschlüssel (engl.: primary key) ist ein Schlüssel, der tatsächlich dazu verwendet wird, Tupel in einer Relation eindeutig zu identifizieren. (🔑 oder unterstrichen)

Relationales Datenmodell

• Daten werden als Tabellen mit Beziehungen dargestellt

• Beispielhafte Fragestellungen:
  • Wie muss das Programm vorgehen, wenn sich die Adresse ändert?
  • Wie kommt das Programm an alle Telefonnummern einer Person?

Problem

dies ist keine ordentliche (tidy) Tabelle, da sich eine "Beobachtung" über mehrere Zeilen erstreckt

Lösung

• relationale Datenbank
• mehr als eine Tabelle mit Beziehungen untereinander
  • Person
  • Telefonnummern
  • Email-Adressen
  • Adressen

Tabelle Person

• Wir fügen eine eindeutige ID hinzu, die als Primärschlüssel dient

Tabelle Telefonnummern

• Wir fügen den Primärschlüssel der Tabelle Person als Fremdschlüssel in die Tabelle Telefonnummern hinzu

Tabelle E-mail

Adressen

• Die Adresse wird nur durch nur durch einen zusammengesetzten Schlüssel aus drei Attributen eindeutig bestimmt

✍️ Aufgabe

Erstellen Sie die Relationen (Tabellen) zu den hier hervorgehobenen Objekten. Kennzeichnen Sie geeignete Primärschlüssel durch unterstreichen und fügen Sie in beide Relationen ein Beispiel ein? - Jede Person hat einen Vor- und Nachnamen, ein Geburtsdatum und E-Mail. - Den Leistungstest sind je eine Kennziffer, Datum, Dauer, und Leistung.

⌛ 10 Minuten

Lösung

• Person

• Leistungstest:

Was sind geeignete Schlüssel?

• Person: 🔑 e-mail kommt nur einmal vor.
• Wohnadresse: 🔑 Kennziffer

Verbindungen zwischen Relationen

• Nun möchten wird Adressen speichern, um Rechnungen zu senden
• Jede Wohnadresse wird von nur einer Person bewohnt. Jede Person hat genau eine Wohnung.
• Wie lässt sich abbilden, dass eine Person eine Wohnung bewohnt?
• Wohnadresse:

• Die Verbindung wird durch das Einfügen des Primärschlüssels der Relation Person (hier Person.e-mail) als Fremdschlüsseln in die Relation Wohnadresse realisiert.
• Damit ist eindeutig bestimmt welche Person die jeweilige Adresse bewohnt.

Mehrfachbeziehungen

• Mehrere Personen in einer Wohnung

• Mehre Wohnungen pro Person

• Person:

• Wohnadresse:

Naiver Lösungsansatz

• Wohnadresse wird um eine Spalte person.e-mail erweitert
• Wohnadresse:

• Was bei der nächsten Mieterhöhung?
• Gefahr von Inkonsistenzen
• Wohnung mit Hausnummer 2 kommt zwei mal vor

Sinnvolle Lösung: Relationstabellen für Mehrfachbeziehungen

• Person: Bleibt gleich
• Bewohnt:

• Wohnadresse:

Was tun, wenn es keinen passenden Schlüssel gibt?

• Wohnadresse:

• Festlegung einer einzigartigen ID (Identifikationsnummer)

• Wohnadresse:

🧠 Atomarität

• Folgendes ist nicht erlaubt, da es die Atomarität verletzt:

Person:

• Eine wichtige Eigenschaft von relationalen Datenbanken ist, dass die Tabellenstruktur nicht verschachtelt ist
• Tabellenzellen sind atomar, enthalten genau einen Wert und keine Listen (Auch pandas funktioniert so am besten!).
• Jeder einzelne Wert steht in einer Zelle.

🧠 Atomarität

Um mehrere Telefonnummern zu hinterlegen würde man eine weitere Relation anlegen:

• Person:

• Telefonnummer:

Referenzielle Integrität

• bedeutet, dass alle Einträge auf die referenziert wird (Fremdschüssel) tatsächlich existieren
• dies ist besonders wichtig, wenn Einträge gelöscht werden

Person:

Telefonnummer: (Primärschlüssel TelNr.; Sekundärschlüssel: e-mail)

• In der Tabelle Telefonnummer ist person.e-mail ein Fremdschlüssel, da es auf e-mail in der Tabelle Person referenziert.

• Alle Ausprägungen von person.e-mail in der Tabelle Telefonnummer müssen auch als Werte in der Tabelle Person als e-mail auftreten, andernfalls enthält der Fremdschlüssel in Telefonnummer einen ungültigen Wert.

• Wird ein Tupel von Person gelöscht, so müssen auch alle referenzierenden Tupel gelöscht (oder geändert) werden, andernfalls ist die referenzielle Integrität verletzt und der Fremdschlüssel verweist auf ein nicht mehr existierendes Tupel.

Entity-Relationship-Diagramm

🎯 Lernziele

Nach dieser Einheit sind Sie in der Lage dazu * Entity-Relationship-Diagramme zu nutzen, um Sachverhalte zu modellieren * Relationen der dritten Normalform hieraus abzuleiten

Wie baut man funktionierende Relationen?

• Wir sind bis jetzt davon ausgegangen, dass Relationen bereits gebildet wurden.
• Um in der Praxis eine Datenbank zu entwickeln, wird der Zusammenhang zunächst mit einem Entity-Relationship-Diagramm modelliert.
• Für die Bildung von Relationen aus einer Menge von Attributen wird das Konzept der Normalisierung angewendet.
• Anhand dieser kann dann die Datenbank mittels SQL erstellt werden.
• Bei der Normalisierung (engl.: normalization) eines relationalen Schemas werden die Attribute derart auf Relationen verteilt, dass beim Einfügen, Löschen oder Ändern von Datensätzen keine Inkonsistenzen auftreten.

🧠 Schritt 1: Entity-Relationship-Diagramm

Aus einem ER-Diagramm kann auf automatisierte Weise durch sechs Regeln ein Relationenschema in dritter Normalform erzeugt werden

• Entitätstypen (als Rechtecke visualisiert) werden zu Tabellen. Der Name der Tabelle entspricht dem Namen des Objekttyps.
• Attribute jedes Entitätstyps (als Ovale visualisiert) werden den entsprechenden Tabellen zugeordnet. Sie repräsentieren die Spalten einer Tabelle.
• Beziehungen (Relationen) zwischen Entitätstypen werden als Rauten gekennzeichnet und sinnvoll benannt

• Multiplizitäten lesen Sie sich wie beim UML Klassendiagramm "von den Entitäten weg": Ein Produkt hat genau einen Preis. Ein Preis kann bei verschiedenen Produkten eingetragen sein.
• zwei Schreibweisen
  • 1 eine, n bzw. * beliebig viele
  • <von>...<bis> z.B. 0...3

🧠 Beispiel Person und Wohnung

🧠 Elemente des ER-Diagramm

• ⬛ Entität
• ⚪ Attribute an einer Entität
• 🔹 Beziehung zwischen zwei Entitäten

✍️ Aufgabe

Beschreiben Sie den folgenden Sachverhalt für eine Gebäudeverwaltung, die mehrere Gebäude betreut und diese verwalten will in einem ER-Diagramm mit geeigneten Multiplizitäten. Benutzen Sie nur atomare Schlüssel. Sie können dafür eigene IDs einführen

• Ein Gebäude kann aus einem (z.B. eine Halle) oder mehreren Räumen bestehen
• Es gibt Türen zwischen Räumen und nach außen
• Ein Gebäude hat eine Adresse bestehend aus Straße, Hausnummer, Postleitzahl und Stadt. Auf einer Adresse kann nur genau ein Gebäude stehen und umgekehrt.

⌛ 20 Minuten

Lösung

🧠 Schritt 2: Ableitung von relationalen Schemata aus ER-Diagrammen

• Die identifizierenden Attribute werden zu Primärschlüsseln.
• Mehrwertige Attribute werden auf eigene Tabellen abgebildet. Dabei werden der Primärschlüssel des Objekttyps und das mehrwertige Attribut selbst zu den Spalten dieser neuen Tabelle (z.B. Adresse).
• Um 1:1-Beziehungen (Beziehungen werden generell als Raute visualisiert) abzubilden, muss der Primärschlüssel eines der beteiligten Entitätstypen als Fremdschlüssel in die Tabelle des anderen Entitätstyps aufgenommen werden. Über den Fremdschlüssel lassen sich Datensätze dieser beiden Tabellen in Beziehung setzen.

🧠 1:1-Beziehungen

könnte man auch der anderen Relation zuschlagen

🧠 1:n-Beziehungen

Die zweite Möglichkeit könnte zu Inkonsistenzen führen

🧠 n:m-Beziehungen

🧠 Schritt 2: Ableitung von relationalen Schemata aus ER-Diagrammen

• Bei 1:n-Beziehungen wird der Primärschlüssel des Entitätstyps auf der durch „1“ gekennzeichneten Seite als Fremdschlüssel in die Tabelle des anderen Entitätstypen (auf der mit „n“ gekennzeichneten Seite) aufgenommen. Zusätzlich werden Attribute, die der Beziehung zwischen den beiden Entitätstypen direkt zugeordnet sind, ebenfalls in die Tabelle des Entitätstypen auf der „n-Seite“ der Beziehung aufgenommen. (z.B. Person mit vielen TelNr)
• Bei n:m-Beziehungen zwischen Entitätstypen wird jeweils eine eigene Tabelle gebildet. Der Tabellenname entspricht hierbei dem Beziehungsnamen. Die Attribute dieser Tabelle sind die Primärschlüssel der an der Beziehung beteiligten Entitätstypen, sowie, falls vorhanden, die der Beziehung direkt zugeordneten Attribute.

Normalformen

• Implizites Ziel unser bisherigen Überlegungen ist es die Daten in die sogenannte 3. Normalform zu bringen
• wir kennen diese bereits aus den tidy-data-Prinzipien

Quelle

Schritt 3: 0. Normalform

• gegeben wenn alle Informationen in einer Tabelle vorhanden

Quelle

Schritt 4: 1. Normalform

• 0.NF + alle Informationen in einer Tabelle atomar vorliegen
• jede Information hat ihre eigene Tabellenspalte
• hier wird beispielsweise der Name aufgespalten

Quelle

Abhängigkeiten

• \(\to\): bestimmt eindeutig
• \(B\) ist funktional abhängig von \(A\)
• \(C\) ist transitiv abhängig von \(A\)

Funktionale Abhängigkeit

• \(A \to B\)
• \(B\) is funktional abhängig von \(A\), wenn für jeden Wert von \(A\) höchstens ein Wert von \(B\) auftreten kann

• Die Personenzahl is abhängig von der Raumnr.
• es können aber auch andere Räume die gleiche Personenzahl haben

(Identifikation)schlüssel oder Kandidat für Primärschlüssel

• Ein Attribut \(A\) für das gilt: Jedes Attribut der Tabelle ist von \(A\) funktional abhängig

• nur die Raumnummer bietet sich als Schlüssel an

Zusammengesetzte (Identifikation)schlüssel

• kein Attribut des Schlüssels \(A\) darf von einem der übrigen Attributen des Schlüssel funktional abhängig sein (sonst ist dieses überflüssig)

• Für eine Raumnr. könnte es verschiedene Gebäudenr. geben.
• Für eine Gebäudenr. gibt es verschiedene Raumnr.
• Also ist Raumnr., Gebäudenr. ein valdier Schlüssel

Volle Funktionale Abhängigkeit

• kein Attribut \(B\) ist nur von einem Teil der Attribute des Schlüssels \(A\) abhängig
• Gegenbeispiel. Das Baujahr hängt nur an der Gebäudenr.

• Das Baujahr ist nicht voll funktional abhängig vom kombinierten Schlüssel Raumnr. und Gebäudenr..

Transitive (indirekte) Abhängigkeit

Raum

• (Raumnr., Gebäudenr.) \(\to\) Personenzahl
• Personenzahl. \(\to\) Raumkategorie
• Aber nicht Raumkategorie \(\to\) (Raumnr., Gebäudenr.)
• Raumkategorie ist transitiv abhängig von (Raumnr., Gebäudenr.)

Schritt 5: 2. Normalform

• 1.NF (Atomarität) + jedes Attribut, das nicht Teil des Schlüssel ist, ist von jedem Bestandteil des Schlüssels abhängig (volle funktionale Abhängigkeit)
• meist schon Ergebnis des ER-Diagramms

Verletzung der 2. Normalform

Raum

• Nur Raumnr. und Gebäudenr. sind gemeinsam der Schlüssel
• Das Gebäude-Baujahr ist alleine von der Gebäudenr. abhängig

2. Normalform herstellen

Raum

Gebäude

• keine unnötigen "doppelten" Einträge

Schritt 6: 3. Normalform

• 2.NF + kein Attribut, das nicht Teil des Schlüssel ist, von diesem transitiv abhängig

• jedes Nicht-Schlüssel-Attribut muss direkt vom Schlüssel abhängen

• Hinweis: Es gibt weitere Normalformen, die in der Praxis nur eine untergeordnete Rolle spielen

Verletzung der 3. Normalform

Raum

• Die Raumkategorie hängt nicht am Schlüssel, sondern an der Personenzahl
• Die Raumkategorie is transitiv abhängig vom Schlüssel

3. Normalform herstellen

Raum

Raumkategorien

✍️ Aufgabe

Erstellen Sie die Relationen zu folgenden ER-Diagramm. Stellen Sie sicher, dass diese in der dritten Normalform vorliegen. Finden Sie zusätzliche Attribute, die Sie einsetzen könnten, um die 2. bzw. 3. NF zu verletzen.

• Patient: 🔑 SVRN, Nachname, Geburtsdatum
• Krankenversicherung:
  🔑 Kürzel, Name
• Krankheit: 🔑 Name
• Diagnose: 🔑 Patient.SVNR, 🔑 Krankheit.Name, 🔑 Datum

Checkliste 3 Normalform

• 0. NF: Tabelle?
• 1. NF: Alles atomar?
• 2. NF: Volle funktionale Abhängigkeit vom 🔑?
  • 2. NF: Nur bei Diagnose zusammengesetzter Schlüssel und keine Nicht-Schlüssel Attribute (Gegenbeispiel: zusätzlich Lateinischer Krankheitsname)
• 3. NF: Keine transitiven Abhängigkeiten?
  • 3. NF: Nur bei Patient mehr als ein Nicht-Schlüssel Attribut. Keine Abhängigkeiten zwischen Nachname und Geburtsdatum (Gegenbeispiel: zusätzliches Attribut Alter)

SQL-Befehle (Entfällt 2025)

🎯 Lernziele

Nach dieser Einheit sind Sie in der Lage dazu - SQL-Befehle ausführen, um Sachverhalte aus Datenbanken abzufragen - sich selbstständig über komplexe SQL-Befehle zu informieren, um mit Datenbanken zu interagieren

Abfragen aus Datenbanken (Tabellen)

• Wichtige Typen von Operationen sind:
  • Selektion (engl.: selection): Auswahl einer Untermenge aller Tupel einer Tabelle
  • Projektion (engl.: projection): Auswahl einer Untermenge der Attribute einer Relation
  • Verbund (engl.: join): Verknüpfung von Tabellen anhand selektierter Attribute

🧠 SQL-Befehle

SELECT <attribute> FROM <relation>

SELECT und FROM sind Schlüsselwörter der SQL-Sprache und werden deswegen großgeschrieben.

SELECT * FROM performance_data # Gibt die gesamte Tabelle sql_performancedata aus

performance_data ist der Name der Tabelle/Relation von der wir abfragen wollen. das * ist eine sogenannte Wildcard und bedeuted, dass wir alle Attribute der Tabelle ausgeben wollen.

🧠 Selektion

Auswahl einer Untermenge aller Tupel einer Tabelle. Die Selektion wird durch zusätzliche Schlüsselwörter hinter der Auswahl der Tabelle (FROM performance_data) angefügt.

Beispielsweise lässt sich die Ausgabe auf fünf Einträge begrenzen:

SELECT * FROM performance_data LIMIT 5
# gibt die ersten 5 aus

🧠 Filtern

Eine besondere Art von Selektion ist das Filtern Beim Filtern wird das Schlüsselwort WHERE genutzt. Darauf folgt eine logische Abfrage z.B. bezüglich des Attributs smoker ob dieses den Wert "0" enthält.

SELECT * FROM performance_data WHERE smoker="0"
# Gibt alle Nichtraucher aus

🧠 Projektion

Durch eine Projektion werden einzelne Attribute aus einer Tabelle abgefragt. Hierzu werden die Attribute hinter der SELECT genannt und mit einen Komma voneinander abgetrennt.

SELECT gender FROM sql_performancedata 
# Gibt gender aller Einträge aus

SELECT gender, VO2_max FROM sql_performancedata 
# Gibt gender und VO2_max aus

🧠 Verbund / Join

• Oft sind Informationen über mehrere Tabellen verteilt, da dies durch die Normalisierung passiert
• z.B. wenn hier der Preis eines Produktes gefunden werden soll (INNER JOIN)

Hierzu müssen die beiden Tabellen in der SQL-Abfrage verbunden werden

SELECT Produkt.ProduktNr, Preis.Betrag FROM Produkt JOIN Preis ON Produkt.Preisgruppe = Preis.Preisgruppe

🤓 Verschiedene SQL JOIN

• (INNER) JOIN: Gibt Elemente zurück, die Werte in beiden Tabellen haben
• LEFT (OUTER) JOIN: Gibt alle Werte aus der linken Tabelle und die Treffer aus der rechten Tabelle zurück.
• RIGHT (OUTER) JOIN: Gibt alle Werte aus der rechten Tabelle und die Treffer aus der linkten Tabelle zurück.
• FULL (OUTER) JOIN: Git alle Einträge zurück, auch wenn es keine Treffer gibt

Quelle

🤓 Manipulation von Daten

• Natürlich können auch Daten erstellt

  INSERT INTO tips (total_bill, tip, sex, smoker, day, time, size)
  VALUES (16.99, 1.01, Female, No, Sun, Dinner, 2);
  

• oder gelöscht werden

  DELETE FROM tips WHERE smoker="No" 
  

• oder Tabellen angelegt werden

  CREATE TABLE table_name (
      column1 datatype,
  ....
  );
  

  Quelle

✍️ Freiwillige Übungs-Aufgabe

• Übung im Google Colab

⌛ 45 Minuten

Zusatzmaterial: - Machen Sie sich mit dem Beispiel auf der folgenden Website vertraut - Lösen Sie die Aufgaben - Select 1-3 - Where 1,3 - Order By 1 - Wildcards - Delete 1 - Ausführliches SQL Tutorial

🏆 Sakai-Aufgabe 9: Datenbanken

• Arbeiten sie im bereits erstellen Repository weiter
• Während die Personen mit Kontaktdaten in einer zentralen Datenbank gepflegt werden, die über eine REST-API verwaltet werden, sollen Leitungstest in einer lokalen SQLite-Datenbank test.db gespeichert werden
• Orientieren Sie sich am existierenden Code in main.py und schreiben Sie zunächst ein Skript, create_db.py, das alle nötigen Tabellen in der Datenbank erstellt, hierzu müssen Sie nur den Code aus der main.py übernehmen und anpassen. Führen Sie den Code aus, um Ihre Datenbank zu erstellen.

🏆 Sakai-Aufgabe 9: Datenbanken (2/3)

• Passen Sie dann die Klassen wie folgt an. Ein Experiment speichert den Vornamen von Versuchsperson und Versuchsleiter. Zudem besteht eine Experiment aus mehreren Stufen, bei denen für eine bestimmte Dauer in Sekunden eine bestimmte Leistung in Watt gefahren wird (ggf. bietet sich hierfür eine neue Klasse Step an - vgl. Stufentest). Diese lassen sich über eine Methode add_step() hinzufügen.

🏆 Sakai-Aufgabe 9: Datenbanken (3/3)

• Zudem gibt es eine Methode store(), welche neue Experimente und deren Stufen in der Datenbank speichert und bzw. bestehende Einträge überschreibt. Achten Sie darauf, dass dabei nacheinander verschiedene Tabellen in der richtigen Reihenfolge bearbeitet werden.
• Zudem gibt es eine Methode print_steps(), die alle Informationen zu den Stufen eines Experiment aus der Datenbank abfragt und ausgibt

🏆 Sakai-Aufgabe 9: Datenbanken (3/3)

• schreiben Sie eine db_test.py-Datei, die die Funktionalität der Klassen testet indem ein neues Experiment angelegt wird, welches zwei Stufen enthält und printen Sie die Informationen zu diesem Experiment nachdem Sie aus der Datenbank abgefragt haben.

performance_test_1 = PerformanceTest(1,"Supervisor","Subject")

performance_test_1.add_step(1, 60, 100)
performance_test_1.add_step(2, 60, 150)
performance_test_1.store_in_db()
performance_test_1.print_steps()
# Ausgabe
#Steps:
#Step 1: 60s 100W
#Step 2: 60s 150W