Erweiterung DML (Outer Joins)

-10- Arbeitsunterlagen
DVT LK13.1 2014/2015
Erweiterung SQL-Sprachumfang
Lehrkraft:
__________________
Kurs:
0
__________________
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Arbeitsaufträge
Aufgabe I: Ordnen Sie sich einer Stammgruppe von 3 Personen zu.
Die Mitglieder (3 Personen) meiner Stammgruppe sind:
_____________________________
_____________________________
_____________________________
Aufgabe II: Auswahl des Themas
Entscheiden Sie sich in Ihrer Stammgruppe für ein Thema aus jedem Themenblock und kreuzen Sie Ihr Thema an! Jedes Thema muss mindestens einmal gewählt sein.
Mein Thema
Expertenthema

A
Erweiterung DML (Outer Joins)

B
Erweiterung DML (Insert, Update, Delete)

C
Grundlagen DDL (Create, Drop, Semantische Integrität)
Aufgabe III: Wechsel in die Expertengruppe
Gehen Sie nun gemäß Ihres gewählten Themas aus dem Themenblock in Ihre
Expertengruppe. Lesen Sie dann ihren Infotext und füllen Sie ihre Notierhilfe aus.
Klären Sie offene Fragen mit den anderen Experten oder der Lehrkraft.
Aufgabe IV: Dreiergespräch in der Stammgruppe
Wechseln Sie zurück in ihre Stammgruppe und tauschen Sie die Informationen der Infotexte
im Dreiergespräch aus.
Aufgabe V: Bearbeiten der Aufgaben
Bearbeiten Sie die Aufgaben am Ende der Materialien in Einzelarbeit.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Infotext Expertenthema A:
Erweiterung DML (Outer Joins)
Sie haben im SQL-Tutorial bereits Abfragen mit dem JOIN-Befehl kennen gelernt, mit dem die
Datensätze mehrerer Tabellen verknüpft werden können. Dazu wird jede in Betracht kommende Tabelle in einer JOIN-Klausel angeführt, der ON-Parameter enthält die Verknüpfungsbedingung.
Wir betrachten im Folgenden eine kleine Datenbank, bestehend aus zwei Relationen:
Mitarbeiter (MNr, Name, Vorname)
Dienstwagen (DNr, Kennzeichen, MNr#)
Sie sehen, dass eine 1:1-Beziehung zwischen Mitarbeitern und Dienstwagen umgesetzt ist,
d.h. ein Mitarbeiter fährt einen oder keinen Dienstwagen.
Um nun alle Mitarbeiter mit Dienstwagen auszugeben, führen wir folgende Abfrage aus:
SELECT *
FROM Mitarbeiter
JOIN Dienstwagen ON Mitarbeiter.MNr = Dienstwagen.MNr
Wir erhalten als Ausgabe:
MNr
-------30001
40001
50001
50002
50003
50004
60001
NAME
-----------Wagner
Langmann
Pohl
Braun
Polovic
Kalman
Aagenau
VORNAME
--------Gaby
Matthias
Helmut
Christian
Frantisek
Aydin
Karolin
DNr
--3
4
5
14
15
16
6
KENNZEICHEN
----------DO-WB 423
DO-WB 424
DO-WB 425
DO-WB 352
DO-WB 353
DO-WB 354
DO-WB 426
Beachten Sie, dass sowohl Mitarbeiter ohne
Dienstwagen, als auch Dienstwagen ohne fahrenden Mitarbeiter unberücksichtigt geblieben sind. Es
wurden
nur
die
Kombinationen
Mitarbeiter/Dienstwagen berücksichtigt, auf welche die Verknüpfungsbedingung hinter ON zutrifft.
Stellen wir die Menge der Datensätze in den beiden
Relationen als Kreise dar, ergibt sich folgendes Bild:
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Der markierte Bereich stellt Datensätze aus beiden Relationen dar, welche die Verknüpfungsbedingung erfüllen. Die JOIN-Abfrage aus dem Beispiel ist ein Beispiel für den sogenannten
INNER-JOIN. Es gibt noch andere Arten von JOIN-Abfragen, welche als OUTER-JOINS bezeichnet werden. Man unterscheidet zwischen LEFT-OUTER-, RIGHT-OUTER und FULLOUTER-JOINS.
LEFT-OUTER-JOIN
Bei einem LEFT-OUTER-JOIN werden aus der "linken" (erstgenannten) Relation auch Datensätze berücksichtigt, welche die Verknüpfungsbedingung hinter ON nicht erfüllen. Betrachten wir hierzu die folgende Abfrage:
SELECT *
FROM Mitarbeiter
LEFT JOIN Dienstwagen ON Mitarbeiter.MNr = Dienstwagen.MNr
Wir erhalten als Ausgabe:
MNr
-------30001
30002
40001
40002
50001
50002
50003
50004
60001
60002
NAME
-----------Wagner
Feyerabend
Langmann
Peters
Pohl
Braun
Polovic
Kalman
Aagenau
Pinkart
VORNAME
--------Gaby
Werner
Matthias
Michael
Helmut
Christian
Frantisek
Aydin
Karolin
Petra
DNr
--3
4
5
14
15
16
6
KENNZEICHEN
----------DO-WB 423
DO-WB 424
DO-WB
DO-WB
DO-WB
DO-WB
DO-WB
425
352
353
354
426
Beachten Sie, dass nun auch Mitarbeiter berücksichtigt wurden, welche keinen Dienstwagen fahren. Unabhängig von der Verknüpfungsbedingung
werden also immer alle Mitarbeiter aufgelistet. Im
Bild links sind die berücksichtigten Datensätze
graphisch dargestellt.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
RIGHT-OUTER-JOIN
Bei einem RIGHT-OUTER-JOIN werden aus der "rechten" (zweitgenannten) Relation auch
Datensätze berücksichtigt, welche die Verknüpfungsbedingung hinter ON nicht erfüllen. Betrachten wir hierzu die folgende Abfrage:
SELECT *
FROM Mitarbeiter
RIGHT JOIN Dienstwagen ON Mitarbeiter.MNr = Dienstwagen.MNr
Wir erhalten als Ausgabe:
MNr
-------30001
40001
50001
NAME
-----------Wagner
Langmann
Pohl
VORNAME
--------Gaby
Matthias
Helmut
50002
50003
50004
60001
Braun
Polovic
Kalman
Aagenau
Christian
Frantisek
Aydin
Karolin
DNr
--3
4
5
8
14
15
16
6
KENNZEICHEN
----------DO-WB 423
DO-WB 424
DO-WB 425
DO-WB 422
DO-WB 352
DO-WB 353
DO-WB 354
DO-WB 426
Beachten Sie, dass nun auch Dienstwagen berücksichtigt wurden, welche von keinem Mitarbeiter gefahren werden. Unabhängig von der Verknüpfungsbedingung werden also immer alle Dienstwagen
aufgelistet. Im Bild rechts sind die berücksichtigten
Datensätze graphisch dargestellt.
Bemerkung: Machen Sie sich klar, dass die Abfrage A LEFT JOIN B dasselbe Ergebnis liefert wie die
Abfrage B RIGHT JOIN A.
FULL-OUTER-JOIN
Der FULL-OUTER-JOIN ergibt sich nun als Kombination von LEFT- und RIGHT-OUTERJOIN. Es werden also unabhängig von der Verknüpfungsbedingung alle Datensätze aus beiden Relationen ausgegeben.
Betrachten wir hierzu die folgende Abfrage:
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
SELECT *
FROM Mitarbeiter
FULL JOIN Dienstwagen ON Mitarbeiter.MNr = Dienstwagen.MNr
Wir erhalten als Ausgabe:
MNr
-------30001
30002
40001
40002
50001
NAME
-----------Wagner
Feyerabend
Langmann
Peters
Pohl
VORNAME
--------Gaby
Werner
Matthias
Michael
Helmut
50002
50003
50004
60001
60002
Braun
Polovic
Kalman
Aagenau
Pinkart
Christian
Frantisek
Aydin
Karolin
Petra
DNr
--3
4
5
8
14
15
16
6
KENNZEICHEN
----------DO-WB 423
DO-WB 424
DO-WB
DO-WB
DO-WB
DO-WB
DO-WB
DO-WB
425
422
352
353
354
426
Obwohl alle Datensätze von beiden Relationen
berücksichtigt werden, macht die Angabe einer
Verknüpfungsbedingung Sinn. Erst durch die Verknüpfungsbedingung werden zusammengehörige
Datensätze in einer Zeile ausgegeben.
Zusammenfassung JOIN-Abfragen
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Notierhilfe Expertenthema A:
Erweiterung DML (Outer Joins)
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Infotext Expertenthema B:
Erweiterung DML (Insert, Update, Delete)
Datenbanksprachen für relationale Datenbanken wie z.B. SQL können in drei Kategorien eingeteilt werden:
Data Manipulation Language (DML)
Untersprache für das Abfragen, Einfügen,
Ändern oder Löschen von Daten
Data Definition Language (DDL)
Untersprache für das Anlegen, Ändern und
Löschen von Datenstrukturen
Data Control Language (DCL)
Untersprache für die Zugriffskontrolle
Bisher haben sie im Unterricht nur Teile der DML kennen gelernt. Sie werden nun weitere Teile der DML kennen lernen. Die Experten des Themas C werden ihnen dann einen Einblick in
die DDL geben.
Zur Bearbeitung von Daten gibt es in SQL die Befehle: INSERT, UPDATE und DELETE. Wie
die Bezeichnungen vermuten lassen, können mit diesen Befehlen Datensätze eingefügt, verändert und gelöscht werden. Beachten Sie, dass diese Befehle nur Datensätze (Zeilen der
Tabelle) verändern, die Relationenschemata (Spalten der Tabelle) aber unverändert bleiben.
Grundlage für die folgenden Erläuterungen ist die Relation Mitarbeiter:
Mitarbeiter (MNr., Nachname, Vorname, Adresse, PLZ, Ort, Gehalt)
INSERT
Variante 1:
INSERT INTO Tabelle
VALUES (Wert_1, Wert_2, ..., Wert_n)
Variante 2:
INSERT INTO Tabelle (Attribut_1, Attribut_2, ...)
VALUES (Wert_1, Wert_2, ...)
Mit der ersten Variante wird ein vollständiger Datensatz angelegt, d.h. alle Attribute werden
mit einem Wert belegt. Bei der zweiten Variante können spezifische Attribute angegeben wer-
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
den, welche mit Werten belegt werden sollen. Alle anderen Attribute werden dann mit einem
null-Wert belegt.
Beachten Sie, dass viele Schlüsselattribute den Datentyp AUTO_INCREMENT besitzen, d.h.
beim Anlegen eines Datensatzes wird dieses Attribut automatisch mit einem eindeutigen (fortlaufenden) Wert belegt. In diesem Fall muss dieses Attribut beim INSERT-Befehl nicht mit
angegeben werden.
Beispiel:
INSERT INTO Mitarbeiter VALUES ('Schmidt', 'Bernd', 'Sandstr. 5', 68519, 'Viernheim', 2300)
Fügt einen vollständigen Datensatz für den Mitarbeiter Bernd Schmidt ein.
INSERT INTO Mitarbeiter (Nachname, Vorname, Adresse, PLZ, Ort)
VALUES ('Schmidt', 'Bernd', 'Sandstr. 5', 68519, 'Viernheim')
Fügt den Mitarbeiter Bernd Schmidt ein. Ein Gehalt ist nicht vorgesehen (null).
UPDATE
UPDATE Tabelle
SET Attribut_1=Wert_1, Attribut_2=Wert_2,...
WHERE Bedingung
Die WHERE-Bedingung gibt an, welche Datensätze von der Änderung betroffen sind. Fehlt
die WHERE-Bedingung, werden alle Datensätze der Tabelle verändert.
Beispiel:
Die Mitarbeiterin Paula Singemeier hat geheiratet. Ihr Nachname soll in Müller verändert werden. Eine gefährliche SQL-Anweisung lautet wie folgt:
UPDATE Mitarbeiter
SET Nachname='Müller'
WHERE Vorname='Paula' AND Nachname = 'Singemeier'
Diese SQL-Anweisung kann zu ungewollten Veränderungen führen, da alle Mitarbeiter mit
dem Namen Paula Singemeier abgeändert werden. Soll nur ein spezieller Datensatz verändert werden, ist die Auswahl über den Primärschlüssel sinnvoll.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
UPDATE Mitarbeiter
SET Nachname='Müller'
WHERE MNr=34
Der neue Wert eines Attributes kann sich auch aus dem aktuellen Attributwert zusammensetzen.
Beispiel:
Alle Mitarbeiter sollen eine Gehaltserhöhung von 5% erhalten.
UPDATE Mitarbeiter
SET Gehalt = Gehalt * 1.05
DELETE
DELETE FROM Tabelle
WHERE Bedingung
Die WHERE-Bedingung gibt an, welche Datensätze gelöscht werden sollen. Fehlt die
WHERE-Bedingung, werden alle Datensätze der Tabelle gelöscht.
Beispiel:
Die Mitarbeiterin Paula Singemeier (MNr 34) verlässt die Firma.
DELETE FROM Mitarbeiter
WHERE MNr=34
Alle Mitarbeiter sollen gelöscht werden.
DELETE FROM Mitarbeiter
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Notierhilfe Expertenthema B:
Erweiterung DML (Insert, Update, Delete)
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Infotext Expertenthema C:
Grundlagen DDL (Create, Drop, Semantische Integrität)
(Der Experte des Themas B wird ihnen die Abkürzung DDL erläutern.)
Der wohl gefährlichste SQL-Befehl ist der Befehl DROP. Dieser löscht nicht nur alle Datensätze einer Tabelle, sondern löscht auch die Tabelle selbst (d.h. das Tabellenschema).
DROP Tabelle
Bsp.: Der folgende Befehl löscht die Tabelle Mitarbeiter.
DROP Mitarbeiter
Um Tabellen anzulegen, wird der CREATE-Befehl verwendet.
CREATE TABLE Tabellenname (
Attribut_1 Datentyp_1 [PRIMARY KEY | FOREIGN KEY],
Attribut_2 Datentyp_2 [PRIMARY KEY | FOREIGN KEY],
….
)
In SQL stehen folgende Datentypen zur Verfügung:
SQL – Datentyp
CHAR(n)
VARCHAR(n)
INT
DOUBLE
FLOAT
BLOB
TIMESTAMP
DATE
TIME
Beschreibung
Zeichenkette fester Länge mit genau n Zeichen
Zeichenkette variabler Länge mit maximal n Zeichen
Ganzzahlen
Gleitkommadarstellung
Gleitkommadarstellung
Für Binärdateien wie z.B. Bilder
Datum + Uhrzeit
Datum
Uhrzeit
Die optionale Angabe PRIMARY KEY gibt an, dass es sich bei dem Attribut um ein Schlüsselattribut handelt. Die Angabe FOREIGN KEY gibt an, dass es sich um einen Fremdschlüssel
handelt.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Bsp.: Der folgende Befehl legt eine Tabelle für die Mitarbeiterverwaltung an.
CREATE TABLE Mitarbeiter (
MNr INT PRIMARY KEY,
Nachname VARCHAR(40),
Vorname VARCHAR(30),
Adresse VARCHAR(40),
PLZ INT,
AbtNr INT FOREIGN KEY REFERENCES Abteilung(AbtNr)
Ort VARCHAR(30),
Gehalt DOUBLE
)
Hinter REFERENCES wird angegeben, mit welchem Primärschlüssel (AbtNr) in welcher Tabelle (Abteilung) der Fremdschlüssel verknüpft sein soll.
Referentielle Integrität
Um Inkonsistenz in Datensätzen zu vermeiden, werden sogenannte Integritätsbedingungen formuliert. Die referentielle Integrität besagt, dass Attributwerte eines Fremdschlüssels
auch als Attributwert des zugehörigen Primärschlüssels vorhanden sein müssen.
Betrachten wir als Beispiel die beiden Relationen
Mitarbeiter (MNr, Vorname, Nachname, AbtNr#, Gehalt)
Abteilung (AbtNr, AbtName, AnzahlMitarbeiter)
Es ist einsichtig, dass es sinnvoll ist zu fordern, dass bei den Mitarbeitern nur tatsächlich existierende Abteilungen eingetragen werden sollen, d.h. der Fremdschlüssel eines jeden Mitarbeiterdatensatzes muss einen entsprechenden Datensatz in der Tabelle Abteilung mit gleichem Primärschlüssel besitzen. In einer SQL-Datenbank wird die referentielle Integrität automatisch sichergestellt, wenn beim Erzeugen einer Tabelle mit CREATE die Fremdschlüssel
durch Angabe des Zusatzes FOREIGN KEY markiert werden. Wird ein Datensatz in einer solchen Tabelle eingefügt, prüft die SQL-Datenbank automatisch, ob der Wert des Fremdschlüssels als Wert des gleichnamigen Primärschlüssels der verknüpften Tabelle vorhanden ist. Im
negativen Fall wird eine Fehlermeldung ausgegeben.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Semantische Integrität
Die semantische Integrität besagt, dass die Eingaben der Benutzer auf Richtigkeit geprüft
werden. Typische semantische Integritätsbedingungen sind:




Im Attribut Wochentag sollen nur die Werte „Montag, Dienstag, …“ eingetragen werden.
Im Attribut Geschlecht sollen nur die Werte „w“ und „m“ eingetragen werden.
Die Gehälter dürfen nicht sinken.
Die Preise dürfen um nicht mehr als 3,5% steigen.
In SQL werden Integritätsbedingungen mit sogenannten constraints umgesetzt.
constraint
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
Beschreibung
Gibt an, dass das Attribut keinen NULL-Wert speichern darf.
Gibt an, dass alle Attributwerte in dieser Tabellenzeile unterschiedlich sein
müssen.
Gibt an, dass es sich um einen Primärschlüssel handelt.
Gibt an, dass es sich um einen Fremdschlüssel handelt.
Gibt an, welche Bedingung der Wert dieses Attributes erfüllen muss.
Gibt an, welcher Standardwert verwendet wird, wenn der Benutzer keinen
anderen Attributwert angibt.
Die beiden constraints PRIMARY KEY und FOREIGN KEY kennen sie schon. Die Angabe
PRIMARY KEY ist dabei eine Kombination von NOT NULL und UNIQUE. Für die anderen
constraints wird nun ein Beispiel angegeben:
CREATE Mitarbeiter (
MNr INT PRIMARY KEY,
Vorname VARCHAR(30) NOT NULL,
Nachname VARCHAR(30) NOT NULL,
Spitzname VARCHAR(30) UNIQUE,
Ort VARCHAR(30) DEFAULT ‘Bensheim’
Geschlecht CHAR CHECK (Geschlecht=‘m’ OR Geschlecht=’w’)
AbtNr INT FOREIGN KEY REFERENCES Abteilung(AbtNr),
Gehalt DOUBLE CHECK (Gehalt > 0) )
In dieser Datenbank wird sichergestellt, dass jeder Mitarbeiter Vor- und Nachnamen besitzen
muss. Die Angabe eines Spitznamens ist optional, muss aber eindeutig sein. Wird kein Wohnort angegeben, wird dieser auf den Standardwert Bensheim gesetzt. Das Geschlecht darf nur
die Werte „m“ oder „w“ annehmen. Das Gehalt muss ein positiver Wert größer Null sein. Durch
diese Bedingungen wird die semantische Integrität der Datensätze sichergestellt. Durch Angabe eines Primär- und Fremdschlüssels wird auch die referentielle Integrität gewährleistet.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Notierhilfe Expertenthema C:
Grundlagen DDL (Create, Drop, Semantische Integrität)
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
Aufgaben
Grundlage der folgenden Aufgaben sind die Relationen einer Kursverwaltung. Dabei werden
die Kursteilnehmer und die Kurse erfasst. Jeder Kursteilnehmer darf in maximal einem Kurs
teilnehmen. Ein Kurs darf dagegen beliebig viele Kursteilnehmer haben. Die Relationen im
Einzelnen:
Kursteilnehmer (KTNr, KTName, KTVorname, KTAdresse, KNr#)
Kurse (KNr, KName, KBeschreibung)
Erstellen Sie folgende SQL-Abfragen / SQL-Anweisungen:
1. Geben Sie alle Kursteilnehmer mit Name und Vorname aus, die den Kurs "Datenbanken" belegen.
2. Geben Sie alle Kursteilnehmer mit Name und Vorname aus, die gerade einen Kurs belegen. Der Kursname soll mit ausgegeben werden.
3. Geben Sie alle Kursteilnehmer mit Name und Vorname aus. Bei Kursteilnehmern, die
aktuell einen Kurs belegen, soll zusätzlich der Kursname ausgegeben werden.
4. Geben Sie alle Kurse mit Kursnamen aus. Bei Kursen, die aktuell von Kursteilnehmern
belegt sind, sollen diese zusätzlich mit Vornamen und Nachnamen ausgegeben werden.
5. Alle Kursteilnehmer (Vor- und Nachname) und alle Kurse (Kursname) sollen ausgegeben werden. Dabei werden auch nicht belegte Kurse und Kursteilnehmer die keinen
Kurs belegen berücksichtigt.
6. Fügen Sie einen Kursteilnehmer ein (Daten frei wählbar), der den Kurs "Stricken" mit
der Kursnummer 5 belegt.
7. Der Kursteilnehmer Nr. 7 wechselt in den Kurs Nr. 7.
8. Der Kursteilnehmer Nr. 5 wechselt in den Kurs mit dem Kursnamen "HTML".
9. Lösche die Datensätze aller Kursteilnehmer des Strickkurses (Kurs Nr. 5) aus der Datenbank.
10. Die Kursleiter sollen in einer weiteren Tabelle erfasst werden. Die Tabelle soll den Vorund Nachnamen der Kursleiter enthalten. Dieser muss eingefügt werden. Als Primärschlüssel wird eine eindeutige Kursleiter-Nummer verwendet. Jeder Kursleiter leitet genau einen Kurs, die entsprechende Kursnummer ist Teil der Tabelle. Das Gehalt der
Kursleiter wird mit angegeben und muss mindestens 500 € betragen. Der Einsatzort
der Kursleiter wird zusätzlich gespeichert und soll per Standard auf "Viernheim" eingestellt sein. Erstellen Sie entsprechend der Beschreibung einen CREATE-Befehl.