Folien im PDF

Teil 3:
Relationenmodell
Einleitung
Daten in Tabellen
Definitionen
Schlüssel
Fremdschlüssel
Null-Werte
gute Relationenschemata
Abbildung des E-RModells
1
Einleitung
Grundlage der meisten derzeitigen Datenbanken
vorgestellt von E.F. Codd 1970
!
Ziel: Daten-Unabhängigkeit
in kommerziellen DBMS seit 1981
basiert auf einer Variante des mathematischen
Konzepts der Relation
Relationen können auf einfache Weise als Tabellen
interpretiert werden
rein wertbasierte Verweise (keine Pointer)
2
Daten in Tabellen
K to N r
KundeNr
K to B e z.
Rahm en
S a ld o
2317
12345
O tto H u b e r
2 0 .0 0 0
1 2 .3 7 5
2318
12346
S p a rb u c h
0
545
2320
23456
R ü c k la g e
1 0 .0 0 0
2 5 .0 0 0
3
Mathematische Relation
D1, D2, ..., Dn : n Mengen
kartesisches Produkt: D1 x D2 x ... x Dn
!
!
Menge aller n-Tupel (d1, d2, ..., dn), sodaß
d1 ∈ D1, d2 ∈ D2, ..., dn ∈ Dn
Eine mathematische Relation auf D1, D2, ..., Dn :
!
Teilmenge des kartesischen Produktes D1 x D2 x ... x Dn
D1, D2, ..., Dn: Wertebereiche (Domänen) der
Relation
n ist der Grad der Relation
Anzahl der Tupel wird Kardinalität der Relation
genannt
4
Mathematische Relation(2)
Beispiel:
konto ⊆ integer x integer x string x number x number
!
{(2317, 12345, „Otto Huber“,20.000, 12.375), ...}
Attribute positional
2317
12345
Otto Huber
20.000
12.375
2318
12346
Sparbuch
0
545
2320
23456
Rücklage
10.000
25.000
5
Relationen in DB
Jeder Domain erhält eindeutigen Attributnamen
!
beschreibt die „Rolle“ des Domains
in Tabellen-Darstellung sind die Attributnamen die
Spaltenüberschriften
Bsp.:
K to N r
KundeNr
K to B e z.
Rahm en
S a ld o
2317
12345
O tto H u b e r
2 0 .0 0 0
1 2 .3 7 5
2318
12346
S p a rb u c h
0
545
2320
23456
R ü c k la g e
1 0 .0 0 0
2 5 .0 0 0
6
Definitionen
Relationen-Schema:
!
Name einer Relation mit einer Menge von Attributen A1, ..., An
R(A1, ..., An)
Datenbank-Schema:
!
Menge von Relationen mit unterschiedlichen Namen
R = { R1(X1), R2(X2), ..., Rn(Xn)}
Relation(Instanz) r mit einem Schema R(X):
!
Menge r von Tupel gemäß X
Datenbank (Instanz)
mit einem Schema R = { R1(X1), R2(X2), ..., Rn(Xn)}:
!
Menge von Relationen r = {r1, ..., rn} mit ri Relation auf Ri
7
Datenbank-Schema (Bsp.)
Mitarbeiter(MNr, Mname, GebDatum, AbtNr)
Abteilung(AbtNr, Bez, Ort, Leiter)
Kurs(KursBez, Leiter, Anzahl_Tage)
Zertifikat(MNr, KursBez, Punkte, Datum)
8
Datenbank (Beisp.)
Mitarbeiter
MNr
4541
4545
4567
4599
4587
Name
Huber
Berger
Dorfer
Maier
Müller
Geb.Datum
1.12.1965
3.5.1952
1.2.1973
11.5.1970
13.8.1969
AbtNr
16
16
23
23
12
Abteilung
AbtNr
16
12
15
23
Bez
Buha
Invest
Kredit
Spar
Ort
Klagenfurt
Graz
Graz
Wien
Leiter
4541
4587
4321
4567
9
Vorteile
Wertbasiert:
Jede Information in relationalen DBs wird durch Werte
ausgedrückt.
Referenzen (Verweise) ebenfalls nur durch Werte
logisches Modell unabhängig von physischer Struktur
enthält nur Information, die aus Anwendersicht relevant ist,
keine Implementierungsdetails
Leichte Transferierbarkeit von Daten zwischen Systemen
keine Pointer!
10
Relationen mit einem Attribut
Sind möglich (allerdings selten)
Beisp.:
Messestadt
Klagenfurt
Wien
Wels
Udine
11
Oberschlüssel
Relation ist Menge von Tupeln - Zeilen müssen
verschieden sein.
In Tabelle könnten 2 Zeilen gleich sein - Probleme
beim Zugriff auf eine bestimmte Zeile
Wenn keine Zeile mehrfach vorhanden sein kann dann enthält die Tabelle einen Schlüssel
Oberschlüssel:
!
identifizierende Attributkombination:
12
Schlüssel
Mitarbeiter(MNr, Name, SV-Nummer, AbtNr, Geb.datum)
Bsp. Oberschlüssel:
!
!
(MNr, Name, Geb.datum),
(SV-Nummer)
Schlüssel: minimaler Oberschlüssel
!
(d.h. wenn Attribut gestrichen wird, dann kein Oberschlüssel mehr)
Bsp. Schlüssel
!
!
MNr
SVNr
13
Schlüssel (2)
Schlüssel kann aus mehreren Attributen bestehen
Zertifikat(MNr, KursBez, Punkte, Datum)
!
!
ein Mitarbeiter kann mehrere Zertifikate haben, auch am
selben Tag
zu einem Kurs können an einem Tag mehrere Zertifikate
ausgestellt werden
Schlüssel: (Mnr, Kursbez, Datum)
14
Primärschlüssel
Mitarbeiter (MNr, Mname, SVNr, GebDatum, AbtNr)
in einer Relation kann es mehrere Schlüssel geben
!
Bsp.: SVNr, MNr
Primärschlüssel: ausgewählter Schlüssel mit dem
ein Tupel referenziert werden soll
!
Bsp: MNr ist Primärschlüssel
ein guter Primärschlüssel:
!
!
!
Wert immer bekannt
Wert ändert sich nie
... deshalb: SVNr, MNr, KontoNr, ....
15
Fremdschlüssel
Mitarbeiter (MNr, Mname, SVNr, GebDatum, AbtNr)
Abteilung(AbtNr, Bez, Ort, Leiter)
Fremdschlüssel:
!
!
!
Attribut einer Relation, das in einer anderen Relation
Schlüssel ist
Bsp.: Attribut Leiter in Abteilung ist Attribut Mnr in
Mitarbeiter
Attribut AbtNr in Mitarbeiter
realisiert Verweise auf eine andere Relation
16
UML - Klassendiagramm
-> Relationen
UML-Klassendiagramm für konzeptuellen Entwurf
Relationenmodell ist semantisch ärmer
!
!
!
nicht alles was im UML-Modell ausgedrückt werden kann,
kann auch im Relationenmodell ausgedrückt werden
Integritätsbedingungen im Datenmodell
Vorgaben für Programmierer und Benutzer
Abbildung notwendig
Schritte:
!
!
1. Vorbereiten des konzeptuellen Modells
2. Abbilden auf Relationen
17
Vorbereiten
Klassen-Diagramm
1. Eliminieren von Mehrfach-Attributen
!
eigenes Entity
2. Eliminieren von komplexen Attributen
!
„ausflachen“
Person
PID
Name
Hobbies 0..*
Adresse
PLZ
Person
PID
Name
PLZ
Ort
*
hat Hobby
*
Hobby
Bezeichnung
Ort
Ort
18
Vorbereiten(2)
mehrwertiges strukturiertes
Beziehungsattribut
Firma
liefert
*
Lieferung
Adresse
PLZ
Ort
Artikel
Firma
*
liefert
*
Lieferort
PLZ
Ort
*
Artikel
19
Abbildungsregeln (1)
Jede Klasse wird auf
eine eigene Relation
abgebildet
!
!
Name der Relation ist
Name der Klasse
Attribute der Klasse sind
Attribute der Relation
Mitarbeiter( MNr, Name, Job)
Mitarbeiter
MNr
Name
Job
20
Abbildungsregeln (2)
Jede m:n Assoziation wird auf
eine eigene Relation abgebildet
!
!
Name der Relation ist der Name
der Assoziation
Attribute der Relation sind
" Schlüsselattribute der beteiligten
Klasse (bei rekursiven
Beziehungen erweitert um die
Rollenbezeichnung)
" Attribute der Beziehung
Mitarbeiter
Mnr
Name
Job
m
Projektmitarbeit
n
Prozent
Projekt
Projektmitarbeit( MNr, ProjNr, Prozent)
ProjNr
Bez
21
Abbildungsregeln (3)
1:n Assoziation
!
!
Hier muß keine eigene Relation
gebildet werden
Die Relation der mehrfach
vorkommenden Klasse wird um
den Schlüssel der (Relation der)
einfach vorkommenden Klasse
und der Assoziations-Attribute
erweitert.
Mitarbeiter
Mnr
Job
1
Βetreuer
betreut
n
seit
Kunde
Kunde(KNr, Kname, Betreuer-MNr, seit)
KNr
Name
22
Abbildungsregeln (4)
1:1 Assoziationen
!
!
Ebenfalls keine eigene Relation
eine der beiden Relationen wird
um den Schlüssel und um die
Assoziationsatribute der anderen
erweitert.
Mitarbeiter
Mnr
Name
Job
1 Leiter
leitet
0..1
Projekt( ProjNr, Bez, Leiter-MNr, seit)
seit
Projekt
ProjNr
Bez
23
Abbildungsregeln (5)
Assoziationen höheren
Grades
!
!
!
eigene Relation
besteht aus Schlüsseln der
beteiligten Entities
und den
Beziehungsattributen
Lieferung(LiefNr,AbtNr,ArtikelNr)
Artikel
Lieferant
Lieferung
Abteilung
24
Abbildung
berichtet an
0..1
Chef
Mitarbeiter
0..n MNr. key
Mitarbeiter Name
Job
1
betreut
0..1
1
*
arbeitet in
leitet
Anteil
0..1
*
Projekt
Proj.Nr. key
Bezeichnung
*
Kunde
KNr. key
Name
Anschrift
gibt auf
*
Bestellung
Best.Nr. key
Daten
*
umfaßt
*
Produkt
Preis
Prod.Nr. key
Bezeichnung
25
Abbildung (Ergebnis)
Mitarbeiter( MNr, Name, Job, Chef-MNr)
Projekt( ProjNr, Bez, Leiter-MNr)
Kunde( Knr, Name, Anschrift, Betreuer-MNr)
Bestellung( BestNr, Datum, Besteller-KNr)
Produkt(ProdNR, Bez, Preis)
Projektarbeit(MNr, ProjNr, Prozent)
Bestellumfang(BestNr, ProdNr)
26
Teil 4:
SQL
Einleitung
Tabellendefinition
Projektion
Selektion
Join
Null-Werte
Komplexe
Bedingungen
Abfragegraph
27
Einleitung
SQL: Structured Query language
Standard für relationale Datenbanken
standardisiert: ANSI, ISO
SQL-89, SQL-92, SQL-99
Teile „implementor defined“
herstellerspezifische Erweiterungen
28
SQL-Umfang
Datendefinitionssprache
!
!
!
Wertebereiche
Relationen
Sichten (externe Modelle)
Datenmanipulationssprache
!
Select, Insert, Update, Delete
Application Programming Interface
!
embedded SQL
physisches Modell
!
tablespace, index, cluster
Operationen
!
prozedurale Erweiterungen
29
Schema
Mitarbeiter (MNr, Mname, AbteilungsNr, GebDatum,
GebOrt, Chef, Punkte, Klappe)
Abteilung (AbteilungsNr, Bez, Ort, Leiter)
Kunde (KNr, Kname, Ort)
KundenKontakt (KNr, MNr)
Bestellung (BNr, BestDatum, Betreuer_MNr, KNr)
Artikel (ANr, Abez, Preis)
BestellPos (BNr, ANr, Anzahl)
30
Datendefinitionssprache
Definition von Tabellen
(Relationenschema)
allgemeine Form (vereinfacht)
CREATE TABLE tableName
({columnName
Datatype [{constraints}]})
tableName, columnName: übliche Bezeichner;
nicht sensitiv auf
Groß/Kleinschreibung
31
Datendefinitionssprache
Dateitypen
Datentypen (Wertebereiche)
CHAR(size), VARCHAR(size),
VARCHAR2(size)
LONG
Zeichenketten mit maximaler
Länge size
Zeichenketten bis zu einer Länge
von 231 - 1 Bytes (2GB)
RAW(size)
BLOBS (binary large objects) Byteketten
LONG RAW
analog CHAR und LONG
NUMBER [(precision [, scale])] Zahlen (Fest- u. Gleitpunkt)
DATE
Datum
ROWID
eindeutiger Tupelidentifikator
aus Kompatibilitätsgründen:
INTEGER, FLOAT [(p)], SMALLINT, REAL, DECIMAL (p, s)
Subtypen von NUMBER
32
Constraints (1)
Einschränkung der zulässigen Datenbankzustände
Alle Insert, Update und Delete Operationen bewirken die
Evaluation der relevanten Einschränkungen
Operationen werden nur dann ausgeführt, wenn sie die
Constraints nicht verletzen
Bedingungen:
NOT NULL
Attributwert muß ungleich NULL sein
UNIQUE
Attributwert darf in einer Spalte nur einmal
vorkommen
Primärschlüssel (NOT NULL notwendig)
Fremdschlüssel
Angabe einer Bedingung
PRIMARY KEY
FOREIGN KEY
CHECK
33
Column Constraints and
Table Constraints
Bsp.:
CREATE TABLE Flug
(Flugnr NUMBER (3)
NOT NULL,
Linie
CHAR (3)
NOT NULL,
DATUM Date,
Abflug Time with Time Zone,
Ankunft Time with Time Zone,
Preis
Number,
CONSTRAINT
Schlüssel_Flug
PRIMARY KEY
(FlugNr, Linie, Datum, Sitz),
CONSTRAINT
vor_nach
CHECK (Abflug < Ankunft),
CONSTRAINT
ok
CHECK (Preis >= (SELECT ...));
34
Tabellendefinition
create table Abteilung
(AbteilungsNr integer primary key not null,
Bez
varchar(15),
Ort
varchar(10),
Leiter
integer
);
create table Mitarbeiter
(MNr
integer primary key not null,
Mname
varchar(20),
AbteilungsNr integer references Abteilung,
GebDatum
date,
GebOrt
varchar(10),
Chef
integer references Mitarbeiter(MNr),
Punkte
integer,
Klappe
varchar(4)
);
35
Tabellendefinition(2)
create table Kunde
(KNr
integer primary key not null,
Kname
varchar(20),
Ort
varchar(10)
);
create table KundenKontakt
(KNr
integer not null references Kunde,
MNr
integer not null references Mitarbeiter
constraint Kkkey primary key(KNr, MNr)
);
36
Tabellendefinition(3)
create table Bestellung
(BNr
integer primary key not null,
BestDatum
date,
Betreuer_MNr integer references Mitarbeiter,
Knr
integer references Kunde);
create table Artikel
(Anr
integer not null primary key,
ABez
varchar(15),
Preis
integer );
create table bestellPos
(BNr
integer not null references Bestellung,
Anr
integer not null references Artikel,
Anzahl
integer
constraint bpkey primary key (BNr, Anr));
37
Datendefinitionssprache
Entfernen von Tabellen
DROP TABLE tableName
Bsp.:
DROP TABLE Mitarb
38
Assertionen (Zusicherungen)
i. d. R. Bedingungen, die mehrere Tabellen betreffen
Bsp.: CREATE ASSERTION positiv
CHECK ((SELECT SUM (Betrag) FROM Ausgaben)
<
(SELECT SUM (Betrag) FROM Einnahmen))
39
(Benannte) Referentielle
Integrität
Jeder Wert eines Fremdschlüsselattributs muß in der referenzierten
Tabelle (als Schlüssel) vorkommen.
Bsp.:
Alle Werte des Attributes AbtNo in der Tabelle Mitarb
müssen auch als Werte des Attributes AbtNo in der
Tabelle Abteilung vorkommen.
CREATE TABLE Mitarb
...
CONSTRAINT M_Abt_fk FOREIGN KEY (AbtNo)
REFERENCES Abteilung (AbtNo)
...
40
Referentielle Constraint
Aktionen
geben an, was passieren soll, wenn ref. Integrität verletzt wird
❚
Anlässe: Delete, Update
Änderung fortpflanzen
AbtNo Number (3) REFERENCES Abteilung
ON UPDATE CASCADE
Aktion zurückweisen: durch Definition
des Foreign Key
❚
Auf Default setzen, auf Null setzen
...
AbtNo Number (3)
DEFAULT '10'
REFERENCES Abteilung
❚
Automatisches Löschen von abhängigen
Fremdschlüsseln
ON DELETE CASCADE
keine Aktion
ON DELETE NO ACTION
ON DELETE SET DEFAULT,
ON UPDATE SET NULL
...
❚
Cascade kann über mehrere FremdschlüsselVerbindungen definiert sein
41
Basisoperationen
Projektion:
!
Auswählen von Spalten aus einer Tabelle
Selektion:
!
Auswählen von Zeilen aus einer Tabelle
Kartesisches Produkt
!
jedes Tupel mit jedem erweitern
Verbund
!
Vereinigen zweier Tabellen aufgrund gemeinsamer Werte
Vereinigung
!
Mengenvereinigung der Tupelmengen
Durchschnitt
Komplement
42
Grundform der
Select-Anweisung
Select
t1.a1, t1.a2, ..., tm.an
Zielattribute
From
R1 t1, R2 t2, ..., Rm tm
Relationen
Where
P1 AND P2, ... AND Pk
Selektionsbedingung
entspricht Ausdruck im Tupelkalkül
{(t1.a1, t1.a2, ..., tm.an) |
t1∈R1, t2 ∈ R2, ..., tm ∈ Rm,
P1, P2, ..., Pk }
43
Projektion
Auswählen von Spalten aus einer Tabelle
„Name und Geburtsdatum aller Mitarbeiter“
Select
From
MName, GebDatum
Mitarbeiter;
MNAME
-------Huber
Neumann
Novak
Willy
Horvat
Asthobl
Baumer
Zuder
GEBDATUM
--------03-NOV-52
01-FEB-66
05-DEC-41
07-JUL-70
29-OCT-59
11-NOV-69
10-SEP-65
27-APR-43
44
Projektion(2)
Alle Daten einer Tabelle
Select
From
*
Mitarbeiter;
MNR MNAME
ABTEILUNGSNR GEBDATUM GEBORT
CHEF PUNKTE KLAP
------ -------- ------------ --------- -------- ---- ------ ---27 Huber
10 03-NOV-52 Wien
11
60 547
45 Neumann
20 01-FEB-66 Linz
27
51 655
23 Novak
30 05-DEC-41 Wels
11
20 877
22 Willy
40 07-JUL-70 Wien
27
67 124
1777 Horvat
20 29-OCT-59 Linz
45
78
56 Asthobl
20 11-NOV-69 Wien
45
43
1566 Baumer
30 10-SEP-65 Villach
23
65 765
11 Zuder
27-APR-43 Villach
30 523
45
Distinct
Eliminieren von Duplikaten
Select Distinct Ort
From Abteilung;
ORT
-------Linz
Wien
Select Ort
From
Abteilung;
ORT
-------Wien
Linz
Wien
Linz
46
Selektion
Auswahl von Zeilen einer Tabelle
„Die Daten aller Abteilungen in Wien“
Select
From
Where
ABTEILUNGSNR
-----------10
30
*
Abteilung
Ort = ´Wien´
BEZ
--------------Strickwaren
Eisenwaren
ORT
LEITER
-------- ---------Wien
27
Wien
23
47
Selektion (2)
Mehrere Bedingungen
„Alle Mitarbeiter der Abteilung 20 mit mehr als 50 Punkten“
Select
From
Where
And
MNR
-----45
1777
*
Mitarbeiter
AbteilungsNr = 20
Punkte > 50;
MNAME
ABTEILUNGSNR GEBDATUM GEBORT
CHEF PUNKTE KLAP
-------- ------------ --------- -------- ---- ------ ---Neumann
20 01-FEB-66 Linz
27
51 655
Horvat
20 29-OCT-59 Linz
45
78
48
Projektion und Selektion
Name und Telefonnummer aller Mitarbeiter in den
Abteilungen 20 und 30 mit mehr als 50 Punkten
Select
From
Where
And
Or
Name, Klappe
Mitarbeiter
Punkte > 50
(AbteilungsNr = 20
AbteilungsNr = 30);
MNAME
-------Neumann
Horvat
Baumer
KLAP
---655
765
49
Selektion(3)
Selektion nach Schlüssel:
Name des Mitarbeiters mit der MNr 27
Select
From
Where
Mname as Name
Mitarbeiter
Mnr = 27;
NAME
-------Huber
50
Selektion(4)
Selektion kann zur leeren Menge als Ergebnis führen
„Name des Mitarbeiters mit der MNr 125“
Select
From
Where
Mname as Name
Mitarbeiter
Mnr = 125;
No rows selected
51
Sortieren
Sortieren der Ergebniszeilen
„Punkte, Name und Klappe aller Mitarbeiter der Abteilung 20
absteigend nach Punkten und aufsteigend nach Namen
sortiert“
Select
From
Where
Order by
Punkte, MName, Klappe
Mitarbeiter
AbteilungsNr = 20
Punkte Desc, MName;
PUNKTE
-----78
51
43
MNAME
KLAP
-------- ---Horvat
Neumann 655
Asthobl
52
karthesisches Produkt
Kombination von Tupeln aus mehreren Relationen
Select
*
From
Mitarbeiter, Abteilung;
MNR MNAME
ABTEILUNGSNR GEBDATUM GEBORT
CHEF PUNKTE KLAP ABTEILUNGSNR
------ -------- ------------ --------- -------- ---- ------ ---- ----------BEZ
ORT
LEITER
--------------- -------- ---------27 Huber
10 03-NOV-52 Wien
11
60 547
10
Strickwaren
Wien
27
45 Neumann
Strickwaren
23 Novak
Strickwaren
...
27
51 655
10
Wien
20 01-FEB-66 Linz
27
11
20 877
10
Wien
30 05-DEC-41 Wels
27
32 rows selected.
53
Selektion und karthesisches
Produkt
Rollennamen für Relationen
Select
From
Where
m.Mname, a.Ort
Mitarbeiter m, Abteilung a
m.AbteilungsNr = a.AbteilungsNr;
MNAME
-------Huber
Neumann
Novak
Willy
Horvat
Asthobl
Baumer
ORT
-------Wien
Linz
Wien
Linz
Linz
Linz
Wien
54
Verbund
Verbinden zweier Tabellen aufgrund gleicher Attribute
insbesondere über Schlüssel - Fremdschlüssel Verbindungen
„Name und Beschäftigungsort aller Mitarbeiter mit mehr
geordnet nach Ort“
Select
m.Mname, a.Ort
MNAME
-------From Mitarbeiter m, Abteilung a
Neumann
Where
m.AbteilungsNr =
Willy
a.AbteilungsNr
Horvat
And
m.Punkte > 40
Asthobl
Order by
a.Ort;
Huber
Baumer
als 40 Punkten
ORT
-------Linz
Linz
Linz
Linz
Wien
Wien
55
Verbund(2)
Verbund über Nichtschlüssel
Name und Abteilungsbez. der Mitarbeiter, an deren Geburtsort
eine Abteilung lokalisiert ist.
Select
From
Where
Mitarbeiter.Mname as Name,
Abteilung.Bez as Department
Mitarbeiter m, Abteilung a
Mitarbeiter.GebOrt = Abteilung.Ort;
56
Verbund(2a)
NAME
-------------------Neumann
Horvat
Neumann
Horvat
Huber
Willy
Asthobl
Huber
Willy
Asthobl
DEPARTMENT
--------------Strickwaren
Strickwaren
Literatur
Literatur
Strickwaren
Strickwaren
Strickwaren
Eisenwaren
Eisenwaren
Eisenwaren
57
Theta-Verbund
Verbund durch beliebige Vergleichsoperation
„Welche Mitarbeiter habe eine niedrigere MNr als der Leiter der
Abteilung 30“
Select
From
Where
And
Mname
Mitarbeiter m, Abteilung a
m.MNr < a.Leiter
a.AbteilungsNr = 30;
MNAME
-------Zuder
Willy
58
Mehrwegverbund
„Kunde und Artikelbez von Bestellungen, die von
Mitarbeiter mit der Nr. 1777 betreut werden?“
Kunde(KNr, Kname, Ort)
Bestellung(BNr, BestDatum, Betreuer_MNr, KNr)
BestellPos(BNr, Anr, Anzahl)
Artikel( ANr, Abez, Preis)
59
Mehrwegverbund(2)
„Kunde und Artikelbez von Bestellungen, die von Mitarbeiter mit
der Nr. 1777 betreut werden?“
Select
From
Where
And
And
And
k.Kname, a.ABez
Kunde k, Bestellung b, Bestellpos ba, Artikel a
k.KNr = b.KNr
b.Betreuer_MNr = 1777
ba.BNr = b.BNr
ba.ANr = a.ANr;
60
Mehrwegverbund(3)
KNAME
-------------------Zellerwitz
Zellerwitz
Hopfmgartner
Wankelhiber
Wankelhiber
ABEZ
--------------Hemd
Hosentraeger
Kleiderbuegel
Hosen
Hemd
61
Semi-Verbund
Verbund zweier Tabellen, wobei nur auf Attribute einer der
Tabellen projeziert wird.
„Name aller Mitarbeiter von Abteilungen in Linz“
Select
From
Where
And
m.Mname as Name
Mitarbeiter m, Abteilung a
m.AbteilungsNr = a.AbteilungsNr
a.Ort = ´Linz´;
NAME
---------Neumann
Willy
Horvat
Asthobl
62
Autoverbund
Join einer Tabelle mit sich selbst
„Wer hat mehr Punkte als sein Chef?“
Select
From
Where
And
u.MName
Mitarbeiter o, Mitarbeiter u
u.Chef = o.MNr
u.Punkte > o.Punkte;
MNAME
-------Huber
Willy
Horvat
Baumer
63
Nullwerte
Spezieller Wert NULL
mit keinem anderen Wert vergleichbar
Name aller Mitarbeiter, deren Klappe unbekannt ist
Select
From
Where
Mname
Mitarbeiter
Klappe is NULL;
MNAME
-------Horvat
Asthobl
64
Logische Operatoren
Vergleich eines Wertes mit NULL ergibt immer NULL
Logik mit 3 Werten: True, False, Null
Wahrheitstabellen
AND T
F
Null
OR
T
F
Null
T
T
F
Null
T
T
T
T
F
F
F
F
F
T
F
Null
F
Null
Null T
Null
Null
Null Null
65
Nullwerte - Vergleich
Not(NULL) ist NULL
„Alle Mitarbeiter deren Klappe ungleich 523 ist.“
Select
From
Where
Mname, Klappe
Mitarbeiter
Klappe <> 523;
MNAME
-------Huber
Neumann
Novak
Willy
Baumer
KLAP
---547
655
877
124
765
66
Vereinigung
Die Orte aller Kunden und Abteilungen
Select
From
Union
Select
From
Ort
Kunde
Ort
Abteilung
ORT
-------Linz
Villach
Wien
67
Vereinigung(2)
Die Orte aller Kunden und Abteilungen mit
„Mehrfachnennung“
Select
From
Union
Select
From
Ort
Kunde
all
Ort
Abteilung
ORT
-------Wien
Villach
Linz
Linz
Wien
Linz
Wien
Linz
68
Durchschnitt
„Die Orte aller Kunden in denen Abteilungen
lokalisiert sind“
Select
From
Intersect
Select
From
Ort
Kunde
ORT
-------Linz
Wien
Ort
Abteilung
69
Komplement
„Die Orte aller Kunden in denen keine Abteilungen
lokalisiert sind“
!
Achtung: Systemabhängig in DB2: except
Select
From
Minus
Select
From
Ort
Kunde
ORT
-------Villach
Ort
Abteilung
70
Teil 5
SQL - Fortsetzung
Vergleichsoperatoren
Subqueries
Quantoren
korrelierte Subqueries
Aggregation
Aggregatsfunktionen
71
Between
A Between X and Y entspricht A >= X AND A<= Y
„Nummer und Namen aller Mitarbeiter die 20 - 60
Punkte haben“
Select
From
Where
MNr, MName
Mitarbeiter
Punkte between 20 and 60;
72
Or
Verknüpfen von Bedingungen mit oder
„Abteilungsnummern aller Mitarbeiter die in Graz
oder Wien geboren wurden“
Select Distinct AbteilungsNr
From
Mitarbeiter
Where
(GebOrt = Wien
Or
GebOrt = Graz);
73
IN
Überprüfen auf Mengenmitgliedschaft mit IN
„Namen aller Mitarbeiter die in Graz oder Wien
geboren wurden“
Select Distinct MName
From
Mitarbeiter
Where
GebOrt in ( 'Wien', 'Graz');
74
Einfache Subquery
Ermitteln einer Menge durch eine Subquery
„Namen aller Kunden, an deren Ort eine Abteilung
existiert “
Select
From
Where
KName
Kunde
Ort in
( select Ort
from Abteilung);
75
Einfache Subquery(2)
Ermitteln einer Menge durch eine Subquery
„Namen aller Mitarbeiter von Abteilungen in Linz “
Select
From
Where
MName
Mitarbeiter
AbteilungsNr in
( select AbteilungsNr
from abteilung
where Ort = 'Linz');
76
Quantoren
Quantifizierung von Vergleichen mit einer Menge mittels ALL
und SOME (bzw. ANY)
„Name und Abteilungsnr. des Mitarbeiters mit den meisten
Punkten“
Select
From
Where
Mname, AbteilungsNr, Punkte
Mitarbeiter
Punkte >= ALL
( select Punkte
from Mitarbeiter);
77
Quantoren (2)
„Name und Abteilungsnr. des Mitarbeiters mit den
meisten Punkten“
Select
From
Where
Mname, AbteilungsNr, Punkte
Mitarbeiter
NOT Punkte < ANY
( select Punkte
from Mitarbeiter);
78
Quantoren (3)
= ANY und IN sind äquivalent
„Name aller Mitarbeiter aus Abteilungen in Linz“
Select
From
Where
Mname
Mitarbeiter
AbteilungsNr = ANY
( select AbteilungsNr
from Abteilung
where Ort = 'Linz');
79
Korrelierte Subqueries
In Bedingung der Subquery wird auf das aktuelle Tupel in der
übergeordneten Query Bezug genommen.
Qualifikation wie beim Join
„Bezeichnung aller Abteilungen, in denen jemand arbeitet, der
in Linz geboren wurde.“
Select
From
Where
Bez
Abteilung abt
'Linz' in
( select GebOrt
from Mitarbeiter m
where m.AbteilungsNr = abt.AbteilungsNr);
80
Korrelierte Subqueries(2)
Viele Subqueries können auch als Joins ausgedrückt
werden
„Bezeichnung aller Abteilungen, in denen jemand
arbeitet, der in Linz geboren wurde.“
Select
From
Where
And
DISTINCT abt.Bez
Abteilung abt, Mitarbeiter m
m.AbteilungsNr = abt.AbteilungsNr
m.GebOrt = 'Linz';
81
Korrelierte Subqueries(3)
Kombination mit Quantoren macht Subqueries mächtiger als
Joins
„Bezeichnung aller Abteilungen, in denen nur Mitarbeiter
arbeiten, die in Linz geboren wurde.“
Select
From
Where
Bez
Abteilung abt
'Linz' = ALL
( select GebOrt
from Mitarbeiter m
where m.AbteilungsNr = abt.AbteilungsNr);
82
Exists - Subquery
Exists(subqu.) ist wahr, wenn subquery ein nichtleeres Ergebnis
hat
„Bezeichnung aller Abteilungen, in denen Mitarbeiter arbeiten,
die in Linz geboren wurde.“
Select
From
Where
Bez
Abteilung abt
exists
( select 1
from Mitarbeiter m
where m.AbteilungsNr = abt.AbteilungsNr
and
m.GebOrt = 'Linz');
83
Not Exists - Subquery
Not Exists(subqu.) ist wahr, wenn subquery ein leeres Ergebnis
hat
„Bezeichnung aller Abteilungen, in denen nur Mitarbeiter
arbeiten, die in Linz geboren wurde.“
Select
From
Where
Bez
Abteilung abt
Not exists
( select 1
from Mitarbeiter m
where m.AbteilungsNr = abt.AbteilungsNr
and
m.GebOrt <> 'Linz');
84
Geschachtelte Subqueries
„Bezeichnung aller Abteilungen, in denen Mitarbeiter arbeiten,
die Kundenkontakt haben.“
Select
From
Where
Bez
Abteilung abt
exists
( select 'x'
from Mitarbeiter m
where m.AbteilungsNr = abt.AbteilungsNr
and
m.Mnr in
(select Mnr from Kundenkontakt));
85
Skalarübergang
Ergebnis einer Query: Relation - Relationen, die aus genau einer
Zeile und einer Spalte bestehen, können als skalar interpretiert
werden
„Bezeichnung der Abteilung, in denen der Mitarbeiter mit der
MNR 1777 arbeitet.“
Select
From
Where
Bez
Abteilung abt
AbteilungsNr =
( select AbteilungsNr
from Mitarbeiter
where Mnr = 1777);
86
Skalarübergang (2)
„Wer hat mehr Punkte als sein Chef?“
Select
From
Where
MNr, Mname
Mitarbeiter unter
unter.Punkte >
( select ober.punkte
from Mitarbeiter ober
where unter.Chef = ober.MNr);
87
Beispiel
Name der Leiter aller Abteilungen, bei denen alle Mitarbeiter
Kontakt zu Kunden aus Linz haben
Select
From
Where
And
Distinct Mname
Mitarbeiter m1, abteilung a
m1.Mnr = a.Leiter
not exists
(select 1 from mitarbeiter m2
where m2.AbteilungsNr = a.AbteilungsNr
and m2.MNr not in
(select MNr from Kundenkontakt kk
where kk.Knr in
(select Knr from kunde
where Ort ='Linz')));
88
Vorsicht bei Null
X in (NULL, 1, 2, 3) ergibt nie False (sondern True oder Null)
X not in (NULL, 1, 2, 3) ergibt nie TRUE !
„Alle Mitarbeiter, die nicht Chef sind“
Select
From
Where
MNr, MName
Mitarbeiter
MNr not in
(select chef
from Mitarbeiter
where chef is not NULL);
89
Count (*)
Count(*) zählt die Tupel der Ergebnisrelation
Wieviele Mitarbeiter gibt es in Abteilungen in Linz?
Select
From
Where
count(*)
Mitarbeiter
AbtNr in
(select Abtnr
from Abteilung
where Ort ='Linz');
90
Count
ALL:
Count(All attrib) zählt die Anzahl der Werte ungleich NULL im angegeb.
Attribut Tupel der Ergebnisrelation
Wieviele Mitarbeiter haben Chefs?
Select
From
count(ALL Chef)
Mitarbeiter;
DISTINCT:
Count(distinct attrib) zählt die Anzahl der unterschiedlichen Werte
ungleich NULL im angegeb. Attribut Tupel der Ergebnisrelation
Wieviele Mitarbeiter sind Chefs?
Select
From
count(DISTINCT Chef)
Mitarbeiter;
91
AVG, SUM, MIN, MAX
AVG retourniert das arithmetische Mittel der (bekannten)
numerischen Werte (ohne Null)
SUM retourniert die Summe der numerischen Werte (ohne Null)
MAX (resp. MIN) retourniert den größten (resp. kleinsten) Wert
(ohne Null)
Die durchschnittliche, minimale und maximale Punkteanzahl der
Mitarbeiter und die Gesamtpunkteanzahl der Abteilungen in Linz?
Select
From
Where
And
avg(Punkte), min(Punkte), max(Punkte), sum(Punkte)
Mitarbeiter m, Abteilung a
a.AbteilungsNr = m.AbteilungsNr
a.Ort ='Linz';
92
Aggregatsfunktionen in
Subqueries
Wer hat mehr Punkte als der Durchschnitt in seiner Abteilung?
Select
From
Where
MNr, Mname
Mitarbeiter m1
Punkte >
(Select avg(Punkte)
from mitarbeiter m2
where m1.AbteilungsNr = m2.AbteilungsNr);
93
Gruppierung
Group by partitioniert eine Ergebnis-Relation aufgrund gleicher
Werte in angegebenen Attributen (Gruppierungsattributen)
Auf die anderen Attribute können Aggregationsfunktionen
angewendet werden.
Die minimale, maximale, durchschnittliche Punktezahl je
Abteilung
Select
AbteilungsNr, min(Punkte), max(Punkte),
avg(Punkte)
From
Mitarbeiter
Group by
AbteilungsNr;
94
Gruppierung(2)
Abt.Nr.
Name
Punkte
20
Otto
35
Hugo
50
30
Karl
20
40
Toni
60
Fritz
40
Peter
55
95
Gruppierung(3)
Die Summe der Punkte der Mitarbeiter, die nicht Chef
sind, je Standort
Select
From
Where
a.Ort, sum(m.Punkte)
mitarbeiter m, abteilung a
m.MNr not in
(select chef from mitarbeiter
where chef is not NULL)
Group by a.Ort
96
Selektion von Gruppen
Having erlaubt es aus den mit Group By erzeugten
Gruppen zu selektieren.
Die Summe der Punkte der Mitarbeiter je Abteilung,
die mehr als 2 Mitarbeiter hat
Select
From
Group by
Having
AbteilungsNr, sum(Punkte)
Mitarbeiter
AbteilungsNr
Count(*) > 2;
97
Selektion von Gruppen(2)
In Having Klausel sind alle Suchbedingungen erlaubt.
AbteilungsNr, -Bezeichnung und Punktedurchschnitt der
Mitarbeitern je Abteilung, für Abteilungen, bei denen nicht der
Leiter das Punktemaximum hat.
Select
From
Where
Group by
Having
a.AbteilungsNr, a.Bez, a.Leiter, avg(m.Punkte)
mitarbeiter m, abteilung a
m.AbteilungsNr = a.AbteilungsNr
a.AbteilungsNr, a.Bez, a.Leiter
max(m.Punkte) >
(select m2.punkte from mitarbeiter m2
where m2.MNr = a.Leiter);
98
Where vor Having
Besser Bedingung in where-Klausel angeben
Differenz zwischen minimaler und maximaler Punkteanzahl von
Abteilungen in Graz, Linz, Wien die mehr als 2 Mitarbeiter haben
Select
From
Where
And
Group by
Having
a.AbteilungsNr, a.Bez, a.Ort
max(m.Punkte) - min(m.Punkte) AS Bandbreite
mitarbeiter m, abteilung a
m.AbteilungsNr = a.AbteilungsNr
a.Ort in ('Graz','Linz','Wien')
a.AbteilungsNr, a.Bez, a.Ort
count(*) > 2;
99
Warnung!
Große Unterschiede
zwischen den einzelnen DBMS-Produkten
bei Datentypen, Operationen und Funktionen
100
Zahlen
Oracle:
Number(precision, scale)
! precision gibt die Anzahl der Stellen an (Dezimalzahlen)
! scale die Anzahl der Stellen nach dem Komma
DB2:
Smallint
integer
decimal(p,s)
! p - Anzahl der Stellen
! s - Anzahl Nachkommastellen
Real
Double
101
Arithmetische Operationen
+, -, *, /
kann in Select-Klausel, Where-Klausel oder Having-Klausel
verwendet werden
Vergleichsoperatoren: =, <>, !=, <, <=, >, >=
Bsp: Rechnungssumme inklusive 100 Versandpauschale und
20%MWST
Select
b.Bnr, (SUM(b.Anzahl*a.Preis) + 100 ) *1.2
From
Artikel a, bestellPos b
Where
a.Anr = b.Anr
Group by
b.Bnr;
102
Operationen auf Zahlen
abs(n)
ceil(n)
floor(n)
mod(m,n)
power(m,n)
round(n, [m]
sqrt(n)
truncate(n,[m])
Absolutwert einer Zahl
kleinste ganze Zahl >= n
größte ganze Zahl <= n
Divisionsrest von m durch n
m hoch n
rundet n (auf m Stellen genau)
Quadratwurzel von n
n auf m Nachkommastellen abschneiden
103
Zeichenketten
Char(n)
Zeichenkette fester Länge
Varchar(n) Zeichenkette variabler Länge
Operationen: || Konkatenation
Vergleiche:
=, <>, !=, <, <=, >,>=
Beim Vergleich wird die kürzere Zeichenkette links
mit Leerzeichen aufgefüllt
< etc. bezieht sich auf Ordnung der Zeichenketten abhängig von der „Collating Sequence“ Unterschiede vor allem bei „national character sets“
104
Funktionen auf Zeichenketten
Chr(n)
Upper(c)
lower(c )
Substr(c, m, [n])
n
length(c)
concat(c1, c2)
Character mit ASCII-Code n
String c mit allen Buchstaben als
Großbuchstaben
alles Kleinbuchstaben
Teilkette von c ab dem m-ten Zeichen und
Zeichen lang
Länge der Zeichenkette
Konkatenieren zweier Zeichenketten
105
Like
Patternmatching: attribute LIKE pattern
wildcards:
% für beliebig lange Zeichenkette (0, 1, viele)
_ für genau ein Zeichen
Select
From
Where
MNr, Mname
Mitarbeiter
Mname like ´M%_er´;
106
soundex
Vergleicht 2 Zeichenketten phonetisch
Beispiel: alle die „Meier“ heißen, egal wie geschrieben
select * from Namen;
NAME
-------------------Maier
Mayr
Meir
Meier
Meyr
Mailand
Huber
select * from Namen where
soundex(name) = soundex('Meier');
NAME
-------------------Maier
Mayr
Meir
Meier
Meyr
107
Datum
Date
besteht aus Tag/Monat/Jahr
Vergleiche:
!
d1 < d2
d1 ist zeitlich vor d2
für Ein-/Ausgabe muß Datum auf bzw. von String konvertiert werden.
to_char(d, fmt)
wandelt Datum d gemäß Formatmodell in
String um
to_date(c, fmt)
wandelt String c gemäß Formatmodell in
Datum um
Date + n
n Tage später
Date - n
d1 - d2
sysdate
vor n Tagen
Anzahl Tage zwischen d1 und d2
aktuelles Datum
108
Formatmodelle
Y,YY,YYY,YYYY
month
mon
mm
dd
day
dy
letzte 1,2,3,4 Ziffern des Jahres
Name des Monats
Monatsname abgekürzt
Monat( 1 - 12)
Tag
Name des Tages
Name abgekürzt
to_char( sysdate, ´dd.mm.yyyy´)
02.02.2000
109
Beispiel
Wert aller Bestellungen, die in der letzten Woche eingetroffen
sind.
Select
From
Where
And
And
SUM(bp.Anzahl*a.Preis)
Artikel a, BestellPos bp, Bestellung b
a.Anr = bp.Anr
b.Bnr =bp.Bnr
b.BestDatum > sysdate - 7;
110
Einfügen in eine Relation
insert into abteilung values( 45, ´Qualität´, ´Krumpendorf´,
1777);
Abteilung(AbteilungsNr, Bez, Ort, Leiter)
create table gutkunde(Knr number, kname varchar(20));
insert into gutkunde
!
!
!
(select knr, kname
from kunde
where 20 <
(select count(*) from bestellung
where bestellung.knr =kunde.knr));
111
Löschen von Tupel
Löschen der Abteilung mit der Nummer 45
Delete from abteilung
Where AbteilungsNr = 45;
Löschen aller Tupel
Delete abteilung;
112
Löschen von Tupel(2)
DELETE [FROM] table WHERE condition
Löschen aller Kunden, die im letzten Jahr nichts bestellt haben
Delete from kunde
Where not exists
(select 1
from bestellung
where bestellung.knr = kunde.knr
and bestellung.bestdatum > sysdate-365);
113
Ändern
Name des Mitarbeiters mit der Nummer 22 auf Huber
ändern
Update
Set
Where
Mitarbeiter
Mname = ´Huber´
MNr = 22;
114
Update mit Ausdruck
UPDATE
SET
[WHERE
table [alias]
column = expr [, column = expr] ...
condition ]
Erhöhe die Preise aller Artikel in der Artikelgruppe 400 - 499
um 10 %
Update
Set
Where
Artikel
Preis = Preis * 1.1
anr between 400 and 499;
115
Update mit Abfragen
UPDATE
SET
[WHERE
table [alias]
(column [, column] ...) = query
[, (column [, column] ...) = query] ...
condition]
"Versetze alle aus Abteilung 20 nach Klagenfurt und erhöhe
ihre Punkte um 10 Prozent, aber mindestens um 5 Punkte."
Update
Set
Mitarb
AbtNr =
Where
(SELECT
AbtNr
FROM Abteilung
WHERE Ort = 'Klagenfurt'),
Punkte = GREATEST (Punkte * 1.1, Punkte + 5)
AbtNr = 20
116
unvollständige Information
Jeder feste Mitarbeiter hat eine Telefondurchwahl
!
!
!
!
Huber hat die Durchwahl 25
Maier ist freier Mitarbeiter (und hat keinen Schreibtisch im Haus)
Die Durchwahl von Berger ist nicht bekannt
Müller wurde neu eingestellt - wir wissen nicht, ob sie schon einen
Telefonanschluss erhalten hat
Name
Müller
Huber
Maier
Berger
Durchwahl
25
117
Nullwerte
Ausdrücken unvollständiger Information:
!
schlecht: nicht verwendete Werte des Wertbereichs
" Bsp.: 0, 99, blank, leere zeichenkette
!
!
!
es muß keine solchen Werte geben
„nichtverwendete“ Werte könnten Bedeutung bekommen
Probleme bei Funktionen Z.B. Durchschnittsalter wenn 0 als
Platzhalter für unbekannte Werte verwendet wird.
Nullwert:
!
spezieller Wert (kein Wert des Wertebereiches), der
ausdrückt, daß kein Wert angeben ist
118
Nullwerte (2)
Bedeutung von Nullwerten
!
unbekannter Wert
" es gibt einen Wert, aber er ist nicht bekannt
!
nicht existierender Wert
" Attribut ist nicht anwendbar
" Bsp. Durchwahl freier Mitarbeiter
!
keine Information
" unbekannt, ob Wert existiert oder nicht
" Bsp.: Durchwahl neuer Mitarbeiter
In DBMS: implizit „keine Information“
119