Lösungen Übungsaufgaben aktuell

FH
0. Datenintegrität constraints : Übung / Prakt. constraints
1) Implementieren Sie folgende constraints auf der Muster-DB:
Relation: Lieferant
Lieferanten_nr primary key
Name not null
alter table lieferanten add constraint lieferantennr_pk
primary key(lieferanten_nr);
alter table lieferanten modify (name not null);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 1
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 2
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
alter table artikel add constraint artikel_nr_pk primary key(artikel_nr);
alter table artikel add constraint lieferanten_fk
foreign key (lieferanten_nr)
references lieferanten(lieferanten_nr) on update cascade on delete set null;
alter table artikel add constraint warengruppe_check
check (warengruppe between 1 and 99);
alter table artikel modify (warengruppe not null);
alter table artikel add constraint mengeneinheit_check
check (mengeneinheit in ('g', 'kg', 't', 'Stck'));
alter table artikel modify (verkaufspreis not null);
alter table artikel add constraint verkaufspreis_check
check (verkaufspreis > 0 and verkaufspreis > einkaufspreis);
alter table artikel add constraint einkaufspreis_check
check (einkaufspreis >=0 and einkaufspreis < verkaufspreis);
alter table artikel add constraint lieferzeit_check
check (lieferzeit > 0 and lieferzeit < 100);
alter table artikel add constraint bestand_min_check
check (bestand_minimum >=0);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 3
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
Relation: ArtikelLager
Artikel_Nr prim key, foreign key (Artikel)
Lager_Nr
prim key, Wert >0
Bestand_Lager
alter table artikellager add constraint artikelnr_pk
primary key(artikel_nr, lager_nr);
alter table artikellager add constraint artikelnr_fk
foreign key(artikel_nr) references artikel(artikel_nr)
on update cascade on delete cascade;
alter table artikellager add constraint lager_nr_check
check(lager_nr > 0);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 4
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 5
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
alter
alter
alter
alter
alter
alter
alter
alter
alter
alter
table kunden add constraint kundenr_pk primary key(kunde_nr);
table kunden modify (name not null);
table kunden modify (plz_strasse not null);
table kunden modify (land not null);
table kunden add constraint land_check
check (land in ("D", "A", "I", "CH", "GB"));
table kunden modify (ort not null);
table kunden add constraint kundengruppe_check
check (kundengruppe >= 0 and kundengruppe <= 100);
table kunden add constraint gesperrt_kz_check
check (gesperrt_kz in (0,1));
table kunden modify (kreditlimit not null);
table kunden add constraint kreditlimit_check
check (kreditlimit >= 0);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 6
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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
alter table auftraege add constraint auftragnr_pk
primary key(auftrag_nr);
alter table auftraege modify (auftrag_datum not null);
alter table auftraege add constraint kundenr_fk
foreign key (kunde_nr) references kunden(kunde_nr) on update
cascade on delete set default;
alter table auftraege modify (kunde_nr not null);
alter table auftraege modify (vertreter_nr not null);
alter table auftraege add constraint rabatt_check
check (rabatt_prozent between 0 and 50);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 7
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 8
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
alter table auftragspositionen add constraint auftragnr_pk2
primary key(auftrag_nr, position_nr);
alter table auftragspositionen add constraint auftragnr_fk2
foreign key(auftrag_nr) references auftraege(auftrag_nr) on
update cascade on delete cascade;
alter table auftragspositionen modify (position_aktiv not null);
alter table auftragspositionen add constraint position_check
check (position_aktiv in (0,1));
alter table auftragspositionen modify (artikel_nr not null);
alter table auftragspositionen add constraint artikelnr_fk
foreign key(artikel_nr) references artikel(artikel_nr) on update
cascade on delete set null;
alter table auftragspositionen modify (menge not null);
alter table auftragspositionen modify (preis not null);
alter table auftragspositionen add constraint preis_check
check(preis >= 0);
alter table auftragspositionen add constraint berechnet_check
Datenbanken
2
check(berechnet
< geliefert); - WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 9
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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 oder > Datum_Mahnung_1 (Wert nur wenn
Datum_Mahnung_1 IS NOT NULL)
Zahldatum NULL oder > Rechnung_Datum
Bezahlt Wert 0,1
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 10
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
alter table rechnungen add constraint rechnungnr_pk
primary key (rechnung_nr);
alter table rechnungen modify (rechnung_datum not null);
alter table rechnungen modify (kunde_nr not null);
alter table rechnungen add constraint kundenr_fk2
foreign key(kunde_nr) references kunden(kunde_nr) on
update cascade on delete set default;
alter table rechnungen modify (auftrag_nr not null);
alter table rechnungen add constraint auftragnr_fk2
foreign key(auftrag_nr) references auftraege(auftrag_nr) on
update cascade on delete set default;
alter table rechnungen add constraint datum_faellig_check
check(datum_faellig is null
or datum_faellig >= rechnung_datum);
alter table rechnungen add constraint mahnung1_check
check(datum_mahnung_1 is null or
datum_mahnung_1 >= nvl(datum_faellig, rechnung_datum));
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 11
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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
alter table bestellungen add constraint bestellnr_pk
primary key(bestell_nr);
alter table bestellungen modify (lieferanten_nr not null);
alter table bestellungen add constraint lieferantennr_fk
foreign key(lieferanten_nr) references
lieferanten(lieferanten_nr) on update cascade on delete cascade;
alter table bestellungen modify (lieferanten_name not null);
alter table bestellungen modify (bestelldatum not null);
alter table bestellungen modify (artikel_nr not null);
alter table bestellungen add constraint artikelnr
foreign key (artikel_nr) references artikel(artikel_nr) on
update cascade on delete set default;;
alter table bestellungen add constraint menge_check
check (menge > 0);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 12
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
2)
Ergänzen Sie die Hochschuldatenbank aus Kap. 5 um die
nachfolgend genannten Integritätsbedingungen
Student
Matrikelnr|Name|Fachbereich|Fachsemester|Geb_Dat|Geschlecht
Schlüssel: Matrikelnr, Geschlecht: w / m, Fachbereich: 1-9
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 13
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
create table student
(matrikel_nr
name
fachbereich
fachsemester
geb_dat
geschlecht
Datenbanken 2
char(12) primary key not null,
char(25) not null,
int not null check(fachbereich
between 1 and 9),
int not null check (fachsemester > 0),
date not null,
char(1) not null check (geschlecht =
'm' or geschlecht= 'w'));
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 14
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
Dozent
Name|Fachbereich|Lehrgebiet
Schlüssel: Name
create table dozent
(name
char(20)
not null primary key,
fachber
int not null check (fachber between 1 and 9),
lehrgebiet char(20) );
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 15
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
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
create table vorlesung
(vorl_nr
bezeichnung
hoersaal
plaetze
char(5)
not null ,
char(20) ,
char(8) ,
int check (plaetze >= 20 and plaetze <=
500),
beginn_h
char(5) ,
ende_h
char(5),
dozent
char(20) not null,
primary key (vorl_nr),
foreign key(dozent) references dozent(name) );
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 16
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
Hoert
Student|Vorlesung
Schlüssel: Student und Vorlesung, Fremdschlüssel: Student auf
Tabelle Student, Feld Matrikelnr, Vorlesung auf Tabelle
Vorlesung Feld Vorl_Nr
create table hoert
(student
char(12) not null,
vorlesung
char(5) not null,
primary key (student, vorlesung) ,
foreign key(student) references student(matrikel_nr),
foreign key(vorlesung) references vorlesung (vorl_nr) );
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 17
[..] 0. Datenintegrität constraints : Übung / Prakt.
constraints
Klausurergebnis
Student|Fach|Klausur_Datum|Ergebnis
Schlüssel: Student und Fach und Klausur_Datum,
Fremdschlüssel: Student auf Tabelle Student, Fach Matrikel_Nr
create table klausurergebnis
(student
char(12)
not null,
fach
char(25)
not null,
klausur_datum
date
not null,
ergebnis
numeric(3,1)
check ( ergebnis in (1.0, 1.3, 1.7, 2.0, 2.3, 2.7, 3.0, 3.3, 3.7,
4.0,5.0) ) ,
primary key (student, fach, klausur_datum) ,
foreign key(student) references student(matrikel_nr)
);
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 18
[..] 0. Datenintegrität Views : Übung / Prakt. constraints
3) Es sei folgende Integritätsregel gegeben:
„Für einen gesperrten Kunden darf kein Auftrag erfasst werden“
Setzen diese Regel durch einem prüfenden View um.
create view auftrag_integrität
as
select
*
from
auftraege
where (select gesperrt_kz from kunden k where .kunde_nr =
auftraege.kunde_nr) = 0
with check option
Datenbanken 2
- WS 2013/2014 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 19
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Bestellung
Artikel
Artikel_nr
Bestand
EK-Preis
Bestellmenge
Bestelldatum
Letzt_Lieferant_nr
Letzt_Lieferant_Name
Lieferant
Lieferanten_nr
Name
Bestellwert_Gesamt
diesem
alter
alter
alter
alter
alter
table
table
table
table
table
artikel add
artikel add
artikel add
artikel add
lieferanten
Datenbanken 2
char(27)
(Prim Key)
numeric(10,0)
numeric(10,2)
numeric(10,0)
(letzte bestellte Menge)
date
(letztes Bestelldatum)
int
(Nummer des letzten Lieferanten)
char(30) (Name des letzten Lieferanten, redundant)
int
char(30)
numeric(10,2)
(Prim Key)
Gesamtwert (Menge*EK-Preis) aller Bestellungen bei
Lieferanten
bestellmenge numeric(10,0);
bestelldatum date;
Letzt_Lieferant_nr int;
Letzt_Lieferant_Name char(30);
add Bestellwert_Gesamt numeric(10,2);
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 20
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Bestellung
Beispieltrigger Nachverarbeitung:
Bei der Eingabe von Tupeln in die Tabelle „Bestellung“ werden die in
Fettschrift gekennzeichneten Daten in den Tabellen „Artikel“ und
„Lieferant“ aktualisiert.
create table bestellungen
(
bestell_nr int,
lieferanten_nr int,
lieferanten_name varchar(30),
bestelldatum date,
artikel_nr varchar(27),
menge numeric(9,0)
);
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 21
Teil 1. Trigger PL/SQL: Übungen
Aufgabe: Bestellung
create or replace trigger bestellneu
after insert on bestellungen
for each row
declare preis float;
begin
update artikel
set bestellmenge = :new.menge,
bestelldatum=:new.bestelldatum,
letzt_lieferant_nr=:new.lieferant_nr,
letzt_lieferant_name = :new.lieferanten_name
where artikel_nr = :new.artikel_nr;
select einkaufspreis into preis from artikel where
artikel_nr=:new.artikel_nr;
update lieferanten
set bestellwert_gesamt = nvl(bestellwert_gesamt,0) + preis *
:new.menge
where lieferanten_nr = :new.lieferanten_nr;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 22
Teil 1. Datenintegrität Trigger : Übungen/Lösungen
Aufgabe: Familienstand
Die Relation „Student“ habe folgende Struktur:
create table student
(matrikel_nr
name
fachbereich
fachsemester
geb_dat
geschlecht
familienstand
char(12),
char(25),
number,
number,
date,
char(1),
char(1));
Implementieren Sie mit den geeigneten Mitteln folgende Integritätsregeln:
matrikel_nr : primärschlüssel
fachbereich : Wertebereich {1,2,3,4,5,9}
geschlecht
: Wertebereich {‚w‘ , ‚m‘}
familienstand : Wertebereich {‚l‘ , ‚v‘ , ‚g‘ , ‚w‘ }
Der Familienstand darf sich nur wie folgt ändern:
l (ledig) -> v (verheiratet)
v
-> g (geschieden) / w (verwitwet)
w,g
-> v
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 23
Teil 1. Datenintegrität Trigger : Übungen/Lösungen
Aufgabe: Familienstand
alter
alter
alter
alter
table
table
table
table
student
student
student
student
add
add
add
add
constraint
constraint
constraint
constraint
pimkey primary key(matrikel_nr);
fachb check(fachbereich in (1,2,3,4,5,9));
geschl check(geschlecht in ('m', 'w'));
famstand check(familienstand in ('l', 'v', 'g', 'w'));
create or replace trigger familienstand
after update of familienstand on student
/* oder before ? Performance , constraints Fehler nach before !!! */
for each row
begin
if :new.familienstand in ('g', 'w') and :old.familienstand not in ('v') then
raise_application_error(-20000, 'Aenderung in geschieden oder verwitwet nur von
verheiratet erlaubt');
rollback;
end if;
if :new.familienstand in ('l') then
raise_application_error(-20000, 'Aenderung in ledig nicht erlaubt');
rollback;
end if;
if :new.familienstand = :old.familienstand then
raise_application_error(-20000, 'Aenderung nicht erlaubt');
rollback;
end if;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 24
Teil 1. Datenintegrität Trigger :
Übungen/Lösungen
Aufgabe: Lagerplätze
Es sei folgende Integritätsregel gegeben:
„Zu jedem Artikel darf es nur maximal 5 Lagerplätze geben“
a) Diskutieren Sie, mit welchen Integritätsmechanismen diese
Regel implementiert werden kann oder nicht
b) Implementieren Sie die Regel
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 25
Teil 1. Datenintegrität Trigger :
Übungen/Lösungen
Aufgabe: Lagerplätze
Regel ist dynamisch (erfordert Zählen der Lagerplätze). Constraints
deshalb nicht möglich. Ein Trigger ist (immer) möglich. Before
Trigger, da Integrität. Erfordert Zugriff auf die dem Trigger
zugeordnete Tabelle, die ist gesperrt, daher Hilfstabelle und merken
der betroffenen Artikel. Verarbeitung der Hilfstabelle in After-TriggerBefehlsorientiert, dann ist die Tabelle frei.
Create table artikeltemp (artikel_nr varchar(27));
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 26
Teil 1. Datenintegrität Trigger :
Übungen/Lösungen
Aufgabe: Lagerplätze
create or replace Trigger beschraenkte_lagerplaetze
before insert on artikellager
for each row
begin
insert into artikel_temp values (:new.artikel_nr);
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 27
Teil 1. Datenintegrität Trigger :
Übungen/Lösungen
Aufgabe: Lagerplätze
Trigger zählt die eingefügten Artikel (in Hilfstabelle), die mehr als 5
Einträge in Artikellager haben. Ist diese Zahl > 0, wird die
Transaktion abgebrochen.
create or replace Trigger beschraenkte_lagerplaetze_kontrolle
after insert on artikellager
Declare anzahl int;
begin
Select count(*) into anzahl from hilfstabelle h where
(select count(*) from artikellager l
where h.artikel_nr = l.artikel_nr) > 5;
If anzahl > 0 then
raise_application_error(-20000, ‘Ein Artikel hat mehr als 5
Lagerplätze ‘);
end if;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 28
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Anzahl Lagerplätze, Lösung MS-SQL-Server
Nur befehlsorientierte After-Trigger definiert, aber Sammeln der vom Ereignis betroffenen Tupel in einer
Tabelle inserted / bzw. deleted. Die wird mit einem Cursor durchlaufen und die Triggerhandlung je Tupel
ausgeführt
CREATE TRIGGER [prueffuenfeintraege] ON dbo.artikellager
FOR insert AS
declare @artikel_nr char(27)
declare articurs Cursor FOR select artikel_nr from inserted
open articurs
fetch next from articurs into @artikel_nr
while @@fetch_status=0
begin
if (select count(*) from artikel_lager where
artikel_nr=@artikel_nr) >= 5
begin
raiserror()
rollback
end
fetch next from articurs into @artikel_nr
end
close artcurs
deallocate articurs
return
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 29
Teil 1. Trigger PL/SQL: Übungen
Aufgabe: Bundesliga
create table Spieler
( Nummer int primary key ,
Spielt_fuer_Ligamannschaft char(40) ,
Gehalt number(10,2))
create table Spieler_Position
( Person int references spieler(person),
Position varchar(40),
primary key (person, position) );
Integritätsregel:
„Einem Spieler, der für die Ligamannschaft ‚Bayern’ spielt, darf nicht zugleich in der
Position „Abwehr“ und „Angriff“ eingesetzt werden (Trigger auf Tabelle Spieler_Position).“
Realisieren Sie diese Regel in Oracle .
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 30
Teil 1. Trigger PL/SQL: Übungen
Aufgabe: Bundesliga - Lösung
Lösungsansatz:
Zeilenorientierter Trigger, da Kenntnis der Daten sinnvoll.
Aber: Zugriff auf Tabelle „Spieler_Position“ notwendig, die ist aber gesperrt.
Daher speichern der betroffenen Tupel (Key) in Hilfstabelle durch zeilenorientierten
Trigger und Prüfung der Regel in befehlsorientiertem after-Trigger (tabelle nicht mehr
gesperrt).
create table hilf_spieler (spieler int);
create trigger pruefe_nutzer_before before insert or update on
Spieler_Position
for each row
begin
insert into hilf_spieler values (:new.person);
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 31
Teil 1. Trigger PL/SQL: Übungen
Aufgabe: Bundesliga - Lösung
create trigger pruefe_nutzer_after after insert or update on
Spieler_Position
declare cnt int;
cursor hilf_cur is select spieler from hilf_spieler;
spieler_hilf int;
mannschaft char(40);
begin
open hilf_cur;
loop
fetch hilf_cur into spieler_hilf;
exit when hilf_cur %notfound;
select Spielt_fuer_Ligamannschaft into mannschaft from spieler
where person= spieler_hilf;
if
if (mannschaft=’Bayern’)
select nvl(count(*),0) into cnt from spieler_position where
person= spieler_hilf and position in (‚Abwehr’,’Angriff’);
if (cnt > 1)
raise_application_error(-20000,’Zu viele Positionen’);
end if;
end if;
end loop;
close hilf_cur;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 32
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Cursor / Stored Proc Bestellung
Schreiben Sie eine gespeicherte Prozedur mit folgender Funktionalität:
Durchlaufen Sie die Tabelle Artikel (cursor) . Für jeden aktiven Artikel, bei dem der Lagerbestand kleiner ist
als der Mindestbestand, wird ein Eintrag (insert) in die Tabelle Bestellung vorgenommen. Der Artikel wird
beim Hauptlieferanten in der im Feld „Bestellmenge“ angegebenen Menge bestellt. Bestelldatum ist das
Tagesdatum, Lieferdatum 14 Tage später. Die Prozedur soll den Bestellwert (Summe EK-Preis*Bestellmenge
über alle bestellte Artikel) als Output-Parameter zurückgeben.
Die Tabellenstrukturen seien wie folgt definiert:
Artikel
Artikel_nr
Aktiv
Bestand
Mindestbestand
Bestellmenge
Hauptlieferant
EKPreis
Lieferant
Lieferant_nr
Name
Bestellung
Bestellnr
Lieferant
Name
Bestelldatum
Lieferdatum
Artikel_nr
Menge
Datenbanken 2
char(20)
bit
int
int
int
int
number
(Prim Key)
int
char(30)
(Prim Key)
int
int
char(20)
date
date
char(20)
int
(primary key höchste bestehende Nummer um 1 erhöhen) )
(Nummer des Lieferanten (foreign key))
(Name des Lieferanten, redundante Speicherung)
(zu bestellende Menge)
(foreign key auf Lieferant)
(foreign key auf Artikel)
Anzahl der bestellten Artikel
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 33
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Cursor / Stored Proc Bestellung
create table artikel(
artikel_nr varchar(20),
aktiv number(1,0),
bestand number,
mindestbestand number,
bestellmenge number,
hauptlieferant number,
ekpreis number(19,4),
primary key(artikel_nr) );
create table lieferant(
lieferant_nr number,
name varchar(30),
primary key(lieferant_nr));
create table bestellung (
bestellnr number,
lieferant number,
name varchar(20),
bestelldatum date,
lieferdatum date,
artikel_nr varchar(20),
menge number,
primary key(bestellnr),
foreign key(lieferant) references lieferant(lieferant_nr),
foreign key(artikel_nr) references artikel(artikel_nr) );
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 34
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Cursor / Stored Proc Bestellung
/* Alternative Lösung unter Nutzung des Oracle-Konzeptes „Sequence“ zur Erzeugung einer Folge von
Bestellnummern*/
CREATE SEQUENCE bestellung_seq START WITH 1 INCREMENT BY 1 ;
CREATE OR REPLACE PROCEDURE "ARTIKELBESTELLUNG"
(bestellwert out number ) as
begin
declare artnr artikel.artikelnr%type;
bestmenge number;
preis number (10,2);
liefnr number;
name char(20);
bestnr int;
cursor artikelcursor is select artikelnr , bestellmenge, hauptlieferant,
ekpreis from artikel where aktiv=1 and bestand < mindestbestand;
begin
bestellwert:=0;
open artikelcursor;
fetch artikelcursor into artnr, bestmenge, liefnr, preis;
while artikelcursor%found loop
bestellwert:=bestellwert + bestmenge*preis;
select name into name from lieferant where lieferant_nr=liefnr;
/* Verwende die Sequenz */
insert into bestellung values( bestellung_seq.nextval , liefnr, name, current_date, current_date+14,
artnr, bestmenge)
fetch artikelcursor into artnr, bestmenge, liefnr, preis;
end loop;
close artikelcursor
end;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 35
Teil 1. Trigger PL/SQL: Übungen / Lösungen
Aufgabe: Cursor / Stored Proc Bestellung
CREATE OR REPLACE PROCEDURE "ARTIKELBESTELLUNG" (bestellwert out number )
as
begin
declare artnr artikel.artikelnr%type;
bestmenge number;
preis number (10,2);
liefnr number;
name char(20);
bestnr int;
cursor artikelcursor is select artikelnr , bestellmenge, hauptlieferant,
ekpreis from artikel where aktiv=1 and bestand < mindestbestand;
begin
bestellwert:=0;
open artikelcursor;
fetch artikelcursor into artnr, bestmenge, liefnr, preis;
while artikelcursor%found loop
bestellwert:=bestellwert + bestmenge*preis;
select name into name from lieferant where lieferant_nr=liefnr;
/* erhöhe bisherige höchste bestellnummer um 1, wird neue nummer */
select nvl(max(bestellnr),0)+1 into bestnr from bestellung;
insert into bestellung values(bestnr, liefnr, name, current_date, current_date+14, artnr, bestmenge)
/* Bestellnr ist nicht dabei, da identity field. Dürfen nicht in value aufgeführt werden */
fetch artikelcursor into artnr, bestmenge, liefnr, preis;
end loop;
close artikelcursor
end;
end;
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 36
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Komponisten
Setzen Sie folgenden Sachverhalt in ein E-R-Diagramm um (inklusive Angabe
der Attribute, Schlüssel, Type, Komplexitäten):
In einer zu modellierenden Umwelt gibt es Personen, die die Rollen von
Komponisten, Sängern, Orchesterdirigenten und Promotern spielen. Personen
haben eine eindeutige ID. Komponisten schreiben Musikstücke. Ein Stück ist
immer genau einem Komponisten zuzuordnen. Das Musikstück wurde in einem
bestimmten Jahr von seinem Komponisten geschrieben. Es hat einen
eindeutigen Titel sowie eine mehrwertige Beschreibung. Musikstücke lassen
sich u.a. in die Kategorien Klassik und Moderne aufteilen.
Klassische Musik wird für ein bestimmtes Instrument geschrieben. Moderne
unterteilt man in Vocal und Instrumental. Moderne Musikstücke wurden u.U.
mehrfach von unterschiedlichen Sängern (Vocal) bzw. Dirigenten
(Instrumental) auf Tonträger aufgenommen. Die Aufnahme hat eine eindeutige
ID, ein Aufnahmedatum und einen Produktionsort. Sänger haben immer genau
einen Promoter, der aber mehrere Sänger betreuen kann.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 37
Teil 2. Datenbankentwurf : Übungen / Lösungen
Person
Aufgabe: Komponisten
ID
is a
Komponist
Promoter
Dirigent
(0,n)
Sänger
(0,n)
(0,n)
(1,1)
schreibt
ID
Datum
betreut
macht
macht
Ort
(0,n)
(1,1)
Ort
(1,1)
Aufnahmen
Aufnahmen
Datum
ID
(1,1)
(1,1)
in
in
(0,n)
(0,n)
Klassik
vocal
Instrument
instrumental
is a
Moderne
is a
(1,1)
Musikstück
Titel
Datenbanken 2
Beschreibung
Jahr
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 38
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Stadtverwaltung
Setzen Sie folgende Beschreibung in ein E-R-Diagramm incl.
Komplexitäten um:
In einer Stadtverwaltung arbeiten Personen (eindeutige
Personalnummer, Name, Einstufung. Sie lassen sich in Arbeiter und
Beamte klassifizieren. Arbeiter erhalten ein Gehalt in einer
bestimmten Höhe, Beamte eine Vergütung. Sie haben zusätzlich eine
Diensteinstufung, die mehrere Werte annehmen kann . Beamte
können ernannt oder gewählt sein (politische Beamte). Jede Person
ist genau einem Dezernat zugeordnet. Dezernate haben eine
eindeutige Bezeichnung, jedem Dezernat sind mehrere
Aufgabengebiete zugeordnet, In einem Dezernat können viele
Personen arbeiten. Jedes Dezernat muss von genau einem politischen
Beamten (Dezernent) geleitet werden, ein Dezernent kann nur ein
Dezernat leiten.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 39
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Stadtverwaltung
Personalnr
Person
Name
(1,1)
Bezeichnung
(1,n)
Dezernat
(1,1)
arbeitet in
Einstufung
Aufgabengebiet
is a
Arbeiter
Gehalt
Beamte
is-a
ernannt
Datenbanken 2
Vergütung
leitet
Einstufung
gewählt
(0,1)
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 40
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Bundestag
Setzen Sie folgende Beschreibung in ein E-R-Diagramm incl. Komplexitäten
um:
Im Bundestag sitzen Abgeordnete aus Wahlkreisen. Sie haben einen Namen,
Geburtsdatum und sind durch die Wahlkreisnummer eindeutig identifiziert.
Ferner müssen sie genau einer Partei angehören. Parteien haben einen
eindeutigen Schlüssel (Bezeichnung) und eine Mitgliederzahl. Wahlkreise
haben eine eindeutige Nummer und eine Bezeichnung. Jeder Wahlkreis
entsendet genau einen Abgeordneten. Wahlkreise liegen in Städten, zu einer
Stadt können mehrere Wahlkreise gehören.
Städte haben eine eindeutige Bezeichnung und eine Einwohnerzahl.
Abgeordnete stimmen ( u.U. mehrfach) über Gesetzesvorlagen ab.
Gesetzesvorlagen haben eine eindeutige Identnummer und einen Inhalt. Das
Abstimmungsergebnis ist je Abgeordnetem mit dem Datum und dem
Abstimmungsverhalten (ja, nein, Enthaltung) zu speichern.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 41
Datenbankentwurf : Übungen / Lösungen
Name
Abgeord
n
1
zu
Nr
n
aus
Abstimm
ung
Verhalten
Partei
(0,n)
(1,1)
1 (1,1)
(0,n)
Mitglieder
Bez
Gebdat
1
(1,1)
Wahlkreis
Bez
(1,1)
Datum
Liegt
in
Aufgabe: Bundestag
m
(1,n)
(0,n)
Stadt
Gesetzesvorlage
Bez
Ident
Einwohnerzahl
Inhalt
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 42
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Verkaufsartikel
Setzen Sie folgende Beschreibung in ein E-R-Diagramm incl. Komplexitäten
um:
Eine Firma verkauft Artikel. Sie sind durch eine eindeutige Artikelnummer
gekennzeichnet. Ferner haben sie eine Bezeichnung und einen Preis. Einige
Artikel sind rabattfähig. Diese haben als weitere Attribute einen „maximalen
Rabatt“. Weiterhin gibt es Artikelrabatte. Sie haben Gültigkeitsbereiche
„gilt_von“ und „gilt_bis“ , „gilt_von“ kennzeichnet einen Rabatt eindeutig,
außerdem haben sie den Rabattwert als Attribut. Rabattfähige Artikel müssen
mindestens einen, sie können viele Artikelrabatte haben.
Jeder Artikelrabatt muss genau einem Artikel zugeordnet sein. Die Firma hat
Kunden (Kundennummer eindeutig und der Name). Kunden können aktiv oder
inaktiv sein. Inaktive Kunden haben das Datum als Attribut, an dem sie inaktiv
wurden. Einge aktive Kunden erhalten für ausgewählte, rabattfähige Artikel
einen Kunden_Sonderrabatt. Ein Artikel kann Sonderrabatte für verschiedene
Kunden haben, ebenso kann ein Kunde Sonderrabatte für mehrere Artikel
erhalten
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 43
Teil 2. Datenbankentwurf : Übungen / Lösungen
Nummer
Bez
Aufgabe: Verkaufsartikel
Kunden
KuNr
Artikel
Preis
Wert
Name
is a
is a
M
Maxrabatt
rabattfähig
1
n
Sonderrab
(0,n)
Preis
(1,n)
aktiv
(0,n)
seit
n
hat
(1,1)
Gilt von
Datenbanken 2
Inaktiv
Wert
Artikelrabatt
Gilt bis
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 44
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Fahrzeugpark
Setzen Sie folgende Beschreibung in ein E-R-Diagramm incl. Komplexitäten um:
In einem Unternehmen gibt es Fahrzeuge. Sie sind durch ihr Kennzeichen eindeutig
identifizierbar. Ferner haben sie eine Leistungsstärke, einen Fahrzeugtyp und eine
Erstzulassung. Weiterhin speichert man die für das Fahrtzeug erforderliche
Führerscheinklasse.
In dem Unternehmen sind Personen angestellt. Sie sind durch eine Personalnummer
eindeutig gekennzeichnet. Sie verfügen über eine Adresse (Strasse, PLZ, Ort) und ein
Eintrittsdatum.
Einige Angestellte sind Fahrer, sie haben eine oder mehrere Führerscheine verschiedener
Klassen. Je Fahrer werden die von ihm gefahrenen Stunden gespeichert. Jedem Fahrzeug
ist genau ein Fahrer zugeordnet, einem Fahrer können mehrere, es muss kein Fahrzeug
zugeordnet sein.
Andere Angestellte sind Monteure. Sie haben eine bestimmte Qualifikation. Monteure
können keine Fahrer sein.
Fahrzeuge werden von Monteuren einer Inspektion (Wartung) unterworfen. Ein Fahrzeug
kann beliebig oft gewartet werden. Diese Inspektion findet an einem bestimmten Tag statt
und hat ein Inspektionsergebnis.
Wenn ein Fahrer ein Fahrzeug benutzt, werden die Daten dieser Fahrt (Datum, Zielort,
sowie Ort und Uhrzeit von evtl. mehreren Zwischenstopps) gespeichert.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 45
Teil 2. Datenbankentwurf : Übungen / Lösungen
Kennzeichen
Leistungsstärke
Erstzulassung
Personalnummer
Strasse
Adresse
Plz
Aufgabe: Fahrzeugpark
Fahrzeug
Fahrzeugtyp
(0,n)
(0,n)
(1,1)
Führerscheinklasse
Person
Datum
Ergebnis
Ort
Eintrittsdatum
is a
Inspektion
fährt
(0,n)
(1,1)
Monteur
Ort
Tour/Fahrt
Zwischenstop
betreut
Qualifikation
(1,1)
Uhrzeit
Abfahrtszeit
Zielort
(0,n)
macht
(0,n)
Fahrer
Stunden
Datenbanken 2
Führerschein
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 46
Teil 2. Datenbankentwurf : Übungen / Lösungen
Kennzeichen
Leistungsstärke
Erstzulassung
Personalnummer
Strasse
Adresse
Plz
Aufgabe: Fahrzeugpark
Fahrzeug
Fahrzeugtyp
(0,n)
Person
(0,n)
Datum
(1,1)
Ergebnis
Führerscheinklasse
Ort
Eintrittsdatum
is a
Inspektion
Monteur
Ort
Tour/Fahrt
Zwischenstop
fährt
Qualifikation
(0,n)
Uhrzeit
Datum
(0,n)
Fahrer
Zielort
Stunden
Datenbanken 2
Führerschein
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 47
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation in Relationen
Überführen Sie die ER-Diagramme aus den bisherigen Aufgaben nach
den vorgegebenen Regeln in relationale Strukturen.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 48
Teil 2. Datenbankentwurf : Übungen / Lösungen
Teil I: Datenbankentwurf
Transformation: Komponisten
Person(PID)
Komponist(PersonID)
Dirigent(PersonID)
Saenger(PersonID, PromoterID)
Promoter(PersonID)
Musikstück(Titel, Jahr, Komponist_ID)
Musikstück_Beschreibung (Titel, Beschreibung)
Klassik(Titel, Instrument)
Moderne(Titel)
Vocal(ModerneID)
Instrumental(ModerneID)
InstrumentalAufnahmen(IID, Ort, Datum, DirigentID, InstrumentalID)
VocalAufnahmen(VID, Ort, Datum, SaengerID, VocalID)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 49
Teil 2. Datenbankentwurf : Übungen / Lösungen
Transformation: Stadtverwaltung
Person(ID, Name, Einstufung, ArbeitetinDezBezeichnung)
Arbeiter(PNR,Gehalt)
Beamter(PNR,Vergütung)
Beamter_Einstufung(BeamterPNR ,Diensteinstufung)
Gewählt(BeamterPNR)
Ernannt(BeamterNR)
Dezernat(Bezeichnung,LeiterGewähltBeamterPNR)
DezAufgabengebiet(DezBezeichnung ,Aufgabengebiet)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 50
Teil 2. Datenbankentwurf : Übungen / Lösungen
Transformation: Bundestag
Abgeordneter (Name, Gebdat, Wahlkreis_Nr, Partei_Bezeichnung)
Wahlkreis (Wahlkreis_Nr, Wahlkreis_Bezeichnung, Stadt_Bezeichnung)
Partei(Bezeichnung,Mitglieder)
Stadt(Bezeichnung,Einwohnerzahl)
Gesetzesvorlage(Identnummer,Inhalt)
Abstimmung(Wahlkreis_Nr_Abgeordneter, GesetzesvorlageIdentNr,
Verhalten, Datum)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 51
Teil 2. Datenbankentwurf : Übungen / Lösungen
Transformation: Verkaufsartikel
Artikel (Nr, Bez, Preis)
Rabattfähig (Artikel_NR, maxrabatt)
Kunden (KUNR, Name)
Aktiv (KUNR)
Inaktiv (KUNR, seit )
Sonderrabatt (RabattfaehigArtikelNR, AktivKUNR, Wert)
Artikelrabatt (RabattfaehigArtikelNR ,Gilt_von ,Gilt_bis,Wert)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 52
Teil 2. Datenbankentwurf : Übungen / Lösungen
Transformation: Fahrzeugpark
Person (PNR,Strasse,PLZ,Ort,Eintritt)
Fahrer (PersonPNR,Stunden)
Fahrer_FSKL (FahrerPNR,FSKL)
Monteur (PersonPNR ,Qualifikation )
Fahrzeug (KZ, PS, FSKL, Typ, Erstzu, FährtFahrerPNR)
Tour (TourNr, Fahrer_PNR , FahrzeugKZ, Datum, Zielort)
Stops (TourNr ,Zeit, Ort)
Inspiziert (MonteurPNR, FahrzeugKZ , Tag , Ergebnis)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 53
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 1
Überführen Sie folgendes Diagramm in eine relationale Struktur.
Berücksichtigen Sie die Tatsache, dass nur sehr wenige Elemente von
E1und E3 zueinander in der Beziehung R1 stehen
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 54
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 1
A1
A2
A3
R3
E1
m
n
A9
1
A5
E2
1
R1
A7
A4
n
R2
n
E3
A8
Datenbanken 2
A6
RE1(A1,A2,A3)
RR3(A1,A2,A4)
RR3a(A1,A2,A4,A9)
RR1(A1,A2,A7)
RE2(A4,A5)
RE3R2(A7,A8,A4)
RR2(A7,A6)
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 55
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 2
Überführen Sie dieses „Semantik“-lose E-R-Diagramm in eine
relationale Struktur unter Kennzeichnung von Schlüsseln und
Fremdschlüsseln. Gehen Sie davon aus, dass die Relationship R2 für
beide Rollen obligatorisch ist. R3 sei für die Rolle E3 obligatorisch, für
E2 optional.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 56
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 2
A1
A2
A3
A5
B1
m
E1
E2
1
n
R_R3B4 (A1,B4)
C3
C2
Datenbanken 2
R3
R_E1E3R2R3 (A1,A2,A3,C1,C2,B1,B2) C1: Unique
E3
C1
1
B4
1
B3
n
R1
R2
B2
R_E3C3 (A1,C3)
R_E2 (B1,B2,B3)
R_R1 (A1,B1,B2,A5)
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 57
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 3
Überführen Sie folgendes ER-Diagramm (ohne Semantik) gemäß den
in der Vorlesung genannten Regeln (ohne Effizienzüberlegungen) in
relationale Strukturen
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 58
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe: Transformation Semantikloses ER-Diagramm 3
A1
A2
E1
A3
m
A4
R1
R2
n
E3
Datenbanken 2
R_E1 (A1,A2,A3)
R_E3 (A10,A11,A1,A2,A4)
R_E2 (A4)
A11
A10
E2
1
1
n
n
R3
A6
R_E2_A6 (A4,A6)
R_R3 (A1,A2,A4)
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 59
Teil 2. Datenbankentwurf : Übungen / Lösungen
Beispiel Generalisierung mit Trigger
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 60
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe Normalisierung FH
Folgende relationale Strukturen seien entwickelt: (Schlüsselattribute jeweils
unterstrichen, Fremdschlüssel kursiv):
Student: MatrikelNr Name Strasse PLZ Ort Bundesland Nationalität Fachbereich
Dozent: Name Fachbereich
Vorlesung: VorlesungsNr Vorlesbezeichnung Fachbereichszuordnung DozentName
Klausur: Matrikel_Nr Name Fachbereich Vorlesung Dozentname Datum Ergebnis
Folgende Regeln gelten im Anwendungsbereich:
1) Eine Vorlesung wird von genau einem Dozenten gehalten
2) Dieser Dozent lässt auch die Klausur schreiben
3) Der Fachbereich des Dozenten stimmt mit der Fachbereichszuordnung einer Vorlesung
überein
4) Klausuren werden nur semesterweise gespeichert, pro Semester pro Vorlesung eine
Klausur
Welche Normalformen werden verletzt (Begründung) ?
Überführen Sie die Relationen in die dritte Normalform.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 61
Teil 2. Datenbankentwurf : Übungen / Lösungen
Student: MatrikelNr Name Strasse
PLZ
Ort
Bundesland Nationalität Fachbereich
3.NF verletzt: { PLZ } -> {Ort, Bundesland}
Student: MatrikelNr Name Strasse PLZ
PLZ_Bundesland: PLZ Ort Bundesland
Nationalität Fachbereich
Dozent: Name Fachbereich
o.k.
Vorlesung: VorlesungsNr
Vorlesbezeichnung Fachbereichszuordnung DozentName
3.NF verletzt: {Dozentname} -> {Fachbereichszuordnung}
Vorlesung: VorlesungsNr
Datenbanken 2
Vorlesbezeichnung DozentName
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 62
Teil 2. Datenbankentwurf : Übungen / Lösungen
Klausur: Matrikel_Nr Name Fachbereich Vorlesung Dozentname
Datum Ergebnis
2.NF : {Matrikel_nr} -> {Name, Fachbereich}
2.NF : {Dozentname} -> {Fachbereich}
2.NF : {Vorlesung} -> {Dozentname, Datum} (pro Vorlesung nur 1 Klausur=1 Datum)
(der Dozent, der die Vorlesung hält, prüft
auch)
Vorlesung: VorlesungsNr Vorlesbezeichnung DozentName Prüfungsdatum
Klausur: Matrikel_Nr Vorlesung Ergebnis
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 63
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe Normalisierung Musik
Folgende relationale Strukturen seien entwickelt: (Schlüsselattribute jeweils
unterstrichen, Fremdschlüssel kursiv):
Dirigent (PersonID Name Alter Nationalität Titel )
Opernhaus (Name Stadt Orchester_Bezeichnung )
Orchester ( OrchesterBezeichnung Mitgliederzahl Gründungsjahr Dirigent_Titel
DirigentID )
Oper ( Titel , Komponist Jahr_Erstaufführung )
Aufführung ( Opernhaus Orchester OperTitel Dirigent Spielzeit Anzahlaufführungen)
Regeln:
1) Es werden immer nur die Aufführungen einer Spielzeit pro Opernhaus gespeichert.
2) Innerhalb einer Spielzeit wechselt der Dirigent eines Orchesters nicht.
3) Innerhalb einer Spielzeit wechselt das Orchester eines Opernhauses nicht
Welche Normalformen werden verletzt (Begründung) ?
Überführen Sie die Relationen in die dritte Normalform.
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 64
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe Normalisierung Musik
Dirigent (PersonID Name Alter Nationalität Titel )
Ist in 3. NF
Orchester ( OrchesterBezeichnung Mitgliederzahl Gründungsjahr Dirigent_Titel DirigentID)
3.NF verletzt: {DirigentID } -> {Dirigent_Titel }
Orchester (OrchesterBezeichnung Mitgliederzahl Gründungsjahr DirigentID )
Opernhaus (Name Stadt Orchester_Bezeichnung )
Ist in 3. NF
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 65
Teil 2. Datenbankentwurf : Übungen / Lösungen
Aufgabe Normalisierung Musik
Oper ( Titel , Komponist
Ist in 3. NF
Jahr_Erstaufführung )
Aufführung ( Opernhaus Orchester OperTitel Dirigent Spielzeit Anzahlaufführungen)
2.NF verletzt: {Orchester } -> {Dirigent} (da kein Wechsel des Dirigenten in
Spielzeit)
2.NF verletzt: {Opernhaus } -> {Orchester} (da kein Wechsel des Dirigenten in
Spielzeit)
Aufführung ( Opernhaus OperTitel
Datenbanken 2
Spielzeit Anzahlaufführungen)
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 66
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Hash-Organisation (1)
Tragen Sie folgende Datensätze mit den entsprechenden
Schlüsselwerten in eine Hash-Organisation ein, die folgende
Eigenschaften hat:
7 Container, ein Container beinhaltet 2 Datensätze. Überlaufbereich
ebenfalls 2 Datensätze pro Container
5 – 15 – 25 – 35 – 7 – 12 – 19 – 22 – 84 – 10 – 21 – 22 – 23 – 30
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 67
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B0
35
7
U2
U1
19
B1
15
22
U3
U2
84
B2
23
30
U3
22
B3
10
U4
B4
25
U5
B5
5
12
U1
21
U6
B6
U7
Datenbehälter
Überlaufbehälter
Hash-Organisation (1)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 68
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Hash-Organisation (2)
Tragen Sie die Schlüsselwerte
1, 2 18, 20, 4, 5, 13, 24, 31, 6, 19, 46, 12, 17
in eine Hash-Organisation mit Überlaufbereich ein, die über n = 11
Behälter
verfügt (ein Datensatz je Behälter)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 69
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B0
U0
13
U1
U3
B1
1
U4
U1
24
B2
2
U0
U2
31
U3
46
B3
B4
4
U4
12
B5
5
U5
17
B6
6
B7
18
U7
B8
19
U8
B9
20
U5
U2
U6
U9
B10
U10
Datenbehälter
Überlaufbehälter
Hash-Organisation (2)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 70
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufbau ISAM-Datei
Eine ISAM-Datei wird i.A. auf Basis einer vorhandenen Datenmenge
sortiert aufgebaut und der Index angelegt. Danach wird die
Organisation unter Verwendung von Überlaufbehältern erweitert.
Sei eine ISAM-Organisation gegeben, in einen Datenblock passen 3
Datensätze, in einen Indexblock 2 Indexeinträge. Zu jedem
Datenblock gibt es einen Zeiger auf genau einen Überlaufblock.
Diese sind ggfs. untereinander verzeigert. Überlaufbehälter nehmen
2 Datensätze auf.
Verteilen Sie folgenden Datensätze (sortiert!) auf die Datenblöcke
und legen Sie den Index an. Lassen Sie je Datenblock einen
Datensatz als Reserve frei:
5 – 25 – 7 – 42 – 36 – 9 – 18 – 21 – 55 – 41 – 38 – 59 – 67
Wie sieht die Datendatei und die Indexdatei nach Eingabe dieser
Daten aus ?
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 71
Teil 3. Physikalische Organisation : Übungen /
Lösungen
I1
I2
I3
I4
7
B1
5
7
18
B2
9
18
25
B3
21
25
38
B4
36
38
42
B5
41
42
59
B6
55
59
67
B7
67
B8
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 72
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufbau ISAM-Datei
Fügen Sie nun folgende Werte ein und nehmen Sie die notwendigen
Änderungen in Datendatei, Indexdatei und Überlaufbereich vor:
8 – 34 – 74 – 43 – 58 – 60 – 1 – 2 – 3 – 22 – 78 – 6 – 85
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 73
Teil 3. Physikalische Organisation : Übungen /
Lösungen
I1
I2
I3
I4
7
B1
1
5
7
18
B2
8
9
18
25
B3
21
22
25
38
B4
34
36
38
42
B5
41
42
59
B6
43
55
59
74
B7
60
67
74
85
B8
78
85
Datenbanken 2
2
3
6
58
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 74
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Zugriffsbeschleunigung bei ISAM (1)
Sei eine geblockte Dateistruktur gegeben. Blockgröße sei 8000
Bytes, Recordlänge 500 Bytes. Die Datei habe 800000 Records. Es
sei ein sortierter, dichter Index angelegt, Schlüssellänge sei 15
Bytes, Verweislänge auf einen Record: 5 Bytes.
In wie vielen Block-Zugriffen wird im Mittel der Datensatz zu einem
Schlüssel gefunden, wenn die Suche im Index erfolgt.
Lösung:
Dichter Index: 1 Indexelement / Record -> 800000 Indexrecords.
Index-Recordlänge : 20 , Blocklänge 8000
-> 8000 / 20 = 400 Indexrecords pro Block
-> 800000 Recs / 400 = 2000 Indexblöcke
-> im Mittel 1000 + 1 = 1001 Index-Block-Zugriffe
(sequentiell 25 000 Zugriffe)
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 75
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Zugriffsbeschleunigung bei ISAM (2)
Sei eine geblockte Dateistruktur gegeben. Blockgröße sei 8000
Bytes, Recordlänge 500 Bytes. Die Datei habe 800000 Records. Es
sei ein sortierter, dünner Index angelegt, Schlüssellänge sei 15
Bytes, Verweislänge auf einen Record: 5 Bytes.
Lösung:
Blockgröße 8.000 : Recordlänge 500
800.000 Records : 16 Records /Block
Dünner Index: 50.000 Datenblöcke
-> 16 Datenrecords / Block
-> 50 000 Daten-Blöcke
-> 50.000 Indexeinträge (1
Eintrag / Block)
Indexlänge (15+5)=20 Bytes, Blockgröße 8.000 -> 400 Indexrecords /
Block
50.000 Indexeinträge / 400 pro Block
-> 125 Indexblöcke
Die Suche erfolgt im Mittel in 63 Index-Block-Zugriffen (Index) + 1
Zugriff Datenblock
Sequentiell 25 000 !
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 76
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufgabe B-Baum (1):
Wie sieht ein B-Baum der Ordnung 1
nach der Eingabe folgender Schlüsselwerte aus:
30– 29 – 27 – 18 – 15 – 20 – 9 – 10 –11 –12 –13
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 77
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 30, 29, 27, spalten
Aufgabe B-Baum (1):
27
29
30
einfügen 18, 15, spalten
29
15
18
27
30
einfügen 20, 9, 10, 2x spalten
9
Datenbanken 2
10 15
18
29
20
27
30
einfügen 11, 12, spalten
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 78
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 11, 12, spalten
Aufgabe B-Baum (1):
18
10
9
29
11
12 15
20
27
30
einfügen 13
18
10
9
Datenbanken 2
11
12
29
13
15
20
27
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 79
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufgabe B-Baum (2):
Wie sieht ein B-Baum der Ordnung 1
nach der Eingabe folgender Schlüsselwerte aus:
1 – 2 – 3 – 4 – 5 – 20 – 19 – 18 – 17 – 16 – 6 – 12 – 41 - 35
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 80
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 1, 2
Aufgabe B-Baum (2):
1
2
einfügen 3
1
2
3
einfügen 4, 5
2
1
Datenbanken 2
4
3
5
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 81
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 20, 19
Aufgabe B-Baum (2):
4
2
1
19
3
5
20
einfügen 18, 17
4
2
1
Datenbanken 2
17
3
5
19
18
20
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 82
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 16, 6
Aufgabe B-Baum (2):
4
17
2
1
6
3
19
5
16
18
20
einfügen 12, 41, 35
4
17
2
1
Datenbanken 2
3
6
5
19
12
16
18
35
20
41
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 83
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufgabe B-Baum (3):
Erzeugen Sie den B-Baum erster Ordnung, der sich durch die
Eingabe folgender Daten ergibt
10 – 15 – 12 – 5 – 11 – 3 – 1 – 25 – 2 – 13 – 14 – 17 – 19
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 84
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 10, 15, 12
Aufgabe B-Baum (3):
10 12 15
einfügen 5, 11
12
5
10 11
15
einfügen 3, 1
10
1
3
5
12
11
15
einfügen 25, 2, 13
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 85
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 25, 2, 13
Aufgabe B-Baum (3):
10
3
1
2
12
5
11
13 15 25
einfügen 14, 17, 19
10
3
1
Datenbanken 2
2
12 15 19
5
11
13
14
17 19 25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 86
Teil 3. Physikalische Organisation : Übungen /
Lösungen
Aufgabe B-Baum (3):
10
12
3
1
Datenbanken 2
2
15
5
19
11
13
14
17
25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 87
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B*-Baum (1)
Wie sieht ein B*-Baum der Ordnung 1 mit 2 Datensätzen pro
Blatt nach der Eingabe folgender Schlüsselwerte aus:
30– 29 – 27 – 18 – 15 – 20 – 9 – 10 –11 –12 –13
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 88
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 30, 29, 27
B*-Baum (1)
29
27 29
30
einfügen 18
27 29
18
Datenbanken 2
27
29
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 89
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 15
B*-Baum (1)
27
18
15 18
29
27
29
30
einfügen 20
27
18
15 18
Datenbanken 2
20 27
29
29
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 90
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 9
B*-Baum (1)
27
15
9
15
18
18
20 27
29
29
30
einfügen 10
15 27
10
9
10
Datenbanken 2
18
15
18
29
20 27
29
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 91
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 11
B*-Baum (1)
15 27
10
9
10
11
18
15
18
29
20 27
29
30
einfügen 12
15 27
10 12
9
10
11
12
Datenbanken 2
15
18
18
29
20
27
29
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 92
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 13
B*-Baum (1)
15 27
10 12
9
10
11
12
Datenbanken 2
13 15
18
18
29
20 27
29
30
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 93
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B*-Baum (2)
Wie sieht ein B*-Baum der Ordnung 1 mit 2 Datensätzen pro
Blatt nach der Eingabe folgender Schlüsselwerte aus:
1 – 2 – 3 – 4 – 5 – 20 – 19 –18 – 17 – 16 - 6 – 12 - 41 - 35
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 94
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 1,2
B*-Baum (2)
1
2
einfügen 3
2
1
2
3
einfügen 4
2
1
Datenbanken 2
2
3
4
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 95
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 5
B*-Baum (2)
1
2
2
4
3
4
5
einfügen 20
1
2
2
4
3
4
5
20
einfügen 19
4
2
1
Datenbanken 2
2
3
4
19
5
19
20
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 96
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 18
B*-Baum (2)
4
2
1
2
3
4
18 19
5
18
19
20
einfügen 17
4
18
17
2
1
Datenbanken 2
2
3
4
5
17
19
18
19
20
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 97
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 16
B*-Baum (2)
4
18
16
2
1
2
3
4
5
16
17
17
19
18
19
20
einfügen 6
16
4
18
6
2
1
2
Datenbanken 2
3
4
5
6
17
16
17
19
18
19
20
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 98
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B*-Baum (2)
einfügen 12
16
4
18
6
2
1
2
3
Datenbanken 2
4
5
6
17
12 16
17
19
18
19
20
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 99
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 41
B*-Baum (2)
16
4
18
2
1
2
3
6
4
5
17
6
12 16
17
19
18
19
20 41
einfügen 35
16
4
18
2
1
2
3
Datenbanken 2
4
6
5
6
17
12 16
17
19
18
19
35
20
35
41
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 100
Teil 3. Physikalische Organisation : Übungen /
Lösungen
B*-Baum (3)
Wie sieht ein B*-Baum der Ordnung 1 mit 2 Datensätzen pro
Blatt nach der Eingabe folgender Schlüsselwerte aus:
10 - 15 - 12 - 5 - 11 - 3 – 1 - 25 – 2 – 13 – 14 – 17 - 19
Datenbanken 2
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 101
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 10,15
B*-Baum (3)
10 15
einfügen 12
12
10 12
15
einfügen 5
10 12
5
Datenbanken 2
10
12
15
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 102
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 11
B*-Baum (3)
10 12
5
10
11
12
15
einfügen 3
10
5
3
Datenbanken 2
5
12
10
11
12
15
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 103
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 1
B*-Baum (3)
10
3
1
3
12
5
10
5
11
12
15
einfügen 25
10
3
1
3
Datenbanken 2
5
12
5
10
11
12
15 25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 104
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 2
B*-Baum (3)
3
2
1
2
10
5
3
12
5
10
11
12
15 25
einfügen 13
3
2
1
Datenbanken 2
2
10
12 15
5
3
5
10
11
12
13 15
25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 105
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 14
B*-Baum (3)
10
3
2
1
2
14
5
3
Datenbanken 2
5
12
10
11
12
15
13 14
15
25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 106
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 17
B*-Baum (3)
10
3
2
1
2
14
5
3
Datenbanken 2
5
12
10
11
12
15
13 14
15
17 25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 107
Teil 3. Physikalische Organisation : Übungen /
Lösungen
einfügen 19
B*-Baum (3)
10
3
2
1
2
14
5
3
Datenbanken 2
5
15 19
12
10
11
12
13 14
15
17 19
25
- SS 2010 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 108