DB 1

Welcome

Data Models and Languages

Typed Data, Declarativity, Data Independence, Persistence

The Relational Data Model

[Last update: Nov 11, 2019 (minor edits on slide 25)]

Constraints

A Diversion into SQL

Referential Integrity

Database Design

Grouping and Aggregation

Functional Dependencies

[Last update: Dec 30, 2019 (added missing paren on slide 25)]

The Entity-Relationship Model

Relational Algebra

Datenmodell Text

Datenmodell Text

Shell-Skript, basierend auf sed und awk (Datenmodell Text).

Usage (UNIX-Shell): weight-of-set5610.sh < set5610-1.txt

Datenmodell JSON (Nested Arrays and Dictionaries)

Datenmodell JSON

(JSONiq-Query)

Usage (UNIX-Shell): zorba -r -i -f -q weight-of-set5610.jq

(JSONiq-Query)

Usage (UNIX-Shell): zorba -r -i -f -q worst-northern-quake-mag.jq

(JSONiq-Query)

File enthält drei Varianten der Query (zwei Varianten sind derzeit via (: ... :) auskommentiert).

Usage (UNIX-Shell): zorba -r -i -f -q worst-northern-quake-mag-place.jq

Datenmodell Tabular (CSV)

Einfache Query-Library (PyQL und relationale Algebra) für Python 2.7. Einbindung in Python-Code via

from DB1 import Table

Einfache Query-Library (PyQL und relationale Algebra) für Python 3. Einbindung in Python-Code via

from DB1v3 import Table

(PyQL-Query, liest CSV-File earthquakes.csv)

Usage (UNIX-Shell): python worst-northern-quake-mag-place.py

Datenmodell Tabular (CSV)

Datenmodell Tabular (CSV)

Datenmodell Tabular (CSV)

(PyQL-Query)

Usage (UNIX-Shell): python weight-of-set5610.py

(PyQL-Query)

Optimierung basiert auf Regeln (constraints) der LEGO Mini-World: eindeutige Identifier in Tabellen bricks und minifigs sowie Disjunktheit beider Tabellen.

Usage (UNIX-Shell): python weight-of-set5610-key.py

(PyQL-Query)

Optimierung basiert auf Konstruktion einer temporären Datenstruktur (Dictionary/partielle Funktion quantity).

Usage (UNIX-Shell): python weight-of-set5610-temp.py

(PyQL-Query)

Einführung einer temporären Liste pieces als Vereinigung von bricks und minifigs.

Usage (UNIX-Shell): python weight-of-set5610-pieces-list.py

(PyQL-Query)

Basiert auf einer neuen persistenten Tabelle pieces.csv, die in der UNIX-Shell durch folgendes Kommando konstruiert werden kann:

cut -f1-6 bricks.csv | last +2 | cat minifigs.csv - > pieces.csv

Usage (UNIX-Shell): python weight-of-set5610-pieces-table.py

(SQL-Skript)

Kopiert erst Schema und dann Zustand einer existierenden Tabelle, benötigt lediglich je ein DDL- und DML-Statement.

Usage (UNIX-Shell): psql -f insert-query.sql

(SQL-Skript)

Demonstration der SQL DML Kommandos INSERT, UPDATE und DELETE.

Usage (UNIX-Shell): psql -f calendar.sql

(SQL-Skript)

Demonstration des PostgreSQL Foreign Data Wrappers (CSV-File wird in den Zustand einer Relation gespiegelt, read-only: Änderungen im CSV-File werden vom RDBMS übernommen, INSERT/UPDATE/DELETE-Kommandos sind auf die Relation nicht anwendbar).

Achtung: Im SQL-Skript muss der (absolute) Pfad zum CSV-File angepasst werden.

Usage (UNIX-Shell): psql -f fdw.sql

(SQL-Skript)

Reichert Tabelle calendar mit einer Reihe von Constraints an, um die Abbildung der Kalender-Miniwelt in die Datenbank zu verfeinern.

Usage (UNIX-Shell): psql -f calendar-constraints.sql

(SQL-Skript)

Deklariert und lädt die Tabellen der LEGO-Miniwelt und fügt Kandidaten- sowie Primärschlüssel hinzu. NB: Einige SQL-Statements führen zu (erwarteten) Fehlern, siehe Kommentare.

Achtung: Im SQL-Skript müssen die (absoluten) Pfade zu den CSV-Files angepasst werden.

Usage (UNIX-Shell): psql -f keys.sql

(SQL-Skript)

Demonstriert, dass die Nennung von Subqueries in der FROM-Klausel reihenfolgeunabhängig ist.

Usage (UNIX-Shell): psql -f calendar-attendees.sql

(SQL-Skript)

Demonstriert das Konzept der row types und row values in SQL.

Usage (UNIX-Shell): psql -f row-types.sql

(SQL-Skript)

Demonstriert, das für einige Queries die Nennung von DISTINCT überflüssig sein kann, da ohnehin keine Duplikate erzeugt werden. Das RDBMS erkennt die meisten dieser Situationen nicht selbsttätig.

Usage (UNIX-Shell): psql -f distinct-vs-keys.sql

