8. Große Übung

(1)

Jan-Christoph Kalo Florian Plötzky

Institut für Informationssysteme

Technische Universität Braunschweig www.ifis.cs.tu-bs.de

8. Große Übung

SQL 2

(2)

Aufgabe 1

persnr

(1,1) Positionen

Hat ein

Hat

eine (1,*)

(1,1)

(1,*) geburtsdatum

vorname

name

stufe

Mitarbeiter

Tarifgehälter

betrag gruppe

Mitarbeiter(persnr, vorname, name, geburtsdatum, position→ Positionen, (gruppe, stufe) →Tarifgehälter)

Tarifgehälter(gruppe, stufe, betrag) Positionen(pid, bezeichnung, bonus)

pid

bezeichnung

bonus

Constraints:

• Tarifgehälter dürfen nie geringer als 1500 EUR ausfallen

• Ein Bonus wird nur Mitarbeiter in den Positionen

„Abteilungsleiter“ und „Vizepräsident“ gezahlt

(3)

Aufgabe 1

CREATE TABLETarifgehälter ( gruppe INTEGER NOT NULL, stufe INTEGER NOT NULL,

betrag FLOAT NOT NULL CHECK (betrag >= 1500), PRIMARY KEY(gruppe, stufe)

)

CREATE TABLE Positionen ( pid INTEGER NOT NULL,

bezeichnung VARCHAR(30) NOT NULL, bonus FLOAT NOT NULL CHECK (

bonus = 0

OR (bonus > 0 AND

(bezeichnung = ‚Abteilungsleiter‘ OR bezeichnung = ‚Vizepräsident‘)) ),

PRIMARY KEY(pid) )

(4)

Aufgabe 1

CREATE TABLE Mitarbeiter ( persnr INTEGER NOT NULL,

vorname VARCHAR(30) NOT NULL, name VARCHAR(30) NOT NULL, geburtsdatum DATE NOT NULL, position INTEGER,

tarifgruppe INTEGER, tarifstufe INTEGER, PRIMARY KEY(persnr),

FOREIGN KEY (position) REFERENCES Positionen ON DELETE SET NULL

ON UPDATE CASCADE,

FOREIGN KEY (tarifgruppe, tarifstufe) REFERENCESTarifgehälter ON DELETE SET NULL

ON UPDATE CASCADE )

(5)

Fügen Sie den Mitarbeiter „Charles Xavier“, geboren am 01.03.1932 hinzu.

Dieser bekleidet die Position eines Abteilungsleiters und wird in Tarifgruppe 38 Stufe 2 eingruppiert. Als Personalnummer ist hierbei die nächste freie Nummer zu wählen

Aufgabe 1.1

