1 - oth-regensburg.de

Datenbanken
8. Übung
Aufgabe: Hierarchische Beziehungen in relationalen DB
Eine hierarchische Beziehung in der Datenbank für das Personalwesen ist bspw. die Beziehung
"Vorgesetzter" zwischen Arbeitnehmern (Angestellte) in einem Unternehmen. Voraussetzung für die
Entwicklung einer hierarchischen Beziehung ist ein Feld "Vorgesetzter" in der Tabelle "Angestellte".
Die Angestellten, die "große Vorgesetzte" sind (d.h. "Vorgesetzte der Vorgesetzten") haben im Feld
"Vorgesetzter" den Eintrag "null" stehen. Die Daten zum Personalwesen erweitern sich um
Vorgesetzte folgendermaßen:
ANG_ID
NAME
GEBJAHR
ABT_ID
JOB_ID
QUALI
A16
A17
A18
A19
A20
A15
A21
Bernd
Dieter
Gustav
Karl
Kurt
Christian
Liesel
22.9.1948
12.9.49
11.5.42
2.4.1951
3.10.1941
17.11.1940
17.8.1956
OD
PA
KO
RZ
VT
LT
LT
AL
AL
AL
AL
AL
GF
SK
AL,SY
AL, IN
AL, IN
AL,PR
AL.KA
GF
SK
Ein ERM-Diagramm würde den Sachverhalt so beschreiben:
Lösungsvorschlag:
1. Erweiterung bzw. Veränderung der Tabelle Angestellte um das Feld "Vorgesetzter"
Die Tabelle Angestellte muß um die Spalte VORGESETZTER erweitert werden:
ANGESTELLTE
ANG_ID NAME
A1
Fritz
A2
Tom
A3
Werner
A4
Gerd
A5
Emil
GEBJAHR
2.1.1950
2.3.1951
23.4.1948
3.11.1950
2.3.1960
ABT_ID
OD
KO
OD
VT
PA
JOB_ID
SY
IN
PR
KA
PR
1
VORGESETZTER
A16
A18
A16
A20
A17
VORGESETZTER
A15
A15
A15
A15
A15
null
A15
Datenbanken
A6
A7
A8
A9
A10
A11
A12
A13
A14
Uwe
Eva
Rita
Ute
Willi
Erna
Anton
Josef
Maria
3.4.1952
17.11.1955
02.12.1957
08.09.1962
7.7.1956
13.10.1966
5.7.1948
2.8.1952
17.09.1964
RZ
KO
KO
OD
KO
OD
OD
KO
PA
OP
TA
TA
SY
IN
KA
SY
SY
KA
A19
A18
A18
A16
A18
A16
A16
A18
A17
Veränderung an der Tabellenstruktur sind über ALTER TABLE ... vorzunehmen:
ALTER TABLE <TABLE NAME> ADD|DROP|MODIFY (<Spalten Spezifikationen...>1);
Im vorliegenden Fall führt das zu: ALTER TABLE angestellte ADD(Vorgesetzter VARCHAR2(3));
2. Füllen der neuen Spalte mit Daten2.
Die Daten sind in die neue Spalte mit der UPDATE-Anweisung einzubringen:
UPDATE <Spalte(n)>
SET <Spalten-Name> = <Wert>
[WHERE <Bedingung>];
2. Erweiterung der Tabellen "job" bzw. Qualifikation um "leitende Positionen".
JOB
JOB_ID
KA
TA
SY
PR
OP
AL
GF
SK
TITEL
Kaufm. Angestellter
Techn. Angestellter
Systemplaner
Programmierer
Operateur
Abteilungsleiter
Geschaeftsfueher
Sekretaerin
GEHALT
3000,00 DM
3000,00 DM
6000,00 DM
5000,00 DM
3500,00 DM
7500,00 DM
9000,00 DM
2500,00 DM
QUALIFIKATION
ANG_ID
JOB_ID
A1
SY
A1
PR
A1
OP
A2
IN
A2
SY
A3
PR
A4
KA
A5
PR
A6
OP
A7
TA
A8
IN
A9
SY
A10
IN
A11
KA
A12
SY
A13
IN
A14
KA
1
2
vgl. CREATE-Befehl
vgl. ueb8.sql
2
Datenbanken
A16
A16
A17
A17
A18
A18
A19
A19
A20
A20
A15
A21
AL
SY
AL
KA
AL
IN
AL
PR
AL
KA
GF
SK
3. Auswahl der hierarchischen Beziehungen.
Sie erfolgt über
a) die Beziehung zwischen oberen und unteren Knoten in der Hierarchie.
Die hierarchische Beziehung wird durch die CONNECT BY -Klausel bestimmt. Damit wird die
Beziehung zwischen Vorgänger und Nachfolgerknoten hergestellt, die Angabe PRIOR ist ein Verweis
auf den Vorgängerknoten.
b) Die Wurzel der Hierarchie
Sie wird durch die START WITH -Klausel definiert.
select ang_id, name, vorgesetzter
from angestellte
connect by prior ang_id = vorgesetzter
start with ang_id = 'A15'
order by name;
Für Baumstrukturen ist die Einführung eines Pseudo-Felds LEVEL möglich, das die Tiefe des
jeweiligen Satzes (Hierarchiestufe) angibt. Die Wurzel des Baums hat die Tiefe 1.
select max(level) from angestellte
connect by prior ang_id = vorgesetzter;
Eine Darstellung, die die hierarchischen Beziehungen besonders gut angibt 3, erhält man mit Hilfe der
Funktion LPAD (padding from left, Auffüllen von links).
select lpad('-',(level-1)*3,'+--') || name || ' -- ' organigramm
from angestellte
connect by prior ang_id = vorgesetzter
start with name = 'Christian';
Eine Analyse vom Blatt zur Wurzel zeigt:
select level, name, ang_id, vorgesetzter
from angestellte
connect by ang_id = prior vorgesetzter
start with name = 'Gerd';
Zusaetzliche Bedingung können in der where-Klausel angegeben werden
select level, name, ang_id, vorgesetzter
from angestellte
where name != 'Bernd'
connect by prior ang_id = vorgesetzter
start with name = 'Christian';
3
vgl. ueb8.sql
3
Datenbanken
Wenn zusaetzliche Bedingungen in der connect by-Klausel eingefuegt werden, werden ganze Teile
der Hierarchie nicht dargestellt
select level, name, ang_id, vorgesetzter
from angestellte
connect by prior ang_id = vorgesetzter
and name != 'Bernd'
start with name = 'Christian';
Eine Darstellung eines Baums mit mehreren Startpunkten
select level, a1.ang_id, a1.name, a1.vorgesetzter
from angestellte a1
connect by prior a1.ang_id = a1.vorgesetzter
start with a1.ang_id in
(select a2.ang_id
from angestellte a2, job
where a2.job_id = job.job_id
and job.titel = 'Abteilungsleiter');
Die Benutzung von connect by in einer Subquery zeigt
select a1.name, abt.bezeichnung
from angestellte a1, abteilung abt
where a1.abt_id = abt.abt_id
and ang_id in
(select a2.ang_id
from angestellte a2
connect by ang_id = prior vorgesetzter
start with name = 'Gerd');
In der FROM-Klausel einer Abfrage mit CONNECT BY darf nur eine Tabelle oder ein View stehen. Es
ist somit auch nicht möglich, den CONNECT BY –Operator innerhalb eines Join zu verwenden. Auch
darf ein veränderter View wiederum nur auf eine Tabelle aufbauen. Eine Alternative für die
Kombination von Join und CONNECT BY ist jedoch vorhanden.
Gib alle Mitarbeiter auf Hierarchiestufe 3 an.
4. Hierarchische Abfragen in Oracle Version 9i
Bisher (vor Oracle Version 9i) galten folgende Einschränkungen:
- Eine Sortierung mit der ORDER BY –Klausel übersteuerte die gefunden Hierarchie
- ein Join oder eine View, die einen JOIN beinhalteten, konnte nicht verwendet werden
- eine Master-Detail –Beziehung musste demzufolge immer in der gleichen Tabelle abgelegt sein
- eine Sub-Query war nicht erlaubt
Join-Beziehung. Die Verwendung eine Join in einer CONNECT BY –Klausel ist nun problemlos
möglich
select substr(lpad('/',2*level,'-') || name,1,15) "NAME",
abt_id, bezeichnung, level
from angestellte NATURAL JOIN abteilung
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter;
Erstellen einer Hilfstabelle
create table t_mgr as select ang_id, vorgesetzter from angestellte;
select * from t_mgr;
Abfrage mit zusammengesetzter Master-Detail Beziehung
4
Datenbanken
select substr(lpad ('/', 2*level,'-') || name, 1, 15) "NAME",
a.ang_id, level
from angestellte a join t_mgr m on (a.ang_id = m.ang_id)
start with m.vorgesetzter is null
connect by prior a.ang_id = m.vorgesetzter;
drop table t_mgr;
Selbst eine connect-by Klausel auf einen View, der einen join beinhaltet, kann nun verwendet werden
create view v_ang_abt
(ang_id, name, vorgesetzter, job_id, abt_id, bezeichnung)
as
select ang_id, name, vorgesetzter, job_id, abt_id, bezeichnung
from angestellte natural join abteilung;
select substr(lpad('/',2*level,'-') || name,1,15) "NAME",
titel, level
from v_ang_abt, job
where v_ang_abt.job_id = job.job_id
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter;
drop view v_ang_abt;
Resultate einer hierarchischen Anwendung konnten über ORDER BY nicht sinnvoll sortiert werden, z.
B.
select substr(lpad('/',2*level,'-') || name,1,15) "NAME",
abt_id, bezeichnung, level
from angestellte NATURAL JOIN abteilung
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter
order by name;
In Oracle 9i steht die ORDER SIBLINGS BY –Klausel zum Sortieren bereit. Das Schlüsselwort
SIBLINGS bewirkt, dass beim Sortiervorgang die vorhanden Hierarchie berücksichtigt wird.
select substr(lpad('/',2*level,'-') || name,1,15) "NAME",
abt_id, bezeichnung, level
from angestellte NATURAL JOIN abteilung
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter
order siblings by name;
Nei ist auch die SYS_CONNECT_BY_PATH –Klausel. Sie veranschaulicht den hierarchischen Weg
zum Root-Element. Der zweite Parameter gibt das Trennzeichen zwischen den verschiedenen Stufen
an.
select substr(sys_connect_by_path(name,'/'),1,30) "name", level
from angestellte
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter
order siblings by name;
Eine Sub-Query in der CONNECT BY –Klausel ist weiterhin nicht erlaubt, z.B:
select name, ang_id, vorgesetzter
from angestellte
start with vorgesetzter is null
connect by prior ang_id = (select vorgesetzter
from angestellte
5
Datenbanken
where name = 'Christian');
Neu ist nur die Möglichkeit eine Sub-Query in der FROM-Klausel anzusprechen.
select substr(lpad('/',2*level,'-') || name,1,15) "NAME",
abt.abt_id, bezeichnung, level
from angestellte ang, (select bezeichnung, abt_id from abteilung) abt
where abt.abt_id = ang.abt_id
start with vorgesetzter is null
connect by prior ang_id = vorgesetzter;
SQL-Skript 1
SQL-Skript 2
6