Datenbanken

Aufgaben + Lösungen

Definitionseinführung
Domänendefinition lautet:
CREATE DOMAIN DOMÄNENNAME [AS]
DATENTYP
[DEFAULT]
[BEDINGUNG]
Beispiele:
CREATE DOMAIN MENGENEINHEIT_DOMAENE
AS CHARACTER(5) NUT NULL
CHECK (UPPER(VALUE) IN ('STÜCK','M','L','G','KG','T'))
oder
CREATE DOMAIN PREISEINHEIT_DOMAENE
AS INT DEFAULT 0 NUT NULL
CHECK (VALUE BETWEEN 0 AND 3)
verwendung
CREATE TABLE artikel (
ARTIKEL_NR VARCHAR(27) NOT NULL,
. . . .
PREISEINHEIT PREISEINHEIT_DOMAENE,
MENGENEINHEIT MENGENEINHEIT_DOMAENE,
. . . .)

Die Syntax zur Definition des Primärschlüssels lautet:
PRIMARY KEY [(ATTRIBUTLISTE)]

Die Syntax zur Definition des Sekundärschlüssels lautet:
UNIQUE [(ATTRIBUTLISTE)]

Die Syntax zur Definition des Fremdschlüssels lautet:
FOREIGEN KEY [(ATTRIBUTLISTE)]
oder
CREATE TABLE Artikellager(
Artikel_NR VARCHAR(27) FOREIGEN KEY REFERENCES Artikel,
...,...)
oder
FOREIGEN KEY (ARTIKEL_NR) REFERENCES ARTIKEL(Artikel_NR)
FOREIGEN KEY (ARTIKEL_NR) REFERENCES ARTIKEL

damit die schlüssel und die integrität nicht die DB komplett von beiden seiten sperrt
CONSTRAINT F2 FOREIGEN KEY (ARTIKEL_NR) REFERENCES ARTIKEL(Artikel_NR)
INITIALLY DEFERRED :: ist noch nicht implementiert

ALTER TABLE ARTIKELLAGER DESABLED | (MSSQL)NOCHECK CONSTRAINT name;

Um die integrität zu sichern oder durchgehen zu lassen:
ON DELETE SET DEFAULT
ON UPDATE CASCADE
-NO ACTION
-SET DEFAULT
-SET NULL
beispiel:
CREATE TABLE AUFTRÄGE(
AUFTRAGNR INT,
...
KUNDE_NR INT REFERENCES KUNDE(KUNDE_NR)
ON UPDATE CASCADE);

check Klausel
CREATE TABLE ARTIKEL(
 ARTIKEL_NR VARCHAR(27)
...,
WarenGruppe INT CHECK WarenGruppe BETWEEN 0 AND 999,
...)
oder
CREATE TABLE ARTIKEL(
 ARTIKEL_NR VARCHAR(27)
...,
WarenGruppe INT
CONSTRAINT WG CHECK(WarenGruppe BETWEEN 0 AND 999))

ALTER TABLE ARTIKEL DROP CONSTRAINT WG; LÖSCHT DEN CON-CHECK

SYNTAX VON TRIGGERN
CREATE OR REPLACE TRIGGER <NamedesTriggers>
BEFOR|AFTER --zeitpunkt
INSERT OR UPDATE [OF <Spalte1, Spalte2>] OR DELETE
ON <TabellenName>
[FOR EACH ROW] --Trigger-Typ
  [WHEN <Prädikat>]
<PL/SQL-BLOCK>

Welches Ereignis ausgelöst wurde?
INSERTING
DELETING
UPDATETING [(<SpaltenName>)]
Alte und Neue Tabellen sind
:OLD.<SpaltenName> -- UPDATE und DELETE
:NEW.<SpaltenName> --UPDATE und INSERT
Beispiel:
CREATE OR REPLACE TRIGGER
ARTIKEL_NR_NEU_CURSOR
AFTER UPDATE OF ARTIKEL_NR ON ARTIKEL
FOR EACH ROW
BEGIN
  UPDATE ARTIKELLAGER
    SET ARTIKEL_NR = :NEW:ARTIKEL.ARTIKEL_NR
    WHERE ARTIKEL_NR=:OLD.ARTIKEL_NR;
END

Ein Protocol Trigger mit einer Tabelle
CREATE TABLE ProtocolT(
ART CHAR(10),
USER CHAR(20),
ARTIKEL_NR_NEW VARCHAR(27),
ARTIKEL_NR_OLD VARCHAR(27),
ZEITPUNKT TIMESTAMP)

