Zurück zur StartseiteInformatik · Q1 Grundkurs · Fach wechseln
Kostenlos starten
Q1 Grundkurs · Informatik
Datenbanken, Datenstrukturen und Automaten in der Qualifikationsphase · Datenbanken und SQL

ER-Modell, relationales Schema und Normalformen

Die Stadtbücherei Münster will Leserinnen, Leser und Bücher verwalten. Bisher steht alles in einer einzigen Excel-Tabelle: Wechselt eine Leserin ihre Adresse, muss man sie in jeder Zeile ändern, in der sie vorkommt. Vergisst man eine Zeile, widersprechen sich die Daten. Wie plant man eine Datenbank so, dass jede Information nur einmal gespeichert wird? Das klären wir in diesem Kapitel in drei Schritten: Modellieren (ER-Modell), Umsetzen (relationales Schema) und Prüfen (Normalformen).

Entitäten, Attribute und Schlüssel

Ein Entitätstyp beschreibt eine Sorte von Dingen der Wirklichkeit, über die man Daten speichert, z. B. Leser oder Buch. Ein einzelner Leser ist eine Entität. Die Eigenschaften eines Entitätstyps heißen Attribute, z. B. Name oder Wohnort.

Der Name eignet sich nicht: Es gibt zwei Leser namens „Jonas Schmidt“. Deshalb vergibt man künstliche Nummern wie LeserNr oder nutzt natürliche eindeutige Größen wie die ISBN eines Buches. Ein Sekundärschlüssel ist dagegen ein Attribut, nach dem man häufig sucht (z. B. Name), das aber nicht eindeutig sein muss. Man legt dafür oft einen Index an, damit die Suche schneller geht.

Schreibweise eines relationalen Schemas: Leser(LeserNr, Name, Wohnort). Im Heft und im Abitur wird der Primärschlüssel unterstrichen; in dieser Textform markieren wir ihn fett (fett = unterstrichen): Leser(LeserNr, Name, Wohnort).

Zwei Entitätstypen Leser und Buch mit ihren Attributen Leser Buch LeserNr Name Wohnort ISBN Titel Autor
Üben★☆☆
Eine Stadtbücherei in Münster verwaltet Leserinnen und Leser sowie Bücher. Zu jedem Leser werden Leser-Nr., Name und Wohnort gespeichert, zu jedem Buch ISBN, Titel und…

Eine Stadtbücherei in Münster verwaltet Leserinnen und Leser sowie Bücher. Zu jedem Leser werden Leser-Nr., Name und Wohnort gespeichert, zu jedem Buch ISBN, Titel und Autor.

  1. a) Nennen Sie zwei Entitätstypen und je drei Attribute.
  1. b) Geben Sie für jeden Entitätstyp ein geeignetes Primärschlüsselattribut an und begründen Sie Ihre Wahl.
Lösen
Üben★☆☆
Gegeben ist die Tabelle Schueler(SNr, Name, Klasse, Klassenlehrer) einer Schule in Kassel. a) Erklären Sie den Unterschied zwischen Primärschlüssel und…

Gegeben ist die Tabelle Schueler(SNr, Name, Klasse, Klassenlehrer) einer Schule in Kassel.

  1. a) Erklären Sie den Unterschied zwischen Primärschlüssel und Sekundärschlüssel.
  1. b) Nennen Sie ein Attribut, das sich als Sekundärschlüssel (z. B. für einen Index) eignen könnte, und begründen Sie dies.
Lösen

Beziehungen und Kardinalitäten

Entitätstypen stehen in Beziehungen: Ein Leser leiht ein Buch, ein Lehrer unterrichtet einen Kurs. Die Kardinalität gibt an, wie viele Entitäten auf jeder Seite beteiligt sein können.

Kardinalität Bedeutung Beispiel
1:1 höchstens eine auf jeder Seite Person – Personalausweis
1:n eine links, viele rechts Lehrer – Kurs (jeder Kurs hat genau einen Lehrer)
n:m viele auf beiden Seiten Schüler – Kurs
ER-Diagramm: Lehrer unterrichtet Kurs mit Kardinalität 1:n Lehrer unterrichtet Kurs 1 n

