Aufgabensammlung Datenbankkurs Lösungen

Aufgabensammlung
Datenbankkurs
Lösungen
© Dr. Arno Schmidhauser
Letzte Revision: 1. Juni 2006
Email: [email protected]
Webseite: http://www.sws.bfh.ch/db
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
2/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Inhalt
Teil I - Datenmodellierung
1
2
Die Support-Applikation................................................................................. 7
Lösung Evaluationssystem ........................................................................... 11
Teil II - Relationenmodell und Normalisierung
3
4
Schlüssel................................................................................................... 17
Normalisierung........................................................................................... 18
Teil III - SQL
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Vorbemerkungen........................................................................................ 23
Erstellen von Tabellen ................................................................................. 24
Domains ................................................................................................... 24
Abfragen mit einer Tabelle ........................................................................... 25
Einfaches Einfügen, Ändern, Löschen............................................................. 26
Ändern der Tabellenstruktur......................................................................... 27
Abfragen mit Gruppierung ........................................................................... 27
Abfragen mit Unterabfragen in der where-Klausel............................................ 28
Abfragen mit Inner Join ............................................................................... 29
Abfragen mit Outer Join .............................................................................. 31
Abfragen mit Self Join ................................................................................. 32
Views ....................................................................................................... 32
Referentielle Integrität, Constraints............................................................... 33
Trigger...................................................................................................... 33
Funktionen ................................................................................................ 35
Prozeduren ................................................................................................ 36
Teil IV - Java Anbindung mit JDBC
21
Übung Support-Servlet................................................................................ 41
Teil V - Transaktionen und Technologie
22
23
24
25
26
27
Transaktionsmodell..................................................................................... 45
Serialisierbarkeit, Concurrency-Control .......................................................... 45
Concurrency Control in SQL ......................................................................... 48
Lange Transaktionen................................................................................... 51
Recovery................................................................................................... 52
Optimierung .............................................................................................. 52
Juni 2006
Arno Schmidhauser
3/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
4/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Teil I
Datenmodellierung
Juni 2006
Arno Schmidhauser
5/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
6/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
1 Die Support-Applikation
1.1 UML-Design
F1
vertritt
«Entity»
Mitarbeiter
name
vorname
supportZeitTotal
0..1
0..*
0..*
0..1
«Entity»
Fall
eingegangemAm
abgeschlossenAm
status {...}
kategorie {...}
beschreibung
zeitaufwand
antwort
0..*
1
wird vertreten durch
«Entity»
Produkt
name {key}
version {key}
datenblatt
mimeType
1
0..*
1
«Entity»
Kunde
name
email {key}
telefon
passwort
0..*
1
0..*
«Entity»
Lizenz
lizenzKey {key}
gültigBis
Mit Hilfe der Aggregations- und Kompositions-Beziehung kann folgendes ausgedrückt
werden: Eine Aggregation bedeutet, dass das aggregierte Objekt unter Umständen den
Besitzer wechseln kann. Beispielsweise kann ein Fall einem anderen Mitarbeiter zugewiesen werden. Eine Komposition bedeutet, dass das Komponentenobjekt niemals seinen
Besitzer wechselt. Eine Lizenz kann beispielsweise nicht auf einen anderen Kunden oder
ein anderes Produkt übertragen werden.
Die Kompositionen bedeuten eine existentielle Abhängigkeit. Wenn der Kunde gelöscht
wird, werden auch seine Lizenzen gelöscht. Wenn ein Produkt gelöscht wird, müssen
auch die Lizenzen entfernt oder die Löschung des Produktes verboten werden. Diese Abhängigkeit ist aber bereits in den Multiplizitäten dargestellt. Die Verwendung von Komposition oder Aggregation drängt sich für diesen Zweck also nicht unbedingt auf.
Juni 2006
Arno Schmidhauser
7/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
1.2 State-Event Diagramm für 'Fall'
F2
Übernahme durch Mitarbeiter
eingegangen
uebernommen
Antwort erstellt und Kunde informiert
abgeschlossen
1.3 Normalisiertes Tabellenmodell
F3
«Table»
Mitarbeiter
«PK» idMitarbeiter : numeric(10,0)
«FK» idMitarbeiterStv{null} : numeric(10,0)
name : varchar
vorname : varchar
supportZeitTotal : float
«Table»
Fall
«PK» idFall : numeric(10,0)
«FK» idMitarbeiter{null} : numeric(10,0)
«FK» idProdukt : numeric(10,0)
«FK» idKunde : numeric(10,0)
status{domain} : SupportStatus
kategorie {domain} : SupportKategorie
eingegangemAm : timestamp
abgeschlossenAm : timestamp
beschreibung : CLOB
antwort : CLOB
zeitaufwand : float
«Table»
Produkt
«PK» idProdukt : numeric(10,0)
«SK» name : varchar
«SK» version : varchar
datenblatt : BLOB
mimeType : varchar
«Table»
Lizenz
«PK» idLizenz : varchar
«FK» idKunde : numeric(10,0)
«FK» idProdukt : numeric(10,0)
«SK» lizenzKey{check} : varchar
gültigBis : date
«Table»
Kunde
«PK» idKunde : numeric(10,0)
«SK» email : varchar
name : varchar
telefon : varchar
passwort : varchar
Für alle Attribute wurde ein Datentyp angegeben. Bei varchar wurde aber auf die Längenangabe verzichtet (z.B. varchar(32) ).
Gewisse Attribute haben im Prinzip den Typ varchar, jedoch nur eine sehr eingeschränkte Anzahl möglicher Werte. Beispielsweise hat das Attribut kategorie in der Tabelle Fall
nur einer der Werte Softwarefehler, Bedienungsproblem, Lizenzierungsfrage. Dieser Sachverhalt wird in einer relationalen Datenbank häufig mit einem Domain implementiert. Der Domain ist eine Einschränkung der Werte für einen Basistyp. Die Einschränkung kann aufgrund einer Regel (zum Beispiel ein regulärer Ausdruck) oder mit
Hilfe einer Auswahltabelle mit den möglichen Werten erfolgen. Im obigen Modell ist die
Juni 2006
Arno Schmidhauser
8/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Verwendung einer Domäne durch die Angabe {domain} und den Namen der Domäne
SupportKategorie resp. SupportStatus in der Tabelle Fall angezeigt. Die gleiche Domäne kann unter Umständen in anderen Tabellen wiederverwendet werden.
Verletzungen der dritten Normalform
Man könnte argumentieren, dass das Attribut status in der Tabelle Fall eine Funktion
der Attribute idMitarbeiter und abgeschlossenAm ist, gemäss folgender Vorschrift:
idMitarbeiter
nicht gesetzt
gesetzt
gesetzt
abgeschlossenAm
nicht gesetzt
nicht gesetzt
gesetzt
->
->
->
status
eingegangen
uebernommen
abgeschlossen
Theoretisch liegt hier eine Verletzung der dritten Normalform vor. Die Verletzung liegt
aber nur vor, wenn wirklich nur drei Werte für das Status-Attribut existieren. In der Praxis wird man aber besser annehmen, dass status weitere Werte bekommen kann, oder
dass abgeschlossemAm auch unbekannt und der Fall trotzdem abgeschlossen sein kann.
Dann liegt keine Verletzung der dritten Normalform mehr vor.
1.4 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.
Nachteile
Ein Lizenz muss zwingend vorliegen. Wenn ein Kunde mehrere Lizenzen für dasselbe Produkt hat, muss der Supportfall eventuell willkürlich einer der vorhandenen Lizenzen zugeordnet werden, auch wenn der Fall gar nicht mit der speziellen
Lizenz, sondern nur mit dem Produkt im Allgemeinen zu tun hat. Das könnte zu
Miss-Interpretationen bei Auswertungen über die Lizenz-Tabelle führen.
Vorteile
Es ist besser ersichtlich, dass nur für lizenzierte Produkte Support geleistet wird.
Sobald effektiv lizenzspezifische Supportfälle auftreten ( "Mein Lizenzkey funktioniert nicht" ), können diese auch wirklich einer bestimmten Lizenz zugeordnet
werden.
Ähnliche Zusammenhänge können eventuell über mehrere Wege hergestellt werden. Ob die Wege die gleiche oder unterschiedliche Aussagen haben, ist genau zu
prüfen.
Wenn idLizenz in der Tabelle Fall aufgenommen wird, können die Attribute idProdukt und idKunde entfernt werden. Diese Information ist ja dann über die Lizenz-Tabelle ersichtlich.
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?
Juni 2006
Arno Schmidhauser
9/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Neues Attribut idVorgaengerFall in der Tabelle Fall einfügen (alter table Befehl verwenden).
Soll es mehrere Vorgängerfälle geben können, muss eine neue Tabelle erstellt
werden:
«Table»
FallVerknuepfung
«PFK» idFallVon : numeric(10,0)
«PFK» idFallNach : numeric(10,0)
Diese Tabelle könnte auch benützt werden, wenn es für jeden Fall nur einen Vorgänger gibt. Die Tabelle Fall müsste dann nicht geändert werden und die Abhängigkeiten zwischen Fällen ist sauber isoliert, an einem eigenen Ort, definiert.
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?
Ergänzung der Tabelle SuportKategorie um die Stundensatz-Information:
«Domain»
SupportKategorie
«PK» kategorie : varchar
stundensatz : float
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.
Erstellen einer neuen der Tabelle Stundensatz:
«Domain»
SupportKategorie
«PK» idKategorie : numeric(10,0)
kategorie : varchar
Juni 2006
«Domain»
Stundensatz
«PK» idStundensatz
«FK» idKategorie : numeric(10,0)
stundensatz : float
gueltigAb : date
Arno Schmidhauser
10/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
2 Lösung Evaluationssystem
F8
Konzeptionelles Datenmodell
«Entity»
Dozent
email {key}
name
vorname
passwort
«Entity»
Fach
abkürzung {key}
titel
beschreibung
«Entity»
Klasse
bezeichnung {key}
beschreibung
0..*
0..*
«Entity»
Student
email {key}
name
vorname
1
1
1
1..*
0..*
«Entity»
FragebogenTyp
typName {key}
beschreibung
0..*
1
0..*
«Entity»
Umfrage
versandDatum
status
emailText
1
0..*
1
0..*
0..*
«Entity»
Abschnitt
nummer
titel
«Entity»
Fragebogen
status
eingangsDatum
1
0..*
1
0..*
0..*
«Entity»
Frage
nummer
frageText
«Entity»
MCFrage
minAnzAntworten
maxAnzAntworten
«Entity»
TextFrage
«Entity»
Antwort
1
0..*
«Entity»
WertFrage
minWert
maxWert
einheit
1
«Entity»
MCAntwort
«Entity»
TextAntwort
text
«Entity»
WertAntwort
zahl
0..*
1..*
«Entity»
AntwortMoeglichkeit
beschriftung
wert
0..*
Frage
Juni 2006
Arno Schmidhauser
Antwort
11/54
Aufgabensammlung Datenbankkurs Lösungen
F9
SWS
Mehrsprachigkeit
Mit folgender Erweiterung des konzeptionellen Modelles kann man der Mehrsprachigkeit
Rechnung tragen:
Als Beispiel
die Tabelle 'Frage'
«Entity»
Frage
nummer
«Entity»
TextElement
text
1..*
0..*
1
«Entity»
Sprache
isoCode
isoBezeichnung
isoCode und isBezeichnung sind zu finden in ISO 639-1
Juni 2006
Arno Schmidhauser
12/54
Aufgabensammlung Datenbankkurs Lösungen
F10
SWS
Abbildung von Vererbungshierarchien
Man unterscheidet drei Varianten:
1. Eine einzige Tabelle für die gesamte Vererbungshierarchie
•
•
•
Attribute der Basisklasse gelten für jeden Datensatz
Attribute von abgeleiteten Klassen sind nur belegt, wenn der Datensatz ein Objekt
dieser Klasse repräsentiert.
Es ist ein Typ-Attribut erforderlich, dass für jeden Datensatz angibt, zu welcher
Klasse das repräsentierte Objekt gehört.
Vorteile: Kompakt, Abfragen über alle Objektarten sind einfach zu handhaben.
Nachteil: Viele Attribute ohne Belegung mit Werten. Attributzuordnung zu Klasse
nicht ersichtlich. Fehlverhalten möglich, indem Attributen Werte zugeordnet werden,
die gar keine haben dürften → Integritätsbedingungen erforderlich.
Anwendung: Hauptinformation in der Basisklasse, abgeleitete Klassen nicht zu umfangreich, oder insgesamt wenig Attribute zu implementieren. Beispiele: PersonenVerwaltung; vorliegendes Evaluationssystem.
2. Eine Tabelle pro abgeleitete Klasse
•
Die Attribute der Basisklasse werden in jeder Tabelle der abgeleiteteten Klasse erstellt. Für die Basisklasse selbst wird keine Tabelle erstellt.
Vorteile: Jede Tabelle entspricht genau einer Klasse. Es sind keine nicht-belegten Attribute vorhanden.
Nachteil: Abfragen über mehrer Klassen hinweg erfordern union-Abfragen (kann teilweise Probleme mit SQL geben). Änderungen an der Basisklasse erfordern Änderungen in allen Tabellen der abgeleiteten Klassen.
Anwendung: Basisklasse nur schwach belegt und abstrakt. Meistens werden nur konkrete einzelne Klassen in einer Applikation benötigt. Beispiele:
Bemerkung: Bei mehreren Vererbungsstufen ist hier die Meinung, dass pro endständige Klasse im Vererbungsbaum eine Tabelle erstellt wird, die alle Klassen des ganzen
Weges von der Basisklasse bis zur letzten abgeleiteten Klasse umfasst.
3. Eine Tabelle pro Klasse
•
•
Jede Tabelle enthält nur die Attribute ihrer Klasse.
Der Primärschlüssel der Basistabelle wird in allen abgeleiteten Klassen ebenfalls
als Primärschlüssel verwendet und gleichzeitig als Fremdschlüssel auf die Basistabelle.
Vorteile: Konzeptionelles Modell im Tabellenmodell noch ersichtlich.
Punktuelle Änderungen am Konzept haben nur punktuelle Änderungen in den Tabellen
zur Folge.
Mit dieser Variante ist es datenbankseitig möglich, dass ein Objekt in mehreren Klassen vorkommt1! Beispielsweise kann in einem CRM-System eine Person gleichzeitig
Kunde und Lieferant sein.
1
Ist auch bei der Ein-Tabellen-Variante möglich, erfordert dann aber ein Typattribut mit MehrfachWerten → Zusätzliche Tabelle erstellen oder mit nicht normalisierter Tabelle arbeiten.
Juni 2006
Arno Schmidhauser
13/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Nachteile: Gewisse Arten von Abfragen sind umständlich, beispielsweise wenn man
wissen will, zu welcher Klasse ein Datensatz in der Basistabelle gehört. Es muss dann
in jeder abgeleiteten Tabellen nach einem entsprechenden Datensatz gesucht werden.
Wenn man verhindern will, dass ein Datensatz der Basistabelle in mehreren abgeleiteten Tabellen vorkommt, müssen zusätzliche Integritätsbedingungen definiert werden.
Anwendung: Viele Attribute, sowohl in der Basisklasse, wie in den abgeleiteten Klassen vorhanden. Mehrstufige Vererbungshierarchie. Flexible Datenstruktur für unterschiedlichste Bedürfnisse und Abfrage-Arten erforderlich. Häufige Änderung des Datenmodells wahrscheinlich.
Lösung mit Variante 1 für das Evaluationssystem, Frage-Antwort Teil:
«Table»
Frage
«PK» idFrage[1]
«FK» idAbschnitt[1]
nummer[1]
frageText[1]
frageTyp[1]
minWert[0..1]
maxWert[0..1]
einheit[0..1]
minAnzAntworten[0..1]
maxAnzAntworten[0..1]
«Table»
Antwort
«PK» idAntwort[1]
«FK» idFrage[1]
antwortTyp[1]
zahl[0..1]
text[0..1]
«Table»
AntwortMoeglichkeit
«PK» idAntwortMoeglichkeit[1]
«FK» idFrage[1]
beschriftung[1]
wertNumerisch[0..1]
wertNominal[0..1]
«Table»
GewaehlteAntwort
«PFK» idAntwortMoeglichkeit[1]
«PFK» idAntwort[1]
Frage
Juni 2006
Arno Schmidhauser
Antwort
14/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Teil II
Das Relationenmodell
Juni 2006
Arno Schmidhauser
15/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
16/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
3
Schlüssel
F11
Was ist der Unterschied zwischen einem Relationenschlüssel und einem Primärschlüssel?
Es kann mehrere Relationenschlüssel geben. Unter den Relationenschlüsseln wird
einer zum Primärschlüssel gemacht. Die anderen Relationenschlüssel können als
Sekundärschlüssel festgelegt werden.
F12
Was ist der Unterschied zwischen einem Primär- und einem Sekundärschlüssel?
Der Primärschlüssel wird in anderen Tabellen der Datenbank zur Referenzierung
der Tabelle mit dem Primärschlüssel verwendet. Der Sekundärschlüssel wird nur
in der Tabelle verwendet, wo er definiert wurde. Ein häufiges Vorgehen ist, dass
Primärschlüssel ausschliesslich datenbankintern und kurzfristig von Anwendungsprogrammen benützt werden, während Sekundärschlüssel für die externe permanente Referenzierung eines Datensatzes gebraucht werden. Beispiel: interner Primärschlüssel ist eine fortlaufende Personal-Nummer, externer Sekundärschlüssel
ist die AHV-Nummer.
F13
Beschreiben Sie Anforderungen an einen Primärschlüssel.
1) Primärschlüssel sollten nicht geändert werden müssen
2) Primärschlüssel sollten keine Semantik tragen (z.B. Datum der Generierung,
mögliche Strukturänderungen des Primärschlüssels würden sich auf Applikationslogik auswirken).
3) Effiziente Erzeugbarkeit (Zufallszahlen sind extrem effizient)
4) Eindeutigkeitsbereich (tabellenbezogen, datenbankweit, global)
5) Fälschungssicherheit (Verwendung von Zufallszahlen oder Verschlüsselung)
6) Datentyp (Double und Float sind schlecht bezüglich Rundungsfehlern. Applikation und Datenbank müssen den Datentyp kennen, z.B. kennt PHP nur integer
bis 4 Byte. String-Typen sind flexibler und besser transportierbar ).
7) Wertebereich (integer mit 4 Byte Grösse können knapp werden!)
F14
Zählen Sie Erzeugungsmöglichkeiten für Primärschlüssel auf.
Applikation-Algorithmus (Client ID + lokale Nummer oder Zeitstempel )
Globale Zufallszahl ( GUID )
Hilfstabelle in der Datenbank ( Zählertabelle, siehe Aufgabe F52 )
Autoincrement-Spalte pro Tabelle
Juni 2006
Arno Schmidhauser
17/54
Aufgabensammlung Datenbankkurs Lösungen
4
F15
SWS
Normalisierung
In welcher Normalform befindet sich eine Relation mit zwei Attributen (inkl.
Schlüssel) immer, sofern sie bereits in erster Normalform ist?
Immer in dritter Normalform. Es gibt zwei mögliche Fälle: Nur eines der Attribute
ist der Primärschlüssel. Da nur ein Attribut Schlüssel ist, gilt automatisch 2 NF. Da
ausserhalb des Schlüssels nur ein einziges Attribut existiert, können keine transitiven Abhängigkeiten bestehen, es gilt also 3 NF. Für den Fall, dass beide Attribute
zum Schlüssel gehören, gilt ebenfalls 3 NF. Es gibt ja keine Attribute ausserhalb
des Schlüssels und damit entfällt jede Möglichkeit, dass die zweite oder dritte
Normalform verletzt ist.
F16
Welche Probleme bezüglich Normalisierung sehen Sie in folgender Tabelle?
Buch
exemplarNr
1
2
3
4
5
isbn
123-123
123-123
123-123
123-124
123-125
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
Die Tabelle ist in zweiter, aber nicht in dritter Normalform. Der Titel eines Buches
ist eine Funktion der isbn, welche selbst nicht zum Schlüssel gehört. Die voll normalisierten Tabellen sehen wie folgt aus:
Exemplar
Buch
F17
exemplarNr
1
2
3
4
5
isbn
123-123
123-124
123-125
isbn
123-123
123-123
123-123
123-124
123-125
standort
Bibliothek
Abteilung I
Abteilung W
Bibliothek
Bibliothek
titel
SQL für Profis
XML-Datenbanken
UML und Datenbanken
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
Doz#
1
1
2
2
2
3
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
Folgende Abhängigkeiten können festgestellt werden: Ku_Name, Doz# und
Doz_Name sind nur von Ku# abhängig. Damit ist die zweite Normalform verletzt.
Eine Normalisierung in 2NF ergibt:
Veranstaltung3NF
Juni 2006
Ku#
10
10
Datum
19. April 02
12. April 02
Arno Schmidhauser
18/54
Aufgabensammlung Datenbankkurs Lösungen
11
12
12
13
Kurs2NF
Doz#
1
2
2
3
13.
21.
28.
30.
Doz_Name
Huber
Iselin
Iselin
Fierz
SWS
April 02
Mai 02
Mai 02
Juni 02
Ku#
10
11
12
13
Ku_Name
ProjMan
Java
C++
Math
In der Tabelle Kurs ist Doz_Name von Doz# abhängig. Dies ist eine transitive Abhängigkeit, verletzt also die dritte Normalform. Es ergibt sich folgende Normalisierung:
Kurs3NF
Ku#
10
11
12
13
Dozent3NF
Ku_Name
ProjMan
Java
C++
Math
Doz#
1
2
3
Doz#
1
2
2
3
Doz_Name
Huber
Iselin
Fierz
Das Schlussresultat sind die drei Tabellen Veranstaltung3NF, Kurs3NF und Dozent3NF.
F18
Stellen Sie die funktionalen Abhängigkeiten in untenstehender Tabelle dar. Überlegen Sie vorerst die Bedeutung der AHV-Nr.
Dozent
Doz#
1
2
3
Doz_Name
Huber
Iselin
Fierz
AHV-Nr
822.59.268.113
162.62.468.122
423.56.487.132
Vorerst könnte man meinen, dass eine transitive Abhängigkeit vorliegt und damit
die 3NF verletzt ist. Da die AHV-Nr aber ein Sekundärschlüssel ist, und Sekundärschlüssel per Definition nicht an einer transitiven Abhängigkeit teilnehmen, ist die
3NF nicht verletzt.
F19
Prüfen Sie folgende Excel-Tabellen auf die Normalisierung und normalisieren sie
allenfalls:
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
19/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Matrikelnr ist Primärschlüssel. Erste Normalform erzeugen. MatrikelNr und Fach
werden zu Primärschlüssel. Name ist partiell von MatrikelNr abhängig. Zweite
Normalform erzeugen. Es entstehen die Tabellen 'Student' und 'Notenblatt'. Beide
sind in dritter Normalform.
Matrikelnr
05-477-611
05-477-611
05-477-611
05-477-642
05-477-642
05-477-642
05-477-681
05-477-681
05-477-681
Fach
Arbeitstechnik
Recht
Projektmanagement
Arbeitstechnik
Recht
Projektmanagement
Arbeitstechnik
Recht
Projektmanagement
Matrikelnr
05-477-611
05-477-642
05-477-681
Note
A
D
E
F
C
B
C
B
E
Name
Allensbach Peter
Binggeli Martin
Zyssett Claude
Normalisierung ist eine Notwendigkeit, bedeutet aber noch nicht, dass ein gutes
Design vorliegt, weil die Normalisierung einzig die vorhandenen Attribute
berücksichtigt. Das Hinzufügen oder Wegnehmen von Attributen kann die
Normalisierung wesentlich beeinflussen.
Juni 2006
Arno Schmidhauser
20/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Teil III
SQL
Juni 2006
Arno Schmidhauser
21/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
22/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
5 Vorbemerkungen
(keine Lösungen)
Juni 2006
Arno Schmidhauser
23/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
6 Erstellen von Tabellen
F20
Erstellen Sie eine Tabelle Fall nach folgenden Anforderungen:
ƒ
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.
create table Fall (
idFall
numeric(10,0)
idProdukt
numeric(10,0)
idKunde
numeric(10,0)
idMitarbeiter
numeric(10,0)
eingegangenAm
timestamp
default
current
abgeschlossenAm
timestamp
default null,
status
SupportStatus
kategorie
SupportKategorie
beschreibung
long varchar
antwort
long varchar
zeitaufwand
float
default null,
primary key
(idFall),
);
not null,
not null,
not null,
null,
not null
timestamp,
null
not null,
not null,
not null,
null,
null
Lösung siehe auch Datei table_Fall.sql und Testdaten für diese Tabelle in data_Fall.sql.
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.
create domain SupportKategorie varchar(64)
check ( @wert in ( select kategorie
from SupportKategorie ) );
Juni 2006
Arno Schmidhauser
24/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
create domain SupportStatus varchar(64)
default 'eingegangen'
check ( @wert in ( 'eingegangen', 'uebernommen',
'abgeschlossen' ) );
create table SupportKategorie (
kategorie
varchar(32)
primary key
(kategorie)
);
not null,
insert into SupportKategorie ( kategorie )
values ( 'Softwarefehler' );
insert into SupportKategorie ( kategorie )
values ( 'Bedienungsproblem' );
insert into SupportKategorie ( kategorie )
values ( 'Lizenzierungsfrage' );
8
Abfragen mit einer Tabelle
F22
Menge aller Kundeneinträge, sortiert nach Name. Sortierung aufsteigend.
select *
from Kunde
order by name
F23
Menge aller Kundeneinträge ohne Telefonangabe (telefon ist null).
select * from Kunde
where telefon is null
Die Bedingung telefon = null geht nicht. Zwei Werte sind nur gleich (im Sinne des
Vergleichsoperators) wenn beide Werte bekannt und gleich sind. Ist einer der
Werte nicht bekannt (null) kann das Datenbank-system nicht entscheiden, ob die
Bedinung jetzt erfüllt ist oder nicht. Der Datensatz kommt daher nicht in die Resultatmenge.
F24
Was ergibt die Abfrage
select name from Kunde
where name like 'O%' ?
Alle Kunden, deren Namen mit O, o, ö, Ö beginnt.
F25
Menge aller abgeschlossen Supportfälle zwischen 1. und 7. Juni 2003. Arbeiten
Sie mit einem Tabellenalias.
select * from Fall f
where f.abgeschlossenAm > '1.6.2003'
and f.abgeschlossenAm < '8.6.2003'
and f.status = 'abgeschlossen'
Zu beachten ist, dass sich eine Datumsangabe ohne Zeitteil auf Mitternacht desselben Tages bezieht. Der Wert '1.1.2003' heisst also ausgeschrieben
'1.1.2003 00:00:00'
Juni 2006
Arno Schmidhauser
25/54
Aufgabensammlung Datenbankkurs Lösungen
F26
SWS
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'.
select
idFall,
dateformat( abgeschlossenAm, 'DD.MM.YYYY HH:NN' )
AbschlussDatum,
datediff( hour, eingegangenAm, abgeschlossenAm) as
'Dauer in Stunden'
from Fall
where status = 'abgeschlossen'
order by "Dauer in Stunden" desc
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.
insert into Kunde( idKunde, name, email,
telefon, passwort )
values ( 13, 'Paul Student', '[email protected]',
'031 33 55 111', 'pssws' );
insert into Lizenz( idLizenz, idKunde,
idProdukt, lizenzKey )
values ( 16, 13, 3, '01-01-01-01' );
oder
insert into Lizenz( idLizenz, idKunde,
idProdukt, lizenzKey )
select 16, 13, idProdukt, '01-01-01-01'
from Produkt
where name = 'BancomatPlus';
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.
Juni 2006
Arno Schmidhauser
26/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
delete Kunde
where idKunde = 13
Das Löschen der Lizenz ist nicht notwendig, weil eine automatische Löschung aufgrund der foreign key-Bedingung in der Lizenz-Tabelle stattfindet.
F29
Was ist der Unterschied zwischen dem Befehl drop und delete?
delete löscht nur den Inhalt einer Tabelle, drop löscht den Inhalt und die gesamte
Struktur inkl. allfällige Trigger und Indices.
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.
alter table Mitarbeiter
add idStellvertreter numeric(10,0) null;
F31
Ändern Sie die Tabelle Mitarbeiter, indem Sie ein paar Stellvertretungen eintragen.
update Mitarbeiter set idStellvertreter = 2
where idMitarbeiter = 1
usw.
11
Abfragen mit Gruppierung
F32
Anzahl abgeschlossene Supportfälle pro Kategorie.
select kategorie, count(*)
from Fall
where status = 'abgeschlossen'
group by kategorie
F33
Anzahl Fälle, gesamter und durchschnittlicher Zeitaufwand aller Supportfälle pro
Kategorie, gerundet auf 2 Stellen (round()-Funktion).
select kategorie, count(zeitaufwand),
round(avg(zeitaufwand),2),
Juni 2006
Arno Schmidhauser
27/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
round(sum(zeitaufwand),2)
from Fall
where status = 'abgeschlossen'
group by kategorie
F34
Welche Produkte (nur idProdukt und gesamter Supportaufwand angeben) haben
insgesamt mehr als 10 Stunden Supportaufwand versursacht. Sortieren Sie absteigend nach Aufwand.
select idProdukt, sum(zeitaufwand) as aufwand
from Fall
group by idProdukt
having aufwand > 10
order by aufwand desc
F35
Liste aller Kunden (nur idKunde), sortiert nach total beanspruchtem Supportaufwand. Was passiert mit Kunden, die überhaupt nie Support beansprucht haben?
select idKunde, sum(zeitaufwand) as aufwand
from Fall
group by idKunde
order by aufwand
Kunden ohne je beanspruchten Support tauchen nicht im Resultat auf.
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.
select *
from Kunde
where idKunde not in ( select idKunde
from Lizenz )
oder
select *
from Kunde k
where not exists ( select *
from Lizenz l
where l.idKunde = k.idKunde )
F37
Welche Kunden haben Produkt 4 und Produkt 5 lizenziert (nur mit idProdukt,
nicht mit dem Produktnamen arbeiten)?
select *
from Kunde
where idKunde in ( select idKunde
from Lizenz
where idProdukt = 4 )
and
idKunde in ( select idKunde
from Lizenz
where idProdukt = 5 )
F38
Welche Kunden haben Produkt 4 oder Produkt 5 lizenziert (nur mit idProdukt,
nicht mit dem Produktnamen arbeiten)?
Juni 2006
Arno Schmidhauser
28/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
select *
from Kunde
where idKunde in ( select idKunde
from Lizenz
where idProdukt = 4 or idProdukt = 5 )
13
Abfragen mit Inner Join
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.
Ausführliche Lösung mit expliziter Join-Bedingung:
select l.lizenzKey, k.name, l.gueltigBis
from Lizenz l, Kunde k
where l.idKunde = k.idKunde
Lösung mit join und on-Klausel:
select l.lizenzKey, k.name, l.gueltigBis
from Lizenz l join Kunde k on l.idKunde = k.idKunde
Lösung mit einem Natural Join (Schlüsselwort natural join):
select l.lizenzKey, k.name, l.gueltigBis
from Lizenz l natural join Kunde k
F40
Liste aller abgeschlossenen Supportfälle mit Name des Mitarbeiters, Abschlussdatum und Beschreibung des Falles (Neueste Fälle zuerst).
Ausführliche Lösung mit expliziter Join-Bedingung:
select m.name, f.abgeschlossenAm, f.beschreibung
from Mitarbeiter m, Fall f
where m.idMitarbeiter = f.idMitarbeiter
and f.status = 'abgeschlossen'
order by f.abgeschlossen desc
Lösung mit join und on-Klausel:
select m.name, f.abgeschlossenAm, f.beschreibung
from Mitarbeiter m join Fall f
on m.idMitarbeiter = f.idMitarbeiter
where f.status = 'abgeschlossen'
order by f.abgeschlossenAm desc
Lösung mit einem Natural Join (Schlüsselwort natural join):
select m.name, f.abgeschlossenAm, f.beschreibung
from Mitarbeiter m natural join Fall f
where f.status = 'abgeschlossen'
order by f.abgeschlossen desc
F41
Liste aller eingegangenen Supportfälle mit Name des Produktes, Name des Kunden, Beschreibung und Eingangsdatum des Falles. Sortiert nach Eingangsdatum.
Ausführliche Lösung mit expliziter Join-Bedingung:
Juni 2006
Arno Schmidhauser
29/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
select p.name, k.name, f.eingegangenAm, beschreibung
from Produkt p, Fall f, Kunde k
where p.idProdukt = f.idProdukt
and f.idKunde = k.idKunde
and f.status = 'eingegangen'
order by f.eingangsdatum
Die Reihenfolge der Tabellen in der from-Klausel obiger Abfrage ist belanglos. Die
Join-Bedingung und damit der logische Zusammenhang zwischen je zwei Tabellen
wird ja durch den Benutzer formuliert.
Lösung mit join und on-Klausel:
select p.name, k.name, f.eingegangenAm, beschreibung
from Produkt p join Fall f on p.idProdukt = f.idProdukt join Kunde k
on f.idKunde = k.idKunde
where f.status = 'eingegangen'
order by f.eingegangenAm
Lösung mit einem Natural Join (Schlüsselwort natural join):
select p.name, k.name, f.eingegangenAm, f.beschreibung
from Produkt p natural join Fall f natural join Kunde k
where f.status = 'eingegangen'
order by f.eingegangenAm
Achtung 1: Der Natural Join wird so ausgewertet, dass jeweils für die Tabelle oder
das Zwischenresultat auf der linken Seite ein gemeinsames Attribut (oder mehrere
gemeinsame Attribute) mit der Tabelle oder dem Zwischenresultat auf der rechten
Seite des natural join Operators existieren muss. Für die vorliegende Abfrage
muss die Tabelle Fall daher zwischen Produkt und Kunde gelegen sein. Das Datenbanksystem generiert selbstständig eine Join-Bedingung zwischen Produkt und
Fall, sowie eine Join-Bedingung zwischen Fall und Kunde. Das Datenbanksystem
generiert aber nur Join-Bedingung zwischen unmittelbar benachbarten Tabellen,
es wird also nicht von links nach rechts gearbeitet und das jeweils erhaltene Zwischenresultat mit der nächsten Tabelle verbunden.
Achtung 2: Wird die from-Klausel zum Beispiel wie folgt formuliert:
from Fall f natural join Kunde k natural join Produkt p
so sucht das Datenbanksystem nach einer Join-Bedingungen zwischen Fall und
Kunde und einer Join-Bedingung zwischen Kunde und Produkt. Zwischen Fall und
Produkt ist die Bedingung f.idKunde = k.idKunde, zwischen Kunde und Produkt
ist die Bedingung k.name = p.name! Diese letzte Bedingung ist natürlich keineswegs die Absicht des Benutzers. Hätte das Datenbanksystem kein gemeinsames
Attribut gefunden, so hätte es eine Fehlermeldung erzeugt. Unglücklicherweise
gibt es aber ein gemeinsames Attribut, allerdings eines, das nur denselben Namen
hat in den zwei Tabellen, jedoch nicht dieselbe Bedeutung und damit nicht diesselben Werte (höchstens zufällig).
Obiges Verhalten ist nicht im Sinne des SQL-Standards, der eigentlich eine Auswertung der Join-Operationen von links nach rechts fordert oder die Möglichkeit
der Klammerung von Join-Ausdrücken gibt (Assoziativ-Gesetz).
Sybase ASA kennt neben dem Natural Join auch den den Key Join (Schlüsselwort
key join oder einfach join). Der Key Join wird von links nach rechts abgearbeitet. Die zuerst genannte Tabelle wird aufgrund der in den Tabellendefinitionen angegebenen Primärschlüssel-Fremdschlüssel-Beziehungen mit der an
zweiter Stelle genannten Tabelle verknüpft. Anschliessend wird das Resultat aus
dem Join der ersten zwei Tabellen mit der an dritter Stelle genannten Tabelle verJuni 2006
Arno Schmidhauser
30/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
knüpft.
Der Key-Join ist konzeptionell ein perfektes Mittel, um Tabellen logisch korrekt zu
verknüpfen.
select p.name, k.name, f.eingegangenAm, beschreibung
from Produkt p key join Fall f key join Kunde k
where f.status = 'eingegangen'
order by f.eingegangenAm
Mit einem Key Join lassen sich sehr einfach und sicher (d.h. ohne unerwartetes
Verhalten) beliebig viele Tabellen über ihre Primärschlüssel-FremdschlüsselBeziehungen verbinden.
14
Abfragen mit 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?
select m.name, f.eingegangenAm
from Mitarbeiter m natural left outer join Fall f
order by m.name
Achtung: key left outer join funktioniert nicht, da die Tabelle Fall keinen definierten Fremdschlüssel auf die Tabelle Mitarbeiter hat.
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.
select k.name, l.gueltigBis, l.lizenzKey
from Kunde k natural left outer join Lizenz l
order by k.name
Es funktioniert sowohl key left outer join wie auch natural left outer
join.
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)
Ja, die Abfragen sind identisch. Die untere Abfrage ist die Umsetzung eines Outer
Joins durch das Datenbanksystem.
Juni 2006
Arno Schmidhauser
31/54
Aufgabensammlung Datenbankkurs Lösungen
15
SWS
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).
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
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.
select distinct e2.person
from PersonenEigenschaft e1, PersonenEigenschaft e2
where e1.person = 'A'
and e1.eigenschaft = e2.eigenschaft
and e1.wert = e2.wert
and e2.person != 'A'
16
Views
F46
Zählen Sie Anwendungsmöglichkeiten von Views auf.
1. Bereitstellung "vorgekochter" Abfragen für Appikationen
2. Einschränkung der Sichtbarkeit von Attributen und Datensätzen gegenüber der
Appliktion
3. Generelle Zwischenschicht zwischen physischer Tabelle und Applikation, damit
die Tabelle erweitert werden kann, ohne dass die Applikation etwas davon
merkt.
Juni 2006
Arno Schmidhauser
32/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
4. Vor der Applikation verstecken, wie eine Abfrage realsiert ist. Die Abfrage nach
Daten kann modifiziert werden, ohne dass die Applikation geändert werden
muss.
5. Einschränkung von Benutzerrechten.
F47
Welchem Konstrukt in Programmiersprachen (z.B. Java) entspricht eine View,
wenn man die Tabelle mit einer Klasse vergleicht?
Eine View ist mit einem Interface vergleichbar.
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.
alter table Fall add
foreign key (idMitarbeiter)
references Mitarbeiter (idMitarbeiter)
on delete set null;
alter table Fall add
foreign key (idProdukt)
references Produkt (idProdukt)
on delete cascade;
alter table Fall add
foreign key (idKunde)
references Kunde (idKunde)
on delete cascade;
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 ... ).
alter table Mitarbeiter add
foreign key (idStellvertreter)
references Mitarbeiter (idMitarbeiter)
on delete set null;
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:
create table Workstep
(
idWorkstep uniqueidentifier default newid(),
Juni 2006
Arno Schmidhauser
33/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
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.
create trigger t1_adjustpositions after insert on Workstep referencing new as newRec
for each row
begin
update Workstep set position = position + 1
where position >= newRec.position
and idWorkstep != newRec.idWorkstep
end;
create trigger t2_adjustpositions after delete on Workstep
referencing old as oldRec
for each row
begin
update Workstep set position = position - 1
where position >= oldRec.position
end;
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;
create trigger t_Fall_u1 after update of status order 1
on Fall
referencing new as newFall
for each row
when ( newFall.status = 'abgeschlossen' )
Juni 2006
Arno Schmidhauser
34/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
begin
update Mitarbeiter
set supportZeitTotal =
ifnull(supportZeitTotal,0,supportZeitTotal)
+ newFall.zeitaufwand
where idMitarbeiter = newFall.idMitarbeiter;
end;
Die Lösung geht davon aus, dass die ermittelte Supportzeit aus der Tabelle Fall
einen korrekten, das heisst von null verschiedenen, Wert hat.
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
F53
keyName keyValue
idFall
1000
idProdukt
1000
idMitarbeiter
1000
idLizenz
1000
idKunde
1000
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.
Hilfstabelle KeyStore erzeugen:
create table KeyStore (
keyName
varchar(64)
not null,
keyValue
numeric(10,0) not null default 1,
primary key
(keyName)
);
Hilfstabelle KeyStore mit den Zählernamen und Anfangswerten füllen:
insert into KeyStore ( keyName, keyValue )
values ( 'idKunde',
1000 );
insert into KeyStore ( keyName, keyValue )
values ( 'idProdukt',
1000 );
insert into KeyStore ( keyName, keyValue )
values ( 'idMitarbeiter', 1000 );
Juni 2006
Arno Schmidhauser
35/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
insert into KeyStore ( keyName, keyValue )
values ( 'idFall',
1000 );
insert into KeyStore ( keyName, keyValue )
values ( 'idLizenz',
1000 );
Funktion getKey() erstellen:
create function getKey( keyNameP varchar(64) )
returns numeric(10,0)
not deterministic
begin
declare curValue numeric(10,0);
declare keyName_nicht_vorhanden
exception for sqlstate '99001';
-- erhöhe Zählerwert
update KeyStore
set keyValue = keyValue + 1
where keyName = keyNameP;
select keyValue into curValue
from KeyStore
where keyName = keyNameP;
if ( @@rowcount != 1 ) then
-- Exception ausgeben, da falscher Zählername
signal keyName_nicht_vorhanden;
end if;
return curValue;
-- exception bearbeiten
exception
when keyName_nicht_vorhanden then
message 'keyName ' || keyNameP || ' existiert nicht'
to client;
-- exception weitergeben
signal keyName_nicht_vorhanden;
end;
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'.
create procedure help ( in tabname varchar(32) )
begin
select t.table_name, t.table_type,
c.column_name, c.pkey,
d.domain_name 'base type',
case when y.type_name is null then ' '
else y.type_name end 'user type',
c.width, c.scale, c.nulls, c."default", c."check"
from sys.syscolumn c
join sys.systable t
join sys.sysdomain d
left outer join sys.sysusertype y
on ( c."user_type" = y."type_id" )
where t.creator in
( select user_id from sys.sysuserperm
Juni 2006
Arno Schmidhauser
36/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
where user_name = current user )
and t.table_type = 'BASE'
and t.table_name = tabname
order by t.table_name, c.column_name;
end
Eine ausführliche help-Prozedure finden Sie unter help.sql.
Juni 2006
Arno Schmidhauser
37/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
38/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Teil IV
Anbindung an Java mit JDBC
Juni 2006
Arno Schmidhauser
39/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
40/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
21 Übung Support-Servlet
F55
Supportfälle anzeigen, keine Musterlösung gemäss Source Code
F56
Supportfall übernehmen, Musterlösung gemäss Source Code
F57
Supportfall eingeben, Musterlösung gemäss Source Code
Juni 2006
Arno Schmidhauser
41/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
42/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Teil V
Transaktionen und Technologie
Juni 2006
Arno Schmidhauser
43/54
Aufgabensammlung Datenbankkurs Lösungen
Juni 2006
Arno Schmidhauser
SWS
44/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
22
Transaktionsmodell
F58
Warum müssen SQL-Lesebefehle (select) in einer Transaktion ablaufen, genauso
wie Änderungsbefehle (insert, update, delete)?
1. Daten dürfen nur gelesen werden, wenn andere Prozesse sie nicht gerade verändern.
2. Eine Applikation will ev. nicht, dass andere Prozesse die Daten nach dem Lesen
bereits wieder ändern können, weil sie selber später Änderungen durchführen will.
Die Transaktion definiert die Grenzen, innerhalb deren die obigen Bedingungen
eingehalten werden, durch das Datenbank-System.
F59
Was bedeutet autocommit?
Das Datenbanksystem, oder allenfalls die applikationsseitige Treibersoftware, ergänzt jeden SQL-Befehl mit einem Commit. Damit ergeben sich nur sehr kurze
Wartezeiten, wenn mehrere Prozesse diesselben Daten ändern wollen. Ist es jedoch aufgrund der Spezifikationen oder des Designs nicht möglich, zusammengehörende Änderungen in einem einzigen SQL-Befehl abzusetzen, ist das Arbeiten
mit autocommit falsch. Aus einem Systemfehler zwischen zwei zusammengehörenden SQL-Befehlen könnte dann ein inkonsistenter Datenbankzustand resultieren.
23
Serialisierbarkeit, Concurrency-Control
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.
Dieses Vorgehen ist nicht zu empfehlen:
Wenn zwei Prozesse gleichzeitig den grössten vorhandenen Wert suchen, wollen
auch beide Prozesse im Schritt 3 denselben Primärschlüssel einfügen. Das Datenbanksystem erkennt diesen Fehler, wenn das entsprechende Attribut als Primärschlüssel definiert ist und gibt eine Exception aus. Das gleichzeitige Suchen nach
dem Maximalwert durch zwei Prozesse wäre nur mit einem exklusiven Zugriff auf
die Tabelle zu verhindern. Dies ist aber wiederum sehr hinderlich für weitere Prozesse, die andere Informationen in der Tabelle suchen oder ändern wollen.
Eine zweite, ebenfalls unerwünschte Situation kann mit diesem Verfahren entstehen. Wenn der Primärschlüssel ausserhalb des Datenbanksystems in Gebrauch ist,
beispielsweise in einem HTML-Formular, ist folgende Situation denkbar: Der Primärschlüssel wird in der Datenbank durch einen anderen Prozess gelöscht und
durch einen dritten Prozess aufgrund des obigen Verfahrens wieder erzeugt und
weiterverwendet. Dann würde die erste Applikation, welche den Primärschlüssel
immer noch in Gebrauch hat, fälschlicherweise auf einen ganz anderen Datensatz
zugreifen.
Juni 2006
Arno Schmidhauser
45/54
Aufgabensammlung Datenbankkurs Lösungen
F61
SWS
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
Die Serialisierbarkeit ist garantiert. Wenn Transaktion 2 die Operation 2.1 durchgeführt hat, wird die Operation 1.2 blockiert bis zum Ende der Transaktion 2: 2.1
setzt eine Lesesperre, die das Aquirieren einer Schreibsperre durch 2.1 verunmöglicht.
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?
Mit einem einfachen S/X-Locking und dem 2-Phasen-Sperrprotokoll ist folgender
Ablauf denkbar:
Transaktion 1
Transaktion 2
select * from Person
insert into Person values (...)
commit
select * from Person
commit
Dieser Ablauf ist nicht serialisierbar, da die Abfrage von Transaktion 2 zweimal ein
unterschiedliches Resultat liefert. Das wäre nicht der Fall, wenn Transaktion 2 als
Ganzes vor oder nach Transaktion 1 ablaufen würde.
Um eine Serialisierung zu erreichen muss mit weiteren Mechanismen gearbeitet
werden, beispielsweise mit einer Sperre auf der ganzen Tabellen oder mit Range
Locking.
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?
Das 2-Phasen-Sperr-Protokoll ist notwendig, aber nicht ausreichend für die Serialisierbarkeit. Es muss mit Sperren auf Tabellenebene oder anderen Mechansimen
ergänzt werden.
F64
Jedes Datenbanksystem gibt X-Locks erst beim Transaktionsende frei. Warum?
Juni 2006
Arno Schmidhauser
46/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Ansonsten hat das Rollback einer zeitlich älteren Transaktion Auswirkungen auf
die Daten einer zeitlich jüngere Transaktion. Damit wäre die Isolations-Bedingung
zwischen Transaktionen verletzt.
F65
Welcher Isolationsgrad ist mit dem Versionenverfahren von Oracle realisiert realisiert (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE)?
Wenn eine Transaktion gelesene Daten nicht verändert, ist das Versionenverfahren mit dem Modus REPEATABLE READ kompatibel. Werden die gelesenen Daten
zurückgeschrieben, ist die Parallelität vergleichbar mit READ COMMITTED.
F66
Stellen Sie sich vor, es gäbe einen Isolationsgrad DIRTY WRITE. Was wären die
Konsequenzen für das Transaktionsverhalten des Datenbanksystems?
DIRTY WRITE würde bedeuten, dass jede Transaktion Datensätze nach Belieben
ändern kann und bereits bestätigte Transaktionen eventuell ihre Änderungen verlieren. Beispiel:
Zeitpunkt
Transaktion 1
1
update Person
set adresse = 'A1'
where idPerson = 1
Transaktion 2
2
update Person
set adresse = 'A2'
where idPerson = 1
3
commit
4
rollback
Was soll beim Rollback zum Zeitpunkt 4 mit den Änderungen von Transaktion 2
geschehen? Ein allgemeines Vorgehen ist nicht möglich.
DIRTY WRITE ist äquivalent zum Begriff 'Non Transactional', den man in Büchern
über Applikationsserver etwa antrifft.
Die Fragen F66 und F64 sind identisch, wenn man DIRTY WRITE und frühzeitige
Freigabe von Schreibsperren als gleichbedeutend ansieht.
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?
SERIALIZABLE ist eine Anweisung für die Datenbank, keine Abläufe zuzulassen,
welche nicht serialisierbar sind. Die Datenbank kann jedoch nicht über den gerade
anstehende SQL-Befehl hinaus planen. Deshalb ist es möglich, dass sie in eine
Sackgasse gerät. Das heisst, sie erkennt, dass kein serialisierbarer Ablauf mehr
möglich ist. Dies ist dann der Fall, wenn ein Deadlock auftritt. Mögliche Deadlocks
sind der Preis für die Parallelität mehrerer Transaktionen.
Der hypothetische Modus SERIALIZE würde eine effektive Serialisierung und nicht
nur eine konzeptionelle bedeuten. Es könnte effektiv nur eine Transaktion gleichzeitig ablaufen. Das Gesamtverhalten wäre damit natürlich deadlock-frei, andererseits ist aber keine parallele Bedienung mehrerer Transaktionen möglich.
Juni 2006
Arno Schmidhauser
47/54
Aufgabensammlung Datenbankkurs Lösungen
F68
SWS
Was ist ein Range Lock?
In den klassischen Lock-Verfahren werden nur vorhandene Datensätze gesperrt.
Damit kann das sogenannte Phantom-Problem auftreten, welches eine Verletzung
des Serialierbarkeits-Prinzips darstellt: Liest eine Transaktion mit einer selectAbfrage eine bestimmte Anzahl Datensätze ein, so sind diese Datensätze mit einer
Lesesperre belegt und damit vor Änderungen durch andere Transaktionen geschützt. Eine andere Transaktion kann jedoch neue Datensätze einfügen, welche
die Abfragebedingung des select-Befehles der ersten Transaktion erfüllen. Die erste Transaktion würde beim Nochmaligen Durchführen ihrer Abfrage diese neuen
Datensätze sehen. Die neue Datensätze sind für die erste Transaktion sogenannte
Phantome.
Ein Range-Lock sperrt nicht nur vorhandene Datensätze, sondern einen ganzen
Bereich von Werten, die in der Abfragebedingung eines SQL-Befehles vorkommen.
Im Gegensatz zu einer vollständigen Tabellensperre, lässt der Range-Lock Daten
ausserhalb des kritischen Bereichs ungesperrt.
Mit Range-Locks wird die Serialisierbarkeit garantiert. Erforderlich ist ein Index auf
einem Attribut, das den zu sperrenden Bereich definiert.
24
Concurrency Control in SQL
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.
Bei Isolationsgrad 0 ist die Änderung aus Session 1 in Session 2 sichtbar. Bei den
anderen Isolationsgraden wird in Session 2 auf die Beendigung der Transaktion
von Session 1 gewartet, damit nur bestätigte Daten aus der Mitarbeitertabelle gelesen werden.
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
48/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Achtung: Schliessen Sie nach Beendigung der Aufgabe die Session 1 mit commit
ab.
Ab Isolationsgrad 2 wird Session 2 behindert, weil für das Lesen eine dauerhafte
(bis zum commit dauernde) Lesesperre auf die gelesenen Daten gesetzt wird.
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?
a) Isolationsgrad 1 ergibt einen behinderungsfreien Ablauf, allerdings tritt ein
Lost-Update auf: der erste Update wird durch den zweiten überschrieben.
b) Isolationsgrad 2 verhindert den Lost-Update, indem jede Transaktion eine Lesesperre auf dem Datensatz besitzt. Diese verhindert, dass die jeweils andere
Transaktion eine Update durchführen kann, für den sie eine Schreibsperre benötigen würde. Isolationsgrad 2 führt in obigem Ablauf allerdings zu einem Deadlock.
Die etwas spätere Transaktion wird zurückgesetzt. Damit entstehen zwar keine inkonsistenten Daten, es ist aber eine lästige Situation, indem die zweite Transaktion nochmals durchgeführt werden muss.
c) Isolationsgrad 3 löst das Problem nicht, der Deadlock tritt totzdem auf.
d) Der Deadlock ist beispielsweise verhinderbar durch den Befehl lock table
Mitarbeiter in exclusive mode. Dies führt allerdings zu massiven Behinderungen dritter Transaktionen, welche auf Datensätze zugreifen möchten, die vom Update gar nicht betroffen wären. Eine weitere Möglichkeit besteht darin, vor dem
Lesen einen dummy-Update durchzuführen, der lediglich den Datensatz sperrt,
beispielsweise: udpate Mitarbeiter set idMitarbeiter = idMitarbeiter
where idMitarbeiter = 3. Anschliessend kann die Abfrage und anschliessend
der eigentliche Update durchgeführt werden.
Juni 2006
Arno Schmidhauser
49/54
Aufgabensammlung Datenbankkurs Lösungen
F72
SWS
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?
Isolationsgrad 3 wird benötigt. Es muss verhindert werden, dass zwischen der ersten und der zweiten Abfrage jemand die Tabelle so ändert, dass neue Datensätze
hinzukommen oder gelöscht werden, welche die Abfragebedingung erfüllen. Die
Berechnung des Durschnittswertes würde nicht mehr stimmen bezüglich der ersten Ausgabe.
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.
1. Schritt: Suchen der offenen Fälle:
set temporary option isolation_level = 1;
select * from V_FallEingang;
2. Schritt: Darstellen der offenen Fälle auf dem GUI (z.B. HMTL-Seite)
commit;
3. Schritt: Der Mitarbeiter sichtet nun die Fälle und liest einen aus, d.h. es wird
eine Fall-ID und eine Mitarbeiter-ID an das Programm übergeben, welches die Übernahme durchführt.
4. Schritt: Das Übernahme-Programm muss verifizieren, ob der Fall zwischenzeitlich nicht von einem anderen Mitarbeiter übernommen wurde:
set temporary option isolation_level = 2;
select count(*)
from Fall
where idFall = idFallParam
and status = 'eingegangen';
5. Schritt: Falls count(*) > 0, dann kann folgender Update durchgeführt werden:
update Fall
set status = 'uebernommen',
Juni 2006
Arno Schmidhauser
50/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
idMitarbeiter = idMitarbeiterParam,
where idFall = idFallParam;
Anstelle des Isolation Levels könnte auch mit lock table Fall in exclusive
mode gearbeitet werden. Die Behinderung anderer Transaktionen, welche lediglich
Daten lesen wollen, ist dann aber sehr gross.
Anstelle der Prüfabfrage in Schritt 4 könnte auch folgender Update-Befehl verwendet werden.
update Fall
set status = 'uebernommen',
idMitarbeiter = idMitarbeiterParam,
where idFall = idFallParam
and status = 'eingegangen';
Die Applikation bekommt die Information (zB. über den Returnwert der executeUpate()-Funktion in JDBC), wieviele Datensätze vom Update-Befehl betroffen wurden. Wenn kein Datensatz betroffen wurde, ist der Fall bereits von einem anderen
Mitarbeiter übernommen, andernfalls ist die Übernahme gelungen.
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?
Für den Fall, dass zwei Transaktionen die Schritte 4 und 5 durchführen, entsteht
ein Deadlock. Das Datenbanksystem setzt dann eine der Transaktionen zurück.
Die andere kann korrekt ablaufen. Soll kein Deadlock möglich sein, muss mit lock
table Fall in exclusive mode gearbeitet werden.
25
Lange Transaktionen
F75
Nennen Sie Anwendungsbeispiele wo das Arbeiten mit dem Checkout/CheckinVerfahren angebracht ist.
{ Planungssysteme
{ Dokumentenverwaltung
{ Source-Code Verwaltungssysteme
{ Überall, wo zum vornherein klar ist, dass die ausgecheckten Daten verändert
werden, und es keinen Sinn macht, dass ein anderer Benutzer ebenfalls die
Daten auscheckt zum ändern.
F76
Nennen Sie Anwendungsbeispiele wo das Arbeiten mit dem Zeitstempel/PrüfregelVerfahren angebracht ist.
{ Reservationssysteme
{ Abgleich von replizierten Daten, die nach einer gewissen Zeit wieder zusammengeführt werden müssen.
{ Überall, wo zum vornherein nicht klar ist, ob und wieviele der gelesenen Daten
auch verändert werden. Das Zeitstempelverfahren ist insbesondere dann
günstig, wenn viele Daten gelesen, aber nur wenige davon auch modifiziert
werden.
Juni 2006
Arno Schmidhauser
51/54
Aufgabensammlung Datenbankkurs Lösungen
26
Recovery
F77
Wodurch unterscheidet sich ein Betriebssystem-Backup von einem On LineBackup eines Datenbanksystems?
SWS
Ein On Line Backup sichert immer einen konsistenten Zustand der Datenbank. Ein
Betriebssystem-Backup sichert einen mehr oder weniger zufälligen Zustand der
Datenbank-Dateien. Der Betriebssystem-Backup könnte beispielsweise das LogFile vor den eingentlichen Datenbank-Files sichern. Wenn nun zwischen dem Sichern des Log-Files und dem Sichern der Datenbank-Files die Datenbank im Rahmen einer Transaktion geändert wird und während der Änderung abstürzt, bleiben
inkorrekte Datenbank-Files zurück. Der Backup-Prozess sichert jedoch diese inkorrekte Datenbank. Optimierung
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´
Der Zugriff auf die erste Person im Index, deren Namen mit S beginnt, benötigt 4
Zugriffe auf Index-Knoten ( 10Log( 10000/10 ) + 1 ).
Das Durchlaufen der Index-Blattknoten benötigt 40 Seitenzugriffe. Jeder Blattknoten enthält 10 Werte. Bei 400 Namen, die mit S beginnen, sind das 40 Knoten (Allenfalls muss ein weiterer Knoten gelesen werden, um das Ende der gesuchten
Daten festzustellen, wenn der letzte "S" gerade auf ein Knotenende fällt. Da es
sich um eine Schätzung handelt, soll dieser Fall aber unberücksichtigt bleiben).
Für jeden gefunden Wert im Index muss auf die Seite mit den eigentlichen Personendaten zugegriffen werden. Da jede Person genau 1 Seite Platz benötigt, sind
das 400 Seiten.
Die Bedingung vorname != 'Peter' kann beim Lesen der Personendaten ausgewertet werden. Sie benötigt also keine weiteren Seitenzugriffe.
Juni 2006
Arno Schmidhauser
52/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
Total ergeben sich also 444 Seitenzugriffe. Das entspricht nur gerade 4.5% Aufwand gegenüber der sequentiellen Suche.
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 );
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´
Die Berechnung nach derselben Idee wie Aufgabe 27 ergibt 4 + 40 + 400 = 444
Seitenzugriffe. Die Anzahl Zugriffe auf die Seiten mit Personendaten ändert sich
nicht, da ausgehend von jedem Indexeintrag (Blattknoten) die zugehörige Datenseite pro Indexeintrag einmal aufgesucht werden muss. Aufeinanderfolgende Einträge im Index entsprechen keinesfalls aufeinanderfolgenden Seiten in der Personentabelle.
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´
Die Berechnung nach derselben Idee wie Aufgabe 27 ergäbe 4 + 120 + 1200 =
1324 Seitenzugriffe. Diese Anzahl Seitenzugriffe entspricht jedoch mehr als der
Grösse der Personentabelle in Seiten gemessen. Das Datenbanksystem wird sich
daher entschliessen, eine sequentielle Bearbeitung der Personentabelle ohne Verwendung des Index vorzunehmen! Der Aufwand beträgt also 1000 Seitenzugriffe.
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( 4,0 ),
Juni 2006
Arno Schmidhauser
53/54
Aufgabensammlung Datenbankkurs Lösungen
SWS
strasse
varchar( 100 ),
ort
varchar( 100 ),
kommentar varchar( 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%'
1. Berechungsart mit Lookup-Join ausgehend von der Person: Für das Auffinden
der Personen mit 'S'-Namen sind (gemäss Aufgabe 27) 444 Zugriffe notwendig.
Für jede Person muss in der Adress-Tabelle durchschnittlich ein Eintrag gesucht
werden. Dafür werden 4 + 1 Zugriffe (4 im Index, 1 auf die Adresse-Tabelle) pro
Person benötigt. Es sind ca. 400 Personen mit 'S' vorhanden. Das ergibt total
2000 weitere Zugriffe. also gesamthaft 2444.
F85
b) Wie könnte folgende Abfrage abgearbeitet werden und wieviele Seitenzugriffe
werden dafür benötigt?
F86
select *
from Person join Adresse
Ein Lookup-Join hat folgenden Aufwand: Das Aufsuchen aller Personen benötigt
10000 Zugriffe. Ein Index wird nicht verwendet, da ohnenhin alle Personen durchlaufen werden müssen. Der Zugriff auf die Adresse erfordert 4 + 1 Zugriffe pro
Person, also sind gesamthaft 10'000 + 10'000 * 5 = 60'000 Zugriffe notwendig.
2. Berechnungsart mit Sort Merge-Algorithmus: Die Personen-ID's liegen für die
Personen-Tabelle und die Adressen-Tabelle bereits sortiert vor in den entsprechenden Indices. Das Durchlaufen des Index erfordert je 4 + 1000 Seitenzugriffe
(Auffinden des ersten Blattknotens + Durchlaufen aller Blattknoten mit jeweils 10
Schlüsseln pro Knoten). Der Merge-Algorithmus erfodert gesamthaft also 2008
Zugriffe. Für jeden Match bei Mischen der Indices muss natürlich noch auf die jeweilige Person und die jeweilige Adresse zugegriffen werden, was 20'000 Zugriffe
erfordert. Gesamthaft sind für die Abarbeitung der Abfrage also 22'008 Zugriffe
erforderlich, also ca 3 mal weniger als beim Lookup-Join.
Juni 2006
Arno Schmidhauser
54/54