% This is a sample LaTeX input file

SQL (Structured Query Language)
Relation FILME:
REGISSEUR
Fassbinder
Hitchcock
Schenk
Schenk
Hopper
Vadim
TITEL
Welt am Draht
Die Vögel
Der blaue Bock
Der blaue Bock 5.5.83
Easy Rider
Barbarella
Relation BÄNDER:
BAND
LÄNGE
1
200
1200
200
1255
110
1250
200
Relation SCHAUSP:
NAME
Jane Fonda
Klaus Kinski
Walter Lamprecht
Audrey Hephurn
Frank Zappa
Barbara Valentin
Rod Taylor
Jessica Tandy
Jack Nicholson
Robert Walker
Dennis Hopper
Peter Fonda
Relation MITW:
TITEL
Welt am Draht
Der blaue Bock 6.3.82
Die Vögel
Die Vögel
Easy Rider
Easy Rider
Easy Rider
Barbarella
ART
VHS
VHS
Bildpl
VHS
GEB
12.12.43
12.12.35
. .
. .
12.12.41
. .
. .
. .
04.05.38
04.05.35
. .
. .
NR
6
5
7
8
10
11
12
1
BAND
1200
1255
1200
1200
1250
1250
STANDORT
M-Zentrum
Röseler
M-Zentrum
Uka
NR
1
2
3
4
5
6
7
8
9
10
11
12
TEL
04131-12345
04131-12645
040-126459
040-874441
040-124441
04131-34441
04131-402722
NR
1
1
2
3
1
2
LÄNGE
90
89
93
91
95
100
Aufbau eines SQL-Befehls:
SELECT <Attribute>
FROM <Relationen>
[WHERE <Bedingung>]
Beispiel:
Zeige die Filmtitel der Relation Filme.
SELECT Titel
FROM Filme
Beispiel:
Zeige alle Filme.
SELECT *
FROM Filme
Beispiel:
Finde alle Filmtitel auf Band 1200.
SELECT *
FROM Filme
WHERE Band=1200
Beispiel:
Finde alle VHS-Bänder.
SELECT *
FROM Bänder
WHERE Art=”VHS”
Die Verknüpfung geschieht durch Angabe mehrerer Relationen in der FROM-Klausel und durch
Vergleich von Attributwerten in der WHERE-Klausel.
Beispiel:
Was bewirkt die folgende Anfrage?
SELECT *
FROM Bänder, Filme
Beispiel:
Zeige alle Filmtitel mit ihren Standorten.
SELECT Titel
FROM Bänder, Filme
WHERE Bänder.Band=Filme.Band
Datenbank definieren:
CREATE DATABASE VIDEO;
CREATE TABLE FILME( REGISSEUR CHAR(20),
TITEL CHAR(30),
BAND INTEGER,
NR INTEGER,
LÄNGE INTEGER);
CREATE TABLE BÄNDER(
BAND INTEGER,
LÄNGE INTEGER,
ART CHAR(6),
STANDORT CHAR(10));
INSERT INTO BÄNDER VALUES(1,200,“VHS“,“M-ZENTRUM“)
INSERT INTO BÄNDER VALUES(1200,200,“VHS“,“RÖSELER“)
INSERT INTO BÄNDER VALUES(1255,110,“BILDPL“,“M-ZENTRUM“)
INSERT INTO BÄNDER VALUES(1250,200,“VHS“,“UKA“)
Kompliziertere Anfragen:
Zeige Band, Filmnummer, Titel und Standort aller Filme.
SELECT filme.band,nr,titel,standort
FROM bänder, Filme
WHERE bänder.band=filme.band;
Finde alle Schauspieler des Films „Die Vögel“.
SELECT name
FROM Filme, Mitw, Schausp
WHERE filme.titel=mitw.titel
AND mitw.nr=schausp.nr
AND titel=„Die Vögel“;
Finde alle Bänder, auf denen „Jane Fonda“ zu sehen ist.
SELECT bänder.*
FROM Filme, Mitw, Schausp, Bänder
WHERE filme.titel=mitw.titel
AND mitw.nr=schausp.nr
AND name=„Jane Fonda“
AND bänder.band=filme.band;
Finde alle Schauspieler die in einem der Filme mitwirken.
SELECT schausp.*
FROM Filme, Mitw, Schausp
WHERE filme.titel=mitw.titel
AND mitw.nr=schausp.nr;
Finde alle Filme auf den Bändern 1200-1400.
SELECT filme.*
FROM Filme, Bänder
WHERE bänder.band=filme.band
AND bänder.band<1199
AND bänder.band<1401;
Finde alle Filme auf den Bändern 1200-1400.
SELECT filme.*
FROM Filme, Bänder
WHERE bänder.band=filme.band
AND bänder.band<1199
AND bänder.band<1401;
Finde alle Schauspieler, die mit Hitchcock oder Hopper gearbeitet haben.
SELECT schausp.*
FROM Filme, Mitw, Schausp
WHERE filme.titel=mitw.titel
AND mitw.nr=schausp.nr
AND (regisseur=„Hopper“ OR regisseur=„Hitchcock“);
Aggregatfunktionen und Sortierung
Funktionen: Es lassen sich sogenannte Aggregatfunktionen verwenden., und zwar COUNT, SUM,
MIN, MAX, AVG.
Beispiel: Berechne die obigen Funktionen für alle Filmlängen.
SELECT ”Länge der Filme“, COUNT(*), SUM(*), MIN(*), MAX(*),
AVG(*)
FROM Filme;
Mit DISTINCT kann man die Ausgabe von Dubletten unterdrücken:
Aufgabe: Zeige die Regisseure.
SELECT DISTINCT Regisseur
FROM filme;
Mit ORDER lassen sich die Daten sortieren.
Aufgabe: Sortiere die die Schauspieler nach Namen.
Lösung:
SELECT *
FROM schausp
ORDER BY Name;
Alias-Namen
Verknüpfungen verschiedener Dateien können sehr verwickelt werden. Noch schwieriger ist der
Fall, wenn eine Datei mit sich selbst verknüpft werden muß.
Aufgabe 10: Suche alle Filme, die auf dem Band von „Easy Rider“ stehen.
Ein erster Lösungsansatz könnte so aussehen:
SELECT Filme.*
FROM filme
WHERE titel="Easy Rider"
AND band=band.
Dies geht nicht, da in der Bedingung band=band nicht klar ist, welche Datensätze miteinander
verglichen werden. Auch eine Qualifizierung nützt nichts. Man kann die Attribute ja nur durch
dieselbe Dateiangabe qualifizieren.
Die Lösung besteht in der Verwendung von Aliasnamen. Man muß die Datei Filme zweimal
öffnen, und zwar unter zwei verschiedenen Namen. Dann werden unter dem ersten Namen der
Datensatz mit „Easy Rider“ gesucht und unter dem zweiten Namen die Datensätze auf dem
gleichen Band.
SELECT f2.*
FROM filme f1, filme f2
WHERE f1.titel="Easy Rider"
AND f2.band=f1.band;
Finde alle Schauspieler, die in den gleichen Filmen wie Jane Fonda mitgespielt haben.
SELECT s2.*
FROM schausp s1, schausp s2, mitw m1, mitw m2, filme
WHERE s1.name=”Jane Fonda”
AND s1.nr=m1.nr
AND m1.titel=filme.titel
AND filme.titel=m2.titel
AND m2.nr=s2.nr;
Unterabfragen
Viele Abfragen lassen sich besser durch verschachtelte Abfragen formulieren. Die Daten einer
untergeordneten Abfrage werden als Menge behandelt. Auf diese Menge kann von der
übergeordneten Abfrage zugegriffen werden.
Als Relationenschemata dienen
1) ANGEST(ANGNR, NAME, BERUF,GEHALT)
2) PROJEKT(PNR, PROJNAME, PLEITER)
3) ANG-PRO(PNR, ANGNR, PROZARB)
4) ABTEIL(ABTNR, ABTLEITER)
5) ABT-ANG(ABTNR, ANGNR)
Beispiel: Suche die Daten des Leiters des Katalogprojekts.
Lösung:
SELECT *
FROM ANGEST
WHERE ANGNR IN
(SELECT PLEITER
FROM PROJEKT
WHERE PROJNAME="Katalog").
Aufgabe 12: Suche in jeder Berufsgruppe den meistverdienenden Mitarbeiter.
Lösung:
SELECT *
FROM ANGEST A1
WHERE GEHALT>=
(SELECT MAX(GEHALT)
FROM ANGEST A2
WHERE A1.BERUF=A2.BERUF).
Aufgaben
Aufgabe 13: Finde die Namen aller Angestellten, die am Projekt 89 mitarbeiten.
Lösung:
SELECT NAME
FROM ANGEST
WHERE ANGNR IN
(SELECT ANGNR
FROM ANGPRO
WHERE.PNR=89).
Aufgabe 14: Finde alle Angestellten, die an mindestens einem Projekt mitarbeiten.
Lösung:
SELECT *
FROM ANGEST
WHERE ANGNR IN
(SELECT ANGNR
FROM ANGPRO).
Aufgabe 15: Finde die Namen aller Angestellten, die an mindestens 2 Projekten mitarbeiten.
Lösung:
SELECT NAME
FROM ANGEST
WHERE 1<
(SELECT COUNT(*)
FROM ANGPRO
WHERE.ANGEST.ANGNR=ANGPRO.ANGNR).
Aufgabe 16: Finde Namen und Beruf aller Projektleiter.
Lösung:
SELECT NAME, BERUF
FROM ANGEST
WHERE ANGNR IN
(SELECT PLEITER
FROM PROJEKT).
Aufgabe 17: Finde die Daten aller Angestellten, die bei keinem Projekte mitarbeiten.
Lösung:
SELECT *
FROM ANGEST
WHERE NOT ANGNR IN
(SELECT ANGNR
FROM ANGPRO).
Aufgabe 18: Finde die Daten aller Angestellten, die insgesamt zu 100% an Projekten beteiligt sind
(eventuell an mehreren).
Lösung:
SELECT *
FROM ANGEST
WHERE 100=
(SELECT SUM(PROZARB)
FROM ANGPRO
WHERE.ANGPRO.ANGNR=ANGEST.ANGNR).
Gruppierungen
Oft interessieren nicht alle Daten, sondern nur gewisse Klassen oder Gruppen. Mit GROUP BY
kann man Datensätze mit gleichem Feldinhalt zusammenfassen.
Beispiel: Finde alle Berufe, die Anzahl der Angestellten in dem Beruf und ihr durchschnittliches
Gehalt.
SELECT BERUF,COUNT(*),AVG(GEHALT)
FROM ANGEST
GROUP BY BERUF.
Wenn man das Ergebnis nach dem Durchschnittsgehalt sortieren möchte, sieht die Abfrage so aus:
SELECT BERUF,COUNT(*),AVG(GEHALT)
FROM ANGEST
GROUP BY BERUF
ORDER BY 3.
Aufgabe 19: Finde alle Wohnorte, sortiere sie und gebe zusätzlich die Summe aller Gehälter der
Mitarbeiter aus.
Lösung:
SELECT WOHNORT, SUM(GEHALT)
FROM ANGEST
GROUP BY WOHNORT
ORDER BY WOHNORT).
Beispiel: Finde alle Abteilungen und den Durchschnittsverdienst ihrer Mitarbeiter.
SELECT abtnr, avg(gehalt)
FROM ANGEst, abtang
where angest.angnr=abtang.angnr
group by abtnr.
Sichten
Man kann in SQL Sichten (Views) definieren. Mit den Sichten kann man klar festlegen, welche
Benutzer welche Daten in welcher Form sehen sollen.
Beispiel: Definiere eine Sicht auf ANGEST, in der nur Name, Beruf und Wohnort aufgeführt
werden.
CREATE VIEW ÜBERSICHT
(NACHNAME, BERUF, ORT)
AS
SELECT NAME, BERUF, WOHNORT
FROM ANGEST.
Diese Sicht wird nicht als Tabelle abgespeichert, sondern wird immer dann temporär erzeugt, wenn
sie gebraucht wird. Deshalb sind die Daten auch immer aktuell. Die Sicht kann wie jede andere
Tabelle benutzt werden.
Beispiel: Definiere eine Übersicht der Abteilungen mit Abteilungsname und den Daten der
Angestellten ohne Angestelltennummer.
CREATE VIEW ABTLANG
(ABT, NAME, BERUF, GEHALT, ORT)
AS
SELECT ABTNAME, NAME, BERUF, GEHALT, WOHNORT
FROM ANGEST, ABTANG, ABTEIL
WHERE ANGEST.ANGNR=ABTANG.ANGNR
AND ABTANG.ABTNR=ABTEIL.ABTNR.
Aufgabe 20: Definiere eine Sicht ABTKURZ, die alle Spalten von ABTLANG enthält außer der
Spalte GEHALT. Definiere hieraus eine Sicht auf die Abteilung Grafik.
Lösung:
CREATE VIEW ABTKURZ
(ABT, NAME, BERUF, ORT)
AS
SELECT ABT, NAME, BERUF, ORT
FROM ABTLANG.
CREATE VIEW GRAFIK
(ABT, NAME, BERUF, ORT)
AS
SELECT ABT, NAME, BERUF, ORT
FROM ABTKURZ
WHERE ABT="Grafik".
Finde alle Schauspieler die in keinem der Filme mitwirken.
SELECT schausp.*
FROM Filme, Mitw, Schausp
WHERE filme.titel=mitw.titel
AND mitw.nr=schausp.nr;
SELECT schausp.*
FROM schausp, mitw
WHERE schausp.nr=mitw.nr
AND titel IN
(SELECT filme.titel
FROM Filme
WHERE Länge>=
(SELECT MAX(f1.Länge)
FROM Filme f1))
Normalisierung
Schlüssel
Eine Menge von Attributen, die einen Datensatz eindeutig identifiziert, wird als Schlüsselkandidat
bezeichnet. Ein Schlüsselkandidat ist minimal, wenn bei Wegnahme eines Attributes eine
eindeutige Identifizierung des Datensatzes nicht mehr möglich ist. Ein minimaler Schlüsselkandidat
wird als Schlüssel bezeichnet. Ein Identifikationsschlüssel, der nicht geändert werden darf, sondern
während der gesamten Lebenszeit eines Objekts existiert, heißt Primärschlüssel.
Jede Attributkombination, die eine Teilmenge der Datensätze bestimmt, wird als Sekundärschlüssel
bezeichnet.
Das Entwurfsproblem
Gegeben sei die Relation Doz(Veranstnr, Name, TelNr, KlausurTermin), wobei Veranstnr der
Primärschlüssel sei. Diese Relation weist einige Schwächen auf:
• Was geschieht, wenn ein neuer Dozent an die Hochschule kommt und noch keine
Veranstaltungen gibt? (Insertion Anomalie)
• Was geschieht, wenn ein Dozent eine Veranstaltung ausfallen läßt? (Deletion Anomalie)
• Welche Probleme treten bei Änderung der Telefonnummer eines Dozenten auf? (Update
Anomalie)
Übung: Wie lassen sich die geschilderten Probleme beseitigen? Welche Nachteile hat diese
Lösung?
Abhängigkeiten
Funktionale Abhängigkeit
Das Attribut bzw. die Attributkombination B ist funktional abhängig vom Attribut bzw. von der
Attributkombination A derselben Relation R, wenn zu einem bestimmten Wert von A höchsten ein
Wert von B möglich ist.
Schreibweise: A → B
Volle funktionale Abhängigigkeit
Seien A und B Attribute oder Attributkombinationen der gleichen Relation R.
B ist genau dann voll funktional abhängig von A, wenn B von der Attributkombination A
funktional abhängig ist (A → B), aber nicht von Teilen der Attributkombination A.
Schreibweise: A ⇒ B
Übung: Welche Abhängigkeiten gibt es in Doz?
Transitiv abhängig
X und Y seien Attribute oder Attributkombinationen von R und A sei ein Nichtschlüsselattribut von
R, das nicht in Y vorkommt.
A ist transitiv abhängig von X, falls
X → Y gilt,
Y → A gilt,
Y → X gilt nicht.
Normalisierung
1. Normalform
Eine Relation befindet sich in der 1. Normalform, wenn ihre Attribute nur einfache Attributswerte
aufweisen.
2. Normalform
Eine Relation ist in der 2. Normalform, wenn sie in der 1. Normalform ist und jedes
Nichtschlüsselattribut voll abhängig von jedem Schlüssel von R ist.
3. Normalform
Eine Relation ist in der 3. Normalform, wenn. es keinen Schlüssel X und kein
Nichtschlüsselattribut A gibt, so daß A transitiv von X abhängig ist.
Beispiel1
Gegeben sei die Relation Liefer(Lieferant, Adresse, Teil, Preis). Der Schlüssel ist {Lieferant, Teil}.
Es gibt die Abhängigkeiten
Lieferant → Adresse
{Lieferant, Teil} → Preis.
Daß die Relation "pathologisch" ist, wird dadurch angezeigt, daß sie nicht in 2. Normalform ist.
Denn es gilt Lieferant → Adresse. Das Nichtschlüsselattribut Adresse ist also von einer echten
Teilmenge des Schlüssels {Lieferant, Teil} abhängig, also nicht voll abhängig von {Lieferant,
Teil}.
Beispiel2
Die Relation Doz(Veranstnr, Name, TelNr, KlausurTermin) ist ebenfalls pathologisch, aber
trotzdem in 2. Normalform. Veranstnr ist der einzige Schlüssel. Alle anderen Attribute hängen voll
funktional von diesem Schlüssel ab. Aber die Relation ist nicht in 3. Normalform. Denn es gilt
Veranstnr → Name und Name → TelNr,
aber nicht Name → Veranstnr.
Übung:
Sei folgende Relation gegeben:
Proj-Angest(Projnr, Angestnr, Projname, Prozarbeit, PLZ, Wohnort).
Ist die Relation in Normalform? Falls nicht, wandeln Sie sie zuerst in 2. und dann in 3. Normalform
um.
Aufgabe
Gegeben sei die Relation VHS in der 1. Normalform. Normalisieren Sie diese Relation, d.h.
überführen Sie sie in die 2. und in die 3. Normalform.
Erläutern Sie die Umwandlungen und geben Sie die entsprechenden Definitionen an.
VHS
TNNr.
101
101
103
103
103
117
117
TNName
Artin
Artin
Braun
Braun
Braun
Behnen
Behnen
KNr
2001
3014
2001
4217
3014
2001
4305
KTitel
KLNr
MS-DOS - Ein Betriebssystem? 63
Seidenmalerei
63
MS-DOS - Ein Betriebssystem? 63
Marx für Betriebswirte
58
Seidenmalerei
63
MS-DOS - Ein Betriebssystem? 63
Englisch 1
58
TN=Teilnehmer, K=Kurs, KL=Kursleiter
KLName
Zuse
Zuse
Zuse
Bergmann
Zuse
Zuse
Bergmann
Aufgabe
Gegeben sei die Relation "Universität" in der 1. Normalform. Normalisieren Sie diese Relation,
d.h. überführen Sie sie in die 2. und in die 3. Normalform.
(Hinweis:
Gehen Sie davon aus, daß jeder Dozentennachnahme nur maximal einmal existiert,
mithin als Primärschlüssel geeignet ist.)
(8 Punkte)
Erläutern Sie die Umwandlungen und geben Sie die entsprechenden Definitionen an.
(12 Punkte)
Universität
Matrnr. StudentIn Veranstnr
880720
Neumann
7001
880720
Neumann
8801
850850
Aurin
7001
850850
Aurin
4217
850850
Aurin
8801
800800
Werner
7001
800800
Werner
4305
Veranst.-Titel
Informatik 1
Datenbanksysteme
Informatik 1
Marx für Betriebswirte
Datenbanksysteme
Informatik 1
Englisch für Betriebswirte
Dozent
Szyszka
Szyszka
Szyszka
Klops
Szyszka
Szyszka
Klops
Tel (Doz)
333
333
333
444
333
333
444
Aufgabe
Gegeben sei die folgende Relation Personal-Projekt in der ersten Normalform zur Darstellung des
Personaleinsatzes in verschiedenen Projekten.
Personal-Projekt
P#
101
101
102
103
103
103
104
104
P-Nm
Hans
Hans
Rolf
Urs
Urs
Urs
Paul
Paul
Dabei bedeuten:
P#
P-Nm
Abt#
Abt-Nm
Pjt#
Pjt-Nm
P-Pjt-Zeit
Abt#
1
1
2
2
2
2
1
1
Abt-Nm
Physik
Physik
Chemie
Chemie
Chemie
Chemie
Physik
Physik
Pjt#
11
12
13
11
12
13
11
13
Pjt-Nm
A
B
C
A
B
C
A
C
P-Pjt-Zeit
60
40
100
20
50
30
80
20
Personalnummer
Personalname
Abteilungsnummer
Abteilungsname
Projektnummer
Projektname
prozentualer Arbeitszeitanteil
Normalisieren Sie diese Relation, indem Sie sie in die zweite und die dritte Normalform überführen
und geben Sie die jeweiligen Definitionen an. Markieren Sie in allen Relationen die
Primärschlüssel.
(15 Punkte)
Geben Sie die Normalisierung in relationaler Algebra an, d.h. erzeugen Sie die neuen Relationen,
die sich im Zuge der Normalisierung ergeben, aus den alten Relationen mithilfe relationaler
Verknüpfungen.
(5 Punkte)
Aufgabe
Gegeben sei die Relation Verkäufe in der 1. Normalform.
In der Relation wird gespeichert, welche Vertreter wann und an wen welche Ware in welcher
Menge verkaufen. Es gibt dabei keine Beschränkungen. Theoretisch können mehrere Vertreter am
gleichen Tag an den gleichen Kunden verkaufen, auch wenn dann die Geduld des Letzteren etwas
überbeansprucht werden dürfte. Außerdem wird festgehalten, wer der Abteilungsleiter des
Vertreters ist und zu welcher Filiale die Abteilung gehört und wieviel die Ware kostet. (Die
Abteilungen verschiedener Filialen haben immer unterschiedliche Nummern.)
Normalisieren Sie diese Relation, d.h. überführen Sie sie in die 2. und in die 3. Normalform.
(12 Punkte)
Erläutern Sie die Umwandlungen und geben Sie die entsprechenden Definitionen an.
(8 Punkte)