Hilfe zu Übung der folgenden aufgabe

[RedPepper]

Könnte mir jemand bitte diese datenbank erstellen und die folgenden SQL abfragen ausprobieren und per code antworten

1. Erstelle Tabellen um Mitarbeiter zu erfassen! Die Tabellen werden fuer eine
Awendung zur Verwaltung von Personal in einer Firma gebraucht.

- Eine >>Abteilung<< der Firma hat einen Namen, der eindeutig sein muss
(z.B. 'Forschung', 'Verkauf', etc..)

- Ein >>Mitarbeiter<< hat einen Namen und eine Matrikelnummer (ein String),
die eindeutig sein muss. Ausserdem noch erfasst ist das Anstellungsjahr
und das Austrittsjahr (default NULL). Das Jahr wird als Ganzzahl (z.B.
2016) erfasst. Stelle sicher, dass nur gueltige Jahreszahlen eingetragen werden
koennen (also z.B. Ganzzahlen groesser als 2000) und dass das Austrittsjahr
(falls erfasst) groesser oder gleich gross wie das Anstellungsjahr ist.

- Die Zwischentabelle >>Anstellung<< verknuepft Mitarbeiter und Abteilungen:
jeder Mitarbeiter ist einer oder mehrerer Abteilungen zugeordnet und jede
Abteilung hat einen oder mehrere Mitarbeiter.

Vergiss nicht, fuer jede Tabelle Primarschluessel und wo noetig auch Fremd-
schluessel und Constraints zu erstellen!

Alle Statements wie zB Create und Alter angeben


2. SQL Abfragen

a) Erstelle eine Liste der Mitarbeiter, die noch in der Firma arbeiten
(also Austrittsjahr IS NULL). Die Liste soll den Namen des Mitarbeiters,
sein Eintrittsjahr und einen String mit allen Namen der Abteilungen enthalten,
in der er oder sie taetig ist (diesen String erstellst Du am einfachsten
mit string_agg(abteilung.name, ', ')).

b) Liste alle Mitarbeiter, die vor 1950 in der Firma gearbeitet haben.

c) Liste alle Mitarbeiter der Abteilung 'Forschung', die bereits seit mehr
als 10 Jahren dort arbeiten.

 

[akretschmer]

Was ist denn Dein Ansatz bisher? Da ist ja gar nix bisher, oder?

Wegen Weihnachten und so, hier mal eine Idee für den ersten Teil:

test=# create table abteilung (id int primary key, name text unique);
CREATE TABLE
test=*# create table mitarbeiter (id int primary key, matrikelnummer text unique, name text, start_jahr int not null, end_jahr int, check(start_jahr <= end_jahr), check(start_jahr >= 1900));
CREATE TABLE
test=*# create table anstellung(mitarbeiter int references mitarbeiter, abteilung int references abteilung, primary key(mitarbeiter, abteilung));
CREATE TABLE
test=*#

Dabei ist "jeder Mitarbeiter ist einer oder mehrerer Abteilungen zugeordnet und jede Abteilung hat einen oder mehrere Mitarbeiter." noch nicht erzwungen, hier könnte man noch einen Constraint Trigger erreichen. Man könnte diskutieren, ob man bei Abteilung den namen oder bei Mitarbeiter die Matrikelnummer gleich als PK verwendet. Ich mag allerdings nicht unbedingt Strings als PK, weil die Indexe dann naturgemäß größer werden. Man könnte auch über den zusammengefaßten PK bei der Anstellungs-Tabelle diskutieren. Alternativ wären hier 2 Indexe denkbar, das hängt aber auch von den erwarteten Abfragen ab. Ich hab mich für diese Version entschieden, um Dubletten von (mitarbeiter, abteilung) zu verhindern.

Da im zweiten Teil auch nach Mitarbeitern von von 1950 gefragt wurde, habe ich den Check-Constraint für das Eintrittasalter auf 1900 gesetzt. Wenn man weiß, wann die Firma gegründet wurde, könnte man hier einen exakten Wert einsetzen - in der Regel stellt man ja erst nach der Gründung jemanden ein.

Für die Angabe der Jahre würde sich übrigens auch ein INT4RANGE anbieten, da ist der Check, daß die obere Grenze gleich/größer der unteren sein soll, schon impliziet.

Den zweiten Teil machst Du bitte selber und zeigst ihn hier.

Bonus-Aufgabe für 2a: erstelle einen partiellen Index, der exakt diese Abfrage beschleunigen könnte!


Andreas

 

[RedPepper]

ich hab nur die b.) geschafft.
Select * from mitarbeiter where start_jahr < 1950

die a.)
Select name, start_jahr from mitarbeiter where end_jahr = null

c.) keine ahnung wie ich den start_jahr und end_jahr subtrahiere

 

[akretschmer]

a) Du kannst nicht via = auf NULL prüfen, verwende statt dessen "... where end_jahr IS NULL"

c) erst einmal brauchst Du hier Joins, um die Daten aus den einzelnen Tabellen zu verknüpfen. Du gehst von der Anstellungs-Tabelle aus und verbindest die mit den beiden anderen Tabellen jeweils über das entsprechende Feld. Dann noch die Where-Condition: Abteilung.Name muß 'Forschung' sein und die Differenz aus dem aktuellen Jahr und dem Eintrittsjahr muß größer 10 sein und das Ende-Jahr muß NULL sein:

select m.* from anstellung s left join abteilung a on s.abteilung=a.id left join mitarbeiter m on s.mitarbeiter=m.id where a.name = 'Forschung' and m.end_jahr is NULL and extract(year from current_date) - m.start_jahr > 10;

simpel, oder?

Hast das mit dem Index verstanden?

 

[akretschmer]

Um das mit dem index zu zeigen (ich denke, da kommst Du nicht drauf...)

test=*# create index idx_mitarbeiter_inaktiv on mitarbeiter (end_jahr) where end_jahr is null;
CREATE INDEX
test=*# set enable_seqscan to off;
SET
test=*# explain select * from mitarbeiter where end_jahr is null;
  QUERY PLAN   
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on mitarbeiter  (cost=4.13..12.58 rows=4 width=76)
  Recheck Cond: (end_jahr IS NULL)
  ->  Bitmap Index Scan on idx_mitarbeiter_inaktiv  (cost=0.00..4.13 rows=4 width=0)
  Index Cond: (end_jahr IS NULL)
(4 Zeilen)

Dieser Index mit der Where-Condition ist ein partieller Index - er wird nur über die Datensätze erstellt, wo end_jahr IS NULL zutrifft. Im Select nachher haben wir dieselbe Where-Condition, damit kann der Index hier genutzt werden. Das "set enable_seqscan to off" ist nötig, um den Planner auszutricksen, weil die Tabelle leer ist. Partielle und auch funktionale Indexe sind eine eher wenig bekannte, aber sehr effektive Methode, um die Performance zu verbessern - wenn diese sinnvoll eingesetzt werden.

 

[RedPepper]

ok danke.
wie kann ich bei der a)
"sein Eintrittsjahr und einen String mit allen Namen der Abteilungen enthalten,
in der er oder sie taetig ist"
herausfinden

 

[akretschmer]

Lies die Aufgabe genau, da steht schon die Lösung drin ;-)

string_agg() ist eine Aggregatsfunktion, erster Parameter das Feld, der zweiter das Trennzeichen. Gieb Dir Mühe, das schaffst Du!