Datenbanksysteme I - Institut für Informatik Augsburg - Uni
Werbung
Universität Augsburg, Institut für Informatik Prof. Dr. W. Kießling L. Rudenko, Dr. F. Wenzel WS 2016/2017 05. Dez. 2016 Ü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 Fremdschlüsselbeziehung von Prüfung.Titel auf Vorlesung.Titel. Vorlesung (Titel : VARCHAR(10)) Prüfung (Student : VARCHAR(10), Titel : VARCHAR(10), Bestanden : VARCHAR(5)) Und fügen Sie folgende Datensätze ein: Prü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 fü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 Fächern durchgefallen? c) Welcher Kurs wurde von den meisten Studenten besucht? Aufgabe 2: Subquery in Join (Hausaufgabe) Manchmal kann man durch die Einfü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, hören. Verwenden Sie fü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 zulässt. Die Vorlesungen mit SWS >= 4 sollen nicht ausgegeben werden. Stellen Sie sicher, dass über den View auch keine Vorlesungen mit SWS >= 4 eingefügt werden können. c) Ermitteln Sie ü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 gehört haben. Formulieren Sie anschließend eine SQL-Anweisung, die herausfindet, in welchem Semester die Studenten von Sokrates sind. e) Versuchen Sie über Ihren View StudProf das Semester von Fichte auf 11 zu setzen. Was können Sie dabei beobachten und wieso? Aufgabe 4: CONSTRAINTS, CHECK (Präsenzaufgabe) Erstellen Sie fü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 über eine Check-Klausel sicher, dass der Stammkunde bei Kunde nicht NULL und nur 0 oder 1 sein kann. • Der Primärschlüssel für Kunde ist KdNr. • Bei Rechnung ist lediglich sicherzustellen, dass keiner der Einträge NULL sein kann. • KdNr von Rechnung soll nur eingetragen werden können, wenn auch in Kunde ein entsprechender Vermerk KdNr vorhanden ist. (Fremdschlü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 Primärschlüssel dienen soll. Schreiben Sie eine entsprechende SQL-Anweisung, die nach dem Anlegen obiger Relationen den Primärschlüssel hinzufügt. Vergeben Sie anschließend geeignete Primärschlüssel für die Rechnungen. Aufgabe 5: DDL und Inserts mit JDBC (Hausaufgabe) Anmerkung: Diese Aufgabe ist Voraussetzung für Aufgabe 6. Schreiben Sie eine Java-Klasse myDBConnection, die folgende Aufgaben übernimmt: a) Im Konstruktor der Klasse soll der Datenbanktreiber registriert und eine Datenbankverbindung geöffnet werden. Die Verbindung erfolgt dabei über die URL jdbc:oracle:thin:@gemini.informatik.uni-augsburg.de:1521:db Login und Passwort entsprechen Ihrer Matrikelnummer. Um den Datenbanktreiber registrieren zu können, benötigen Sie den JDBC-Treiber ojdbc6.jar, den Sie auf der Seite der Übungsblä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 Fremdschlüsselbeziehungen von Name zwischen Vorstellung und Buchung. Verwenden Sie für Datum den Datentyp Date. Für die restlichen Attribute wä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 erhält und zum Einfügen von Tupeln in die Relationen Vorstellung und Buchung dient. Verwenden Sie zum Einfügen der Tupel in die Relation Vorstellung ein PreparedStatement. Macht hier eine Transaktion Sinn? d) In der main-Methode soll das Erstellen der Relationen ausgeführt und mit Hilfe von Aufgabe c) folgende Tupel in die Datenbank eingefügt werden. Vorstellung Buchung Name Schneewittchen Aschenputtel Dornrö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 Dornröschen 60 30 DB1-Klausur 244 0 3 Aufgabe 6: Queries und Updates mit JDBC (Prä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 zurückgibt. Geben Sie diese Namen über die main-Methode auf der Console aus. b) Schreiben Sie eine Methode, die die Anzahl der freien Sitzplätze für eine bestimmte Vorstellung zurückliefert. Geben Sie die Anzahl der freien Sitzplätze von Dornröschen aus. c) Schreiben Sie eine Methode zur Belegung von Sitzplätzen, wobei die Anzahl der Sitzplätze als Parameter übergeben werden soll. Verwenden Sie folgende Signatur: public boolean bookSeats(String vorstellungs name, int anzahl plaetze) throws SQLException Dabei sollen Sitzplätze nur gebucht werden können, wenn hierfür noch genügend Plätze frei sind. Buchen Sie für Dornröschen zwei Sitzplätze. d) Implementieren Sie eine Methode, die das Datum (im deutschen Format) für eine bestimmte Vorstellung zurückliefert. Geben Sie den Termin für die DB1-Klausur aus. 4
