Datenbanken

1. Datenbanken IFB 2002 Klaus Becker

2. Teil 1 Relationale Datenbanken

3. Zielsetzung Das Burggymnasium Kaiserslautern möchte seine Schulbibliothek auf EDV-Betrieb umstellen. Bisher wurde die Schulbibliothek herkömmlich mit Hilfe von Karteikarten und Ausleihscheinen verwaltet. Künftig soll hier ein geeignetes Softwarepaket die Arbeit erleichtern.

4. Anwendungsfälle Buchdaten registrieren Ausleihdaten aufnehmen Abfrage erstellen ... Anwender: Bibliothekar(in) Anwender: Leser Anwendungssystem: DBS Anwendungsfall:Interaktion zwischen einem Anwender / Akteur und dem Anwendungssystem

5. Datenbanksystem Sammlung von Daten DB DBVS Software zur Verwaltung der Daten Anwender Anwender Datenbanksystem Ein Datenbanksystem ist eine systematisch strukturierte, langfristig verfügbare Sammlung von Daten (DB) einschließlich der zur sicheren Bearbeitung und Verwaltung dieser Daten erforderlichen Software (DBVS).

6. Datenbanksysteme sind überall Beispiele: - Bibliothek - Bank - Reisebüro: Buchungssystem - Bahn: Auskunftssystem - Suchmaschine im Internet - ...

7. Das Modellierungsproblem Miniwelt DB-Modellwelt Zustand: Gerd Müller ist registrierter Leser der Bibliothek. Er hat am 12.2.2002 das Buch „Fußball ist mein Leben“ von Franz Beckenbauer ausgeliehen. ... Vorgang: Gerd Müller gibt das Buch ... zurück. ? Informationen über die Miniwelt Ziel: Repräsentation von Information

8. Repräsentation mit Tabellen Information: Gerd Müller ist registrierter Leser der Bibliothek. Er hat am 12.2.2002 das Buch „Fußball ist mein Leben“ von Franz Beckenbauer ausgeliehen. Repräsentation:

9. Redundanz und Inkonsistenz Ausleihen Das Tabellenmodell ist redundant: Informationen werden hier z. T. mehrfach repräsentiert. Eine Transaktion (Ändern) führt leicht zu inkonsistenten Modellen: Informationen werden auf unterschiedliche Weise repräsentiert.

10. Verknüpfte Tabellen Fremd-schlüssel Primärschlüssel Die Verknüpfung von Tabellen erfolgt mit Hilfe von Schlüsseln.

11. Verknüpfte Tabellen Leser Buch Ausleihe

12. Tabellenspezifikation Leser: Das logische Schema einer Tabelle wird durch eine endliche Menge von Attributspezifikationen (Attributname; Datentyp) festgelegt. TABELLE LeserLNr: ZahlName: ZeichenketteVorname: ZeichenketteGebJahr: ZahlStammkurs: Zeichenkette

13. Datentypen / Datenstrukturen Ein Datentyp legt einen Wertebereich fest einschließlich der Operationen, die auf die Werte angewandt werden dürfen. Bsp.: Datentyp Zahl Eine Datenstruktur legt den Aufbau von komplexen Wertebereichen aus elementaren Wertebereichen fest. Bsp.: Datenstruktur Tabelle (bzw. Relation)

14. Relation Ein Relationenschema ist eine endliche Folge (A1:W1; A2:W2; ...; An:Wn)von Attributspezifikationen. Dabei wird jedes Attribut durch einen Attributnamen und einen zugehörigen Wertebereich (Datentyp) festgelegt. Die Attributwerte müssen atomar sein (d. h. es sind keine zusammengesetzen Attributwerte erlaubt). Bsp.: (LNr: Zahl; Name: Zeichenkette; Vorname: Zeichenkette) Ein Tupel (zu einem gegebenen Relationenschema) enthält zu jedem Attribut genau einen Attributwert. Bsp.: (2; “Schmitt”; “Otto”)

15. Relation Sei (A1:W1; A2:W2; ...; An:Wn) ein gegebenes Relationen-schema mit den Attributnamen A1; ...; An und den zugehörigen (endlichen) Wertebereichen W1; ...; Wn. Unter dem (kartesischen) Produkt W1 ...  Wn versteht man die Menge aller möglichen Tupel (w1; ... ; wn) mit wiWi. Eine Relation zum Relationenschema (A1:W1; A2:W2; ...; An:Wn) ist eine Teilmenge des Produkts W1 ...  Wn. Bsp.: { (1; “Meier”; “Karin”), (2; “Schmitt”; “Otto”), (3; “Breuer”; “Hans”) }