Lösung zur Zwischenfrage: Trainer – Gruppe ist 1:n (ein Trainer, viele Gruppen).

Üben★☆☆
In einem ER-Modell stehen die Entitätstypen Lehrer und Kurs. Ein Lehrer unterrichtet mehrere Kurse, jeder Kurs wird von genau einer Lehrkraft unterrichtet. a) Geben Sie…

In einem ER-Modell stehen die Entitätstypen Lehrer und Kurs. Ein Lehrer unterrichtet mehrere Kurse, jeder Kurs wird von genau einer Lehrkraft unterrichtet.

  1. a) Geben Sie die Kardinalität der Beziehung unterrichtet in der Notation 1:n bzw. n:m an.
  1. b) Beschreiben Sie, wie diese Beziehung im relationalen Schema umgesetzt wird (Fremdschlüssel).
Lösen

Vom ER-Modell zum relationalen Schema

Jetzt wird das Modell in Tabellen übersetzt. Drei Regeln genügen:

Der Grund für Regel 2: Eine Zelle darf nur einen Wert enthalten. Ein Kurs hat genau einen Lehrer, also passt dessen Nummer in eine Spalte der Kurs-Tabelle. Ein Lehrer hat aber viele Kurse – die passen nicht in eine Zelle der Lehrer-Tabelle.

Antwort: In Training steht die Zeile (7, 3).

Üben★★☆
Ein Sportverein in Leipzig möchte Mitglieder, Trainingsgruppen und Trainer verwalten. Ein Mitglied kann in mehreren Gruppen trainieren, jede Gruppe hat mehrere…

Ein Sportverein in Leipzig möchte Mitglieder, Trainingsgruppen und Trainer verwalten. Ein Mitglied kann in mehreren Gruppen trainieren, jede Gruppe hat mehrere Mitglieder. Jede Gruppe wird von genau einem Trainer geleitet, ein Trainer kann mehrere Gruppen leiten.

  1. a) Entwerfen Sie ein ER-Modell mit Entitätstypen, Beziehungen und Kardinalitäten.
  1. b) Überführen Sie das Modell in ein relationales Schema mit Primär- und Fremdschlüsseln. Beachten Sie dabei die n:m-Beziehung.
Lösen

Die 1. Normalform und Anomalien

Die Pizzeria in Dortmund speichert:

Bestellung(BestNr, Kunde, Kundenadresse, Pizza1, Pizza2, Pizza3)

Was passiert, wenn jemand vier Pizzen bestellt? Und welche Adresse gilt, wenn ein Kunde zweimal bestellt und beim zweiten Mal umgezogen ist?

Solche Probleme heißen Anomalien:

  • Einfügeanomalie: Daten lassen sich nicht ohne unnötige andere Daten speichern.

  • Änderungsanomalie: Eine Information steht mehrfach; man ändert nur einen Teil davon, die Daten widersprechen sich.

  • Löschanomalie: Beim Löschen eines Datensatzes gehen andere Informationen verloren.

Es bleibt eine Redundanz: Die Kundenadresse steht in jeder Zeile der Bestellung wieder. Ändert sie sich, muss man mehrere Zeilen anpassen (Änderungsanomalie). Das beheben die nächsten Normalformen.

Üben★★☆
Gegeben ist die folgende Tabelle einer Pizzeria in Dortmund: Bestellung(BestNr, Kunde, Kundenadresse, Pizza1, Pizza2, Pizza3) a) Begründen Sie, warum die Tabelle die 1.…

Gegeben ist die folgende Tabelle einer Pizzeria in Dortmund:

Bestellung(BestNr, Kunde, Kundenadresse, Pizza1, Pizza2, Pizza3)

  1. a) Begründen Sie, warum die Tabelle die 1. Normalform verletzt.
  1. b) Überführen Sie die Tabelle in die 1. Normalform und geben Sie das Schema an.
  1. c) Nennen Sie zwei Anomalien, die in der ursprünglichen Tabelle auftreten können.