CREATE OR REPLACE TRIGGER ARTAENDERUNG
BEFOR DELETE OR INSERT OR UPDATE ON ARTIKEL
FOR EACH ROW
DECLARE
DESC VARCHAR(10);
OLDART VARCHAR(27);
NEWART VARCHAR(27);
BEGIN
 IF DELETING THEN
   :DESC:= 'DELETE';
   :OLDART:=:OLD.ARTIKEL_NR;
   :NEWART:=NULL;
 END IF
IF INSERTING THEN
  :DESC:='INSERT';
  :OLDART:=:OLD.ARTIKEL_NR;
  :NEWART:=:NEW.ARTIKEL_NR;
END IF
IF UPDATING THEN
  :DESC:='UPDATE'
  :OLDART:=:OLD.ARTIKEL_NR;
  :NEWART:=:NEW.ARTIKEL_NR;
END IF

  INSERT INTO ProtocolT
    (:DESC,USER, :OLDART, :NEWART, CURRENT_TIMESTAMP)
END

Domänen
Erzeugen Sie zur Musterdatenbank eine Domäne für Preise (Basis money, 9 stellig, 2 Nachkommastellen, >0, <1 Mio, keine NULL-Werte) und implementieren Sie dies mittels MS-SQL / ORACLE.

Ändern Sie die Datenbank dahingehend, dass die neu definierte Domäne bei VK-/EK-Preisen, Einstandswert, Verkaufswert benutzt werden. (auch im Skript eintragen)

Musterbeispiel Oracle:
create or replace type laenderkennzeichen as object (land varchar(3));
create table test(laender laenderkennzeichen not null);
insert into test values(laenderkennzeichen('D'));

create or replace type money as object (money number(10,2));
create table test(vkpreis money);
insert into test values(money(25.40));

Primärschlüssel / Sekundärschlüssel
Ändern Sie das Skript der Musterdatenbank dahingehend, dass für die Tabellen Artikel und Artikellager die in der Dokumentation angegebenen Primärschlüssel bzw. Sekundärschlüssel angelegt werden.
Legen Sie die für die übrigen Tabellen die definierten Primär- bzw. Sekundärschlüssel mittels der MS-SQL-Server / ORACLE Oberfläche an.
Erzeugen Sie ein neues Skript unter Verwendung der Schlüsseldefinitionen.

create table artikel(
artikel_nr varchar(27),
bezeichnung varchar(40),
lieferantennummer varchar(10),
warengruppe int,
verkaufspreis numeric,
einkaufspreis numeric,
lieferzeit int,
bestand_lager int,
bestand_minimum int,
jahresumsatz numeric,
vorjahresumsatz numeric,
auftragsbestand int,
primary key(artikel_nr));

create table artikellager(
artikel_nr varchar(27),
lager_nr int,
bestand_lager int,
primary key(artikel_nr, lager_nr),
foreign key(artikel_nr) references artikel(artikel_nr));

Fremdschlüssel
Ändern Sie das Skript der Musterdatenbank dahingehend, dass Sie die Fremdschlüssel für alle Tabellen anlegen.
alter table lieferanten add constraint lieferantenkey primary key(lieferantennummer);
alter table artikel add constraint artikelkey primary key(artikel_nr);
alter table artikellager add constraint lagerkey primary key(artikel_nr, lager_nr);
alter table kunden add constraint kundekey primary key(kunde_nr);
alter table auftraege add constraint auftragekey primary key(auftrag_nr);
alter table auftragspositionen add constraint positionenkey primary key(auftrag_nr, position_nr);
alter table rechnungen add constraint rechnungenkey primary key(rechnung_nr);
alter table rechnungen add constraint frechauft foreign key references auftraege (auftrag_nr);
alter table artikelsonderfarben add constraint farbenkey primary key(artikel_nr, farbe);

Übrige Integritätsbedingungen
(Wertebereiche, Default, NOT NULL, spaltenübergreifende Bedingungen)
Implementieren Sie die Wertebereichsbeschränkungen der Dokumentation für die Einschränkung „>=0“ über eine Regel und ordnen Sie diese den Attributen zu. (Mehrfachverwendung!)
alter table artikel add constraint cch1 check (warengruppe between 0 and 999);
alter table artikel add constraint cch2 check (verkaufspreis > 0);
alter table artikel add constraint cch3 check (einkaufspreis > 0 and einkaufspreis < verkaufspreis );
alter table artikel add constraint cch4 check (lieferzeit between 0 and 99);
alter table artikellager add constraint cch5 check (lager_nr > 0);
alter table artikellager add constraint cch6 check (bestand_lager >= 0);
alter table lieferanten add constraint cch7 check (lieferantennummer is not null);
alter table kunden add constraint cch8 check (upper(land) in ('D','CH','NL','A'));
alter table kunden add constraint cch9 check (kundengruppe between 0 and 200);
alter table kunden add constraint cch10 check (vertreter_nr > 0);
alter table kunden add constraint cch11 check (kreditlimit >=0);
alter table kunden add constraint cch12 check(saldo_offene_rechnungen<=kreditlimit);
alter table auftraege add constraint cch13 check (rabatt >=0);
alter table auftragspositionen add constraint cch14 check (position_nr > 0);
alter table auftragspositionen add constraint cch15 check (menge <> 0);
alter table auftragspositionen add constraint cch16 check (preis > 0);
alter table rechnungen add constraint cch17 check (datum_mahnung > datum_faellig);
alter table rechnungen add constraint cch18 check (zahlbetrag_gesamt >=0 );
alter table rechnungen add constraint cch19 check (zahlbetrag_gesamt >=0 );
alter table rechnungen add constraint cch20 check (datum_zahlung IS NULL or datum_zahlung > rechnung_datum);

