Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Hilfe zu Übung der folgenden aufgabe

Dieses Thema im Forum "PostgreSQL" wurde erstellt von RedPepper, 26 Dezember 2016.

  1. RedPepper

    RedPepper Neuer Benutzer

    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.
     
    Zuletzt bearbeitet: 26 Dezember 2016
  2. akretschmer

    akretschmer Datenbank-Guru

    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:

    Code:
    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 gefällt das.
  3. RedPepper

    RedPepper Neuer Benutzer

    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
     
  4. akretschmer

    akretschmer Datenbank-Guru

    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:

    Code:
    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?
     
  5. akretschmer

    akretschmer Datenbank-Guru

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

    Code:
    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.
     
  6. RedPepper

    RedPepper Neuer Benutzer

    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
     
  7. akretschmer

    akretschmer Datenbank-Guru

    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!
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden