Klausur Informationssysteme Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Ich möchte mein Prüfungsergebnis per Email erhalten. Termin: Prüfungsdauer: Erlaubte Hilfsmittel: 22. Februar 2006 / 10–12 Uhr 120 Min. keine Aufgabe max. Punkte Punkte A 10 B 24 C 13 D 15 E 14 F 10 14 XG Punkte (max. 100) Beurteilung: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Prüfer: Prof. Marc H. Scholl Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . A Datendefinition in SQL (2 + 5 + 3 Punkte) Ein Betreiber eines Internet-Shops möchte die Bestellungen in einer relationalen Datenbank verwalten. Wie im folgendem ER-Diagramm abgebiltet, werden die Bestellungen von einzelnen Kunden gemacht. Eine Bestellung besteht aus einem oder mehreren Artikeln und für jeden Artikel muss die Anzahl (Stück) eingegeben werden. 1 Vervollständigen Sie das ER-Diagramm um die Kardinalitäten der Beziehungen in [min, max] -Notation. 2 Überführen Sie das obige Modell in relationale Tabellen (verwenden Sie die Notation TABELLENNAME (attr1, attr2, ...)). Spezifizieren Sie dabei sämtliche Primär- und Fremdschlüsselbeziehungen (durch das Untersteichen des Primärschlüssels und einen Pfeil zu dem(den) referenzierten Attribut(-en) bei einem Fremdschlüssel). Geben Sie die SQL-Anweisung zum Anlegen der Tabelle an, welche die “enthält”Beziehung zwischen Bestellungen und Artikel abbildet. Spezifizieren Sie dabei sämtliche Constraints (Integritätsbedingungen). 3 Definieren Sie in SQL eine Sicht Rechnungen(BestellNr, Rechnungssumme), die den Gesamtwert jeder Bestellung berechnet. Dieser Wert ergibt sich aus der Summe der Bruttopresie für jeden bestellten Artikel (der Bruttopreis pro Artikel ist (1 + 0, 01 ∗ MwS t) ∗ Netto Preis ∗ S tueck). Nun möchten Sie der Benutzergruppe ‘Verkaeufer’ das Recht einräumen, auf die Sicht Rechnungen lesend zuzugreifen. Geben Sie die notwendige Anweisung in SQL-Syntax an. Informationssysteme 2 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . B Anfragesprachen (4 + 6 + 6 + 4 + 4 Punkte) An einer Universität werden die Prüfungsleistungen der Studierenden in einer relationalen Datenbank mit dem folgenden Schema verwaltet: Student (MatrNr, Name, Geburtsdatum, Studiengang, Fachsemester) Betreuer (PersonalNr, Name, Akadem Titel, Fachbereich) Veranstaltung (VeranstID, Veranst Titel, Typ, SWS, PersonalNr*) Ergebnis (VeranstID*, MatrNr*, Datum, Note) Die mit * markierten Attribute sind Fremdschlüssel, die auf die gleichbenannten Primärschlüssel verweisen. Achtung: Verwenden Sie bei allen SQL Anfragen DISTINCT genau dann, wenn Duplikate im Ergebnis nicht ausgeschlossen wären! Formulieren Sie die folgende Anfrage in Tupel-Relationen-Kalkül und SQL. 1 Finden Sie alle Prüfungsfälle, in denen ein Studierender im 1. Fachsemester eine 1.0 bekommen hat (Ausgabe: MatrNr, Name, Veranst Titel ). Formulieren Sie die folgenden Anfragen in Relationen-Algebra und SQL. 2 Welche Studierenden des Studiengangs Geschichte/Bachelor haben an keiner Prüfung teilgenommen? Geben Sie MatrNr, Name und Fachsemester aus. 3 Geben Sie alle Studenten und Mitarbeiter aus, deren Name mit Meier oder Maier endet (Ausgabe: Name, in SQL bitte alphabetisch sortiert). Verwenden Sie dabei in der Relationenalgebra den gleichen Operator für Stringvergleiche wie in SQL. Formulieren Sie die folgenden zwei Anfragen nur in SQL: 4 Bei welchen Betreuern liegt die durschnittlich vergebene Note oberhalb von (d.h. ist kleiner als) 2.3? Geben Sie Name und Fachbereich aus. 5 Welcher Studiengang hat die größte Zahl von Studenten (Ausgabe: Studiengang, Zahl ), die bei keiner Prüfung durchgefallen sind? Achten Sie darauf, jede Person nur einmal zu zählen! Informationssysteme 3 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . C Rekursive Anfragen (2 + 4 + 7 Punkte) Ein Autohersteller verwaltet die Komponenten eines Automodells mit Hilfe zweier Relationen: Komponente (Id, Bezeichnung, Code) Bestandteil von (ChildId*, ParentId*, Anzahl) Bestandeil von beschreibt die hierarchischen Beziehungen zwischen einzelnen Komponenten eines Autos (ParentId enthält ChildId Anzahl mal). Leider wird rekursives SQL von diesem Datenbanksystem nicht unterstützt. 1 Formulieren Sie eine SQL-Anfrage, die alle unmittelbaren Bestandteile (Ausgabe: Bezeichung, Code ) der Komponente Motor findet. Um rekursive Anfragen zu ermöglichen, hat ein Praktikant die hierarchische Struktur in Bestandteil von mit einem Preorder/Size -Verfahren kodiert. Dabei ist die folgende neue Tabelle Bestandteile enstanden, die die ursprüngliche Tabelle Bestandteil von ersetzt: pre ChildId Anzahl size level 0 1 2 ... 1 2 17 ... NULL 1 4 ... 5620 391 24 ... 1 2 3 ... Preorder/Size-Kodierung hat eine wertvolle Eigenschaft: Teil x ist ein Bestandteil (unmittelbar oder rekursiv!) von Teil y wenn prex > prey und prex <= prey + sizey . Damit können Sie rekursive Anfragen wie die folgende in SQL formulieren: 2 Finden Sie rekursiv alle Bestandteile (Id, Bezeichung ) der Komponente Motor. 3 Der Autohersteller will auf ein Logik-basiertes System umsteigen. (a) Skizzieren Sie mit ein paar Beispiel-Einträgen, wie sich das urprüngliche Szenario des Automobilherstellers als Prolog-Fakten modellieren lässt. (b) Basiert auf Ihrer in (a) skizzierten Faktendarstellung, definieren Sie eine Prolog-Regel enthalten_in(Unterteil, Oberteil), so dass Unterteil ein unmittelbarer oder rekursiv enhaltener Bestandteil von Oberteil ist. (c) Formulieren Sie nun ein Prolog-Prädikat motor_teil(Id) , das die Id aller Bestandteile der Komponente Motor zurückgibt. Informationssysteme 4 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . D Datenbank-Architektur (2 + 3 + 4 + 6 Punkte) 1 Erklären Sie kurz Aufgabe und Funktionsweise des “Buffer Managers”. Welche Rolle hat seine “Ersetzungsstrategie”? Ein DBMS verwendet einen Buffer-Manager aus 8 Frames mit LRU (“Least Recently Used”) als die Ersetzungsstrategie. Ein Nested-Loop-Join zweier Relationen R und S wird ausgeführt. R besteht aus 20 Seiten (r1, r2 , ...,r20 ) und S aus 6 Seiten ( s1, s2 , ..., s6 ). 2 Für R 1 S (d.h. mit S in der inneren Schleife), zeigen Sie in der bereitgestellten Skizze den Zustand des Buffers nachdem dieser zum ersten Mal voll wurde. Markieren Sie die Seite, die von LRU als das erste “Opfer” bestimmt wurde. frame # page # 0 1 2 3 4 5 6 7 Wie viele “cache hits” und “cache misses” entstehen durch die Ausführung der gesamten Join-Operation? 3 Wiederholen Sie die vorige Aufgabe für S 1 R (d.h. mit R in der inneren Schleife). frame # page # 0 1 2 3 4 5 6 7 Schlagen Sie eine für diesen Fall bessere Ersetzungsstrategie vor. Begründen Sie den Vorschlag durch die Abschätzung der “cache hits” und “cache misses”. + 4 Bei einem B - oder ISAM-Index werden pro Indexseite n Attributwerte und n + 1 Pointer auf die unterliegenden Indexseiten gespeichert. Ein Datenbanksystem arbeitet mit einer Seitengröße von 4 KB (4096 Bytes). Das zu indexierende Attribut ist ein Integer (8 Byte) und für den Pointer werden 4 Byte benötigt. (a) Wie groß ist der maximale Fanout des Indexes? Begründen Sie Ihre Aussage (z. B. mit einer Skizze). (b) Bei einem ISAM-Index, bestimmen Sie die Höhe des entstandenen Baums nachdem Laden von 35.000.000 Einträge. (c) Eine Tabelle wird mit einem B+ -Baum indexiert.Nach dem Laden von 35.000.000 Tupeln sind die Seiten des Baums zu etwa 2/3 gefüllt. Bestimmen und begründen Sie die Höhe des entstandenen Baums. Informationssysteme 5 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . E Transaktionen (2 + 6 + 3 + 3 Punkte) 1 Zeigen Sie an einem Beispiel-Schedule das “Dirty Read” Problem. Wie wirken sich “Dirty-Reads” auf die Wiederherstellbarkeit (Recoverability) eines Schedules aus? 2 Untersuchen Sie die folgenden Schedules auf Serialisierbarkeit. Listen Sie dabei alle Konflike auf und zeichnen Sie den sich daraus ergebenen Abhängigkeitsgraphen. Geben Sie, falls möglich, eine äquivalente serielle Reihenfolge der Transaktionen an. (a) hr2 [z], r2 [x], r3 [y], w2 [x], c2 , w1 [z], w1 [y], w3 [x], c3 , r1 [x], w1 [x], c1 i (b) hr3 [x], r1 [x], w1 [x], r2 [z], r4[z], w1 [y], c1 , r2 [y], w3 [z], c3 , r4 [y], c4 , w2 [x], c2 i 3 Wozu dient das “2-Phasen-Sperrprotokoll”? Betrachten Sie die unten abgebildeten Sperr- und Freigabe-Strategien des 2Phasen-Sperrprotokolls. Welche der Varianten (a)-(d) erfüllen jeweils das folgende Kriterium (mit kurzer Begründung): (a) Es besteht keine “Dead-lock”-Gefahr. (b) Es werden stets serialisierbare Schedules erzeugt. (c) Er werden zu jedem Zeitpunkt nicht mehr Objekte gesperrt, als notwendig. (d) “Fortgepflanztes” Zurücksetzen kommt nicht vor. (e) Maximale Parallelität der ausgeführten Transaktionen. 4 Demonstrieren Sie die Funktionsweise des “sukzessiven” 2-Phasen-Sperrprotokolls (Variante (a) in der Abbildung), indem Sie den Schedule aus 2 (b) um das Anfordern der Lese- und Schreibsperren (jeweils rli [x] und wli [x] für Transaktion T i und Objekt x) und ihre Freigabe (uli [x]) erweitern. Was für ein Problem tritt dabei auf? Abbruch welcher der blockierten Transaktionen würde die meisten Sperren freigeben? Informationssysteme 6 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . F Data Warehousing (6 + 3 + 1 Punkte) 1 Ein Internet-Provider will mit Hilfe der OLAP-Technologie den Datenverkehr auf dem Webserver untersuchen. Die “rohen” Daten sollen aus der Log-Datei des Servers gewonnen werden: IP-Adresse Zeitstempel Methode Ressource Protokoll Code Bytes 134.34.57.13 13.05.2005 13:40:01 GET /img/logo.jpg HTTP/1 200 17883 192.11.24.15 13.05.2005 13:40:03 POST /app/navi.php HTTP/1 200 3242 ... ... ... ... ... ... ... Das numerische Attribut Bytes soll entlang der Dimensionen Host, Zeit, Protokoll, Ressource und Code analysiert werden. Skizzieren Sie den Star-Schema-Entwurf des Data-Warehouses und beachten Sie dabei folgende Anforderungen: (a) Dimension Host besteht aus IP-Adresse, bei der zusätzlich die Zugehörigkeit zu einem Netzwerk und einem Land spezifiziert wird. (b) Dimension Zeit speichert alle Zeiteinheiten (Sekunde, Minute usw.) als getrennte Attribute. (c) Protokoll besteht aus den Attributen Protokoll und Methode der Logdatei. (d) Ressource wird in Pfad, Dateiname und Dateityp aufgeteilt. (e) Bei Code wird die zu jedem Code die dazugehörige Erläuterung mitgespeichert. Zeigen Sie alle Primär- und Fremdschlüssel an. 2 Demonstrieren Sie die Funktionsweise des OLAP-Operators CUBE, indem Sie einen 3-dimensionalen Würfel in SQL definieren, wobei Bytes über Dateityp, Methode und Code aggregiert werden. 3 Aus welchen Gründen wird in Data-Warehouse-Systemen das “Star-Schema” mit redundanten Informationen dem normalisierten “Snowflake-Schema” meist bevorzugt? Informationssysteme 7 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . G Information Retrieval (5 + 4 + 2 +3 Punkte) 1 Aus welchen zwei Parametern wird die Gewichtung eines Terms im Vektormodell berechnet? Wie werden diese ermittelt und was drücken sie aus? 2 Bei der Indexierung der Suchterme werden die zu indexierenden Begriffe und ihre Menge durch Stopword-removal und Stemming transformiert. Erläutern Sie die beiden Techniken und Ihre Auswirkung auf die Effizienz der Suche und auf die Qualität des Ergebnisses. 3 Welches der zwei IR-Modelle - das Boole’sche oder das Vektormodell - liefert bei der Exakt-Suche (“exact string matching“) eine genauere Menge der potenziellen Treffer? Begründen Sie Ihre Aussage. 4 Die Qualität einer Suchstrategie kann über Precision und Recall bewertet werden. Definieren Sie die beiden Kenngrößen. Welche davon kann über das vom Benutzer erhaltene Relevanz-Feedback zum Suchergebnis ermittelt werden? Wie? Informationssysteme 8 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Vereinfachte SQL-Syntax Anfragen: SFW-Block SELECT [ FROM { [ WHERE [ GROUP [ ORDER ALL | DISTINCT ] Attributliste { Relation | ( SFW-Block ) | ( join-stmt ) } [ AS Variable ] } . . . Bedingung ] BY { Attributliste | CUBE ( Attributliste ) } [ HAVING Bedingung ] ] BY Attributliste ] wobei: join-stmt :: { Relation | ( SFW-Block ) } { [ LEFT | RIGHT | FULL ] OUTER | [ INNER ] | { CROSS | NATURAL } } JOIN { Relation | ( SFW-Block ) } [ ON Bedingung ] Update-Operationen INSERT INTO Relation [ ( Attributliste ) ] { VALUES ( Wert1, Wert2, . . . ) | SFW-Block } UPDATE Relation SET Attribut1 = Wert1, Attribut2 = Wert2, . . . [ WHERE Bedingung ] DELETE FROM Relation [ WHERE Bedingung ] Schema-Definition: Sichten CREATE VIEW SichtName [ ( Attributliste ) ] AS SFW-Block [ WITH CHECK OPTION ] Informationssysteme 9 / 10 22. Februar 2006 Name/Matrikel-Nr.: . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Schema-Definition: Tabellen CREATE TABLE Relationenname ( Attribut1 Datentyp1 [ NOT NULL ] [ PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES Relation( Attribut) ], ... [ , PRIMARY KEY ( Attribut1, Attribut2, . . . ) ] [ , FOREIGN KEY ( Attribut1, Attribut2, . . . ) REFERENCES Relation( Attribut1, Attribut2, . . . ) [ ON { DELETE | UPDATE } { CASCADE | NO ACTION | SET { DEFAULT | NULL } } ] ] ) Rechteverwaltung GRANT { ALTER | DELETE | INDEX | INSERT | REFERENCES | SELECT | UPDATE } ON [ TABLE ] { Relation | Sicht } TO [ USER | GROUP ] Name REVOKE { ALTER | DELETE | INDEX | INSERT | REFERENCES | SELECT | UPDATE } ON [ TABLE ] { Relation | Sicht } FROM [ USER | GROUP ] Name Informationssysteme 10 / 10 22. Februar 2006