Universität Konstanz
Werbung
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
