DB_V4 - InfoConomy

Datenbanken
Relationale Anfragesprachen (SQL)
Tobias Galliat
Sommersemester 2012
Professoren
Studenten
Vorlesungen
MatrNr
Name
Semester
226
24002
Xenokrates
18
C4
232
25403
Jonas
Kopernikus
C3
310
26120
2133
Popper
C3
52
26830
2134
Augustinus
C3
309
2136
Curie
C4
2137
Kant
C4
PersNr
Name
Rang Raum
2125
Sokrates
C4
2126
Russel
2127
VorlNr
Titel
SWS
gelesen
von
12
5001
Grundzüge
4
2137
Fichte
10
5041
Ethik
4
2125
Aristoxenos
8
5043
Erkenntnistheorie
3
2126
27550 Schopenhauer
6
5049
Mäeutik
2
2125
36
28106
Carnap
3
4052
Logik
4
2125
7
29120
Theophrastos
2
5052 Wissenschaftstheorie
3
2126
29555
Feuerbach
2
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die 3 Kritiken
4
2137
voraussetzen
hören
Vorgänger
Nachfolger
5001
5041
MatrNr
VorlNr
5001
5043
26120
5001
5001
5049
27550
5001
5041
5216
27550
4052
5043
5052
28106
5041
5041
5052
28106
5052
5052
5259
28106
5216
PerslNr
Name
Fachgebiet
Boss
28106
5259
3002
Platon
Ideenlehre
2125
29120
5001
3003
Aristoteles
Syllogistik
2125
29120
5041
3004
Wittgenstein
Sprachtheorie
2126
29120
5049
3005
Rhetikus
Planetenbewegung
2127
Keplersche Gesetze 2 2127
prüfen
Die relationale FH-DB
Assistenten
MatrNr
VorlNr
PersNr
Note
28106
5001
2126
1
25403
5041
2125
2
29555
5022
3006
Newton
27550
4630
2137
2
25403
5022
3007
Spinoza
Gott und Natur
2126
Datenmodell
DBMS basieren auf Datenmodell:
- Beschreibung der Struktur der Datenobjekte (Datenbankschema)
 Datendefinitionssprache (DDL)
- Festlegung der anwendbaren Operatoren und deren Wirkung
 Datenmanipulationssprache (DML)
 Anfragesprache (interaktiv / eingebettet)
