Vorlesung #8 Wiederholung: Referentielle Integrität/ Embedded SQL

1. Vorlesung #8Wiederholung: Referentielle Integrität/Embedded SQL

2. „Fahrplan“ • Wiederholung • Beziehungstypen 1:1, 1:n, n:m • Primärschlüssel, Fremdschlüssel, referentielle Integrität • Dynamische Intergritätsbedingungen - „Trigger“-Konzept von Oracle • Prozedurale Erweiterungen (Embedded SQL) • Beispiel Oracle PL/SQL • Einbettung von SQL in Wirtssprachen, C, C++ • ODBC – Open Database Connectivity • JDBC – Java Database Connectivity • SQLJ – Einbettung von SQL in Java • QBE – Query by Example • Fazit und Ausblick Vorlesung #9 Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

3. Relationales Modell • ... besteht aus Relationen bzw. Tabellen, die zueinander in Relation stehen • Relation = Beziehung • Relation: • Synonym für Tabelle und • Synonym für Beziehung • umgangssprachlich: Tabellen stehen in einer Beziehung zu einander (Relationen stehein in Relation zu einander ) • Das relationale Modell beschreibt die Beziehung zwischen zwei Tabellen (Relationen) mittels referentieller Integrität Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

4. Beziehungstypen • Zwei Tabellen T1, T2. Beziehungstypen • 1:1, 1:n, n:m • m:1 entfällt, da analog zu 1:n • 1:1 Ehemann <-> Ehefrau, • 1:n Mannschaft <-> Spieler • n:m Studenten <-> Vorlesungen • (* Notation Prof. Kemper, die meiste Literatur) Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

5. Beziehungstypen (fortgesetzt) • Notation Prof. Schwenkert • c steht für „conditional“, d.h „ein“ oder „kein“ • Eigentlich 10 statt 16 Funktionalitäten, da Einträge oberhalb der Diagonale analog zu Einträgen unterhalb der Diagonale ist Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

6. Schlüsselbegriff • Primary Key – PK – Primärschlüssel • identifiziert eindeutig eine Relation • kann aus mehreren Attributen (Spalten) bestehen  zusammengesetzter Primärschlüssen • Beispiel: Relation Personen • Personal_ID - einfacher PK • (Vorname, Nachname, Geburtsort und Geburtsuhrzeit) – zusammengesetzter PK • Foreign Key – FK - Fremdschlüssel • zeigt auf die Primärschlüsselspalte in der Referenztabelle • sorgt für referentielle Integrität Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

7. 1:1 Beziehungen • Beispiele: • Ehemann <-> Ehefrau • Fußballverein <-> Trainer (*an einem Spieltag) • können in eine einzige Relation bzw. Tabelle zusammengefasst werden • Stufe 1 (3 Tabellen) • Männer <-> Ehen <-> Frauen • FK  zusammengesetzter PK  FK • Stufe 2 (2 Tabellen): • Männer <-> Ehefrauen • FK  PK • Stufe 3 (1 Tabelle): Eheleute • Zusammengesetzter PK Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

8. 1:n Beziehungen • Beispiele: • Mutter <-> Kinder • Fußballverein <-> Spieler • können in zwei sinnvolle Relationen (sinnvoll = ohne Redundanz) bzw. Tabellen zusammengefasst werden • Stufe 1 (3 Tabellen): • Mütter <-> Mutter_Kind <-> Kinder • FK  zusammengesetzter PK  FK • Stufe 2 (2 Tabellen): Mütter <-> Kinder • PK  FK • Stufe 3 (1 Tabelle): Kinder (Redundanz – sämtliche Informationen über Mutter wiederholt sich pro Kind!!!) Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

9. n:m Beziehungen • Beispiele: • Menschen <-> befreundet_mit <-> Menschen (rekursiv, Self-Join) • Fußballvereine <-> spielen_in <-> Gaststadien • können nicht sinnvoll zusammengefasst werden, man braucht immer eine Zuordnungsrelation • Fußballvereine <-> spielen_in <-> Gaststadien • FK  zusammengesetzter PK  FK Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

10. Dynamische Integritätsbedingungen • Statische Integritätsbedingungen: PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE etc. • Dynamische Integritätsbedingungen: Bedingungen an Zustandsänderungen bzw. Datenänderungen werden mittels Trigger überprüft. Man kann mit Triggern auch nachträgliche bzw. zusätzliche Berechnungen durchführen. • CREATE TRIGGER – eigenständiger Datenbankobjekt der einer Tabelle zugeordnet wird, d.h. auf eine Tabelle „aufpasst“ • Auslöser wird definiert (before update, for each row) • Bedingungen gefolgt von Anweisungen Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

11. Trigger in Oracle Syntax create or replace trigger keineDegradierung before update on Professoren for each row when (old.Rang is not null) begin if :old.Rang = 'C3' and :new.Rang = 'C2' then :new.Rang := 'C3'; end if; if :old.Rang = 'C4' then :new.Rang := 'C4'; end if; if :new.Rang is null then :new.Rang := :old.Rang; end if; end; Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