16. Relation Eine Relation kann in Tabellenform dargestellt werden. { (1; “Meier”; “Karin”), (2; “Schmitt”; “Otto”), (3; “Breuer”; “Hans”) } Beachte: Die Reihenfolge der Zeilen (Tupel) ist ohne Bedeutung. Die Reihenfolge der Spalten (Attribute) ist ohne Bedeutung.

17. Relationale Datenbank Eine relationale Datenbank besteht aus einer endlichen Menge von Relationen. Bem.: Das relationale Datenmodell wurde 1970 von E. F. Codd entwickelt. Datenbank Bibliothek: - Relation Leser - Relation Buch - Relation Ausleihe

18. Schlüssel Primärschlüssel: Ein (Primär-) Schlüssel für eine Tabelle ist ein Attribut oder eine Attributkombination, mit dem / mit der jeder Datensatz der Tabelle eindeutig identifiziert werden kann. Schlüssel müssen immer minimal gewählt werden; d. h. man kann auf kein Attribut verzichten. Leser Ausleihe

19. Schlüssel Fremdschlüssel: Wird ein Primärschlüssel einer Tabelle in einer anderen Tabelle benutzt, so stellt er dort einen Fremdschlüssel dar. Mit Hilfe von Fremdschlüsseln werden Bezüge zwischen Tabellen hergestellt. Ausleihe

20. Referentielle Integrität Buch Ausleihe Referentielle Integrität Wenn in einer Tabelle ein Fremdschlüssel vorhanden ist, dann muss jeder Fremdschlüsselwert auch als Primärschlüsselwert in der korrespondierenden Tabelle vorkommen.

21. Übungen - Aufgabe 1 Miniwelt „Schule“ Herr P. Müller (MP) unterrichtet die Klasse 5a (Raum M21) in den Fächern Mathematik und NWU. Frau A. Albrecht (AL) unterrichtet die 5a in Französisch. Sie ist auch Klassenlehrerin der 5a. Die Französischstunden finden Mo 3 | Di 2 | Mi 6 | Fr 1 statt. (...) Entwerfen Sie ein relationales Datenmodell zu dieser Miniwelt und implementieren Sie es mit Access. Erstellen Sie hierzu die entsprechenden Tabellen (in der Entwurfsansicht), geben Sie einige Testdaten ein und verknüpfen Sie die Tabellen (Primärschlüssel festlegen und Beziehungen knüpfen).

22. Übungen - Aufgabe 2 Miniwelt „Sportverein“ Der Sportverein ist in Abteilungen (Leichtathletik, Turnen, Fußball, ...) aufgeteilt. Jede Abteilung bietet z. T. mehrere Sportgruppen an (z. B. bietet die Abteilung Fußball eine A-Jugend, B-Jugend, Altherrengruppe, ... an). Jede Sportgruppe wird von einem Trainer geleitet. Jede Abteilung hat einen Vorsitzenden. Trainer und Vorsitzender sind Mitglieder des Vereins. Jedes Mitglied wird über eine Mitgliedsnummer und die üblichen Daten erfasst. ... Entwerfen Sie ein relationales Datenmodell zu dieser Miniwelt und implementieren Sie es mit Access.

23. Aufgabe 1 - Lösungsvorschlag Miniwelt „Schule“ Herr P. Müller (MP) unterrichtet die Klasse 5a (Raum M21) in den Fächern Mathematik und NWU. Frau A. Albrecht (AL) unterrichtet die 5a in Französisch. Sie ist auch Klassenlehrerin der 5a. Die Französischstunden finden Mo 3 | Di 2 | Mi 6 | Fr 1 statt. (...)

24. Teil 2 Abfragen

25. Zielsetzung Es soll möglich sein, Abfragen an die Bibliotheksdatenbank zu stellen. Z.B.: Ist das Buch X ausgeliehen? Wer hat das Buch X ausgeliehen? ...

26. Relationales Datenmodell Leser Buch Ausleihe

27. Relationales Datenmodell

28. Query by Example Abfrage: Welche Leser haben ein Buch ausgeliehen? QbE: QbE: Grafik-basierte Erstellung von Abfragen

29. Query by Example Abfrage: Welche Leser haben ein Buch ausgeliehen? Ergebnis der Abfrage: neue Tabelle (Dynaset) Beachte: Die neue Tabelle wird i. a. nicht gespeichert.