Lösen

2. und 3. Normalform

Dazu braucht man den Begriff der funktionalen Abhängigkeit: $A \to B$ heißt, dass der Wert von $A$ den Wert von $B$ eindeutig festlegt. Beispiel: ArtikelNr → Artikelname.

Merkhilfe: Jedes Attribut hängt „vom Schlüssel, vom ganzen Schlüssel und nur vom Schlüssel“ ab.

2NF ist nur bei zusammengesetzten Schlüsseln ein Thema: Besteht der Schlüssel aus einem einzigen Attribut, ist eine Tabelle in 1NF automatisch in 2NF.

Antwort: AuftragNr (verweist auf Auftrag) und ArtikelNr (verweist auf Artikel).

Antwort: Vorher müssen alle Mitarbeiter-Zeilen dieser Abteilung geändert werden (sonst entstehen Widersprüche), nachher nur ein Datensatz in Standort. Das ist die vermiedene Änderungsanomalie.

Verlustfreie Zerlegung: Nach dem Zerlegen muss man die Originaltabelle durch einen Verbund (JOIN: Datensätze zweier Tabellen mit gleichem Wert im gemeinsamen Attribut werden zu einer Zeile zusammengefügt) über den Fremdschlüssel wieder erhalten können, ohne Datensätze zu verlieren oder zu erfinden. Das gelingt zum Beispiel (hinreichende Bedingung), wenn das gemeinsame Attribut in einer der Tabellen Schlüssel ist (hier AbtNr in Abteilung bzw. Abteilungsname in Standort).

Üben★★☆
Gegeben ist Auftrag(AuftragNr, ArtikelNr, Artikelname, Menge, Kunde) mit dem zusammengesetzten Primärschlüssel (AuftragNr, ArtikelNr). Es gilt: ArtikelNr → Artikelname…

Gegeben ist Auftrag(AuftragNr, ArtikelNr, Artikelname, Menge, Kunde) mit dem zusammengesetzten Primärschlüssel (AuftragNr, ArtikelNr). Es gilt: ArtikelNr → Artikelname und AuftragNr → Kunde.

  1. a) Zeigen Sie, dass die Tabelle nicht in 2. Normalform ist.
  1. b) Zerlegen Sie die Tabelle so, dass die 2. Normalform erfüllt ist.
Lösen
Üben★★★
Gegeben ist Mitarbeiter(MNr, Name, AbtNr, Abteilungsname, Standort) mit Primärschlüssel MNr. Es gilt: AbtNr → Abteilungsname und Abteilungsname → Standort. a) Weisen Sie…

Gegeben ist Mitarbeiter(MNr, Name, AbtNr, Abteilungsname, Standort) mit Primärschlüssel MNr. Es gilt: AbtNr → Abteilungsname und Abteilungsname → Standort.

  1. a) Weisen Sie nach, dass die Tabelle in 2., aber nicht in 3. Normalform ist.
  1. b) Zerlegen Sie die Tabelle in die 3. Normalform und geben Sie die Fremdschlüssel an.
  1. c) Erläutern Sie, welche Änderungsanomalie durch die Zerlegung vermieden wird, anhand eines konkreten Beispiels (Umzug einer Abteilung).
Lösen
Üben★★★
Ein Krankenhaus in Hamburg speichert Patienten, Ärzte, Stationen und Behandlungen. Eine Behandlung verbindet genau einen Patienten mit genau einem Arzt an einem Datum…

