Skript Folge 6

Rückblick
SELECT mit mehreren Relationen
 Inner Join
 CROSS JOIN
 NATURAL JOIN
 CONDITION JOIN
 COLUMN NAME JOIN
 Self Join
 Outer Join
 LEFT OUTER JOIN
 RIGHT OUTER JOIN
 FULL OUTER JOIN
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
1
Rückblick
 ORDER BY (asc / desc)
 NULL-Werte und 3wertige Logik






IS NULL
IS NOT NULL
Wahrheitswerte für Terme
Wahrheitswerte für Vergleichsausdrücke
Wahrheitswerte für logische Ausdrücke
Wahrheitstabellen (Wahrheitstafeln)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
2
Ausblick





Funktionale Abhängigkeiten
Normalformen
Zerlegungen
Sichten / Views
Übungen Bibliothek
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
3
Normalformen
Funktionale
Abhängigkeiten
Zerlegungen
Normalformen
Ziel:
Gute von schlechten DatenbankSchemata unterscheiden können!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
5
Normalformen
Gesucht:
ein zuverlässiges Verfahren zur
Erkennung eines schlechten
Schemadesigns
ein zuverlässiges Verfahren zur
Erzeugung eines guten
Schemadesigns
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
6
Normalformen
Gute Datenbankschemata
 ermöglichen die Herleitung aller benötigten
Daten aus den Basisrelationen
 schränken die Möglichkeit inkonsistente
Daten darzustellen weitgehend ein
 stellen die Daten möglichst redundanzfrei
dar
 ermöglichen gute Performance bei Zugriffen
 verhindern Mutationsanomalien
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
7
Normalformen
Durch die Zerlegung von Tabellen wird
für manche Queries ein zusätzlicher
Join notwendig.
Dies beeinträchtigt die Performance!!!
Folglich müssen triftige Gründe für
eine Zerlegung vorliegen, um die
potentiellen Performanceverluste
aufzuwiegen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
8
Normalformen
Gute Datenbankschemata
Unvermeidbarer Zielkonflikt zwischen
der Forderung nach guter Performance
und Redundanzfreiheit!!!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
9
Mutationsanomalien
Mutationsanomalien
 Einfügeanomalien (Insert-Anomalie)
 Änderungsanomalien (Update-Anomalie)
 Löschanomalien (Delete-Anomalie)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