Prozedurale Integrität / Trigger
Einfache Trigger
a) Machen Sie sich mit der Syntax des create trigger Befehls vertraut (SQL-Query-Analyser Hilfe, Transaction SQL, SQL+)
b) Machen Sie sich mit den „Sprachkonstrukten zur Ablaufsteuerung“ in Transaction SQL / PL-SQL vertraut.
c) Schreiben Sie einen Trigger, der bei einer Änderung der Artikelnummer in der Tabelle Artikel, diese auch in der Tabelle Artikellager ändert.

MS_SQL
Create trigger updateartikelnummer on artikel
For update As If update(artikel_nr)
Begin
 Declare @artikelneu varchar(27)
 Declare @artikelalt varchar(27)
 Select @artikelneu = artikel_nr from inserted
 Select @artikelalt = artikel_nr from deleted
  Update artikellager Set artikel_nr = @artikelneu
   Where artikel_nr = @artikelalt
End

ORACLE
create or replace trigger updartikel
after update on artikel
for each row
begin
 update artikellager set artikel_nr=:new.artikel_nr where artikel_nr=:old.artikel_nr;
end;

Trigger mit Cursor
Schreiben Sie obigen Trigger c) so um, dass er auch auf einen Update mehrerer Einträge in der Tabelle Artikel reagiert

MS_SQL
Create trigger updateartikelnummermitcursor on artikel

For update

As

If update(artikel_nr)

Begin

Declare updatecursorneu cursor for select artikel_nr from inserted

Declare updatecursornalt cursor for select artikel_nr from deleted

Declare @artikelneu varchar(27)

Declare @artikelalt varchar(27)

Fetch next from updatecursorneu into @artikelneu

Fetch next from updatecursoralt into @artikelalt

While @@fetch_status=0

begin

Update artikellager

Set artikel_nr = @artikelneu

Where artikel_nr = @artikelalt

Fetch next from updatecursorneu into @artikelneu

Fetch next from updatecursoralt into @artikelalt

End

Close updatecursorneu

Deallocate updatecursorneu

Close updatecursoralt

Deallocate updatecursoralt

End

ORACLE
Unter Oracle nicht notwendig

Geschäftsregeln

Schreiben Sie einen Trigger, der bei der Änderung des Bestandsfeldes eines Artikels in einem Lager den Gesamtbestand beim Artikel ändert. Es sollen sich auch mehrere Artikellager-Einträge gleichzeitig ändern können (cursor)
MS-SQL
Create trigger updatebestandmitcursor on artikellager

For update

As

If update(bestand_lager)

Begin

Declare updatecursorneu cursor for select artikel_nr, bestand_lager from inserted

Declare updatecursornalt cursor for select bestand_lager from deleted

Declare @artikel varchar(27)

Declare @bestandneu float

Declare @bestandalt float

Fetch next from updatecursorneu into @artikel , @bestandneu

Fetch next from updatecursoralt into @bestandalt

While @@fetch_status=0

begin

Update artikel

Set bestand_lager = bestand_lager + (@bestandneu - @bestandalt)

Where artikel_nr = @artikel

Fetch next from updatecursorneu into @artikel , @bestandneu

Fetch next from updatecursoralt into @bestandalt

End

Close updatecursorneu

Deallocate updatecursorneu

Close updatecursoralt

Deallocate updatecursoralt

End

ORACLE
create or replace trigger updbestand_lager
after update of bestand_lager on artikellager
 for each row
begin
 update artikel set bestand_lager=bestand_lager+(:new.bestand_lager-:old.bestand_lager)
 where artikel_nr=:new.artikel_nr;
end;

Schreiben Sie eine Prozedur, die die Artikeltabelle durchläuft und für jeden Artikel den Lagerbestand aus der Artikellagertabelle aktualisiert. Verwenden Sie hierfür keinen Cursor.
create procedure anpassenbestand as
 update artikel set bestand_lager = ( select sum(bestand_lager)
 from artikel_lager
 where artikel.artikel_nr = artikellager.artikel_nr)