30. Übungen - Aufgabe 3 • Erstellen Sie zu den folgenden Aufgaben QbE-basierte Abfragen. Welche Leser gibt es? Welche Leser sind 84 geboren? Welche Leser sind vor 84 geboren? Welche Leser gibt es? Ausgabe aller Leser (Name, Vorname und Geburtsjahr) sortiert nach dem Geburtsjahr Welche Bücher von Goethe sind in der Bibliothek vorhanden? Welche Bücher sind ausgeliehen? (sortierte Ausgabe) Welche Bücher hat die Leserin Lisa Wollenweber ausgeliehen? Welche Leser haben ein Buch ausgeliehen? (Ausgabe mit / ohne Duplikate) Welche Leser haben ein Buch vor mehr als 60 Tagen ausgeliehen? (Hinweis: Datum()-Operator benutzen) ... Welche Bücher hat Leser X ausgeliehen? Wer hat Buch X ausgeliehen? Welche Bücher sind vom Autor X vorhanden? Wer hat welche Bücher vor dem Datum X ausgeliehen? Ausgabe sortiert nach Stammkursleitern ...

31. SQL - Structured Query Language Abfrage: Welche Leser haben ein Buch ausgeliehen? QbE: SQL: SELECT Leser.Name, Leser.Vorname, Ausleihe.Sig FROM Leser INNER JOIN Ausleihe ON Leser.LNr = Ausleihe.LNr;

32. SQL Aufgabe SQL-Ausdruck Bestimme Vorname und Nachname aller Leser. SELECT Leser.Vorname, Leser.Name FROM Leser; Bestimme die Daten aller Leser, die im Stammkurs von Frau Römer sind. SELECT * FROM Leser WHERE ((Leser.Stammkursleiter)="ROE"); Bestimme die Daten aller Bücher, die ausgeliehen sind. SELECT * FROM Buch INNER JOIN Ausleihe ON Buch.Sig = Ausleihe.Sig; SELECT * FROM Buch, Ausleihe WHERE (Buch.Sig = Ausleihe.Sig);

33. SQL - Syntax Grundstruktur einer SQL-Auswahlabfrage: <SQL-Auswahlabfrage> ::= SELECT [ALL | DISTINCT] <Attributliste> FROM<Referenztabellen> [WHERE <Bedingung>] [GROUP BY <Attributliste>] [HAVING <Bedingung>] Erläuterung < ... > : noch zu erklärendes Symbol FROM : Bestandteil der Sprache [ ... ] : optionales Element ... | ... : Alternative

34. SELECT-Klausel Grundstruktur SELECT [ALL | DISTINCT] <Attributliste> FROM <Referenztabelle> [WHERE <Bedingung>] [GROUP BY <Attributliste>] [HAVING <Bedingung>] Beispiele SELECT Leser.Vorname, Leser.Name FROM Leser; SELECT Vorname, Name FROM Leser; SELECT DISTINCT Name FROM Leser; SELECT * FROM Leser;

35. FROM-Klausel Grundstruktur SELECT [ALL | DISTINCT] <Attributliste> FROM <Referenztabelle> [WHERE <Bedingung>] [GROUP BY <Attributliste>] [HAVING <Bedingung>] Beispiele SELECT * FROM Leser; SELECT * FROM Buch INNER JOIN Ausleihe ON Buch.Sig = Ausleihe.Sig; SELECT * FROM Leser, Buch

36. WHERE-Klausel SELECT Titel FROM Buch WHERE (Jahr <1990); SELECT Titel FROM Buch WHERE ((Jahr <1990) AND (Jahr > 1950)); SELECT Titel FROM Buch WHERE (NOT (Autor=“Goethe“)); SELECT Name, Vorname FROM Leser WHERE (Vorname IN (“Franz“, “Heinz“, “Franz“)); SELECT Autor, Titel FROM Buch WHERE (Sig IN (SELECT Sig FROM Buch INNER JOIN Ausleihe ON ... ));

37. SQL - Semantik Informelle Beschreibung der Bedeutung: SELECT ... // Attribute der erzeugten Tabelle FROM ... // Tabelle, aus der die Daten stammen [WHERE ...] // Bedingung, die die Datensätze erfüllen müssen [GROUP BY ...] // Partitionierung in Gruppen [HAVING ...] // Auswahl von Gruppen Formale Beschreibung der Bedeutung: Relationenalgebra Objekte: Relationen Operationen: erzeugen aus Relationen neue Relationen

38. Operationen auf Relationen Mit Hilfe von Operationen lassen sich aus gegebenen Relationen neue Relationen erzeugen. Folgende Grundoperationen werden üblicherweise benötigt: - Projektion - Selektion - kartesisches Produkt - Verbund (join) - Vereinigung, Durchschnitt, Differenz

39. SELECT-Klausel Ausgangsrelation: Buch Sig Autor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... Ph3 Russell Geschichte der ... 1952 Philosophie SQL-Ausdruck: SELECT Autor, Titel FROM Buch; Ergebnisrelation: • Autor Titel Goethe Faust Mann Dr. Faustus Mann Der Zauberberg Russell Geschichte der ...