(SQL-Skript)

Demonstriert die Formulierung von Equi- und allgemeiner θ-Joins (Theta-Joins), auch zwischen mehreren Tabellen (hier: 3-Way Join).

Usage (UNIX-Shell): psql -f who-is-busy-at-what-times.sql

(SQL-Skript)

Demonstriert den Einsatz von Subqueries (⟨query⟩) in der Berechnung von Ausdrücken.

Usage (UNIX-Shell): psql -f compositional.sql

(SQL-Skript)

Demonstriert den Einsatz von WITH ... zur Konstruktion komplexerer Queries. Die in WITH gebundenen Namen sind nur lokal sichtbar und sind nicht persistent.

Usage (UNIX-Shell): psql -f large-yellow-bricks.sql

(SQL-Skript)

Demonstriert die Nutzung korrelierter Unteranfragen (die freie Variablen beinhalten, die in der umgebenden Anfrage gebunden werden).

Usage (UNIX-Shell): psql -f correlation.sql

(SQL-Skript)

Demonstriert die Deklaration und den Einsatz von Fremdschlüsseln (foreign keys), um eine konsistente Verweisstruktur zwischen Source- und Target-Tabellen zu gewährleisten.

Achtung: Im SQL-Skript müssen die (absoluten) Pfade zu den CSV-Files angepasst werden.

Usage (UNIX-Shell): psql -f foreign-keys.sql

(SQL-Skript)

Demonstriert die (manuelle) Überprüfung der referentiellen Integrität einer Datenbank mittels der SQL-Prädikate EXISTS und [NOT] IN.

Achtung: Im SQL-Skript muss ein (absoluter) Pfad zu einem CSV-File angepasst werden.

Usage (UNIX-Shell): psql -f referential-integrity.sql

(SQL-Skript)

Repräsentation von Bäumen mittels Fremdschlüsseln innerhalb einer Tabelle (tree). Baum-Operationen führen zu Self-Joins.

Usage (UNIX-Shell): psql -f intra-table-foreign-keys.sql

(SQL-Skript)

Repräsentation von Logo turtle drawing commands mittels Datentyp text. Führt zur Extraktion von Teilinformation mittels regulärer Ausdrücke (Funktionen regxp\_split\_to\_table(), regexp\_matches()).

Usage (UNIX-Shell): psql -f turtle-text.sql

(SQL-Skript)

Repräsentation von Logo turtle drawing commands mittels Arrays von Kommandos (Typ cmd[]). Benutzt tabellen-generierende Funktion unnest() als "Brücke" von Arrays zu Tabellen.

Usage (UNIX-Shell): psql -f turtle-array.sql

(SQL-Skript)

Repräsentation von Logo turtle drawing commands in geschachtelten Tabellen — die resultierende Tabelle ist in Non-First Normal Form (NF²). Nach Entschachtelung wird die NF²-Tabelle durch zwei 1NF-Tabellen shapes und turtles repräsentiert.

Usage (UNIX-Shell): psql -f turtle-1NF.sql

(SQL-Skript)

Übersetzung von Anfragen der hypothetischen "NF²SQL"-Sprache in reguläre SQL-Queries über Bundles von 1NF-Tabellen (hier: shapes und turtles).

Usage (UNIX-Shell): psql -f functions-1NF.sql

(SQL-Skript)

Demonstration verschiedener SQL Aggregat-Funktionen (siehe auch die PostgreSQL 11-Dokumentation zu Aggregat-Funktionen).

Usage (UNIX-Shell): psql -f grouping-aggregation.sql

(SQL-Skript)

Demonstration von Gruppierung und Aggregation im shapes/turtles-Beispiel.

Usage (UNIX-Shell): psql -f grouping-turtle.sql

(SQL-Skript)

Gruppierung und Aggregation auf der Kalender-Datenbank

Usage (UNIX-Shell): psql -f who-is-busy-at-what-times-grouped.sql

(SQL-Skript)

Aufbau eines Mini-Data Warehouse für die LEGO-Datenbank. Einfache Report-Queries.

Usage (UNIX-Shell): psql -f dw.sql

(SQL-Skript)

Weitere Gruppierungen und Aggregationen auf dem LEGO Data Warehouse (Set mit der meisten Teilen, schwerstes Set).

Usage (UNIX-Shell): psql -f lego-aggregates.sql

(SQL-Skript)

Test: Ist die angegebene Attributmenge ein Kandidatenschlüssel für Tabelle contains?

Usage (UNIX-Shell): psql -f candidate-key-test.sql

(SQL-Skript)

Verhalten von Gruppierung und Aggregation auf leeren Tabellen und bei der Verarbeitung von NULL-Werten.

Usage (UNIX-Shell): psql -f grouping-null.sql

(SQL-Skript)

Generiert Tabelle instructions, die einen Auszug einer LEGO-Bauanleitung darstellt. Szenario zur Demonstration von funktionalen Abhängigkeiten.

Achtung: Im Skript müssen Pfade zu den Illustrationen der Bauanleitung angepasst werden. Die Illustrationen selbst befinden sich ich im ZIP-Archiv instructions.zip.