Relationales Datenmodell  SQL als DDL, DML und Anfragesprache
3
Datendefinition in SQL
Datentypen:
• Zeichenkette (string)
- character(n)  char(n)
- character varying(n)  varchar(n)
• Zahl
- integer
- numeric(p,s)
• Datum
- date
4
Datendefinition in SQL
• Anlegen einer neuen Tabelle:
create table Professoren
(PersNr integer primary key,
Name varchar(30) not null,
Rang char(2),
Raum integer);
Zugehörige relationale Modellierung:
Professoren: {[PersNr: integer, Name: string, Rang: string, Raum: integer]}
5
Datendefinition in SQL
• Löschen einer Tabelle:
drop table Professoren;
• Hinzufügen einer Spalte zu einer bestehenden Tabelle:
alter table Professoren
add liest integer;
• Löschen einer Spalte:
alter table Professoren
drop liest;
6
Datenmanipulation in SQL
• Einfügen von Tupeln:
insert into Professoren
values (2136, ‘Curie‘, ‘C4‘, 63);
• Löschen von Tupeln:
delete Professoren
where PersNr = 2136;
• Verändern von Tupeln:
update Professoren
set Raum = 36
where PersNr = 2136;
7
Einfache Anfragen in SQL
• Selektion:
z.B. Rang = ‘C4‘ (Professoren)
select *
from Professoren
where Rang = ´C4´;
PersNr
Name
Rang
Raum
2125
Sokrates
C4
226
2126
Russel
C4
232
2136
Curie
C4
36
2137
Kant
C4
7
8
Einfache Anfragen in SQL
• Projektion mit Duplikateliminierung: z.B. Rang(Professoren)
select distinct Rang
from Professoren;
Rang
C4
C3
9
Einfache Anfragen in SQL
• Projektion ohne Duplikateliminierung:
select SWS, gelesenVon
from Vorlesungen;
SWS
gelesenVon
4
2137
4
2125
3
2126
2
2125
4
2125
3
2126
2
2126
2
2133
2
2134
4
2137
10
Einfache Anfragen in SQL
• Projektion mit Sortierung:
select PersNr, Name, Rang
from Professoren
order by Rang desc, Name asc
PersNr
Name
Rang
2136
Curie
C4
2137
Kant
C4
2126
Russel
C4
2125
Sokrates
C4
2134
Augustinus
C3
2127
Kopernikus
C3
2133
Popper
C3
11
Einfache Anfragen in SQL
• Selektion mit Projektion und Sortierung:
select PersNr, Name, Rang
from Professoren
PersNr
where Rang = ´C4´
2136
order by Name asc;
Name
Rang
Curie
C4
2137
Kant
C4
2126
Russel
C4
2125
Sokrates
C4
12
SQL-Anfragen über mehrere Tabellen
• Kartesisches Produkt mit Selektion und Projektion
select Name, Titel
from Professoren, Vorlesungen
where PersNr = gelesenVon and Titel = `Mäeutik‘ ;
Professoren
PersNr
Name
Rang Raum
2125
Sokrates
C4
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
2133
Popper
C3
52
2134
Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Vorlesungen

VorlNr
Titel
SWS
gelesenVon
5001
Grundzüge
4
2137
…
…
…
…
5049
Mäeutik
2
2125
…
…
…
…
4630
Die 3 Kritiken
4
2137
13
PersNr
2125
Name
Sokrates
Rang
C4
Raum
226
VorlNr
5001
Titel
Grundzüge
SWS gelesenVon
4
2137
1225
Sokrates
C4
226
5041
Ethik
4
2125
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
2126
2126
Russel
Russel
C4
C4
232
232
5001
5041
Grundzüge
Ethik
4
4
2137
2125
2137
Kant
C4
7
4630
Die 3 Kritiken
4
2137
Selektion
PersNr
Name
Rang
Raum
VorlNr
Titel
SWS
gelesenVon
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
Projektion
Name
Titel
Sokrates
Mäeutik
14
SQL-Anfragen über mehrere Tabellen
• Join mit Selektion und Projektion
select Name, Titel
from Professoren
join Vorlesungen
on PersNr = gelesenVon
where Titel = `Mäeutik‘ ;
auch: left outer join, right outer join, full outer join
15
SQL-Anfragen über mehrere Tabellen
• Welche Studenten hören welche Vorlesungen?
select Name, Titel
from Studenten, hören, Vorlesungen
where Studenten.MatrNr = hören.MatrNr
and hören.VorlNr = Vorlesungen.VorlNr;
oder
select s.Name, v.Titel
from Studenten s, hören h, Vorlesungen v
where s.MatrNr = h.MatrNr
and h.VorlNr = v.VorlNr
16
Datenmanipulation in SQL (Fortsetzung)
• Einfügen von Tupeln:
insert into hören
select MatrNr, VorlNr
from Studenten, Vorlesungen
where Semester = 2 and Titel= `Grundzüge‘ ;
17
Aggregatfunktionen und Gruppierung
• Aggregatfunktionen: avg, max, min, sum, count (distinct)
select max (Semester)
from Studenten;
select count(Rang)
from Professoren;
select count(distinct Rang)
from Professoren;
max(Semester)
18
count(Rang)
7
count(distinct Rang)
2
18
Aggregatfunktionen und Gruppierung
• Gruppierung
select gelesenVon, sum (SWS)
from Vorlesungen
group by gelesenVon;
gelesenVon
sum(SWS)
2137
8
2125
10
2126
8
2133
2
2134
2
 SQL erzeugt pro Gruppe ein Ergebnistupel
 deshalb müssen alle in der select-Klausel aufgeführten Attribute - außer den
aggregierten – auch in der group by-Klausel aufgeführt werden
 nur so kann SQL sicherstellen, dass sich das Attribut nicht innerhalb der
Gruppe ändert
19
Nullwerte
• ein unbekannter Attributwert wird als null gespeichert
• Nullwerte entstehen oft im Zuge der Anfrageauswertung
(z.B. bei äußeren Joins)
• manchmal sehr überraschende Anfrageergebnisse, wenn Nullwerte
beteiligt sind:
Beispiel:
select count (*)
from Studenten
where Semester < 13 or Semester > =13
 Studenten, deren Semester-Attribut den Wert null hat, werden
nicht mitgezählt!
20
Auswertung bei Nullwerten
• arithmetische Ausdrücke:
Wenn einer der Operanden null ist, wird auch das Ergebnis null.
d.h. null + 1 = null, null * 0 = null
• logische Ausdrücke:
SQL verwendet eine dreiwertige Logik: true, false, unknown
or
true
unknown
false
and
true
unknown
false
true
true
true
true
true
true
unknown
false
unknown
true
unknown unknown
unknown unknown unknown
false
false
true
unknown
false
false
false
false
false
21
Auswertung bei Nullwerten
• In einer where-Bedingung werden nur Tupel selektiert, für die
die Bedingung true ist.
• Bei einer Gruppierung wird null als eigenständiger Wert aufgefaßt
und in eine eigen Gruppe eingeordnet.
• Mit dem Befehl is (not) null kann man auf Nullwerte prüfen:
select count (*)
from Studenten
where Semester is null;
• Logische Ausdrücke lassen sich mit is unknown testen.
22
Spezielle Sprachkonstrukte
select *
from Studenten
where Semester > = 1 and Semester < = 4;
Äquivalente Formulierungen:
select *
from Studenten
where Semester between 1 and 4;
select *
from Studenten
where Semester in (1,2,3,4);
23
Spezielle Sprachkonstrukte
• Der Vergleich von Zeichenketten auf Ähnlichkeit mit like:
select *
from Studenten
where Name like `T%eophrastos‘;
Platzhalter % steht für beliebig viele (auch gar kein) Zeichen.
24
Spezielle Sprachkonstrukte
• Das case-Konstrukt für die „Dekodierung“ von Attributwerten:
select MatrNr, ( case when Note < 1.5 then ´sehr gut´
when Note < 2.5 then ´gut´
when Note < 3.5 then ´befriedigend´
when Note < 4.0 then ´ausreichend´
else ´nicht bestanden´ end)
from prüfen;
Die Alternativen (when-Klauseln) werden in der Reihenfolge ihres
Auftretens ausgewertet.
25
Geschachtelte Anfragen
• Unteranfrage in der where-Klausel
Beispiel: Welche Prüfungen sind besser als durchschnittlich
verlaufen?
select *
from prüfen
where Note < ( select avg (Note)
from prüfen );
26
Geschachtelte Anfragen
• Unteranfrage in der select-Klausel
Beispiel: Ermittlung der Lehrbelastung der Professoren
select PersNr, Name, ( select sum (SWS) as Lehrbelastung
from Vorlesungen
where gelesenVon = PersNr )
from Professoren;
Beachte: Unteranfrage ist mit der äußeren Anfrage korreliert.
 Für jedes Ergebnistupel wird die Unteranfrage ausgeführt.
