Aufgabe 1: Updatable Views (1 P.)

Datenbankanwendung WS 2014/15
Prof. Dr.-Ing. Sebastian Michel
M. Sc. Johannes Schildgen
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme http://wwwlgis.informatik.uni-kl.de
Übungsblatt 7: Ausgabe 09.12.2014, Präsentation 18.12.2014
Aufgabe 1: Updatable Views
(1 P.)
1. Gegeben seien die von folgenden CREATE-VIEW-Statements erzeugten Views:
CREATE VIEW C 4 P r o f e s s u r e n AS SELECT ∗ FROM P r o f e s s o r e n WHERE Rang= ' C4 ' ;
CREATE VIEW P r o f e s s o r e n N a m e n AS SELECT Name FROM P r o f e s s o r e n ;
CREATE VIEW P r o f e s s o r e n N e g P e r s N r AS
SELECT −P e r s N r AS PersNr , Name , Rang , Raum FROM P r o f e s s o r e n ;
CREATE VIEW P r o f e s s o r e n U e b e r s i c h t AS
SELECT Name | | ' ( ' | | Rang | | ' ) ' AS B e s c h r e i b u n g FROM P r o f e s s o r e n ;
CREATE VIEW HoerenMitNamen AS
SELECT s . Name AS S t u d e n t , v . T i t e l AS V o r l e s u n g
FROM h o e r e n h , S t u d e n t e n s , V o r l e s u n g e n v
WHERE s . MatrNr = h . MatrNr AND v . V o r l N r = h . V o r l N r ;
Geben Sie jeweils mit Begründung an, ob folgende UPDATE-Statements in SQL 99 funktionieren
sollten. Gehen Sie dabei von dem Universitätsschema der Vorlesungsseite mit ausschließlich den auf
der Vorlesungsseite verlinkten Beispieldaten der VL-Datenbank aus.
(a) INSERT INTO C 4 P r o f e s s u r e n ( PersNr , Name , Rang , Raum)
VALUES ( 2 1 4 2 , ' S o k r a t e s ' , ' C4 ' , 2 2 0 1 ) ;
(b) INSERT INTO C 4 P r o f e s s u r e n ( PersNr , Name , Rang , Raum)
VALUES ( 2 1 4 3 , ' Schmidt ' , ' C2 ' , 2 2 0 2 ) ;
(c) UPDATE C 4 P r o f e s s u r e n SET Rang = ' C3 ' WHERE Name = ' S o k r a t e s ' ;
(d) INSERT INTO P r o f e s s o r e n N a m e n ( Name ) VALUES ( ' Hansen ' ) ;
(e) UPDATE P r o f e s s o r e n N e g P e r s N r
SET P e r s N r = P e r s N r + 1 WHERE Name = ' A r i s t o t e l e s ' ;
(f) INSERT INTO P r o f e s s o r e n N e g P e r s N r ( PersNr , Name , Rang , Raum)
VALUES ( −2152 , ' M o e l l e r ' , ' C4 ' , 5 0 6 ) ;
(g) UPDATE P r o f e s s o r e n U e b e r s i c h t SET B e s c h r e i b u n g = ' K o p e r n i k u s ( C4 ) '
WHERE B e s c h r e i b u n g = ' K o p e r n i k u s ( C3 ) ' ;
(h) INSERT INTO HoerenMitNamen ( V o r l e s u n g ) VALUES ( ' D a t a b a s e S y s t e m s ' ) ;
(i) INSERT INTO HoerenMitNamen ( S t u d e n t ) VALUES ( ' X e n o k r a t e s ' ) ;
(j) INSERT INTO HoerenMitNamen ( S t u d e n t , V o r l e s u n g )
VALUES ( ' A r i s t o x e n o s ' , ' I n f o r m a t i o n s s y s t e m e ' ) ;
2. Geben Sie ein SQL Statement an, mit welchem Sie in Postgres eine View VorlesungProfessor mit
dem folgenden Inhalt erzeugen können: Professoren P o
nP.P ersN r=V.gelesenV on Vorlesung V.
3. Da in Postgres Views nicht automatisch änderbar sind, erstellen Sie wie in der Vorlesung beschrieben
eine Rule, mit welcher das folgende Insert-Statement unterstützt wird:
1
Datenbankanwendung WS 2014/15
Prof. Dr.-Ing. Sebastian Michel
M. Sc. Johannes Schildgen
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme http://wwwlgis.informatik.uni-kl.de
Übungsblatt 7: Ausgabe 09.12.2014, Präsentation 18.12.2014
INSERT INTO V o r l e s u n g P r o f e s s o r
( PersNr , Name , Rang , Raum , V o r l N r , T i t e l , SWS )
VALUES ( 3 1 0 0 , ' Ovid ' , ' C4 ' , 1 2 6 , 3 0 0 1 , ' L i t e r a t u r ' , 3 ) ;
Aufgabe 2: JDBC
(1 P.)
Erstellen Sie ein Konsolen-Programm mit Java und unter der Verwendung von JDBC, welches das
Einfügen und Suchen von Fernbusreisen erlaubt. Verwenden Sie dazu das Schema der Fernbus-Tabelle
von Übungsblatt 61 .
CREATE TABLE f e r n b u s (
b u s n r INT PRIMARY KEY,
von VARCHAR( 3 1 ) ,
nach VARCHAR( 3 1 ) ,
d a u e r INT ,
−− i n Minuten
p r e i s INT ) ;
−− i n Euro
Anforderungen:
1. Das Programm soll als Konsolenparameter Servername, Port, Datenbankname, Benutzer und
Passwort zur Datenbankverbindung entgegennehmen.
2. Bei Programmstart soll die Anzahl der aktuellen Fernbusreisen angezeigt werden.
3. Der Benutzer hat die Wahl (z.B. durch Eingabe von 1 oder 2), eine Reise einzutragen oder zu
suchen.
4. Beim Eintragen müssen alle Attribute außer der Busnummer eingegeben werden. Ändern Sie das Datenbankschema so, dass diese automatisch hochzählt oder lassen Sie ihr Programm die nächsthöhere
Busnummer ermitteln.
5. Beim Eintragen wird automatisch auch die Rückreise eingefügt. Dies soll in der gleichen Transaktion
erfolgen wie das Einfügen der Hinreise.
6. Zur Suche muss der Benutzer zwei Städte eingeben (von und bis) und bekommt daraufhin alle
Attribute zu dieser Busreise angezeigt.
Optional:
7. Bei der Suche kann von oder nach oder beides leer gelassen werden. Dann werden alle Busreisen zu
einer bestimmten Stadt, alle von einer bestimmten Stadt bzw. die komplette Tabelle ausgegeben.
8. Suche einer Verbindung mit Umsteigen. Verwenden Sie dafür die rekursive SQL-Anfrage vom vorherigen Übungsblatt.
9. Bearbeiten- und Löschfunktion.
Bringen Sie ihr lauffähiges Programm in die Übung am 18.12.2014 auf Ihrem Laptop oder einem USBStick mit, sodass sie sowohl den Programmcode als auch das Programm im Betrieb präsentieren können.
1 Auf
der Vorlesungswebseite finden Sie unter “Extras zu Übung 6” das Tabellenschema samt Beispieldaten zum Download
2
Datenbankanwendung WS 2014/15
Prof. Dr.-Ing. Sebastian Michel
M. Sc. Johannes Schildgen
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme http://wwwlgis.informatik.uni-kl.de
Übungsblatt 7: Ausgabe 09.12.2014, Präsentation 18.12.2014
Aufgabe 3: User-Defined Functions
(1 P.)
Verwenden Sie auch für diese Aufgabe die Tabelle fernbus1 und erstellen Sie User-Defined Functions:
1. Eine Prozedur preiserhoehung(int zuschlag), die den Preis jeder Busreise pro voller Stunde
Reisedauer um zuschlag EUR erhöht. Beispiel: preiserhoehung(1) erhöht die Preise von Fernbusreisen, die weniger als 60 Minuten dauern, nicht, aber von Reisen, die z.B. 65 Minuten dauern,
um einen Euro, die 150 Minuten dauern, um zwei Euro, etc.
2. Eine Funktion teuerer(int, int), die zwei Busnummern als Parameter erhält und −1 ausgibt,
wenn die erste Fernbusreise günstiger als die zweite ist, 0, wenn die beiden Reisen gleich teuer sind
und 1, wenn die erste Reise teurer als die zweite ist.
3. Eine Tabellenfunktion anschluss(int), die als Parameter eine Busnummer entgegennimmt und
die Fernbusreisen anzeigt, die am Zielort der gegebenen Reise starten.
Aufgabe 4: JDBC - Zugriff auf Metadaten
(0 P.)
Erstellen Sie unter der Verwendung von JDBC ein Java-Programm, welches als Konsolenparameter Servername, Port, Benutzer, Passwort, den Datenbanknamen sowie einen Tabellennamen entgegennimmt
und ein CREATE TABLE-Statement ausgibt, um die Tabelle neu zu erstellen. Verwenden sie die Funktion getMetaData, um die Metadaten der Tabelle auszulesen.
Beispiel:
$ java JDBCMetadata localhost 5432 user password database fernbus
CREATE TABLE fernbus (busnr INT UNIQUE NOT NULL, von VARCHAR(31),
nach VARCHAR(31), dauer INT, preis INT, PRIMARY KEY(id));
Beachten Sie Primärschlüssel-, NOT NULL- und UNIQUE-Angaben. Fremdschlüsselbeziehungen müssen
nicht ausgegeben werden.
Bringen Sie ihr lauffähiges Programm in die Übung am 18.12.2014 auf Ihrem Laptop oder einem USBStick mit, sodass sie sowohl den Programmcode als auch das Programm im Betrieb präsentieren können.
3