get-the-solution

Datenbank erstellen mit dem ER-Diagramm und MySql

Datenbanken erstellen / modellieren mit dem ER-Modell

Ein Modell ist ein Ausschnitt aus der Realität oder ein gedankliches Konzept. Das Entity-Relationship-Modell ist ein Modell das aus Gegenständen und Beziehungen besteht. Es wird häufig verwendet um eine Datebank zu designen.

Beim ERM-Model handelt es sich bei den Entities meist um einen physikalischen Gegenstand, oder um ein gedankliches Konzept. Die Beziehungen zwischen den Gegenständen werden abstrahiert.

• Grundlagen
• Beziehungstypen
• Datenbank erstellen
• Literatur

Grundlagen

Entity

Ein Gegenstand wird im Modell durch ein Rechteck abgebildet. Der Gegenstand kann Eigenschaften enthalten die durch Attribute dargestellt werden. Attribute die zu einem Gegenstand gehören werden durch Linien verbunden.

Schlüssel sind eine minimale Menge von Attributen, deren Wert eine Entity eindeutig innerhalb aller Entities eines Types identifizieren. Schlüssel werden durch unterstrichene Attributnamen dargestellt.

Generalisierung

Die Generalisierung soll für ein übersichtliches und strukturiertes Modell sorgen. Dies kann erzielt werden, in dem Entities mit den gleichen Attributen zu einem Basis Entity zusammengefasst werden. Die Unterentities „erben“ die Attribute des Basis Entitys. Das Erben der Unterenties kann symbolisch durch einen Pfeil dargestellt werden. Im unteren Beispiel erben Mitarbeiter und Kunde die Attribute von Person.

Relationship

Beziehungstypen werden durch Rauten dargestellt. Diese werden dazu passend beschriftet.

Beziehungstypen

Es gibt verschiedene Beziehungstypen.

1:1 Beziehung

Jedem Entity m aus Mann wird höchstens ein Entity f aus Frau zugeordnet. Umgekehrt verhält es sich genauso. Jedem Entity f aus Frau wird maximal ein Entity m aus Mann zugeordnet. Es kann auch vorkommen, dass Entities keinen Partner haben.

Die Relation kann wie folgt in Worten beschrieben werden. Ein Mann ist mit einer Frau verheiratet. Eine Frau ist mit einem Mann verheiratet.

1:n Beziehung

Jedem Entity h aus Haus wird kein oder beliebige viele Entities aus Bewohner zugeordnet. Jedes Entity b aus der Menge Bewohner steht maximal einem Entity aus Haus in einer Beziehung.

Die Relation kann wie folgt in Worten beschrieben werden.

Ein Haus hat mehrere Bewohner. Mehrere Bewohner können in einem Haus sein.

n:1

Verhält sich wie die 1:n Beziehung nur umgekehrt.

N:m

Wenn keine Einschränkungen und Begrenzungen gelten, also jedes Entity aus E1 mit beliebig vielen Entities aus E2 in Beziehung stehen kann und umgekehrt jedes Entity aus E2 mit beliebig vielen Entities aus E1 assoziiert ist, handelt es sich um eine n:m Beziehung. (vgl. Alfons Kempler 2009)

Min / Max Notation

Mit der Min / Max Notation kann man die Beziehungstypen genauer spezifizieren. Wir können z.B. sagen, dass kein oder ein Haus von genau einer Person besessen wird. Eine Person kann aber 0 oder mehrere Häuser besitzen. Die Notation würde dann wie folgt aussehen.

Da wir beim Erstellen der Datenbank die Min / Max Notation außer Acht lassen, verweise ich deshalb für weitere Informationen auf den Wikipedia Artikel und auf das Kapitel 6.6 Die (min, max) – Notation von der Online-Kurs ‘Datenbanken und Datenmodellierung’ Print Version.

Datenbank erstellen

Nach dem also das Model nach den ER-Diagramm Richtlinien erstellt wurde, gilt es den Entwurf in eine Datenbank zu übertragen. Ich gehe ab hier davon aus, dass man das Grundlegende über Datenbanken weiß.

Fremdschlüssel dienen als Verweis zwischen zwei Relationen, d. h. er zeigt an, welche Tupel / Zeile der Relationen inhaltlich miteinander in Verbindung stehen.

Als erstes beginnt man damit, die Entities in Tabellen um zu wandeln. Als Tabellenamen werden vorzugsweise die Entitynamen verwendet. Alle Attribute werden in der Tabelle als Spalten dargestellt. Dabei muss man sich jetzt überlegen, welches Attribut welchen Datentyp verwenden soll.

Folgendes Modell kann man wie folgt in eine Tabelle übersetzen (für das Beispiel wurde eine MySQL DB verwendet):

CREATE TABLE `Autor` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 50 ) NULL
) ENGINE = InnoDB;

Wie man sehen kann wurde der Attributname „Identifikationsnr“ auf id verkürzt und als Primary Key gesetzt. Für den Name wurde der Datentyp varchar verwendet. So werden die Entities der Reihe nach in die Datenbank „übersetzt“.

Als nächstes muss man in der Datenbank die Voraussetzungen dafür schaffen, dass die Beziehungen abgebildet werden können. Am Einfachsten ist die n:m Beziehung ab zu bilden.

Aus der Min / Max Notation „(0,*)“ erkennen wir, dass es sich um eine „m:n“ Beziehung handelt.

