2. Praktikum WS 2005/06 Fach Datenbanken und Informationssysteme

Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
2. Praktikum
WS 2005/06
Fach Datenbanken und Informationssysteme
Aufgabe 1:
Relationale Algebra im „Reisebüro“
Ein Reisebüro verwendet das folgende Datenbankschema:
Stadt (Stadt, Land, Flughafen)
Reisezeit (Flughafen1, Flughafen2, Zeit)
Hotel (Hotel, Stadt, Klasse, Adresse, AnzahlEZ, AnzahlDZ, PreisEZ, PreisDZ)
Buchung (BuchungsNr, Hotel, Stadt, Anreisedatum, Abreisedatum, KundenNr,
GebuchteEZ, GebuchteDZ)
Kunde (KundenNr, Name, Vorname, Adresse)
Dabei liegen folgende Vereinbarungen vor :
Wir gehen dabei davon aus, dass die von den Hotels angegebenen Zimmer ausschließlich von unserem Reisebüro belegt werden können.
- Ein Kunde wird festgehalten mit Kundennummer, Name, Vorname und Adresse.
- Ein Hotel hat eine eindeutige Adresse in einer Stadt, außerdem eine Klasse. Jedes Hotel hat eine
festgelegte Zahl von Einzel- und Doppelzimmer mit festgelegten Preisen pro Übernachtung im Einzel- und Doppelzimmer.
- Eine Stadt liegt in genau einem Land und hat genau einen Flughafen in ihrer Nähe.
- Eine Buchung besteht aus der Buchungsnummer, Name und Stadt des gebuchten Hotel, An- und
Abreisedatum, der Kundennummer desjenigen Kunden, der den Aufenthalt bucht und der Zahl der
von ihm gebuchten Einzel- und Doppelzimmer und der Zeit.
Die Zeit ist die Reisezeit insgesamt zwischen zwei Flughäfen, also den Start – und Landeflughäfen
ohne Zwischenlandung
Beantworten Sie die folgenden Fragen in der relationalen Algebra und zeichnen Sie einen Operatorbaum! Falls sich die Frage nicht mittels der relationalen Algebra beantworten lässt, begründen Sie bitte, wieso!
a) Bestimmen Sie alle Städte (mit Land und allen Flughäfen), in denen es ein Hotel mit dem Namen
'Tiziano' gibt.
Projektion
Stadt, Land, Flughafen
*
Stadt
Selektion
Hotelname = 'Tiziano'
Hotel
Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
b) Welche Hotelketten haben in allen erfassten Städten Filialen? (d.h. welche Hotels mit der gleichen
Bezeichnung kommen in allen erfassten Städten vor)?
Division
Projektion
Projektion
Stadt
Stadtname, Hotelname
Hotel
Hotel
c ) Nennen Sie Name, Vorname und Adresse aller Kunden, die ein Hotel gebucht haben, welches über
mehr Einzelzimmer als Doppelzimmer verfügt.
Projektion
Name, Vorname,
Adresse
*
*
Kunde
Selektion
Buchung
AnzahlEZ >AnzahlDZ
Hotel
d) Nennen Sie alle Hotels und die Städte, in denen sie liegen, für die keine Buchung vorliegt!
Differenz
Projektion
Projektion
Hotel, Stadt
Hotel
Hotel, Stadt
Buchung
Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
Aufgabe 2:
Relationale Algebra „Weinhändler“
Betrachten Sie die folgende Mini-Wein-Datenbank. Die Primärschlüssel sind unterstrichen.
Rebsorte
Sorte
1
2
3
Wein
Name
Merlot
Riesling
Spätburgunder
Farbe
rot
weiss
rot
Jahr
2003
2004
2001
2004
Preis
20
14
50
10
Wein_ID
1
2
3
Name
Meerling
Vom Feld
Baldere
Rebsorte
1
2
3
Jahrgang
Wein_ID
2
2
3
1
Qualität
1
2
1
2
Beantworten Sie die folgenden Fragen in der relationalen Algebra und zeichnen Sie einen Operatorbaum! Falls sich die Frage nicht mittels der relationalen Algebra beantworten lässt, begründen Sie bitte, wieso! Falls Sie einen Operatorbaum zeichnen, geben Sie bitte das Ergebnis der
Anfrage an.
a) Geben Sie für alle Weine des Jahrgangs 2004, die mehr als 13 Euro kosten, neben ihrem Namen
auch den Namen der zugehörigen Rebsorte an.
Projektion
Name, Rebsorte
Selektion
Jahr = 2004 AND preis >= 13
*
*
Jahrgang
Ergebnis
NAME
Vom Feld
Rebsorte
Wein
Rebsorte
Riesling
b) Welche Weine (Name, durchschnittliche Qualität, durchschnittlicher Preis, maximaler Preis, minimaler Preis) haben eine durchschnittliche Qualität über alle ihre Jahrgänge von 2 und besser (also 1)?
Geht nicht, da der Durchschnitt in der relationalen Algebra nicht enthalten
ist!
Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
c) Welche Riesling-Weine (Wein_id, Name) haben in keinem (!) Jahrgang die Qualitätsstufe 3 besessen?
Projektion
Wein_Id, Name
*
Wein
Differenz
Projektion
Projektion
Wein_Id
Wein_Id
Selektion
Selektion
Rebsorte = 'Riesling'
Qualitaet = 3
Jahrgang
Wein
Ergebnis
NAME
Vom Feld
d) Welche Burgunder-Weine (Name, Jahrgang, Qualität, Preis) haben einen höheren Preis als der
Durchschnittspreis der Merlot-Weine?
Geht nicht, da der Durchschnitt in der relationalen Algebra nicht enthalten
ist!
e) Für welchem Jahrgang (Zeile in Tabelle „Jahrgang“) sind keine Rotweine (Farbe = ‚rot’) mehr vorrätig?
Projektion
Wein_Id, Name
*
Wein
Differenz
Projektion
Projektion
Jahrgang
Jahrgang
Selektion
Jahrgang
Farbe = 'rot'
*
Rebsorte
*
Jahrgang
Ergebnis
JAHR
2003
Wein
Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
Aufgabe 3:
Datenbankschema „Brumm & Brüder“
Generieren Sie für Ihr eigenes Brumm& Brüder-Datenmodell (physisches Modell) „per Knopfdruck“ aus
ERwin heraus ausführbare SQL-Scripte zur Implementierung Ihres Schemas in der Datenbank. (Wenigstens fünf Tabellen Ihres Modells sollten in dem ausführbaren Script enthalten sein.)
Ihr Datenmodell sollte enthalten:
1. Relationen, Attribute mit Oracle-Datentypen (!), Primärschlüsseln, Fremdschlüsseln
2. Domänen: mindestens drei selbst definierte Domänen erstellen, die auf Validation-Rules in
ERWIN beruhen und sich als CONSTRAINTS in der Datenbank erzeugen lassen
3. Zwei in ERWIN selbst definierte Benutzersichten (Views)
4. Mindestens zwei zusätzliche Sequenzen und drei Zweitschlüssel (Indizes)
5. eine Übersicht über Benutzerrechte
6. und zwei Integritätsregeln, die sich nicht als CONSTRAINTS darstellen lassen
7. Fügen Sie in jede Ihrer Tabellen im Datenbankschema Brumm& Brüder mindesten einen Datensatz ein
Bis auf die Benutzerrechte und komplexe Integritätsbedingungen können Sie die für das Datenbankschema notwendigen Informationen komplett in ERwin erstellen. Dazu das physische Modell vollständig
mit Informationen versorgen. Benutzerrechte und Integritätsbedingungen, die sich nicht mit ERwin beschreiben lassen, bitte in einer separaten Text-Datei ablegen.
Aufgabe 4:
Constraints „Brumm & Brüder“
Führen Sie auf Ihrer Datenbank Brumm& Brüder folgende Erweiterungen durch:
-
-
Erweitern Sie die Fahrzeug-Tabelle um die Spalte ’status’, die nur die Zustände ’V’ für ’vorbestellt’,
’A’ für ’ausgeliehen’ , ’T’ für ’Tag-Reinigung’, ’N’ für ’Nacht-Reinigung’ und ’S’ für ’sauber und entleihbar’ annehmen kann.
Programmieren Sie die Integritätsbedingung so, dass sie erst zum Transaktionsende geprüft wird
Default-Status soll ‚S’ sein.
Groß-/Kleinschreibung soll bei der Erfassung der Statuskürzel keine Rolle spielen.
Erstellen Sie Testfälle und demonstrieren Sie sie bei der Abnahme.
Hinweis:
Verwenden Sie den Prüfungszeitpunkt INITIALLY DEFERRED
Lösung:
ALTER TABLE fahrzeuge DROP (status);
ALTER TABLE fahrzeuge ADD ( status VARCHAR2(1) DEFAULT ‘S’
CONSTRAINT ic_status CHECK (UPPER(status) IN (‘V’,’A’,’T’,’N’,’S’))
INITIALLY DEFERRED);
-- alte Daten anpassen
UPDATE fahrzeuge SET status = ’S’;
NSERT INTO fahrzeuge VALUES (999888770, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘V’);
INSERT INTO fahrzeuge VALUES (999888771, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘A’);
INSERT INTO fahrzeuge VALUES (999888772, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘t’);
INSERT INTO fahrzeuge VALUES (999888773, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘N’);
INSERT INTO fahrzeuge VALUES (999888774, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘s’);
-- IC-Fehler
INSERT INTO fahrzeuge VALUES (999888775, 3, 66, 15800, ‘GM-ZX 89’, SYSDATE+100,
SYSDATE+100, ‘rot’, ‘j’, SYSDATE-60, NULL, ‘X’);
-- ohne COMMIT-Befehl akzeptiert das DBMS den fehlerhaften Datensatz vorerst und fügt ihn ungeprüft
Prof. Dr. Heide Faeskorn–Woyke – Prof. Dr. Birgit Bertelsmeier
Fakultät 10, Institut für Informatik, Fachhochschule Köln - Campus Gummersbach
-- in den Datenbestand ein.
COMMIT;
*
FEHLER in Zeile 1:
ORA-02091: Transaktion wurde zurückgesetzt
ORA-02290: Verstoß gegen CHECK-Regel (SCOTT.IC_STATUS)
-- auch die 5 ersten richtigen Datensätze wurden zurückgerollt, da sie zu der gesamten Transaktion
gehörten; die ’neue’ Datenbasis hat den gleichen Zustand wie vor Beginn der Transaktion

Download Report