Datenbanksysteme I - Institut für Informatik Augsburg - Uni

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
L. Rudenko, Dr. F. Wenzel
WS 2016/2017
05. Dez. 2016
Übungsblatt 7
Datenbanksysteme I
Aufgabe 1: SQL-Anfragen (Hausaufgabe)
Erstellen Sie mit Hilfe des SQL Developers die folgenden zwei Relationen in Ihrem DB-Account.
Beachten Sie dabei die Fremdschlüsselbeziehung von Prüfung.Titel auf Vorlesung.Titel.
Vorlesung (Titel : VARCHAR(10))
Prüfung (Student : VARCHAR(10), Titel : VARCHAR(10), Bestanden : VARCHAR(5))
Und fügen Sie folgende Datensätze ein:
Prüfung
Vorlesung
Titel
’Mathematik’
’Informatik’
’Physik’
Student
’Bernd’
’Bernd’
’Bernd’
’Klaus’
’Klaus’
’Elmar’
’Elmar’
Titel
’Mathematik’
’Informatik’
’Physik’
’Mathematik’
’Informatik’
’Mathematik’
’Informatik’
Bestanden
’Ja’
’Nein’
’Nein’
’Nein’
’Nein’
’Ja’
’Ja’
Geben Sie SQL-Statements für die folgenden Anfragen an:
a) Welche Studenten haben alle angebotenen Vorlesungen besucht?
b) Welche Studenten haben nur in Mathematik bestanden, aber sind in allen anderen belegten
Fächern durchgefallen?
c) Welcher Kurs wurde von den meisten Studenten besucht?
Aufgabe 2: Subquery in Join (Hausaufgabe)
Manchmal kann man durch die Einführung eines Joins eine sogenannte Entschachtelung einer korrelierten Unteranfrage erzielen. Formulieren Sie folgende Anfrage unter Verwendung eines Joins
um, so dass das Resultat das gleiche ist.
SELECT a.*
FROM Assistenten a
WHERE EXISTS (SELECT p.*
FROM Professoren p
WHERE a.Boss = p.PersNr
AND p.GebDatum < a.GebDatum);
1
Aufgabe 3: Views (Hausaufgabe)
a) Erstellen Sie in Ihrem DB-Account lokale Kopien der folgenden Relationen: Studenten, Professoren, Vorlesungen, hören. Verwenden Sie für Ihre Relationen eigene Namen (siehe Aufgabe
1), z.B. Studenten [matrNr]. Die Relationen kann man wie folgt kopieren:
CREATE TABLE Studenten_matrNr AS (SELECT * FROM Studenten);
b) Erstellen Sie einen View Vorlesungen klein, der nur die Sicht auf VorlNr, Titel und SWS der
Relation Vorlesungen matrNr zulässt. Die Vorlesungen mit SWS >= 4 sollen nicht ausgegeben werden. Stellen Sie sicher, dass über den View auch keine Vorlesungen mit SWS >= 4
eingefügt werden können.
c) Ermitteln Sie über Ihren View, wie viele Vorlesungen es mit SWS = 1, 2 oder 3 gibt.
d) Erstellen Sie eine View StudProf, der Studenten mit den Professoren assoziiert, bei denen sie
Vorlesungen gehört haben. Formulieren Sie anschließend eine SQL-Anweisung, die herausfindet, in welchem Semester die Studenten von Sokrates sind.
e) Versuchen Sie über Ihren View StudProf das Semester von Fichte auf 11 zu setzen. Was können
Sie dabei beobachten und wieso?
Aufgabe 4: CONSTRAINTS, CHECK (Präsenzaufgabe)
Erstellen Sie für eine Restaurant-Datenbank folgende zwei Tabellen:
Kunde (KdNr : Integer, Stammkunde : Integer)
Rechnung (KdNr : Integer, ReDatum : Date, Betrag : Numeric(7, 2))
a) Erweiteren Sie dieses Schema um folgende Constraints:
• Leider gibt es den Datentyp BOOLEAN in Oracle SQL nicht. Stellen Sie deshalb über eine
Check-Klausel sicher, dass der Stammkunde bei Kunde nicht NULL und nur 0 oder 1 sein
kann.
• Der Primärschlüssel für Kunde ist KdNr.
• Bei Rechnung ist lediglich sicherzustellen, dass keiner der Einträge NULL sein kann.
• KdNr von Rechnung soll nur eingetragen werden können, wenn auch in Kunde ein entsprechender Vermerk KdNr vorhanden ist. (Fremdschlüssel!)
b) Tragen Sie nun folgende Werte in die Tabellen ein:
Rechnung
Kunde
KdNr
212
133
716
Stammkunde
1
1
0
2
KdNr
212
212
212
212
212
133
133
716
ReDatum
’12-DEC-1999’
’12-DEC-1999’
’10-DEC-1999’
’29-OCT-1999’
’01-OCT-1999’
’29-NOV-1999’
’30-OCt-1999’
’29-OCT-1999’
Betrag
30.12
31.17
31.54
19.03
33.89
55.11
19.59
19.34
c) Der Relation Rechnung soll jetzt um ein numerisches Attribt erweitert werden, welches als
Primärschlüssel dienen soll. Schreiben Sie eine entsprechende SQL-Anweisung, die nach dem
Anlegen obiger Relationen den Primärschlüssel hinzufügt. Vergeben Sie anschließend geeignete
Primärschlüssel für die Rechnungen.
Aufgabe 5: DDL und Inserts mit JDBC (Hausaufgabe)
Anmerkung: Diese Aufgabe ist Voraussetzung für Aufgabe 6.
Schreiben Sie eine Java-Klasse myDBConnection, die folgende Aufgaben übernimmt:
a) Im Konstruktor der Klasse soll der Datenbanktreiber registriert und eine Datenbankverbindung
geöffnet werden. Die Verbindung erfolgt dabei über die URL
jdbc:oracle:thin:@gemini.informatik.uni-augsburg.de:1521:db
Login und Passwort entsprechen Ihrer Matrikelnummer.
Um den Datenbanktreiber registrieren zu können, benötigen Sie den JDBC-Treiber ojdbc6.jar,
den Sie auf der Seite der Übungsblätter zum Download finden. Binden Sie diesen in Ihren
Klassenpfad ein.
b) Schreiben Sie eine Methode, die folgende Relationen auf der Datenbank erstellt:
Vorstellung(Name, Datum)
Buchung(Name, Sitzplaetze Maximal, Sitzplaetze Gebucht)
Achten Sie auf die zyklische Fremdschlüsselbeziehungen von Name zwischen Vorstellung und
Buchung. Verwenden Sie für Datum den Datentyp Date. Für die restlichen Attribute wählen Sie
geeignete Datentypen. Fassen Sie die beiden Relationen nicht zusammen.
c) Implementieren Sie eine Methode, die die Parameter Name, Datum, Anzahl Sitzplaetze, Sitzplaetze Gebucht erhält und zum Einfügen von Tupeln in die Relationen Vorstellung und Buchung dient. Verwenden Sie zum Einfügen der Tupel in die Relation Vorstellung ein PreparedStatement. Macht hier eine Transaktion Sinn?
d) In der main-Methode soll das Erstellen der Relationen ausgeführt und mit Hilfe von Aufgabe
c) folgende Tupel in die Datenbank eingefügt werden.
Vorstellung
Buchung
Name
Schneewittchen
Aschenputtel
Dornröschen
DB1-Klausur
Datum
23.12.2005
06.01.2006
11.01.2006
07.02.2017
Name
Anzahl Sitzplaetze Sitzplaetze Gebucht
Schneewittchen
80
23
Aschenputtel
100
22
Dornröschen
60
30
DB1-Klausur
244
0
3
Aufgabe 6: Queries und Updates mit JDBC (Präsenzaufgabe)
Erweitern Sie Ihr JDBC-Programm aus Aufgabe 2) um folgende Funktionen:
a) Schreiben Sie eine Methode mit der Signatur public String[] getShowNames(), die alle Vorstellungsnamen zurückgibt. Geben Sie diese Namen über die main-Methode auf der Console
aus.
b) Schreiben Sie eine Methode, die die Anzahl der freien Sitzplätze für eine bestimmte Vorstellung
zurückliefert. Geben Sie die Anzahl der freien Sitzplätze von Dornröschen aus.
c) Schreiben Sie eine Methode zur Belegung von Sitzplätzen, wobei die Anzahl der Sitzplätze als
Parameter übergeben werden soll. Verwenden Sie folgende Signatur:
public boolean bookSeats(String vorstellungs name, int anzahl plaetze) throws SQLException
Dabei sollen Sitzplätze nur gebucht werden können, wenn hierfür noch genügend Plätze frei
sind. Buchen Sie für Dornröschen zwei Sitzplätze.
d) Implementieren Sie eine Methode, die das Datum (im deutschen Format) für eine bestimmte
Vorstellung zurückliefert. Geben Sie den Termin für die DB1-Klausur aus.
4