Datenbanken II - fbi.h

Kapitel 16
Vorbereitung auf die Prüfung
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
1
Konstruktion eines E-R-Diagramms
Insgesamt 80 Punkte für 90minutige Klausur, d. h. Aufwand pro Punkt: Ca. 1 Minute.
(17 Punkte)
Für die Firma Maier Kfz-Ersatzteilhandel GmbH ist eine Auftragsabwicklung zu schreiben. Als ersten Schritt dazu sollen Sie ein ER-Diagramm
entwerfen.
Ein Auftrag an die Firma Maier Kfz-Ersatzteilhandel GmbH sieht folgendermaßen aus:
________________________________________________________________
F. Müller AG
Im Feld 13
37657 Musterstadt
An Firma Maier Kfz-Ersatzteilhandel GmbH
Postfach 4711
64378 Darmstadt
15.01.02
Unsere Auftragsnummer: 4711
Hiermit bestellen wir gemäß Ihrer Preisliste:
3 Stück
Artikel Nr. 12345 Kupplungsscheiben
7 Stück
Artikel Nr. 23456 Kolbenrückzugsfedern
Mit freundlichen Grüßen
Müller
__________________________________________________________________
Nach Eingang des Auftrags wird eine Auftragsbestätigung geschrieben, in der zusätzlich die Listenpreise für die bestellten Artikel angegeben sind.
Erstellen Sie als Grundlage für das zu erstellende System ein vollständiges ER-Diagramm mit Angabe der Komplexitäten und der Attribute und
Kennzeichnung der Schlüsselattribute durch Unterstreichung
Im zu entwickelnden System sollen alle für die zur weiteren Verarbeitung in der Auftragsabwicklung benötigten und in der Auftragsbestätigung
auszudruckenden Daten abgelegt werden können.
Bedenken Sie: Zum Schreiben der Auftragsbestätigung benötigen Sie die Listenpreise, die irgendwo zugreifbar sein müssen. Während der
Abwicklung des Auftrages können sich die Listenpreise ändern. Bei der späteren Rechnungsschreibung müssen allerdings die in der
Auftragsbestätigung angegebenen Preise fakturiert werden. Die Auftragsbestätigung als auch der Auftrag habe ein Datum.
Die vom Kunden angegebene Auftragsnummer ist nicht unbedingt bezüglich aller bei uns eingehenden Aufträge eindeutig!
Die Rechnungsverwaltung sei ein eigenes System, d. h. in unserem ER-Diagramm wollen wir keine Rechnungen modellieren.
Obwohl Auftrag und Auftragsbestätigung eigentlich verschiedene Datenobjekte sind, ist es Ihnen in dieser Aufgabe freigestellt, in Ihrem ER-Modell
beide Datenobjekte durch ein Entity zu modellieren.
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
2
Definition eines relationalen DB-Schemas,
Normalformen
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
3
Subtype
(6P)
Wie kann man eine Subtypebeziehung (z. B. zwischen Person mit den
Attributen Personen-Nr. und Name und Mitarbeiter mit den Attributen
(Personen-Nr. und) Qualifikation) in Relationen umsetzen? Welche
Attribute sind wo, was sind die Schlüssel und Fremdschlüssel?
Beschreiben Sie mind. 2 Möglichkeiten der Abbildung zwischen der
Subtype-Beziehung des ER-Modells auf Relationen des
Relationenmodells. Diskutieren Sie die Nachteile der Lösungen.
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
4
SQL-Anweisungen
(30 Punkte)
Gegeben sei folgendes relationale Schema:
Kunde (Kd_Nr, Kd_Name, Kd_Bezirk)
Auftrag (Auftr_Nr, Auftr_Datum, Kd_Nr)
Kd_Nr sei Fremdschlüssel mit Referenz auf Kunde
Artikel (Artikel_Nr, Artikel_Bez, Preis)
Auftragsposition (Auftr_Nr, Artikel_Nr, Anzahl)
Auftr_Nr und Artikel_Nr seien Fremdschlüssel mit Referenz auf Auftrag und Artikel
•
Relationales Schema:
a.Unterstreichen Sie die Schlüssel und kennzeichnen Sie die Fremdschlüssel durch einen Strich über dem Attribut.
DDL:
b. Definieren Sie die Relation Auftragsposition in SQL mit Schlüssel- und Fremdschlüssel-Definition. (Auftragsposition hat 2
Fremdschlüssel.) Alle Attribute seien vom Typ Integer.
DML:
Schreiben Sie SQL-Anweisungen, die aus der Datenbank mit den oben angegebenen Relationen die folgenden Ausgaben erzeugen:
c. Geben Sie zu allen Aufträgen zu Kunden mit Kd_Name = ’Maier’ die Auftragsnummern (Auftr_Nr) aus. In der Überschrift der Liste
stehe statt Auftr_Nr Auftragsnummer.
d. Generieren Sie die Liste aller vom Kunden mit Kd-Name = ‚Maier’ bestellten Artikel. Geben Sie zu jedem Artikel Auftr_Nr,
Artikel_Bez und Anzahl aus.
e. Geben Sie zu allen Aufträgen Auftr_Nr und Gesamtpreis aus. In der Überschrift erscheine das Wort ‚Gesamtpreis’. Gesamtpreis sei
die Summe der Preise aller Artikel des Auftrags jeweils multipliziert mit der Anzahl des Artikels in dem Auftrag.
g. Geben Sie zu allen Aufträgen Auftr_Nr und Gesamtpreis aus, falls Maximalpreis der Preise aller bestellten Artikel > 1000.
Gesamtpreis sei die Summe der Preise aller Artikel des Auftrags jeweils multipliziert mit der Anzahl. Maximalpreis sei der
Einzelpreis des teuersten der bestellten Artikel.
Views:
h. Definieren sie eine Sicht (view) Darmstaedter-Kunde, die nur Kunden, die im Kd_Bezirk ’Darmstadt’ ihren Sitz haben, enthält und die
nur die Attribute Kd_Nr und Kd_Name besitzt.
Integritätsbedingungen:
i. Definieren sie eine Integritätsbedingung (assertion), die besagt, dass ein Preis eines Artikels immer kleiner sein muss als 5000.
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
5
SQL-Anweisungen
(21 Punkte)
Gegeben sei folgendes relationale Schema:
Mitarbeiter (Mitarb-Nr, Mitarb-Name)
Projekt (Proj-Nr, Proj-Name, Proj-Leiter-Nr)
Proj-Leiter-Nr sei Fremdschlüssel mit Referenz auf Mitarbeiter
Mitarbeiter_arbeitet_in_Projekt (Mitarb-Nr, Proj-Nr, Prozent_der_Arbeitszeit)
Mitarb-Nr und Proj-Nr seien Fremdschlüssel mit Referenz auf Mitarbeiter und Projekt
3a. (3P)
Definieren Sie die Relation Mitarbeiter_arbeitet_in_Projekt in SQL mit Schlüssel- und Fremdschlüssel-Definition.
Alle Attribute seien vom Typ Integer.
Generieren Sie aus der Datenbank mit den oben angegebenen Relationen SQL-Anweisungen, die folgende
Ausgaben erzeugen:
3b. (3P)
Geben Sie zu allen Projekten mit Projektleiter Maier Proj-Nr und Mitarb-Name des Projektleiters aus. In der
Überschrift der Liste stehe statt Proj-Nr Projektnummer und statt Mitarb-Name Projektleitername.
3c. (5P)
Generieren Sie die Liste aller im Projekt 4711 und Projekt 4712 mitarbeitenden Mitarbeiter. Geben Sie jeweils
Proj-Nr, Proj-Name, Mitarb-Name und Prozent_der_Arbeitszeit aus.
3d. (7P)
Geben Sie zu allen Projekten Proj-Name und Anzahl der Mitarbeiterstunden pro Tag aus. Anzahl der
Mitarbeiterstunden pro Tag sei die Summe der (Prozent_der_Arbeitszeit/100)*8.
3e. (3P)
Definieren Sie eine Sicht (view) Maiers_und_Muellers_Projekte, die nur Projekte, die von Herrn Maier oder Herrn
Mueller geleitet werden enthält und in der nur Proj-Nr, Proj-Name und Name_Projektleiter enthalten sind.
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
6
Embedded SQL
(9 Punkte)
a. (4P)
Definieren Sie einen Cursor AktMitarbeiter_Cursor über der Relation
Mitarbeiter aus voriger Aufgabe. (Es handele sich um die gesamte
Mitarbeiter-Relation mit allen Attributen.)
b. ( 2P)
Definieren Sie einen Record AktMitarbeiter_Record zur Aufnahme eines
durch das Fetch-Kommando aus der Mitarbeiter-Relation
herausgeholten Mitarbeiter-Records (Verwendung der %RowtypeOperation) oder definieren sie andere Variablen, in die Sie die Attribute
eines Mitarbeiter-Datensatzes hineinbringen können.
c. ( 3P)
Holen Sie per Fetch-Befehl den ersten Mitarbeiter-Datensatz in den unter
b. definierten Record AktMitarbeiter_Record. (oder in die in b.
definierten Variablen).
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
7
Objektorientiertes Datenbanksystem
Wie wird die Beziehung
Marke
Auto
1
0..*
im objektorientierten Datenbanksystem realisiert? (Man soll von einem
Auto zur dazugehörigen Marke und von der Marke zu den zugehörigen
Autos navigieren können.)
Prof. Dr. Wolfgang Weber
Vorlesung Datenbanken
8