Datenbankentwurf Das Telefonbuch als Datenbasis Das herkömmliche Telefonbuch... ... ist eine Ansammlung vieler Daten ... besteht aus einzelnen Teilnehmern als Datensätze ... ist nach festem Schlüssel sortiert (Ort, Name, Vorname, ...) ... enthält keine Möglichkeit zum Eintragen, Ändern, Löschen oder Umsortieren ... ist deshalb nur eine Datenbasis, keine Datenbank. Straße ER-Modell „Telefonbuch“ Hausnr Vorname Name Teilnehmer Eine Telefonbuch-CD... ... enthält exakt die gleiche Datenbasis wie das Telefonbuch Nummer Vorwahl ... ist durch die zusätzliche Software eine Datenbank ... kann durch Änderung der Sortierschlüssel wesentlich mehr Information bereitstellen: landesweite Namensuche Nachbarsuche (Adressbuch) Rufnummeridentifikation (Datenschutzkonflikt!) ... hat (oft) keine Möglichkeit zum Ändern/Löschen/Hinzufügen von Datensätzen Ort Datenbankentwurf Datenschutz Früher: alles ist erlaubt, was nicht verboten ist! Heute: alles ist verboten, was nicht erlaubt ist! Das Bundesdatenschutzgesetz unterscheidet Datenverarbeitung bei Behörden und öffentlichen Stellen (z.B. Einwohnermeldeamt) bei nicht öffentlichen Stellen für eigene Zwecke (z.B. Banken) bei nicht öffentlichen Stellen für fremde Zwecke (z.B. Kundenkarte) Jeder Bürger hat das Recht auf Auskunft über die gespeicherten Daten Berichtigung bzw. Sperrung oder Löschung von fehlerhaften Daten Recht auf informationelle Selbstbestimmung, d.h. Daten dürfen nur mit Einverständnis und für bestimmte Zwecke gespeichert werden! Beispiel: Die Telekom darf Verbindungsdaten zum Zwecke der Rechnungsstellung speichern. Danach müssen sie gelöscht werden. Eine Verwendung für andere Zwecke ist nur in Ausnahmefällen erlaubt (Zugriff durch die Polizei?). Datenbankentwurf Datenbanken mit persönlichen Inhalten bei Behörden und öffentlichen Stellen Einwohnermeldeamt Polizei Sozialamt Finanzamt ... bei nicht öffentlichen Stellen für eigene Zwecke Bank Krankenkasse, Ärzte Telekommunikationsunternehmen, Provider Arbeitgeber, auch Schule? Maut ... bei nicht öffentlichen Stellen für fremde Zwecke Kundenkarte Gewinnspiele ... Datenbankentwurf Eine Tabellenkalkulation als Datenbankwerkzeug Eine Tabellenkalkulation ist in Ansätzen als Datenbankwerkzeug einsetzbar: Es können Tabellen für die Datensätze angelegt werden Die Datenfelder sind (in Ansätzen) typisierbar Es besteht die Möglichkeit zur Umsortierung bzw. Suche Datensätze können direkt eingefügt/geändert und gelöscht werden Es existieren spezielle Datenbankbenutzeroberflächen Es existieren Schnittstellen zu anderen Datenbankwerkzeugen Es besteht die Möglichkeit, eigene Oberflächen zu erschaffen ... aber: es besteht in der Regel keine Möglichkeit, Tabellen zu verknüpfen, Rechte gezielt zu vergeben oder die Datenbank mehreren Nutzern gleichzeitig zur Verfügung zu stellen. Datenbankentwurf Datenbasis Datenbank DBMS Datum Datenfeld Datensatz Datenfeldname ER-Diagramm Primärschlüssel Fremdschlüssel Relationale DB Sortierschlüssel SQL kleines Begriffslexikon Die Gesamtmenge der abgespeicherten Daten. Datenbasis + Software zur Eingabe, Verwaltung und Ausgabe der Daten. Datenbankmanagementsystem: Datenbank + vorgeschaltete Software zum Regeln der Zugriffsrechte usw. Eine Einzelinformation. Spalte einer Datentabelle. Zeile einer Datentabelle. Überschrift einer Spalte der Datentabelle. Entity-Relationship-Diagramm: grafisches Werkzeug zum Modellieren einer Miniwelt (Objekte, Attribute und Beziehungen). Datenfeld, das für jeden Datensatz der Tabelle einen unterschiedlichen Wert annehmen muss. Datenfeld, dessen Wert in einer anderen Tabelle als Primärschlüssel vorkommen muss. Datenbankentwurf, bei dem einzelne Tabellen miteinander in Beziehung stehen (durch Fremd- und Primärschlüssel). Datenfeld, nach dem die Datensätze sortiert vorliegen. Structured Query Language: Datenbanksprache zur Kommunikation des Anwenders mit dem DBMS. Datenbankentwurf allgemeine Vorgehensweise • Aufgabenstellung konkretisieren (Analyse der Realität/Miniwelt) – Charakteristische Vorgänge, benötigte Informationen, ... • Erstellen eines ER-Modells der Miniwelt – Bestimmung der Objekte, Attribute, Relationen und Schlüssel • Wahl des (Datenbank-) Werkzeugs – Excel, Access, MySQL, ... • Erstellen der Tabellen und Strukturen – Tabellen und Felder anlegen, Attribute mit geeignetem Typ festlegen, Schlüssel festlegen, ... • evtl. Erstellung von Oberflächen – – – – Excel: Maske / SVERWEIS Access: Abfragen / Formulare / Berichte MySQL: phpMyAdmin als Standardoberfläche MySQL: eigene PHP-Internetseiten zum Eintragen / Abfragen / ... • Daten eintragen, abfragen, ändern, auswerten, ... Datenbankentwurf ER-Diagramm CD-Liste Versuch 1 Probleme: Titel Interpret CDnr CD Liedzahl 1.Die einzelnen Lieder sind nicht aufgeführt! Laenge 2.Jede CD kann nur Lieder von einem Interpreten enthalten! Allgemeines zu ER-Diagrammen (entity-relationship) CD Interpret CDnr Titel Zahl Text 1 Waterloo 2 The Wall 3 Interpret Text ... Laenge Zahl Abba 49:32 9 Pink Floyd 66:12 5 ... Attribut eines Objekts Liedzahl Zahl/Zeit ... Objekt (entity) ... CDnr leitet 1 Schlüsselattribut eines Objekts, muss für jedes Objekt unterschiedlich sein! Beziehung (relation) zwischen 2 Objekten Kardinalität der Beziehung (1:1, 1:n, n:1 oder n:m) Datenbankentwurf ER-Diagramm CD-Liste Versuch 2 Titel Titel Interpret CDnr CD Liedzahl Interpret Liednr 1 n enthält Lied Redundanz! Die Information steht schon in der CD-Liste! Laenge Probleme: Laenge 1.Jede CD kann nur Lieder von einem Interpreten enthalten! 2.Jedes Lied kann nur auf einer CD sein! CDnr Titel Zahl Text 1 Interpret Text To the bone 2 So far 3 ... Kinks Bryan Adams ... Laenge Liedzahl Zahl/Zeit Zahl 49:32 67:12 ... Liednr CDnr Titel Laenge Interpret Zahl Zahl Text Zahl/Zeit Text 12 1 2 Summer of `69 3:35 B.Adams 14 2 2 Heaven 4:03 B.Adams 3 1 ... Apeman 4:06 Kinks Fremdschlüssel „CDnr“: Der Eintrag im Datenfeld CDnr der Tabelle Lied muss als Primärschlüssel in der Tabelle CD vorkommen! Datenbankentwurf Auf jeder CD können mehrere Interpreten sein! Titel Interpret CDnr CD Liedzahl ER-Diagramm CD-Liste Versuch 3 Titel Interpret Liednr 1 m Lied n enthält Laenge Laenge Jede m:n - Relation bekommt ihre eigene Zuordnungstabelle! Jedes Lied kann auf mehreren CDs sein! CDnr Titel Zahl Text Interpret Text 1 To the bone 2 So far 3 ... Kinks Bryan Adams ... Laenge Liedzahl Cdnr Liednr Zahl/Zeit Zahl 2 49:32 12 67:12 14 ... ... Liednr CDnr Titel Laenge Interpret 1 Zahl Zahl Text Zahl/Zeit Text 2 2 1 2 3:35 B.Adams 1 3 2 2 ... ... 3 1 Summer of `69 Heaven Apeman 4:03 4:06 B.Adams Kinks Datenbankentwurf ER-Diagramm CD-Liste Versuch 4 Adresse Name Intnr Plattenfirma Umsatz Adresse Name Pnr m Interpret n engagiert 1 1 vertreibt nimmt auf CDnr Interpret Liednr n m n enthält Lied Eine m:n - Relation kann auch Attribute besitzen! Laenge Titel Ich will mehr Information über den Interpreten! n CD Liedzahl Alter Position Titel Laenge Datenbankentwurf ER-Diagramm der Miniwelt Schule name raumart raumnr sitze 1 fach1 kuerzel Raum 1 leitet Lehrer 1 fach2 1 klassenname schülerzahl 1 gehört findet statt in Klasse 1 1 jahrgang hält 1 wohnort unr snr stunde n Schüler name n gehört zu n hat Unterricht tag n fach Datenbankentwurf Beispiel aus Datenbanken.pps (Dr.Becker) Aufgabe: Worin besteht der Unterschied zwischen den beiden Modellen? Leser LNr Name Ausleihe ... Leser LNr Name Datum Buch Sig Ausleihe ... Datum Autor Buch Sig Autor Datenbankentwurf Aufgabe zu ER-Diagrammen Miniwelt Sportverein Ein Sportverein besteht aus Mitgliedern (z.B. Heike Drechsler, geb.23.3.1966, Karlsruhe), die zu bestimmten Trainingszeiten (z.B. Montag, 19 Uhr, Leichtathletik, Frauen) teilnehmen und Sport treiben. Für das Training stehen dem Verein verschiedene Sportstätten (z.B. Rasenplatz, große Halle, kleine Halle, Tennisplatz , ...) zur Verfügung. Jede Sportstätte befindet sich an einem bestimmten Ort und hat Öffnungszeiten (z.B. Schwimmbad, Herxheim, 9-21 Uhr). Aufgabe: Erstellen Sie aus der obigen Beschreibung die Miniwelt Sportverein als ER-Diagramm! Sorgen Sie bei den Objekten für sinnvolle Primärschlüssel und geben Sie auch jeweils die Art der Relation (1:1,1:n,...) an. Miniwelt Bibliothek Leihdatum Titel Standort Jahr Buch Autor n BNummer leiht aus m Name Benutzer Wohnort Alter Rückgabedatum Warum ist die Relation “leiht aus” eine m:n-Relation? Welche Tabellen müssen anhand dieses Modells angelegt werden? Geben Sie für jede Tabelle den Tabellennamen und die Namen und Typen der Datenfelder an. Sorgen Sie für geeignete Primärschlüssel für die Tabellen! Datenbankentwurf Umsetzung eines ER-Diagramms in Tabellen • Jedes Objekt braucht eine eigene Tabelle – Die Attribute des Objekts sind die Datenfelder. – Jedes Datenfeld bekommt einen Datentyp: Speicherbedarf, Vorgriff auf die Variablentypen der Programmiersprachen. – Ist das Objekt auf der „n-Seite“ einer (1:n) - Beziehung, so kommt der Primärschlüssel des Beziehungsobjekts als Fremdschlüssel hinzu. – Ist das Objekt Teil einer (1:1) - Beziehung, so kommt (zur Redundanzvermeidung) nur bei einem der beiden Objekte der Primärschlüssel des jeweils anderen Objekts hinzu. – Bei (m:n) - Beziehungen kommt nichts hinzu, denn: • Jede (m:n) - Beziehung braucht eine eigene Tabelle – Die Primärschlüssel der beiden dazugehörigen Objekte tauchen als Fremdschlüssel auf. – Alle weiteren Attribute der Beziehung tauchen als Datenfeld auf. Datenbanksystem DB Sammlung von Daten DB VS 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). Datenbanksysteme sind überall Beispiele: - Bibliothek - Bank - Reisebüro: Buchungssystem - Bahn: Auskunftssystem - Suchmaschine im Internet - ... Das Modellierungsproblem Miniwelt 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. ... DB-Modellwelt ? Vorgang: Gerd Müller gibt das Buch ... zurück. Informationen über die Miniwelt Ziel: Repräsentation von Information 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: Sig Autor F1 Beckenbauer I2 Gaardner ... Titel Fußball ist Sofies Welt Jahr Name 1980 Müller 1995 Müller Vorname Wohnort Gerd München Peter Landstuhl Straße Ausleihdatum Kirchstr. 13 02.02.02 Hauptstr. 7 18.01.02 Redundanz und Inkonsistenz Ausleihen Sig I1 I2 D1 D2 Autor Balzert Gaardner Musil Kafka Titel Informatik Sofies Welt Der Mann ohne Das Schloß Jahr 1999 1995 1978 1990 Ausleihdatum 02.02.2002 18.01.2002 16.02.2002 31.12.2001 Name Schreiner Müller Schreiner Müller Vorname Rudolf Peter Rudi Peter Wohnort Bann Landstuhl Bann Landstuhl Straße Kirchstr. 13 Hauptstr. 7 Kichstr. 13 Hauptstraße 7 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. Verknüpfte Tabellen Primärschlüssel LNr 0 1 2 3 ... 13 14 15 Sig D1 D2 D3 D4 D5 M1 M2 M3 P1 P2 P3 Ph1 Ph2 Ph3 Name Christ Eberle Friedrich Frisch Vorname Benjamin Gerrit Andy Johannes Teubner Thielen Wollenweber Ruth Clemens Lisa Autor Goethe Mann Mann Kafka Grass Euklid Singh Padberg Heisenberg Einstein Weinberg Gardner Kant Russell Titel Faust Dr. Faustus Der Zauberberg Das Schloss Ein weites Feld Die Elemente Fermats letzter Satz Elementare Zahlentheorie Der Teil und das Ganze Relativitätsthe orie Die ersten drei Minuten Sofies Welt Kritik der reinen Geschichte der Philosophie GebJahr 83 84 83 84 Stamm kursleit ROE TM HB TM 84 HEI 84 TM 84 TM Jahr 1973 1937 1940 1930 1997 1970 1998 1999 1955 1960 1989 1995 1958 1952 Fachbereich Deutsch Deutsch Deutsch Deutsch Deutsch Mathematik Mathematik Mathematik Physik Physik Physik Philosophie Philosophie Philosophie Fremdschlüssel LNr Ausleihdatum 2 16.01.02 5 30.12.01 3 25.02.02 12 03.03.02 2 18.01.02 Die Verknüpfung von Tabellen erfolgt mit Hilfe von Schlüsseln. Verknüpfte Tabellen Leser Buch Ausleihe LNr 0 1 2 3 ... 13 14 15 Name Christ Eberle Friedrich Frisch Vorname Benjamin Gerrit Andy Johannes Teubner Thielen Wollenweber Ruth Clemens Lisa Sig D1 D2 D3 ... Ph1 Ph2 Ph3 Autor Goethe Mann Mann LNr 4 8 11 13 13 14 15 Gardner Kant Russell Sig D2 M1 P1 D5 Ph2 D1 M3 GebJahr 83 84 83 84 Stamm kursleit ROE TM HB TM 84 HEI 84 TM 84 TM Titel Faust Dr. Faustus Der Zauberberg Sofies Welt Kritik der reinen Vernunft Geschichte der Philosophie Datum 29.10.01 03.11.01 16.08.01 12.09.01 12.09.01 06.12.01 12.10.01 Jahr 1973 1937 1940 Fachbereich Deutsch Deutsch Deutsch 1995 Philosophie 1958 Philosophie 1952 Philosophie Tabellenspezifikation Leser: LNr 0 1 2 3 ... 13 14 15 Name Christ Eberle Friedrich Frisch Vorname Benjamin Gerrit Andy Johannes Teubner Thielen Wollenweber Ruth Clemens Lisa GebJahr 83 84 83 84 Stamm kursleit ROE TM HB TM 84 HEI 84 TM 84 TM Das logische Schema einer Tabelle wird durch eine endliche Menge von Attributspezifikationen (Attributname; Datentyp) festgelegt. TABELLE Leser LNr: Name: Vorname: GebJahr: Stammkurs: Zahl Zeichenkette Zeichenkette Zahl Zeichenkette 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) Relation Eine Relation kann in Tabellenform dargestellt werden. { } (1; “Meier”; “Karin”), (2; “Schmitt”; “Otto”), (3; “Breuer”; “Hans”) LNr Name 1 Meier 2 Schmitt 3 Breuer Vorname Karin Otto Hans Beachte: Die Reihenfolge der Zeilen (Tupel) ist ohne Bedeutung. Die Reihenfolge der Spalten (Attribute) ist ohne Bedeutung. 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 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 LNr 0 1 2 3 ... 13 14 15 Ausleihe Name Christ Eberle Friedrich Frisch Vorname Benjamin Gerrit Andy Johannes Teubner Thielen Wollenweber Ruth Clemens Lisa GebJahr 83 84 83 84 Stamm kursleit ROE TM HB TM 84 HEI 84 TM 84 TM LNr 4 8 11 13 13 14 15 Sig D2 M1 P1 D5 Ph2 D1 M3 Datum 29.10.01 03.11.01 16.08.01 12.09.01 12.09.01 06.12.01 12.10.01 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 LNr 4 8 11 13 13 14 15 Sig D2 M1 P1 D5 Ph2 D1 M3 Datum 29.10.01 03.11.01 16.08.01 12.09.01 12.09.01 06.12.01 12.10.01 Referentielle Integrität Buch Ausleihe Sig D1 D2 D3 ... Ph1 Ph2 Ph3 LNr 4 8 11 13 13 14 15 Autor Goethe Mann Mann Gardner Kant Russell Sig D0 M1 P1 D5 Ph2 D1 M3 Titel Faust Dr. Faustus Der Zauberberg Sofies Welt Kritik der reinen Vernunft Geschichte der Philosophie Jahr 1973 1937 1940 Fachbereich Deutsch Deutsch Deutsch 1995 Philosophie 1958 Philosophie 1952 Philosophie Datum 29.10.01 03.11.01 16.08.01 12.09.01 12.09.01 06.12.01 12.10.01 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. Ü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). 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. (...) Kürzel AL BEC MP ... Name Albrecht Becker Müller Bez 5a 5b 5c ... Klasse 5a 5a 5a 5a 5a ... Stufe Vorname Anni Klaus Peter 5 5 5 Klassenlehrer AL MP BEC Fach FR FR FR FR NWU AL AL AL AL MP Telefon 45678 12345 23456 Lehrer Tag DI FR MI MO MI Stunde 3 2 6 1 1