DBS1-Übung - Friedrich-Schiller

Datenbanksysteme 1
- Übungsblatt 6 Prof. Dr. Klaus Küspert
Dipl.-Inf. Andreas Göbel
Friedrich-Schiller-Universität Jena
Lehrstuhl für Datenbanken und
Informationssysteme
0) Vorbereitung
 Wechsel vom Kommandozeilenmodus in den interaktiven Modus mit Semikolon
als Trennzeichen
(iibm08:ueb0??) $db2 -t
 Verbindung zur Datenbank UNIDB herstellen
db2 => connect to unidb;
Database Connection Information
Database server
SQL authorization ID
Local database alias
= DB2/6000 8.2.4
= UEB000
= UNIDB
 Für die weiteren Statements wird der interaktive Modus verwendet und auf die
Darstellung des Prompts db2 => verzichtet
 Verlassen des interaktiven Modus mit terminate;
2
DBS1-Übung
1) Tabellen anlegen
CREATE TABLE mitarbeiter(
pnr
CHAR(3) NOT NULL PRIMARY KEY,
name
VARCHAR(20) NOT NULL,
gehalt INTEGER CHECK (gehalt >= 0));
CREATE TABLE beamter(
pnr CHAR(3) NOT NULL REFERENCES mitarbeiter(pnr)
ON DELETE NO ACTION,
besoldungsgruppe VARCHAR(4) NOT NULL,
PRIMARY KEY(pnr));
 DB2 verlangt Klammern um Check-Klausel und ein explizites NOT NULL bei PK
 Explizite Fremdschlüsseldefinition als Alternative zu Inline-Definition:
FOREIGN KEY (pnr) REFERENCES mitarbeiter
ON DELETE NO ACTION
 Umsetzung eines schwachen Entitätstyps durch Fremdschlüssel
3
DBS1-Übung
2a) Erweiterung des Beamten um Status
ALTER TABLE beamter
ADD COLUMN status VARCHAR(20)
NOT NULL
DEFAULT 'auf Widerruf'
CHECK (status IN ('auf Widerruf', 'auf Probe',
'auf Zeit', ‚auf Abstellgleis', 'auf
Lebenszeit'));
4
DBS1-Übung
2b) Löschen des Status
ALTER TABLE beamter
DROP COLUMN status;
SQL0104N An unexpected token "COLUMN" was found following
"R TABLE beamter DROP". Expected tokens may include:
"CONSTRAINT". SQLSTATE=42601
 Sprachkonstrukt unbekannt
 DB2 unterstützt Löschen von Spalten erst ab Version 9
5
DBS1-Übung
2c) Erneutes Anlegen der Tabellen
DROP TABLE mitarbeiter;
DROP TABLE beamter;

6
Anschließend Statements der Aufgabe 1a) erneut ausführen
DBS1-Übung
3a) Einfügen der Mitarbeitertupel
INSERT
INTO mitarbeiter (pnr,
VALUES ('P01', 'Ameyer',
('P02', 'Bmeyer',
('P03', 'Cmeyer',
('P04', 'Dmeyer',
('P05', 'Emeyer',
('P06', 'Fmeyer',
('P07', 'Gmeyer',
('P08', 'Hmeyer',
name, gehalt)
105),
205),
305),
405),
505),
605),
705),
805);
 Attributliste ist in diesem Fall optional, da in den einzufügenden Tupeln jedes
Attribut von Mitarbeiter ein Wert zugeordnet wird
 Es ist empfehlenswert, die Attributliste stets anzugeben, um Missverständnisse
zu vermeiden
 Alternative: acht INSERT-Anweisungen mit je einem Tupel
7
DBS1-Übung
3b) Einfügen der Tupel für Beamte (Möglichkeit 1)
INSERT
INTO beamter (pnr, besoldungsgruppe)
VALUES ('P02','A3'),
('P04','A4'),
('P06','A4'),
('P08','A6');
 Nachteil: Die Personalnummern der Beamten müssen vorher manuell ermittelt
