Kapitel 3: Datenbanksysteme

LUDWIGMAXIMILIANSUNIVERSITY
MUNICH
DEPARTMENT
INSTITUTE FOR
INFORMATICS
DATABASE
SYSTEMS
GROUP
Skript zur Vorlesung:
Einführung in die Informatik: Systeme und Anwendungen
Sommersemester 2009
Kapitel 3:
Datenbanksysteme
Vorlesung: PD Dr. Peer Kröger
Übungen: Thomas Bernecker
Skript © 2004 Christian Böhm, Peer Kröger
http://www.dbs.ifi.lmu.de/Lehre/InfoNF
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
Überblick
3.1 Einleitung
3.2 Das Relationale Modell
3.3 Die Relationale Algebra
3.4 Mehr zu SQL
3.5 Das E/R-Modell
[3.6 Normalformen]
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
2
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Charakteristika
– Die Tabelle (Relation) ist das ausschließliche Strukturierungsmittel
des relationalen Datenmodells
– Edgar F. Codd, 1970
– Konzept: vgl. Excel-Tabelle
– Grundlage vieler kommerzieller DBS:
Informix
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
3
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Domain
– Ein Wertebereich (oder Typ)
– Logisch zusammengehörige Menge von Werten
– Beispiele:
•
•
•
•
•
D1 = Integer
(ganze Zahlen {…, -2, -1, 0, 1, 2, …})
(Zeichenketten, z.B. „Hallo“)
D2 = String
D3 = Date
(Datum, typischerweise: TTMMYYYY)
D4 = {rot, gelb, grün, blau}
D5 = {1, 2, 3}
– Kann endliche oder unendliche Kardinalität haben
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
4
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Kartesisches Produkt
– Bedeutung des kartesisches Produkts (Kreuzprodukts) von k
Domains:
Menge von allen möglichen Kombinationen der Elemente der
Mengen
– Beispiel (k = 2):
D1 = {1, 2, 3}, D2 = {a,b}
D1 × D2 = {(1,a), (1,b), (2,a), (2,b), (3,a), (3,b)}
– Beispiel (k = 3):
D1 = D2 = D3 = N
D1×D2×D3 = {(1,1,1),(1,1,2),(1,1,3),...,(1,2,1),...}
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
5
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Relation
– Mathematische Definition:
Relation R ist Teilmenge des kartesischen Produktes von k Domains
D1, D2, ... Dk
R ⊆ D1 × D2 × ... × Dk
– Beispiel (k = 2):
D1 = {1, 2, 3}, D2 = {a,b}
(leere Menge, leere Relation)
R1 = {}
R2 = {(1,a), (2,b)}
R3 = {(1,a), (2,a), (3,a)}
R4 = D1 × D2 = {(1,a),(1,b),(2,a),(2,b),(3,a),(3,b)}
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
6
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
– Weiteres Beispiel:
D1 = D2 = N
Relation R1 = {(1,1),(1,2),(1,3),...,(2,2),(2,3),....,
(3,3),(3,4),...(4,4),(4,5),(4,6),....}
Wie heißt diese mathematische Relation?
≤ : R≤ = {(x, y) ∈ N × N | x ≤ y}
– Es gibt endliche und unendliche Relationen
(wenn mindestens eine Domain unendlich ist)
– In Datenbanksystemen: Nur endliche Relationen
Unendlich: Nicht darstellbar
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
7
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Sprechweisen
– Die einzelnen Domains lassen sich als Spalten einer Tabelle
verstehen und werden als Attribute bezeichnet
– Für R ⊆ D1 × ... × Dk ist k der Grad (Stelligkeit)
– Die Elemente der Relation heißen Tupel:
(1,a), (2,a), (3,a) sind drei Tupel vom Grad k = 2
– Relation ist Menge von Tupeln
d.h. die Reihenfolge der Tupel spielt keine Rolle
{(0,a), (1,b)} = {(1,b), (0,a)}
– Reihenfolge der Attribute ist von Bedeutung:
{(a,0), (b,1)} ≠ {(0,a), (1,b)}
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
8
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Relationen und Datenbanken
– Relationen sind die mathematische Formalisierung der Tabelle
– Jedes Tupel steht für einen Datensatz
– Die einzelnen Informationen sind in den Attributwerten der Tupel
gespeichert
– Die Attribute können auch benannt sein:
D1 = Name: String
D2 = Vorname: String
– Zeichenketten und Zahlen sind die häufigsten Domains
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
9
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Alternative Definition:
Relation ist Ausprägung eines Relationen-Schemas
• Geordnetes Relationenschema:
– k-Tupel aus Domains (Attribute)
– Attribute können benannt sein
– Attribute werden anhand ihrer Position im Tupel referenziert
R = (A1: D1, ... Ak: Dk)
• Domänen-Abbildung (ungeordnetes Relationenschema):
– Relationenschema R ist Menge von Attributnamen:
– Jedem Attributnamen Ai ist Domäne Di zugeordnet:
– Attribute werden anhand ihres Namens referenziert
R = {A1, .... Ak} mit dom(Ai) = Di, 1 ≤ i ≤ k
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
10
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Beispiel: Städte-Relation
Städte
Name
München
Bremen
Passau
Einwohner
1.211.617
535.058
49.800
Land
Bayern
Bremen
Bayern
• Als geordnetes Relationenschema:
Schema:
Städte = (Name: String, Einwohner: Integer, Land: String)
Ausprägung: {(München, 1.211.617, Bayern), (Bremen, 535.058,
Bremen), (Passau, 49.800, Bayern)}
• Als Relationenschema mit Domänenabbildung:
Schema:
Städte = {Name, Einwohner, Land}
mit dom(Name) = String, dom(Einwohner) = Integer, ...
Ausprägung: {t1, t2, t3}
mit t1(Name) = München, t1(Einwohner) = 1.211.617,...
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
11
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Vorteil von geordnetem Relationenschema:
– Prägnanter aufzuschreiben.
Wichtig z.B. beim Einfügen neuer Tupel:
t3 = (Passau, 49.800, Bayern)
vergleiche: t3 (Name) = Passau; t3 (Einwohner) = ...
• Nachteil von geordnetem Relationenschema:
– Einschränkungen bei logischer Datenunabhängigkeit:
Applikationen sensibel bzgl. Einfügung neuer Attribute (nur am
Ende da sonst die Position der vorhandenen Attribute verändert
wird)
• Definitionen gleichwertig
(lassen sich ineinander überführen)
• Wir verwenden beide Ansätze
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
12
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Begriffe
– Relation: Ausprägung eines Relationenschemas
– Datenbankschema: Menge von Relationenschemata
– Datenbank: Menge von Relationen (Ausprägungen)
Relationenname
Städte
Tupel
Name
München
Bremen
Passau
Attribut(-Namen)
Einwohner
1.211.617
535.058
49.800
Land
Bayern
Bremen
Bayern
Relationenschema
Relation
Attributwerte
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
13
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Duplikate
– Relationen sind Mengen von Tupeln.
Konsequenzen:
• Reihenfolge der Tupel irrelevant (wie bei math. Def)
• Es gibt keine Duplikate (gleiche Tupel) in Relationen:
{(0,a), (0,a), (0,a), (1,b)} = {(0,a), (1,b)}
– Frage: Gilt dies auch für die Spalten beim ungeordneten
Relationenschema R = {A1,...,Ak} ?
• Die Reihenfolge der Spalten ist irrelevant
(das ist gerade das Besondere am ungeordneten RS)
• Duplikate treten nicht auf, weil alle Attribut-Namen verschieden sind
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
14
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Schlüssel
– Tupel müssen eindeutig identifiziert werden
– Warum? Z.B. für Verweise:
Mitarbeiter
PNr
001
002
003
Name
Huber
Mayer
Müller
Abteilungen
Vorname Abteilung
Erwin
Hugo
Anton
ANr
01
02
03
Abteilungsname
Buchhaltung
Produktion
Marketing
– Objektidentifikation in Java:
Mit Referenz (Adresse im Speicher)
– Im relationalen Modell werden Tupel anhand von Attributwerten
identifiziert
– Ein/mehrere Attribute als Schlüssel kennzeichnen
– Konvention: Schlüsselattribut(e) unterstreichen!
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
15
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Beispiel: PNr und ANr werden Primärschlüssel:
Mitarbeiter
PNr Name
001 Huber
002 Mayer
003 Müller
Vorname Abteilung
Erwin
Hugo
Anton
Abteilungen
ANr Abteilungsname
01 Buchhaltung
02 Produktion
03 Marketing
– Damit müssen diese Attributswerte eindeutig sein!
– Verweis durch Wert dieses Schlüsselattributs:
Mitarbeiter
PNr Name
001 Huber
002 Mayer
003 Müller
Vorname
Erwin
Hugo
Anton
Abteilung
01
01
02
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
Abteilungen
ANr Abteilungsname
01 Buchhaltung
02 Produktion
03 Marketing
16
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Zusammengesetzter Schlüssel
– Oft ist ein einzelnes Attribut nicht ausreichend,
um die Tupel eindeutig zu identifizieren
• Beispiel:
Lehrveranstaltung
VNr
012
012
013
...
Titel
Einführung in die Informatik
Einführung in die Informatik
Medizinische Informationssyst.
...
Semester
WS 2001/02
WS 2002/03
WS 2001/02
...
– Schlüssel: (VNr, Semester)
– Anmerkung: Warum ist dies in diesem Fall ein schlechtes DBDesign?
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
17
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Schlüssel:
Eine Teilmenge S der Attribute eines Relationenschemas R
heißt Schlüssel, wenn gilt:
– Eindeutigkeit
Keine Ausprägung von R kann zwei verschiedene Tupel enthalten,
die sich in allen Attributen von S gleichen.
– Minimalität
Keine echte Teilmenge von S erfüllt bereits die Bedingung der
Eindeutigkeit
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
18
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
In noch formalerer Notation:
Schlüssel:
– Sei r eine Relation über dem Relationenschema R
und S ⊆ R eine Auswahl von Attributen.
– t[S] bezeichne ein Tupel t eingeschränkt auf die Attribute aus S
(alle anderen Attribute gestrichen)
– (1) Eindeutigkeit:
∀ möglichen Ausprägungen r und Tupel t1, t2 ∈ r gilt:
t1 ≠ t2 ⇒ t1[S] ≠ t2[S]
– (2) Minimalität:
Für alle Attributmengen S und T, die (1) erfüllen, gilt:
T⊆S⇒T=S
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
19
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Beispiele
– Gegeben sei die folgende Relation:
Lehrveranst
t1
t2
t3
LNr
1
2
3
...
VNr
012
012
013
...
Titel
Einführung in die Informatik
Einführung in die Informatik
Medizinische Informationssyst.
...
– {VNr}
ist kein Schlüssel
– {Titel}
ist kein Schlüssel
– {Semester}
ist kein Schlüssel
Semester
WS 2001/02
WS 2002/03
WS 2001/02
...
Nicht eindeutig: t1 ≠ t2 aber t1[VNr] = t2[VNr] = 012
(gleiche Begründung)
Nicht eindeutig: t1 ≠ t3 aber t1[Semester] = t3[Semester]
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
20
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Lehrveranst
t1
t2
t3
LNr
1
2
3
...
VNr
012
012
013
...
– {LNr}
Titel
Einführung in die Informatik
Einführung in die Informatik
Medizinische Informationssyst.
...
Semester
WS 2001/02
WS 2002/03
WS 2001/02
...
ist Schlüssel !!!
Eindeutigkeit: Alle ti[LNr] sind paarweise verschieden,
d.h. t1[LNr]≠t2[LNr], t1[LNr]≠t3[LNr], t2[LNr]≠t3[LNr]
Minimalität: Trivial, weil 1 Attribut kürzeste Möglichkeit
– {LNr, VNr}
ist kein Schlüssel
Eindeutigkeit: Alle ti[LNr,VNr] paarweise verschieden.
Nicht minimal, da echte Teilmenge {LNr} ⊂ {LNr, VNr} (≠) die Eindeutigkeit
bereits gewährleistet, s.o.
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
21
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Lehrveranst
t1
t2
t3
LNr
1
2
3
...
VNr
012
012
013
...
– {VNr, Semester}
Titel
Einführung in die Informatik
Einführung in die Informatik
Medizinische Informationssyst.
...
Semester
WS 2001/02
WS 2002/03
WS 2001/02
...
ist Schlüssel !!!
Eindeutigkeit: Alle ti[VNr, Semester] paarw. verschieden:
• t1 [VNr, Semester] = (012, WS 2001/02)
• t2 [VNr, Semester] = (012, WS 2002/03)
• t3 [VNr, Semester] = (013, WS 2001/02)
≠
≠
≠
Minimalität:
Weder {VNr} noch {Semester} gewährleisten Eindeutigkeit
(siehe vorher). Dies sind alle echten Teilmengen.
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
22
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Primärschlüssel
– Minimalität bedeutet nicht :
Schlüssel mit den wenigsten Attributen
– Sondern Minimalität bedeutet:
Keine überflüssigen Attribute sind enthalten
(d.h. solche, die zur Eindeutigkeit nichts beitragen)
– Manchmal gibt es mehrere verschiedene Schlüssel
• {LNr}
• {VNr, Semester}
– Diese nennt man auch Schlüsselkandidaten
– Man wählt einen dieser Kandidaten aus als sog. Primärschlüssel
(oft den kürzesten, nicht immer)
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
23
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Die Eindeutigkeit bezieht sich nicht auf die aktuelle
Ausprägung einer Relation r
• Sondern immer auf die Semantik der realen Welt
Mitarbeiter
PNr
Name
Gehalt
001
002
003
004
Müller
Mayer
Huber
Schulz
1700 €
2172 €
3189 €
2171 €
– Bei der aktuellen Relation wären sowohl {PNr} als auch {Name} und
{Gehalt} eindeutig.
– Aber es ist möglich, dass mehrere Mitarbeiter mit gleichem Namen
und/oder Gehalt eingestellt werden
– {PNr} ist für jede mögliche Ausprägung eindeutig
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
24
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Definition eines Relationenschemas in SQL
CREATE TABLE n
(
a1 d1 c1,
a2 d2 c2,
...
ak dk ck
)
← n Name der Relation
← Definition des ersten Attributs
← Definition des Attributs Nr. k
• hierbei bedeuten...
– ai der Name des Attributs Nr. i
– di der Typ (die Domain) des Attributs
– ci ein optionaler Constraint für das Attribut
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
25
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Was passiert dabei?
– Es wird eine leere Tabelle angelegt
– Beispiel:
CREATE TABLE Mitarbeiter
(
PNr NUMBER (3),
Name CHAR (20),
Vorname CHAR (20),
ANr NUMBER (2)
)
PNr Name
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
Vorname ANr
(leer)
26
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Der SQL-Standard kennt u.a. folgende Basis-Datentypen
(Domains):
–
–
–
–
integer oder auch interger4, int
smallint oder integer2
float (p) oder auch float
decimal (p,q) und numeric (p,q)
mit p Stellen, davon q Nachkommast.
– character (n), char (n) für Strings fester Länge n
– character varying (n), varchar (n): variable Strings
– date, time, timestamp für Datum und Zeit
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
27
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Einfache Zusätze (Integritätsbedingungen) können
unmittelbar hinter einer Attributdefinition stehen:
– not null: Das Attribut darf nicht undefiniert sein
in DBS: undefinierte Werte heissen null-Werte
– primary key: Das Attribut ist Primärschlüssel
(nur bei 1-Attribut-Schlüsseln)
– check f:
Die Formel f wird bei jeder Einfügung überprüft
– references t1 (a1):
Ein Verweis auf Attribut a1 von Tabelle t1
Verschiedene Zusatzoptionen:
• on delete cascade
• on delete set null
– default w1: Wert w1 ist Default, wenn unbesetzt.
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
28
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Integritätsbedingungen
Zusätze, die keinem einzelnen Attribut zugeordnet sind,
stehen mit Komma abgetrennt in extra Zeilen
– primary key (s1, s2, ...):
Zusammengesetzter Primärschlüssel
– foreign key (s1, s2, ...) references t1 (...)
Verweis auf zusammengesetzten Schlüssel in t1
– check f
• Anmerkung:
SQL ist case-insensitiv:
– im Ggs. zu Java hat die Groß-/Kleinschreibung weder bei
Schlüsselworten noch bei Bezeichnern Bedeutung
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
29
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
Beispiele für Tabellendefinitionen
• Zusammengesetzter Primärschlüssel {VNr, Semester}:
create table Lehrveranst
(
LNr
integer
not null,
VNr
integer
not null,
Titel
varchar(50),
Semester varchar(20) not null,
primary key (VNr, Semester)
)
• Alternative mit einfachem Primärschlüssel {LNr}:
create table Lehrveranst2
(
LNr
integer
VNr
integer
Titel
varchar(50),
Semester varchar(20)
)
primary key,
not null,
not null
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
30
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
• Tabelle für Dozenten:
create table Dozenten
(
DNr
integer
primary key,
Name
varchar(50),
Geburt
date,
)
• Verwendung von Fremdschlüsseln:
create table Haelt
(
Dozent integer
)
references Dozenten (DNr)
on delete cascade,
VNr
integer
not null,
Semester varchar(20) not null,
primary key (Dozent, VNr, Semester),
foreign key (VNr, Semester) references Lehrveranst
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
31
Kapitel 3: Datenbanksysteme
DATABASE
SYSTEMS
GROUP
3.2 Das Relationale Modell
– Das Schlüsselwort on delete cascade in Haelt führt dazu, dass bei
Löschen eines Dozenten auch entsprechende Tupel in Haelt
gelöscht werden
– Weitere Konstrukte der Data Definition Language:
• drop table n1
Relationen-Schema n1 wird mit allen evtl. vorhandenen Tupeln
gelöscht.
• alter table n1 add (a1 d1 c1, a2 d2 c2, ...)
– Zusätzliche Attribute oder Integritätsbedingungen werden (rechts) an die
Tabelle angehängt
– Bei allen vorhandenen Tupeln Null-Werte
• alter table n1 drop (a1 , a2 , ...)
• alter table n1 modify (a1 d1 c1, a2 d2 c2, ...)
Einführung in die Informatik: Systeme und Anwendungen – SoSe 2009
32