go

ohne Cursor
create or replace procedure anpassenbestand
is begin
update artikel set bestand_lager=(select sum(bestand_lager)
from artikellager
where artikel.artikel_nr=artikellager.artikel_nr);
end;

mit Cursor
create or replace procedure anpassenbestand_cursor

is

cursor artikel is select artikel_nr, bestand_lager from artikellager;

artikel_rec artikel%rowtype;

bestand numeric;

artikelnr varchar(27);

begin

update artikel set bestand_lager =0;

open artikel;

loop

 fetch artikel into artikel_rec;

  exit when artikel%notfound;

 artikelnr:=artikel_rec.artikel_nr;

 bestand:=artikel_rec.bestand_lager;

 update artikel set bestand_lager=bestand_lager+bestand

    where artikel.artikel_nr=artikelnr;

end loop;

close artikel;

end;
Aufruf
call anpassenbestand();

call anpassenbestand_cursor();

Legen Sie eine einfache Tabelle Bestellungen an. Schreiben Sie eine Prozedur, die alle Artikel durchläuft und für die Artikel, bei denen der Lagerbestand den Mindestbestand unterschreitet, einen Eintrag in die Tabelle Bestellung vornimmt.
--anlegen Tabelle
create table bestellung(
lieferantennummer varchar(10),
artikelnummer varchar(27),
bestelldatum date default sysdate,
sollliefertermin date,
menge int,
preis number(10,2),
wert number(10,2));

--anlegen Tabelle
create or replace procedure ermittelnbestellvorschlag

is

cursor artikel_cur is select
lieferantennummer, artikel_nr, sysdate, lieferzeit, bestand_minimum, bestand_lager, einkaufspreis from artikel
where bestand_lager<bestand_minimum;
artikel_rec artikel_cur%rowtype;

lieferantennummer varchar(10);

artikelnummer varchar(27);

bestelldatum date;

sollliefertermin date;

menge int;

preis number(10,2);

wert number(10,2);

begin

delete from bestellung;

open artikel_cur;

loop

 fetch artikel_cur into artikel_rec ;

 exit when artikel_cur%notfound;

 lieferantennummer := artikel_rec.lieferantennummer;

 artikelnummer := artikel_rec.artikel_nr;

 bestelldatum := artikel_rec.sysdate;

 sollliefertermin := bestelldatum+artikel_rec.lieferzeit;

 menge := artikel_rec.bestand_minimum-artikel_rec.bestand_lager;

 preis := artikel_rec.einkaufspreis;

 wert :=artikel_rec.einkaufspreis*(artikel_rec.bestand_minimum-

 artikel_rec.bestand_lager);

 Insert into bestellung values(lieferantennummer, artikelnummer, bestelldatum,

 sollliefertermin, menge, preis, wert);

end loop;

close artikel_cur;

end;

Implementieren Sie folgende Geschäftsregel:
Beim Anlegen einer Auftragsposition wird
• der Positionswert berechnet
• der gesamte Auftragswert aktualisiert
• der Auftragsbestand des betreffenden Artikel erhöht

MS-SQL
Create trigger insertpositionmitcursor on auftragsposition

For insert

As

Declare insertcursor cursor for select auftrag_nr, position_nr, artikel_nr, menge from

inserted

Declare @artikel varchar(27)

Declare @auftrag int

Declare @position int

Declare @menge float

Fetch next from insertcursor into @auftrag, @position, @artikel , @menge

While @@fetch_status=0

begin

Update position

Set positionswert = menge * preis

Where auftrag_nr = @auftrag and position_nr = @position

Update auftrag

Set auftragswert = ( select sum(positionswert) from auftragspositionen where

auftrag_nr = @auftrag)

Where auftrag_nr = @auftrag

Update artikel

Set auftragsbestand = auftragsbestand + @menge

Where artikel_nr = @artikel

Fetch next from insertcursor into @auftrag, @position, @artikel , @menge

End

Close insertcursor

Deallocate insertcursor

End

ORACLE
create or replace procedure anpassenpositionswert
is begin
 update auftragspositionen set positionswert=preis*menge;
end;

create or replace trigger auftragswert
after insert on auftragspositionen
for each row
begin
 update auftraege set auftragswert =
             (select sum(positionswert)  from auftragspositionen  where auftragspositionen.auftrag_nr= :new.auftrag_nr)
 where auftraege.auftrag_nr=:new.auftrag_nr;
 update artikel set auftragsbestand=
             (select sum(menge) from auftragspositionen where auftragspositionen.artikel_nr= :new.artikel_nr)
 where artikel.artikel_nr=:new.artikel_nr;
end;

