Übungsaufgaben

0. Datenintegrität Constraints: Übung / Praktikum
constraints
1) Implementieren Sie folgende Constraints auf der Muster-DB:
Legen Sie dazu die DB neu an (nur create table). Nach
Implementierung der Constraints spielen Sie bitte die Daten
wieder ein. Sie werden einige Constraint-Verletzungen bemerken.
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 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 1
[..] 0. Datenintegrität Constraints: Übung / Praktikum
constraints
Relation: ArtikelLager
Artikel_Nr prim key, foreign key (Artikel)
Lager_Nr
prim key, Wert >0
Bestand_Lager
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 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 2
[..] 0. Datenintegrität Constraints: Übung / Praktikum
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
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 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 3
[..] 0. Datenintegrität Constraints: Übung / Praktikum
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)
NULL oder > Datum_Mahnung_1 (Wert nur wenn Datum_Mahnung_1 IS NOT
Zahldatum NULL oder > Rechnung_Datum
Bezahlt Wert 0,1
Relation: Lieferant
Lieferanten_nr primary key
Name not null
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 4
[..] 0. Datenintegrität Constraints: Übung / Praktikum
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
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 5
[..] 0. Datenintegrität Constraints: Übung / Praktikum
constraints Sie folgende Constraints auf der Hochschul-DB.
2) Implementieren
Student
Matrikelnr Name Fachbereich Fachsemester Geb_Dat Geschlecht
Schlüssel: Matrikelnr, Geschlecht: w / m, Fachbereich: 1-9
Dozent
Name Fachbereich Lehrgebiet
Schlüssel: Name
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
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 6
[..] 0. Datenintegrität Constraints: Übung / Praktikum
constraints
hoert
Student
Vorlesung
Schlüssel: Student und Vorlesung, Fremdschlüssel:
Student auf Tabelle Student, Feld Matrikelnr, Vorlesung auf
Tabelle Vorlesung Feld Vorl_Nr
Klausurergebnis
Student
Fach
Klausur_Datum
Ergebnis
Schlüssel: Student und Fach und Klausur_Datum,
Fremdschlüssel: Student auf Tabelle Student, Fach
Matrikel_Nr
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 7
[..] 0. Datenintegrität Constraints: Übung / Praktikum
constraints
3) Es sei folgende Integritätsregel gegeben: „Für einen gesperrten
Kunden darf kein Auftrag erfasst werden“. Setzen Sie diese Regel
durch einen prüfenden View um.
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 8
[..] 0. Datenintegrität Constraints: Übung / Praktikum
constraints
4)
Seien die folgenden Daten einer Vereinsanwendung gegeben. Implementieren Sie die genannten
Integritäten (alter table add constraint ...) (kein create table schreiben, alter table nur 1 x pro Relation
ausschreiben)
Mitglied
Nummer
Name
date
primärschlüssel
nicht leer
Ø
01.01.1940
<= 31.12.1994
date
Ø
KrankenVSNR
Monatsbeitrag
number
number
eindeutig
Zwischen 50 und 100 €
Beitragszahlung
Mitgliedsnummer
Relative Zahlnummer
Zahldatum
BLZ
Kontonummer
number
number
date
number
number
Fremdschlüssel Mitglied
Nicht leer
Nicht leer
Geburtsdatum
Eintrittsdatum
number
char(20)
Geburtsdatum
Primärschlüssel: (Mitgliedsnummer + Relative Zahlnummer)
Eindeutig: (BLZ + Kontonummer)
Mitgliedsnummer ändern, wenn in „Mitglied“ geändert wird.
Mitgliedsnummer auf NULL setzen, wenn Mitglied gelöscht wird.
Datenbanken 2
- WS 2015/2016 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 9
Teil 1. Trigger PL/SQL: Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 10
Teil 1. Trigger PL/SQL: Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 11
Teil 1. Trigger PL/SQL: Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 12
Teil 1. Trigger PL/SQL: Übungen
Aufgabe: Anzahl Lagerplätze
Integritätsregel:
„Zu einem Artikel darf es nicht mehr als 5 Lagerplätze geben!“
Realisieren Sie diese Regel in Oracle bzw. MS-SQL-Server.
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 13
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 14
Teil 1. Trigger PL/SQL: Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 15
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
Datenbanken 2
(nach Sander)
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 16
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
Datenbanken 2
(nach Sander)
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 17
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
Datenbanken 2
(nach Sander)
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 18
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
Datenbanken 2
(nach Sander)
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 19
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
Datenbanken 2
(nach Sander)
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 20
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
(nach Sander)
is-a : partiell, disjunkt
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 21
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
(nach Sander)
§ Ein Assistent arbeitet für einen oder mehrere Professor(en)
§ Für einen Professor arbeiten beliebig viele Assistenten
(1,n)
(0,n)
is-a : partiell, disjunkt
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 22
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule
(nach Sander)
§ Ein Professor hält „beliebig viele“
Vorlesungen
(1,1)
§ Vorlesungen werden von genau einem
Professor gehalten
(1,n)
(0,n)
(0,n)
is-a : partiell, disjunkt
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 23
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule (nach Sander)
(0,n)
(0,n)
(1,1)
(0,n)
(1,n)
(0,n)
is-a : partiell, disjunkt
Datenbanken 2
§
Ein Student hört „beliebig viele“
Vorlesungen
§
In Vorlesungen sitzen „beliebig viele“
Studenten
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 24
Teil 2. Datenbankentwurf : Übungen
Beispiel-Modellierung Hochschule (nach Sander)
(0,n)
(0,n)
(0,n)
(1,1)
(0,n)
(0,n)
(1,1)
(1,n)
§ Professoren prüfen
Studenten über
Vorlesungen
§ Je Prüfung wird eine
Note als Eigenschaft
zugewiesen
Datenbanken 2
(0,n)
is-a : partiell, disjunkt
§ Ein Student wird in beliebig vielen
Vorlesungen geprüft
§ Zu jedem Paar aus Studenten und
Vorlesungen gibt es genau einen
Professor, der prüft
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 25
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 26
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 27
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 28
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 29
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 30
Teil 2. Datenbankentwurf : Übungen
Aufgabe: Transformation in Relationen
Überführen Sie die ER-Diagramme aus den bisherigen Aufgaben nach
den vorgegebenen Regeln in relationale Strukturen.
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 31
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 32
Teil 2. Datenbankentwurf : Übungen
Aufgabe: Transformation Semantikloses ER-Diagramm 1
A1
A2
A3
A4
R3
E1
m
A9
1
E2
n
1
R1
R2
A7
n
A5
A6
n
E3
A8
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 33
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 34
Teil 2. Datenbankentwurf : Übungen
Aufgabe: Transformation Semantikloses ER-Diagramm 2
A1
A2
A3
A5
m
E1
E2
R3
B4
1
C1
B3
1
R2
E3
B2
n
R1
1
B1
n
C3
C2
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 35
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 36
Teil 2. Datenbankentwurf : Übungen
Aufgabe: Transformation Semantikloses ER-Diagramm 3
A1
A2
E1
A3
A4
m
R3
1
n
A6
E2
1
R1
n
n
R2
E3
A11
A10
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 37
Teil 2. Datenbankentwurf : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 38
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 39
Teil 2. Datenbankentwurf : Übungen
Implementierung von is-a Beziehungen im klassischen
Relationenmodell
Die Aufteilung einer Oberklasse in Unterklassen kann:
§ Partiell oder total
§ Disjunkt bzw. nicht disjunkt sein
Beschränken wir die Aufteilung auf zwei Subklassen:
Oberklasse
Unterklasse 1
Unterklasse 2
Datenbanken 2
Gehen wir davon aus, dass in einer relationalen
Implementierung die Attribute der Oberklasse in
den Relationen zu den Unterklassen wiederholt
werden. Diskutieren Sie die Integritätsprüfungen,
welche Sie bei allen DML-Operationen auf
Oberklassen-Relation oder Unterklassenrelation
zu beachten sind. Machen Sie Vorschläge zur
Implementierung der Einfügeoperationen.
Bereiten Sie die Realisierung der Integritäten
schablonenhaft vor.
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 40
Teil 3 Physikalische Organisation : Übungen
Hash-Organisation
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 41
Teil 3 Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 42
Teil 3 Physikalische Organisation : Übungen
[..] Aufbau ISAM-Datei
579
18 21 25
36 38 41
42 55 59
67
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 43
Teil 3 Physikalische Organisation : Übungen
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, dünner Index angelegt, Schlüssellänge sei 15
Bytes, Verweislänge auf einen Record: 5 Bytes.
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 44
Teil 3 Physikalische Organisation : Übungen
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.
In wie vielen Block-Zugriffen wird im Mittel der Datensatz zu einem
Schlüssel gefunden, wenn die Suche im Index erfolgt.
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 45
Teil 3. Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 46
Teil 3. Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 47
Teil 3. Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 48
Teil 3 Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 49
Teil 3 Physikalische Organisation : Übungen
B*-Baum (1)
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 50
Teil 3 Physikalische Organisation : Übungen
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 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 51
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Bank 1
In einer Bank-Anwendung benötigt man folgende 2 Objektklassen:
Kunden: Ein Kunde hat eine Kundennummer und einen Namen
(varchar(20)). Zu einem Kunden werden maximal 3 Kreditauskünfte
gespeichert, die aus einem Datum und dem Auskunfttext
(varchar(40)) bestehen. Ein Kunde hat beliebig viele Konten
(Kontonummer (integer), Kontostand(number(12,2) ).
Bankmitarbeiter: Eine Bank hat Mitarbeiter mit einer Personalnummer
(integer) und einem Namen (varchar(20)). Jedem Mitarbeiter sind
beliebig viele Kunden zugeordnet .
Entwickeln Sie hierzu die objekt-relationalen Typen und Tabellen
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 52
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Hochschule
An einer Hochschule arbeiten Dozenten, die eine eindeutige Nummer und
eine Namen haben und mehrere (array-wertig!) komplexe
Kommunikationsverbindungen ( (Art, Wert) z.B. (Mail, @aaa) oder (Telefon
, 0231.....)) sowie genau eine komplexe Adresse (Strasse, PLZ, Ort).
An einer Hochschule studieren Studenten, die eine eindeutige
Matrikelnummer und eine Namen haben und mehrere (array-wertig!)
komplexe Kommunikationsverbindungen ( (Art, Wert) z.B. (Mail, @aaa)
oder (Telefon , 0231.....)) sowie mehrere (array-wertig!) komplexe
Adressen (Strasse, PLZ, Ort). Zusätzlich haben Studenten eine
Fachbereichszuordnung (int) und ein Fachsemester (int).
Vorlesungen sind komplexe Objekte mit einer eindeutigen Nummer, einer
Bezeichnung, einer Semesterwochenstundenzahl.
Einer Vorlesung sind beliebig Veranstaltungszeiten zugeordnet bestehend
jeweils aus dem Wochentag, Beginnzeit, Dauer in Minuten und dem
Hörsaal.
Dozenten sind beliebig viele Vorlesungen zugeordnet, die sie halten.
Studenten sind beliebig viele Vorlesungen zugeordnet, die sie hören.
Entwickeln Sie hierzu die objekt-relationalen Typen und Tabellen
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 53
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Übertragen Sie folgendes E-R-Modell in Objektrelationale Strukturen
(create type, create table)
Welche Konsequenzen ergeben sich für die Überlegungen zu Totalität
oder Disjunktheit von Spezialisierungen?
hausnummer
branche
name
Firma
strasse
plz
Datenbanken 2
firmnr
ort
land
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 54
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Kunde_nr
Kunden
Kundengruppe
jahresumsatz
Ansprechpartner
name
Datenbanken 2
funktion
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 55
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Lieferanten_nr
Lieferanten
jahresumsatz
abholzeit
wochentag
Datenbanken 2
von
bis
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 56
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Bestellungen
Bestell_nr
Datenbanken 2
ausgefuehrt
datum
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 57
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Rechnung_nr
Rechnungen
warenwert
zahlungen
datum
Datenbanken 2
betrag
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 58
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Auftrag_nr
Auftraege
Auftrag_datum
menge
Datenbanken 2
geliefert
preis
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 59
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Artikel_nr
Artikel
bezeichnung
bestand
Datenbanken 2
lieferzeit
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 60
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Artikelbewegungen
menge
Datenbanken 2
verkaufspreis
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 61
Teil 4: Objektrelationales Datenbankmodell
: Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
Auftragspositionen
geliefert
Position_nr
menge
Datenbanken 2
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 62
Teil 4: Objektrelationales Datenbankmodell :
Übungen
Abbildung: E-R-Modell -> Objektrelationales Modell
1
(0,n)
n
Artikel
1
(0,n)
1
(0,n)
n
hat
(1,1)
n
(1,1)
(1,n)
preis
n
(1,1)
Bestellungen
n
hat
1
n
(1,1)
Rechnungen
(0,n)
n
(1,1)
erhält
hat
Datenbanken 2
(1,1)
erteilt
Firma
1
(1,1)
(0,n)
bewirkt
n
(0,n)
Auftraege
1
lieferzeit
n
(1,1)
1
n
n umfasst
Auftragspositionen
(1,1)
haben
Lieferproramm
n
(0,n)
Verkauft
in
Bestellt_in
Artikelbewegungen
(0,n)
1
Lieferanten
is a
(0,n)
1
(0,n)
Kunden
- SS 2015 - © Prof. Dr. Klaus-Dieter Krägeloh - Folie 63