Usage (UNIX-Shell): psql -f instructions.sql

(ZIP-Archiv mit PNG-Bildern)

Enthält die Illustrationen zur LEGO-Bauanleitung in Tabelle instructions. Werden in die Datenbank geladen durch SQL-Skript instructions.sql.

Usage (UNIX-Shell): unzip instructions.zip

(SQL-Skript)

Nutzt einen SQL-Trigger, um die FD rating → stars in Tabelle users zu überwachen. Achtung: Dies ist ineffizient und kein gutes Datenbank-Design.

Usage (UNIX-Shell): psql -f simulate-FD-constraint.sql

(SQL-Skript)

Nutzt den Spalten, die als GENERATED ALWAYS AS IDENTITY deklariert wurden. Stützt sich intern auf SQL-Sequenzen (sequences).

Usage (UNIX-Shell): psql -f identity.sql

(SQL-Skript)

Relationale Implementation eines ER-Diagramms, das eine many-to-one-Relationship zwischen Entities Airport und Country beschreibt.

Usage (UNIX-Shell): psql -f airport-country.sql

(SQL-Skript)

Relationale Implementation eines ER-Diagramms, das Entity-Typ Employee sowie dessen Spezialisierungen Secretary und Engineer beschreibt. Nutzt die SQL-Klausel INHERITS.

Usage (UNIX-Shell): psql -f employee-secretary-engineer.sql

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Demonstriert den Selektions-Operators σ[p](R) der Relationalen Algebra (RA). Das Selektionsprädikat p wird hier in Form eines λ-Ausdrucks als anonyme Funktion angegeben:

lambda t: ...⟨Boolescher Ausdruck, der sich auf Tupel t bezieht⟩ ...

Usage (UNIX-Shell): python selection.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Demonstriert den Projektions-Operators π[ℓ](R) der Relationalen Algebra (RA). Die Projektions-Funktion ℓ kann in zwei Formen angegeben werden:

1. Als anonyme Funktion, die ein Eingabetupel t auf ein Ausgabetupel abbildet (z.B. zur Umbenennung oder Erzeugung neuer Spalten), Beispiel:

   lambda t: { 'X': t['A'], 'Z': int(t['B']) + 42 }

2. Als Liste von Namen von Spalten, die nach der Projektion erhalten bleiben — alle anderen Spalten werden "wegprojiziert"):

   ['A', 'B']

Usage (UNIX-Shell): python projection.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Demonstriert das Zusammenspiel der RA-Operatoren π, σ und ⨉ zur Implementation einer Anfrage über Graphen (findet alle Pfade der Länge 2).

Usage (UNIX-Shell): python two-hops.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Demonstriert die Funktionalität des Natural Join-Operators ⨝ (verbindet zwei Relationen, Join-Prädikat ergibt sich implizit aus der Gleichheit der gleich benannter Spalten).

Usage (UNIX-Shell): python natural-joins.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Findet die LEGO-Steine, die aus einer Kategorie mit Bezug zu "Animal" stammen. Greift auf die Tabellen sets.csv und categories.csv zu.

Usage (UNIX-Shell): python animal-bricks.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Findet die LEGO-Sets, die schwerer als das LEGO-Set "Taj Mahal" sind. RA-Query ist bereits in mehrern Schritten optimiert. Greift auf die Tabelle sets.csv zu.

Usage (UNIX-Shell): python heavy-sets.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Kategorisiert LEGO-Sets nach ihrem Volumen (small, medium, large). Greift auf Tabelle sets zu.

Usage (UNIX-Shell): python set-size-categories.py

(SQL-Skript)

Kategorisiert LEGO-Sets nach ihrem Volumen (small, medium, large). Zwei Varianten nutzen Fallunterscheidung (CASE...WHEN) und Mengenoperationen (UNION ALL).

Usage (UNIX-Shell): psql -f set-size-categories.sql

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Findet die LEGO-Sets, in denen kein Baustein einen Aufkleber erhält. Nicht-monotone Anfrage, demonstriert den Einsatz der relationalen Differenz. Greift auf gekürzte Varianten der Tabellen sets, contains und bricks zu.

Usage (UNIX-Shell): python no-sticker-bricks.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Findet die LEGO-Sets, in nur gelbe Bausteine enthalten sind. Nicht-monotone Anfrage, demonstriert den Einsatz der relationalen Differenz. Greift auf gekürzte Varianten der Tabellen sets, contains sowie auf Tabelle colors zu.

Usage (UNIX-Shell): python all-yellow-bricks.py

(Python-Skript, benötigt Python-Modul DB1 oder Python3-Modul DB1v3)

Findet die LEGO-Sets, die alle Steine beinhalten, die benötigt werden, um das LEGO-Set 1609 zu bauen. Nicht-monotone Anfrage, demonstriert den Einsatz des Divisions-Operators (÷). Greift auf gekürzte Variante der Tabellen sets und contains zu.

Usage (UNIX-Shell): python what-can-we-build.py

Datenmodell Tabular (CSV)

Datenmodell Tabular (CSV)

Datenmodell Tabular (CSV)

Datenmodell Tabular (CSV)