11
Mutationsanomalien
(Beispiel 1)
Abteilungsmitarbeiter
M#
Name
M19
M1
M7
M4
Schweizer
Meier
Huber
Becker
Strasse
Ort
A
Bezeichnung
Hauptstrasse
Lindenstrasse
Mattenweg
Wasserweg
Zürich
Liestal
Basel
Liestal
A6
A3
A5
A6
Finanz
Informatik
Personal
Finanz
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
12
Mutationsanomalien
Einfügeanomalie
Falls man eine neue Abteilung z.B. A9 mit dem
Namen „Marketing“ erfassen will, ist dies nur
möglich, wenn man auch einen Mitarbeiter
dafür erfasst, auch wenn es noch keinen gibt.
Ein NULL-Wert für Mitarbeiter ist nicht möglich,
da M# Teil des Schlüssels ist.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
13
Mutationsanomalien
Änderungsanomalie
Soll z.B. die Bezeichnung der Abteilung A6 von
„Finanz“ in „Finance“ geändert werden, muss
die Änderung bei sämtlichen Mitarbeitertupeln
der Abteilung vollzogen werden, obwohl sich
nur ein einziger Sachverhalt ändert.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
14
Mutationsanomalien
Update in SQL
UPDATE Abteilungsmitarbeiter
SET Bezeichnung = 'Finance'
WHERE Bezeichnung ='Finanz';
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
15
Mutationsanomalien
Löschanomalie
eine Löschanomalie liegt vor, wenn eine
Information ungewollt verloren geht.
Wenn man z.B: in der Tabelle
„Abteilungsmitarbeiter“ Mitarbeiter M1 löscht,
geht die Abteilung „Informatik mit der
Abteilungsnummer „A3“ verloren.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
16
Mutationsanomalien
DELETE
DELETE FROM Abteilungsmitarbeiter
WHERE M# = M1;
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
17
Mutationsanomalien
Um Probleme der Redundanz und die dabei
auftretenden Änderungsanomalien in den Griff
zu bekommen, bietet sich folgende Lösung
an:
– Es wird eine Überführung des
Ausgangsschemas in spezielle andere
Schemata vorgenommen, die die
genannten Probleme nach Möglichkeit
vermeiden. Dies entspricht einer Zerlegung
in Normalformen. (Normalisierung)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
18
Mutationsanomalien
Beispiel 2
Angenommen, wir hätten
LIEFERANT(LName, LAdresse)
liefert(LName, ArtName, Preis)
zusammengefaßt in
L_INFO(LName, LAdresse, ArtName, Preis)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
19
Mutationsanomalien
Beispiel 2
Die Lieferantenadresse „LAdresse“ muß
dann für jede Lieferung wiederholt
werden. Dies führt zu potentiellen
Inkonsistenzen infolge von Redundanz.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
20
Mutationsanomalien
Beispiel 2
• Update-Anomalie
Beim Ändern der Adresse LAdresse muß dies in jedem Tupel
geschehen, in dem der zugehörige Lieferant vorkommt.
• Insert-Anomalie:
Ein Lieferant mit Adresse kann nicht erfaßt werden, wenn er
momentan noch nichts liefert. NULL für ArtName ist nicht erlaubt,
da ArtName Teil des Schlüssels ist
• Delete-Anomalie:
Falls man die Artikel eines Lieferanten löscht, verliert man
ungewollt auch dessen Adresse
Bei einer Zerlegung von „L_INFO“ in „Lieferant“ und „liefert“
verschwinden diese Probleme.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
21
Funktionale Abhängigkeit
Zwischen Attributen einer Tabelle
Funktionale Abhängigkeiten
Definition
Seien R(A1, ..., An) ein Relationenschema und
und X,Y  {A1, ..., An}.
Y heißt funktional abhängig von X
(geschrieben X  Y) genau dann,
wenn gleiche Attributwerte für X auch gleiche
Attributwerte für Y erzwingen.
X  Y heißt dann FD (functional dependency)
für R(A1, ..., An).
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
23
Funktionale Abhängigkeiten
Die Definition ist „identisch“ mit der
mathematischen Definition einer Funktion:
f: X Y
/ f XxY
( x  X,  y, z  Y:
(x,y)  f und (x,z)  f )  y = z
(Zu einem Zeitpunkt / in einem DB-Zustand)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
24
Funktionale Abhängigkeiten
Beispiel (L_INFO)
LName
'Kahn'
'Kahn'
LAdresse
'Seestr. 9'
???
ArtName
'Käse'
'Wurst'
Preis
1.99
1.19
Ist der Attributwert ??? irgendwie herleitbar?
Da LName Primärschlüssel in LIEFERANT ist, wissen wir, daß
„LAdresse“ von LName funktional abhängig ist.
Folglich muß ??? gleich Seestr. 9 sein.
Die Redundanz wurde durch die FD {Lname}  {LAdresse} bewirkt.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
25
Funktionale Abhängigkeiten
Für Relationenschemata, die aus dem E/RDesign entstanden sind, ergeben sich
folgende FDs:
Repräsentiere R(A1, ..., An) den
Entity-Typ R und
sei X Schlüsselkandidat von R.
dann gilt:
X  Y für alle Y  {A1, ..., An }
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
26
Funktionale Abhängigkeiten
Für Relationenschemata, die aus dem E/RDesign entstanden, ergeben sich folgende
FDs:
Repräsentiere R(A1, ..., An) eine N:1Relationship zwischen E1 und E2 und
habe E1 den Schlüsselkandidaten X.
dann gilt:
X  Y für alle Y  {A1, ..., An }
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
27
Funktionale Abhängigkeiten
Beispiele für FDs
 In LIEFERANT gilt:
{LName}  {LAdresse}.
 In liefert gilt:
{LName, ArtName}  {Preis}.
 In KUNDE gilt:
{KName}  {KAdresse, Saldo}.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
28
Funktionale Abhängigkeiten
Beispiele für FDs
triviale FD:
{LName}  {LName}
aber auch:
{LName, ArtName}  {LAdresse, Preis}
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
29
Funktionale Abhängigkeiten
Bemerkung:
 FDs sind Zusicherungen über die
