Klausur Datenbanksysteme - Hochschule Ravensburg

Fachhochschule Ravensburg-Weingarten, Fachbereich Elektrotechnik und Informatik
Musterstudent
11111
Klausur Datenbanksysteme
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
«sname» «mnr»
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
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
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
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
«sname» «mnr»
-3-
b) Vervollständigen Sie nun den Trigger!
CREATE OR REPLACE TRIGGER korrigiere_Notenschnitt
/* Triggerzeitpunkt */
/* Trigger-Ereignis */
ON
/* Tabellenname */
FOR EACH ROW
DECLARE
BEGIN
SELECT
UPDATE
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
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.
«sname» «mnr»
-4-
int Summe_Belastungszulage (void)
{
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL END DECLARE SECTION;
unsigned int summe_zulage = 0; /* Variable fuer Rueckgabewert */
EXEC SQL DECLARE
/* Cursordeklaration */
}
«sname» «mnr»
-5-
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
K_Nr
Makler
K_Name
K_Adr
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“.
«sname» «mnr»
-6-
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
«sname» «mnr»
-7-
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Tabellenname:
Feldname
Constraint
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf
Primärschlüssel
Fremdschlüssel, Verweis auf