Universität Konstanz

Universität Konstanz
Lehrstuhl DBIS
Prof. Marc H. Scholl / A. Seifert
Übungen zur Vorlesung
Informationssysteme
Wintersemester 01/02
4. Übungsblatt
Kontrolltermin: KW 47
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. Nullwerte, Aggregatfunktionen und Joins
Punkte 6
Gegeben ist folgender Datenbankauszug aus der Relation Studenten:
Studenten
SID
SName
SPunkte
SAlter
1
2
Karl
Maier
2
null
34
20
3
4
Geier
Hans
34
8
28
20
Erzeugen Sie die Tabelle Studenten und die dazugehörigen Tabellenwerte in Ihrem
Datenbankaccount. Nutzen Sie dazu das SQL-Skript unter
~seifert/oracleuebung/student.sql.
a) Schreiben Sie SQL-Statements, um die durchschnittliche Kreditpunktzahl,
die Summe aller Kreditpunkte und die Anzahl der Kreditpunkteinträge der
Tabelle Studenten zu berechnen. Wie verhalten sich die Aggregatfunktionen
bezüglich der Existenz von Nullwerten.
b) Ein Kommilitone von Ihnen hat die Aufgabe erhalten, alle Studenten aus der
DB zu erfragen, die mehr Kreditpunkte besitzen, als all diejenigen
Studenten, die jünger als 23 Jahre sind. Die beiden folgenden Statements
wurden von Ihrem Kommilitonen ausgearbeitet. Erzeugen beide Anfragen
das gleiche Ergebnis? Wenn nicht, erklären Sie warum (Gibt es Fälle, bei
denen beide Anfragen nicht das gleiche Ergebnis erzeugen?).
Select S.sname from Studenten s
where not exists
(Select * from studenten s1
where s1.salter < 23 and s.spunkte <= s1.spunkte);
Select s.sname from Studenten s
where s.spunkte > all
(Select s1.spunkte
from studenten s1 where s1.salter < 23);
c) Formulieren Sie folgende Join-Statements:
1
i. Bilden Sie den Left Outer Join der Relation Studenten mit sich selbst unter
Berücksichtigung der Join-Bedingung sid=sid.
ii. Bilden Sie den Right Outer Join der Relation Studenten mit sich selbst unter
Berücksichtigung der Join-Bedingung sid=sid.
iii. Bilden Sie den Left Outer Join der ersten beiden Tupel der Relation
Studenten mit den letzten beiden Tupeln unter Berücksichtigung der JoinBedingung sid=sid.
iv. Bilden Sie den Right Outer Join der ersten beiden Tupel der Relation
Studenten mit den letzten beiden Tupeln unter Berücksichtigung der JoinBedingung sid=sid.
2) DDL und DML Statements in Kombination:
Punkte 5
Gegeben sind folgende Relationenschemata:
Angestellter
AID
AName
AAlter
Abteilung
AbtID
AbtName
Bugdet
arbeitet
AID
AbtID
Stunden
Gehalt
Beantworten Sie die folgenden Fragen kurz und prägnant. Nach jeder
Fragestellung wird in Klammern darauf hingewiesen, ob die Aufgabe mit dem
Rechner (Oracle Datenbank) oder auf dem Papier gelöst werden soll.
a) Was versteht man unter Entity- und referentieller Integrität? Weshalb sind
diese zwei Integritätsbedingungen so wichtig? (Papier)
b) Formulieren Sie ein Beispiel für eine referentielle Integritätsbedingung, die sich
auf die Relation Abteilung bezieht. Geben Sie alle denkbaren Möglichkeiten an,
damit die gebildete referentielle Integritätsbedingung bewahrt wird, wenn ein
Anwender versucht, ein Abteilungstupel zu löschen. (SQL*PLUS, Papier)
c) Formulieren Sie SQL Statements, um die oben genannten drei Relationen
anzulegen. Achten Sie darauf, dass die notwendigen Entity- und referentiellen
Integritätsbedingungen enthalten sind. Beachten Sie, dass Sie die referentiellen
Integritätsbedingungen erst nach dem Anlegen der drei Relationstabellen
definieren können. Löschen Sie anschließend die angelegten Relationen wieder.
Beachten Sie dabei die Löschreihenfolge. (SQL*Plus)
d) Definiere eine Abteilungsrelation mit SQL, so dass garantiert ist, dass jede
Abteilung genau einen Abteilungsmanager zugewiesen bekommt. Löschen Sie
anschließend die angelegte Relation wieder. (SQL*PLUS)
e) Definiere eine Integritätsbedingung für die Relation Angestellter, die garantiert,
dass jeder Angestellte wenigstens 5000 DM verdient. Löschen Sie anschließend
die angelegte Relation wieder. (SQL*PLUS)
2
f) Stellen Sie sich vor, dass folgender View Senior_Angesteller definiert wurde:
Create View Senior_Angesteller (SName, SAlter, Gehalt)
As Select (A.AName, A.AAlter, A.Gehalt)
From Angestellter A
Where A.Alter>50
Erläutern Sie, was der Query Parser macht, wenn folgende Abfrage an die
Datenbank geschickt wird. (Papier)
Select S.SName
From Senior_Angestellter
Where S.Gehalt>100000
g) Kann ein SQL Update jeden Wert eines Relationenattributes ändern? Wenn dies
nicht der Fall ist, gib die Werte an, die nicht geändert werden können. (Papier)
h) Legen Sie die Relation Angestellter ohne Aktivierung der referentiellen
Integritätsbedingung wieder an. Formulieren Sie anschließend ein SQL
Statement, das „B. Maier“ mit der AID=101, Alter=45 und Gehalt=8000 der
Relation Angestellter hinzufügt. (SQL*Plus)
i) Formuliere Sie ein SQL Statement, das jedem Angestellten der Relation
Angestellter eine Gehaltserhöhung von 10 Prozent gewährleistet. (SQL*Plus)
j) Löschen Sie den angelegten Datensatz wieder. (SQL*Plus)
3) Views und Sicherheit mit SQL
Punkte 5
a) Gegeben ist folgendes Relationenschema:
Angestellter
AID
AName
AAlter
Gehalt
Stellen Sie sich vor, Sie müssen als Datenbankadministrator tätig werden. Zur
Vereinfachung besteht ihre Datenbank aus nur einer Relation. Dies ist die
erwähnte Angestellten-Relation. Aus firmenpolitischen Gründen möchte Ihr
Chef vermeiden, dass jeder Datenbankbenutzer herausbekommen kann, welcher
Mitarbeiter welches Gehalt erhält. Jedoch soll es jedem Benutzer erlaubt sein,
sowohl die Namen aller Angestellten als auch das Durchschnittsgehalt aller
Gehälter auszurechnen. Erarbeiten Sie einen Vorschlag, wie obige Forderung
effizient mittels Views realisiert werden kann. Definieren Sie dazu zunächst die
Tabelle Angestellter mit den dazugehörigen Attributen unter SQL*PLUS und
anschließend die dazugehörigen Sichten.
b) Gewähren Sie anschließend jedem Datenbankbenutzer das Recht, auf die
angelegten Sichten lesend zuzugreifen.
c) Ihr Chef möchte wissen, welche Angestellten weniger als 620 DM und welche
mehr als 6500 DM verdienen. Legen Sie dazu einen View an und gewähren Sie
Ihrem Chef (Seifert) das Recht, diesen View lesend zu benutzen. Außerdem
sollten Sie den User seifert die Möglichkeit geben, dass er das erworbene
Leserecht (Select-Recht) auch an andere DB-Benutzer weitergeben kann.
3
d) Als letztes möchte Ihr Chef wissen, wie Oracle das Weitergeben und Entziehen
von Rechten regelt. Als Ausgangsbasis der Betrachtung soll zunächst folgende
Abbildung dienen.
+
(1
R
0,
)
B
(3
0,
R+
)
(4
A*
(20
+
R
0,
)
E
D
, R+
)
+
C
(5
R
0,
) (6 0 ,
R+
)
F
Obige Abbildung ist wie folgt zu interpretieren: User A ist Eigentümer der
Relation Angestellter und hat zum Zeitpunkt 10 dem User B das Recht zum
Lesen (R) der Relation Angestellter mit der Erlaubnis der Propagierung dieses
Rechts (With Grant Option indiziert durch +) gegeben. Gleichfalls, jedoch zum
Zeitpunkt 20, hat A dem User C das Leserecht der Relation gewährleistet. Alle
weiteren Angaben der Abbildung können entsprechend abgeleitet werden.
Erarbeiten Sie experimentell Antworten auf folgende Fragestellungen:
i. Was passiert, wenn B dem User D das Leserecht für die Angestellten
Relation entzieht, d.h., welche User haben nach dem Revoke noch das
Leserecht der Relation?
ii. Wie reagiert Oracle, wenn C anschließend dem User D das Leserecht für die
Angestellten Relation entzieht?
Lösungshinweis: Um eine Antwort auf obige Fragestellung zu bekommen,
benötigen Sie mehrere DB-Accounts. Dazu stehen Ihnen folgende sechs Accounts
zur Verfügung a) $USER, b) $USER1, c) $USER2, d) $USER3, e) $USER4, f)
$USER5. Die Passwörter der Accounts entsprechen wieder den Usernamen.
4