Datenbanken
Entity-Relationship-Modell
Andreas Heß
Hochschule Furtwangen
Inhalte heute
• Einführung ins Entity-Relationship-Modell
• Einführung ins relationale Modell
• Umsetzung vom E/R- ins relationale Modell
• Erste SQL-Statements
Begriffsklärung
Name Vorname Straße PLZ Ort Mail
Kemper Alfons Schillerstr. 15 55116 Mainz
[email protected]Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected]
Heß Andreas Lessingstr. 25 78120 Furtwangen
Zeilen
[email protected] Tupel
Heß Andreas Goethestr. 39 60318 Frankfurt
[email protected]Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected]
Spalten / Attribute
Adressdaten in Datei
Name Vorname Straße PLZ
Eine Person mit mehrerenOrt Mail
Kemper Alfons Adressen
Schillerstr. 15- wie?
55116 Mainz [email protected]
Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected]
Heß Andreas Lessingstr. 25 78120 Furtwangen [email protected]
Heß Andreas Goethestr. 39 60318 Frankfurt [email protected]
Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected]
➔ Manche Informationen doppelt
➔ unklar, ob es sich um die selbe Person handelt
Personen identifizieren
P-Nr Name Vorname Straße PLZ Ort Mail
1 Kemper Alfons Schillerstr. 15 55116 Mainz [email protected]
2 Meier Hans Goethestr. 23a 12345 Mönchengladbach [email protected]
3 Heß Andreas Lessingstr. 25 78120 Furtwangen [email protected]
Redundanz!
3 Heß Andreas Goethestr. 39 60318 Frankfurt [email protected]
4 Albrecht Alexandra Marktplatz 1 67543 Heidelberg [email protected]
➔ Person jetzt eindeutig identifiziert
Primärschlüssel?
Konzeptuelles Modell
• Wildes „Herumschrauben“ an Tabellen ist
nicht zielführend
• Was wir brauchen ist ein sauberer Entwurf!
ONE DOES NOT SIMPLY
CREATE A DATABASE
Modell: Abbild der Welt
• Überlegungen:
• Welche Dinge gibt es in der realen Welt?
• Wie stehen diese in Beziehung?
Beziehungen
1 n
Person wohnt Adresse
1
Entity Relationship
mailt
Entity-Relationship-Diagramm
n
Mailadresse
Modell: Abbild der Welt
• Überlegungen:
• Welche Dinge gibt es in der realen Welt?
• Wie stehen diese in Beziehung?
• Welche Daten möchte ich über diese
Dinge speichern?
Attribute
Name Vorname
1 n
Person wohnt Adresse
1
mailt
Entity-Relationship-Diagramm
n
Mailadresse
Abbildung auf Tabellen
• Wieviele Tabellen braucht man?
• Welche sind das?
• Wie sehen sie aus?
Abbildung auf Tabellen
Personen Adressen
P-Nr Name Vorname P-Nr Straße PLZ Ort
1 Kemper Alfons 1 Schillerstr. 15 55116 Mainz
2 Meier Hans 2 Goethestr. 23a 12345 M‘gladbach
3 Heß Andreas 3 Lessingstr. 25 78120 Furtwangen
4 Albrecht Alexandra 3 Goethestr. 39 60318 Frankfurt
4 Marktplatz 1 67543 Heidelberg
Mailadressen
Primär- P-Nr Mail
schlüssel 1 [email protected]
Fremdschlüssel
2 [email protected]
3 [email protected]
4 [email protected]
Schlüssel
• Fremdschlüssel einer Tabelle ist
Primärschlüssel einer anderen Tabelle
Datenbank-Architektur
Sicht 1 Sicht 2 ... Sicht n
ANSI-SPARC-
logische Ebene Architektur
physische Ebene
logische Ebene
Fragen...
• Auf welcher Ebene spielt sich das alles ab?
• Welche Rolle könnten Sichten spielen?
AuftrNr
1 n
Person bestellt Bestellung
m
Produkt enthält
Wie wird diese
Art von
ArtNr Bezeichnung
Beziehung
abgebildet?
Abbildung auf Tabelle
enthaelt
Art-Nr Auftr.-Nr.
1 1234
1 9876
3 9876
3 4711
Beziehungstabelle
Rückblick
• Motivation Adressdatenbank
• Probleme bei mehreren Anschriften
• Konzeptionell „sauberer“ Entwurf nötig
• Entity-Relationship-Modell: Beziehungen
zwischen Entitäten (Gegenständen)
Datenbankentwurf
• Drei Ebenen des Entwurfs:
• konzeptuelle Ebene
• Implementationsebene
• physische Ebene Hat das was mit der ANSI/
SPARC-Architektur zu tun?
Auf welcher Ebene haben
wir bisher gearbeitet?
Entwurfsebenen
• Konzeptuelle Ebene:
• Entity-Relationship-Modell
• Implementationsebene:
• Tabellen (Relationenmodell)
• Physische Ebene
• vom DBMS verwaltet
Entity-Relationship-Modell
• Modellierung von Entitäten (Gegenständen)
und Beziehungen zwischen diesen Entitäten
• Konzeptuelles Modell
Als konzeptuelles Modell
komplett unabhängig von einem
konkreten Typ von DBMS!
Adressdatenbank
Schlüssel
identifiziert die
Entität eindeutig
PersNr Name Vorname Straße Hausnr
Attribut
1 n
Person wohnt Adresse
1 n
Entität
mailt
PLZ liegt in
von
n Beziehung 1
Ortsname
Mailadresse Ort
Mailaddr
E-Commerce
PersNr Name Vorname AuftrNr
1 n
Person beauftragt Bestellung
n
Beziehungen
m können auch
Produkt enthält Attribute haben
Menge
ArtNr Bezeichnung Farbe
Abbildung auf Tabelle
Produkt_Bestellung
Art-Nr Auftr.-Nr. Menge
1 1234 1
1 9876 5
3 9876 1
3 4711 1
Relationales Modell
• Modellierung als Tabellen (Relationen)
• Implementierungsebene
Die meisten aktuellen DBMS sind
relationale DBMS (RDBMS),
speichern also Daten in Tabellen
! Relationen ?
• Verwechslungsgefahr!
• Relationen im relationalen Modell nicht zu
verwechseln mit den Beziehungen im ER-
Modell.
• Bißchen Mathematik zur Erklärung...
Relationen
R ⊆ D1 × D2 × ... × Dn
• Relation ist Teilmenge des kartesischen
Produkts (Kreuzprodukts) der Mengen D1
bis Dn
• Mengen D bis Dn heißen Domänen oder
1
Wertebereiche
Relationen: Beispiel
R ⊆ D1 × D2 × ... × Dn
Personen ⊆ integer × string × string
Personen
• Relation = Tabelle PersNr
1 Kemper
Name Vorname
Alfons
• Domänen = Datentypen 2 Meier
3 Heß
Hans
Andreas
• Elemente von R = Tupel = Zeilen
Übung
• Umwandlung des E/R-Modells in Tabellen
Abbildung auf Tabellen
• Für die Adressdatenbank:
• Wieviele Tabellen braucht man?
• Welche sind das?
• Wie sehen sie aus?
• Wie werden die 1:n-Beziehungen
abgebildet?
Adressdatenbank
PersNr Name Vorname Straße Hausnr
1 n
Person wohnt Adresse
1 n
mailt
PLZ liegt in
von
n 1
Ortsname
Mailadresse Ort
Mailaddr
Adressdatenbank
Personen Adressen
PersNr Name Vorname 1:n-Beziehungen werden über
PersNr Straße PLZ
1 Kemper Alfons Fremdschlüssel abgebildet
1 Schillerstr. 15 55116
2 Goethestr. 23a 12345
2 Meier Hans
3 Lessingstr. 25 78120
3 Heß Andreas
3 Goethestr. 39 60318
Primär- Orte
schlüssel Mailadressen Fremd- PLZ Ort
PersNr Mailadresse schlüssel 55116 Mainz
Schlüssel - 1
• Primärschlüssel identifiziert eine Entität
eindeutig
• Fremdschlüssel stellen Beziehungen
zwischen Entitäten her
Schlüssel - 2
• Primärschlüssel identifiziert eine Zeile in
der Tabelle eindeutig
• Fremdschlüssel stellen Beziehungen zu
anderen Tabellen her
Schlüssel - 3
• Fremdschlüssel einer Tabelle ist
Primärschlüssel einer anderen Tabelle
Abbildung auf Tabellen
• Für die E-Commerce-Datenbank:
• Wieviele Tabellen braucht man?
• Welche sind das?
• Wie sehen sie aus?
• Könnte es ein Problem mit der
n:m-Beziehung geben?
E-Commerce
PersNr Name Vorname AuftrNr
1 n
Person beauftragt Bestellung
n
m
Produkt enthält
Menge
ArtNr Bezeichnung Farbe
E-Commerce
Produkt Bestellung
ArtNr Bezeichnung Farbe AuftrNr PersNr
21 Zahnbürste rosa 34 1
22 iPod Touch schwarz 35 2
23 Gummientchen gelb 36 3
Produkt_Bestellung
ArtNr AuftrNr Menge
21 34 5
n:m-Beziehungen werden über
23 34 1 Beziehungstabellen abgebildet
22 35 1
Fremdschlüssel
Relationales Modell
• Schreibweise für relationales Modell:
Relation : {[ Primärschlüssel : Datentyp,
Attribut : Datentyp,
Fremdschlüsselattribut : Datentyp ( ➚ Relation)
]}
Beispiel:
Adresse : {[ AdressID : INTEGER,
PLZ : DECIMAL(5,0) ( ➚ Orte),
Strasse : VARCHAR(50)
]}
Zusammenfassung Teil 1
• E/R-Modelle sind rein konzeptionell!
• Umsetzung ins relationale Modell:
• Entitäten ➜ Tabellen (Relationen)
• 1:n-Beziehungen
➜ Fremdschlüssel (auf der n-Seite)
• n:m-Beziehungen
➜ immer mit Beziehungstabelle
Datentypen
• Welche Datentypen sollen die Spalten der
Tabellen bekommen?
Datentypen in DBMS
INTEGER ganze Zahlen
NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen
FLOAT(n), DOUBLE, REAL Gleitkommazahlen
CHARACTER(n), VARCHAR(n) Zeichenketten
DATE, TIME, TIMESTAMP Datum und Zeit
BLOB, CLOB große Daten
BOOLEAN wahr / falsch
Datentypen in DBMS
INTEGER ganze Zahlen
NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen
FLOAT(n), DOUBLE, REAL Gleitkommazahlen
CHARACTER(n), VARCHAR(n) Zeichenketten
DATE, TIME, TIMESTAMP Datum und Zeit
BLOB, CLOB große Daten
BOOLEAN wahr / falsch
Datentypen in DBMS
INTEGER ganze Zahlen
NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen
FLOAT(n), DOUBLE, REAL Gleitkommazahlen
CHARACTER(n), VARCHAR(n) Zeichenketten
DATE, TIME, TIMESTAMP Datum und Zeit
CHAR immer feste Länge
große Daten
VARCHAR variabel bis zur festgelegten Maximallänge
BLOB, CLOB
BOOLEAN wahr / falsch
Datentypen in DBMS
INTEGER ganze Zahlen
NUMERIC(n,m), DECIMAL(n,m) Dezimalzahlen
FLOAT(n), DOUBLE, REAL Gleitkommazahlen
CHARACTER(n), VARCHAR(n) Zeichenketten
DATE, TIME, TIMESTAMP Datum und Zeit
BLOB, CLOB große Daten
BOOLEAN wahr / falsch
Werden vom DBMS nur gespeichert und
gelesen, aber können nicht verarbeitet werden.
NULL
• Undefinierte Werte können mit NULL
gekennzeichnet werden
• NULL ist nicht 0
i n!
Darf ein Primärschlüssel NULL sein?
Ne
SQL
• Structured Query Language1
• ANSI / ISO standardisiert
• DDL und DML für RDBMS
1) früher; heute steht laut ANSI der Name für sich
SQL: DDL
Tabellen erstellen
CREATE TABLE tabelle (
spalte DATENTYP,
spalte DATENTYP,
...
);
SQL: DDL
Tabellen erstellen - Beispiel
CREATE TABLE personen (
nummer INTEGER,
name VARCHAR(100),
vorname VARCHAR(100)
);
SQL: DDL
Primärschlüssel
CREATE TABLE personen (
nummer INTEGER PRIMARY KEY,
name VARCHAR(100),
vorname VARCHAR(100)
);
SQL: DDL
NULL-Werte zulassen / verbieten
CREATE TABLE personen (
nummer INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
vorname VARCHAR(100) NOT NULL
);
PRIMARY KEY impliziert NOT NULL
SQL: DDL
Fremdschlüssel
CREATE TABLE adressen ( SQ
L-S
person INTEGER
t and
ard
REFERENCES personen,
strasse VARCHAR(100) NOT NULL,
plz CHAR(5)
von MySQL
REFERENCES orte ignoriert!
); Wieso braucht man bei REFERENCES nur
eine Tabelle, aber keine Spalte anzugeben?
Fremdschlüssel ist immer Primärschlüssel
einer anderen Tabelle Ok, es gibt Ausnahmen...
SQL: DDL
SQ
Fremdschlüssel L-S
tan
dar
d
auch
CREATE TABLE adressen ( MySQ
L
person INTEGER NOT NULL,
strasse VARCHAR(100) NOT NULL,
plz CHAR(5) NOT NULL,
FOREIGN KEY (person)
REFERENCES Person(PNr)
);
SQL: DML
Daten einfügen
INSERT INTO personen VALUES (
1, 'Kemper', 'Alfons');
INSERT INTO personen(Name, Vorname)
VALUES ('Kemper', 'Alfons');
(z.B. falls Spalten leer bleiben sollen
oder generierte Schlüssel sind)
SQL: DML
Daten abfragen
SELECT * FROM personen
WHERE nummer = 1;
WHERE kann auch weggelassen werden,
dann werden alle Zeilen ausgegeben.
SQL: DML
Daten ändern
UPDATE personen
SET vorname = 'Hans'
WHERE nummer = 1;
SQL: DML
Daten löschen
DELETE FROM personen
WHERE nummer = 1;
Was passiert wohl, wenn
man WHERE weglässt?
WHERE kann auch weggelassen werden,
dann werden alle Zeilen gelöscht!
SQL
Konsistenz bei Fremdschlüsseln
CREATE TABLE adressen (
person INTEGER
REFERENCES personen,
...);
INSERT INTO adressen VALUES (
12345, 'Hauptstr. 5', '78120');
12345 sei kein Primärschlüssel
einer Zeile in Tabelle personen
- Was passiert?
SQL
Konsistenz bei Fremdschlüsseln
CREATE TABLE adressen (
person INTEGER
REFERENCES personen,
...);
INSERT INTO adressen VALUES (
12345, 'Hauptstr. 5', '78120');
Fehlermeldung, wenn 12345 kein
Primärschlüssel einer Zeile in
Tabelle personen
Aufgabe 1/4:
E/R ➙ Relational
• 1. Aufgabe für‘s Praktikum
• Setzen Sie das E/R-Diagramm für unsere
e-Commerce-Datenbank in das
relationale Modell um
• „Pen & Paper“
Aufgabe 2/4:
Installieren
• 2. Aufgabe für‘s Praktikum
• Bringen Sie ein DBMS zum Laufen!
• Im Praktikum wird die Installation von
MySQL erklärt
Aufgabe 3/4:
Relational ➙ SQL
• 3. Aufgabe für‘s Praktikum
• Setzen Sie das relationale Modell für die
e-Commerce-Datenbank aus Aufgabe 1 in
SQL-Anweisungen und geben Sie die
CREATE TABLE-Anweisungen ein
Aufgabe 4/4:
Ausprobieren
• 4. Aufgabe für‘s Praktikum
• Versuchen Sie einige INSERT, UPDATE
und DELETE-Anweisungen und
beobachten Sie mit SELECT das Ergebnis
Hinweise für das
Praktikum
• Gruppeneinteilung
• Datenbank zum Laufen bringen!
show engines;
InnoDB | DEFAULT
setzt InnoDB als Default
Datenbank erzeugen
Datenbank benutzen
Tabellen anlegen
Erstellen eines
SQL-Dumps
Mit mysqldump an der
Befehlszeile des Betriebssystems:
Hinweise zur Abgabe
• PDF
• zusätzlich SQL-Dump
• beides in ZIP
• und in FELIX hochladen!
BIS GLEICH
IM PRAKTIKUM!
