Klausur Datenbanksysteme Musterlösung

Fachhochschule Ravensburg-Weingarten, Fachbereich Elektrotechnik und Informatik
Musterstudent
11111
Klausur Datenbanksysteme Musterlösung
Prüfer:
Datum:
Prof. Dr. Martin Hulin
11. Februar 2000
Dauer:
Hilfsmittel:
Aufgaben
90 Minuten
A
Punkte (ohne Gewähr)
Für die Aufgaben 1 bis 5 ist das relationale Datenbankschema einer Fachhochschule ausschnittsweise
gegeben. Es wird davon ausgegangen, dass jede Vorlesung von nur einem Dozenten gehalten wird.
Das Attribut „Vorgängerveranstaltung“ enthält bei zusammengehörigen Vorlesungen die V_Nr der
ersten Vorlesung, z. B. wird bei der Vorlesung „Mathematik 2“ auf die V_Nr der Vorlesung
„Mathematik 1" verwiesen.
Tabellenname: Dozent
Feldname
Constraint
PersNr
Primärschlüssel
D_Name
Gehalt
Tabellenname: Student
Feldname
Constraint
MatrikelNr
Primärschlüssel
S_Name
Studiengang
Notendurchschnitt
Tabellenname: Vorlesung
Feldname
Constraint
V_Nr
Primärschlüssel
DozentNr
Fremdschlüssel, Verweis auf Dozent (PersNr)
V_Name
Vorgängerveranstaltung Fremdschlüssel, Verweis auf Vorlesung (V_Nr)
Tabellenname: Teilnahme
Feldname
Constraint
V_Nr
Fremdschlüssel, Verweis auf Vorlesung (V_Nr)
MatrikelNr
Fremdschlüssel, Verweis auf Student (MatrikelNr)
Note
Musterstudent 11111
1. Aufgabe
-2-
(5 P)
Erstellen Sie eine SQL-Abfrage, die die Namen aller Vorlesungen ausgibt zusammen mit dem Namen
des Dozenten, der die Vorlesung hält.
SELECT V_Name, D_Name
FROM Vorlesung, Dozent
WHERE DozentNr = PersNr;
2. Aufgabe
(9 P)
Geben Sie eine SQL-Anweisung an, mit der die Namen aller Studenten angezeigt werden, die eine
Vorlesung bei dem Dozenten mit dem Namen „Manfred Meiselstein“ hören.
SELECT S_Name
FROM Student, Teilnahme, Vorlesung, Dozent
WHERE Student.MatrikelNr = Teilnahme.MatrikelNr AND
Teilnahme.V_Nr = Vorlesung.V_Nr AND
DozentNr = PersNr AND
D_Name = ‚Manfred Meiselstein‘;
3. Aufgabe
(6 P)
Geben Sie eine SQL-Anweisung an, mit der für alle Vorlesungen, die eine zugehörige
Vorgängerveranstaltung haben, in der ersten Spalte der Name der Vorlesung und der zweiten Spalte
der Name der Vorgängervorlesung ausgegeben wird.
SELECT V2.V_Name, V1.V_Name
FROM Vorlesung V1, Vorlesung V2
WHERE V2.Vorgängerveranstaltung = V1.V_Nr;
4. Aufgabe
(18 P)
In dem Datenbankschema gibt es das redundante Feld „Notendurchschnitt“ in der Tabelle „Student“.
Vervollständigen Sie den Trigger in Aufgabenteil b), der den Notendurchschnitt eines Studenten neu
berechnet und zwar beim Einfügen eines neuen Datensatzes in die Tabelle „Teilnahme“, beim
Löschen eines Datensatzes aus der Tabelle „Teilnahme“ und beim Ändern des Feldes „Note“ der
Tabelle „Teilnahme“. Ihre Lösung von Aufgabenteil a) können Sie in Teil b) leicht modifiziert
benutzen.
a) Geben Sie eine SQL-Anweisung an, die für den Studenten mit der MatrikelNr „12345“ seine
Notensumme und die Anzahl seiner Noten aus der Tabelle „Teilnahme“ berechnet.
SELECT SUM(Note), COUNT(Note)
FROM Teilnahme
WHERE MatrikelNr = 12345;
b) Vervollständigen Sie nun den Trigger!
CREATE OR REPLACE TRIGGER korrigiere_Notenschnitt
AFTER /* Triggerzeitpunkt */
INSERT OR DELETE OR UPDATE OF Note /* Trigger-Ereignis */
ON Teilnahme /* Tabellenname */
FOR EACH ROW
DECLARE
Schnitt INTEGER;
BEGIN
Musterstudent 11111
-3-
SELECT AVG (Note) INTO Schnitt
FROM Teilnahme
WHERE Teilnahme.MatrikelNr = :new.MatrikelNr OR
Teilnahme.MatrikelNr = :old.MatrikelNr;
UPDATE Student
SET Notendurchschnitt = Schnitt
WHERE Teilnahme.MatrikelNr = :new.MatrikelNr OR
Teilnahme.MatrikelNr = :old.MatrikelNr;
END;
5. Aufgabe
(20 P)
a) Erstellen Sie eine SQL-Abfrage, die für jeden Dozenten die PersonalNr und die Anzahl aller
Teilnehmer seiner Vorlesungen ausgibt. Sitzt ein Student in mehreren Vorlesungen des Dozenten,
so wird er mehrfach gezählt.
SELECT PersNr, Count (MatrikelNr)
FROM Teilnahme, Vorlesung
WHERE Teilnahme.V_Nr = Vorlesung.V_Nr
GROUP BY PersNr;
b) Das Ministerium plant eine leistungsgerechte Besoldung der Professoren. Da die Qualität der
Vorlesungen aber nur aufwendig zu ermitteln ist, wird zunächst eine Belastungszulage auf das
(niedrigere) Grundgehalt gewährt. Wer mehr als 150 Studenten unterrichtet, bekommt 50 DM
Zulage, wer mehr als 250 Studenten unterrichtet, bekommt 100 DM Zulage. Ergänzen Sie
folgende C-Funktion mit Embedded-SQL, die die Summe der Belastungszulagen aller
Professoren berechnet. Benutzen Sie dabei Aufgabenteil a) für eine Cursor-Deklaration. Sie
brauchen die Abfrage nicht nochmals hinzuschreiben, sondern können mit [a] darauf verweisen.
Die Anmeldung bei der Datenbank ist bereits erfolgt und die Struktur sqlca deklariert.
int Summe_Belastungszulage (void)
{
EXEC SQL BEGIN DECLARE SECTION;
int pnr, studzahl;
EXEC SQL END DECLARE SECTION;
unsigned int summe_zulage = 0; /* Variable für Rueckgabewert */
EXEC SQL DECLARE Studentenzahl CURSOR FOR [a];
/* Cursordeklaration */
EXEC SQL OPEN Studentenzahl;
EXEC SQL FETCH Studentenzahl INTO pnr, studzahl;
while (sqlca.sqlcode == 0)
{
if (studzahl > 250) summe_zulage + = 100;
else if (studzahl > 150) summe_zulage + = 50;
EXEC SQL FETCH Studentenzahl INTO pnr, studzahl;
}
EXEC SQL CLOSE Studentenzahl;
return (summe_zulage);
}
Musterstudent 11111
-4-
6. Aufgabe
(30 P)
Gegeben ist folgender Ausschnitt eines Entity-Relationship-Diagramm eines Maklerbüros, in dem
mehrere Makler angestellt sind.
M_Nr
M_Name
Makler
K_Nr
K_Name
K_Adr
Zeitpunkt
Kunde
(0, n)
(0, n)
Besichtigungs
termin
(0, n)
Wohnung_Haus
W_Nr
Preis
W_Adr
Ausstattung
a) Besichtigungstermin ist eine 3-stellige Relation, d. h. die Elemente der Relation sind Tripel
bestehend aus einem Makler, einem Kunden und einem Haus (oder Wohnung), das besichtigt
wird. Der Zeitpunkt eines Besichtigungstermins fehlt noch. Tragen Sie ihn an der richtigen Stelle
im Entity-Relationship-Diagramm ein!
b) Übersetzen Sie das Entity-Relationship-Diagramm in ein relationales Datenbankschema. Tragen
Sie dazu auf dem nächsten Blatt ein, welche Tabellen entstehen und welche Felder die Tabellen
haben. Bei jedem Feld können Sie ankreuzen, ob es sich um einen Primär- oder Fremdschlüssel
handelt. Bei einem Fremdschlüssel müssen Sie noch eintragen, auf welche andere Tabelle und
welches Feld in der anderen Tabelle er verweist.
Achten Sie auch auf die korrekte Implementierung des mehrwertigen Attributs „Ausstattung“.
Darin ist für jede Wohnung/Haus eine Liste von Ausstattungsdetails gespeichert, z. B.
„Parkettboden“, „Balkon“, „Markise“. Insbesondere soll eine einfache Suche nach
Ausstattungsdetails möglich sein, z. B.: „Suche alle Häuser mit Balkon und Fußbodenheizung“.
Musterstudent 11111
-5-
Tabellenname: Makler
Feldname
Constraint
M_Nr
Primärschlüssel
M_Name
Tabellenname: Kunde
Feldname
Constraint
K_Nr
Primärschlüssel
K_Name
K_Adr
Tabellenname: Wohnung_Haus
Feldname
Constraint
W_Nr
Primärschlüssel
Preis
W_Adr
Tabellenname: Ausstattung
Feldname
Constraint
W_Nr
Fremdschlüssel, Verweis auf Wohnung_Haus (W_Nr)
Ausstattungsdetail
Tabellenname: Besichtigungstermin
Feldname
Constraint
M_Nr
Fremdschlüssel, Verweis auf Makler (M_Nr)
K_Nr
Fremdschlüssel, Verweis auf Kunde (K_Nr)
W_Nr
Fremdschlüssel, Verweis auf Wohnung_Haus (W_Nr)
Zeitpunkt