INSERT INTO Mitarbeiter VALUES (

(SELECT MAX(persnr) + 1 FROM Mitarbeiter), 'Charles‘,

'Xavier‘, 19320301,

(SELECT pid FROM Positionen WHERE bezeichnung = 'Abteilungsleiter‘), 38,

2 )

(6)

Aufgabe 1.2

Durch eine Hochzeit ändert sich der Nachname von „Jean Grey“ in „Black“. Passen Sie den Namen an, Sie können davon ausgehen, dass es nur eine „Jean Grey“ in der Datenbank gibt.

UPDATE Mitarbeiter SET name = 'Black' WHERE persnr = ( SELECT persnr

FROM Mitarbeiter

WHERE name = 'Grey’

AND vorname = ‘Jean’

)

(7)

Aufgabe 1.3

Erhöhen Sie das Tarifgehalt der Gruppe 3 für alle Stufen um 5%.

UPDATE Tarifgehälter SET betrag = betrag * 1.05 WHERE gruppe = 3

(8)

Aufgabe 1.4

Durch eine plötzliche Welle an Frühverrentungen scheiden alle Mitarbeiter, die vor dem 01.05.1957 geboren wurden. Löschen Sie die entsprechenden Datensätze.

DELETE FROM Mitarbeiter WHERE geburtsdatum < 19570501

(9)

Aufgabe 2

• Das Datenbankschema bildet Familienbeziehungen ab.

– Person(id, name, alter)

– IstKindVon(kind →Person, vater →Person, mutter →Person)

(10)

Aufgabe 2.1

• Finden Sie den Namen des ältesten Enkel von Jean Grey.

WITH enkel_jean AS (

SELECT p2.name, p2.alter FROM Person p1

JOIN IstKindVon k1 ON p1.id=k1.mutter

JOIN IstKindVon k2 ON k1.kind IN (k2.vater, k2.mutter) JOIN Person p2 ON k2.kind = p2.id

WHERE p1.name = “Jean Grey”

)

SELECT name FROM enkel_jean WHERE alter =

SELECT MAX(alter) FROM enkel_jean

Person(id, name, alter)

IstKindVon(kind→Person, vater→Person, mutter→Person)

(11)

Aufgabe 2.2

• Finden Sie die Anzahl der Geschwister für jede Person in der Datenbank. (keine Halbgeschwister)

WITH anzahl_geschwister AS (

SELECT k1.kind AS id, COUNT(k2.kind) AS anzahl FROM IstKindVon k1

LEFT JOIN IstKindVon k2 ON k1.mutter = k2.mutter AND k1.vater=k2.vater WHERE k1.kind <> k2.kind

GROUP BY k1.kind )

SELECT name, anzahl FROM Person p

LEFT JOIN anzahl_geschwister ag ON p.id = ag.id

(12)

Aufgabe 2.3

• Wie viele Cousins hat eine Person im Durchschnitt?

WITH anzahl_cousins AS (

SELECT k4.kind AS id, COUNT(k5.kind) AS anzahl FROM IstKindVon k1

JOIN IstKindVon k2 ON k1.kind IN (k2.vater, k2.mutter) JOIN IstKindVon k3 ON k1.kind IN (k3.vater, k3.mutter) JOIN IstKindVon k4 ON k2.kind IN (k4.vater, k4.mutter) JOIN IstKindVon k5 ON k3.kind IN (k5.vater, k5.mutter) WHERE k4.vater <> k5.vater AND k4.mutter <> k5.mutter

GROUP BY k4.kind )

SELECT AVG(anzahl) FROM anzahl_cousins

(13)

• Ungerade Anzahl an Gehältern: letzte Woche

• Gerade Anzahl an Gehältern: arithmetisches Mittel der beiden mittleren Zahlen bilden.

– Beispiel: 1000, 2000, 3000, 4000 dann ist der Median

2000+3000

2 = 2500

– Was müssen wir machen?

Aufgabe 3

(14)

• Ein mögliches Herangehen:

1. Gehälter sortieren

2. Zeilennummern als Berechnungsgrundlage hinzufügen (ROW_NUMBER())

3. CASE Unterscheidung verwenden um festzustellen ob Anzahl der Gehälter gerade oder ungerade (was heißt das?) ist

4. Entsprechend auswerten!

Aufgabe 3

(15)

• Schritte 1 und 2:

WITH gehalt AS (

SELECT betrag AS b,

ROW_NUMBER() OVER (ORDER BY betrag ASC) AS pos FROM Gehälter

)

Aufgabe 3

b pos

1000 1

2000 2

3000 3

4000 4

(16)

• Skelett für 3

SELECT DISTINCT

CASE WHEN (SELECT MAX(pos) FROM gehalt) % 2 = 0 THEN [GERADE]

ELSE [UNGERADE]

END AS median_gehalt FROM gehalt

Aufgabe 3

MAX(pos) ≡ Anzahl der Gehälter. Gerade Zahlen sind restlos durch 2 teilbar

(17)

• Ungerader Fall:

SELECT b

FROM gehalt

WHERE pos = (

SELECT MAX(pos) FROM gehalt) / 2 + 1 )

Aufgabe 3

b pos

1000 1

2000 2

3000 3

4000 4

(18)

• Gerader Fall:

SELECT (

(

SELECT b FROM gehalt

WHERE pos = (SELECT MAX(pos) FROM gehalt) / 2 ) +

(

SELECT b FROM gehalt

WHERE pos = (SELECT MAX(pos) FROM gehalt) / 2 + 1 )

) / 2.0

FROM gehalt

Aufgabe 3

b pos

1000 1

2000 2

3000 3

4000 4

(19)

WITH gehalt AS (

SELECT betrag AS b,

ROW_NUMBER() OVER (ORDER BY betrag ASC) AS pos FROM Gehälter

)

SELECT DISTINCT

CASE WHEN (SELECT MAX(pos) FROM gehalt) % 2 = 0 THEN (

(SELECT b FROM gehalt WHERE pos = (SELECT MAX(pos) FROM gehalt) / 2) + (SELECT b FROM gehalt WHERE pos = (SELECT MAX(pos) FROM gehalt) / 2 + 1) ) / 2.0

ELSE (

SELECT b FROM gehalt WHERE pos = (SELECT MAX(pos) FROM gehalt) / 2 + 1 )

END AS median_gehalt FROM gehalt

Aufgabe 3 - Gesamtlösung

(20)

• Normalisierung

• Funktionale Abhängigkeiten

• Normalformen

– 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, 6NF

Nächste Woche

hero_id team_id hero_name team_name join_year

hero_id hero_name hero_id team_id team_name join_year

It‘s that simple!