Übungsblatt 4 - Lösung - Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. A. Huhn, M. Endres, T. Preisinger
WS 2008/2009
14. Nov. 2008
Lösungsblatt 4
Datenbanksysteme I
Aufgabe 1: ER-Modellierung
Abbildung 1 zeigt eine mögliche Modellierung für ein Krankhausverwaltungssystem.
Abbildung 1: Modellierung einer Krankenhausverwaltung
Beziehungen und Konsistenzbedingungen
• Patienten sind stationär in Zimmern untergebracht.
• Zimmer sind Stationen zugeordnet und sind daher existenzabhängig zu modellieren.
• Einem Patienten sind behandelnde Ärzte zugeordnet.
• Ärzte und Pflegepersonal sind Stationen zugeordnet und von Stationspersonal abgeleitet.
• Die Beziehung zwischen Pflegepersonal und Patienten besteht nur indirekt über die Stationen.
1
Aufgabe 2: Aggregation und Gruppierung in Relationaler Algebra und SQL
Gegeben war folgende Tabelle einer Gehaltsabrechnung:
Gehaltsabrechnung
Mitarbeiter
Anton
Anton
Anton
Berta
Berta
Berta
Claus
Claus
Claus
Monat
1
2
3
1
2
3
1
2
3
Gehalt
10
20
30
10
10
10
20
20
10
Zu beantworten waren nachstehende Anfragen. Dabei ist zu beachten, dass es sich bei COUNT, SUM und AVG immer
um MULTISET GROUPBY handeln muss, damit die Duplikate nicht verloren gehen.
a) Wie hoch ist das durchschnittliche Gehalt der einzelnen Mitarbeiter?
• RA:
M U LT ISET GROU P BY (Gehaltsabrechnung, M itarbeiter, AV G, Gehalt)
• SQL:
SELECT Mitarbeiter, AVG(Gehalt)
FROM Gehaltsabrechnung
GROUP BY Mitarbeiter;
Mitarbieter
Anton
Berta
Claus
AVG(Gehalt)
20
10
16,6666667
b) Bestimmen Sie, wie oft die jeweiligen Gehälter ausbezahlt wurden.
• RA:
M U LT ISET GROU P BY (Gehaltsabrechnung, Gehalt, COU N T, Gehalt)
• SQL:
SELECT Gehalt, COUNT(Gehalt)
FROM Gehaltsabrechnung
GROUP BY Gehalt;
Gehalt
10
20
30
COUNT(Gehalt)
5
3
1
c) Wieviel Geld wurde im jeweiligen Monat insgesamt ausbezahlt?
• RA:
M U LT ISET GROU P BY (Gehaltsabrechnung, M onat, SU M, Gehalt)
• SQL:
SELECT Monat, SUM(Gehalt)
FROM Gehaltsabrechnung
GROUP BY Monat
2
Monat
1
2
3
SUM(Gehalt)
40
50
50
d) Was ist das höchste Gehalt, dass die jeweiligen Mitarbeiter jemals erhalten haben?
• RA:
GROU P BY (Gehaltsabrechnung, M itarbeiter, M AX, Gehalt)
• SQL:
SELECT Mitarbeiter, MAX(Gehalt)
FROM Gehaltsabrechnung
GROUP BY Mitarbeiter;
Mitarbeiter
Anton
Berta
Claus
MAX(Gehalt)
30
10
20
e) Bestimmen Sie das Gesamtgehalt des am schlechtesten bezahlten Mitarbeiters.
• RA: Zunächst
Pwerden die Mitarbeiter gruppiert und somit das Gesamtgehalt bestimmt. Das Ergebnis bezeichnen wir mit
X
:= M U LT ISET GROU P BY (Gehaltsabrechnung, M itarbeiter, SU M, Gehalt)
und entspricht
Mitarbeiter
Anton
Berta
Claus
SUM(Gehalt)
60
30
50
Die Aggregation MIN erfolgt ebenfalls über ein GROUPBY. Da hier eine Gruppierung allerdings nicht mehr
notwendig ist, verwenden wir als Gruppierungsattribut die leer Menge ∅, d.h.
X
M U LT ISET GROU P BY ( , ∅, M IN, SU M (Gehalt))
• SQL:
SELECT MIN(SUM(Gehalt))
FROM Gehaltsabrechnung
GROUP BY Mitarbeiter
MIN(SUM(Gehalt))
30
3
Aufgabe 3: SQL Queries
Anfragen in SQL.
1. Finden Sie die Studenten, die mindestens im neunten Semester sind.
SELECT * FROM Studenten WHERE Semester >= 9;
2. Finden Sie pro Vorlesung die jeweiligen Hörer. Geben Sie Matrikelnummer und Name des Studierenden sowie
Vorlesungsnummer und Titel der Vorlesung aus.
SELECT s.MatrNr, s.Name, v.VorlNr, v.Titel
FROM Studenten s, hören h, Vorlesungen v
WHERE s.MatrNr = h.MatrNr and h.VorlNr = V.VorlNr;
3. Finden Sie die Studierenden mit der größten Semesterzahl.
SELECT * FROM Studenten
WHERE Semester = (SELECT MAX(Semester) FROM Studenten);
4. Berechnen Sie den Umfang des Prüfungsstoffes jedes Studierenden. Es soll der Name des Studierenden und die
Summe der Semesterwochenstunden der Prüfungsvorlesungen ausgegeben werden.
SELECT s.Name, SUM(v.SWS)
FROM Studenten s, hören h, Vorlesungen v
WHERE s.MatrNr = h.MatrNr and h.VorlNr = V.VorlNr
GROUP BY s.Name;
5. Finden Sie die Namen der Studierenden, die in keiner Prüfung eine bessere Note als 3,0 hatten. Die Anfrage
SELECT s.Name
FROM Studenten s, prüfen p
WHERE s.MatrNr = p.MatrNr
AND p.Note > 3;
liefert nur Studenten, die auch schon ein Prüfung abgeschlossen haben. Es sind aber alle Studenten gesucht, die in
keiner Prüfung eine bessere Note als 3,0 erzielten. Dies schließt auch die Studenten ein, die noch überhaupt keine
Prüfung abgelegt haben. Eine korrekte Anfrage lautet somit:
SELECT s.Name, s.MatrNr
FROM Studenten s
WHERE NOT EXISTS (SELECT *
FROM prüfen p
WHERE p.MatrNr = s.MatrNr
AND p.Note < 3.0);
4