Datenbanken Entwurf von Datenbanken Entwurf von Datenbanken Bisher: was sind Datenbanken? Wie funktionieren sie? Im Folgenden: wie entwickle ich eine Datenbank? Was ist eine gute Datenbank? ◮ ◮ ◮ ◮ Der Datenbankentwurfsprozess Das Entity Relationship (ER) Modell Abbildung von ER-Diagrammen auf Relationenschemata Normalformen als Qualitätskriterien Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–48 Datenbanken Entwurf von Datenbanken Der Datenbankentwurfsprozess Datenbankentwurfsprozess beschreibt systematische Vorgehensweise zur Entwicklung einer Datenbanklösung: ◮ ◮ ◮ Ausgehend von Anforderungen an zu entwickelnde Lösung über eine schrittweise Verfeinerung des Entwurfs bis hin zur Implementierung und zum Einsatz der Lösung Angelehnt an Software-Entwicklungsprozess (→) zur Entwicklung allgemeiner Software-Lösungen Unabhängig von konkretem Anwendungsszenario Im folgenden: Entwurf relationaler Datenbanken Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–49 Datenbanken Entwurf von Datenbanken Der Datenbankentwurfsprozess /2 Anforderungsanalyse Dokumentatation Eike Schallehn Konzeptueller Entwurf Konzeptuelles Schema Logischer Entwurf z.B. Entity Relationship Diagramm Logisches Schema Datendefintion und Implementierung = Tabellen- und Spaltendefinition Datenbank Grundlagen der Informatik für Ingenieure 2008/2009 6–50 Datenbanken Entwurf von Datenbanken Phasen des Datenbankentwurfs /1 Anforderungsanalyse: Sammlung von Anforderungen, die zu entwickelndes Datenbanksystem beschreiben Z.B. Informationsbedarf zukünftiger Anwender, zu unterstützende Abläufe, etc. Ergebnis: informell festgehaltene Dokumentation der Anforderungen Konzeptueller Entwurf: Entwicklung eines implementierungsunabhängigen (abstrakt, high-level) Datenbankschemas Erste Strukturierung für Anwendungsdaten Dient der schrittweisen Verfeinerung des Entwurfs sowie der Diskussion verschiedener Entwickler untereinander und mit Anwendern Ergebnis: konzeptuelles Schema, z.B. als Entity Relationship Diagramm Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–51 Datenbanken Entwurf von Datenbanken Phasen des Datenbankentwurfs /2 Verteilungsentwurf (optional): nur für verteilte Systeme Festlegung des Speicherorts der Daten im Netz Prinzipiell unabhängig vom Implementierungsmodell (nächster Schritt) Erfolgt meist aber als Teil des physischen Entwurfs Ergebnis: Verteilungsschema Logischer Entwurf: Überführung in relationales Datenmodell für Implementierung sowie Erfüllung von Qualitätskriterien (Normalformen) durch Normalisierung Entwurf geeigneter Tabellenstrukturen zur Darstellung der Anwendungsdaten Qualitätskriterium: Strukturen vermeiden Abspeicherung widersprüchlicher Daten Ergebnis: logisches Schema Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–52 Datenbanken Entwurf von Datenbanken Phasen des Datenbankentwurfs /3 Physischer Entwurf: ermöglicht Beeinflussung interner Speicherstrukturen zu Zwecken der Performance Optimierung Festlegen von Indextsrukturen (Hash-Tabellen, B-Bäume) für Zugriffspfade Weitere Mittel: materialisierte Sichten (Vorberechnung) sowie Partitionierung (Teile und Herrsche) Datendefinition und Implementierung: Erstellen enstprechender DDL-Statements und deren Ausführung Erzeugung von Tabellen, Sichten und Indexstrukturen Ergebnis: (leere) Datenbank Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–53 Datenbanken Entwurf von Datenbanken Das Enity Relationship (ER) Modell Standard für die konzeptuelle Modellierung von Datenbankschemata Ziel: Darstellung der Inhalte und Bedeutung (auch semantische Modellierung) ◮ ◮ Was wird durch das Schema dargestellt (welche Daten)? Nicht: wie werden die Daten dargestellt (Implementierung)? Dient der Diskussion (Entwickler und Anwender) und Verfeinerung der Schemata Deshalb möglichst einfache Modellierungskonstrukte: Gegenstände (Entities), deren Beziehungen untereinander (Relationships) und Eigenschaften (Attributes) Eigentliche Modellierung auf Typebene: Gegenstände mit gleichen Eigenschaften und Beziehungen werden zu einem Entity Type zusammgefaßt (analog Relationship Types) Begriffe Entity und Relationship werden meist verkürzend für Entity Types bzw. Relationship Types verwendet Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–54 Datenbanken Entwurf von Datenbanken ER Modell: Einführendes Beispiel 6WXGHQW Student EHVXFKW ! besucht MatrNr Vorlesung ID Semester Name Bezeichnung Vorname " # $ Eike Schallehn " % "# $ $ Grundlagen der Informatik für Ingenieure 2008/2009 6–55 Datenbanken Entwurf von Datenbanken ER Modell: Grundlegende Grafische Notation Entity (Type): Rechteck mit Typbezeichner Relationship (Type): Raute mit Typbezeichner Attribut: abgerundete Box oder Ellipse mit Attributbezeichner, Schlüssel mit Unterstreichung Zahlreiche abweichende grafische Darstellungen in verwandten Ansätzen und Entwicklungs-Tools mit gleicher oder ähnlicher Beduetung sowie ggf. Erweiterungen Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–56 Datenbanken Entwurf von Datenbanken ER Modell: Kardinalitäten Kardinalitäten geben numerische Grenzen an, wie Objekte verschiedener Typen miteinander in Beziehung stehen können Beispiele: ◮ ◮ ◮ ◮ ◮ Ein Student kann beliebig viele Vorlesungen besuchen Eine Vorlesung kann (je nach Kapazität des Hörsaals) von vielen Studenten besucht werden Eine Vorlesung wird von genau einem Dozenten angeboten Eine Person kann mit maximal einer anderen Person verheiratet sein (optional) Jede Person hat genau eine Mutter und genau einen Vater Von entscheidender Bedeutung bei Überführung in das Relationenmodell Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–57 Datenbanken Entwurf von Datenbanken ER Modell: Kardinalitäten /2 Dozent [1,1] hält [1,*] Vorlesung ist äquivalent zu: Dozent 1 hält * Vorlesung 1:N-Beziehung: ein Objekt darf mit beliebig vielen eines anderen Typs in Beziehung stehen, aber eindeutige Zuordnung in die andere Richtung Min/Max-Notation: Angabe der minimimalen und maximalen Anzahl, in der das Objekt in Beziehung stehen kann Abkürzende Schreibweise verwendet nur Obergrenze (Optionalität mit Untergrenze 0 so aber schlecht abbildbar) Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–58 Datenbanken Entwurf von Datenbanken ER Modell: Kardinalitäten /3 Student Vorlesung besucht ist äquivalent zu: Student * besucht * Vorlesung N:M-Beziehungen (Objekte beider beteiligter Typen können beliebig oft in Beziehung stehen) sind bei keiner Angabe von Kardinalität der angenommene Standardfall Oft auch auch N und M als Notation für Kardinalitäten verwendet Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–59 Datenbanken Entwurf von Datenbanken ER Modell: Kardinalitäten /4 verheiratet [0,1] Person [0,1] Beispiel für eine optionale Beziehung Außerdem selbst-bezüglich auf Typ-Ebene: auch Objekte des selben Typs können in Beziehungen zueinander stehen Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–60 Datenbanken Entwurf von Datenbanken ER Modell: Weitere Konstrukte Dozent Vorlesung Gebäude hält hat Raum Raum Mehrstellige Beziehungstypen Schwache (existentiell abhängige) Entitätstypen Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–61 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen auf Relationenschemata ER Modell ist prinzipiell unabhängig vom Implementierungsmodell In der Praxis meist eingesetzt als Entwurfsmittel für relationale Datenbanken Überführung von ER Diagrammen auf Relationenschemata geschieht nach einfachen Regeln Im folgenden illustriert an folgendem einfachen Beispiel: Artikel * * in ArtikelNr Bezeichnung Preis Eike Schallehn Anzahl Bestellung * von 1 Kunde BestellNr KundenNr Rabat Name Datum Anschrift Grundlagen der Informatik für Ingenieure 2008/2009 6–62 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen: Entities Artikel Artikel !"#$%&'(" +&,&$-./0/1 )"&$* ArtikelNr Bezeichnung Preis Alle Entities werden auf separate Tabellen abgebildet Attribute werden Spalten, konkrete Datentypen müssen festgelegt werden Schlüsselattribute werden Schlüssel der Tabelle Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–63 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen: N:M-Beziehungen Artikel * * in ArtikelNr BestellNr Anzahl Bezeichnung Rabat Preis Datum Artikel !"#$%&'(" Bestellung Bestellung +&,&$-./0/1 +&*#&''(" )"&$* 5363# 23#04 ArtikelBestellung !"#$%&'(" Eike Schallehn +&*#&''(" !/,3.' Grundlagen der Informatik für Ingenieure 2008/2009 6–64 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen: N:M-Beziehungen /2 N:M-Beziehungen müssen generell auf separate Tabellen abgebildet werden Schlüssel der Beziehungstabelle bildet sich aus zusammengesetzten Schlüsseln der in Beziehung stehen Entity-Tabellen Teilschlüssel dienen als Fremdschlüssel auf Entity-Tabellen Attribute der Beziehung werden Spalten der Beziehungstabelle Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–65 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen: 1:N-Beziehungen Bestellung * 1 von Kunde BestellNr KundenNr Rabat Name Datum Anschrift Kunde Bestellung 2%).%33&' 4/5/. 1/."0 !"#$%#&' !"#$%#&' &/0% (#)*+',-. Bei 1:N-Beziehungen Verschmelzung der Beziehungstabelle mit der Entity-Tabelle der N-Kardinalität möglich Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–66 Datenbanken Entwurf von Datenbanken Abbildung von ER-Diagrammen: Optionale Beziehungen Optionale Beziehungen, egal ob N:M, 1:N oder 1:1, sollten als separate Tabelle umgesetzt werden Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–67 Datenbanken Entwurf von Datenbanken Schemakonsistenz Ergebnis der Überführung ist relationales Datenbankschema Zweiter Teilschritt des logischen Entwurfs umfaßt Sicherstellung der Schemakonsistenz Allgemein drei wichtige Kriterien der Konsistenz (Widerspruchsfreiheit) für Schemata und Daten ◮ ◮ ◮ Modellkonsistenz: reale Informationen können im Schema korrekt dargestellt werden → muss durch konzeptuellen Entwurf und korrekte Überführung in Relationenmodell sichergestellt werden Semantische Konsistenz: die gespeicherten Daten sind korrekt (stehen nicht im Widerspruch zur Wirklichkeit) → kann durch Integritätsbedingungen und Anwendungslogik unterstützt werden, letzten Endes aber Verantwortlichkeit der Anwender Schemakonsistenz: Daten müssen untereinander widerspruchsfrei sein → Sicherstellung durch Vermeidung mehrfacher Abspeicherung von Informationen (Redundanz) → Normalformen als Qualitätskriterium Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–68 Datenbanken Entwurf von Datenbanken Redundanz und Inkonsistenzen #3+,&*D/ J.!% IE&#.!% C2: 6/.5/ 5&%33% <H))%& FG. =>1AB <.$5%8;&$ 2%*/%&3/&.4%"1 6+,;)0% C%/%& =>1AB :%#/&;! ()&*+,-)./0"1' 6E!!%& 6*%$D&*%5 =>?1@ 6+,7#%8%+9 !" #$%&"'' 6E!!%& 6*%$D&*%5 =>?1@ 6+,7#%8%+9 !"#$%&& Mehrfache Speicherung der selben Realweltfakten (Redundanz) ermöglicht Dateninkonsistenzen Erkennbar an „Abhängigkeiten zwischen Attributwerten“ Sollen durch Normalisierung vermieden werden Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–69 Datenbanken Entwurf von Datenbanken Funktionale Abhängikeiten Funktionale Abhängikeiten in einer Tabelle liegen vor, wenn Werte einer Spalte (oder einer Gruppe von Spalten) einen eindeutigen Schluss auf die Werte einer anderen (Gruppe von) Spalte(n) zulassen „Funktional“, weil ... eindeutige Werteabbildung entspricht mathematischem Konzept der Funktion: für einen Eingabewert ist nur ein Ergebniswert möglich (Eindeutigkeit) Beispiele: ◮ ◮ ◮ ◮ Die Postleitzahl bestimmt eindeutig den Ort Die Matrikelnummer (Schlüssel) bestimmt alle weiteren Eigenschaften eines Studenten Vorwahl und Telefonnummer bestimmen eindeutig alle Eigenschaften des Anschlusses Semester, Termin und Raum bestimmen eindeutig Vorlesungstitel und Dozenten Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–70 Datenbanken Entwurf von Datenbanken Normalformen Ziel der Normalisierung: alle Spalten einer Tabelle sollen nur vom vollständigen Schlüssel abhängen, d.h. dadurch bestimmt sein (3. Normalform) Erreichen von Normalformen z.B. durch schrittweises Zerlegen Wichtigste Normalformen: ◮ ◮ ◮ 1. Normalform: nur atomare Werte in jeder Spalte 2. Normalform: keine funktionalen Abhängigkeiten von einem Teil des Schlüssels 3. Normalform: keine funktionalen Abhängigkeiten zwischen Nicht-Schlüsselattributen Zahlreiche weitere Normalformen existieren (hier nicht behandelt) Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–71 Datenbanken Entwurf von Datenbanken 1. Normalform: Problem F4,+8E04%%$" :1"# ;0D ;0D"#4"E ;$"0$ 61#+*C$1# <=>B ./&$0"1&+2$(6*78'(.0&96*78'(:0+&%*% A+/7* <==@ ./&$0"1&+2$(3*4"&05'(!"#$%$"#$"& -12$?$"& <=>= !"#$%$"#$"&'()*+,$ -*%( 1. Normalform: nur atomare Werte in jeder Spalte (grundlegende Anforderung im Relationenmodell) Problem: mengen- oder listenwertige Spalten Eigentlich kein Problem bzgl. Redundanz, aber ◮ ◮ Voraussetzung für weitere Normalformen Erleichtert Lesen und Modifikation von Daten Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–72 Datenbanken Entwurf von Datenbanken 1. Normalform: Lösung ;8<-=968>>') 568>>')5')6' :%)3 567 567)38)9 :%)3 5')6' 2%3-04'%3 !"#1 2%3-04'%3 :6-*>0> ,-./0 !""+ 2%3-04'%3 A6*B20/= $%&'(')* !"#" 2%3-04'%3 A.*'6)%*-&'C20/= ,-./0 @)3'>')3')* ,-./0 A.*'6)%*-&'CD08)*6E $%&'(')* ?0-<' $0> $%&'(')* @)3'>')3')* Abspalten einer separaten Tabelle mit folgenden Spalten: ◮ ◮ Schlüssel der Ursprungstabelle Spalte für einzelne Einträge der Menge Schlüssel der neuen Tabelle sind beide Spalten gemeinsam Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–73 Datenbanken Entwurf von Datenbanken 2. Normalform: Problem F;<'$+,$)(83 F;<')$G8+& :4;>4 B*(>$3';(> B$4$8'8,*(, A$(4+*1 #;,>$)*+, :;%<3$(-=(<;'4 D9" :*>$()*+, #;,>$)*+, :;%<3$(-=(<;'4 DE" :4;>4?$'> #;,>$)*+, :;%<3$(-=(<;'4 9 " 5+/6 7'$8( 2/34/%& #$%&'$()*+,- ./+0/11$+( A$(4+*1 2/34/%& #$%&'$()*+,- ./+0/11$+( 9@" C C !" 2. Normalform: 1. Normalform + keine funktionalen Abhängigkeiten von nur einem Teil des Schlüssels Problem: mögliche Redundanzen durch sich oft wiederholende Wertepaare Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–74 Datenbanken Entwurf von Datenbanken 2. Normalform: Lösung B3C-.*4.60/% B3C-6.D/*( 8&35& 8&35&@705.%-305 @.&A 8&35& @705.%-305 ;.0&*7< 2345.67*4 >1" 2345.67*4 83'C%.0EH0C3-& 875.067*4 2345.67*4 >?" #$%&$'( 2.'(-.067*4E F$*G$<<.*0 8&35&9.-5 2345.67*4 1 " = )*$+ ,-./0 #$%&$'( !" ;.0&*7< #$%&$'( 1:" = = Abspalten einer separaten Tabelle mit folgenden Spalten: ◮ ◮ Teilschlüssel der Ursprungstabelle, von welchem andere Spalte(n) abhängig Alle vom Teilschlüssel abhängig Spalten Abhängige Spalten werden aus der Originaltabelle entfernt Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–75 Datenbanken Entwurf von Datenbanken 3. Normalform: Problem C*B@86* ()*+,+ ,)58 O.+6)58 I=J C*)@* 4@+8>>8 "#%!' (N118+ LM) %G H# ()7@8EB+7 =8:*8+>*+)?83 &&!%% C;<B128 I8*8+ %G H# ()7@8EB+7 91+:;<01)*23 ! &$!!! C.558+ C:87/+:8@ %G' $ C;<D68E8;F 4534678+3!! !"#$% A:6*8+ %G"'# AB>* -.+/01)*23'' K.E8+* 3. Normalform: 2. Normalform + keine funktionalen Abhängigkeiten zwischen Nicht-Schlüsselattributen Problem: mögliche Redundanzen durch sich oft wiederholende Wertepaare Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–76 Datenbanken Entwurf von Datenbanken 3. Normalform: Lösung H*I@86* ()*+,+ ,)58 M.+6)58 C=D 4@+8>>8 "#%!' (L118+ JK) %A B# =8:*8+>*+)?83 &&!%% H;<I128 C8*8+ %A B# 91+:;<01)*23 ! &$!!! H.558+ H:87/+:8@ %A' $ 4534678+3!! !"#$% G:6*8+ %A"'# -.+/01)*23'' E.F8+* C=DH*)@* C=D H*)@* %A B# ()7@8FI+7 %A' $ H;<N68F8;O %A"'# GI>* Abspalten einer separaten Tabelle mit folgenden Spalten: ◮ ◮ Bestimmende Spalte(n) als Schlüssel Alle davon abhängigen Spalten Abhängige Spalten werden aus der Originaltabelle entfernt Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–77 Datenbanken Entwurf von Datenbanken Normalformen in der Praxis Praktisch relevant zur Vermeidung von Inkonsistenzen Aber: Zerlegung von Tabellen führt zu höherem Aufwand bei der Anfragebearbeitung durch mehr Verbundoperationen Deshalb oft Abstriche von Normalformen → kontrollierte Redundanz Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–78 Datenbanken Entwurf von Datenbanken Zusammenfassung: DB-Entwurf Entwurfsprozess für Datenbanken angelehnt an allgemeine Entwurfsprozesse: Analyse des Problems, schrittweise Verfeinerung der Lösung bis hin zur Implementierung ER-Modell als implementierungsunabhängige Modellierungsmethode für Datenbankschemata Überführung in das Relationenmodell entsprechend festen Regeln Normalformen als Qualitätskriterien für Tabellen Eike Schallehn Grundlagen der Informatik für Ingenieure 2008/2009 6–79