12. Trigger in Oracle Syntax (2) Vereinfacht – nur DML Events, man kann auch DDL abfangen CREATE [OR REPLACE] TRIGGER [schema .] trigger { BEFORE | AFTER | INSTEAD OF } { DELETE | INSERT | UPDATE [OF column [, column]...] } [FOREACHROW] [WHEN ( condition ) ] { pl/sql_block | call_procedure_statement } Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

13. Trigger in DB2 Syntax Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

14. Prozedurale Erweiterung von SQL – Oracle PL/SQL • Trigger waren bzw. sind bereits prozedural! • Prozedurale Erweiterung ist notwendig • Aus theoretischer Sicht – um Touring-Vollständigkeit zu erreichen • Aus praktischer Sicht - manche Probleme des Alltags, lassen sich einfacher prozedural statt deklarativ lösen • Hauptbegriff: Datenbank Cursor • Dynamisches SQL in PL/SQL – würde hier den Rahmen sprengen Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

15. Oracle PL/SQL • Prozedurale Erweiterung von SQL • Beispiel: Funktion Summe - rekursiv CREATE FUNCTION Summe1 (n INTEGER) RETURN INTEGER IS BEGIN IF n = 0 THEN return 0; ELSIF n = 1 THEN return 1; ELSIF n > 1 THEN return n + Summe1(n - 1); END IF; END; Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

16. Oracle PL/SQL (2) • nicht rekursiv, schlau FUNCTION Summe3(n INTEGER) RETURN INTEGER IS BEGIN RETURN (n*(n+1))/2; END; • nicht rekursiv FUNCTION Summe2(n INTEGER) RETURN INTEGER IS result INTEGER DEFAULT 0; v_n INTEGER DEFAULT n; BEGIN WHILE v_n >= 0 LOOP result := result + v_n; v_n := v_n - 1; END LOOP; return result; END; Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

17. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

18. Oracle PL/SQL Beispiel • man verfügt über eine Tabelle (oder eine View), die zu jeder Spiel-Saison einen oder mehreren Trainer eines Vereins beinhaltet • Man möchte für jeden Trainer Saison-Intervale bestimmen • In SQL sehr umständlich, mit vielen „unsauberen“ Hilfskonstrukten • Besser mit einer einfachen Schleife, die sich Zustandsübergänge merkt • Wie bringt aber man aber Daten aus einer Tabelle in eine prozedurale Sprache hinein? Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

19. Deklarativ vs. Prozedural (2) Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

20. Deklarativ vs. Prozedural (3) • Zeitintervall jedes Trainers • Idee • Sortiere nach der Saison • Merke den Trainer • Bei jeder weiteren Saison überprüfe, ob sich der Trainer geändert hat, d.h. vergleich den aktuellen Trainer mit dem Trainer der Vorsaison • Wenn ja, dokumentiere die Änderung • Wiederhole bis zur aktuellen Saison • Cursor-FOR oder Cursor-WHILE Schleife  vorgestellt in der Vorlesung Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

21. Cursor Beispiel (3) ... CURSOR c_club_trainer_saison IS SELECT saison, trainer, verein FROM club_trainer_saison ORDER BY saison; ... BEGIN FOR curvar IN c_club_trainer_saison LOOP ... v_trainer := curvar.trainer; ... END LOOP; ... * Vollständiger Code wird Online gestellt  Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

22. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

23. Einbettung in Wirtssprachen „Embedded SQL“ Mit Hilfe eines Präcompilers!!! #include <stdio.h> exec sql begin declare section; varchar user_passwd[30]; int exMatrNr; exec sql end declare section; exec sql include SQLCA; main() { printf("Name/Password:"); scanf("%", user_passwd.arr); Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

24. Einbettung in Wirtssprachen „Embedded SQL“ (2) user_passwd.len=strlen(user_passwd.arr); exec sql wheneversqlerror goto error; exec sql connect :user_passwd; while (1) { printf("Matrikelnummer (0 zum beenden):"); scanf("%d", &ecMatrNr); if (!exMatrNr) break; exec sql delete from Studenten where MatrNr= :exMatrNr; } exec sql commit work release; exit(0); Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

25. Einbettung in Wirtssprachen „Embedded SQL“ (3) error:exec sql whenever sqlerror continue;exec sql rollback work release;printf("fehler aufgetreten!\n");exit(-1);} Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

26. Datenbankanbindungen • ODBC (Open Database Connecticity) • Anbindung von Windows-Programmen • Beispiel –Excel Zugriff auf Oracle • JDBC (Java Database Connectivity) • Eigentlich ein CLI Call-Level Interface • SQLJ • Echte Einbettung von SQL in Java, wie C, C++ • Wichtig bei allen Schnittstellen: immer an die Voreinstellungen denken • was passiert bei einem Fehler • gibt es automatisch einen COMMIT oder ROLLBACK Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

27. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

28. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

29. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

30. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

31. Vorlesung #8 - Wiederholung Referentielle Integrität / Embedded SQL

32. Vorlesung #8Ende