Semantik der ,,realen Welt``, d.h. sie
sind nicht beweisbar. (intensional)
 Manche FDs werden automatisch vom
DBS überwacht, wie z.B. der
Primärschlüssel
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
30
Funktionale Abhängigkeiten
Definition Erfüllung funktionaler
Abhängigkeiten (extensional):
Seien r eine Instanz von R(A1, ..., An),
X,X  {A1, ..., An}.
Die Instanz r erfüllt X  Y , wenn für alle
Tupel    r gilt:
[X] =[X] impliziert [Y] = [Y].
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
31
Funktionale Abhängigkeiten
Bemerkung:
 Falls X  Y für R gilt, so muß X  Y
in jedem r erfüllt sein.
 Falls X  Y nicht gilt, kann X  Y
durch ein r erfüllt sein oder auch nicht.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
32
Funktionale Abhängigkeiten
Beispiel (Postleitzahlen):
Wir betrachten die deutschen Postleitzahlen.
In den meisten Fällen ist die Postleitzahl durch Stadt
und Straße eindeutig bestimmt, z.B.
{München, Dachsteinstr.}  81825
Von Ausnahmefällen, wie z.B.
{München, Dachauerstr. 1-147} 80335
{München, Dachauerstr. 149-181} 80636
wollen wir im folgenden abstrahieren.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
33
Funktionale Abhängigkeiten
Sei
PLZBUCH(Stadt,Straße,PLZ)
gegeben mit den nicht-trivialen FDs
F= { {Stadt,Straße}  {PLZ}, {PLZ}  {Stadt} }.
wir können von folgenden Mengen von
Schlüsselkandidaten ausgehen:
schlüsselkand(PLZBUCH) = {{Stadt,Straße},{PLZ}}
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
34
Funktionale Abhängigkeiten
Für die funktionale Abhängigkeit gelten für
Attributmengen A, B einer Relation R folgende
Regeln:
A  B   bB A  {b}
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
35
Funktionale Abhängigkeiten
Für die funktionale Abhängigkeit gelten für
Attributmengen A, B, C einer Relation R
folgende Regeln:
A  B  B  A (triviale FD)
ABACBC
A  B und B  C  A  C
(Armstrongsche Regeln)
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
36
Funktionale Abhängigkeiten
Wenn S ein Schlüssel von R ist,
dann gilt für alle B:
SB
Wenn S  S' dann gilt: S'  B
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
37
Funktionale Abhängigkeiten
Volle Funktionale Abhängigkeit
Eine Menge von Attributen B ist von
einer Attributmenge A voll funktional
abhängig, wenn gilt:
1) A  B
2) es gibt kein A' mit A'  A, A'  A,
mit: A'  B
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
38
Funktionale Abhängigkeiten
Mit Hilfe der vollen Funktionalen
Abhängigkeit lässt sich der Begriff des
Schlüsselkandidaten präzise
definieren:
Eine Menge von Attributen A einer
Relation R ist Schlüsselkandidat der
Relation R, wenn für jede Menge B
von Attributen von R gilt:
B ist voll funktional abhängig von A.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
39
Normalformen
Normalformen (1NF)
erste Normalform
Eine Relation ist in erster Normalform
(1NF), wenn alle ihre Attribute nur
atomare (elementare) Attributwerte
besitzen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
41
Normalformen (2NF)
Zweite Normalform (2NF):
Eine Tabelle ist in zweiter Normalform,
wenn jedes nicht dem Schlüssel
angehörende Attribut funktional
abhängig ist vom Gesamtschlüssel nicht
aber von den Schlüsselteilen
d.h: es gibt kein Attribut ausserhalb des
Schlüssels, das schon von einem Teil
des Schlüssels funktional abhängig ist.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
42
Normalformen
Zweite Normalform (2NF):
Eine Tabelle ist in zweiter Normalform,
wenn sie in erster Normalform ist und
wenn jedes Nichtschlüsselattribut von
jedem Schlüsselkandidaten voll
funktional abhängig ist.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
43
Normalformen (2NF)
Ein Attribut ist voll funktional
abhängig von einem
zusammengesetzten
Schlüsselkandidaten, falls es nicht
funktional abhängig ist von einem
Teil des Schlüsselkandidaten.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
44
Normalformen (Beispiel)
M#
M7
M1
Name
Huber
Meier
Ort
Basel
Liestal
P
{P1,P9}
{P7,P11,P9}
Projektmitarbeiter (unnormalisiert)
M#
M7
M7
M1
M1
M1
Name
Huber
Huber
Meier
Meier
Meier
Ort
Basel
Basel
Liestal
Liestal
Liestal
P#
P1
P9
P7
P11
P9
1NF
Projektmitarbeiter (in erster Normalform)
45
Normalformen (1 NF) (2 NF)
M#
M7
M7
M1
M1
M1
Name
Huber
Huber
Meier
Meier
Meier
Ort
Basel
Basel
Liestal
Liestal
Liestal
P#
P1
P9
P7
P11
P9
Projektmitarbeiter (in erster Normalform)
M#
M7
M1
Name
Huber
Meier
Mitarbeiter (2NF)
Ort
Basel
Liestal
M#
P#
M7
M7
M1
M1
M1
P1
P9
P7
P11
P9
Zugehörigkeit (2NF)
46
Normalformen (2NF)
Zweite Normalform (2NF):
die 2 NF kann nur verletzt werden
• wenn eine Relation einen
zusammengesetzten Schlüssel hat
• und die Relation mindestens ein Attribut
besitzt, das nicht zum Schlüssel gehört
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
47
Normalformen (2NF)
Zerlegung
durch Zerlegung der Ausgangsrelation in
eine Relation mit voll funktional
abhängigen Attributen und eine weitere
Relation mit teilabhängigen Attributen
können Relationen in der 2. NF
gewonnen werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
48
Normalformen (2NF)
Alternative Formulierung
A ist ein Primattribut, wenn A Element
eines Schlüsselkandidaten ist.
R ist in 2 NF, wenn es keine nicht-trivialen
funktionalen Abhängigkeiten T  A gibt,
wobei T eine echte Untermenge eines
Schlüsselkandidaten ist und A kein
Primattribut enthält.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
49
Normalformen (3 NF)
Dritte Normalform (3NF):
Eine Tabelle ist in dritter Normalform,
wenn sie in zweiter Normalform ist
und keine funktionalen Abhängigkeiten
zwischen Attributen erlaubt, die keine
Schlüsselattribute sind.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
50
Normalformen (3 NF)
Dritte Normalform (3NF):
Eine Tabelle ist in dritter Normalform, falls sie
in 2NF ist und kein Nichtschlüsselattribut von
irgendeinem Schlüsselkandidaten transitiv
abhängig ist.
Transitive Abhängigkeit
heisst über Umwege funktional abhängig sein:
A  B und B  C  A  C
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
51
Normalformen (3 NF)
Dritte Normalform (3NF):
Die dritte Normalform kann nur verletzt
werden, wenn eine Relation neben dem
Schlüssel (einfach oder
zusammengesetzt) mindestens zwei
zusätzliche Attribute hat, die keine
Attribute eines Schlüsselkandidaten
sind.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
52
Normalformen (3NF)
Beispiel:
(S1#, S2#, A, B)
Voraussetzung:
S1#, S2# sind die beiden Schlüsselattribute
Falls S2#  A ist die Relation nicht 2NF
Falls A  B ist die Relation nicht in 3NF
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
53
Normalformen (3NF)
Beispiel:
R ( S1#,
S2#,
A,
B)
3NF
2NF
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
54
Normalformen (3NF)
Beispiel 2:
mitarbeiter(emp_nr, name, ort, abt_nr, abt_name)
Verletzt 3NF, wegen funktionaler Abhängigkeit zwischen
abt_nr und abt_name
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
55
Normalformen (4NF / 5NF)
Die Normalformen 4 und 5 sind in der
Praxis von geringer Bedeutung und
werden deshalb hier nicht behandelt.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
56
Zerlegungen
Zerlegung
Durch eine geeignete Zerlegung eines
schlechten Schemas kann man die
bekannten Design-Anomalien
beseitigen.
Im folgenden identifizieren wir dazu ein
Schema R(A1, ... An) mit der Menge
seiner Attribute R={A1, A2, ..., An}.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
58
Zerlegung
Definition (Zerlegung):
Die Zerlegung eines Relationenschemas
R = {A1 ... An} ist dessen Ersetzung durch eine
Menge von Relationenschemata
 = {R1, ... , Rk} mit Ri  R und
R = R1  ...  Rk.
Die Ri müssen dabei nicht disjunkt sein. Für
eine gute Zerlegung sollte  aber bestimmte
Eigenschaften aufweisen
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
59
Zerlegung
Beispiel (Zerlegung):
L_INFO = {LName, LAdresse, ArtName, Preis},
LIEFERANT = {LName, LAdresse} und
liefert = {LName, ArtName, Preis}.
Offensichtlich ist  = {LIEFERANT, liefert} eine
Zerlegung von L_INFO. Diese Zerlegung bereinigte
einige der in L_INFO aufgetretenen Anomalien.
Umgekehrt stellt sich die Frage, ob man L_INFO
eindeutig aus der Zerlegung zurückgewinnen kann?
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
60
Zerlegung
Beispiel (Zerlegung mit Verlusten):
Daß dies im allgemeinen nicht der Fall ist, zeigt
folgendes Beispiel:
r
A
B
C
17
98
21
21
45
69
r1
A
B
17
98
21
21
r2
B
C
21
21
45
69
Die Instanz r ist durch einen Natural Join aus den
Projektionen r1 und r2 nicht rekonstruierbar.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
61
Zerlegung
Es gilt:
 = {R1, R2} ist eine Zerlegung von R.
r1 = A,B(r), r2 = B,C(r)
Jedoch gilt: r  r1 |x|r1.B =r2.B r2
Die Instanz r ist demnach im allgemeinen
durch einen Natural Join aus den Projektionen
r1 und r2 nicht rekonstruierbar.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
62
Zerlegung
Definition (Verlustfreie Zerlegung bzgl. F):
• Sei  = {R1, ... , Rk} eine Zerlegung von R und F
eine Menge von FDs auf R. Die Zerlegung besitzt
einen verlustfreien Join bezüglich F, wenn für jede
Instanz r von R, die F erfüllt, gilt:
• r = R1(r) |x| ... |x| Rk(r)
• Die Wiedergewinnung der ursprünglichen Information
aus der Zerlegung ist also für solche  möglich, die
einen verlustfreien Join besitzen.
 heißt dann auch verlustfreie Zerlegung bzgl. F.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
63
Zerlegung
Eine verlustfreie Zerlegung für jede
Instanz r, die F erfüllt, erfordert:
– keine dangling Tupel in den
Projektionen,
– vollständige Kombinationen in R.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
64
Zerlegung
Beispiel 2 (Dangling Tupel):
r
A
B
C
17
21
45
r1
A
17
B
21
r2
B
C
21
45
69
50
Ausgangspunkt: r1 und r2; r ist natural join von r1 und r2.
Die anschliessende Zerlegung führt wieder zu einem Verlust.
Ursache für den Verlust bei der Zerlegung sind in diesem Fall
Tupel ohne natürlichen Joinpartner in r2.
Solche Tupel ((50,69) in r2) heissen auch dangling Tupel
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
65
Sichten / Views
Sichten (Views)
 Eine (Daten)Sicht ist eine virtuelle
Tabelle.
 In Sichten werden Informationen aus
mehreren Tabellen zusammen geführt.
 Eine Sicht wird nicht physikalisch
langfristig gespeichert, sondern bei jeder
Nutzung neu mittels einer zugeordneten
Abfrage aus den aktuellen Daten der
involvierten Tabellen neu berechnet.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
67
Sichten (Views)
 Eine Datensicht verhält ich bei Abfragen
wie eine reale Tabelle.
 Abfragen an Sichten können genau so
formuliert werden wie Abfragen an
Tabellen.
 Es sind Verbünde (Joins) mit anderen
Tabellen oder Datensichten möglich.
 Sichten erlauben auch
Datenmanipulationen
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
68
Sichten (Views)
 (Daten)Sichten gehören zum externen
Schema des ANSI-SPARC-Architektur.
 Sie ermöglichen es zusammengehörige
Daten für den Benutzer wieder
zusammenhängend zugänglich zu
machen, so dass sich dieser nicht um
das redundanzarme konzeptionelle
Schema kümmern muss.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
69
Sichten (Views)
 Resultate von Berechnungen werden
nicht in die Basisrelationen des
logischen (konzeptionellen)
Datenbankschemas aufgenommen.
 Sie können aber sinnvoll in Sichten
eingebracht und so auch weiter
verarbeitet werden – solche Sichten
erlauben allerdings keine Updates
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
70
Sichten (Views)
Datensichtdefinition in SQL (Syntax):
CREATE VIEW viewname [(spaltenliste)]
AS abfrage
[WITH [CASCADED | LOKAL] CHECK OPTION]
„ abfrage „ ist dabei eine beliebige Abfrage. Die
ORDER BY-Klausel ist darin nicht zulässig.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
71
Sichten (Views)
Die Angabe der Spaltennamen
(spaltenliste) kann entfallen, wenn die
Spaltennamen der „abfrage“
übernommen werden sollen.
WITH CHECK OPTION formuliert
Integritätsregeln für UPDATEOperationen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
72
Sichten (Views)
Beispiel:
CREATE VIEW kundenadr AS
SELECT kundennr, name, strasse, plz,ort
FROM kunde;
SELECT *
FROM kundenadr
ORDER BY kundennr
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
73
Sichten (Views)
CREATE VIEW rechnung AS
SELECT
be.bestellnr,
be.lieferdatum AS datum,
po.bestellmenge, po.liefermenge AS menge,
po.mwst,
po.gesamtpreis AS preis,
ku.kundennr,ku.name,ku.strasse, ku.plz, ku.ort,
ar.artikelnr
ar.verpackkung AS packung,
ar.bezeichnung ar.lagerplatz AS lager
FROM bestellung be
JOIN kunde ku
ON be.kundennr = ku.kundennr
JOIN position po
ON be.bestellnr = po.bestellnr
Join artikel ar
ON po.artikelnr = ar.artikelnr
WHERE be.lieferdatum IS NOT NULL;
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
74
Sichten (Views)
Mit der View „rechnung“ lässt sich sehr einfach
eine Rechnung „drucken" z.B.:
SELECT bestellnr, datum, menge, preis, name, artikelnr
FROM rechnung
WHERE bestellnr = 4711;
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
75
Sichten (Views)
CHECK OPTION
Änderung von Daten mittels Sichten
CREATE VIEW kunden_freiburg AS
SELECT kundennr, name, strasse, plz, ort
FROM kunde
WHERE ort = „Freiburg“;
UPDATE kunden_freiburg
SET ort=„Offenburg“
WHERE kundennr = 3612;
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
76
Sichten (Views)
CHECK OPTION
Die UPDATE-Anweisung führt dazu, dass
Kunde 3612 nicht mehr in der Sicht
„kunden_freiburg“ auftaucht.
Durch die CHECK OPTION kann ein solches
Update verhindert werden.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
77
Sichten (Views)
CHECK OPTION
CREATE VIEW kunden_freiburg AS
SELECT kundennr, name, strasse, plz,ort
FROM kunde
WHERE ort = „Freiburg“
WITH CHECK OPTION;
Ein Update wie vorher geschildert, wird dadurch zurück
gewiesen.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
78
Sichten (Views)
Aus Normalisierungsgründen werden beim
logischen Datenbankentwurf meist keine
Spalten in den Basisrelationen aufgenommen,
die durch Berechnungen aus vorhandenen
Attributwerten einzelner Tupel abzuleiten sind.
Beispiel:
Wert des Lagerbestandes pro Artikel
bestandswert = listenpreis * bestand
Mit Sichten ist dies jedoch sinnvoll möglich!
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
79
Sichten (Views)
Folgende Sicht zeigt den berechneten
„bestandswert“ an.
CREATE VIEW artikel_bestandswert AS
SELECT artikelnr, listenpreis, bestand,
listenpreis * bestand AS bestandswert
FROM artikel;
In der Spalte „bestandswert“ sind automatisch
keine Update und INSERT-Operation zulässig.
Prof. Dr. Fabian Glasen, Datenbanken, Februar 2002
80
Bibliotheksanwendung
ISBN
?
Verlag
Ort
Titel
Buch
1
ANr
n
Autor
von
m
Autor
n
Name
Vorname
SNr
hatAufl
von
Auflage
Preis
n
Buchauflage
hatExemp
n
BibNr
leiht
aus
m
BNr
n
hat
vorgemerkt
Name
1
Datum
Buchexemplar
Standort
Wort
Datum
1
Jahr
Schlagwort
m
Benutzer
Vorname
m
Adresse
82
Tabellenmodell
Autor(Anr, Name, Vorname)
Buch(ISBN, Titel, Verlag, Ort)
Autor-von(Anr, ISBN)
Schlagwort(SNr, Wort)
Schlagwort-von(SNr, ISBN)
Buchauflage(Auflage, Preis, Jahr, ISBN)
Buchexemplar(BibNr, Standort, ISBN, Auflage)
Benutzer(BNr, Name, Vorname, Adresse)
Leiht-aus(BibNr, BNr, Datum)
Hat-vorgemerkt(BNr, BibNr, Datum)
83
Autor
CREATE TABLE Autor (
ANr
INTEGER PRIMARY KEY,
Name
CHAR(30) NOT NULL,
Vorname
CHAR(30)
);
84
Buch
CREATE TABLE Buch (
ISBN
INTEGER PRIMARY KEY,
Verlag
CHAR(30) NOT NULL,
Titel
CHAR(30) NOT NULL,
Ort
CHAR(30)
);
85
Autor-von
CREATE TABLE Autor-von (
ISBN
INTEGER,
ANr
INTEGER,
PRIMARY KEY ( ISBN, ANr),
FOREIGN KEY ISBN REFERENCES Buch
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY ANr REFERENCES Autor
ON DELETE CASCADE ON UPDATE CASCADE
);
86
Schlagwort
CREATE TABLE Schlagwort (
SNr
INTEGER PRIMARY KEY,
Wort
CHAR(30) NOT NULL
);
87
Schlagwort von
CREATE TABLE Schlagwort-von (
ISBN
INTEGER
FOREIGN KEY REFERENCES Buch,
SNr
INTEGER
FOREIGN KEY REFERENCES
Schlagwort,
PRIMARY KEY ( ISBN, SNr)
);
88
Buchauflage
CREATE TABLE Buchauflage (
ISBN
INTEGER,
Auflage
INTEGER,
Preis
NUMERIC(8,2),
Jahr
CHAR(4),
PRIMARY KEY(ISBN, Auflage),
FOREIGN KEY ISBN REFERENCES Buch
ON DELETE CASCADE
ON UPDATE CASCADE);
89
Buchexemplar
CREATE TABLE Buchexemplar (
BibNR
INTEGER PRIMARY KEY,
Standort
CHAR(10),
ISBN
INTEGER NOT NULL,
Auflage
INTEGER NOT NULL,
FOREIGN KEY ISBN REFERENCES Buchauflage
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY Auflage REFERENCES Buchauflage
ON DELETE CASCADE ON UPDATE CASCADE
);
90
Benutzer
CREATE TABLE Benutzer (
BNr
INTEGER PRIMARY KEY,
Name
CHAR(30) NOT NULL,
Vorname
CHAR(30) NOT NULL,
Adresse
CHAR(60) NOT NULL
);
91
Leiht-aus
CREATE TABLE leiht-aus (
BibNr
INTEGER
FOREIGN KEY REFERENCES
Buchexemplar,
BNr
INTEGER
FOREIGN KEY REFERENCES Benutzer,
PRIMARY KEY (BNr, BibNr)
);
92
Hat-vorgemerkt
CREATE TABLE hat-vorgemerkt (
BibNr
INTEGER FOREIGN KEY
REFERENCES Buchexemplar,
BNr
INTEGER FOREIGN KEY
REFERENCES Benutzer,
Datum
DATE,
PRIMARY KEY (BNr, BibNr)
);
93
Bibliothek SQL-Aufgaben
• Buch einfügen:
 (Andreas Meier, Relationale Datenbanken,
Springer, Berlin, 4. Auflage, 2001, ISBN=3-54041468-1, (Schlagworte: Datenbank, Informatik),
Preis 45.90 DM, Bibliotheksnr 111)
• Buch updaten
• Ändern Sie das zugeordnete Schlagwort
"Datenbank" des eingegebenen Buches mit
einem SQL-Kommando in "Relationale
Datenbank" ab
94
Bibliothek SQL-Aufgaben
INSERT INTO Buch
(ISBN, Titel, Verlag, Ort) VALUES (3540414681,
'Relationale Datenbanken', 'Springer', 'Berlin');
INSERT INTO Buchauflage
(ISBN, Auflage, Preis, Jahr) VALUES (3540414681, 4,
45.90, '2001');
INSERT INTO Buchexemplar
(ISBN, Auflage, BibNr, Standort) VALUES (3540414681,
4, 111, NULL);
95
Bibliothek SQL-Aufgaben
INSERT INTO Schlagwort
(SNr,Wort) VALUES (1, 'Datenbank'), (2, 'Informatik');
INSERT INTO Schlagwort-von
(SNr,ISBN)
VALUES (1, 3540414681 ), (1, 3540414681 );
Update Schlagwort
SET Wort = 'Relationale Datenbank'
WHERE SNr =1
96
Bibliothek SQL-Aufgaben
• Benutzer erfassen:
 (Peter Georgi, 78462 Konstanz Inselgasse 25)
 Benutzer updaten
 (Peter Georgi 78462 Konstanz Obere Laube
22)
 Benutzer löschen
 (Peter Georgi 78462 Konstanz Obere Laube
22)
 Buch löschen (Andreas Meier ...)
97
Bibliothek SQL-Aufgaben
• Benutzer erfassen:
 (Peter Georgi, 78462 Konstanz Inselgasse 25)
 INSERT INTO Benutzer
(BNr, Name, Vorname, Adresse)
VALUES
(1, 'Georgi', 'Peter'‚ '78462 Konstanz Inselgasse
25');
98
Bibliothek SQL-Aufgaben
• Benutzer updaten:
 (Peter Georgi, 78462 Konstanz Obere Laube
22)
 UPDATE Benutzer
SET Adresse = '78462 Konstanz Obere Laube
22'
WHERE BNr=1;
99
Bibliothek SQL-Aufgaben
• Benutzer / Buch löschen:
 DELETE FROM Benutzer
WHERE BNr = 1;
 DELELTE FROM Buch
WHERE ISBN=3540414681;
100
Bibliothek SQL-Aufgaben
 Ausleihe
 Benutzer Peter Georgi leiht das erfasste Buch
von Andreas Meier am 7.3.2002 aus.
 Vormerkung
 Marianne Weber merkt das erfasste Buch am
8.3.2002 vor.
 Rückgabe
 Peter Georgi gibt das Buch am 10.3.2002
zurück.
101
Bibliothek SQL-Aufgaben
 Ausleihe
 Benutzer Peter Georgi leiht das erfasste Buch
von Andreas Meier am 7.3.2002 aus.
 INSERT INTO leiht-aus
(BibNr, BNr, Datum)
VALUES
(111,1,DATE'07-03-2002');
102
Bibliothek SQL-Aufgaben
 Vormerkung
Marianne Weber merkt das bereits erfasste Buch am
8.3.2002 vor.
 INSERT INTO Benutzer
(BNr,Name, Vorname,Adresse)
(2, 'Weber', 'Marianne', '78462 Konstanz
Konradigasse 7');
 INSERT INTO hat-vorgemerkt
(BibNr,BNr,Datum)
VALUES (111,2,DATE'08-03-2002');
103
Bibliothek SQL-Aufgaben
 Rückgabe
 Peter Georgi gibt das Buch am 10.3.2002
zurück.
 DELETE FROM leiht-aus
WHERE BibNr=111;
104