N:M Beziehungen benötigen immer eine Hilfstabelle! In der Hilfstabelle müssen wir sogenannte Fremdschlüssel verwenden, um auf die jeweilige Tabelle zu verweisen. In die Hilfstabelle erstellen wir also zwei Spalten, die jeweils die Bezeichnung auf der zu verweisenden Schlüssel verwenden. Da die Beziehung „besuchen“ heißt, erstellen wir passenderweise eine Tabelle namens besuchen. Darin erstellen wir die zwei Spalten Schuelerid und Fachid. Das sieht dann so aus:

CREATE TABLE `Schueler` (
`Schuelderid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 100 ) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE `Unterrichtsfach` (
`Fachid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 50 ) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE `besuchen` (
  `Schuelderid` int(11) NOT NULL,
  `Fachid` int(11) NOT NULL
) ENGINE=InnoDB;

Anbei noch ein paar Testeinträge:

INSERT INTO `Schueler` (
`Schuelderid` ,
`Name`
)
VALUES (
NULL , 'Martin'
), (
NULL , 'Simon'
);
INSERT INTO `Unterrichtsfach` (
`Fachid` ,
`Name`
)
VALUES (
NULL , 'Betriebssysteme'
), (
NULL , 'Datenmodellierung'
);

Wenn Martin jetzt die die Unterrichtsfächer ‘Betriebssysteme’ und ‘Datenmodellierung’ besuchen soll, müssen wir die entsprechenden Einträge in die Tabelle ‘besuchen’ machen. Z.B. mit:

INSERT INTO `besuchen` (
`Schuelderid` ,
`Fachid`
)
VALUES (
'1', '1'
), (
'1', '2'
);

INSERT INTO `besuchen` (
`Schuelderid` ,
`Fachid`
)
VALUES (
'2', '2'
);

Nun fragen wir ab welche Fächer Martin besucht.

SELECTs.name,u.name
FROM`besuchen`ASb,UnterrichtsfachASu,SchuelerASs
WHEREb.fachid=u.fachid
ANDs.Schuelderid=b.Schuelderid
ANDs.name='Martin';

Aus der unteren Abbildung ist erkenntlich, dass ein Schüler 0 oder maximal einen Computer verwenden kann. Ein Computer ist 0 oder mehreren Schüler zugeordnet. Es handelt sich also hier um eine 1:N Beziehung. Da wir im ersten Schritt alle Entities in der Datenbank implementiert haben, sieht unsere Computer Tabelle so aus:

CREATE TABLE `Computer` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 10 ) NOT NULL
) ENGINE = InnoDB;

Anders wie bei einer N:M Beziehung muss nicht eine Hilfstabelle erstellt werden. Der Fremdschlüssel der Computertabelle kann direkt in die Tabelle des Schülers hinzugefügt werden.

ALTERTABLESchuelerADDcomputeridINTNULLCOMMENT'verweis auf tabelle computer'

In die Computertabelle dürfte man keine Spalte erstellen um den Fremdschlüssel vom Schüler zu speichern, weil laut ER-Diagramm ein Computer mehreren Schüler zugeordnet sein kann.

Bei dieser Abbildung wird davon ausgegangen, dass dem Schüler ein Computer zugeordnet sein kann, aber nicht muss. Deshalb sind in der Spalte computerid NULL Einträge erlaubt. Falls man davon ausgeht, dass ein Schüler 0 oder mehrere Computer verwendet, müsste man dies mit einer N:M Beziehung realisieren.

Theoretisch könnte man eine 1:N Beziehung auch mit einer Hilfstabelle realisieren, jedoch versucht man in der Praxis, überflüssige Tabellen zu vermeiden. Denn wenn man bei größeren ER-Diagrammen alle Beziehungen mit Tabellen übersetzt, kann die Datenbank schnell unübersichtlich werden.

Bei einer 1:1 Beziehung wird so vorgegangen wie im oberen 1:N Beispiel. Die Verwendung von Hilfstabellen ist nicht möglich. Um die Tabellen miteinander zu verknüpfen, wird in die jeweilige Tabelle für die man sich entscheidet, eine zusätzliche Spalte erstellt in die man den Fremdschlüssel speichert. Ein Beispiel dazu:

CREATE TABLE `Mann` (
`idmann` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB;

CREATE TABLE `Frau` (
`idfrau` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`Name` VARCHAR( 100 ) NOT NULL
) ENGINE = InnoDB;

Die Beziehung kann folgendermaßen realisiert werden:

ALTERTABLEMannADDidfrauINTNULLCOMMENT'verweis auf tabelle frau‘

Dabei spielt es keine Rolle welche Tabelle verwendet wird. Wir hätten genauso zur Tabelle Frau die Spalte mit dem Fremdschlüssel hinzufügen können.

Hat man alle Beziehungen in der Datenbank implementiert kann man als nächstes die Integritätsbedingung erstellen. Die Erstellung der Referenzielle Integritätsbedingung hier auch noch auszuführen sprengt allerdings den Rahmen. Ich verweise deshalb vorerst auf den Wikipedia Artikel “Referentielle Integrität”.

Literatur

Beziehungen, 09.02.2011, http://sql.idv.edu/thema/dbgrundlagen/beziehungen.htm#5

Referentielle Integrität, 09.02.2011, http://de.wikipedia.org/wiki/Referentielle_Integrit%C3%A4t#Verwendung_in_Datenbanksystemen

Integritätsbedingung, 09.02.2011, http://de.wikipedia.org/wiki/Integrit%C3%A4tsbedingung

A. Kemper A. Eickler Datenbanksysteme, 09.02.2011, http://www-db.in.tum.de/research/publications/books/DBMSeinf/

Alfons Kempler. (2009) Datenbanksysteme - Eine Einführung 7 Auflage, Oldenbourg Wissenscahftsverlag GmbH München