Aufgabensammlung Datenbankkurs
Werbung
Aufgabensammlung Datenbankkurs © Dr. Arno Schmidhauser Letzte Revision: 1. Juni 2006 Email: [email protected] Webseite: http://www.sws.bfh.ch/db Aufgabensammlung Datenbankkurs SWS Juni 2006 2/43 Arno Schmidhauser Aufgabensammlung Datenbankkurs SWS Inhalt Teil I - Datenmodellierung 1 2 Die Support-Applikation................................................................................. 7 Das Evaluationssystem................................................................................ 10 Teil II - Relationenmodell und Normalisierung 3 4 Schlüssel................................................................................................... 17 Normalisierung........................................................................................... 17 Teil III - SQL 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Vorbemerkungen........................................................................................ 21 Erstellen von Tabellen ................................................................................. 23 Domains ................................................................................................... 24 Abfragen mit einer Tabelle ........................................................................... 24 Einfaches Einfügen, Ändern, Löschen............................................................. 25 Ändern der Tabellenstruktur......................................................................... 25 Abfragen mit Gruppierung ........................................................................... 25 Abfragen mit Unterabfragen in der where-Klausel............................................ 26 Abfragen mit Inner Join ............................................................................... 26 Abfragen mit Outer Join .............................................................................. 26 Abfragen mit Self Join ................................................................................. 27 Views ....................................................................................................... 28 Referentielle Integrität, Constraints............................................................... 28 Trigger...................................................................................................... 28 Funktionen ................................................................................................ 29 Prozeduren ................................................................................................ 30 Teil IV - Java Anbindung mit JDBC 21 Übung Support-Servlet................................................................................ 33 Teil V - Transaktionen und Technologie 22 23 24 25 26 27 Transaktionsmodell..................................................................................... 39 Serialisierbarkeit ........................................................................................ 39 Concurrency Control in SQL ......................................................................... 40 Lange Transaktionen................................................................................... 42 Recovery................................................................................................... 42 Optimierung .............................................................................................. 42 Juni 2006 Arno Schmidhauser 3/43 Aufgabensammlung Datenbankkurs SWS Juni 2006 4/43 Arno Schmidhauser Aufgabensammlung Datenbankkurs SWS Teil I Datenmodellierung Juni 2006 Arno Schmidhauser 5/43 Aufgabensammlung Datenbankkurs SWS Juni 2006 6/43 Arno Schmidhauser Aufgabensammlung Datenbankkurs SWS 1 Die Support-Applikation 1.1 Beschreibung Eine Hotline eines Software-Herstellers nimmt Anfragen und Anrufe von Kunden zu verschiedensten Fragen und Problemen entgegen. Anfragen können per Telefon oder per Web-Formular eingehen. Die telefonischen Anfragen werden vom Mitarbeiter, der Telefondienst hat, über eine Applikation in die Datenbank eingegeben. Die Formular-Anfragen werden via Webserver direkt in eine Datenbank übertragen.Kunden sind Personen, welche in der Regel eine Lizenz für eines oder mehrere der vertriebenen Produkte besitzen. Die Mitarbeiter, welche Supportdienst haben, sitzen vor einem Bildschirm und sehen darauf die wartenden Support-Anfragen. Ein Support-Mitarbeiter übernimmt, wenn er frei ist, die nächste Anfrage. Er ist dann für deren Beantwortung verantwortlich. Die Beantwortung kann unterschiedlich lange dauern, je nach Komplexität des Falles. Die Antwort wird vom Supportsystem beim Abschluss des Falles per Email an den Kunden zurückgeschickt. Jede Anfrage hat einen Status. Der Status ist bei Eintreffen der Anfrage "eingegangen". Mit der Übernahme durch einen Mitarbeiter wird die Anfrage "uebernommen". Sobald der Fall vom Mitarbeiter abgeschlossen wird, geht die Anfrage in den Zustand "abgeschlossen" über. Für die Support-Abteilung ist die Durchführung von statistischen Abfragen sehr wichtig, zum Beispiel welche Produkte wieviel Support verlangen, wie lange die Mitarbeiter für die Beantwortung brauchen usw. Der gesamte Fall wird daher aufbewahrt. Juni 2006 Arno Schmidhauser 7/43 Aufgabensammlung Datenbankkurs SWS 1.2 Uses Case Diagramm Support-Applikation Fall eröffnen Kunde Fall bearbeiten Mitarbeiter F übernehmen F abschliessen F beantworten SupportStatistik anzeigen SupportLeiter 1.3 Weitere Informationen Von einem Fall wird das Eingangs- und das Abschlussdatum festgehalten. Für den Mitarbeiter, welcher den Fall behandelt hat, wird die geleistete Supportzeit für diesen Fall festgehalten. Produkte haben einen Namen und eine Version. Zu jedem Produkt gehört auch ein Datenblatt (Beispielsweise eine PDF-Datei). Mitarbeiter haben einen Namen und einen Vornamen. Die gesamte von einem Mitarbeiter geleistete Supportzeit ist wichtig für dessen Zeitabrechnung. Diese Supportzeit muss erhalten bleiben, auch wenn Fälle gelöscht werden. Das Mitarbeitertotal wird nach der Überzeitabrechnung Ende Jahr auf 0 zurückgesetzt. Die geleistete Supportzeit wird mit dem Abschluss eines Falles festgehalten und dem Mitarbeiter gutgeschrieben, der den Fall abschliesst. Kunden haben einen Namen, eine Email-Adresse, eine Telefonnummer und ein Passwort. Letzteres wird für das Login in der Support-Applikation benötigt. Kunden können für ein Produkt eine oder mehrere Lizenzen besitzen. Jede Lizenz ist zeitlich begrenzt. Unterschiedliche Versionen eines Produktes müssen einzeln lizenziert werden. Für jede Lizenz existiert ein Key. Dieser Key wird dem Kunden mitgeteilt und dient als Beweis, dass der Kunde in Besitz der Lizenz ist. Jeder Mitarbeiter hat einen anderen Mitarbeiter als Stellvertreter. Ein Mitarbeiter kann mehrere andere vertreten, hat selber jedoch nur einen, oder im Ausnahmefall keinen Stellvertreter. Juni 2006 Arno Schmidhauser 8/43 Aufgabensammlung Datenbankkurs SWS Eine Lizenz kann nur existieren, wenn der zugehörige Kunde und das zugehörige Produkt existiert. Eine Lizenz gehört lebenslang zum selben Kunden und zum selben Produkt. Sie kann nicht auf einen anderen Kunden oder ein anderes Produkt übertragen werden. Ein Fall wird durch einen bestimmten Mitarbeiter übernommen. Es kann jedoch vorkommen, dass ein Mitarbeiter krank wird oder den Supportdienst verlässt. Unter diesen Umständen kann der Fall seinem Stelvertreter oder einem anderen Mitarbeiter zugewiesen werden. Jeder Fall ist einer der Kategorien 'Softwarefehler', 'Bedienungsproblem', 'Lizenzierungsfrage' zugewiesen. 1.4 Aufgaben F1 Erstellen Sie ein Datenmodell mit UML, welches die notwendigen Informationen für den Supportdienst umfasst. F2 Erstellen Sie ein State Event-Diagramm für den Zustand eines Supportfalles. F3 Erstellen Sie ein normalisiertes Tabellenmodell mit UML, inkl. Bezeichnung der Primärschlüssel und Sekundärschlüssel. Definieren Sie im Tabellenmodell auch die Datentypen für Attribute und allenfalls Hilfstabellen zur Ablage von festen Werten oder Konstanten. 1.5 Anschlussfragen F4 Ein Fall bezieht sich auf Produkt und Kunde. Er könnte sich stattdessen nur auf Lizenz beziehen. Wo sehen sie Vor- und Nachteile? Technische Betrachtungen wie Performance oder Anzahl Tabellen in einer Abfrage sollen keine Rolle spielen, es geht nur um konzeptionell Vor- oder Nachteile. F5 Ein Supportfall soll sich auf einen früheren Supportfall beziehen können. Mit Hilfe welcher neuer Attribute in welcher (ev. neuen) Tabelle können Sie das modellieren? F6 Supportfälle werden von unterschiedlich qualifizierten Mitarbeitern behandelt. Die Kategorie 'Softwarefehler' wird von Ingenieuren behandelt, was den Support 250 CHF/Stunde kostet. Die Kategorie 'Bedienungsproblem' wird von Mitarbeitern des Schulungszentrums behandelt, was den Support 180 CHF/Stunde kostet. Die Kategorie 'Lizenzierungsfrage' wird von Verkaufsmitarbeitern behandelt, was den Support 140 CHF/Stunde kostet. Die Kosten des Supportdienstes werden periodisch ermittelt. Wo im Datenmodell bauen Sie die Kostensätze ein? F7 Die Kostensätze ändern sich gelegentlich. Trotzdem muss nachvollzogen werden können, welche Kostensätze zu einem bestimmten Zeitpunkt aktiv waren. Modellieren Sie diesen Sachverhalt im Datenmodell. Juni 2006 Arno Schmidhauser 9/43 Aufgabensammlung Datenbankkurs SWS 2 Das Evaluationssystem 2.1 Vorbemerkung Diese Aufgabe ist relativ komplex, bringt aber einige wichtige Problemstellungen auf, die im praktischen Datenbankentwurf immer wieder zu lösen sind. 2.2 Beschreibung Das Evaluationssystem ermöglicht es, Umfragen über ein Fach oder einen Kurs bei Studierenden einer Fachhochschule per Browser/Email durchzuführen. 2.3 Use Cases Evaluationssystem Stammdaten verwalten Fragebogen entwerfen Umfrage auswerten Statistiken erstellen Umfragen planen Dozent Administrator Umfrage abschliessen Fragebogen ausfüllen Umfrage starten System Student 2.3.1 Stammdaten verwalten Stammdaten sind Klassen, Fächer, Dozenten und Studenten. Ein Student gehört gehört eventuell zu mehreren Klassen. Zwischen Klassen, Dozenten und Fächer besteht kein fester Zusammenhang. Dieser Zusammenhang wird erst über eine Umfrage hergestellt. Klassen haben eine Bezeichnung und eine Beschreibung. Dozenten habe einen Name, Vorname, Email-Adresse und Passwort. Fächer haben eine Abkürzung, einen Titel und eine Beschreibung. Studenten haben Name, Vorname und Email-Adresse. Juni 2006 Arno Schmidhauser 10/43 Aufgabensammlung Datenbankkurs SWS 2.3.2 Fragebogen entwerfen (Fragebogentyp) Das Evaluationssystem kann für jede Umfrage einen bestimmten Fragebogentyp verwenden (Ein Fragebogentyp entspricht dem leeren Fragebogen, definiert also Art und Anzahl der Fragen, die möglichen Antworten usw.) Ein Fragebogentyp wird einmal erstellt und kann dann für viele Umfrage verwendet werden. Es muss möglich sein, den Fragenbogentyp anzupassen, also neue Frage hinzuzufügen, bestehende zu löschen, Frage- und Antworttexte anzupassen. Ein Fragebogentyp hat einen Namen und umfasst mehrere Abschnitte. Jeder Abschnitt hat einen Titel und beinhaltet mehrere Fragen. Die Abschnitte sind nummeriert. Es gibt folgende Arten von Fragen: Eine Multiple Choice Frage besteht aus dem Fragetext und einer Vorgabe mehrerer Antwortmöglichkeiten. Jede Antwortmöglichkeit besteht aus einer Beschriftung und einem hinterlegten Wert. Je nach Art der Frage ist die Beschriftung das wesentliche Element oder der Wert. Lautet die Frage beispielsweise 'Welche Zeitungen lesen Sie? (NZZ, Blick, BZ, Bund)', so ist die Beschriftung das wichtige Element, weil bei der Auswertung lediglich die Anzahl Nennungen für eine bestimmte Zeitung wichtig ist. Lautet die Frage 'Welche Noten geben Sie dem Dozenten? (Gut, Mittel, Schlecht)', so ist der Wert das wichtige Element, weil letztlich ein Mittelwert aller Antworten gebildet werden soll. Für Multiple Choice-Fragen muss ausserdem angegeben werden können, wieviele Antworten mindestens und wieviele Antworten höchstens gegeben werden können. Eine Text-Frage ermöglicht die Eingabe von freiem Text als Antwort. Eine Wert-Frage ermöglicht die Eingabe eines beliebigen Zahlenwertes zwischen einem Minimal- und einem Maximalwert. Werte haben typischerweise auch eine Einheit, beispielsweise Meter, Tage, Kilogramm, Kisten usw. Zu jeder Frage gehört natürlich auch der eigentliche Fragetext. Die Fragen sind ausserdem nummeriert. 2.3.3 Umfragen planen Umfragen können durch den Administrator geplant werden. Er muss hierzu folgende Angaben eingeben: Fach und Klasse, beteiligte Dozenten, Fragebogentyp, vorgesehenes Datum und Zeit des Versandes. Jede Umfrage hat einen Status. Beim Erstellen ist dieser Status 'geplant'. 2.3.4 Umfrage verschicken Das Verschicken der Umfragen wird durch das System am vorgesehenen Datum zur vorgesehenen Zeit durchgeführt. Verschicken ist so zu verstehen, dass den Studenten ein Email geschickt wird. Dieses Email enthält einen Link auf das Evaluationssystem. In diesem Link ist eine ID eingebaut, die auf die zu beantwortende Umfrage, resp. auf den für diesen Studenten vorgesehenen Fragebogen zeigt. Klickt der Student im Email auf diesen Link, wird der Fragebogen aus der Datenbank dynamisch zusammengestellt und an den Browser geschickt. Mit dem Verschicken wird der Status der Umfrage auf 'laufend' gesetzt. 2.3.5 Umfrage abschliessen Nach einer gewissen Zeit (einige Tage) wird die Umfrage durch den Administrator manuell abgeschlossen. Ab diesem Zeitpunkt können keine Fragebogen mehr beantwortet werden. Technisch heisst das Abschliessen lediglich, dass der Status der Umfrage auf 'abgeschlossen' gesetzt wird. 2.3.6 Fragebogen ausfüllen Der Student bekommt beim Versand der Umfrage ein Email zugestellt, das einen HTMLLink enthält. In diesem Link ist eine ID eingebaut, die auf die zu beantwortende Umfrage, resp. auf den für diesen Studenten vorgesehenen Fragebogen zeigt. Der Student klickt auf diesen Link. Damit wird das Evaluationssystem aufgerufen und ein Fragebogen dynamisch zusammengestellt (Die Formatierungsinformation sei in den Programmen enthalten und nicht der Datenbank). Für jeden Fragenbogen wird zuerst ein Juni 2006 Arno Schmidhauser 11/43 Aufgabensammlung Datenbankkurs SWS Kopf erstellt, der Angaben zu Klasse, Fach, beteiligte Dozenten und Versand-Datum der Umfrage enthält. Der Hauptteil des Fragebogens besteht aus Abschnitten mit jeweils einer oder mehreren Fragen. Diese werden anhand der Definitionen aus dem Fragebogentyp zusammengestellt. Der Fragebogen (sein Layout) ist zwar nicht in der Datenbank vorhanden. In der Datenbank muss aber festgehalten werden, ob und wann der Fragebogen ausgefüllt wurde, damit derselbe Fragebogen nicht zweimal beantwortet werden kann. Beispiel eines Fragebogens: Juni 2006 Arno Schmidhauser 12/43 Aufgabensammlung Datenbankkurs SWS 2.3.7 Umfrage auswerten / Statistiken erstellen Sobald eine Umfrage gestartet und die ersten Antworten eingegangen sind, können die an einer Umfrage beteiligten Dozenten eine Auswertung vornehmen. Der genaue Inhalt und die Art der Auswertungen ist für die Gestaltung der Datenbank weniger wichtig. Von äusserster Wichtigkeit ist jedoch, dass alle Informationen und Daten verfügbar sind. Das heisst auch, dass jede Antwort einzeln aufbewahrt wird, der Zusammenhang zur gestellten Frage, zum Fragebogen (resp. Student), zur Umfrage, zum Dozenten, zur Klasse, zum Fach usw. direkt oder indirekt erhalten bleiben muss. Es müssen beispielsweise folgende Fragen anhand der Datenbank beantwortet werden können: Durschnittlicher Wert der Frage F für einen Dozenten D in allen Klassen. Durschnittlicher Wert der Frage F über alle Dozenten einer bestimmte Klasse K. usw. usw. 2.4 Aufgaben F8 Erstellen Sie ein konzeptionelles Datenmodell mit UML. F9 Wie würden Sie vorgehen, wenn das Evaluationssystem mehrsprachig sein müsste, das heisst, sämtliche dem Benutzer erscheinende Texte auf dem Fragebogen in einer vom Benutzer abhängigen Sprache erscheinen sollen? F10 Hat es in ihrer Lösung Vererbungsbeziehungen? Wenn ja, finden Sie Lösungsmöglichkeiten, diese in Tabellen abzubilden. Juni 2006 Arno Schmidhauser 13/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 14/43 Aufgabensammlung Datenbankkurs SWS Teil II Das Relationenmodell Juni 2006 Arno Schmidhauser 15/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 16/43 Aufgabensammlung Datenbankkurs SWS 3 Schlüssel F11 Was ist der Unterschied zwischen einem Relationenschlüssel und einem Primärschlüssel? F12 Was ist der Unterschied zwischen einem Primär- und einem Sekundärschlüssel? F13 Beschreiben Sie Anforderungen an einen Primärschlüssel. F14 Zählen Sie Erzeugungsmöglichkeiten für Primärschlüssel auf. 4 Normalisierung F15 In welcher Normalform befindet sich eine Relation mit zwei Attributen (inkl. Schlüssel) immer, sofern sie bereits in erster Normalform ist? F16 Welche Probleme bezüglich Normalisierung sehen Sie in folgender Tabelle? Buch F17 Doz# 1 1 2 2 2 3 titel SQL für Profis SQL für Profis SQL für Profis XML-Datenbanken UML und Datenbanken standort Bibliothek Abteilung I Abteilung W Bibliothek Bibliothek Doz_Name Huber Huber Iselin Iselin Iselin Fierz Ku# 10 10 11 12 12 13 Ku_Name ProjMan ProjMan Java C++ C++ Math Datum 19. April 02 12. April 02 13. April 02 21. Mai 02 28. Mai 02 30. Juni 02 Stellen Sie die funktionalen Abhängigkeiten in untenstehender Tabelle dar. Überlegen Sie vorerst die Bedeutung der AHV-Nr. Dozent F19 isbn 123-123 123-123 123-123 123-124 123-125 Welche Normalform verletzt untenstehende Tabelle, wenn man von den Tatsachen ausgeht, dass a) ein Dozent verschiedene Kurse gibt, aber b) ein Kurstyp nur von einem Dozenten unterrichtet wird und c) derselbe Kurs pro Tag höchstens einmal stattfindet? Stellen Sie die funktionalen Abhängigkeiten dar und führen Sie eine Normalisierung bis zur 3NF durch? Die Attribute des Primärschlüssels sind unterstrichen. Kurs F18 exemplarNr 1 2 3 4 5 Doz# 1 2 3 Doz_Name Huber Iselin Fierz AHV-Nr 822.59.268.113 162.62.468.122 423.56.487.132 Prüfen Sie folgende Excel-Tabellen auf die Normalisierung und normalisieren sie allenfalls: Juni 2006 Arno Schmidhauser 17/43 Aufgabensammlung Datenbankkurs SWS Notenblatt Juni 2006 Matrikelnr 05-477-611 Name Allensbach Peter 05-477-642 Binggeli Martin 05-477-681 Zyssett Claude Fach Arbeitstechnik Recht Projektmanagement Arbeitstechnik Recht Projektmanagement Arbeitstechnik Recht Projektmanagement Arno Schmidhauser Note A D E F C B C B E 18/43 Aufgabensammlung Datenbankkurs SWS Teil III SQL Juni 2006 Arno Schmidhauser 19/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 20/43 Aufgabensammlung Datenbankkurs 5 SWS Vorbemerkungen 5.1 UML-Diagramm Mitarbeiter Fall 0.. 1 Produkt 0.. * 0.. * 1 0.. * 1 0.. * 1 Kunde Lizenz 1 0.. * Das Kernelement des Datenmodelles ist der Supportfall (Tabelle Fall). Ein Fall referenziert obligatorisch einen Kunden und ein Produkt. Ein Fall durchläuft nacheinander die drei Zustände 'eingegangen', 'übernommen' oder 'abgeschlossen'. Ab dem Zustand 'eingegangen' ist dem Fall ein Mitarbeiter zugewiesen. Folgendes Zustandsdiagramm gilt für einen Supportfall: Übernahme durch Mitarbeiter eingegangen uebernommen Antwort erstellt und Kunde informiert abgeschlossen Juni 2006 Arno Schmidhauser 21/43 Aufgabensammlung Datenbankkurs SWS 5.2 SQL-Tabellen Folgende Tabellen-Definitionen sind für die Übungen vorgegeben: create table Mitarbeiter ( idMitarbeiter numeric(10,0) not null, name varchar(32) not null, vorname varchar(32) not null, supportZeitTotal float null, primary key (idMitarbeiter), ); create table Produkt ( idProdukt numeric(10,0) not name varchar(255) not datenblatt long binary mimeType varchar(64) version varchar(16) not primary key (idProdukt), unique (name, version) ); create table Kunde ( idKunde numeric(10,0) name varchar(32) email varchar(32) telefon varchar(16) passwort varchar(16) primary key (idKunde), unique (email) ); null, null, null, null, null, not null, not null, not null, null, not null, create table Lizenz ( idLizenz numeric(10,0) not null, idKunde numeric(10,0) not null, idProdukt numeric(10,0) not null, lizenzKey SecureId not null, gueltigBis Date not null default dateadd( day, 30, current date ), primary key (idLizenz), unique (lizenzKey), foreign key (idProdukt) references Produkt (idProdukt) on delete restrict, foreign key (idKunde) references Kunde (idKunde) on delete cascade ); ) Wenn Sie direkt mit dem Kapitel 8 'Abfragen mit einer Tabelle' beginnen, müssen Sie noch die Tabelle 'Fall' gemäss Lösung der Aufgaben F20 und F21 berücksichtigen. SQL-Definitionen für die Tabellen Mitarbeiter, Produkt, Kunde und Lizenz liegen in der Datei tables.sql bereits vor. Entsprechende Testdaten zum Einfügen finden Sie in der Datei data.sql. Übergeben Sie den Inhalt der beiden Dateien via 'Interactive SQL' der Datenbank. 5.3 'Interactive SQL' Datenbank Frontend 'Interactive SQL' ist das Frontend von Sybase ASA für eine Datenbank, um Tabellen zu definieren, Administrationsbefehle durchzuführen, Abfragen zu testen usw. Juni 2006 Arno Schmidhauser 22/43 Aufgabensammlung Datenbankkurs SWS Im oberen Teil des Fensters geben Sie SQL-Befehl ein, im unteren ersehen Sie das Resultat von Abfragebefehlen: ) Bitte nehmen Sie für die Übungen folgende Einstellungen vor: Extras -> Optionen ... ) Die Dateien mit den detaillierten SQL-Definitionen, welche Sie als Vorlagen benützen können, sind auf der Webseite unter Hilfsmittel für die Aufgaben und Übungen -> SQL-Skripte zu den Aufgaben zu finden. 6 Erstellen von Tabellen F20 Erstellen Sie eine Tabelle Fall nach folgenden Anforderungen: Juni 2006 Arno Schmidhauser 23/43 Aufgabensammlung Datenbankkurs SWS Die Tabelle muss die ID's des verantwortlichen Mitarbeiters, des Kunden und des Produktes enthalten. Der verantwortliche Mitarbeiter für den Fall ist nicht von Anfang bekannt. Der Fall selber hat ebenfalls eine ID. Jeder Fall hat eine Beschreibung sowie eine Antwort des Supports. Jeder Fall durchläuft die folgenden Zustände: 'eingegangen', 'uebernommen' und 'abgeschlossen'. Im Zustand eingegangen ist noch kein Mitarbeiter zugewiesen. Dies geschieht erst mit der Übernahme durch einen Mitarbeiter. Jeder Fall ist einer der Kategorien 'Softwarefehler', 'Bedienungsproblem', 'Lizenzierungsfrage' zugewiesen. Eingangsdatum, Abschlussdatum und Zeitaufwand (dieser entspricht nicht notwendigerweise der Datumsdifferenz). Definieren Sie die ID des Falles als Primärschlüssel. 7 Domains F21 Falls Sie in der Tabelle Fall noch keine Domäne für den Support-Status und die Kategorie eines Falles definiert haben, tun Sie dies hier. Ein Beispiel, welches den Lizenz-Key für die Tabelle Lizenz definiert, ist in tables.sql zu finden. 8 Abfragen mit einer Tabelle F22 Menge aller Kundeneinträge, sortiert nach Name. Sortierung aufsteigend. F23 Menge aller Kundeneinträge ohne Telefonangabe (telefon ist null). F24 Was ergibt die Abfrage select name from Kunde where name like 'O%' ? F25 Menge aller abgeschlossen Supportfälle zwischen 1. und 7. Juni 2003. Arbeiten Sie mit einem Tabellenalias. F26 Menge aller abgeschlossenen Supportfälle unter Angabe von idFall, abgeschlossenAm und der Dauer zwischen Eingang und Abschluss jedes Falles in Stunden (Funktion datediff() verwenden). Die Ausgabe soll absteigend sortiert sein, nach Dauer zwischen Eingang und Abschluss in Stunden. Das Ausgabeformat von abgeschlossenAm soll 'DD.MM.YYYY HH:NN') sein (Funktion dateformat() oder die Option set temporary option timestamp_format = '...' verwenden). Der Name der ersten Spalte soll Abschlussdatum sein, der Name der zweiten Spalte 'Dauer in Stunden'. Juni 2006 Arno Schmidhauser 24/43 Aufgabensammlung Datenbankkurs SWS 9 Einfaches Einfügen, Ändern, Löschen F27 Nehmen Sie einen neuen Kunden auf und eine Lizenz für ihn für das Produkt 'BancomatPlus'. Suchen Sie die idProdukt einmal manuell und einmal aufgrund des Produktnamens ( einmal insert into tabelle(...) values(...) und einmal insert into tabelle(...) select-Befehl verwenden. SQL-Befehle wie insert, delete, update werden immer innerhalb einer so genannten Transaktion durchgeführt. Das heisst, die vorgenommenen Änderungen sind noch nicht definitiv. Sie können sie jederzeit mit rollback wieder rückgängig machen. Erst wenn Sie die Änderungen mit commit bestätigen ('festschreiben'), sind sie definitiv. Mit rollback werden alle Änderungen seit dem letzten commit verworfen. F28 Löschen Sie den Kunden und seine Lizenz wieder. F29 Was ist der Unterschied zwischen dem Befehl drop und delete? 10 Ändern der Tabellenstruktur Tabellen müssen aus folgenden Gründen gelegentlich geändert werden: Es ist ein zusätzliches Attribut erforderlich. Ein Attribute ist überflüssig und soll gelöscht werden. Ein Attribut benötigt einen anderen Datentyp, z.B. einen grössere maximale Länge als bisher. Bedingungen für ein Attribut müssen geändert, gelöscht oder hinzugefügt werden. Tabellen (nicht der Inhalt, sondern die Struktur) werden mit alter table geändert. Dabei ist es natürlich von absoluter Notwendigkeit, dass bereits bestehende Datensätze erhalten bleiben. F30 Ändern Sie die Tabelle Mitarbeiter, indem Sie ein neues Attribut idStellvertreter hinzufügen. Dieses soll für jeden Mitarbeiter die ID eines anderen Mitarbeiters sein, der sein Stellvertreter ist. Das Attribut darf null sein, d.h. ein Mitarbeiter kann auch keinen Stellvertreter haben. F31 Ändern Sie die Tabelle Mitarbeiter, indem Sie ein paar Stellvertretungen eintragen. 11 Abfragen mit Gruppierung F32 Anzahl abgeschlossene Supportfälle pro Kategorie. F33 Anzahl Fälle, gesamter und durchschnittlicher Zeitaufwand aller Supportfälle pro Kategorie, gerundet auf 2 Stellen (round()-Funktion). F34 Welche Produkte (nur idProdukt und gesamter Supportaufwand angeben) haben insgesamt mehr als 10 Stunden Supportaufwand versursacht. Sortieren Sie absteigend nach Aufwand. F35 Liste aller Kunden (nur idKunde), sortiert nach total beanspruchtem Supportaufwand. Was passiert mit Kunden, die überhaupt nie Support beansprucht haben? Juni 2006 Arno Schmidhauser 25/43 Aufgabensammlung Datenbankkurs SWS 12 Abfragen mit Unterabfragen in der where-Klausel F36 Welche Kunden haben keine Lizenz für irgendein Produkt? Versuchen sie eine Variante mit dem exists- und eine mit dem in-Operator. F37 Welche Kunden haben Produkt 4 und Produkt 5 lizenziert (nur mit idProdukt, nicht mit dem Produktnamen arbeiten)? F38 Welche Kunden haben Produkt 4 oder Produkt 5 lizenziert (nur mit idProdukt, nicht mit dem Produktnamen arbeiten)? 13 Abfragen mit Inner Join Es gibt verschiedene Arten von Inner Joins. Je nach Aufgabe und Tabellenstrukturen eignet sich die eine oder andere Art, manchmal gibt es auch mehr als eine Möglichkeit: Natural Join Verknüpfung von zwei Tabellen über ihre gemeinsamen, gleichnamigen Attribute: from Tabelle1 natural join Tabelle2 Key Join Verknüpfung von zwei Tabellen über die in der Tabellendefinition festgelegte Primärschlüssel/Fremdschlüssel-Beziehung. Der Key Join ist in der hier verwendeten Syntax Sybase ASA spezifisch: from Tabelle1 key join Tabelle2 Join On Zwei Tabellen werden durch Angabe einer On-Klausel explizit über eine bestimmte Join-Bedingung verknüpft: from Tabelle1 join Tabelle2 on Tabelle1.attr1 = Tabelle2.attr2 Explizite Join-Bedingung als Teil der where-Klausel Dies ist die älteste und ausführlichste Schreibweise. Sie ist aber konzeptionell am klarsten und in jedem Datenbanksystem und für jede Aufgabenstellung anwendbar. from Tabelle1, Tabelle2 where Tabelle1.attr1 = Tabelle2.attr2 F39 Zu welchen Kunden gehören welche Lizenz-Keys? Erstellen Sie eine Liste der Lizenz-Keys mit dem zugehörigen Kundennamen und dem Gültigkeitsdatum. Keine Angabe des Produktnamens. F40 Liste aller abgeschlossenen Supportfälle mit Name des Mitarbeiters, Abschlussdatum und Beschreibung des Falles (Neueste Fälle zuerst). F41 Liste aller eingegangenen Supportfälle mit Name des Produktes, Name des Kunden, Beschreibung und Eingangsdatum des Falles. Sortiert nach Eingangsdatum. 14 Abfragen mit Outer Join Es gibt verschiedene Arten von Outer Joins. Je nach Aufgabe und Tabellenstrukturen eignet sich die eine oder andere Art, manchmal gibt es auch mehr als Juni 2006 Arno Schmidhauser 26/43 Aufgabensammlung Datenbankkurs SWS eine Möglichkeit. Natural Left|Right|Full Outer Join Verknüpfung von zwei Tabellen über ihre gemeinsamen, gleichnamigen Attribute: from Tabelle1 natural left|right|full outer join Tabelle2 Key Left|Right|Full Outer Join Verknüpfung von zwei Tabellen über die in der Tabellendefinition festgelegte Primärschlüssel/Fremdschlüssel-Beziehung. Der Key Join ist in der hier verwendeten Syntax Sybase ASA spezifisch: from Tabelle1 [key] left|right|full outer join Tabelle2 Achtung bei Sybase ASA: Es wird ein Key Join durchgeführt, wenn weder natural, noch key noch eine on-Klausel angegeben ist. Die Syntax full outer join, ohne Angabe von key, natural oder einer on-Klausel führt zur Bildung eines kartesischen Produktes. Left|Right|Full Outer Join On Zwei Tabellen werden durch Angabe einer On-Klausel explizit über eine bestimmte Join-Bedingung verknüpft: from Tabelle1 left|right|full outer join Tabelle2 on Tabelle1.attr1 = Tabelle2.attr2 Achtung: Wenn Sie left outer join schreiben, meint Sybase ASA damit einen key left outer join und nicht einen natural left outer join. F42 Liste aller Mitarbeiter und ihrer Fälle, sortiert nach Mitarbeitername. Der Name des Mitarbeiters und das Eingangsdatum des Falles soll angegeben werden. Auch Mitarbeiter ohne Fälle sollen aufgeführt werden. Welche Mitarbeiter haben keine Fälle? F43 Liste aller Kunden und ihrer Lizenzen, sortiert nach Kundenname. Der Name des Kunden, das Gueltigkeitsdatum und die Lizenznummer soll angegeben werden. Auch Kunden ohne Lizenzen sollen aufgeführt werden. F44 Sind folgende zwei Abfragen äquivalent? select k.name, l.lizenzKey from Kunde k natural left outer join Lizenz l select k.name, l.lizenzKey from Kunde k natural join Lizenz l union select k.name, null from Kunde k where idKunde not in ( select idKunde from Lizenz) 15 Abfragen mit Self Join Ein Self Join ist ein Join einer Tabelle mit sich selber. Dabei werden, logisch gesehen, aus einer Tabelle zwei Kopien erstellt und diese miteinander in einem Join verbunden. Damit die beiden logischen Kopien unterscheidbar sind, ist es bei einem Self Join zwingend, mit je einem Aliasnamen für die beiden Tabellen zu arbeiten. F45 Gegeben sei folgende Tabelle PersonenEigenschaft, welche Eigenschaften von Personen beschreibt (Der Datentyp aller Attribute ist varchar). Juni 2006 Arno Schmidhauser 27/43 Aufgabensammlung Datenbankkurs person A A A B B B C C C D D D E E E eigenschaft schuhgrösse haarfarbe nationalität schuhgrösse haarfarbe nationalität schuhgrösse haarfarbe nationalität schuhgrösse haarfarbe nationalität schuhgrösse haarfarbe nationalität SWS wert 41 braun CH 42 blond DE 41 schwarz US 42 braun US 43 blond DE Erstellen Sie eine SQL-Abfrage zur Feststellung von "Berührungspunkten" einer Person A mit anderen Personen. Das heisst, die Abfrage soll alle Personen ausgeben (nur einmal), welche für eine oder mehrere Eigenschaften denselben Wert haben wie die Person A. 16 Views F46 Zählen Sie Anwendungsmöglichkeiten von Views auf. F47 Welchem Konstrukt in Programmiersprachen (z.B. Java) entspricht eine View, wenn man die Tabelle mit einer Klasse vergleicht? 17 Referentielle Integrität, Constraints F48 Erstellen Sie für die Tabelle Fall folgende zusätzlichen Integritätsbedingungen (Verwenden Sie den Befehl alter table add foreign key ... ). Beim Löschen eines Mitarbeiters soll idMitarbeiter auf null gesetzt werden. Das Löschen eines Kunden soll das Löschen aller Fälle dieses Kunden bewirken. Das Löschen eines Produktes soll das Löschen aller Fälle dieses Produktes bewirken. F49 Stellen Sie in der Tabelle Mitarbeiter sicher, dass beim Löschen eines Mitarbeiters seine Einträge als Stellvertreter bei anderen Mitarbeitern auf null gesetzt werden(Verwenden Sie den Befehl alter table add foreign key ... ). 18 Trigger Hinweis: Wenn Sie mehrere Trigger auf derselben Tabelle für dieselbe Aktion definieren wollen (also beispielsweise mehrere update-Trigger für die Tabelle Fall), so müssen Sie die Trigger mit der Klausel order n (siehe Manual) nummerieren. F50 Gegeben sei eine Tabelle, in der einzelne Schritte eines Arbeitsablaufes abgelegt sind: Juni 2006 Arno Schmidhauser 28/43 Aufgabensammlung Datenbankkurs SWS create table Workstep ( idWorkstep uniqueidentifier default newid(), position integer not null, description varchar( 255 ), primary key ( idWorkstep ) ); Die Schritte müssen eine Nummerierung (Position) besitzen, so dass sie entsprechend dieser Nummerierung abgearbeitet werden können. Benützer der Tabelle können durch Einfüge-Operationen an beliebiger Stelle neue Schritte einfügen, oder durch Löschoperationen Schritte löschen, beispielsweise mit: insert into Workstep ( position, description ) values( 1, 'Geld abheben' ); insert into Workstep ( position, description ) values( 2, 'Geschenk kaufen' ); insert into Workstep ( position, description ) values( 3, 'Geschenk verschicken' ); Wenn eine Einfüge-Operation an einer bereits besetzten Position erfolgt, sollen diese und alle nachfolgenden Positionen um 1 erhöht werden. Beim Löschen einer Position sollen alle nachfolgenden um 1 nach unten rutschen. Erstellen Sie entsprechende Trigger. F51 Vervollständigen Sie folgenden Trigger für die Tabelle Fall, welcher die aufgewendete Supportzeit (Attribut zeitaufwand) für einen Fall beim entsprechenden Mitarbeiter im Attribut supportZeitTotal dazuaddiert: create trigger t_Fall_u1 after update of status order 1 on Fall referencing new as newFall for each row when ( newFall.status = 'abgeschlossen' ) begin ... end; 19 Funktionen F52 Ein klassisches Beispiel einer Funktion ist das Erzeugen von Primärschlüsseln. Primärschlüssel können auf verschiedenste Weise erzeugt werden: Eine häufige, aber unflexible und proprietäre Art ist das Verwenden eines Attributes mit automatischer Erhöhung (in Sybase ASA default autoincrement-Schlüsselwort für ein Tabellenattribut. Ein wesentlich allgemeineres und flexibleres Vorgehen ist wie folgt: In einer Hilfstabelle (z.B. KeyStore genannt) wird für jedes Schlüsselattribut (z.B. idFall oder idKunde) ein aktueller Zählerwert aufbewahrt. Muss ein Schlüsselwert generiert werden (z.B. im Rahmen eines insert-Befehls für die Tabelle Fall oder Kunde), so wird der Zähler in der Hilfstabelle erhöht und gelesen: KeyStore Juni 2006 keyName keyValue idFall 1000 idProdukt 1000 idMitarbeiter 1000 Arno Schmidhauser 29/43 Aufgabensammlung Datenbankkurs idLizenz idKunde SWS 1000 1000 F53 Erstellen Sie die Funktion getKey( keyName ), welche den verlangten Zähler erhöht, liest und an den Aufrufer zurückgibt. Ein Anwendungsbeispiel von getKey() könnte beispielsweise folgender insert-Befehl sein: insert into Mitarbeiter ( idMitarbeiter, name, vorname ) values ( getKey( 'idMitarbeiter' ), 'Muster', 'Daniela' ); Erstellen Sie ebenfalls die Tabelle KeyStore. 20 Prozeduren F54 In jeder Datenbank ist die Information über Tabellen und andere Objekte der Datenbank ebenfalls in (System-)Tabellen abgelegt. Erstellen Sie eine Prozedur namens help mit dem Parameter tabname. Die Prozedur soll alle Attribute der Tabelle tabname mit der zugehörigen Information über Datentyp, Länge des Attributes usw. ausgeben. Konsultieren Sie das Datenbank-Handbuch zum Stichwort 'Systemtabellen'. Juni 2006 Arno Schmidhauser 30/43 Aufgabensammlung Datenbankkurs SWS Teil IV Anbindung an Java mit JDBC Juni 2006 Arno Schmidhauser 31/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 32/43 Aufgabensammlung Datenbankkurs SWS 21 Übung Support-Servlet Folgende Supportfälle sollen mit einer Web-Lösung realisiert werden. Support-Applikation Supportfall eingeben Kunde SupportFälle anzeigen «erweitert» Mitarbeiter Supportfall beantworten Die Weblösung basiert auf HTML-Formularen und einem oder mehreren Servlets (JavaProgramm im Webserver). Die Prozesskette sieht wie folgt aus: HTML - Applikation HTTP Webserver Servlet SQL im Intranet JDBC Extranet DMZ RDB Server Intranet Das Servlet wird über zwei Einstiegsformulare aufgerufen: Support.html und Kunde.html. Das Erste ist für die Support-Mitarbeiter und dient dem Anzeigen und später dem Übernehmen von Supportfällen. Das zweite ist für die Kunden und dient dem Eingeben von Supportfällen. Juni 2006 Arno Schmidhauser 33/43 Aufgabensammlung Datenbankkurs SWS 21.1 Aufgaben F55 Supportfälle anzeigen Supportfälle sollen als HTML-Webseite dargestellt werden. Der Support-Mitarbeiter kann in einem Eingabe-Formular (http://localhost/Support.html) den gewünschten Status (eingegangen, übernommen, abgeschlossen) wählen und sich die dazugehörenden Supportfälle anzeigen lassen. Die Anzeige soll folgende Felder umfassen: Für jeden Fall soll Eingangsdatum, Name des Kunden, Name des Produktes und Beschreibung des Falles angezeigt werden. Bei Fällen im Status 'übernommen' oder 'abgeschlossen' soll auch der Name des Mitarbeiters ausgegeben werden, der den Fall bearbeitet. doc\SupportFallAnzeige.html enthält ein paar Beispiele von HTML-Code, wie die Supportfälle dargestellt werden können. Dieser Code muss natürlich im Servlet dynamisch generiert werden, die Datei wird also nicht effektiv via Browser aufgerufen. F56 Supportfall übernehmen Ändern Sie das Servlet aus Aufgabe F55 so, dass bei der Anzeige der eingegangenen Fälle jeweils rechts von jedem Fall ein Feld für die Eingabe der Mitarbeiter ID und ein Knopf mit der Beschriftung 'übernehmen' steht. Beim Drücken des Knopfes wird das Servlet veranlasst, den Mitarbeiter in der Datenbank diesem Fall zuzordnen. Die Datei doc\SupportFallUebernahme.html enthält ein Beispiel von HTML-Code, wie die Supportfälle dargestellt werden können und wie Knöpfe eingebaut werden können. Dieser Code muss natürlich im Servlet dynamisch generiert werden, die Datei wird also nicht effektiv via Browser aufgerufen. Das Übernehmen des Supportfalles soll in der ersten Stufe nur gerade mit einem einfachen 'Fall wurde übernommen' quittiert werden. In einer zweiten Stufe können Sie direkt ein HTML-Formular ausgeben, in das der Antworttext an den Kunden eingegeben werden kann. Das Übernehmen des Antworttextes führt dann zum Abschluss des Falles. F57 Supportfall eingeben Der Kunde soll nun effektiv einen Supportfall eingeben können. Ausgangspunkt ist das Eingabe-Formular (http://localhost/Kunde.html). Die Angaben werden vom SupportServlet übernommen und in die Datenbank übertragen. Die Produktauswahl ist im Formular fix einprogrammiert. Das ist natürlich unschön. Entwickeln Sie anstelle des Formulares ein neues Servlet KundenServlet.java, welches das Eingabe-Formular dynamisch zusammenstellt. Der Aufruf des Servlets erfolgt dann über http://localhost/servlet/KundenServlet. 21.2 Vorbereitung und Hinweise Gemäss Angaben Dozent die ZIP-Datei mit den Vorlagen für die JDBC-Übung entpacken. In das Verzeichis Vorlagen wechseln. Durch Anklicken von startWebserver.bat wird der Webserver gestartet. In einem Browser den URL http://localhost/Support.html eingeben. Es erscheint ein Formular mit dem man Supportfälle nach Status gefiltert abfragen kann. Den Namen der eigenen Datenbank angeben, klicken, und es erscheint eine Seite mit dem Text 'Verbindung aufgebaut.'. Juni 2006 Arno Schmidhauser 34/43 Aufgabensammlung Datenbankkurs SWS Programmieren Sie die Java-Klasse SupportServlet.java nun entsprechend den obigen Aufgaben um. 21.3 Hinweis zur Programmierung Sie können der Einfachheit halber zunächst nur eine fixe Abfrage einbauen, also nur mit Statement und nicht mit PreparedStatement arbeiten. Alles was Sie im Programm an den Browser ausliefern wollen, müssen Sie mit out.println(...) ausgegeben. Juni 2006 Arno Schmidhauser 35/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 36/43 Aufgabensammlung Datenbankkurs SWS Teil V Transaktionen und Technologie Juni 2006 Arno Schmidhauser 37/43 Aufgabensammlung Datenbankkurs Juni 2006 Arno Schmidhauser SWS 38/43 Aufgabensammlung Datenbankkurs SWS 22 Transaktionsmodell F58 Warum müssen SQL-Lesebefehle (select) in einer Transaktion ablaufen, genauso wie Änderungsbefehle (insert, update, delete)? F59 Was bedeutet autocommit? 23 Serialisierbarkeit F60 Der Primärschlüssel einer Tabelle sei gegeben durch einen Integer-Wert. Was halten Sie von folgender Methode für das Erzeugen von Primärschlüsseln? 1. Suche den grössten vorhandenen Wert. 2. Inkrementiere um 1. 3. Benütze diesen neuen Wert als Primärschlüssel für den nächsten einzufügenden Datensatz. F61 Ist mit einem einfachen S/X-Locking-Mechanismus für Datensätze garantiert, dass nur noch serialisierbare Abläufe stattfinden können? Prüfen Sie anhand des folgenden Beispieles: Transaktion 1 Transaktion 2 1.1 select :persnr from Person where name = "Schmid" 2.1 select :persnr from Person where name = "Schmid" 1.2 delete from Person where persnr = :persnr 2.2 1.3 delete from Adressen where persnr = :persnr commit select * from Adressen where persnr = :persnr commit F62 Mit Locking können natürlich nur Daten gesperrt werden, die vorhanden sind. Beschreiben Sie, was passieren könnte, wenn eine Transaktion mehrmals hintereinander den Befehl select * from Person absetzt, währenddesssen eine andere Transaktion neue Personen einfügt. Ist das Verhalten serialisierbar? F63 Was schliessen Sie aus der Lösung der Aufgaben F60 und F60 für die Serialisierbarkeit von Abläufen mit 2-Phasen-Sperr-Protokoll? F64 Jedes Datenbanksystem gibt X-Locks erst beim Transaktionsende frei. Warum? F65 Welcher Isolationsgrad ist mit dem Versionenverfahren von Oracle realisiert realisiert (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)? F66 Stellen Sie sich vor, es gäbe einen Isolationsgrad DIRTY WRITE. Was wären die Konsequenzen für das Transaktionsverhalten des Datenbanksystems? Juni 2006 Arno Schmidhauser 39/43 Aufgabensammlung Datenbankkurs SWS F67 Stellen Sie sich vor, es gäbe einen Isolationsgrad SERIALIZE (im Gegensatz zu SERIALIZEABLE). Was wären die Konsequenzen für das Transaktionsverhalten und die Performance des Datenbanksystems? F68 Was ist ein Range Lock? 24 Concurrency Control in SQL Die folgenden Aufgaben können Sie am besten über die DOS-Konsolenapplikation dbisql lösen. Geben sie hierzu in zwei DOS-Fenstern je folgenden Befehl ein: dbisql -nogui -c "DBN=dbname;UID=dba;PWD=sql" -host hostname -port 2638 Damit wird je eine Session zur Datenbank gestartet. Der Autocommit-Modus ist standardmässig ausgeschaltet, was für die folgenden Aufgaben auch der Fall sein muss. Den Isolationsgrad können Sie ändern mit set transaction isolation level 0 | 1 | 2 | 3 . Den Isolationsgrad abfragen können Sie mit select connection_property ( 'isolation_level' ) F69 Führen Sie in der Session 1 folgenden Befehl durch: update Mitarbeiter set supportZeitTotal = 24 where idMitarbeiter = 3 Führen Sie in der Session 2 unter den Isolationsgraden 0, 1, 2, 3 den Befehl durch: select * from Mitarbeiter; Wie verhält sich Session 2? Achtung: Schliessen Sie nach Beendigung der Aufgabe die Session 1 mit commit ab. F70 Führen Sie in Session 1 die Abfrage durch select * from Mitarbeiter Führen Sie in einer 2 die Abfrage durch update Mitarbeiter set supportZeitTotal = 24 where idMitarbeiter = 1 Bei welchem Isolationsgrad der Session 1 wird das Update von Session 2 behindert? Juni 2006 Arno Schmidhauser 40/43 Aufgabensammlung Datenbankkurs SWS Achtung: Schliessen Sie nach Beendigung der Aufgabe die Session 1 mit commit ab. F71 Führen Sie vorerst in beiden Sessions die folgenden SQL-Befehle durch: create variable totSupp float; select supportZeitTotal into totSupp from Mitarbeiter where idMitarbeiter = 3; (Mit create variable wird eine lokale Variable erzeugt, die während der ganzen Session erhalten bleibt und nur in der erzeugenden Session sichtbar ist) Führen Sie anschliessend in beiden Sessions folgende Befehle durch: update Mitarbeiter set supportZeitTotal = totSupp + 1 where idMitarbeiter = 3; select * from Mitarbeiter; commit; a) Welches Problem tritt auf, wenn die Transaktion in beiden Sessions unter Isolationsgrad 1 ablaufen? b) Was passiert, wenn die beiden Transaktion unter Isolationsgrad 2 ablaufen? c) Können Sie das unter b) entstehende Problem mit Isolationsgrad 3 lösen? d) Könnten Sie den Befehl lock table nutzbringend einsetzen für die Lösung des Problems unter b)? Was sind die Vorteile/Nachteile? F72 Eine Applikation soll ausgeben, welche Supportfälle für das Produkt 3 bisher aufgetreten sind. Ausserdem soll sie die durchschnittliche Zeit ausgegeben, die für die Beantwortung eines Falles benötigt werden. Folgende SQL-Befehle werden also abgesetzt: select * from Fall where idProdukt = 3 and status = 'abgeschlossen'; select avg(zeitAufwand) from Fall where idProdukt = 3 and status = 'abgeschlossen'; Mit welchem Isolationsgrad sollte eine Transaktion für die Ausführung dieser beiden Abfragen arbeiten? F73 Gegeben sei folgende Situation: Die eingegangenen Support-Fälle werden auf einer Liste in einer Web-Applikation angezeigt. Diese Liste wird auf Knopfdruck des Mitarbeiters aktualisiert. Ebenfalls auf Knopfdruck kann der Mitarbeiter einen bestimmten Fall übernehmen. Da mehrere Mitarbeiter gleichzeitig im Support tätig sind, könnten unter Umständen zwei Mitarbeiter versuchen, denselben Fall zu übernehmen. Beschreiben Sie ein Vorgehen, wie sichergestellt wird, dass ein Fall nur von einem Mitarbeiter übernommen werden kann. Schreiben Sie die notwendigen SQL-Befehle hin. Juni 2006 Arno Schmidhauser 41/43 Aufgabensammlung Datenbankkurs SWS F74 Führen Sie die Befehle aus der Lösung von Aufgabe F73 mit Hilfe von zwei Interactive SQL Fenstern durch und verifizieren Sie die Lösung. Was passiert, wenn zwei Transaktionen gleichzeitig die Schritte 4 und 5 durchführen? 25 Lange Transaktionen F75 Nennen Sie Anwendungsbeispiele wo das Arbeiten mit dem Checkout/CheckinVerfahren angebracht ist. F76 Nennen Sie Anwendungsbeispiele wo das Arbeiten mit dem Zeitstempel/PrüfregelVerfahren angebracht ist. 26 Recovery F77 Wodurch unterscheidet sich ein Betriebssystem-Backup von einem On LineBackup eines Datenbanksystems? 27 Optimierung F78 Gegeben seien folgende zwei SQL-Definitionen: create table Person ( name varchar( 100 ), vorname varchar( 100 ), lebenslauf varchar( 1800 ) ); create index ixname on Person ( name ); Die Tabelle Person sei 10‘000 Einträge gross. Der Index ixname sei ein B*-Baum mit jeweils 10 Schlüsselwerten pro Index-Knoten (Rechnen Sie mit der Vereinfachung, dass die Anzahl Schlüssel pro Knoten = Verzweigungsgrad des Index = 10 ist). Die Anfangsbuchstaben der Namen seien gleichverteilt über das Alphabet und das Alphabet habe 25 Buchstaben. Jeder Personeneintrag in der Tabelle person belege eine ganze I/O-Page (2000 Bytes). Mit wievielen Zugriffen (Indexknoten und I/O-Pages mit Datensätzen) kann folgende Abfrage voraussichtlich abgearbeitet werden? select * from Person where name like 'S%' and vorname != ´Peter´ F79 Gegeben sei folgende, leicht andere Tabelledefinition als in Aufgabe 27: create table Person ( name varchar( 100 ), vorname varchar( 100 ), create index ixname on Person ( name ); Juni 2006 Arno Schmidhauser 42/43 Aufgabensammlung Datenbankkurs SWS Die Tabelle Person sei 10‘000 Einträge gross. Der Index ixname sei ein B*-Baum mit jeweils 10 Schlüsselwerten pro Index-Knoten. Die Anfangsbuchstaben der Namen seien gleichverteilt über das Alphabet und das Alphabet habe 25 Buchstaben. Auf einer I/O-Page in der Tabelle Person finden 10 Personen Platz. a) Mit wievielen Zugriffen (Indexknoten und I/O-Pages mit Datensätzen) kann folgende Abfrage abgearbeitet werden? select * from person where name like 'S%' and vorname != ´Peter´ b) Mit wievielen Zugriffen (Indexknoten und I/O-Pages mit Datensätzen) kann folgende Abfrage abgearbeitet werden (Alle Personen, deren Name mit A, B oder C beginnt)? F80 select * from person where name >= 'A' and name < 'D' and vorname != ´Peter´ F81 Gegeben seien folgende SQL-Tabellen und Indices: create table Person ( idPerson numeric( 4,0 ), name varchar( 100 ), vorname varchar( 100 ), kommentar varchar( 1700 ) ); create table Adresse idPerson numeric( strasse varchar( ort varchar( kommentar varchar( ( 4,0 ), 100 ), 100 ), 1700 ) ); create index ixPName on Person ( name ); create index ixIdPPerson on Person ( idPerson ); create index ixIdPAdresse on Adresse ( idPerson ); F82 Jede Person habe durchschnittlich eine Adresse. Jede Adresse benötigt eine I/OPage und jeder Indexknoten enthält 10 Schlüsselwerte. F83 a) Wie könnte folgende Abfrage abgearbeitet werden und wieviele Zugriffe werden dafür benötigt? F84 select * from Person join Adresse where Person.name like 'S%' F85 b) Wie könnte folgende Abfrage abgearbeitet werden und wieviele Seitenzugriffe werden dafür benötigt? F86 select * from Person join Adresse Juni 2006 Arno Schmidhauser 43/43