Trigger und Proceduren
Aufgabe Trigger
Schreiben Sie einen Trigger, der (nur!) auf die Änderung des Attributes „PW_Gesamt“ in einer Auftragsposition (Tabelle auftragspositionen) reagiert und das Attribut „Auftragswert_Gesamt“ beim zugehörigen Auftrag (Tabelle auftraege) um die Differenz zwischen Positionswert alt und Positionswert neu aktualisiert. Es können mehrere Positionen gleichzeitig geändert worden sein.

Die Tabellenstrukturen seien wie folgt definiert:

CREATE TRIGGER ArtikelPosAEN ON Auftragspositionen

FOR Update

AS

DECLARE @NEUAufNr int

DECLARE @NEUPosWert money

DECLARE @ALTPosWert money

If update(PW_Gesamt)

begin

 DECLARE Position_Cursor_NEU FOR CURSOR  Select Auftrag_Nr, PW_Gesamt From Inserted

 OPEN Position_CURSOR_NEU

 DECLARE Position_Cursor_ALT FOR CURSOR  Select Auftrag_Nr, PW_Gesamt From Deleted

 OPEN Position_CURSOR_ALT

 FETCH FROM Position_CURSOR_NEU INTO @NEUAufNr, @NEUPosWert

 FETCH FROM Position_CURSOR_ALT INTO @NEUAufNr, @ALTPosWert

 WHILE @@Fetch_status = 0

 Begin

   Update Auftrag SET Auftragswert_Gesamt = Auftragswert_Gesamt + ( @NEUPosWert - @ALTPosWert)
   WHERE Auftrag_Nr = @NEUAufNr

   FETCH FROM Position_CURSOR_NEU INTO @NEUAufNr, @NEUPosWert

   FETCH FROM Position_CURSOR_ALT INTO  @NEUAufNr, @ALTPosWert

 end

 close Position_CURSOR_NEU

 deallocate Position_CURSOR_NEU

 close Position_CURSOR_ALT

 deallocate Position_CURSOR_ALT

Aufgabe Trigger

Schreiben Sie einen Trigger, der bei einer Änderung der Artikelnummer in der Tabelle Artikel, diese auch in der Tabelle Artikellager ändert.
create or replace trigger updartikel
after update on artikel
for each row
  begin
    update artikellager set artikel_nr=:new.artikel_nr
    where artikel_nr=:old.artikel_nr;
end;

Aufgabe (45)

Schreiben Sie eine gespeicherte Prozedur mit folgender Funktionalität: Durchlaufen Sie die Tabelle Artikel. 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:

CREATE PROCEDURE Artikelbestellung

@Bestellwert money output

AS

Begin

DECLARE @ArtNR char(20)

DECLARE @BestMenge int

DECLARE @Preis money

DECLARE @LiefNr int

DECLARE @Name char(30)

Set @Bestellwert = 0

DECLARE Artikel_Cursor FOR CURSOR

               Select Artikel_nr, Bestellmenge, Hauptlieferant, EKPreis from Artikel
               where aktiv=1 and Bestand < Mindestbestand

FETCH FROM Artikel_CURSOR INTO @ArtNr, @BestMenge, @LiefNr, @Preis

WHILE @@Fetch_status = 0

Begin

Set @Bestellwert = @Bestellwert + @Bestmenge*@Preis

Set @Name = (select Name from Lieferant where Lieferant_nr = @LiefNr)

Insert into Bestellung values (@LiefNr, @Name, getdate(), dateadd(getdate(),14), @ArtNr, @Bestmenge)

FETCH FROM Artikel_CURSOR INTO @ArtNr, @BestMenge, @LiefNr, @Preis

end

          close Artikel_CURSOR

          deallocate Artikel_CURSOR

end

Diagramme

Setzen Sie folgenden Sachverhalt in ein E-R-Diagramm um
(inklusive Angabe der Attribute, Schlüssel, Type, Komplexitäten):

An einer Schule gibt es Lehrer und Schüler. Lehrer sind durch ihren Namen eindeutig identifiziert, Schüler durch ihren Namen und das Geburtsdatum. Weitere Attribute von Schülern sind das Geschlecht und die Adresse, die aus Strasse, PLZ und Ort besteht. Jeder Schüler geht in genau eine Klasse, die durch eine Klassennummer eindeutig identifiziert sind. Klassen haben einen eigenen Raum. In eine Klasse gehen viele Schüler. Lehrer unterrichten Schüler in Fächern. Ein Lehrer unterrichtet viele Schüler und ein Schüler kann von vielen Lehrer unterrichtet werden. Jede Klasse hat genau einen Lehrer in der Rolle des Klassenlehrers, ein Lehrer kann diese Funktion nur bei einer Klasse ausüben.

