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 from ; 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, =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 = 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