Hier wird eine ungefähre Übersicht gegeben, wann welcher Stoff behandelt werden soll. Im Laufe des Semesters ist beabsichtigt, die Liste zu aktualisieren, und den tatsächlich behandelten Stoff einzutragen. Selbstverständlich sind alle Angaben unverbindlich.
Tatsächlicher Ablauf:
- ...
Planung (unverbindlich, wird noch aktualisiert):
Themen der Vorlesung, Motivation, Organisatorisches, Prüfung über Hausarbeit und Kurzklausur, Studienleistung: Hausaufgaben, Literatur, Software: PostgreSQL, Bedeutung der Portabilität, Ausblick auf mögliche Bachelor-Arbeiten.
Geschichte von PostgreSQL, Erläuterung zur Client-Server Architektur, psql (Kommandozeilen-Schnittstelle) als Client, pgAdmin, Starten und Stoppen des Servers, "Database Cluster", Datenbanken, Nutzer ("Rollen") und Datenbanken anlegen, Nutzer-Authentifizierung, Schemata, public Schema, Suchpfad für Schemata, Zugriffsrechte für Schemata, Verbindung zum Server mit psql (Parameter: Host-Rechner, Port, Datenbank, Nutzer), Unterschied Unix-Domain Socket vs. TCP/IP, wichtigste psql Kommandos, Unterschied zwischen psql Kommandos (beginnen mit \, enden mit Zeilenumbruch) und SQL-Befehlen (mit ";" abzuschließen), PostgreSQL Data Dictionary, Standard "Information Schema".
Bedeutung der Portabilität. Wiederholung: Template-Datenbanken von PostgreSQL, Nutzer/Rollen, Nutzer-Authentifizierung (pg_hba.conf), Schemata, Schema-Suchpfad, Sitzungen sind mit nur einer Datenbank verbunden. psql Kommandozeilenschnittstelle. Adminer ist ein anderer Client zum PostgreSQL-Server, da funktionieren die speziellen psql-Befehle nicht. Data Dictionary. Editieren der Eingabe, GNU Readline Bibliothek, Historie. SQL-Skripte, ausführen mit \i, psql Variablen, \echo zur Ausgabe, Ergebnisse in Dateien schreiben mit \o, \qecho. Ausgabe-Formatierung, \pset tuples_only on, \pset format unaligned, Ausgaben im HTML oder LaTeX Format.
Beispiel-Datenbank, Vergleich von Relation mit Java-Klasse, Wiederholung zu Grundbegriffen des relationalen Modells: Nullwerte, Schlüssel, Fremdschlüssel, Schema-Kurznotation, CREATE TABLE-Statement. Datentypen: CHAR(n), CHAR, VARCHAR(n), Datentypen für lange Zeichenketten, Datentyp TEXT in PostgreSQL, NUMERIC(p,s), NUMERIC(p), DECIMAL(p,s), INT, SMALLINT, BIGINT, Vor- und Nachteile von INT vs. NUMERIC(p), FLOAT(p), REAL, DOUBLE PRECISION.
Wiederholung zu INT vs. NUMERIC, Wiederholung zu Geitkomma/Fließkommazahlen, Fließkomma-Zahlen ("approximate numeric types") nicht für Geldbeträge verwenden! Datumsangaben: DATE, TIME, TIMESTAMP, Konstanten vom DATE-Typ, Intervalltypen, DATE in Oracle (intern mit Uhrzeit), Hinweis auf Dokumentation verschiedener DBMS, Syntax des CREATE TABLE-Statements, Tabellen-Constraints vs. Spalten-Constraints, PRIMARY KEY impliziert NOT NULL, aber aus Prortabilitätsgründen sollte man NOT NULL mit aufschreiben (eventuell auch klarer). Andere implizierte Constraints führen zu Punktabzug. CHECK-Constraints, Einschränkung der Bedingung, Begründung über effiziente Integritätsprüfung. Constraint-Namen. Fremdschlüssel, Einschränkungen bei MySQL (man wähle Tabellen-Constraint-Syntax und gebe Spalten des referenzierten Schlüssels explizit an). ON DELETE CASCADE, weitere Optionen zu ON DELETE/ON UPDATE. DEFAULT-Spaltenwerte, Anwendung: Mitloggen des Nutzers, der die Zeile angelegt hat, auch aktuelles Datum/Uhrzeit beim Einfügen, Hinweis auf Beteiligung des Betriebsrats beim Loggen solcher Daten. Temporäre Tabellen. Generierung eindeutiger Zahlen (IDs), GENERATED ALWAYS AS IDENTITY, Sequenzen, AUTO_INCREMENT in MySQL, Typen SERIAL und BIGSERIEL in PostgreSQL. Hinweis auf CREATE SCHEMA, DROP TABLE, ALTER TABLE.
Beispiel: Direkte/indirekte Vorgesetzte in der EMP-DEPT-Datenbank, Hinweis auf Einschränkung der relationalen Algebra und damit auch von klassischem SQL: Transitive Hülle kann nicht berechnet werden. Durch die rekursiven Sichten geht es in modernem SQL. WITH-Klausel, WITH RECURSIVE, Lösung der Aufgabe im Beispiel (indirekte Vorgesetzte), Erläuterung zur Abarbeitung rekursiver Abfragen. UNION vs. UNION ALL. Terminierung. Unterstützung rekursiver Anfragen in verschiedenen DBMS. Weitere Anwendungen von Rekursion, "Bill of Materials" Problem. Einschränkungen der relationalen Algebra (nicht berechnungsuniversell, nicht unbedingt ein Nachteil: Halteproblem, polynomielle Komplexität von Relationenalgebra-Anfragen). Syntax der WITH-Klausel. Nur lineare Rekursionen.
Wiederholung zu rekursiven Anfragen: Verschiebung der Grenze zwischen Datenbank-Abfragen und Programmen (transitive Hülle war vorher nicht möglich), Diskussion zu Terminierung und Halteproblem. Syntax der WITH-Klausel. Beschränkung von SQL auf lineare Rekursion. Beschränkung in PostgreSQL, dass nur der letzte UNION-Teil rekursiv. Beispiele: Zahlen bis 100 berechnen, Fakultätsfunktion mit SQL berechnen. Datalog, Regeln. Transitive Hülle in Datalog.
Wiederholung zu LIKE. Stilhinweis: Nicht LIKE verwenden, wenn es = auch getan hätte. SIMILAR TO, Syntax und Semantik von regulären Ausdrücken nach dem SQL-Standard, Erweiterungen in SQL-2003, Beispiele. Reguläre Ausdrücke in PostgreSQL. Mustervergleich in anderen Systemen. Reguläre Ausdrücke in Funktionen: SUBSTRING.
Einführung in Data Warehouses, Beispiel: Anwendungen, z.B. Verkaufszahlen. Charakteristika von OLAP-Datenbanken vs. OLTP-Datenbanken, Multidimensionale Daten: "Data Cube".
Wiederholung zu OLAP vs. OLTP, Datenmodell "Data Cube", hierarchische Strukturierung der Dimensionen, Typische Operationen mit Data Cubes, mathematische Darstellung von multidimensionalen Daten. Darstellung von multidimensionalen Daten als Tabelle: Stern-Schema (Fakten-Tabelle, Dimensions-Tabellen). Kurzer Einschub: NULLS FIRST, NULLS LAST. Fortgeschrittene Gruppierung: ROLLUP, CUBE, GROUPING SETS.
Top-N Anfragen, Klassische Lösung ohne spezielles Konstrukt (ziemlich aufwändig). Was macht man bei Gleichstand? FETCH FIRST in SQL:2008. Unterstützung in verschiedenen DBMS. Anmerkung zur Performance (Nutzung des Wissens, dass sich der Nutzer nur für wenige Zeilen interessiert, durch den Optimierer). Seiteneinteilung von Anfragen: OFFSET. Warnung vor Nichtdeterminismus. Performance von OFFSET. Syntax-Alternativen für FETCH FIRST: LIMIT (MySQL). MS SQL Server: SELECT TOP(n). Oracle: ROWNUM.
Kurze Wiederholung zu Top-N Anfragen, ROWNUM bei Oracle. Window Functions/Analytic Functions: RANK() OVER(ORDER BY ...), DENSE_RANK, ROW_NUMBER, PERCENT_RANK, CUME_DIST, PARTITION BY, Verwendung von Aggregationsfunktionen mit OVER, Unterschied zu GROUP BY, Syntax der Window Specification, Unterstützung in verschiedenen DBMS, Implizite und Explizite Fenster im OVER-Konstrukt, mögliche Auswertung. Beispiel: Glättung von Aktienkursen. Benannte Fenster: WINDOW-Klausel.
INSERT, INSERT INTO ... VALUES, Empfehlung: Spalten bei INSERT in Programmen explizit angeben, INSERT mit Anfrage, DELETE, Undo-Möglichkeiten in SQL und in Oracle (ROLLBACK, autocommit, Point-in-time-Recovery, Flashback Query in Oracle), UPDATE, TRUNCATE, MERGE (kurz).
Wiederholung zu TRUNCATE. MERGE, "ON CONFLICT"-Klausel beim INSERT in PostgreSQL, "RETURNING"-Klausel bei Updates in PostgreSQL. Laden großer Datenmengen, CSV-Format, Daten-Import/Export bei PostgreSQL (COPY-Befehl, \COPY-Befehl in psql). Transaktionen. ACID-Merkregel für Transaktionen: Atomarität, Dauerhaftigkeit, Isolation, Konsistenz (Consistency). Exkurs zur Wichtigkeit von Backups (Kontrollieren Sie, dass die Backups wirklich lesbar sind! Trennen Sie die Backup-Medien vom Server-Rechner: Denken Sie an Hacker, die Sie erpressen wollen, und vielleicht länger unerkannt Administrator-Rechte haben. Lagern Sie Backups in einem anderen Gebäude, denken Sie an Brände!). Typische Beispiele für Transaktionen. Diskussion eines Stromausfalls, während ein COMMIT läuft. COMMIT, ROLLBACK, Autocommit Modus. START TRANSACTION.
Illusion exklusiven Zugriffs auf ganze DB: "Ein-Terminal-Betrieb", Gründe für verschachtelte/parallele Abarbeitung von Transaktionen. Zielkonflikt: Isolation vs. Leistung. Fehler aufgrund störender gleichzeitiger Transaktionen werden beim Testen wahrscheinlich nicht gefunden und sind nicht reproduzierbar. Praktisches Ausprobieren der Mehrbenutzer-Fähigkeiten eines DBMS. Beispiel, bei dem eine Transaktion auf eine Sperre warten muss. Sperren (X-Locks, S-Locks), Deadlock. Dirty Read Problem.
Motivation: Beispiel für parallele DB-Zugriffe im Webshop. Wiederholung: Isolation, serialisierbarer Schedule, Sperren, Deadlocks. Dirty Read Problem, Lösung mit Sperren und mit Multi-Version-Concurrency-Control. Exkurs: MVCC-Implementierung bei PostgreSQL. Lost Update Problem, Lost Updates sind bei heutigen DBMS nicht möglich, wenn der ganze Update mit einem UPDATE-Befehl ausgeführt wird, aber schon mit getrenntem SELECT des alten Werts und UPDATE mit neuem Wert. SELECT ... FOR UPDATE. Diskussion über Vermeidung von "Lost Updates" bei formularbasierten Web-Interfaces. Nonrepeatable Read Problem, Inconsistent Analysis Problem, Phantom Problem, LOCK TABLE, Isolationsstufen in SQL. Sperrtypen für Tabellen in PostgreSQL, Sperren auf verschiedenen Ebenen: Intent-Sperren, Vergröberung von Sperren ("Lock Escalation").
Theoretisches Modell für Transaktionen als Folge von read- und write-Anweisungen, Schedules, semantische Serialisierbarkeit, Konflikt-Serialisierbarkeit, Test mittels Konfliktgraph.
Wiederholung zum theoretisches Modell für Transaktionen als Folge von read- und write-Anweisungen. Beispiele zur Konflikt-Serialisierbarkeit und zum Konflikt-Graphen. Kurzer Ausblick auf Zwei-Phasen-Sperrprotokoll.
Datenbanksprache vs. Programmiersprache, Turing-vollstäp;ndige Sprachen haben immer das Halteproblem, Motivation: Aufteilung einer Anwendung auf SQL und Programmiersprache. Kurzer Überblick über verschiedene Sprachen und Werkzeuge zur Entwicklung von Datenbank-Anwendungsprogrammen. U.a. kurze Erklärung zu Embedded SQL in Programmiersprachen. Techniken zur Entwicklung von Web-Schnittstellen, Hinweis auf Java Servlets, PHP. Mögliche Probleme an der Schnittstelle zwischen Datenbank und Programmiersprache. Lösungsvorschläge. Hinweis: Lassen Sie die Datenbank ihren Job machen! Programmieren Sie nicht in Java nach, was die Datenbank besser machen kann. Jede Client-Server-Kommunikation kostet Zeit.
JDBC: "Java Database Connectivity", Beziehung zu ODBC, SQL/CLI. Diskussion zur Austauschbarkeit von Datenbanken (Entkopplung von DB und JDBC-Anwendung). Hinweis auf "Embedded" Datenbanken (HSQLDB, Apache Derby). Typen von JDBC-Treibern. Verbindung zur Datenbank: Database Connection URL. Erstes Beispiel-Programm: Test der Datenbank-Verbindung. Diskussion zu Passworten im Programm. Klassen DriverManager (Methode getConnection), Connection (Methode createStatement), Statement (Methoden executeQuery, executeUpdate), ResultSet (Methoden next, getString, getInt, ...). Zweites Beispiel: Einfache Anfrage. Nullwerte. Spezielle Datentypen: BigDecimal, Datumstypen (java.sql.Date, java.util.Date, java.util.Calendar, SimpleDateFormat), Time, TimeStamp. Prepared Statements. Vorteile von Prepared Statements gegenüber dem Zusammensetzen von Anfragen mit Konstanten direkt in der Anfrage. Methoden prepareStatement, setInt etc., executeQuery und executeUpdate.
Beispiel mit try-with-resources. Hinweis auf Syntax für Classpath unter Windows: -cp ".;pg.tar" (Trennung mit Semikolon, "..." nötig). Wiederholung zu Prepared Statements. Vorteile von Prepared Statements. Diskussion zur Speicherung von Passworten in Datenbanken, Hash von Passwort plus "Salt" und "Pepper", Erläterung zu "Rainbow Tables". SQL Injection (mit Beispielen). Anwendungsprogramme sollten nicht unter dem DBA-Account "postgres" laufen, sondern nicht zu viel Rechte für die Datenbank haben. Der Linux-Nutzer "postgres" (unter dem der DB-Server läuft) sollte nicht viele Rechte für das Betriebssystem haben. Zusammenfassung: Niemals Eingabedaten in SQL-Statements kopieren, Eingabedaten nur als Parameterwerte von "Prepared Statements" verwenden! Noch ein Angriff: Wenn Hacker unsere DB-Inhalte ändern kann, kann er dort JavaScript Schadcode einfügen. Klären Sie, welche Spalten der Datenbank HTML enthalten (möglichst wenige) und schicken Sie alle anderen Zeichenketten vor der Ausgabe durch eine Funktion, die HTML-Spezialzeichen maskiert (z.B. < in < übersetzen). JDBC und Transaktionen. ResultSets und Updates des aktuellen Tupels. Abfrage von Metadaten über JDBC (z.B. Anzahl, Namen, Datentypen der Ergebnisspalten eines ResultSet). Hinweis auf Projekt zur automatischen Korrektur von SQL-Anfragen.
Einleitung/Motivation für gespeicherte Prozeduren (z.B. Updates nur über gespeicherte Prozeduren, so Sicherstellung komplexer Integritätsbedingungen). Sprachen für gespeicherte Prozeduren in verschiedenen DBMS, Hinweis auf SQL/PSM (Persistent Stored Modules). Erweiterung der Sprache SQL mit neuen Datentypen und Funktionen. Vergleich mit Unterprogramm-Bibliothek (DBMS kennt SQL-Anweisungen in gespeicherten Prozeduren, Unterprogrammbibliothek ist ausserhalb der DB). CREATE FUNCTION in PostgreSQL, zunächst Sprache SQL für Rumpf der Funktion. $$-Notation (bzw. $tag$) für Zeichenketten (nützlich für Funktionsrumpf, wenn dort auch Zeichenketten '...'). Rumpf von Funktionen in der Sprache SQL, Rückgabewert. Parameter. Mögliche Namenskonflikte mit Spaltennamen. Funktionen im Systemkatalog. Parameter-Typen, Typ-Umwandlungen, Problem mit NUMERIC-Spalten (keine automatische Umwandlung von NUMERIC(3) nach INTEGER). Hinweis: Bei PostgreSQL ist der Datentyp eigentlich "NUMERIC", der Zusatz "(3)" ist nur eine Art Integritätsbedingung. Nullwerte als Funktionsparameter, Option STRICT bei der Funktionsdeklaration. Rechte bei der Funktionsausführung: SECURITY DEFINER. Seiteneffekte (Updates in FUNCTIONEN), Problem bei Funktionsaufrufen in WHERE. Optionen: IMMUTABLE/STABLE/VOLATILE. Funktionen werden als Teil der Transaktionen der aufrufenden Anfrage ausgeführt (kein COMMIT/ROLLBACK in Funktionen). Tabellen-Funktionen (u.a. Vergleich mit Sichten).
Wiederholung zu gespeicherten Prozeduren in PostgreSQL (u.a. Aufruf von Funktionen mit Updates/Seiteneffekten, $$-Notation für Strings, Typ NUMERIC vs. NUMERIC(3), STRICT, IMMUTABLE/STABLE/VOLATILE). Tabellen-Funktionen, Datentypen in PostgreSQL, TABELLE.SPALTE%TYPE, SETOF Datentypen, Zeilentypen, Ausgabe-Parameter und RETURNS, Default-Werte für Parameter, "Positional Notation" vs. "Named Notation" für Parameter. Sprache PL/PgSQL: Einordnung, Beispiel Finonacci-Funktion, RAISE NOTICE, Beispiel: Geld von einem Konto abheben ohne Kontoüberziehung, nicht existierende Tabellen werden erst bei der Ausführung gemeldet, nicht beim CREATE FUNCTION, Blöcke in PL/pgSQL, Variablen-Deklarationen, Wertausdrücke wie in SQL, Zuweisungen, PERFORM, Hinweis auf CREATE PROCEDURE seit PostgreSQL 11, IF ... ELSIF ... END IF, CASE, Schleifen: WHILE ... LOOP ... END LOOP, FOR-Schleife für Zahlbereiche, LOOP END LOOP mit EXIT WHEN ... im Rumpf, RETURN, RETURN NEXT für mengenwertige Funktionen, SQL UPDATE-Anweisungen in PL/pgSQL, SELECT INTO, Status-Abfragen mit Variable FOUND, FOR-Schleife für Anfrage-Ergebnisse, SELECT-Anfragen mit Cursor über Anfrage-Ergebnis, WHERE CURRENT OF Cursor, Fehlerbehandlung: Exceptions, Auswirkung von Exceptions auf Transaktionen, Exception-Handler: BEGIN ... EXCEPTION WHEN ... THEN ... END, automatische Savepoints am Beginn von Blöcken.
Wiederholung zur Fehlerbehandlung in PL/pgSQL: Diskussion zu Exceptions während der Auswertung der WHERE-Klausel, Entsprechung zu try ... catch ... in Java, Savepoint beim BEGIN. Beispiel: SELECT STRICT INTO, Exception NO_DATA_FOUND. RAISE NOTICE, RAIS EXCEPTION.
Trigger sind ECA-Regeln (Event, Condition, Action), Typische Anwendungen von Triggern, Hinweis auf INSTEAD OF Trigger für sonst nicht updatebare Sichten, Terminierung und Konfluenz, Beispiel: Lager-Haltung mit automatischer Nachbestellung bei Unterschreiten des Minimalbestands, Bestandteile einer Trigger-Definition: Name, Tabelle, Auslösendes Ereignis (Operation), Granularität (Zeilen-Trigger vs. Anweisungs-Trigger), Zeitpunkt der Ausführung (BEFORE oder AFTER), Bedingung (WHEN), Tupelvariablen OLD und NEW in Zeilentriggern, Aktion (Trigger-Funktionen). Hinweis auf Wichtigkeit von RETURN NEW in Triggerfunktionen von BEFORE-Triggern (mögliche automatische Korrekturen durch Änderung von NEW). Sicherbarer Zustand der Datenbank in Triggern. DROP TRIGGER Name ON Tabelle. Informationen zu Triggern in Systemkatalog. Automatisch generierte Trigger für Fremdschlüssel. Integritäts-überwachung mit Triggern. Beispiel: "Die für eine Aufgabe vergebene Punktzahl (in BEWERTUNGEN) darf nicht größer sein als die Maximal-Punktzahl für die Aufgabe (in AUFGABEN)." SQL-Anfragen zu Formalisierung und Test von Integritätsbedingungen. Bestimmung kritischer Updates für eine Integritätsbedingung. Trigger-Funktionen für das Beispiel (mit RAISE EXCEPTION). Weitere Anwendungen: Update-Propagierung, Mit-Loggen von Änderungen (AUDIT). Hinweis auf Arbeitnehmer-Rechte beim Logging.
Beispiel zur Motivation: Dauer für Full-Table-Scan bei großer Tabelle. Diskussion über benötigten Speicherplatz für VARCHAR(n) und NUMERIC(p,s). Welchen Sinn hat die Grenze n bei VARCHAR(n)?
- 13. Montag, 11.07.2022:
| Prof. Dr. Stefan Brass | Impressum |