Aufgabe zum Model (17)
Entwickeln Sie zu folgendem Sachverhalt ein E-R-Diagramm: Projekte werden durch eine eindeutige Projektnummer und Start- / Endedatum qualifiziert. Sie laufen in Phasen ab, die je Projekt durchnumeriert werden und eine Bezeichnung haben. Jedes Projekt hat mindestens 3 Phasen. Zu jeder Phasen können (müssen nicht) beliebig viele Ergebnisberichte erstellt werden, die ein eindeutiges Datum und eine Beschreibung haben. Mitarbeiter werden durch ihren Namen und eine eindeutige Personalnummer qualifiziert. Sie können an beliebig vielen Projekten-Phasen eines oder verschiedener Projekte mitarbeiten, an einer Phase muss mindestens einer, maximal dürfen aber nur 5 Mitarbeiter arbeiten. Die Mitarbeit hat ein Start- und ein Endedatum.

R1: Typ 1:n, kpr(3,n)Kph(1:1) Berichtet: Typ 1:n, kph(0,n), kbe(1:1) Mitarb:m:n, kM(0,n) kp(1,5)

Aufgabe zum Model (20)
Entwickeln Sie zu folgendem Sachverhalt ein E-R-Diagramm (incl. Typen und Komplexitäten) : Ein Handballverein hat aktive und passive Mitglieder. Zu diesen wird eine Adresse bestehend aus Name, Strasse, PLZ und Ort gespeichert. Sie sind jeweils durch eine Nummer eindeutig gekennzeichnet. Alle Mitglieder haben 1 oder mehrere Bankverbindungen gekennzeichnet durch Bankleitzahl und Kontonummer. Aktive haben eine Spielklasse (Junioren, etc.). Eine Mannschaft besteht aus 4-10 Aktiven, ein Aktiver muss in keiner Mannschaft spielen, er kann in mehreren Mannschaften spielen. Eine Mannschaft hat eine eindeutige Bezeichnung und eine Ligenbezeichnung (Regionalliga, Verbandsliga etc.). Eine Mannschaft muss genau 1 Aktiven als Trainer haben, der darf kein Spieler sein. Ein Trainer hat ein Diplom („A-Schein“ etc.) . Ein Aktiver kann mehrere Mannschaften trainieren. Für das Training einer Mannschaft wird eine Aufwandsentschädigung bezahlt.

Setzen Sie folgendes formale E-R-Diagramm in eine relationale Struktur um und kennzeichnen Sie die Schlüsselattribute
(letztere sind im E-R-Diagramm unterstrichen )

Relation E1 : A1 A2 A3
Relation E2 : A7 A6 A5
Relation E3+R1: A8 A9 A1 A2 
Relation E3 : A8 A9 A10
Relation R2 :A1 A2 A7 A4

Überführen Sie folgendes Diagramm in eine relationale Struktur:

RE1: A1 A2 A3
RE3: A10 A1 A2 A6
RE2: A4 A5 A10 A9
RR2: A4 A10 A8

Überführen Sie folgendes Diagramm in eine relationale Struktur:

B-Baum
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

Datengröße berechnen
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.
Dichter Index: 1 Indexelement / Record -> 800000 Indexrecords.
Index-Recordlänge : 20
Blocklänge 8000 -> 8000 / 20 = 400
Indexrecords pro Block -> 800000 / 400 = 2000 Indexblöcke -> im Mittel 1000 + 1 = 1001 Zugriffe

( Indexrecords/Blocklänge/2)+1=Zugriffe

Schedule
Schreiben Sie ein Schedule-Beispiel (ohne Sperren!) für einen Lost Update
r1(x) r2(x) w1(x) w2(x)

Seien folgende Transaktionen gegeben:
t1 = r1 (x) w1(x) r1(y) w1(y)
t2 = r2 (z) w2 (z) r2 (x) w2 (x)
Schreiben Sie einen gültigen Schedule ohne Sperren für diese Transaktionen, der nicht seriell ist.
 r1(x) r2(z) w2(z) w1(x) r1(y) w1(y) c1 r2(x) w2(x) c2

Wie lautet das statische 2-Phasen-Sperrprotokoll?
Alles zu beginn sperren, verarbeiten, dann freigeben

Warum entspricht folgendes Schedule nicht dem 2-Phasen-Sperr-Protokoll ?
rl1x wl2y r1x r2y ru1x w2y wu2y rl1x r1x ru1x c1 c2
Die Sperre rl1x ist im Schedule 2x vorhanden.

Allgemein
Wie nennt man eine Datenbankstruktur in gespeicherter Form
Schema

Welches Instrument zur Integritätswahrung ist mächtiger: Trigger / constraints
Trigger

Wieviele member-Records enthält eine set-occurence
0-n

