CREATE TRIGGER bestellerzeugung ON artikel

Vorlesung „Moderne Datenbanksysteme“
Bonusaufgabe 3
Aufgabe 1
Folgendes ER-Diagramm (Ausschnitt Zeitungsredaktion) sei gegeben. Überführen
Sie das Diagramm in objektrelationale Strukturen, indem Sie die notwendigen Typen
deklarieren und die Objekttabellen geeignet festlegen (Alle mehrwertigen Typen sind
mit dem Kollektionstyp table zu implementieren)
Name
Redaktion
Zeitung
PLZ
PNR
Vorjobs
Adresse
Ort
Person
Text (sehr
lang)
Datum
Fachgebiet
Redakteur
n
n
m
erhält
Beurteilung
Anteil (%)
1
schreibt
n
verfasst
Ist Chef
von
seit
n
Chefredakteur
1
1
n
Ernannt
am
1
Artikel
korrigiert
Kennzeichen
Inhalt (Text,
sehr lang)
Seite : 1
Vorlesung „Moderne Datenbanksysteme“
Bonusaufgabe 3
Aufgabe 2:
Beantworten Sie auf der Basis des Bundesligamodells folgende Anfragen:
a) Nenne alle Abwehr-Spieler der Ligamannschaft xyz ?
b) Gib mir die Adresse des Vereinsarztes der Ligamannschaft xyz
c) Nenne die Namen und die Nationalität aller am Spiel 1 beteiligten Spieler
d) Wieviel gibt jeder Verein für seinen Vorstand aus (Summe Verguetung) ?
e) Liste je Spieltag die Torschützen auf:
Datum Spieltag, Bezeichnung Heimmannschaft, Spielminute, Name Spieler, Nationalität,
d)
Seite : 2
Vorlesung „Moderne Datenbanksysteme“
Bonusaufgabe 3
Basis für die folgenden Aufgaben ist das im Anhang vorgegebene Typsystem bzw.
die Typtabellen
Aufgabe 3)
Erweitern Sie den Typ „Leser“ um eine Methode „anzahl_verspaetete_rueckgabe“,
welche für einen Leser die Anzahl der Bücher berechnet, die er entliehen und noch
nicht zurückgegeben hat, bei denen aber das Solldatum der Rückgabe bereits
überschritten wurde..
Implementieren Sie diese Methode.
Aufgabe 4 )
Erweitern Sie den Buchtyp um eine „order“-Ordnungsfunktion. Sie soll Bücher in
folgender Reihenfolge sortieren:
a) Anzahl der Entleihvorgänge für ein Buch
b) Anzahl der Leser, die dieses Buch als Lieblingsbuch haben
Implementieren Sie diese Ordnungsfunktion.
Aufgabe 5)
Geben Sie folgende Daten ein:
a) Geben Sie das Buch mit der ISBN „4“ ein, es heißt Sommer“, ist 2008 zum
ersten Mal erschienen und hat 2 Auflagen: 2008 20000 Stück und 2009 18000
Stück.
b) Geben Sie die Person 2 ein, sie heißt Meier, hat die Rufnummer ‚0876 6655’
und dieselben Hobbies wie ‚Müller’ (Von Müller (schon da!) kopieren!)
c) Person 3 ist „Leser“, sie heißt ‚Bach’, hat noch kein Telefon und hat als
Hobbies nur ‚Lesen’. Sein Lieblingsbuch ist das Buch 1, entliehen hat er noch
nichts.
d) Bach hat sich Buch 2 am 7.7.2008 entliehen. Er muss es 14 Tage später
wieder zurückgeben.
e) Buch 4 wird im Jahr 2009 noch einmal mit 60000 Stück aufgelegt.
Seite : 3
Vorlesung „Moderne Datenbanksysteme“
Bonusaufgabe 3
Aufgabe 6)
Lösen Sie folgende Aufgaben mittels SQL:
a) Wie oft wurde im Jahr 2008 das Buch mit dem Titel „Sommer“ entliehen ?
b) Geben Sie alle Bücher (ISBN, Titel) sowie ihre Auflagenzahlen aus dem Jahr
2009 aus.
c) Welcher bzw. welche Autor(en) (Name(n)) hat das Buch mit dem Titel
'Sommer' geschrieben ?
d) Erzeugen Sie eine Liste aller Bücher (ISBN, Titel), die heute zurückgegeben
werden müssten.
e) Erhöhen Sie alle Auflagen von Buch 1 im Jahre 2008 um 20 %.
f) Berechnen Sie: Wieviele Autoren arbeiten für den Verlag "Mehr“ ?
g) Ersetzen Sie bei allen Personen das Hobby ‚Fernsehen’ durch ‚Kino’
Seite : 4
Vorlesung „Moderne Datenbanksysteme“
Bonusaufgabe 3
Typsystem / Typisierte Relationen:
create type telefon_array as array(10) of varchar(20)
create type hobbies_table as table of varchar(15)
create type verlag as object (
name varchar(30),
ort varchar(50) )
create type verlag_table as table of verlag
create type auflage as object (
jahr integer,
exemplare integer )
create type auflage_table as table of auflage
create type buch as object (
ISBN number(8,0),
titel varchar(30) ,
auflagen auflage_table,
Erst_Erscheinungsjahr integer)
create type viele_buecher as table of ref buch
create type entleihvorgang as object(
buch_entliehen ref buch,
entliehen_am date,
soll_rueckgabe_am date,
ist_rueckgabe_am date)
/* solange ein Buch nicht zurueckgegeben wurde, trägt „ist_rueckgabe“ die Marke NULL */
create type entleihvorgang_table as table of entleihvorgang
create type persontyp as object (
nr number(5,0),
name varchar(20),
telefon telefon_array,
hobbies Hobbies_table)
not final
create type leser under persontyp (
lieblingsbuch ref buch,
entliehen entleihvorgang_table )
create type autor under persontyp (
schreibt_fuer verlag_table,
ist_autor_von viele_buecher,
arbeitet_zusammen_mit ref autor)
/
create table buecher of buch
(ISBN primary key)
nested table auflagen store as alleauflagen_table
/
create table personen of persontyp
(nr primary key)
nested table hobbies store as allehobbies_rel
/
Seite : 5