Lise-Meitner-Schule Oberstufenzentrum Chemie, Physik und Biologie Berlin, Bezirk Neukölln Datenbanken Entwurf 1 ER Model Entity Relation Ship Model Mit einem ER Modell bildet man die Entitäten (Gegenständen) und Relation (Beziehungen) zwischen den Entitäten ab. Damit wird versucht das Gedankliche Konzept der modellierten Welt darstellbar zu machen. Entitäten sind abstrahierte Gegenstandstypen (wie z.B. CDs, Interpreten) diese werden als Rechteck dargestellt. Beziehungen werden als Rauten dargestellt. Attribute (Titel, Preis, Spiellänge) der Entitäten werden als Ovale dargestellt. Die Funktionalität der Beziehungen wird wie folgt dargestellt: o 1:1 Eine CD hat in der Regel einen Interpreten o 1:n / n:1 Ein Interpret kann mehrere CDs aufgenommen haben o n:m keine Restriktionen jede Entität kann mit jeder anderen Entität verbunden sein 1 Lise-Meitner-Schule Oberstufenzentrum Chemie, Physik und Biologie Berlin, Bezirk Neukölln Datenbanken Entwurf 2 Datenbank Schlüssel Jede Reihe in einer Tabelle sollte eindeutig identifizierbar sein, d.h. in einer Spalte der Tabelle sollten Werte eingetragen sein, die einmalig sind und die betreffende Reihe eindeutig identifizieren (z.B. Identifikationsnummer). Eine solche Spalte nennt man Primärschlüssel (primary key). ID Titel Interpret Spiellänge Preis 1 Queen Live Queen 65:01 12.99 Primärschlüssel: ID (hier können wir sicher sein das es bei einer fortlaufenden ID nur ein vorkommen dieser gibt und somit jede Zeile eindeutig identifiziert werden kann.) Ein Primärschlüssel kann sich auch aus mehreren Attributen zusammensetzen, dann spricht man von einem zusammengesetzter Primärschlüssel (composite key) Mittels des Primärschlüssels ist die Verknüpfung mit anderen Tabellen einfach: in der anderen Tabelle muss nur eine Spalte vorhanden sein, in der der entsprechende Primärschlüssel der ersten Spalte eingetragen ist. Eine solche Spalte nennt man auswärtigen Schlüssel (foreign key). CDs Interpreten ID Titel Interpret Spiellänge Preis Name 1 Queen Live 11 65:01 12.99 11 Queen Der Primärschlüssel der Tabelle Interpreten ist in der Tabelle CDs gleichzeitig der sogenannte Fremdschlüssel (foreign key). Begrifflichkeit: Ein Schlüsselattribut ist ein Attribut, das Teil mindestens eines Schlüsselkandidaten ist. Alle anderen Attribute sind Nicht-Schlüsselattribute Beispiel: CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL) TYPE=INNODB; Begrifflichkeiten Beziehung Darunter versteht man die Beziehungen von Tabellen untereinander. 1:1 (sprich: eins zu eins) Ein Element in der einen Tabelle hat genau ein entsprechendes Element in der anderen (Macht nur dann Sinn wenn Datenfelder ausgelagert sollen um sie z. B: mit anderen Berechtigungen zu versehen (Lohndaten, Passwörter)) 1:0 (sprich: eins zu 0) Ein Element muss kein entsprechendes Element in der anderen Tabelle haben ... (z. B. Optionale Werte in einem Formular in einer extra Tabelle diese hängt immer an einem DS wenn überhaupt ein Eintrag vorhanden ist) 1:n (sprich: eins zu n) Ein Element kann viele Einträge haben (z.B. Schauspieler spielen viele in einem Film mit/ Ein Regisseur dreht viele Filme) n:m (sprich: n zu m) Mehrfach vorkommende Elemente auf beiden Seiten Nicht nur viele Schauspieler spielen nur in einem Film mit sondern Schauspieler spielen auch in vielen Filmen mit. m:n Beziehungen lassen sich nur über eine sogenannte Kreuztabelle in Relationalen Datenbanken realisieren! 2 Lise-Meitner-Schule Oberstufenzentrum Chemie, Physik und Biologie Berlin, Bezirk Neukölln Datenbanken Entwurf 3 Normalisierung Das Thema Normalisierung bereitet vielen Teilnehmern Schwierigkeiten. Deshalb habe ich versucht verschiedene Erklärungsmodelle/Beispiele aufzunehmen. Weshalb Normalisieren wir? Überführung komplexer Beziehungen innerhalb von Tabellen in einfache Beziehungen durch Aufteilung der Attribute einer Tabelle auf mehrere Tabellen. Ziel sind stabile und flexible Datenstrukturen. D.h.: Komplexe Tabellen sollen durch eine Aufteilung in mehrere Tabellen entflechtet werden. So das dabei stabile und flexible Strukturen entstehen und keine Anomalien mehr auftreten Beispiel Informationssystem über Kinofilme Anforderungen: Konkret: Datenbank einer Produktionsfirma In einer Datenbank sollen Informationen zu Filmen gespeichert werden. Dabei ist es von Interesse von wem und in welchem Studio ein Film gedreht wurde. Um den Regisseur bezahlen zu können ist seine Kontonummer erforderlich. Weiterhin sollen alle am Film beteiligten Schauspieler mit ihrer momentanen Adresse gespeichert werden. Nullte Normalform Alle Ausprägungen aus der realen Welt (Der Film) werden abstrahiert und in Felder zerlegt (Name_des_Films) in einer Tabelle abgebildet. Dabei werden alle Felder entfernt die errechnet werden können. z.B. Würde die Kosten des Films sowie die Laufzeit gespeichert werden bräuchten wir kein Feld in dem die Kosten pro Filmminute gespeichert sind da dies errechnet werden kann Erste Normalform Definition Eine Relation ist in der Ersten Normalform, wenn jeder Attributwert atomar ist. Erklärung Ein Attributwert ist atomar, wenn er nicht aus mehreren Werten zusammengesetzt ist. (Hilfe: Sobald Werte in einem Feld getrennt werden müssen, verstösst man gegen die 1NF!) z.B. Namen der Schauspieler ist nicht Atomar, da in diesem Feld bei mehr als einem Mitwirkenden Schauspieler mehrere Namen gespeichert werden müssten. Ebenso verhält es sich mit den Adressen der Schauspieler... Abhilfe: Attribute mit Nicht-atomaren-Attributwerten werden in mehrere Attribute aufgeteilt. Diese Gruppe von Attributen wird aus der Tabelle entfernt und in einer eigenen Tabelle untergebracht. z.B. Namen der Schauspieler und Adressen der Schauspieler werden in eine extra Tabelle transferiert. Da es zu einem Film mehrere Schauspieler geben kann. Zu jedem Schauspieler gehört jedoch nur eine Adresse. Deshalb wird dies mit in die Schauspielertabelle transferiert 3 Lise-Meitner-Schule Oberstufenzentrum Chemie, Physik und Biologie Berlin, Bezirk Neukölln Datenbanken Entwurf 4 Zweite Normalform Definition Eine Relation ist in der Zweiten Normalform, wenn sie in der Ersten Normalform ist und jedes NichtSchlüsselattribut von jedem Schlüsselkandidaten vollständig funktional abhängig ist. Achtung Besteht der Primärschlüssel (pk) nur aus einem einzigen Attribut (also kein zusammengesetzter Schlüssel), so ist ein Datensatz in Erster Normalform bereits automatisch in Zweiter Normalform. Erklärung Ein Attribut das kein Schlüssel ist darf nicht nur von einem Teil des Primärschlüssels voll funktional abhängig sein sondern muss von beiden abhängen. D.h. Sobald ein Attribut nur von einem Teil des Schlüssels vollständig abhängig ist verstößt dies gegen die zweite Normalform. z.B. Kontonummer des Regisseurs ist nicht vom kompletten Primary Key (Name des Films und Name des Regisseurs) abhängig sondern nur vom Regisseur selbst also vom Namen des Regisseurs.. Abhilfe: Datenfelder, die von einem zusammengesetzten Primarschlüssel nicht vollständig funktional abhängig sind, werden in weiteren Tabellen untergebracht. Der Teil des Schlüsselkandidaten, von dem ein ausgelagertes Datenfeld funktional abhängig ist, wird Primärschlüssel der neuen Tabelle. z.B. Der Regisseur wird als eigene Tabelle angelegt mit dem Primär Schlüssel Name des Regisseurs und dem Attribut Kontonummer das nun voll funktional vom PK abhängig ist Als Ergebnis erhalten wird die drei folgenden Tabellen. 4 Lise-Meitner-Schule Oberstufenzentrum Chemie, Physik und Biologie Berlin, Bezirk Neukölln Datenbanken Entwurf 5 Weitere Normalisierungen (3NF) Alternativer Erklärungsansatz Nachdem bereits die 0NF-2NF bekannt sind wollen wir uns auch noch mit der 3NF befassen. Definition Eine Relation ist in der Dritten Normalform, wenn Sie in der Zweiten Normalform ist und jedes NichtSchlüssel-Attribut von keinem Schlüsselkandidaten transitiv funktional abhängig ist. Erklärung Der Verstoß tritt dann ein wenn eine beschreibende Nicht Schlüssel Eigenschaft (Adresse) von einer anderen beschreibenden Nicht-Schlüssel Eigenschaft abhängig ist... Ausgangspunkt für unsere Betrachtungen die Film DB in der zweiten Normalform: Wenn wir die Adresse des Filmstudios betrachten so stellen wir fest das diese funktional vom Name des Studios abhängig ist und nicht vom Name des Films. Abhilfe: Rauslösen der beiden Eigenschaften Filmstudio und Adresse des Filmstudios aus der dem Objekttyp Film und in einen neuen Objekttyp Filmstudio bilden. 5