Prüfungsfach: Datenbanken Arbeitszeit: 120 Minuten Zugelassene Hilfsmittel: Skript Name:
Prüfungstermin: 31.01.2003 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 sei folgendes relationale Schema einer Studentendatenbank:
Student
Matrikelnummer Name Geschlecht Wohnort
123456 Kop M Regensburg
234567 Jannach W Regenstauf
NULL Meier M Lappersdorf
331123 Zanker W Regensburg
Vorlesung
Volesungsnummer Name Fachbereich
100 Datenbanken Informatik
101 Programmiersprachen Informatik
200 Rechnungswesen BW
Einschreibung
Volesungsnummer Student
100 123456
100 234567
101 332211
200 222312
105 123456
a) Besitzen die Relationen (Tabellen) einen Fremdschlüssel? Falls ja: Geben Sie den Fremdschlüssel an.
Tabelle: Einschreibung Fremdschlüssel: Student
b) Welche Zeilen in den Tabellen verstoßen gegen die Entitätsregel (Integritäts- bedingung!)? Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.
Tabelle: Student Spalte – Matrikelnummer - Primärschlüssel: NULL
Tabelle: Einschreibung Spalte – Vorlesungsnr., Primärschlüssel nicht eindeutig 100,100 c) Welche Zeilen in den Tabellen verstoßen gegen die referentielle Integrität?
Geben Sie die Zeilen an und begründen Sie Ihre Entscheidung.
Beide Matrikelnummern sind Fremdschlüssel, die nicht durch den Primärschlüssel in der Tabelle: Student definiert sind.
Tabelle: Einschreibung Spalte: Vorlesungsnr. Zeile: 105 Diese Forlesungsnr. ist nicht in der Tabelle: Vorlesung definiert.
d) Wird eine Integrität verletzt, wenn man folgenden Befehl ausführt (Begründen Sie Ihre Antwort):
INSERT INTO Einschreibung VALUES (102, 331123);
Ja, da keine Vorlesung „102“ als Primärschlüssel in der Tabelle: Vorlesung definiert ist.
Aufgabe 2 Normalisierung
Gegeben sei folgendes Relationenschema mit Informationen über Vorlesungen und Studierende:
Vorlesung
Vorlesungsnummer Student Vorlesung Studentenname Fachbereich
100 123456 Datenbanken Kop Informatik
100 234567 Datenbanken Jannach Informatik
101 332211 Programmiersprachen Zanker BW
101 123456 Programmiersprachen Kop
Student
Matrikelnummer Name
123456 Kop
234567 Jannach
NULL Meier
331123 Zanker
1. Erfüllt diese Relation die 1. und die 2. Normalform? Wenn nicht, dann führen Sie bitte die Relation in die 2. Normalform! Begründen Sie Ihre Aussagen!
1. Normalform: Ja
2. Normalform: Nein, da die Vorlesung nur von der Vorlesungsnummer abhängig ist.
Vorlesungsnummer -> { Vorlesung , Fachbereich }
Student -> { Studentenname }
Vorlesungsnummer , Matrikelnr
2. Existieren Redundanzen bei der Abspeicherung von Daten im Relationalen Datenmodell, so kann es zu sog. Anomalien kommen. Welche Anomalien kennen Sie? Erklären Sie diese anhand des hier angegebenen Beispiels Vorlesung.
UPDATE-A.: Wenn sich der Vorlesungsname ändert, muss der öfters geändert werden.
INSERT-A: Mann kann keine Vorlesung anlegen für die noch kein Student eingeschrieben ist.
DELETE-A: Wenn man die Einschreibungen der Studenten in der Vorlesung löscht, gibt es keine Vorlesung mehr.
Aufgabe 3 SQL
1. Welches Ergebnis liefert die folgende SQL-Abfrage:
SELECT Vorlesungsnummer, Vorlesung, Fachbereich FROM Student
WHERE Fachbereich = ‚Informatik’ OR Fachbereich = ‚BW’.
Diese Abfrage gibt gar nichts aus, da in der Tabelle Student die WHERE Klausel mit ‚Fachbereich’
absolut ins leere greift!
2. Formulieren Sie eine SQL-Abfrage, die für alle Studenten die Matrikelnummer und die Anzahl der Vorlesungen auflistet, die ein Student belegt hat.
SELECT Student, COUNT(Vorlesungsnummer) AS Anzahl_Vorlesung
FROM Vorlesung
GROUP BY Student;
3. Formulieren Sie eine SQL-Abfrage, die für alle Vorlesungen die eingeschriebenen Studenten herausfindet. Das Ergebnis soll alphabetisch nach dem Vorlesungs- namen sortiert werden.
SELECT Vorlesung, Studentenname
FROM Vorlesung
GROUP BY Vorlesung;
4. Der Student Zanker will sich für die Vorlesung Datenbanken einschreiben.
Vervollständigen Sie den folgenden SQL-Ausdruck, so dass diese Daten in die Datenbank eingefügt werden.
INSERT INTO Vorlesung VALUES (100,331123,Datenbanken,Zanker,BW)
SELECT
Aufgabe 4 Relationale Operatoren
Gegeben sei folgendes Relationenmodell einer Bank:
Filiale
FNR BLZ Filialname Leiter
Konto
KTNR Saldo Dispo Kunde Filiale
Kredit
DispoNr Betrag Rate Zins Kunde Filiale Sparbuch
SNR Guthaben Zinssatz Kunde Filiale
Stellen Sie an die Datenbank folgende Anfragen mittels Relationenalgebra:
1. Listen Sie die Kontonummern auf, die Kunden mit dem Namen ‚Meier’ gehören.
T1 := REST ( Konto, Kunde=“Meier“ )
T2 := PROJ ( T1, KTNR )
??? ob das reicht ???
2. Bitte beschreiben Sie die Fragenstellung, die im folgenden Ausdruck codiert ist:
T1 = Rest (Sparbuch, (Guthaben > 5000)).
T2 = Join (T1, (Filiale = FNR), Filiale).
T3 = PROJ (T2, Filialname).
Ausgegeben wird, eine Liste mit Filialen bei denen Sparbücher mit mehr als 5000 geführt werden.
3. Bitte geben Sie eine semantisch äquivalente SQL-Anweisung an!
SELECT FI.Filialname
FROM Sparbuch AS SP INNER JOIN Filiale AS FI ON ( Filiale = FNR) WHERE Guthaben > 5000;
Aufgabe 5 (Datenunabhängigkeit)
Bitte kreuzen Sie die richtigen Antworten an (es kann mehrere richtige Antworten geben!). Falsche Angaben werden negativ benotet.
1. Um ein möglichst großes Maß an Datenunabhängigkeit zu erreichen, sollen drei verschiedene Datenebenen realisiert werden:
Externes, konzeptionelles und internes Schema
Attribute, Relationen und Konsistenzbedingungen
Dateisystem, Speichersystem und Zugriffskontrolle 2. Eine Transaktion
Wird entweder ganz oder gar nicht ausgeführt
Ist eine atomare Datenbankoperation
Ist eine Menge von Datenbankoperationen, die zu einem Zeitpunkt parallel ausgeführt werden können
Ist eine Folge von Datenbankoperationen, die die Datenbank von einem konsistenten (d.h. korrekten) Zustand zum nächsten konsistenten Zustand überführen.
Aufgabe 6 ER-Diagramme, relationales Datenbankmodell, SQL
Die Datenanalyse bei einer Luftverkehrgesellschaft hat folgendes ergeben: Der ge- samte Luftverkehr ist in Flugverbindungen unterteilt. Eine Flugverbindung stellt die Verbindung zwischen zwei Orten mit einer Fluggesellschaft zu einer bestimmten Abflug- und Ankunftszeit her. Für jede Flugverbindung wird täglich ein Flugplan gefertigt. Dieser enthält alle Flüge für ein bestimmtes Datum. Flügen ist eindeutig ein Flugzeugtyp zugeordnet. Jedes Flugzeug hat abhängig von seinem Typ eine
bestimmte Anzahl von Sitzplätzen der 1. und der 2. Klasse und wird mit Begleit- personal ausgestattet. Für die Bemannung der Flugzeuge existieren verschiedene Typen, die jeweils die Anzahl der Piloten und Stewardessen festlegen. Um die Reservierung von Flügen zu erleichtern, werden alle noch offenen Flüge gesondert geführt, mit der noch frei verfügbaren Kapazität an Plätzen der 1. und 2. Klasse. Bei Buchungen muss der Flug und die Agentur angegeben werden. Passagiere werden mit ihrem Namen, Adresse und Telefonnummer registriert.
Das Personal der Fluggesellschaft ist unterteilt in Flugpersonal und Bodenpersonal.
Flugpersonal muss über eine Flugerlaubnis verfügen und darf eine maximale Anzahl von Flugstunden nicht übersteigen. Bodenpersonal hat im Gegensatz zu Flug- personal einen bestimmten Einsatzort.
Für das Flugpersonal wird ein Flugdienstplan gefertigt, in dem für jeden Flug das Flugpersonal mit jeweils einem Vertreter aufgeführt sind.
Identifizieren Sie die im Anwendungsbereich vorkommenden Objekte und Objekttypen, ihre Merkmale sowie ihre Beziehungen zueinander.
Zeichen Sie die ER-Diagramme
Führen Sie das ER-Modell in ein relationales Datenbankschema über.
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 zuge- schlagen.
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).
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üfungs- protokoll anfertigt. Nach der Prüfung wird die Note festgelegt.
1. Bitte entwerfen Sie ein ER-Diagramm für diese Aufgabenstellung!
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, Fach- bereich) 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.
