Begleitmaterial zum Unterricht M104 «Relationen»

Von Kardinalitäten bis zum ERD

1. Theorie zu ERD erarbeiten

Lernziele:

  • Sie können Beziehungen zwischen Tabellen erkennen und mit Typen versehen
  • Sie kennen die vier Beziehungstypen und die daraus möglichen Kombinationen
  • Sie können Tabellen und Beziehungen mittels ERM grafisch darstellen
  • Sie kennen die notwendigen Attribute, um die Beziehungstypen zu realisieren

Erarbeiten sie die Theorie zu folgenden Themen:

  • Entitätenblockdiagramm
  • Kardinalität
  • Die zehn Beziehungstypen 1:1 / 1:c / 1:m / 1:mc / c:c / c:m / c:mc / m:m / m:mc / mc:mc
  • Referentielle Integrität
  • Tabellenbeschreibung

und erstellen sie eine Zusammenfassung. (Sie finden z.B. auf dieser Webseite die entsprechenden Fachbeiträge)

2. Aufgaben zu ERD

2.1 Relationen bestimmen

Laden und entpacken Sie diese OpenOfficeBase-Datenbanken: RelationenDB.zip

Es sind drei odb-Datenbanken in diesem ZIP-File enthalten. Starten sie diese nacheinander und versuchen Sie, die Beziehungstypen zu ermitteln. Zeigen Sie für jede dieser drei Datenbanken auf, wie die Beziehung realisiert ist, indem Sie die Tabellenfelder untersuchen. Beschreiben sie die Relation in einer Skizze der folgenden Art:

Erstellen sie für jede Datenbank eine Tabelle mit den Angaben wie Primär-, Fremdschlüssel etc. mit folgendem Aussehen: (Dies ist eine Tabellenbeschreibung)

  • Datenbank: Relation1.odb
    Tabelle1: T_KFZ_NR (Autokontrollschild)
    Tabelle2: T_KFZ_SN (Fahrzeugseriennummer)
    Situationsbeschreibung: Im Strassenverkehrsamt wird erfasst, welches Fahrzeug welches Kontrollschild besitzt
    Rolle der Beziehung: Immatrikuliertes Fahrzeug
  • Datenbank: Relation2.odb
    Tabelle1: T_KFZ_NR (Autokontrollschild)
    Tabelle2: T_Personen (Erfasste Personen)
    Situationsbeschreibung: Im Strassenverkehrsamt wird erfasst, welche Person welches Auto besitzt
    Rolle der Beziehung: Fahrzeugbesitzer
  • Datenbank: Relation3.odb
    Tabelle1: T_Automarke (Marke des Fahrzeugs)
    Tabelle2: T_Personen (Erfasste Personen)
    Tabelle3: ???
    Situationsbeschreibung: Eine Agentur führt eine Statistik über die Beliebtheit von Automarken
    Rolle der Beziehung: Markenpräferenz

2.2. Einfache Rechnungsverwaltung analysieren

Zeichnen sie für diese 5 Tabellen das ERD:

2.3. Referentielle Integrität erfahren

In dieser Aufgabe sollen sie die Auswirkung von «Referentieller Integrität» erleben. Erstellen sie dazu in OpenOffice/LibreOffice zwei Tabellen, die in einer 1:mc-Beziehung stehen und wo die eine Tabelle einen Fremdschlüssel enthält, der auf den Identifikationsschlüssel bzw. Primärschlüssel der anderen zeigt.

  • Beide Tabellen besitzen einen Primärschlüssel und je ein weiteres Attribut
    (Z.B. Tabelle1: P_ID, Name, O_ID / Tabelle2: O_ID, Ort)
  • Definieren Sie die Beziehung zwischen diesen beiden Tabellen
    (In OOoBase unter Extras/Beziehungen)
  • Erfassen Sie in beiden Tabellen Daten – Versuchen sie Fremdschlüssel zu erfassen, die noch nicht als Primärschlüssel existieren – Geht das?
  • Löschen Sie verschiedene Datensätze, solche ohne Verknüpfung und solche mit einer bestehenden Beziehung durch den Fremdschlüssel – Ihre Erkenntnis?
  • Anschliessend entfernen Sie die referenzielle Integrität…
    (In OOoBase unter Extras/Beziehungen und Beziehung mit RMB > Löschen)
    …und versuchen Gleiches wie zuvor
  • Sie sollten nun erkennen, dass Sie «Referenzielle Integrität»…
    (In OOoBase unter Extras/Beziehungen und Beziehung wieder aktivieren)
    …nur dann wieder erhalten, wenn zuvor keine Widersprüche erzeugt wurden
  • Testen Sie jetzt noch das Verhalten bei Aktivierung von «Kaskadiert löschen»
    (In OOoBase unter Extras/Beziehungen und Beziehung mit RMB > Bearbeiten)
    Löschen sie in der einen Tabelle einen Datensatz und überprüfen sie in der anderen Tabelle, wo Fremdschlüssel in die erste Tabelle verweisen, was nun alles gelöscht wurde