40. Projektion Ausgangsrelation: Buch Sig Autor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... Ph3 Russell Geschichte der ... 1952 Philosophie Projektion: Autor, Titel Ergebnisrelation: Autor, Titel(Buch) • Autor Titel Goethe Faust Mann Dr. Faustus Mann Der Zauberberg ... Russell Geschichte der ... Mit Hilfe einer Projektion lassen sich Attributwerte einer Relation auswählen.

41. WHERE-Klausel Ausgangsrelation: Buch Sig Autor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... Ph3 Russell Geschichte der ... 1952 Philosophie SQL-Ausdruck: SELECT * FROM BUCH WHERE (Autor = „Goethe“); Ergebnisrelation: (Autor=”Goethe”)(Buch) Sig Autor Titel Jahr FachbereichD1 Goethe Faust 1973 Deutsch

42. Selektion Ausgangsrelation: Buch Sig Autor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... Ph3 Russell Geschichte der ... 1952 Philosophie Selektion: (Autor=”Goethe”) Ergebnisrelation: (Autor=”Goethe”)(Buch) Sig Autor Titel Jahr FachbereichD1 Goethe Faust 1973 Deutsch Mit Hilfe einer Selektion kann man bestimmte Tupel aus einer Relation herausfiltern.

43. Produkt Ausgangsrelationen: Buch, Leser Sig Autor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... LNr Name Vorname GebJahr Stamm...0 Christ Benjamin 83 ROE1 Eberle Gerrit 84 TM... Ergebnisrelation: Buch  Leser // Kombination der Relationen Sig Autor Titel Jahr Fach... LNr Name ...D1 Goethe Faust 1973 Deutsch 0 Christ D1 Goethe Faust 1973 Deutsch 1 Eberle ... D1 Goethe Faust 1973 Deutsch 15 Wollen... D2 Mann Dr. Fau... 1937 Deutsch 0 Christ D2 Mann Dr. Fau... 1937 Deutsch 1 Eberle ...

44. INNER JOIN Ausgangsrelationen: Buch, Ausleihe SigAutor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... LNr SigDatum 4 D2 29.10.01 8 M1 03.11.01 ... SQL-Ausdruck: SELECT * FROM Buch INNER JOIN Ausleihe ON Buch.Sig = Ausleihe.Sig; Ergebnisrelationen: Sig Autor Titel Jahr Fach... LNr Datum ... D2 Mann Dr. Fau... 1937 Deutsch 4 29.10.01 ...

45. Verbund Ausgangsrelationen: Buch, Ausleihe SigAutor Titel Jahr Fachbereich D1 Goethe Faust 1973 Deutsch D2 Mann Dr. Faustus 1937 Deutsch ... LNr SigDatum 4 D2 29.10.01 8 M1 03.11.01 ... Mit Hilfe eines Verbundes (natural join) lassen sich Relationen mit einem gemeinsamen Attribut verschmelzen. Verbund / join:  Ergebnisrelationen: Buch  Ausleihe Sig Autor Titel Jahr Fach... LNr Datum ... D2 Mann Dr. Fau... 1937 Deutsch 4 29.10.01 ...

46. Mengenoperationen Zwei Relationen R und S (über demselben Relationenschema) können mit den üblichen Mengenoperationen verknüpft werden: • Vereinigung: R  S • Durchschnitt: R  S • Differenz: R \ S

47. Relationenalgebra und SQL Autor, Titel(Buch) SELECT Autor, Titel FROM Buch (Autor=”Goethe”)(Buch) SELECT * FROM Buch WHERE (Autor = “Goethe”) Buch  Ausleihe SELECT * FROM Buch INNER JOIN Ausleihe ON Buch.Sig = Ausleihe.Sig Buch  Leser SELECT * FROM Buch, Leser Buch1  Buch2 Buch1 UNION Buch2

48. SQL als Programmiersprache Ausgangsrelationen SQL-Interpreter Programm: SQL-Ausdruck Ergebnisrelation SQL: Sprache zur Programmierung von Abfragen

49. Übungen - Aufgabe 3 Wir betrachten hier die folgenden Tabellen zur Modellierung der Miniwelt „Unterrichtsverteilung“: Erstellen Sie SQL-Ausdrücke zu den folgenden Abfrageaufträgen:

50. Übungen - Aufgabe 3 (1) Bestimme Name und Vorname sämtlicher Lehrer. (2) Bestimme alle Lehrer (nur Kürzel), die montags in der 3. Stunde Unterricht haben. (3) Bestimme die Namen aller Lehrer, die eine 6. Klasse leiten. (4) Bestimme, welche Lehrer in den 8. Klassen Physik unterrichten. Gib den Lehrernamen und die Klasse jeweils aus. (5) Bestimme alle Lehrer (Namen und Vornamen), die keine Klasse leiten. (6) Bestimme, wann die 10a ihre Deutschstunden hat.