werden.
8
DBS1-Übung
3b) Einfügen der Tupel für Beamte (Möglichkeit 2)
INSERT INTO beamter (pnr, besoldungsgruppe)
SELECT pnr, 'A3'
FROM
mitarbeiter
WHERE name='Bmeyer';
INSERT INTO beamter (pnr, besoldungsgruppe)
SELECT pnr, 'A4' FROM mitarbeiter WHERE name='Dmeyer';
INSERT INTO beamter (pnr, besoldungsgruppe)
SELECT pnr, 'A4' FROM mitarbeiter WHERE name='Fmeyer';
INSERT INTO beamter (pnr, besoldungsgruppe)
SELECT pnr, 'A6' FROM mitarbeiter WHERE name='Hmeyer';
9
DBS1-Übung
3c) Einfügen des Beamten P13
INSERT INTO beamter (pnr, besoldungsgruppe)
VALUES ('P13', 'W7');
SQL0530N The insert or update value of the FOREIGN KEY
"UEB000.BEAMTER.SQL120111113907071" is not equal to any value of the
parent key of the parent table. SQLSTATE=23503
 Fehlermeldung, da Verletzung der Fremdschlüsselbeziehung
10
DBS1-Übung
3d) Einfügen des Mitarbeiters P01
INSERT INTO mitarbeiter (pnr, name, gehalt)
VALUES ('P01', 'Ymeyer', 777);
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index identified
by "1" constrains table "UEB000.MITARBEITER" from having duplicate
rows for those columns. SQLSTATE=23505
 Fehlermeldung, da Verletzung der Primärschlüsseleigenschaft
11
DBS1-Übung
4a) Abfrage der Namen aller Mitarbeiter
SELECT name
FROM
mitarbeiter;
NAME
-----------------------------Ameyer
Bmeyer
Cmeyer
Dmeyer
Emeyer
Fmeyer
Gmeyer
Hmeyer
8 record(s) selected.
12
DBS1-Übung
4b) Abfrage der Namen aller Mitarbeiter mit Gehalt > 600 & PNr != P08
SELECT name
FROM
mitarbeiter
WHERE gehalt>600 AND pnr <> 'P08';
NAME
-----------------------------Fmeyer
Gmeyer
2 record(s) selected.
13
DBS1-Übung
4c) Abfrage der Durchschnittsgehälter aller Mitarbeiter
SELECT AVG(gehalt) AS durchschnittsgehalt
FROM
mitarbeiter;
DURCHSCHNITTSGEHALT
------------------455
1 record(s) selected.
14
DBS1-Übung
Exkurs: Anfragen auf mehrere Relationen
 Impliziter Cross Join:
PNR
--P01
P02
P03
SELECT *
FROM
mitarbeiter,
beamter;
 Expliziter Cross Join (ab DB2 V9)
SELECT *
FROM
mitarbeiter
CROSS JOIN
beamter;
15
NAME
GEHALT PNR BESOLDUNGSGRUPPE
------ ------ --- ---------------Ameyer
105 P02 A3
Bmeyer
205 P02 A3
Cmeyer
305 P02 A3
. . .
P08 Hmeyer
805 P08 A6
32 record(s) selected.
DBS1-Übung
Exkurs: Anfragen auf mehrere Relationen
 Impliziter Inner Join:
SELECT *
FROM
mitarbeiter m,
beamter b
WHERE m.pnr = b.pnr;
PNR
--P02
P04
P06
P08
NAME
-----Bmeyer
Dmeyer
Fmeyer
Hmeyer
GEHALT
-----205
405
605
805
PNR
--P02
P04
P06
P08
BESOLDUNGSGRUPPE
---------------A3
A4
A4
A6
4 record(s) selected.
 Expliziter Inner Join:
SELECT *
FROM
mitarbeiter m INNER JOIN beamter b
ON
m.pnr = b.pnr;
 Natural Join: Wird von DB2 nicht unterstützt , existiert aber laut SQL-Norm
16
DBS1-Übung
Exkurs: Anfragen auf mehrere Relationen
 Left Outer Join:
SELECT *
FROM
mitarbeiter m LEFT OUTER JOIN beamter b
ON
m.pnr = b.pnr;
PNR
--P02
P04
P06
P08
P05
P01
P03
P07
NAME
-------Bmeyer
Dmeyer
Fmeyer
Hmeyer
Emeyer
Ameyer
Cmeyer
Gmeyer
GEHALT
-----205
405
605
805
505
105
305
705
PNR
--P02
P04
P06
P08
-
BESOLDUNGSGRUPPE
---------------A3
A4
A4
A6
-
8 record(s) selected.
 Anfrageergebnis für Right Outer Join und Full Outer Join?
- Ergebnis des Right Outer Joins entspricht (HIER!) dem des Inner Joins
- Ergebnis des Full Outer Joins entspricht (HIER!) dem des Left Outer Joins
17
DBS1-Übung
4d) Abfrage derNamen aller Beamten der Besoldungsgruppe A4
 Impliziter Join:
SELECT name
FROM
mitarbeiter m, beamter b
WHERE m.pnr = b.pnr AND b.besoldungsgruppe = 'A4';
NAME
----------------------Dmeyer
Fmeyer
2 record(s) selected.
 Expliziter Join:
SELECT name
FROM
mitarbeiter m INNER JOIN beamter b
ON
m.pnr = b.pnr AND b.besoldungsgruppe = 'A4';
18
DBS1-Übung
5a-c) Ändern und Löschen von Daten
a) Alle Mitarbeitergehälter um 1 erhöhen:
UPDATE mitarbeiter
SET
gehalt=gehalt+1;
b) Mitarbeiter mit PNr P02 Beamtenstatus entziehen:
DELETE
FROM
beamter
WHERE pnr='P02';
c) Mitarbeiter mit PNr P02 entlassen
DELETE
FROM
mitarbeiter
WHERE pnr='P02';
19
DBS1-Übung
5d) Mitarbeiter mit Gehalt ≥ 706 entlassen
 Wegen der Fremdschlüsselbeziehung müssen zuerst die betroffenen BeamtenTupel gelöscht werden:
DELETE
FROM
beamter
WHERE pnr IN (SELECT pnr
FROM
mitarbeiter
WHERE gehalt >= 706);
 Anschließend können die Mitarbeiter-Tupel gelöscht werden:
DELETE
FROM
mitarbeiter
WHERE gehalt >= 706;
20
DBS1-Übung
5e-f) Verlobung von Dmeyer übernehmen und Mitarbeiterdaten ausgeben
UPDATE mitarbeiter
SET
name='Dmeyer-Tmeyer'
WHERE name='Dmeyer';
SELECT *
FROM
mitarbeiter;
PNR
--P01
P03
P04
P05
P06
NAME
GEHALT
------------------------------ ----------Ameyer
106
Cmeyer
306
Dmeyer-Tmeyer
406
Emeyer
506
Fmeyer
606
5 record(s) selected.
21
DBS1-Übung
Exkurs: Sichten (Allgemeines)
 Logische / abgeleitete / virtuelle Relationen
 Bauen auf Basisrelationen bzw. anderen, vorher erzeugten, virtuellen Relationen
auf
 Bsp:
View
View
View
Tabelle
Tabelle
 Syntax:
CREATE VIEW
name
AS
(
attribut
,
DROP VIEW
22
name
DBS1-Übung
)
subselect
zur Umbenennung
von Attributen
Exkurs: Sichten (Bewertung und Hinweise)
 Vorteile:
- Realisierung der Externen Ebene gemäß ANSI/SPARC (Datenunabhängigkeit)
- Realisierung virtueller Attribute (Berechnungen) und Aggregationen
- Sichten können u.a. zeitabhängige (wie das aktuelle Datum) und
nutzerabhängige (wie Nutzernamen) Elemente enthalten
- Verbergen der Komplexität von Daten führt zu vereinfachtem Zugriff
- Datensicherheit: zielgerichtete Vergabe von Zugriffsrechten durch Admin.
- Geringer Speicherplatzbedarf: Speichern der Sichtdefinition statt des Inhalts
 Nachteile:
- Komplexe Sichtdefinitionen führen u.U. zu Performance-Problemen
- Definition vieler aufeinander aufbauender Sichten reduziert Übersichtlichkeit
 rekursive Sichten ab SQL-99 erlaubt
 automatisches Update bei Änderungen in Basistabellen („Änderungen von unten“)
 Lesende Anfragen auf Sichten uneingeschränkt, ändernde Anfragen nur
eingeschränkt möglich („Änderungen von oben“)
23
DBS1-Übung
6a-b) Definition der View und Einfügen eines Mitarbeiters
CREATE VIEW sauteureMitarbeiter
(pnr, name, saudummeUnkosten) AS
SELECT pnr, name, gehalt
FROM
mitarbeiter
WHERE gehalt>418;
INSERT
INTO
sauteureMitarbeiter
VALUES ('P09', 'Imeyer', 905);
24
DBS1-Übung
6c) Ausgabe der sauteuren Mitarbeiter
SELECT name
FROM
sauteureMitarbeiter;
NAME
-------------------Imeyer
Emeyer
Fmeyer
3 record(s) selected.
25
DBS1-Übung