2.4. Den Unique Constraint anwenden

In einer Tabellenspalte soll jeder Wert nur einmal vorkommen oder datenbanktechnisch formuliert: Der Skalar muss innerhalb des Attributes einzigartig sein. Dies ist z.B. bei einem Primärschlüssel notwendig. Bei OOoBase und auch Access ist das automatisch der Fall. Anders sieht es aus, wenn eine weiteres Attribut einzigartig sein muss, wie z.B. die Tabelle T_KFZ_NR im obigen Beispiel, wo aus verständlichen Gründen die Nummer eines Fahrzeug-Kontrollschildes nur einmal vorkommen darf. Bei Access ist es kein Problem, diesen Constraint zu ergänzen. Bei der aktuellen (2017) Version von OOoBase muss man das von Hand, bzw. mit einem SQL-Kommando erledigen:

  • SQL-Editor aufrufen unter Extras/SQL
  • ALTER TABLE "T_Auto" ADD UNIQUE ("Kontrollschild"); eintippen und ausführen
    (Das Semikolon «;» am Schluss nicht vergessen)
    («T_Auto» ist der Tabellennamen und «Kontrollschild» das Attribut)

Aufgabe: Ergänzen sie in der aus Aufgabe 2.1 «Relationen bestimmen»  stammenden OOoBase-Datenbank Relation1.odb in der Tabelle T_KFZ_NR das Attribut Autokontrollschild mit obigem SQL-Befehl auf UNIQUE. Versuchen sie nun, zweimal dieselbe KFZ-Nummer einzugeben. Was stellen sie fest?

Hinweis: Wenn 1:1 Relationen verlangt sind, kann dies ebenfalls mit dem UNIQUE-Constraint erzwungen werden oder noch besser, das Ganze auf eine Tabelle reduzieren und damit die Übersichtlichkeit des ERD’s etwas fördern.

2.5 PC-Komponenten verwalten

Setzen sie die folgende Tabellenbeschreibung in ein ERD um. Realisieren sie anschliessend die Datenbank mit OOoBase.

(PS=Primärschlüssel; FS=Fremdschlüssel; NN=NOT NULL; UN=UNIQUE)

Hinweis: Bei der Tabelle Ort müsste nach den Kriterien der Datenmodellierung der Kanton in eine eigene Tabelle ausgegliedert werden und mit einem Fremdschlüssel verknüpft werden. Damit könnten zusätzlich Daten redundanzfrei zu den Kantonen abgespeichert werden. Hier wurde darauf verzichtet, da die Daten in genau dieser Form von der Post zur Verfügung gestellt werden und zu den Kantonen keine weiteren Informationen abgespeichert werden sollen.

2.6 Einfache Rechnungsverwaltungs-DB

Erstellen sie nun die Datenbank gemäss ihrem ERD aus Aufgabe 2.2  «Einfache Rechnungsverwaltung analysieren».
Nachdem sie die Tabellen erstellt haben, erfassen Sie einige sinnvolle Daten. Die Tabelle «Ort» brauchen Sie übrigens nicht selbst zu erfassen. Die Webseite der Swisspost kann ihnen da sicher mit Datenmaterial weiterhelfen, das ihnen alle Orte und Postleitzahlen liefert. ERD’s etwas fördern.

2.7 Einen Velohandel verwalten

Öffnen Sie die hier als ZIP-Datei bereitgestellte Datenbank Velohandel.zip. Bestimmen Sie die Assoziationstypen durch die Definitionen von «UNIQUE» und «NOT NULL» in den Tabellenentwürfen entsprechend der Datenbank. Zeichnen Sie anschliessend das ERM zur Datenbank Velohandel.
(Das ERD soll Rechtecke mit den Entitätsnamen, die Beziehungen untereinander und die Assoziationstypen in der Form 1, c, m, mc enthalten)

3. Musterlösungen

3.1. Musterlösungen Relationen bestimmen

3.2. Musterlösungen Einfache Rechnungsverwaltung analysieren

3.3. Musterlösungen Referentielle Integrität erfahren

Keine Musterlösung erforderlich.

3.4. Musterlösungen Den Unique Constraint anwenden

Keine Musterlösung erforderlich.

3.5 Musterlösungen PC-Komponenten verwalten

3.6 Musterlösungen Einfache Rechnungsverwaltungs-DB

Keine Musterlösung erforderlich.

3.7 Musterlösungen Einen Velohandel verwalten