1. Architektur von Datenbanksystemen: Übung 1

1. Architektur von Datenbanksystemen: Übung 1
Transaktionskonzept
§ Alle Veränderungen in einer Datenbank werden
transaktionsorientiert durchgeführt
§ Eine Transaktion ist eine inhaltlich zusammenhängende
Menge von Datenbankänderungen, die entweder ganz
oder gar nicht durchgeführt werden.
§ Eine Transaktion überführt eine Datenbank von einem
konsistenten Zustand in einen anderen konsistenten
Zustand
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 1
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Regeln für Transaktionen:
§ atomar: werden ganz oder gar nicht ausgeführt
Atomicity
§ konsistenzerhaltend: Integritätsregeln werden
beachtet
Consistency
§ isoliert: Transaktionen laufen voneinander
getrennt ab
Isolation
Durability
§ dauerhaft, das Ergebnis erfolgreicher Transaktionen wird in der DB gespeichert
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 2
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Lösung ACID-Prinzip Isolation:
§ Alle Transaktionen sequentiell hintereinander ausführen
Problem
§ Ineffizienz
§ Unzumutbare Antwortzeiten
Alternative Lösung
§ Paralleles Vorantreiben der Transaktionen
§ Zeitlich verschränkte Ausführung der einzelnen Schritte der
verschiedenen Transaktionen
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 3
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Verzahnen von Transaktionsschritten:
§ wird Scheduling genannt
§ erfolgt durch das DBMS
§ Ist ein nicht triviales Problem
§ erfolgt nach genauen Regeln (Protokolle)
§ Führt bei unkontrolliertem Vorgehen zu Inkonsistenzen /
Anomalien
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 4
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Anomalien: Vorbemerkungen
§ x , y, z bezeichnen Datenbankobjekte
§ r1 (x) bedeutet: in einer Transaktion T1 wird x gelesen
§ w1 (x) bedeutet: in einer Transaktion T1 wird x geschrieben
§ c1 bedeutet: Transaktion T1 wird korrekt beendet
§ a1 bedeutet: Transaktion T1 wird abgebrochen und
zurückgesetzt
§ { x=x-20 } bedeutet: Im Client wird das Datenbankobjekt x
verändert (hier sein Wert um 20 vermindert)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 5
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Anomalien: Lost Update
§ T1 : r1(x) , {x = x + 10} , w1 (x) c1
§ T2 : r2(x) , {x = x - 10} , w2 (x) c2
Ausgangswert x=50 -> nach T1 und T2 : x=50
Verzahnung
r1(x)
{x = x + 10}
r2(x)
w1 (x)
c1
{x = x - 10}
w2 (x) c2
Zeitachse t
x=40!
Änderung von T1 auf x verloren !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 6
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Anomalien: Dirty Read
§ T1 : r1(x) , {x = x + 10} , w1 (x) a1
§ T2 : r2(x) , {x = x - 10} , w2 (x) c2
Ausgangswert x=50 -> nach T1 und T2 : x=40
Verzahnung
r1(x) {x = x + 10} w1 (x)
a1
r2(x) {x = x - 10}
w2 (x) c2
Zeitachse t
x=50 ! T2 liest Wert von x, der anschließend wieder zurückgesetzt
wird !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 7
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Anomalien: Unrepeatable Read
§ x=40, y=50, z=60 seien Kontostände
§ Die Summe der Kontostände ist 150 €
§ Transaktion T1 bucht vom Konto z 10 € auf das Konto x um
§ Diese Transaktion verändert die Summe der Kontostände nicht
§ Transaktion T2 berechnet „parallel“ die Summe der Kontostände
x,y,z
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 8
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Anomalien: Unrepeatable Read
§ T1 : r1(z) , {z = z - 10} , w1 (z), r1(x) , {x = x + 10} , w1 (x) c1
§ T2 : {sum = 0}, r2(x) , r2(y) , {sum = sum+x+y}, r2(z) ,
{sum = sum+z}, c2
Verzahnung
{sum = 0} r2(x) r2(y) {sum = sum+x+y}
... r1(x) {x = x + 10} w1 (x)
c1
r1(z) {z = z - 10} w1 (z) ....
r2(z) {sum = sum+z} c2
Zeitachse t
Bitte Ergebnis von T2 prüfen !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 9
1. Architektur von Datenbanksystemen: Übung 1
[..] Transaktionskonzept
Verzahnen von Transaktionsschritten, Fazit:
§ „Korrekte“ Synchronisation absolut notwendig, um fehlerhafte
Verarbeitung der Daten zu vermeiden
§ Aufgabe wird vom „Scheduler“ eines DBMS zuverlässig gelöst.
§ Nähere Behandlung des Problems in späteren Vorlesungen
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 10
1. Architektur von Datenbanksystemen: Übung 1a
Zeitstempelverfahren
§ Jeder Transaktion T wird beim Start ein Zeitstempel Z(T)
zugewiesen
§ Für eine Transaktion T1 die vor einer Transaktion T2
gestartet wurde gilt: Z(T1) < Z(T2)
§ Jedem Datenbankobjekt X werden zwei Zeitstempel
zugewiesen:
§ Lesestempel ZR(X): der Zeitstempel der letzten
Transaktion, die dieses Objekt gelesen hat
§ Schreibstempel ZW(X): der Zeitstempel der letzten
Transaktion, die dieses Objekt geschrieben hat
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 11
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Das Zeitstempelverfahren garantiert, das zwei Transaktionen
T1 und T2, die einen Konflikt enthalten (eine der beiden
Transaktionen schreibt wenigstens ein Objekt, das auch in der
anderen Transaktion gelesen oder geschrieben wird),
garantiert nacheinander ausgeführt werden.
Regeln:
T1 liest X:
Abbruch von T1, wenn für den Schreibstempel von X gilt
ZW(X) > Z(T1) (eine Transaktion, die nach T1 gestartet
wurde, hat X geschrieben)
Sonst: T1 liest X und setzt ZR(X) = Z(T1).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 12
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Regeln:
T1 schreibt X:
Abbruch von T1, wenn für den Schreibstempel oder den
Lesestempel von X gilt
ZW(X) > Z(T1)
oder
ZR(X) > Z(T1)
(eine Transaktion, die nach T1 gestartet wurde, hat X gelesen
oder geschrieben)
Sonst: T1 schreibt X und setzt ZW(X) = Z(T1)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 13
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Prüfen Sie anhand des read-write-Modells, welche Anomalien
durch das Zeitstempelverfahren verhindert werden.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 14
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Lost Update:
Zeitstempel Z: Z(T1) < Z(T2)
ZR(x): leer
r1(x)
r2(x)
w2(x)
w1(x)
Z(T1)
Z(T2)
Z(T2)
Abbruch
ZW(x): leer
t
Z(T2)
w1(x) nicht möglich da ZR(x) und ZW(x) = Z(T2) und
Z(T2) > Z(T1) !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 15
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Lost Update:
Zeitstempel Z: Z(T1) > Z(T2)
ZR(x): leer
r1(x)
r2(x)
w2(x)
w1(x)
Z(T1)
Z(T2)
Abbruch
t
ZW(x): leer
w2(x) nicht möglich da ZR(x) = Z(T1) > Z(T2) !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 16
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Dirty Read:
Zeitstempel Z: Z(T1) < Z(T2)
ZR(x): leer
ZW(x): leer
r1(x)
w1(x)
r2(x)
a1
Z(T1)
Z(T1)
Z(T2)
Abbruch
Z(T1)
Z(T1)
t
r2(x) möglich da ZR(x) = Z(T1) < Z(T2) !
Dirty Read wird nicht verhindert !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 17
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Dirty Read:
Zeitstempel Z: Z(T1) > Z(T2)
ZR(x): leer
ZW(x): leer
r1(x)
w1(x)
r2(x)
a1
Z(T1)
Z(T1)
Abbruch
t
Z(T1)
r2(x) nicht möglich da ZR(x) und ZW(x) = Z(T1) > Z(T2) !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 18
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Unrepeatable Read:
Zeitstempel Z: Z(T1) < Z(T2)
ZR(x): leer
ZW(x): leer
r1(x)
r2(x)
w2(x)
Z(T1)
Z(T2)
Z(T2)
r1(x)
t
Abbruch
Z(T2)
2. r1(x) nicht möglich da ZW(x) = Z(T2) > Z(T1) !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 19
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Unrepeatable Read:
Zeitstempel Z: Z(T1) > Z(T2)
ZR(x): leer
r1(x)
r2(x)
w2(x)
r1(x)
Z(T1)
Z(T2)
Abbruch
t
ZW(x): leer
w2(x) nicht möglich da ZR(x) = Z(T2) > Z(T1) !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 20
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Unrepeatable Read, Übungsbeispiel
Zeitstempel Z: Z(T1) < Z(T2)
r1(x)
ZR(z):
ZW(z):
r1(y)
r2(z)
w2 (z)
Z(T2)
r2(x)
w2 (x)
c2
r1(z) c1
t
Abbruch
Z(T2)
r1(z) nicht möglich, da ZW(z) = Z(T2) > Z(T1) (commit löscht
nicht !)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 21
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Unrepeatable Read, Übungsbeispiel
Zeitstempel Z: Z(T1) > Z(T2)
r1(x)
ZR(x): Z(T1)
r1(y)
r2(z)
w2 (z)
r2(x)
w2 (x)
c2
r1(z) c2
t
Abbruch
ZW(x):
w2(x) nicht möglich, da ZR(x) = Z(T1) > Z(T2) (commit löscht
nicht !)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 22
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Phantomproblem
Zeitstempel Z: Z(T1) < Z(T2)
T1 lies alle
Konten X
ZR(x): leer
ZW(x): leer
T2 fügt
Konto Y ein
Z(T1)
T1 lies alle
Konten X (mit Y)
Abbruch
t
Z(T2)
Abbruch, da ZW(Y)=Z(T2) > Z(T1)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 23
1. Architektur von Datenbanksystemen: Übung 1a
[...] Zeitstempelverfahren
Phantomproblem
Zeitstempel Z: Z(T1) > Z(T2)
T1 lies alle
Konten X
ZR(x): leer
ZW(x): leer
T2 fügt
Konto Y ein
Z(T1)
T1 lies alle
Konten X (mit Y)
Z(T1)
t
Z(T2)
r1(Y) möglich, da ZW(Y)=Z(T2) < Z(T1)
Phantomproblem wird nicht verhindert !
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 24
1. Architektur von Datenbanksystemen: Übung 1b
Isolationslevel
Isolationslevel beeinflussen den Grad der Interaktion zwischen
Transaktion
A C
I
D
SET TRANSACTION ISOLATION LEVEL
{ READ COMMITTED
READ UNCOMMITTED
REPEATABLE READ
| SERIALIZABLE
}
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 25
1. Architektur von Datenbanksystemen: Übung 1b
[...] Isolationslevel
READ COMMITTED
Nur bestätigte Daten werden gelesen. Verhindert Dirty Read
REPEATABLE READ
Gesperrte Daten können nicht geändert werden. Garantiert auch Repeatable
Read
SERIALIZABLE
Keine Einfügungen in gesperrte Daten möglich. Verhindert auch
Phantomproblem.
READ UNCOMMITTED
Keine Synchronisation
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 26
2. Das relationale Modell: Übung 2
Abbildung auf das relationale Modell
Umweltbeschreibung: Hotel
In einem Hotel gibt es die Objektklassen: Zimmer, Gast, Reservierung und Zimmermädchen.
Zimmer haben eine eindeutige numerische Zimmernummer, eine alphanumerische
Klassifikation, eine Bettenzahl und ein Reinigungsdatum, das mehrere Werte annehmen kann.
Ein Gast hat ebenfalls eine eindeutige Nummer, einen Namen, eine Adresse, die aus der
Strasse, Hausnummer, Postleitzahl und dem Ort besteht, Informationen über Kommunikationsverbindungen. Jede Kommunikationsverbindung trägt die Information der Art (Mail,
Handy, Festnetz etc.) und den Wert (0231..)
Zimmermädchen haben eine eindeutige Nummer. einen Namen, ein Geburtsdatum, eine
Information über das Geschlecht und das Einstellungsdatum.
Eine Reservierung hat eine eindeutige Nummer, ein Anreisedatum, ein Abreisedatum, ein
Bearbeiterkennzeichen (alphanumerisch) und ein Kennzeichen über die Bestätigung.
Es gibt folgende Beziehungen zwischen den Objektklassen:
Hat_belegt: zwischen Gast und Zimmer
Hat_reserviert_fuer : zwischen Reservierung, Gast und Zimmer
Zustaendig_fuer: zwischen Zimmer und Zimmermädchen
Aufgabe: Diese Umweltbeschreibung in Relationen übertragen!
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 27
3. Relationale Musterdatenbank: Übung / Praktikum 3
§ Anlegen einer Datenbank in Oracle (Enterprise-Manager)
§ SQL+:Ausführen von SQL-Anweisungen / Skripten
§ Implementieren der Strukturen der Musterdatenbank durch das
betreffende Skript.
§ Laden der Daten für die Musterdatenbank durch das Skript.
§ Löschen der Musterdatenbank und Wiederholung des Anlegens.
§ Anlegen von Tabellen im Enterprise-Manager
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 28
4. SQL-Funktionen: Übung / Praktikum 4
Berechnen Sie aus der Musterdatenbank folgende Ausdrücke bzw.
Funktionen. Führen Sie die Ausdrücke zum Testen immer mit
folgender einfachen Syntax aus:
select <ausdruck>
from <bezogene Tabelle>;
Beispiel: Berechnen Sie die Differenz aus Jahresumsatz und
Vorjahresumsatz aus der Tabelle artikel.
select jahresumsatz-vorjahresumsatz
from artikel;
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 29
[..] 4. SQL-Funktionen: Übung / Praktikum 4
1) Runden Sie das Attribut Jahresumsatz der Kunden auf HunderterStellen genau (Über die Syntax der round-Funktion und der
Bedeutung der anderen Funktionen lesen Sie bitte in der OnlineHilfe nach. Machen Sie sich mit dieser vertraut).
2) Ersetzen Sie in der Branchenbezeichnung (branche) der Tabelle
Kunden alle Vorkommnisse der Zeichenkette ‚Elektro’ in
‚Elektrogroß’ (Funktion replace). Machen Sie diese Ersetzung
unabhängig von Groß- oder Kleinschreibung.
3) Bilden Sie eine zusammenhängende Zeichenkette (durch das
Zeichen ‚/’ getrennt) aus den Attributen Datum_Kreditauskunft
und Kreditlimit der Tabelle Kunden (Funktion cast)
4) Geben Sie den Wert des Attributes Land in der Tabelle Kunden als
Langtext aus (machen Sie das für die Werte ’D’, ’CH’, ’NL’)
(Funktion case).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 30
[..] 4. SQL-Funktionen: Übung / Praktikum 4
5) Berechnen Sie aus der Tabelle Rechnungen die Differenz aus dem
Rechnungsdatum und dem Fälligkeitsdatum.
6) Berechnen Sie aus der Tabelle Rechnungen die Anzahl der Wochen
(ganzzahlig), die seit der Rechnungsstellung vergangen ist (Tipp:
berechnen Sie zunächst die Tage).
7) Ermitteln Sie mittels character-Funktionen aus der Tabelle
Rechnungen jeweils den Monat, an dem die Rechnung erstellt
wurde. Geben Sie den Monatsnamen aus (Funktionen: substring,
cast, case).
8) Geben Sie die Artikelbezeichnung aus. Beachten Sie dabei, dass
‚Laserdrucker‘ durch ‚Farblaserdrucker‘ und ‚Scanner‘ durch
‚Multifunktionsscanner‘ ersetzt werden soll.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 31
[..] 4. SQL-Funktionen: Übung / Praktikum 4
9) Führen Sie die Aufgabe 4.2) so aus, dass der Anfangsbuchstabe
groß ausgegeben wird und der Rest klein. Benutzen Sie hierfür
einmal die Funktion ‚substr‘ und suchen Sie außerdem in den
Oracle-Docs eine Funktion, die dieses für Sie übernimmt.
10)Geben Sie für jeden Artikel die Bezeichnung und die
Lieferantennummer aus. Ermitteln Sie den Namen des Lieferanten
und ersetzen die Lieferantennummer durch den Namen (Keine
subselects oder joins! Nicht dynamisch).
11)Geben Sie für jede Auftragsposition die Artikelnummer, den Preis
und das Datum aus. Die Ausgabe soll in einer Spalte erfolgen, die
einzelnen Einträge sollen durch ein Leerzeichen getrennt werden.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 32
[..] 4. SQL-Funktionen: Übung / Praktikum 4
12)Überprüfen Sie, ob man den Kunden für die noch offenen
Rechnungen Kredite geben könnte. Geben Sie dazu die Differenz
zwischen dem Kreditlimit und den offenen Rechnungen aus.
13)Geben Sie den Namen und die Adresse der Kunden aus (Straße,
Ort, PLZ). Die Ausgabe soll so erfolgen, dass keine Abkürzung für
Straße ausgeben wird, sondern das ganze Wort.
14)Geben Sie für jeden Artikel den vorhandenen Verkaufswert aus.
Runden Sie diesen Wert auf den Tausender.
15)Geben Sie die Artikelnummer und die Bezeichnung (getrennt
durch einen Bindestrich, in Großbuchstaben), die Warengruppe
(Ersetzen Sie die Zahl durch eine Bezeichnung) und die Differenz
zwischen Einkaufspreis und Verkaufspreis (gerundet auf EinerStellen).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 33
5. SQL – create table / index: Übung / Praktikum 5
1) Legen Sie eine neue Datenbank ‚Hochschule’ in Form von Tabellen
an (kein create database!).
Schreiben Sie zu folgenden Strukturen aus dem Hochschul-Bereich
create-table-Deklarationen. Entscheiden Sie, welche Datentypen zu
den Attributen passen.
Implementieren Sie die genannten Constraints NICHT!
Nach Lösen der Aufgaben löschen Sie die Tabellen bitte wieder.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 34
[..] 5. SQL – create table / index: Übung / Praktikum 5
Student
Matrikelnr Name Fachbereich Fachsemester Geb_Dat Geschlecht
Schlüssel: Matrikelnr, Geschlecht: w / m, Fachbereich: 1-9
Dozent
Name Fachbereich Lehrgebiet
Schlüssel: Name
Vorlesung
Vorl_Nr
Bezeichnung
Hoersaal
Plätze
Beginn_h
Ende_h
Dozent
Schlüssel: Vorl_Nr, Plätze: 20-500, Fremdschlüssel: Dozent
auf Tabelle Dozent, Feld Name
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 35
[..] 5. SQL – create table / index: Übung / Praktikum 5
hoert
Student
Vorlesung
Schlüssel: Student und Vorlesung, Fremdschlüssel:
Student auf Tabelle Student, Feld Matrikelnr, Vorlesung auf
Tabelle Vorlesung Feld Vorl_Nr
Klausurergebnis
Student
Fach
Klausur_Datum
Ergebnis
Schlüssel: Student und Fach und Klausur_Datum,
Fremdschlüssel: Student auf Tabelle Student, Fach
Matrikel_Nr
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 36
[..] 5. SQL – create table / index: Übung / Praktikum 5
2) Legen Sie gemäß den Empfehlungen aus der Vorlesung
Indexstrukturen über die neue Hochschuldatenbank an (select *
from USER_IND_COLUMNS zeigt bestehende Indizes an).
3) Erzeugen Sie einen Index, der eine sortierte Abfrage der Tabelle
Auftragspositionen nach Artikelnummern aufsteigenden und
innerhalb der Artikelnummer nach Auftragsnummer absteigend
zulässt.
4) Überlegen Sie sich eine Tabellenstruktur für eine Autovermietung
und realisieren Sie diese durch create-table-Deklarationen. Die
Tabellenstruktur soll die Tabellen Fahrzeug, Kunde, mietet,
Fahrzeugmodell und Autovermietung enthalten. Überlegen Sie sich
für jede Tabelle etwa 5 Attribute mit sinnvollen Datentypen.
Beachten Sie, dass es einen möglichen Kandidaten für einen
Primärschlüssel geben sollte (realisieren Sie die Constraints aber
nicht!).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 37
[..] 5. SQL – alter table: Übung / Praktikum 5
Führen Sie folgende Änderungen in der Musterdatenbank durch
(Hinweis: “select * from user_tab_columns;” zeigt die Spalten aller
Tabellen an).
5) Löschen Sie das Attribut Datum_Mahnung_2 in der Tabelle
Rechnungen.
6) Übernehmen Sie in der Tabelle Auftraege zusätzlich den Namen
und die Adresse des Kunden (Typen wie in Tabelle Kunden, ohne
NOT NULL) in die Auftragsstruktur (redundant).
7) Erstellen Sie eine Tabelle Kunden_details, die die Attribute
Kundennummer, Kreditlimit und Jahresumsatz der Tabelle Kunden
enthält. Übernehmen Sie die Daten der Tabelle Kunden direkt mit.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 38
[..] 5. SQL – alter table: Übung / Praktikum 5
8) Verändern Sie die Tabelle Sachbearbeiter wie folgt: Fügen Sie ein
Datenfeld für eine Emailadresse hinzu und ändern Sie die
Telefonnummer so, dass sie 30-stellig sein kann (eine
Anweisung!).
9) Fügen Sie im Kundenstamm das Datenfeld „Summe Auftragswert“
ein, es soll €-Werte aufnehmen.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 39
6. Datenmanipulation: Übung / Praktikum 6
Führen Sie folgende Änderungen in der Musterdatenbank durch (mit
DML-Befehlen). Machen Sie anschließend alle Änderungen an der
Musterdatenbank mithilfe des SQL-Skriptes wieder rückgängig.
1) Löschen Sie alle Artikel
bestand_lager = 0 ist.
aus
der
Tabelle
Artikel
bei
den
2) Erhöhen Sie bei allen Artikeln den Verkaufspreis um 2 % (auf Cent
runden!).
3) Löschen Sie alle Rechnungen, bei
Datum_faellig vor dem 01.01.2005 liegt.
denen
das
Attribut
4) Löschen Sie den Inhalt der Tabelle Artikellager.
5) Eliminieren Sie die Tabelle Lieferanten aus der Datenbank.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 40
[..] 6. Datenmanipulation: Übung / Praktikum 6
6) Fügen Sie folgende Information in die Datenbank ein: Der Auftrag
5612 wurde am 15.4.2006 für den Kunden 10000 angelegt.
7) Erhöhen Sie den Mehrwertsteuersatz bei allen Rechnungen auf
19%.
8) Halbieren Sie alle Rabatte in den Aufträgen außer beim Kunden
mit der Kundennummer 10006.
9) Für alle Kunden der Kundengruppe 1-5 wurde am 02.06.2007 eine
neue
Kreditauskunft
eingeholt.
Ändern
Sie
die
Daten
entsprechend.
10)Löschen Sie alle Artikel, die vom Lieferanten mit der
Lieferantennummer 12191 geliefert werden und eine Lieferzeit
von mehr als 5 Tagen haben (über Lieferantennummer).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 41
[..] 6. Datenmanipulation: Übung / Praktikum 6
11)Löschen Sie alle Artikel auf die eine der folgenden Eigenschaften
zutrifft: Jahresumsatz oder Vorjahresumsatz kleiner als 2000€, in
der Warengruppe 1, Einkaufspreis kleiner als 50€.
12)Ändern Sie den Auftrag mit der Nummer 5. Die Kundennummer
lautet 10001, der Vertreter hat die Nummer 2 und es sind 3%
Rabatt vorgesehen.
13)Erstellen Sie eine neue Rechnung mit diesen Daten:
Kundennummer: 10006
Sachbearbeiternummer: 3
Fälligkeitsdatum: 02.06.2007
Zahlbetrag insgesamt: 2732,20€
Ergänzen Sie eventuell fehlende Attribute sinnvoll.
14)Löschen Sie alle Daten in denen die Kundennummer 10002
auftaucht (3 Tabellen).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 42
7. Select Grundlagen: Übung / Praktikum 7
1) Wie viele unterschiedliche Artikel haben einen Lagerplatz
(Artikellager)? Nenne die verschiedenen Artikelnummern (2
Anweisungen).
2) Welche Vertreter haben Aufträge im Jahr 2004 abgeschlossen
(jeden nur 1x nennen)?
3) Bei welchen Kunden (Kundennummer, Name, Datum der letzten
Auskunft) im Postleitzahlbereiches 5xxxx – 9xxxx wurde seit mehr
als einem Jahr keine Kreditauskunft mehr eingeholt?
4) Welche Aufträge wurden in 2004 oder 2005 abgeschlossen?
Geben Sie die Auftragsnummer und die Kundennummer aus und
sortieren Sie die Ausgabe nach dem Datum. Verwenden Sie
folgende Ausdrücke: between, in extract (3 Lösungen).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 43
[..] 7. Select Grundlagen: Übung / Praktikum 7
5) Ändern Sie den Spaltennamen „bestand_lager“ der Tabelle
Artikellager in „bestand“. Verwenden Sie nicht den renameOperator, sondern erstellen Sie eine Hilfstabelle, kopieren die
Daten hinein. Löschen Sie die Originaltabelle und erstellen Sie
diese neu. Kopieren Sie die Daten zurück und löschen dann die
Hilfstabelle.
Führen Sie anschließend das Datenbankskript neu aus.
6) Erzeugen Sie eine Liste aller unbezahlten Rechnungen (Nummer,
Zahldatum, Wert). Wenn das Zahldatum die NULL-Marke enthält,
geben Sie stattdessen die Zeichenfolge „kein Datum“ aus.
7) Listen Sie von allen Artikeln die Nummer, den Verkaufs- und den
Einkaufspreis auf. Wenn letzterer größer als 60% des
Verkaufspreises ist, geben Sie hinter die Daten des Artikels in
einer eigenen Spalte das Zeichen „*“ aus.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 44
[..] 7. Select Grundlagen: Übung / Praktikum 7
8) Listen
Sie
alle
Auftragspositionen
mit
Auftragsnummer,
Positionsnummer, Artikelnummer, Preis, Menge sowie dem BruttoPositionswert aus, der sich aus Menge* Preis* Mehrwertsteuersatz
errechnet. Runden Sie den Brutto-Positionswert auf Euro. Nennen
Sie die Spalte auch „Brutto-Positionswert
9) Ermitteln Sie je Artikel die Gewinnspanne (VerkaufspreisEinkaufspreis) und geben Sie die Artikel (alle Daten) sowie die
Gewinnspanne aus, bei denen die Gewinnspanne < 80 % des
Verkaufspreises ist. Sortieren Sie die Ausgabe nach der
Gewinnspanne absteigend.
10)Bei
welchen
Kunden
(Nummer,
Name,
Kreditlimit,
Saldo_offene_Rechnungen ausgeben), bei denen bereits ein
Kreditlimit bzw. das Saldo eingegeben wurde (nicht NULL), ist
das Saldo gleich dem Kreditlimit?
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 45
[..] 7. Select Grundlagen: Übung / Praktikum 7
11)Bei welcher Artikelbewegung unterscheiden sich Einstandspreis
und Einstandswert? Geben Sie die Artikelnummer, Uhrzeit und
Einstandspreis sowie Einstandswert zur Kontrolle aus.
12)Geben Sie alle Daten der Kunden aus, die das Wort ‚Buero‘ im
Namen enthalten.
13)Erzeugen Sie als Ausgabe eine Liste aller Artikel, deren
Artikelnummer sich aus bis zu 3 beliebigen Zeichen, einem
Bindestrich und einem beliebigen Rest zusammensetzt.
14)Listen Sie die Artikel aus Warengruppe 2 und 3 auf, die nicht von
HP sind. Geben Sie Artikelnummer, Bezeichnung und die Differenz
zwischen
aktuellem
Lagerbestand
und
dem
minimalen
Lagerbestand aus. Nennen Sie diese Spalte „Differenz
Lagerbestand“ und sortieren sie nach dieser.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 46
[..] 7. Select Grundlagen: Übung / Praktikum 7
15)Stellen Sie fest bei welchen Auftragspositionen die Menge nicht
mit der Summe aus offenen und gelieferten Mengen
übereinstimmt. Sortieren Sie nach Artikelnummer absteigend und
nach Positionsnummer absteigend.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 47
8. Gruppenfunktion: Übung / Praktikum 8
1) Nennen Sie die unterschiedlichen Kunden, von denen Rechnungen
in der Datenbank existieren (nur Kundennummer) und je Kunde
die Anzahl der Rechnungen.
2) Nennen Sie den durchschnittlichen Rabatt je Kunde, den er bei
seinen Aufträgen des Jahres 2004 erhalten hat, wenn dieser
größer als 5% ist.
3) Ermitteln Sie aus den Auftragspositionen je Auftrag die
Auftragsnummer und die gesamte gelieferte Menge des Auftrages
(geliefert), wenn diese größer 3 ist.
4) Nennen Sie das höchste Kreditlimit aller Kunde der Kundengruppe
1 oder 7.
5) Berechnen Sie die Summe der Rechnungsbeträge je Kunde,
Ausgabe nur, wenn diese größer 0 ist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 48
[..] 8. Gruppenfunktion: Übung / Praktikum 8
6) Welche Kunden haben mehr als einen Auftrag abgeschlossen, der
dem Vertreter 3 zugeordnet ist (Kundennummer, Anzahl)?
7) Was ist der höchste Rechnungsbetrag aller unbezahlten
Rechnungen, die bereits gemahnt wurden (datum_mahnung_1
trägt nicht die Null-Marke)?
8) Bilden Sie die Summe aller Rechnungsbeträge je Auftrag sowie
aller Zahlbeträge je Auftrag sowie die Differenz zwischen beiden.
Geben Sie diese Daten mit der Auftragsnummer und der
Kundennummer nach Kundennummer aufsteigend und innerhalb
der Kundennummer nach der Differenz absteigend sortiert aus.
Geben Sie nur Aufträge aus, bei denen der Absolutbetrag der
Differenz größer 1 Euro ist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 49
[..] 8. Gruppenfunktion: Übung / Praktikum 8
9) Bilden Sie je Kunde (Kundennummer) die maximale Differenz
zwischen Rechnungsdatum und Zahldatum seiner Rechnungen in
Tagen.
10)Bei welchen Warengruppen ist die Summe der Jahresumsätze
größer als die Summe der Vorjahresumsätze?
11)Es ist eine Liste zu erzeugen, die je Vertreter die Anzahl der
Aufträge aufführt, die ihm zugeordnet sind. Es sind nur Aufträge
der Jahre 2004 und 2005 zu berücksichtigen. Es sind nur Vertreter
interessant, die mehr als 1 Auftrag abgeschlossen haben.
12)An sich soll jeder Artikel in einem Auftrag nur 1x vorkommen. Wo
gibt es Abweichungen? Listen Sie Auftrag, Artikelnummer und
Anzahl aller Artikel auf, die in einem Auftrag mehr als einmal
vorkommen.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 50
[..] 8. Gruppenfunktion: Übung / Praktikum 8
13)Listen Sie je Auftrag die Summe der unbezahlten, fälligen
Rechnungsbeträge auf. Ist nichts fällig, schreiben Sie stattdessen
'Nichts fällig' in die Spalte.
14)Geben die Artikelnummer und die Anzahl der Artikelbewegungen
aus, falls diese größer 5 ist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 51
[..] 8. Gruppenfunktion: Übung / Praktikum 8
17)Geben Sie die Summe der Preise zu jedem Lieferanten und die
Lieferantennummer aus der Tabelle Lieferprogramm aus.
Berücksichtigen Sie nur Werte bei denen die Lieferzeit größer als 2
ist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 52
9. Subqueries: Übung / Praktikum 9
1) Nennen Sie je Kunde (Kundennummer) die Rechnungsnummer
der Rechnung, welche die
maximale Differenz zwischen
Rechnungsdatum und Zahldatum von allen seinen Rechnungen
hat.
2) Stellen Sie folgende Liste auf: Kundennummer, Name, Anzahl der
Aufträge des Kunden in 2005, Anzahl der Rechnungen des Kunden
in 2005. Schreiben Sie die Zeichenkette „Nicht vorhanden“
anstelle des Wertes 0 bei jeder der beiden Zahlen.
3) Welche Artikel kommen in keiner Auftragsposition vor (nur
Artikelnummer zeigen)?
4) Listen Sie alle Artikel auf, die in wenigstens einer Auftragsposition
vorkommen. Geben Sie für die Lösung dieser Aufgabe 4
Alternativen mit Subqueries an.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 53
[..] 9. Subqueries: Übung / Praktikum 9
5) Welche Artikel haben die größte Gewinnspanne ihrer Gruppe
(Differenz: Verkaufspreis - Einkaufspreis). Listen Sie den Artikel,
die Gruppe, die Gewinnspanne auf. Ausgabe sortiert nach Spanne.
6) Listen Sie alle Kunden (Kundennummer, Name) auf, für die eine
unbezahlte Rechnung existiert.
7) Listen Sie für alle Kunden, für die eine unbezahlte Rechnung
existiert, die Kundennummer, den Namen und die Summe der
Rechnungsbeträge der unbezahlten Rechnungen auf.
8) Geben Sie alle Kundennummern der Kunden aus Kundengruppe 7
aus, falls sie einen größeren Jahresumsatz haben als alle Kunden
der Kundengruppe 0.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 54
[..] 9. Subqueries: Übung / Praktikum 9
9) Listen Sie die Kunden mit Nummer und Name auf, deren Aufträge
vom Vertreter 1 bearbeitet werden. Beachten Sie jedoch nur
Aufträge, die mehr als 2 Auftragspositionen haben.
10)Welche Artikel haben keinen Platz im Artikellager? Als Ausgabe
soll die Artikelnummer, der minimalen Bestand und der aktuelle
Lagerbestand erfolgen. Verwenden Sie nicht den Operator ‚in‘.
11)Welche Rechnungen eines Kunden haben einen höheren
Rechnungsbetrag als sein durchschnittlicher Rechnungsbetrag?
Ausgabe: Nummern der Rechnungen und Kunden sowie der
Rechnungsbetrag.
12)Welche Monitor sind teurer als irgendein Scanner? Verwenden Sie
zur Abfrage die Warengruppe.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 55
[..] 9. Subqueries: Übung / Praktikum 9
13)Welche(r) Artikel haben(hat) die längste Lieferzeit? Geben Sie
Artikelnummer, Bezeichnung und Lieferzeit aus.
14)Welchem Kunden gehört die Rechnung mit dem aktuell höchsten
fälligen, unbeglichenen Betrag?
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 56
[..] 10. Join: Übung / Praktikum 10
Lösen Sie die folgenden Aufgaben nur unter Verwendung eines Join,
auch wenn andere Lösungen denkbar wären.
1) Erzeugen Sie eine Liste, die den durchschnittlichen Auftragsrabatt
aller Aufträge je Kundengruppe anzeigt.
2) Erzeugen Sie eine Liste mit folgenden Daten: Kundennummer,
Name, Auftragsnummer, Auftragswert (=Preis*Menge summiert
über alle Positionen des Auftrages).
3) Erstellen Sie eine Liste, die je Artikel (Artikelnummer reicht) die
durchschnittliche Abweichung des Verkaufspreises (Attribut
Verkaufspreis im Artikelstamm) von den in Aufträgen erzielten
Verkaufspreisen (Attribut Preis in Auftragspositionen) angibt.
Ausgabe nur, wenn die Abweichung größer als 10% des
Verkaufspreises (Artikelstamm) ist (Absolutbetrag beachten!).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 57
[..] 10. Join: Übung / Praktikum 10
4) Wurden Artikel im Zeitraum 1.Januar 2004 bis 31.12.2005 mehr
als 1x verkauft (verkauft = kommt in einer Auftragsposition vor)?
Wenn ja, welche (Artikelnummer, Bezeichnung) und wie oft?
5) Listen Sie auf, wie oft jeder Artikel überhaupt bisher verkauft
wurde (s.o.). Wurde er noch nicht verkauft, schreiben Sie in die
Spalte anstelle der Anzahl der Verkäufe die Zeichenkette ’kein
Verkauf’.
6) Zu welchen Aufträgen (nur Auftragsnummer) wurde noch keine
Rechnung geschrieben?
7) Geben Sie je Kunde die Kundennummer, den Namen und die
Anzahl seiner Aufträge aus. Geben Sie auch Kunden aus, die keine
Aufträge haben (0 als Anzahl ausgeben).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 58
[..] 10. Join: Übung / Praktikum 10
8) Erstellen Sie eine Liste aller Aufträge des Jahres 2004. Ermitteln
Sie je Auftrag die Summe der Rechnungsbeträge der unbezahlten
Rechnungen (bezahlt=0). Berücksichtigen Sie nur Aufträge, bei
denen die Summe größer als 100€ ist. Geben Sie die
Auftragsnummer und die Summe aus. Nennen Sie die
Summenspalte „Summe offene Rechnungen“. Ist die Summe
größer als 1000€, setzen Sie vor und hinter die Summe das
Zeichen „*“. Es gelte noch folgende Einschränkung: Es sind nur
Aufträge zu berücksichtigen, deren zugeordneter Kunde nicht die
Kundengruppe 0 hat.
9) Geben Sie eine Liste der Sachbearbeiter mit Namen und
Nummern aus. Zählen Sie die Anzahl der Aufträge zu dem
Sachbearbeiter. Ausgabe nur, wenn die Anzahl größer 1.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 59
[..] 10. Join: Übung / Praktikum 10
10)Welche Artikel entsprechen nicht dem minimalen Bestand? Geben
Sie die Artikelnummer und den dazugehörigen Lieferanten mit
Nummer und Name aus.
11)Welcher Artikel im Artikellager kann nicht mehr direkt geliefert
werden? Geben Sie die Nummer, den Lagerbestand und die
Summe der noch offenen Menge der Auftragspositionen an.
Summieren Sie nicht den Lagerbestand, gehen Sie davon aus,
dass jeder Artikel nur einen Lagerplatz hat!
12)Ermitteln Sie je Warengruppe den größten Lagerbestand.
13)Erzeugen Sie eine Liste, welche die am jeweils aktuellen Tag je
Kunde (Kundennummer) die Summe der an diesem Tag fällig
werdenden Rechnungsbeträge ausweist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 60
[..] 10. Join: Übung / Praktikum 10
14)Listen Sie je Kunde die Kundennummer, den Namen sowie das
Fälligkeitsdatum (Datum_faellig) seiner unbezahlten Rechnung
mit der längsten Überfälligkeit auf (Überfälligkeit = fällig und nicht
bezahlt). Führen Sie nur Kunden auf, bei denen dieses
Fälligkeitsdatum um mehr als 2 Wochen überschritten ist.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 61
11. Mengenoperationen: Übung / Praktikum 11
Lösen Sie die folgenden Aufgaben nur unter Verwendung von
Mengenoperatoren, auch wenn andere Lösungen denkbar wären.
1) Zu welchen Aufträgen (Auftragsnummer) gibt es Rechnungen?
2) Zu welchen
Rechnungen?
Aufträgen
(Auftragsnummer)
gibt
es
keine
3) Gibt es Kunden, die auch Lieferanten sind (Übereinstimmung über
den Namen vornehmen)?
4) Geben Sie alle Artikelnummern aus, die in Auftragspositionen und
Artikelbewegungen auftauchen (auch doppelte Nennungen).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 62
12. Views: Übung / Praktikum 12
1) Erzeugen Sie einen View „Auftragswert“ mit folgenden Daten:
Auftragsnummer, Kundennummer, Auftragsdatum, Auftragswert
(= Verkaufspreis * Verkaufsmenge summiert über alle Positionen
des Auftrages). Testen Sie den View (mittels select)
2) Erzeugen Sie einen View, der jahresweise die Summe der
Auftragswerte aller Aufträge des Jahres zeigt. Basieren Sie den
View auf dem View „Auftragswert“.
3) Erstellen Sie einen View, der - sortiert nach Datum -die an dem
jeweiligen Tag fälligen Rechnungsbeträge (unbezahlt!) aufführt.
4) Erstellen Sie einen View, der je Kunde die durchschnittliche Anzahl
der Tage aufführt, die der Kunde das Fälligkeitsdatum der
Rechnung überzieht (Differenz Fälligkeitsdatum / Zahldatum bei
bezahlten Rechnungen, Differenz Fälligkeitsdatum / Tagesdatum
bei unbezahlten Rechnungen).
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 63
[..] 12. Views: Übung / Praktikum 12
5) Ermitteln Sie mit Hilfe des vorigen Views, welcher Kunde sich im
Schnitt am meisten Zeit lässt, seine Rechnungen zu bezahlen (nur
select, kein view)
6) Ermitteln Sie je in den Kunden vorkommenden Vertreter die
Anzahl der von ihm abgeschlossenen Aufträge im Jahr 2005
(auftrag_datum), die Summe der Rechnungsbeträge aller
Rechnungen, die aus Aufträgen des Vertreters hervorgegangen
sind (nur Rechnungen des Jahres 2005 berücksichtigen) sowie die
Summe
der
offenen
Beträge
(rechnungsbetragzahlbetrag_gesamt) aus den unbezahlten Rechnungen (auch nur
2005), die dem Vertreter zuzuordnen sind. Führen Sie auch
Vertreter auf, zu denen es keine Aufträge bzw. Rechnungen in
2005 gibt (zu jeder Rechnung muss es einen Auftrag geben !).
(Hinweis: Die Aufgabe ist nur mit views lösbar, beginnen Sie mit
einem view, der alle Vertreter ermittelt)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 64
14. Datenintegrität Constraints: Übung / Praktikum 14
1) Implementieren Sie folgende Constraints auf der Muster-DB:
Legen Sie dazu die DB neu an (nur create table). Nach
Implementierung der Constraints spielen Sie bitte die Daten
wieder ein. Sie werden einige Constraint-Verletzungen bemerken.
Relation: Artikel
Artikel_Nr prim key
Lieferanten_Nr foreign key (Lieferant)
Warengruppe Wert: 1-99, not null
Mengeneinheit Wert: "g","kg","t","Stck"
Verkaufspreis not null, Wert > 0 und > einkaufspreis
Einkaufspreis Wert >=0 und < verkaufspreis
Lieferzeit Wert > 0 und < 100
Bestand_Minimum Wert >= 0
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 65
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
Relation: ArtikelLager
Artikel_Nr prim key, foreign key (Artikel)
Lager_Nr
prim key, Wert >0
Bestand_Lager
Relation : Kunden
Kunde_Nr prim key
Name
not null
Plz_Strasse not null
Land not null , Wert : "D", "A", "I", "CH", "GB"
Ort not null
Kundengruppe >=0, <= 100
Gesperrt_Kz Wert: 0,1
Kreditlimit NOT NULL, >=0
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 66
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
Relation: Auftraege
Auftrag_Nr prim key
Auftrag_Datum not null
Kunde_Nr
foreign key (Kunden) , not null
Vertreter_Nr not null
Rabatt_Prozent Wert >=0 und < 50
Relation: Auftragspositionen
Auftrag_Nr prim key, foreign key (Auftrag)
Position_Nr prim key, > 0
Position_aktiv not null, Wert 0,1
Artikel_Nr not null, foreign key (Artikel)
Menge not null
Preis not null, Wert >= 0
Berechnet Wert <= geliefert
eindeutig: (auftrag_nr, Artikel_nr)
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 67
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
Relation: Rechnungen
Rechnung_Nr prim key
Rechnung_Datum not null
Kunde_Nr not null, foreign key (Kunde)
Auftrag_Nr not null, foreign key (Auftraege)
Datum_Faellig Wert NULL oder >= Rechnung_Datum
Datum_Mahnung_1 Wert NULL oder >= Datum_Faellig / Rechnung_Datum
Datum_Mahnung_2
NULL)
NULL oder > Datum_Mahnung_1 (Wert nur wenn Datum_Mahnung_1 IS NOT
Zahldatum NULL oder > Rechnung_Datum
Bezahlt Wert 0,1
Relation: Lieferant
Lieferanten_nr primary key
Name not null
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 68
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
Relation: Bestellungen
Bestell_Nr
prim key
Lieferanten_Nr not null, foreign key (Lieferanten)
Lieferant_Name not null
Bestelldatum
not null
Artikel_Nr
not null, foreign key
Menge
Wert > 0
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 69
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
2) Implementieren Sie folgende Constraints auf der Hochschul-DB.
Student
Matrikelnr Name Fachbereich Fachsemester Geb_Dat Geschlecht
Schlüssel: Matrikelnr, Geschlecht: w / m, Fachbereich: 1-9
Dozent
Name Fachbereich Lehrgebiet
Schlüssel: Name
Vorlesung
Vorl_Nr
Bezeichnung
Hoersaal
Plätze
Beginn_h
Ende_h
Dozent
Schlüssel: Vorl_Nr, Plätze: 20-500, Fremdschlüssel: Dozent
auf Tabelle Dozent, Feld Name
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 70
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
hoert
Student
Vorlesung
Schlüssel: Student und Vorlesung, Fremdschlüssel:
Student auf Tabelle Student, Feld Matrikelnr, Vorlesung auf
Tabelle Vorlesung Feld Vorl_Nr
Klausurergebnis
Student
Fach
Klausur_Datum
Ergebnis
Schlüssel: Student und Fach und Klausur_Datum,
Fremdschlüssel: Student auf Tabelle Student, Fach
Matrikel_Nr
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 71
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
3) Es sei folgende Integritätsregel gegeben: „Für einen gesperrten
Kunden darf kein Auftrag erfasst werden“. Setzen Sie diese Regel
durch einen prüfenden View um.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 72
[..] 14. Datenintegrität Constraints: Übung / Praktikum 14
4)
Seien die folgenden Daten einer Vereinsanwendung gegeben. Implementieren Sie die genannten
Integritäten (alter table add constraint ...) (kein create table schreiben, alter table nur 1 x pro Relation
ausschreiben)
Mitglied
Nummer
Name
date
primärschlüssel
nicht leer
Ø
01.01.1940
<= 31.12.1994
date
Ø
KrankenVSNR
Monatsbeitrag
number
number
eindeutig
Zwischen 50 und 100 €
Beitragszahlung
Mitgliedsnummer
Relative Zahlnummer
Zahldatum
BLZ
Kontonummer
number
number
date
number
number
Fremdschlüssel Mitglied
Nicht leer
Nicht leer
Geburtsdatum
Eintrittsdatum
number
char(20)
Geburtsdatum
Primärschlüssel: (Mitgliedsnummer + Relative Zahlnummer)
Eindeutig: (BLZ + Kontonummer)
Mitgliedsnummer ändern, wenn in „Mitglied“ geändert wird.
Mitgliedsnummer auf NULL setzen, wenn Mitglied gelöscht wird.
Datenbanken 1
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 73