Was bedeutet Atomarität bei Transaktionen
Eine Transaktion ist unteilbar, sie wird ganz oder gar nicht durchgeführt

Welche Eigenschaften kann eine Spezialisierung haben
Partiell/Vollständig, Disjunkt/Nicht disjunkt

Was ist die externe Ebene einer Datenbankstruktur.
Sichten der Anwendungsprogramme

Welche Integritäten lassen sich nicht mit constraints verwalten
Tabellenübergreifende

Was ist ein set-type
Eine Relation im Netzwerkmodell

Wie lautet das statische 2-Phasen-Sperrprotokoll
Alles zu beginn sperren, verarbeiten, dann freigeben

Wie stellt man im ER-Modell Spezialisierungen dar.
Über is-a Relationships

Nennen Sie die zweite Normalform (Definition)
Alle nicht zu den Schlüsselattributen einer Relation gehörigen Attribute sind nur vom Gesamtschlüssel der Relation abhängig sind und nicht schon bereits von einem Teilschlüssel

Nennen Sie die dritte Normalform (Definition).
Die dritte Normalform behandelt die funktionale Abhängigkeit A -> B, wobei A nicht Obermenge eines Schlüssels ist und B keine Schlüsselattribute enthält.

Was ist das Grundziel der Normierung ?
Redundanzvermeidung

Geben Sie ein Beispiel für eine Relation, die nicht in 3. Normalform ist
Auftrag ( auftrag_nr, Datum, kunde_nr, Kunde_name, ...),
Kunde_Name ist von Kunde_nr funktional abhängig. Kunde_nr gehört nicht zum Schlüssel

Was ist ein schwacher Entitätstyp im ER-Modell
Ein Entitätstyp, der ohne einen übergeordneten Typ, zu dem eine Relationship besteht, nicht existieren kann. Zu jeder Entität dieses Typs muss es ein entity in einem übergeordneten Typ geben , zu dem der schwache in einer Beziehung steht

Geben Sie ein Beispiel für eine optionale, eindeutige Beziehung
EHE: Mann Frau

Geben Sie ein Beispiel für eine Aggregation
Stückliste eines Computers: Computer besteht aus Teilen. Die Teile-Beziehung ist eine Aggregation.

Mit welchem Symbol bezeichnet man obligatorische, mehrdeutige Beziehungen nach Zehnder

Was versteht man unter Konsolidierung ?
Vereinigung mehrerer Teil-E-R-Diagramme zu einem großen Diagramm

Wie werden mehrwertige Attribute des E-R-Modells in das Relationenmodell übertragen?
 Eigene Relation

Geben Sie ein Beispiel für eine obligatorische, eindeutige Beziehung
Rolle Kinde in Vater-Kind Relationship

Deklarative Integritäten sind?
Primärschlüssel
Sekunderschlüssel
Fremdschlüssel mit Regeln für die Bewahrung der referentiellen Integrität
Wertebereichsbeschränkungen für Attribute (Spalten)
Zulässigkeit von NULL-Marken
Vorgabe von Default-Werten
Spaltenübergreifende Integritätsbedingungen
Tabellenübergreifende Integritätsbedingungen

Wofür braucht man Integritätssicherung?
TRIGGER braucht man, wenn man Integritäts-Bedingungen haben will die Tabellen übergreifend reagieren.

Was ist ein schwacher Entitätstyp im ER-Modell
Ein Entitätstyp, der ohne einen übergeordneten Typ, zu dem eine Relationship besteht, nicht existieren kann. Zu jeder Entität dieses Typs muss es ein entity in einem übergeordneten Typ geben , zu dem der schwache in einer Beziehung steht

Geben Sie ein Beispiel für eine optionale, eindeutige Beziehung
EHE: Mann Frau

Geben Sie ein Beispiel für eine optionale, nicht eindeutige Beziehung
Beziehung Person-Person: Vater Sohn

Geben Sie ein Beispiel für eine obligatorisch, eindeutige Beziehung
Beziehung Person-Person, Kind

Geben Sie ein Beispiel für eine obligatorisch, nicht eindeutige Beziehung
Beziehung Buch-Autor: „geschrieben von“

Geben Sie ein Beispiel für eine Aggregation
Stückliste eines Computers: Computer besteht aus Teilen. Die Teile-Beziehung ist eine Aggregation.

Nennen Sie die Phasen des Datenbankentwurfs.
Anforderungsanalyse
Konzeptioneller Entwurf
Logischer Entwurf
Implementierungsentwurf
Pysikalischerentwurf

Mit welchem Symbol bezeichnet man obligatorische, mehrdeutige Beziehungen nach Zehnder

Komplettaufgabe

Folgende Entity-Sets seien für den Fuhrpark eines kleinen Unternehmens definiert:

