Prüfungsfach: Datenbanken Arbeitszeit: 120 Minuten Zugelassene Hilfsmittel: Skript Name:
Prüfungstermin: 13.07.2002 Vorname: Sem.:
Aufgabensteller: Prof. Dr. A. Tsakpinis Bitte deutlich schreiben!
Wenn du Fehler findest, oder eine bessere Lösung hast!
Zurück an eike@ehringer.de Aufgabe 1 Relationenmodell
Gegeben sind die beiden Tabellen Vertreter und Kunde. Kunden sind eindeutig einem Vertreter zugeordnet. Die erste Spalte bildet jeweils den Primärschlüssel.
Vertreter
Vertreternummer Name Gehalt
123 Kop 20000
234 Jannach 21000
NULL Meier 25000
331 Zanker 22000
Kunde
Kundennummer Name Ort Vertreter
1999-1 Müller München 331
1999-3 Huber München 331
1999-3 Maier München 200
1999-4 Felfernig Berlin 234
1999-5 Erkollar Berlin 212
a) Besitzen die Relationen (Tabellen) einen Fremdschlüssel? Falls ja: Geben Sie den Fremdschlüssel an.
Tabelle: Kunde Fremdschlüssel: Vertreter
b) Welche Zeilen in den Tabellen verstoßen gegen die Entitätsregel
(Integritätsbedingung!)? Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.
Tabelle: Vertreter Spalte: Vertreternummer, NULL Eintrag im Primärschlüssel
Tabelle: Kunde Spalte: Kundennummer, Doppelte Tupel: 1999-3 im Primärschlüssel
c) Welche Zeilen in den Tabellen verstoßen gegen die referentielle Integrität?
Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.
Tabelle: Kunde Spalte: Vertreter, Eintrag-Fremdschlüssel: 200,212 nicht im Primärschlüssel der Tabelle: Vertreter definiert.
d) Wird eine Integrität verletzt, wenn man folgenden Befehl ausführt (Begründen Sie Ihre Antwort):
INSERT INTO Kunde VALUES ('1994-6', 'Schwarzenegger', 'Berlin', 205);
Ja, der Wert Vertreter „205“ ist nicht in der Tabelle: Vertreter im Primärschlüssel definiert.
___________________________________________________________________________
Aufgabe 2 Relationale Operatoren, SQL
Einem Flugbuchungssystem liegen u.a. folgende Tabellen zugrunde:
SPFLI: Flugverbindungen SFLIGHT: Flugplan
Betrachten Sie bitte die folgenden Ausdrücke:
T1 := JOIN (SPFLI, SFLIGHT).
T2 := REST (T1, (seatsocc = 0)).
T3 := PROJ (T2, CARRID, CONNID, CITYFROM, CITYTO, FLDATE).
1. Formulieren Sie eine dafür passende Aufgabenstellung!
Gib alle Flüge aus ( mit CARRID,CONNID,CITYFROM,CITYTO,FLDATE) die noch keine Buchungen haben.
2. Geben Sie eine semantisch äquivalente SQL-Anweisung an!
SELECT SP.CARRID, SP.CONNID, SP.CITYFROM, SP.CITYTO, SF.FLDATE FROM SPFLI AS SP, SFLIGHT AS SF
WHERE seatsocc = 0;
___________________________________________________________________________
2
Aufgabe 3 SQL
Dieser Aufgabe liegen folgende Tabellen zugrunde:
Personal Auftrag
Bitte berechnen Sie das Ergebnis der folgenden Anweisungen:
a. SELECT COUNT(*) FROM Personal Group by Ort.
Ausgabe: 6
b. SELECT COUNT(*) FROM Personal Group by Ort
HAVING count (*) > 2.
Ausgabe: Regensburg
c. Select count(*) FROM Personal INNER JOIN Auftrag ON Auftrag.PersNr = Personal.PersNr.
Ausgabe: 5 ???????????
___________________________________________________________________________
Aufgabe 4 Normalisierung
Gegeben sei folgende Tabelle, die Daten von Bankkunden verwaltet:
KNR Name Vorname GebDatum Sparbuch Guthaben Zinssatz Filiale BLZ Filialenname Leiter
Bemerkungen
Ein Kunde kann mehrere Sparbücher haben. Jedes Sparbuch hat einen festgelegten Zinssatz.
In einem Sparbuch können mehrere Inhaber eingetragen sein (z. B. Eheleute).
Ein Sparbuch wird von einer Filiale der Bank ausgestellt.
a. Diese Tabelle befindet sich offensichtlich in der 1. Normalform. Befindet sich die Tabelle auch in der 2. Normalform? Begründen Sie Ihre Aussage!
b. Bitte überführen Sie die Tabelle in die 3. Normalform!
Aufgabe 5 ER-Diagramme, relationales Datenbankmodell, SQL Gegeben sei folgendes relationales Datenbankschema, das Kunden-, und Mitarbeiterdaten sowie Daten von Autoreparaturaufträgen verwaltet. Folgende Informationen sollen berücksichtigt werden:
Kundendaten: Kundenidentifikation, Name, Vorname, Telefonnummer Mitarbeiterdaten: Personalnummer, Name, Vorname, Abteilung (eindeutige
Zuordnung von Mitarbeitern zu Abteilungen)
Autodaten: Identifikation (wird bei der Erteilung des ersten Auftrags vergeben und bleibt für künftige Aufträge gleich), Modell und KFZ-Nummer
Reparaturaufträge: Datum des Reparaturauftrags, Reparaturkosten.
___________________________________________________________________________
4
Zusatzinformationen
Ein Reparaturauftrag bezieht sich auf ein einziges Auto.
Einem Mitarbeiter werden i.d.R. mehrere Reparaturaufträge zugeordnet, für einen Auftrag ist aber immer nur ein Mitarbeiter zuständig.
Ein Kunde kann mehrere Autos zur Reparatur bringen.
Ein Auto kann mehrere Reparaturaufträge verursachen.
___________________________________________________________________________
1. Bitte entwerfen Sie ein für die Aufgabenstellung geeignetes ER-Diagramm!
2. Führen Sie das ER-Diagramm in ein relationales Datenbankmodell über!
3. Geben Sie die Definition eines Sekundärschlüssels an! Geben Sie ein Beispiel für einen sinnvollen Sekundärschlüssel für die oben angegebene Datenbank!
___________________________________________________________________________
6
4. Formulieren Sie auf Basis folgende Anfragen in SQL:
a. Zeigen Sie den Reparaturauftrag für das Auto mit der Identifikation
‚SS625000‘.
b. Zeigen Sie alle Reparaturaufträge, die zwischen 1.5.1999 und 10.5.1999 abgeschlossen worden sind.
c. Zeigen Sie den Durchschnittswert der Reparaturkosten aller bisher abgeschlossenen Reparaturaufträge.
d. Zeigen Sie alle Reparaturaufträge, deren Kosten über dem Durchschnitt liegt.
___________________________________________________________________________
Aufgabe 6 Datenmodellierung (Vorlesungsbetrieb)
Gegeben sei folgendes relationales Datenbankschema, das Informationen zum Vorlesungsbetrieb einer Hochschule verwaltet. Folgende Informationen sollen berücksichtigt werden:
Studenten: Matrikelnummer, Name, Vorname, Semester
Assistenten: Personalnummer, Name, Vorname, Fachbereich, Raum Professor: Personalnummer, Name, Vorname, Fachbereich, Raum Vorlesung: Vorlesungsnummer, Anzahl Semesterwochenstunden, Titel Zusatzinformationen
Ein Assistent ist einem oder mehreren Professoren (jeweils zu einem Prozentsatz seiner Arbeitszeit) zugeordnet. Professoren können mehrere Assistenten haben.
Professoren halten i.d.R. mehrere Vorlesungen, es gibt aber auch Professoren, die Ämter begleiten bzw. forschungsfreies Semester haben und keine
Vorlesungen halten. Eine Vorlesung wird eindeutig einem Professor zugeschlagen.
Studenten belegen i.d.R. mehrere Vorlesungen, es gibt aber auch Studenten, die keine Vorlesungen belegen wollen bzw. dürfen (z.B. wegen ausstehender
Prüfungen).
Das Belegen einer Vorlesung kann von der erfolgreicher Prüfungsteilnahme an maximal einer anderen Vorlesungen abhängig sein.
Prüfungen sind mündlich. Die Studenten werden einzeln geprüft. Die Prüfung wird von einem Professor gehalten, der nicht notwendigerweise die Vorlesung selbst hält. Während der Prüfung ist ein Assistent anwesend, der ein
Prüfungsprotokoll anfertigt. Nach der Prüfung wird die Note festgelegt.
1. Bitte entwerfen Sie ein ER-Diagramm für diese Aufgabenstellung!
___________________________________________________________________________
8
2. Führen Sie das ER-Diagramm in ein relationales Datenbankmodell über!
3. Formulieren Sie SQL-Anweisungen, die folgende Fragen beantworten:
a. Prüfungsliste: Liste aller Prüfungen mit Angabe der Vorlesungsnummer, des Studenten (Matrikelnummer, Name), des Professors (Name,
Fachbereich) sowie des Prüfers (Name, Fachbereich) und seines Assistenten (Name, Fachbereich) sortiert nach dem Studentennamen.
b. Statistik: Liste der Prüfungen mit Anzahl der Prüfungsteilnehmer pro Prüfung.
c. Liste der Professoren, die mehr als 30 Prüfungen halten müssen.
___________________________________________________________________________
