Aufgabe 1) (15 Punkte) Überführen Sie folgendes E-R

Aufgabe 1) (15 Punkte)
Überführen Sie folgendes E-R-Diagramm in eine objektrelationale Datenbank (Oracle) und
schreiben Sie die SQL-Anweisungen für die benötigten Typen und objektrelationalen
Tabellen.
Strasse
(char(30))
NR
(int)
Strasse
(char(30))
PLZ (int)
PLZ (int)
Ort
(char(30))
GebDat
(date)
Ort
(char(30))
Adresse
n
Mitarbeiter
Adresse
1
arbeitete
_fuer
1
Unternehmer
Zeitraum
.
1
erhielt
Von
(date)
Bis
(date)
n
Datum
(date)
verfasst
Beurteilung
n
Lösung:
create type adresse_typ as object (strasse char(30), plz int, ort char(30) );
create type adresse_table as table of adresse_typ;
create type zeitraum_typ as object (von date, bis date);
create type unternehmer_typ as object (name char(30), adresse adresse_table);
create type beurteilung_typ as object (datum date, verfasst ref unternehmer)
create type beurteilung_table as table of beurteilung_typ
create type arbeitet_fuer_type as object (zeitraum zeitraum_typ, unternehmer ref
unternehmer_typ )
create type mitarbeiter as object (nr int, gebdat date, adresse adresse_typ, arbeitete_fuer
arbeitet_fuer_type, beurteilungen beurteilung_table)
create table unternehmer of unternehmer_typ (name primary key)
create table mitarbeiter of mitarbeitertyp (nr primary key, nested table adressen store as
adressen_rel, nested table arbeitete_fuer store as arbeitete_fuer_rel)
Name (char)
Basis für die folgenden Aufgaben ist das im Anhang vorgegebeneTypsystem
(Bundesliga) bzw. die Typtabellen
Aufgabe 2 (10 P):
Erweitern Sie den Spielertyptyp um eine „map order“-Ordnungsfunktion. Sie soll Spieler in
folgender Reihenfolge sortieren:
a) Anzahl der Positionen, auf denen er spielen kann
b ) Gehalt
Implementieren Sie diese Ordnungsfunktion.
Lösung:
create type body Spielertyp as
map order function spielerorder return char
as e1 char(20)
begin
e1=cast(self.position.count as char(5))
e1 = e1+ cast(self.gehalt as char(10))
return e1;
end ;
Aufgabe 3 (35 P)
Lösen Sie folgende Aufgaben mittels SQL (Benutzen Sie bei Deferenzierungen grundsätzlich
explizit den deref-Operator ! Sonst Punktabzug)
a)
Wieviele Spieler hat die Ligamannschaft, die in der Ligatabelle „ranking“ 1 hat. (5 P)
Select l.spieler.count from ligamannschaften l where ref(l) = (select mannschaft from
ligatabellenplaetze where ranking=1)
b)
Fügen Sie den Spieler Costa mit der Nummer 231 ein. Er hat keine Adresse, die
Kommunikationsverbindungen haben einen Eintrag, der z.Z. leer ist, er ist heute vor 15
Tagen eingestellt worden, seine Nationalität ist Brasilianisch (‚B‘), sein Gehalt ist 30000,- €,
Spieleinsätze hat er noch nicht, er hat bisher 4 Tore geschossen. Er kann auf denselben
Positionen spielen wie der Spieler mit der Nummer 232. (5 P)
Insert into personen (spielertyp(231, ‚Costa‘, Null, kommvb(null), current_date-15,
30000, cast (multiset(select treat(Value(r) as spielertyp).position from personen r
where r.nr=232)) as positiontabelletyp ,
0, 4, (select ref(p) from nationalitaet p where p.nationalitaet=’B’) )
c)
Listen Sie für den Spieltag 18.12.2012 je Spiel (SpielNr) die Anzahl der roten Karten
(Art=Platzverweis) auf (Spielereignis) (5 P)
Select s.spielnr , count(t.*) from spiele s, table(spielereignis) t
where s.datum = ‚18.12.2012‘ and t.art=‘Platzverweis‘
group by 1
d)
Wie viele Tore wurden am letzten Spieltag (22.01.2012) von Spielern geschossen, die
Abwehrspieler sind ? (5 P)
Select t.count from spiele s , table(spieltore) t, table(treat(deref(t.torschuetze) as
spielertyp).position) p
Where s.datum=’22.01.2012’ and value(p)=’Abwehr’
e)
Für welche Ligamannschaften (Bezeichnung) arbeiten Vereinsärzte, deren
Fachrichtung ‚Orthopädie‘ ist ? (5 P)
Select bezeichnung from ligamannschaften where deref(treat(dref(mannschaftsarzt)
as arzttyp).fachrichtung).fachrichtung=‘Orthopädie‘
f)
Bei welchen Spiel (Spielnr, Datum) haben mehr als 24 Spieler mitgespielt
(Mannschaftsaufstellung) (5 P)
Select spiel_nr, datum from spiele s where s.mansschaftsaufstellung.count > 24
g)
Löschen Sie den Spieler aus der Mannschaftsaufstellung des Spiels vom 22.01.2010,
bei dem ‚BVB‘ Heimmannschaft war, der in der 66. Minute ein Tor geschossen hat.. (5 P)
Delete from table(select mannschaftsaufstellung from spiele s where
s.datum=’22.01.2010‘ and dref(s.heimmannschaft).bezeichnung=‘BVB‘) m where
ref(m) in select spielereignis.torschuetze from spiele t where t.datum=’22.01.2010‘
and dref(t.heimmannschaft).bezeichnung=‘BVB‘