Universität Konstanz

Universität Konstanz
Lehrstuhl DBIS
Prof. Marc H. Scholl / A. Seifert
Übungen zur Vorlesung
Informationssysteme
Wintersemester 01/02
3. Übungsblatt
Kontrolltermin: KW 46
Hinweis: Alle aktuellen Informationen dieser Veranstaltung sowie die ps/pdf-Version des
Übungsblattes können Sie auch im Web unter
http://www.inf.uni-konstanz.de/dbis/teaching/ws0102/informationssysteme abrufen.
1. Konfiguration Ihrer Systemumgebung für die Benutzung von SQL*Plus
In den nächsten Übungen wird das Datenbanktool SQL*Plus zum Einsatz kommen.
Voraussetzung für die Nutzung dieses Tools ist das richtige Setzen von diversen
Systemumgebungsvariablen. Wie diese zusetzen sind, beschreibt die Anleitung zu
SQL*Plus unter http://www.inf.unikonstanz.de/dbis/teaching/ws0102/informationssysteme/ (Rubrik
Dokumentation).
Nehmen Sie diese Anleitung als Basis und führen Sie die Anpassung Ihrer UNIXUmgebung durch.
Hinweis: Das Starten von SQL*Plus können Sie erst durchführen, wenn Sie einen
Datenbankaccount besitzen. Dieser wurde letzte Woche automatisch für alle
Studenten erzeugt, die sich über das Accounttool für diese Veranstaltung registriert
haben. Alle anderen Benutzer können nicht mit Oracle arbeiten!
2. Einfache Ausdrücke der Sprache SQL:
Punkte 5
Geben Sie für die folgenden Relationen-Algebraausdrücke jeweils einen
äquivalenten SQL-Ausdruck an (Papierübung):
a)
b)
c)
d)
e)
π{L#, KM} (σStadt='London' (Lieferanten))
π{T#, Farbe} (σGewicht<15 ∨ Gewicht>17 (Teile))
π{T#, LagerIn} (σGewicht<15 ∨ Gewicht>17 (Teile |×| Liefert))
π{T#, Anzahl} (σStadt='Paris' (Lieferanten) |×| Liefert)
π{Farbe} (σStadt='Paris' (Lieferanten) |×| Liefert |×| Teile)
3. Einfache und geschachtelte SQL-Anfragen I:
Punkte 6
Verschaffen Sie sich mit dem Befehl describe Schema-Informationen über die
Tabellen KURS, TEILNEHMER, KURSLEITER, ANGEBOT, NIMMT_TEIL,
FUEHRT_DURCH, VORAUSS der Beispiel-Datenbank KursDB.
Formulieren Sie die folgenden Anfragen zur Beispiel-Datenbank KursDB und
geben Sie die Ergebnisse der Anfragen an (mit Hilfe von SQL*PLUS ).
1
a) Welche Teilnehmer nehmen an solchen Kursen teil, die auch in deren
Wohnort angeboten werden?
b) Welche Teilnehmer nehmen an den Kursen 'Grundlagen I' oder
'Grundlagen II' teil (Ausgabe: Teilnehmer.*)?
c) Welche Teilnehmer nehmen nur an den Kursen 'Grundlagen I' oder
'Grundlagen II' teil (Ausgabe: Teilnehmer.*)?
d) Welche Teilnehmer nehmen an allen Kursen, die nicht 'Grundlagen I'
oder 'Grundlagen II' sind, teil (Ausgabe: Teilnehmer.*)?
Hinweis: Die Ergebnisse der folgenden Aufgaben können Sie in SQL*Plus mit dem Befehlspaar
SPOOL filename, SPOOL OFF in einer Datei mitprotokollieren.
4.
Einfache und geschachtelte SQL-Anfragen II:
Punkte 10
Betrachten Sie folgende Relationen als gegeben:
Student
SNummer
SName
Hauptfach
Semester
Veranstaltung
VName
Beginnt_um
Raum
FID
Besucht
SNummer
VName
Fakultät
FID
FName
SAlter
Die SQL-Statements, die diese Tabellen generieren und mit Werten fühlen, finden
Sie in der Datei ~seifert/oracleuebung/studentdb.sql. Kopieren Sie diese
Datei in Ihr Arbeitsverzeichnis und führen Sie es unter SQL*PLUS aus.
Formulieren Sie folgende Datenbankanfragen in SQL Syntax. Geben Sie dabei –
wenn möglich – sowohl die geschachtelte als auch die ungeschachtelte
Anfragevariante an. Schreiben Sie Ihre SQL Anfragen so, dass keine Duplikate im
Anfrageergebnis vorkommen:
a) Gesucht werden die Namen aller Studenten im 5. Semester, die
Lehrveranstaltungen der Fakultät „Informatik“ besuchen.
b) Gesucht ist das Alter des ältesten Studenten, der im Hauptfach „Biologie“
eingeschrieben ist und einen Kurs der Fakultät „Informatik“ besucht.
c) Finde die Bezeichnung aller Kurse (Veranstaltungen), die entweder im Raum
„D301“ stattfinden oder an denen vier oder mehr Studenten teilnehmen.
d) Finde die Namen aller Studenten, die an zwei Kursen teilnehmen, die zur
gleichen Zeit stattfinden (beginnen). (Hinweis: Es wird angenommen, dass
sich die Einträge in der DB auf einen Wochentag beziehen.)
2
e) Gesucht werden die Fakultäten, die weniger als fünf Studenten in einem
Ihrer angebotenen Kurse haben.
f) Gib das Semester und das Durchschnittsalter aller Studenten des jeweiligen
Semesters aus.
g) Gib das Semester und das Durchschnittsalter aller Studenten des jeweiligen
Semesters aus, ohne die Studenten im 5. Semester zu berücksichtigen.
h) Gesucht werden die Namen aller Studenten, die alle angebotenen
Kurse/Veranstaltungen besuchen.
i) Gesucht werden die Namen aller Studenten, die keinen angebotenen Kurs
besuchen.
j) Untersuchen Sie, ob mehrmals ein Student mit dem gleichen Namen in die
Datenbank aufgenommen wurde.
5. Einfügen von Datensätzen:
Punkte 4
a) Für die folgende Aufgabe werden die Tabellen Abteilung, Artikel,
bietet_an, Lieferant und liefert sowie sechs Constraints
(FKist_manager_von, Fkarbeitet_in, Fkbie_Art, Fklie_Lie und
FK_lie_Art) benötigt.
Die DDL-Statements, die diese Tabellen generieren, finden Sie in der Datei
~seifert/oracleuebung/liefert.sql. Diese Datei können Sie in Ihr
Arbeitsverzeichnis kopieren und unter SQL*PLUS ausführen lassen.
Sie können mit dem SQL*PLUS Befehl desc Tabellenname (desc ist die
Abkürzung für describe) feststellen, ob eine Tabelle schon existiert oder
nicht. Um die Constraints zu finden, können Sie die folgende SQL-Anfrage
an ORACLE stellen:
SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS;
b) Versuchen Sie einen Datensatz in die Tabelle Angestellte einzufügen. Zum
Beispiel:
INSERT INTO Angestellte VALUES ('P101', 'Schmidt, H.',
'Ring Str. 3, 78234 Konstanz', 'Abt-01');
Gibt es dabei ein Problem? Warum?
c) Versuchen Sie folgenden Datensatz in die Tabelle Angestellte einzufügen.
INSERT INTO Angestellte VALUES ('P101', 'Schmidt, H.',
'Ring Str. 3, 78234 Konstanz', NULL);
Ist dies Problemlos möglich? Warum?
d) Schalten Sie die Integritätsbedingung Fkarbeit_in aus, indem Sie das
folgende SQL-Kommando eingeben:
ALTER Table Angestellte DISABLE CONSTRAINT Fkarbeit_in;
3
Versuchen Sie nochmals den ersten Datensatz in die Tabelle Angestellte
einzufügen.
e) Versuchen Sie die Integritätsbedingung Fkarbeit_in mit
ALTER Table Angestellte ENABLE CONSTRAINT Fkarbeit_in;
wieder einzuschalten. Was passiert dabei?
4