27
Entschachtelung korrelierter Unteranfragen (I)
• Entschachtelung durch Join:
select p.PersNr, p.Name, v.Lehrbelastung
from Professoren p
join ( select gelesenVon, sum(SWS) as Lehrbelastung
from Vorlesungen
group by gelesenVon ) v
on p.PersNr = v.gelesenVon;
28
Quantifizierte Anfragen
• Existenzquantor:
select Name
from Professoren
where not exists ( select *
from Vorlesungen
where gelesen Von = PersNr );
Beachte: Unteranfrage ist mit der äußeren Anfrage korreliert.
29
Entschachtelung korrelierter Unteranfragen (II)
• Entschachtelung durch Verwendung des Mengenvergleichs
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
30
Sichten
Benutzer
Sicht 1
Sicht 2
Sicht 3
Relation 1
Relation 2
Relation 3
31
Sichten
• Definition einer Sicht:
create view prüfenSicht as
select MatrNr, VorlNr, PersNr
from prüfen
• Gründe für die Definition von Sichten:
- Datenschutz (z.B. kein Zugriff auf die Note der Prüfung)
- Vereinfachung von Anfragen
create view StudProf (Sname, Semester, Titel, Pname) as
select s.Name, s.Semester, v.Titel, p.Name
from Studenten s, hören h, Vorlesungen v, Professoren p
where s.Matr.Nr=h.MatrNr and h.VorlNr=v.VorlNr and v.gelesenVon=p.PersNr;
select distinct Semester from StudProf where PName=`Sokrates‘;
32
Sichten
- zur Modellierung von Generalisierungen
create table Angestellte
(PersNr integer not null,
Name varchar(30) not null);
create table ProfDaten
(PersNr integer not null,
Rang char(2),
Raum integer);
create table AssiDaten
(PersNr integer not null,
Fachgebiet varchar(30),
Boss integer);
create view Professoren as
select *
from Angestellte a, ProfDaten d
where a.PersNr = d.PersNr;
create view Assistenten as
select *
from Angestellte a, AssiDaten d
where a.PersNr = d.PersNr;
Untertypen als Sicht
33
Sichten
create table Professoren
(PersNr integer not null,
Name varchar(30) not null,
Rang char(2),
Raum integer);
create table Assistenten
(PersNr integer not null,
Name varchar(30) not null,
Fachgebiet varchar(30),
Boss integer);
create table AndereAngestellte
(PersNr integer not null,
Name varchar (30) not null);
create view Angestellte as
(select PersNr, Name
from Professoren)
union
(select PersNr, Name
from Assistenten)
union
(select *
from AndereAngestellte);
Obertyp als Sicht
34
Update von Sichten
• Sichten sind oft nicht änderbar (update-fähig):
create view VorlesungenSicht as
select Titel, SWS, Name
from Vorlesungen, Professoren
where gelesen Von=PersNr;
Folgender Befehl würde vom DBMS als nicht zulässig
zurückgewiesen:
insert into VorlesungenSicht
values (`Nihilismus‘, 2, `Nobody‘);
35
Update von Sichten
• Die folgende Sicht ist ebenfalls nicht änderbar, da sie ein
berechnetes Attribut enthält:
create view WieHartAlsPrüfer (PersNr, Durchschnittsnote) as
select PersNr, avg(Note)
from prüfen
group by PersNr;
• Sichten in SQL sind änderbar, wenn
- sie weder Aggregatfunktionen, Gruppierungen noch
Duplikatelliminierung (distinct) enthalten
- in der select-Liste nur eine Basisrelation oder veränderbare Sicht
enthalten und der Schlüssel vorhanden ist
36