Beziehungen und Kardinalität

Beziehungen und Kardinalität
Beziehungen und Kardinalität
Durch eine Fremdschlüsselspalte wird eine Beziehung (Relation) zwischen zwei Tabellen hergestellt: jeder Datensätze der referenzierenden Tabelle (die mit dem
Fremdschlüssel) ist mit einem Datensatz der referenzierten Tabelle (der „Zieltabelle“) verknüpft.
Da man in eine Fremdschlüsselspalte immer nur einen Wert eintragen kann1, korrespondiert ein Datensatz (eine Reihe = ein Datensatz) aus der referenzierenden
Tabelle immer genau mit einem Datensatz aus der Zieltabelle:
für jeden Helden kann ich nur eine Rasse eintragen.
hero
no
432
97
123
543
name
Alf
Belf
Celf
Delf
rasse
2
1
3
3
…
race
id
1
2
3
beschreibung …
Human
Dwarf
Elf
Umgekehrt kann aber ein Datensatz aus der referenzierten Tabelle mit
mehreren Datensätzen der Tabelle mit dem Fremdschlüssel verknüpft sein: die
Nummer 3 kann mehrfach in der Spalte „rasse“ von „hero“ auftauchen → es kann
mehrere elfische Helden geben.
1 Eine Designvorschrift für relationale Datenbanken verbietet es, in eine Zelle Listen einzutragen.
1
Beziehungen und Kardinalität
Eine Fremdschlüsselspalte entspricht immer einer „1 zu mehrere“-Beziehung (oneto-many relation), diese nennt man auch „1:n“ Beziehung. Dabei steht das
„mehrere“ (also das n) auf der Seite der Tabelle mit dem Fremdschlüssel.
Genau genommen ist die 1:n Beziehung die einzige Art von Beziehung, die man
in relationalen Datenbank aufbauen kann.
Grafische Darstellung – Beziehungen im Entity Relationship Model
Die Struktur einer Datenbank stellt man oft auch grafisch dar. Für das so genannten „Entity Relationship Model“ gibt es mehrere Standards. Allen gemein ist, dass
jede Tabelle (Entität) durch ein Rechteck repräsentiert werden und die Beziehung
durch Linien. In der Chen-Notation wird jede Beziehung durch eine beschriftete
(!) Raute gezeichnet. Durch eine „1“ und ein „n“ wird die „Kardinalität“ der Beziehung gekennzeichnet, also die Angaben darüber, wie viele Datensätze von Tabelle
A mit Tabelle B verknüpft werden können. Dabei steht das „n“ (das „mehrere“) immer auf der Seite der Entität (der Tabelle) mit dem Fremdschlüssel.
2
Beziehungen und Kardinalität
Übungen zu 1:n Beziehungen
Vervollständige die folgenden „Beziehungskreise“ 2
Aufgabe 1
klasse
abk
E1FS1T
E2FS1T
E3FS1T
E1FI1T
E2FI1T
E3FI1T
…
schüler
klassenlehrer
MER
DT
MAS
PAU
NAZ
MEI
…
„Ein Schüler.
2
Auszüge aus Tabellen, Auszüge aus CREATE TABLE, textliche Beschreibung der Beziehung,
Ausschnitt aus ERM
3
Beziehungen und Kardinalität
Aufgabe 2
Aufgabe 3
(Fortgeschrittene: ergänzt auch eine Tabelle „fahrer“)
dienstfahrten
id
1
2
3
4
fahrzeug
FR-CR 633
FR-M 21
FR-CR 633
FR-AJ 712
fahrer
4
1
5
1
datum
…
dienstwagen
kennzeichen
FR-CR 633
FR-M 21
FR-AJ 712
marke
Toyota
Renault
VW
typ
Yaris
Traffic
Passat
…
4
Many-to-many Relationships
Many-to-many Relationships
Rein technisch gibt es in relationalen Beziehung nur one-to-many Beziehungen 3.
Rein logisch kann man damit aber auch eine „mehrere-to-mehrere“ Beziehung
(„many-to-many“ Relationship) aufstellen:
item
id
1
2
3
4
beschreibung
….
Screwdriver
Antistatic Wrist Strap
Smartphone
Buisness Suit
inventory
item
hero
1:n
1
3
4
3
hero
no
432
97
97
432
n:1
432
97
123
name
Alf
Belf
Celf
In diesen Tabellen steht, dass
•
Alf einen Screwdriver und ein Smartphone hat. Ein Held kann also mehrere Gegenstände haben.
•
ein Smartphone im Besitz von Alf ist, ein weiteres (nicht dasselbe, ein anderes
Smartphone!) im Besitz von Belf. Ein Gegenstand (zwei Exemplare einesGegenstands) kann also mehreren Helden zugeordnet sein.
Hier dient die Tabelle „inventory“ als Zwischentabelle, um die „mehrere-zu-mehrere“-Beziehung zwischen Gegenständen und Helden herzustellen.
Eine „many-to-many“-Beziehung zwischen zwei Entitäten wird durch eine zusätzliche Tabelle realisiert, die zwei Fremdschlüsselspalten enthält und damit je eine
1:n Beziehung zu den Tabellen aufbaut, die eine n:n Beziehung eingehen wollen.
In Tabellenform kann man einen „many-to-many“-Beziehung nicht direkt abbilden,
in einem Entity-Relationship-Diagramm dagegen schon:
Lasst Euch nicht von der „dasselbe ↔ das gleiche“-Frage verunsichern - die ent steht unter Umständen auch schon bei 1:n Beziehungen. Manchmal geht es um
„dasselbe“, manchmal um „das gleiche“, das ergibt sich nur aus dem Kontext.
3 Aus denen kann man eine one-to-one Beziehung machen, wenn man die Fremdschlüsselspalte
mit „UNIQUE“ einschränkt.
5
Beispiele:
Beispiele:
Beziehungen mit Attributen
In der Übung zu den 1:n Beziehungen hatten wir die Zwischentabelle zu Dienstwagen und Fahrer schon gesehen – sie heißt dort „dienstfahrt“ und enthält neben
den Fremdschlüsseln auch weitere Angaben ( Datum, Zeit, Ziel der Fahrt etc). Solche zusätzlichen Attribute kann man im ERM auch einzeichnen.
Natürlich kann es vorkommen, dass eine n:n Beziehung so „aufgebläht“ wird, dass
man der Zwischentabelle auch in der Zeichnung eine eigene Entität verpasst (in
diesem Fall die schon bekannte „Dienstfahrt“. Man ist da als Entwickler aber wahlfrei!
6
Relationenmodell
Da im ERM aufgrund der möglichen many-to-many Beziehungen nicht alle Tabellen
erwähnt sind, ist es üblich diese im so genannten „Relationenmodell“ aufzuzählen.
Das ist vor allem eine beliebte Prüfungsfrage! In einem Relationenmodell listet
man alle Tabellennamen Ihren Spalten auf und markiert Primärschlüssel (z.B. unterstrichen oder fett) und Fremdschlüsselspalten (z.B. gestrichelt unterstrichen oder
kursiv).
Für das Dienstwagenbeispiel wäre das also:
Fahrer: Personalnummer, Vorname, Name
Dienstwagen: Kennzeichen, Marke, Typ
Dienstfahrt: Nummer, Fahrzeug, Fahrer, Datum, Zeit , Ziel
Aufgaben
Entscheide für folgende Entitäten, in welchen Beziehungen sie zueinander stehen.
Zeichne das zugehörige ERM und gib das Relationenmodell an.
Musiker und Band
Kunde und Artikel (in einem Online-Warenhaus)
Tätowierung und Tätowierer
Tätowierung und Tätowierter (beachte das „t“ )
7
Entity-Relationship-Model komplett
Entity-Relationship-Model komplett
Bei einem vollständigen Entity-Relationship-Model gibt man die Attribute für alle
Tabellen/Entitäten an. Das ERM des Rollenspiels sähe also folgendermaßen aus.
Beachte: den Primärschlüssel markiert man. Die Fremdschlüssel(spalten) gibt man
nicht extra als Attribut an, dafür wird ja die Beziehung eingezeichnet.
8
Entity-Relationship-Model komplett
Aufgabe 1 (nach einer Aufgabe aus der FIAE-Prüfung Sommer 2011)
Erstelle für folgendes Projekt das ERM der benötigten Datenbank in Chen-Notation und gib das dazu gehörige Relationenmodell an.
Gesprächsaufzeichnung mit dem Geschäftsführer von „Solar-Power“
„Unsere Aufgabe besteht darin, Investoren für Solaranlagen und Vermieter für geeignete Dachflächen zu finden. Jeder Vermieter erhält eine Jahresmiete, die sich aus der Dachflä­
che multipliziert mit einer Quadratmetermiete berechnet. Die
Miete wird für jedes Dach neu ausgehandelt.
In der Regel handelt es sich um sehr große Dächer, diese Dä­
cher mieten die Investoren für eine festgelegte Laufzeit.So­
wohl von Vermietern als auch Investoren speichern wir Vor­ und Nachnamen sowie eine Kontoverbindung.
Für die Verwaltung der Solarmodule müssen eine Nummer und die bisher erzeugte Energie gespeichert werden. Außerdem müssen wir wissen, auf welchem Dach die Anlage aufgebaut ist
und welches Modul welchem Investor gehört.“
ERM in Crow's Foot Notation (Martin Notation)
Die Chen Notation von ERMs ist
relativ raumgreifend. Deutlich kompakter ist die Krähenfußnotation,
da hier die Attribute direkt in dem
Rechteck aufgelistet werden, die
die Entität repräsentiert. Eine Ein
Krähenfuß kennzeichnet eine 1:n
Beziehung (der Fuß ist auf der Seite des „mehrere“) ein doppelter
Krähenfuß steht für „many-tomany“. Der Beziehungstext (ja, das
muss sein) wird einfach an die Linie geschrieben. Da das Tool „yed“, mit dem ich
die Zeichnung erstellt habe, das Unterstreichen einzelner Texte nicht unterstützt,
habe ich die Primärschlüssel mit einem Stern gekennzeichnet.
9
Entity-Relationship-Model komplett
Aufgabe 2
Benutze das Programm „yed“ um ein ERM in Krähenfußnotation für folgende Anwendung zu erstellen.
Sie entwickeln die Datenbank für eine Online­Videoplattform.
Hier können registrierte Benutzer Videos hochladen. Zu jedem
Video wird ein Titel gespeichert, ein Beschreibung, das Da­
tum des Uploads und der Pfad zur Videodatei. Außerdem können
registrierte Benutzer Kommentare zu Videos abgeben. Dabei kann ein Kommentar auch als Antwort auf einen anderen Kom­
mentar dienen.
Jeder User kann Kanäle erstellen und seine Videos diesen Kanälen zuordnen. Dabei kann ein Video durchaus zu mehreren Kanälen gehören. Diese Kanäle können von anderen Usern abon­
niert werden. Bei der Speicherung eines Abonnements wird auch festgehalten, wann die Abonnierung stattfand.
Aufgabe 3 (FIAE Prüfung Winter 2013)
Verwende die Software „dia“ um ein ERM in Chen-Notation für folgendes Szenario zu erstellen.
10
Entity-Relationship-Model komplett
Aufgabe 4
Mögliche Lösung für ERM 1
1. Gib die CREATE Befehle an, um die im ERM gezeigte Datenbank zu erstellen.
2. Erstelle einen SELECT-Befehl, der alle Vermieter auflistet ( voller Name und
Kontoverbindung).
3. Schreibe einen SELECT-Befehl, der alle Investoren auflistet (voller Name
und Anzahl der Module, an denen Sie beteiligt sind).
4. Gib den SELECT-Befehl an, der alle Personen auflistet. Ist eine Person ein
Vermieter, so wird auch die Anzahl der Dächer angegeben, die der Person
gehören (dafür brauchst Du einen LEFT JOIN bzw. RIGHT JOIN).
11