Ferner gelten folgenden Zusammenhänge:

Die Führerscheinklasse beim Fahrzeug bestimmt, welchen Führerschein der Fahrer dieses Fahrzeuges haben muss. Das Kennzeichen ist eindeutig.

Die Fahrernummer ist eindeutig. Ein Fahrer kann Führerscheine verschiedener Klassen besitzen.

Jedem Fahrzeug muss genau ein verantwortlicher Fahrer zugeordnet sein. Einem Fahrer muss kein Fahrzeug in diesem Sinne zugeordnet sein, maximal aber eines.

Wenn ein Fahrer ein Fahrzeug benutzt, wird diese Fahrt mit folgenden Daten gespeichert: Fahrer, Fahrzeug, Datum, Abfahrtzeit, Rückkehrzeit, gefahrene km. Jeder Fahrer kann ein Fahrzeug benutzen, wenn er die passende Führer-scheinklasse hat.

Benutzen Sie zur Abbildung von Fahrten in Aufgabe 1 keinen Entity-Set !

Folgende Integritäten gelten:

Fahrzeuge: Das Kennzeichen hat den Aufbau "HA - .......", es muss einen Wert haben. Die PS-Zahl ist größer als 200, Typ kann sein "LKW" oder PKW". Alle Datumfelder haben das Tagesdatum als Default Wert. Das Datum der letzten Fahrt muss größer sein als das Datum der Erstzulassung und entspricht dem Datum des letzten Eintrages in der Tabelle Fahrt für dieses Fahrzeug. Das Attribut "Summe_km" enthält die Summe der gefahrenen km und entspricht der Summe der km aller Fahrten für dieses Fahrzeug.

Fahrt: Datum hat Tagesdatum als default und ist größer als Erstzulassung, Rückkehrzeit ist größer als Abfahrtszeit, km ist größer 0, alle Attribute müssen einen Wert haben.

1) Erweitern Sie die aufgeführten Entity-Sets unter Berücksichtigung der genannten Zusammenhänge und Integritäten zu einem vollständigen ER-Diagramm (mit Attributen !) und geben Sie die Komplexität der Relationships an. Kennzeichnen Sie die Schlüssel/Fremdschlüssel-Attribute

2) Übertragen Sie das Diagramm in Strukturen des Relationen-Modells unter Kennzeichnung der Schlüssel und Fremdschlüssel (3. Normalform!)

3) Schreiben Sie die create-table Deklaration incl. constraints für die Relationen Fahrzeug und Fahrt

Create table Fahrzeug

(kennz varchar2 const f1 check kennz like “HA -%” primary key,

ps number const f2 check ps >200, type char(10) const f3 check type in (“LKW”,”PKW”), fklasse number, Erstzul date const f4 default sysdate(), Lefahrt Date const f5 default sysdate() check lefahrt > Erstzul, summe_km number, Hauptfhr number check f6 references Fahrer(Fnr) )

Create table Fahrer(

Fnr number const f7 primary key,
Name varchar const f8 not null,
geschlecht char(1),
Gebdat date)

Create table fklasse(

Fnr number const f8 references Fahrer(Fnr) not null,
wert number,
const f9 (Fnr,Wert) primary key  )

Create table Fahrt (

Fahrer number const f10 references Fahrer (fnr) not null,
fzeug varchar2 const f11 references fahrzeug (Kennz) not null,
datum date const f12 default sysdate() not null,
abfahrt “time” const f13 not null,
rueckkehr “time” const f14 not null check rueckkehr > abfahrt,
km number const f15 not null check km >0 ,
const f16 (fzeug,datum,abfaht) primary key)

4) Sichern Sie durch einen Trigger ab, dass bei der Eingabe einer Fahrt alle Bedingungen geprüft werden und alle Integritäten gewahrt bleiben
 

Create or replace trigger insfahrt

After insert on fahrt

For each row

Begin

Declare klatype fahrzeug.fklasse%type

/* Prüfen erstzulassungsdatum fahrzeug

if :new.datum < (select erstzul from fahrzeug where :new.fzeug=fahrzeug.kennz)
then raise_application_error (-20001,“Fahrtdatum < Erstzulassung“)
end if

/* passt führerscheinklasse

/* klasse für fahrzeug ermitteln

klatype=(select fklasse from fahrzeug where :new.fzeug=fahrzeug.kennz)

Hat Fahrer diese Klasse

if klatype not in (select wert from fklasse where :new.fahrer=fklasse.fnr)

then raise_application_error(-20002,”Fahrer hat nicht die Fklasse”)
end if

/* update Fahrzeug

update fahrzeug

set summe_km=summe_km+:new.km,

    lefahrt=:new.datum

where fahrzeug.kennz=:new.fzeug

end