Ein Krankenhaus in Hamburg speichert Patienten, Ärzte, Stationen und Behandlungen. Eine Behandlung verbindet genau einen Patienten mit genau einem Arzt an einem Datum und hat eine Diagnose. Ein Patient liegt zu einem Zeitpunkt auf höchstens einer Station.

  1. a) Modellieren Sie die Situation als ER-Diagramm (in Textform) mit Kardinalitäten. Begründen Sie, ob Behandlung als Entitätstyp oder als Beziehung modelliert wird.
  1. b) Leiten Sie das relationale Schema in 3. Normalform her.
  1. c) Begründen Sie, warum (PatientNr, ArztNr) allein kein geeigneter Primärschlüssel von Behandlung ist.
Lösen
Üben★★★
Eine Tabelle Kurswahl(SNr, Name, KursNr, Kursname, Lehrer, Raum) einer Schule enthält folgende Abhängigkeiten: SNr → Name; KursNr → Kursname, Lehrer; Lehrer → Raum. a)…

Eine Tabelle Kurswahl(SNr, Name, KursNr, Kursname, Lehrer, Raum) einer Schule enthält folgende Abhängigkeiten: SNr → Name; KursNr → Kursname, Lehrer; Lehrer → Raum.

  1. a) Bestimmen Sie den Primärschlüssel und begründen Sie Ihre Wahl.
  1. b) Prüfen Sie schrittweise 1., 2. und 3. Normalform.
  1. c) Geben Sie ein Schema in 3. Normalform an und begründen Sie, dass sich die ursprüngliche Tabelle durch einen Verbund verlustfrei rekonstruieren lässt.
Lösen

Vorgehen bei Aufgaben wie der letzten: (1) Schlüssel bestimmen – welche Attribute müssen zusammen alle anderen festlegen? (2) 1NF: Alles atomar? (3) 2NF: Hängt jedes Attribut vom ganzen Schlüssel ab? (4) 3NF: Hängt ein Attribut von einem Nichtschlüsselattribut ab? Dann jeweils auslagern.

Zurück zum Anfang: Die Büchereitabelle mit den wiederholten Adressen wird in Leser, Buch und die Tabelle Ausleihe zerlegt; jede Adresse steht nur einmal in Leser, und eine Änderung betrifft genau einen Datensatz. Ohne Redundanz können sich die Daten nicht widersprechen: Das erhält die Konsistenz und damit die Integrität, eines der drei Schutzziele der IT-Sicherheit.

Los geht's! 9 Aufgaben in diesem Abschnitt
★☆☆ Eine Stadtbücherei in Münster verwaltet Leserinnen und Leser sowie Bücher. Zu jedem Leser…★☆☆ Gegeben ist die Tabelle Schueler(SNr, Name, Klasse, Klassenlehrer) einer Schule in…★☆☆ In einem ER-Modell stehen die Entitätstypen Lehrer und Kurs. Ein Lehrer unterrichtet…

Auf einen Blick

  • ER-Modell: Entitätstypen mit Attributen und Beziehungen mit Kardinalität (1:1, 1:n, n:m).

  • Primärschlüssel identifiziert eindeutig und nie leer; Sekundärschlüssel dient der schnellen Suche; Fremdschlüssel verweist auf einen Primärschlüssel einer anderen Tabelle.

  • Umsetzung: Entität → Tabelle; 1:n → Fremdschlüssel auf der n-Seite; n:m → eigene Tabelle mit beiden Schlüsseln.

  • 1NF: atomare Werte, keine Wiederholungsgruppen. 2NF: keine partiellen Abhängigkeiten. 3NF: keine transitiven Abhängigkeiten.

  • Anomalien (Einfügen, Ändern, Löschen) entstehen durch Redundanz; Normalisierung vermeidet sie.

  • Die Zerlegung muss verlustfrei sein: Per Verbund erhält man die Originaltabelle zurück.

  • Datenintegrität: Primär- und Fremdschlüssel verhindern doppelte Datensätze und Verweise ins Leere; so unterstützen sie die sichere Nutzung einer Datenbank (z. B. neben Zugriffsrechten).

  • Operatoren: Nennen, Erläutern, Überführen und Begründen Sie – immer mit konkretem Bezug auf die Tabelle.

Alle Aufgaben zu diesem Abschnitt anzeigen