Datenbanken Unit 3: Das relationale Modell

SQL
Das ER Modell
Das Relationale Modell
Datenbanken
Unit 3: Das relationale Modell
Ronald Ortner
7. III. 2017
Ronald Ortner
SQL
Das ER Modell
Outline
1
SQL
2
Das ER Modell
Zusammenfassung
3
Das Relationale Modell
Datenbanken aus ER Modellen
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Das Relationale Modell
Termin zweiter Zwischentest
UE-Tests (Thema: SQL)
zweiter Zwischentest findet am 10. Mai (zur Übungszeit) statt.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Organisatorisches
Blindaufgaben vom Übungsblatt:
Reklamationsmöglichkeit in der UE und per E-Mail
(Bitte Matrikel- und Aufgabennummer angeben!)
Fast richtig (z.B. > statt >=) ist auch falsch!
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
SQL: Schlechte Angewohnheiten
Numerische Werte vs. Strings:
SELECT jahr,monat,ROUND((tmax+tmin)/2,”1”) FROM sowe
WHERE jahr <= ”1940”
AND jahr >= ”1920”
AND (tmin < ”0” or gmin < ”0”)
AND sun > ”90”
funktioniert (leider) in MySQL, Stil nicht empfehlenswert.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
SQL: Schlechte Angewohnheiten
Numerische Werte vs. Strings:
SELECT * FROM sowe
WHERE tmin LIKE ”-%”
funktioniert (leider) in MySQL, Stil nicht empfehlenswert.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
SQL: Schlechte Angewohnheiten
Numerische Werte vs. Strings:
Ähnlich:
Runden auf ganze Millionen über numerische Funktionen,
nicht über Stringfunktionen!
Ronald Ortner
SQL
Das ER Modell
SQL-Lesson 3
Heute:
Subqueries
UNION
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Zusammenfassung
Outline
1
SQL
2
Das ER Modell
Zusammenfassung
3
Das Relationale Modell
Datenbanken aus ER Modellen
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Zusammenfassung
Zusammenfassung letzter Woche
Entitäten und Relationen:
Entitäten und Entitätstypen
Relationen und Relationstypen
ER Diagramme
Klassifikation binärer Relationstypen:
1 : 1 - Relationen
1 : N - Relationen
N : M - Relationen
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Datenbanken aus ER Modellen
Outline
1
SQL
2
Das ER Modell
Zusammenfassung
3
Das Relationale Modell
Datenbanken aus ER Modellen
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Zur Erinnerung
Bisher haben wir über folgende Dinge gesprochen:
Entitäts- und Relationstypen
1 : 1, 1 : N und N : M Relationen
Schlüssel
Was hat das mit Datenbanken zu tun?
→ versuchen aus dem ER Modell eine Datenbank zu extrahieren
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Die Grundidee des relationalen Modells
Im ER Modell haben wir Entitäten, Relationen, und Attribute.
Die Grundidee des relationen Modells ist es,
die Entitätstypen mit ihren Attributen zu identifizieren,
die Entitäten mit ihren Attributwerten zu identifizieren.
→ Im relationalen Model gibt es letztendlich nur Attribute und
Relationen.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Wertebereiche (Domänen)
Eine Domäne besteht aus möglichen Werten für ein vorgegebenes
Attribut.
Typische Domänen sind z.B.
Zahlen (natürliche, rationale, reelle, etc.)
Zeichenketten
Datumswerte
Wir verlangen typischerweise, dass eine Domäne nur atomare Werte
beinhaltet, also insbesondere
keine zusammengesetzten Werte,
keine strukturierten Werte (z.B. Mengen).
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Relationen auf Domänen
Definition (Relation)
Eine Relation (Tabelle) R zwischen Domänen D1 , D2 , . . . , Dn ist eine
Teilmenge des Cartesischen Produkts der Domänen:
R ⊆ D1 × D2 × . . . × Dn .
NB: Das entspricht unserer Definition von Relationen zwischen
Entitätstypen.
Ronald Ortner
SQL
Das ER Modell
Datenbanken aus ER Modellen
Zur Erinnerung:
Math. Repräsentierung von Relationen
Beispiel:
Relation ’lieben’:
Romeo liebt Julia.
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Zur Erinnerung:
Math. Repräsentierung von Relationen
Beispiel:
Relation ’lieben’:
Romeo liebt Julia.
Wir sagen:
Das Tupel (Romeo, Julia) erfüllt die Relation ’lieben’.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Zur Erinnerung:
Math. Repräsentierung von Relationen
Beispiel:
Relation ’lieben’:
Romeo liebt Julia.
Wir sagen:
Das Tupel (Romeo, Julia) erfüllt die Relation ’lieben’.
Allgmein:
Wir können eine Relation R mit der Menge aller Tupel von Objekten
identifizieren, die die Relation R erfüllen.
(x, y ) | x liebt y
= (Romeo, Julia), (Romeo, Julia), . . .
’lieben’ =
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Mathematische Repräsentation von Relationen
’lieben’ = (x, y ) | x liebt y
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Mathematische Repräsentation von Relationen
’lieben’ = (x, y ) | x liebt y ⊆ P × P,
wobei × das Cartesische Produkt bezeichnet und P die Menge aller
Personen ist.
Definition (Relation)
Eine Relation R zwischen Entitätstypen E1 , E2 , . . . , En
ist eine Teilmenge von E1 × E2 × . . . × En .
Beispiel:
’besitzen’ ist eine Teilmenge von P × D, wobei D die Menge aller
Dinge ist.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Relationen auf Domänen
Definition (Relation)
Eine Relation (Tabelle) R zwischen Domänen D1 , D2 , . . . , Dn ist eine
Teilmenge des Cartesischen Produkts der Domänen:
R ⊆ D1 × D2 × . . . × Dn .
NB: Das entspricht unserer Definition von Relationen zwischen
Entitätstypen.
Beispiel:
Telefonbuch:
Attribute: Name, Adresse, Telefonnummer
→ Telefonbuch ⊆ String × String × Integer
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Relationen auf Domänen
Definition (Relation)
Eine Relation (Tabelle) R zwischen Domänen D1 , D2 , . . . , Dn ist eine
Teilmenge des Cartesischen Produkts der Domänen:
R ⊆ D1 × D2 × . . . × Dn .
NB: Das entspricht unserer Definition von Relationen zwischen
Entitätstypen.
Beispiel:
Tabelle cia:
Attribute: name, region, area, population, gdp
Tabelle cia ⊆ String × String × Integer× Integer× Integer
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Entitätstypen im relationalen
Modell
Entitätstypen werden als Relationen zwischen Domänen (von
Attributen) dargestellt.
Beispiele:
Studenten:
{[Matrnr.:String, Name: String, Semester: integer]}
Lehrveranstaltungen:
{[Lvanr.:Integer, Name: String, Semesterstunden: Integer]}
Länder:
{[Name:String, Region: String, Fläche: Integer, Bevölkerung:
Integer, BIP:Integer]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Zur Erinnerung: Schlüssel
Definition
Schlüssel:
Menge von Attributen, die eine Entität eindeutig bestimmen
Kandidatenschlüssel:
minimaler Schlüssel (ein Schlüssel, aus dem kein Attribut entfernt
werden kann → im allgemeinen nicht eindeutig)
Primärschlüssel: ein beliebig gewählter Kandidatenschlüssel
Beispiele:
Die Matrikelnummer bestimmt einen Studenten eindeutig.
Die Sozialversicherungsnummer bestimmt eine Person eindeutig.
Das Kennzeichen, das Land und der Zeitpunkt bestimmen
eindeutig ein Auto.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell
Relationstypen zwischen Entitätstypen E1 , E2 , . . . , En werden
repräsentiert als Relationen zwischen den Domänen der
Schlüsselattribute der Entitätstypen E1 , E2 , . . . , En und den Domänen
der Attribute des Relationstyps. Das heißt, wenn Entitätstyp Ei die
Schlüsselattribute Ai1 , . . . , Ai,ki (i = 1, . . . n) hat, und die Relation
R ⊆ E1 × E2 × . . . × En
R
die Attribute AR
1 , . . . , AkR , dann wird R repräsentiert als
R
D(A11 ) × · · · × D(A1,k1 ) × D(A21 ) × · · · × D(An,kn ) × D(AR
1 ) × · · · × D(AkR ).
Die Primärschlüssel des Entitätstyps werden Fremdschlüssel genannt.
Fremdschlüssel werden zur Identifizierung von Entitäten verwendet.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell
Beispiel: Relation besuchen zwischen Studenten und
Lehrveranstaltungen.
Der Entitätstyp Student hat das Attribut Matrnr als
Primärschlüssel.
Der Entitätstyp Lehrveranstaltung hat das Attribut LvaNr als
Primärschlüssel.
Die Relation besuchen hat als Attribut das Semester, in dem der
Student die Lehrveranstaltung besucht.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell
Beispiel: Relation besuchen zwischen Studenten und
Lehrveranstaltungen.
Der Entitätstyp Student hat das Attribut Matrnr als
Primärschlüssel.
Der Entitätstyp Lehrveranstaltung hat das Attribut LvaNr als
Primärschlüssel.
Die Relation besuchen hat als Attribut das Semester, in dem der
Student die Lehrveranstaltung besucht.
Entsprechend benötigen wir die folgenden Attribute, um die Relation
besuchen darzustellen:
die Matrnr (des Studenten) → String,
die LvaNr (der Lehrveranstaltung) → Integer,
das Semester (in dem die Lehrveranstaltung besucht wird)
→ String.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell
Beispiel:
Entsprechend benötigen wir die folgenden Attribute, um die Relation
besuchen darzustellen:
die Matrnr (des Studenten) → String,
die LvaNr (der Lehrveranstaltung) → Integer,
das Semester (in dem die Lehrveranstaltung besucht wird)
→ String.
besuchen: {[Matrnr.:String, LvaNr.:Integer, Semester:String]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Primärschlüssel
Der Primärschlüssel der die Relation repräsentierenden Tabelle hängt
davon ab, ob die Relation 1 : 1, 1 : N, oder N : M ist (für binäre
Relation).
Definition (Primärschlüssel einer Tabelle)
Der Primärschlüssel ist eine minimale Menge von Attributen, die eine
Zeile der Tabelle eindeutig bestimmen.
NB: In einer Tabelle mit Attributen A1 , . . . , An bilden die Attribute
Ai1 , . . . , Aik einen Schlüssel, wenn es für i = 1, 2, . . . , n partielle
Funktionen
D(Ai1 ) × · · · × D(Aik ) → D(Ai )
gibt.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Primärschlüssel
Definition (Primärschlüssel einer Tabelle)
Der Primärschlüssel ist eine minimale Menge von Attributen, die eine
Zeile der Tabelle eindeutig bestimmen.
NB: In einer Tabelle mit Attributen A1 , . . . , An bilden die Attribute
Ai1 , . . . , Aik einen Schlüssel, wenn es für i = 1, 2, . . . , n partielle
Funktionen
D(Ai1 ) × · · · × D(Aik ) → D(Ai )
gibt.
Das bedeutet: Legt man die Werte für die Attribute Ai1 , . . . , Aik fest, so
sind die Wert der anderen Attribute eindeutig bestimmt.
D.h. für vorgegebene Werte für die Attribute Ai1 , . . . , Aik kann es in der
Tabelle nur eine Zeile geben.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Zusammenfassung von Tabellen
Gibt es mehrere Tabellen mit demselben Primärschlüssel, so sollte
man diese i.a. zusammenfassen:
1 : 1- und 1 : N-Relationen mit demselben Primärschlüssel
können in einer Tabelle zusammengefasst werden.
Beispiel:
Lehrveranstaltungen: {[LvaNr., Titel, Semesterstunden]}
Professoren: {[Personalnummer, Name, Titel, Raum]}
gibt Lehrveranstaltung: {[LvaNr., Personalnummer]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Zusammenfassung von Tabellen
Gibt es mehrere Tabellen mit demselben Primärschlüssel, so sollte
man diese i.a. zusammenfassen:
1 : 1- und 1 : N-Relationen mit demselben Primärschlüssel
können in einer Tabelle zusammengefasst werden.
Beispiel:
Lehrveranstaltungen: {[LvaNr., Titel, Semesterstunden]}
Professoren: {[Personalnummer, Name, Titel, Raum]}
gibt Lehrveranstaltung: {[LvaNr., Personalnummer]}
→ Die Information, wer die Lehrveranstaltung gibt, kann als
Fremdschlüssel in die Tabelle Lehrveranstaltungen gegeben werden
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Zusammenfassung von Tabellen
Manchmal lässt sich das Datenbankschema noch verfeinern, indem
Tabellen mit demselben Primärschlüssel zusammengefasst werden.
1 : 1- und 1 : N-Relationen mit demselben Primärschlüssel
können in einer Tabelle zusammengefasst werden.
Beispiel:
Lehrveranstaltungen: {[LvaNr., Titel, Semesterstunden, Professor]}
Professoren: {[Personalnummer, Name, Titel, Raum]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Zusammenfassung von Tabellen
Manchmal lässt sich das Datenbankschema noch verfeinern, indem
Tabellen mit demselben Primärschlüssel zusammengefasst werden.
1 : 1- und 1 : N-Relationen mit demselben Primärschlüssel
können in einer Tabelle zusammengefasst werden.
Beispiel:
Lehrveranstaltungen: {[LvaNr., Titel, Semesterstunden, Professor]}
Professoren: {[Personalnummer, Name, Titel, Raum]}
NB: Das funktioniert für N : M-Relationen nicht!
Ronald Ortner
SQL
Das ER Modell
Datenbanken aus ER Modellen
Beispiel: Bücherdatenbank
Ronald Ortner
Das Relationale Modell
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Bücherdatenbank
Wir führen IDs für Autoren, Bücher und Schlagwörter ein.
Dann:
Je eine Tabelle für jeden Entitätstyp (mit den IDs als
Primärschlüssel):
Autoren: {[a_id, Name]}
Bücher: {[isbn, Titel, Verlag]}
Schlagwörter: {[s_id, Schlagwort]}
Je eine Tabelle für jede Relation:
schreibt: {[a_id, isbn]}
hat_Schlagwort: {[isbn, s_id, Gewicht]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Repräsentation von Relationen im relationalen Modell:
Vermeidung von NULL-Werten
Für Entitäten, die nicht an Relation teilnehmen, müssen oft
NULL-Werte eingefügt werden.
→ Repräsentation mit wenigen NULL-Werten ist vorteilhaft.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Je eine Tabelle für jeden Entitätstyp:
Studenten: {[Mnr, Name]}
Studienrichtungen: {[Stkz, Name]}
Je eine Tabelle für jede Relation:
Studienrichtungen: {[Mnr, Stkz]}
ist Strv: {[Mnr, Stkz]}
ist Strv-Vorsitzender: {[Mnr, Stkz]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Je eine Tabelle für jeden Entitätstyp:
Studenten: {[Mnr, Name]}
Studienrichtungen: {[Stkz, Name]}
Je eine Tabelle für jede Relation:
Studienrichtungen: {[Mnr, Stkz]}
ist Strv: {[Mnr, Stkz]}
ist Strv-Vorsitzender: {[Mnr, Stkz]}
Fassen Tabellen mit gleichen Primärschlüsseln zusammen.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Zusammenfassen von Tabellen mit gleichem Primärschlüssel ergibt:
Tables:
Studenten: {[Mnr, Name, Stkz(Strv), Stkz(StrvV) ]}
Studienrichtungen: {[Stkz, Name]}
studiert: {[Mnr, Stkz]}
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Zusammenfassen von Tabellen mit gleichem Primärschlüssel ergibt:
Tables:
Studenten: {[Mnr, Name, Stkz(Strv), Stkz(StrvV) ]}
Studienrichtungen: {[Stkz, Name]}
studiert: {[Mnr, Stkz]}
Nicht schön:
Viele NULL-Einträge in Tabelle Studenten: Die meisten Studenten sind
nicht in Strv.
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Tabellen:
Studenten: {[Mnr, Name, Stkz(Strv), Stkz(StrvV) ]}
Studienrichtungen: {[Stkz, Name]}
studiert: {[Mnr, Stkz]}
Besser:
Ronald Ortner
SQL
Das ER Modell
Das Relationale Modell
Datenbanken aus ER Modellen
Beispiel: Studenten und Studienrichtungen
Tabellen:
Studenten: {[Mnr, Name, Stkz(Strv), Stkz(StrvV) ]}
Studienrichtungen: {[Stkz, Name]}
studiert: {[Mnr, Stkz]}
Besser:
Tabellen:
Studenten: {[Mnr, Name]}
Studienrichtungen: {[Stkz, Name, StrvV]}
Strv: {[Mnr, Stkz]}
studiert: {[Mnr, Stkz]}
Ronald Ortner