Informatik/Jahrgangsstufe Q/001 Klausuren

Aufgabe 3:
Datenbanken – Entity-Relationship-Modell
a) Geben sie die Komplexität der folgenden Situationen an. Erstellen Sie jeweils ein ERDiagramm. Formulieren Sie sinnvolle Geschäftsregeln.
1.
Eine Ladenkette möchte Informationen über ihre Filialen und über deren Zulieferer
speichern. Jeder Zulieferer beliefert mehrere Filialen, und jede Filiale kauft von
mehreren Zulieferern.
2.
Ein Installateur möchte über seine Handwerker und über die Häuser, in denen sie
gerade arbeiten, Informationen speichern.
b) Die Computerzubehörfirma Microtec GmbH möchte ihre Verwaltung auf EDV umstellen.
Sie verkauft ein Sortiment von Artikeln, die sie von verschiedenen Herstellern bezieht.
Außerdem hat sie einen bestimmten Kundenkreis, der bei ihr Bestellungen aufgibt. Eine
Bestellung kann natürlich mehrere Artikel umfassen. Derselbe Artikel kann oft von
mehreren Herstellern bezogen werden, und ein Hersteller liefert natürlich meist mehr als
einen Artikel.
Erstellen Sie im Entity-Relationship-Modell ein sinnvolles Datenmodell für die Firma, das
Datenredundanz vermeidet. Wählen Sie geeignete Entities mit notwendigen Attributen und
geben Sie die zwischen den Entities bestehenden Beziehungen mit ihrem Komplexitätsgrad
an.
c) Eine Fluggesellschaft will ein Informationssystem aufbauen, um diese Informationen zu
speichern:
1. die Piloten, identifiziert durch die Personal-Nr mit Name und Alter,
2. die verwendeten Flugzeugtypen mit eindeutiger Typ-Bezeichnung und Sitzanzahl,
3. die Information, welcher Pilot welchen Flugzeugtyp fliegt,
4. die Flugstunden, die ein Pilot insgesamt geflogen ist,
5. die Flugstunden die er auf einem bestimmten Flugzeugtyp geflogen ist und
6. die Flugstunden, die mit einem Flugzeugtyp insgesamt geflogen wurden.
I)
Zeichnen Sie das entsprechende ER-Diagramm, unterstreichen Sie dabei die
Schlüsselattribute.
II)
Überführen Sie das Modell in das relationale Modell.
Lösung:
Aufgabe 3:
a.I)
Zulieferer
n
beliefert
cn
Filialen
Ein Zulieferer kann in der Datenbank vorhanden sein, obwohl er keine Filiale beliefert, Eine
Filiale dagegen hat mindestens einen Zulieferer.
a.II)
Handwerker
n
arbeitet
an
m
Häuser
Ein Handwerker arbeitet mindestens an einem Haus. In einem Haus arbeiten mindestens ein
Handwerker.
b)
Name
HerstNr
Anschrift
Hersteller
Bezeichnung
ArtNr
n
liefert
m
Preis
Artikel
n
gehört
zu
KundNr
Kunde
1
gibt
auf
n
Anzahl
cm
Bestellung
RechnNr
Name
Anschrift
Datum
c.I)
Name
PersNr
Alter
Pilot
Stunden
n
c.II)
Pilot(PersNr, Name, Alter, Stunden)
fliegt(PersNr, TypBez, Stunden)
Flugzeugtyp(TypBez, Stunden, Sitze)
Stunden
fliegt
TypBez
m
FlugzTyp
Sitze
Stunden
Aufgabe 4:
Datenbanken – Relationales Modell
a) Gegeben seien drei Relationen mit den folgenden Daten:
Besucht
Serviert
Mag
Gast Bistro
Bistro Getränk
Gast
Hans Uno
Uno
Wasser
Hans
Ede
Uno
Uno
Kaffee
Ede
Ede
Dos
Dos
Kaffee
Ede
Ede
Chico
Karl
Karl Dos
Karl Chico
Heini Uno
Getränk
Wasser
Wasser
Kaffee
Kaffee
I)
Bilden Sie Serviert × Mag.
II) Bilden Sie Serviert
Mag. Welche Informationen beinhaltet diese Relation?
III) Geben Sie alle Bistros aus, die Getränke servieren, die Karl mag. Geben Sie auch die
entsprechende Operation der Relationenalgebra an.
IV) Geben Sie alle Gäste aus, die ein Bistro besuchen, das auch das Getränk serviert,
das sie mögen. Formulieren Sie die Anfrage mit Operationen der Relationenalgebra.
b) Gegeben seien folgende Relationen:
Lieferanten (LieferantenNr, Name, Status, Stadt)
Teile (TeileNr, Name, Farbe, Gewicht, Stadt)
Projekte (ProjektNr, Name, Stadt)
Lieferungen (LieferantenNr, TeileNr, ProjektNr, Anzahl)
Hierbei bedeutet Stadt einmal die Stadt, in der ein Lieferant sitzt, die Stadt, in der das
entsprechende Teil hergestellt wird, bzw. die Stadt, in der ein Projekt stattfindet. Lösen Sie
die folgenden Aufgaben durch Operationen aus der Relationenalgebra:
I)
Finden Sie alle Lieferungen mit Anzahlen zwischen 300 und 750 und geben Sie alle
dazu in der Relation Lieferungen verzeichneten Informationen aus.
II) Geben Sie alle Städte aus, in denen Lieferanten sitzen.
III) Finden Sie alle schwarzen Teile. Geben Sie ihre Nummer und ihren Namen aus.
IV) Finden Sie alle Lieferanten, die in einer Einzellieferung mehr als 150 Teile geliefert
haben. Geben Sie ihren Namen aus.
V) Finden Sie alle Städte, in denen sowohl Projekte als auch Lieferanten beheimatet sind.
c) Bilden Sie das folgende ER-Diagramm in das relationale Modell ab. Optimieren Sie die
Relationenanzahl
Lehrstuhlbezeichnun
g
PName
Telefon
Anschrift
1
Professor
akad.
Grad
AName
hat
cn
1
Telefon
Assistent
1
betreut
hält
ÜbungsNr
Zeit
Vorl-Nr
cn
1
Vorlesung
hat
cn
cn
Übungsgruppe
1
Zeit
Ort
MatrikelNr
Stundenzahl
korrigiert
IS
A
1
Student
c
Ort
Stundenzahl
1
Hiwi
Vertrag
Name
Anschrift
Fach
d) Sie importieren Daten aus einer Excel-Tabelle und erhalten die folgende Struktur.
Normalisieren Sie die Daten von der 1. bis zur 3. Normalform. Dokumentieren Sie dabei
ausführlich Ihre Schritte mit der entsprechenden Begründung (Definition der
Normalformen und ihre Folgerungen).
PersNr Name
Abteilung
513
134
599
Einkauf
Verkauf
Personal
Lorenz, Christian
Baumann, Peter
Petersen, Anna
AbteilungsNr
13
21
7
Lösung:
Aufgabe 4:
a.I)
Bistro
Uno
Uno
Uno
Uno
Uno
Uno
Getränk
Wasser
Wasser
Wasser
Wasser
Kaffee
Kaffee
Gast
Hans
Ede
Ede
Karl
Hans
Ede
Getränk
Wasser
Wasser
Kaffee
Kaffee
Wasser
Wasser
Abteilungsort
Stunden
51519 Odentahl
51467 Berg. Gl.
51515 Kürten
198, 201
120, 189, 43
120
Uno
Uno
Dos
Dos
Dos
Dos
Kaffee
Kaffee
Kaffee
Kaffee
Kaffee
Kaffee
Ede
Karl
Hans
Ede
Ede
Karl
Kaffee
Kaffee
Wasser
Wasser
Kaffee
Kaffee
a.II)
Bistro
Getränk Gast
Uno
Wasser
Hans
Uno
Wasser
Ede
Uno
Kaffee
Ede
Uno
Kaffee
Karl
Dos
Kaffee
Ede
Dos
Kaffee
Karl
Die Tabelle enthält die Information darüber, in welchem Bistro welche Personen ihr
Lieblingsgetränk erhalten könnte.
a.III) πBistro (σGast = Karl (Serviert
Bistro
Uno
Dos
a.IV) πGast (Besucht
Mag)
(πBistro, Gast (Serviert
Mag)
Gast
Hans
Ede
Karl
b.I)
b.II)
σAnzahl ≥ 300 und Anzahl ≤ 750 (Lieferungen)
πStadt (Lieferanten)
b.IV)
πTeileNr, Name (σFarbe = schwarz (Teile))
πName ((πLieferantenNr (σAnzahl > 150 (Lieferungen)))
b.V)
πStadt (Lieferanten
b.III)
Lieferanten)
Projekte)
c)
Professor(PName, Lehrstuhlbez., Telefon, Anschrift)
Assistent(AName, akad. Grad, Telefon, PName)
Vorlesung(Vorl.Nr, Zeit, Ort, Std.zahl, PName)
Übungsgruppe(Übungs-Nr, Zeit, Ort, Std.zahl, AName, Vorl.Nr, Vertrag, MatrikelNr)
Student(MatrikelNr, Name, Adresse, Fach)
d)
1. Normalform: eindt. Schlüssel und atomare Werte
Hier: Name -> Vorname, Nachname
Stunden auslagern in extra Tabelle, da Mehrfachattribut
Stunden(PersNr,AbteilungsNr, StundenID, Stunden)
Personal(PersNr, AbteilungsNr, Vorname, Nachname, Abteilung, AbteilungsPLZ, Abteilungsort)
2. Normalform: Attribute nur Abhängig vom gesamten Primärschlüssel.
Hier: Abteilung (mit PLZ und Ort) nur von AbteilungsNr abhängig. => auslagern. Diese Lösung lässt
auch zu, dass Personal in mehreren Abteilungen gleichzeitig eingesetzt werden kann.
Stunden(PersNr,AbteilungsNr, StundenID, Stunden)
Personal(PersNr, Vorname, Nachname)
Personal_istIn_Abteilung(PersNr, AbteilungsNr)
Abteilungen(AbteilungsNr, Abteilung, AbteilungsPLZ, Abteilungsort)
3. Normalform: keine transitiven Abhängigkeiten.
Hier: Ort hängt von PLZ ab. => auslagern in PLZ-Ort-Tabelle
Stunden(PersNr,AbteilungsNr, StundenID, Stunden)
Personal(PersNr, Vorname, Nachname)
Personal_istIn_Abteilungs(PersNr, AbteilungsNr)
Abteilungen(AbteilungsNr, Abteilung, AbteilungsPLZ)
Orte(PLZ, Ort)
Aufgabe 2:
Datenbanken
a) Frau Emma möchte ihren Tante-Emma-Laden ab jetzt mit einer Datenbank verwalten.
Für jeden Artikel will Frau Emma die Bezeichnung, den Bestand, den Einkaufspreis und
den Verkaufspreis speichern.
Die Artikel gehören sogenannten Warengruppen (Lebensmittel, Zeitschriften, etc.) an.
Diese haben eine eindeutige Bezeichnung und einen Mehrwertsteuersatz.
Neben den Artikeln und Warengruppen will Frau Emma auch ihre Verkaufsfläche auf
EDV umstellen. In ihrem Laden gibt es verschiedene Standorte, welche durch die
Kombination aus Regal-Nummer und Platz-Nummer eindeutig festgelegt sind. Es Gibt
Standorte, welche noch leer sind, andernfalls ist im Standort vermerkt, welcher Artikel sich
dort befindet. Da bestimmte Artikel nur in Regalen eines bestimmten Materials gelagert
werden können, soll für jeden Standort zusätzlich das Material des Regals gespeichert
werden. Die Relation Standorte könnte damit wie folgt aussehen:
Standorte RegalNr PlatzNr ArtikelNr Material
11
15
17
Holz
11
16
NULL
Holz
11
17
39
Holz
12
5
NULL
Eisen
12
15
25
Eisen
I)
Erstellen Sie ein Entity-Relationship-Diagramm für dieses Problem (Bitte nur die
oben angegebenen Attribute einfügen). Begründen Sie alle angegebenen
Kardinalitäten.
II)
Überführen Sie das Diagramm in das relationale Modell. Optimieren Sie falls
möglich die Anzahl der Relationen.
III) Begründen Sie entweder, dass Ihr relationales Modell bereits der 1. Normalform
entspricht. Oder überführen Sie Ihre Datenbank in die 1. Normalform.
IV) Begründen Sie, dass Ihre Datenbank noch nicht den Anforderungen der 2.
Normalform entspricht. Überführen Sie Ihre Datenbank anschließend in die 2.
Normalform.
V)
Formulieren Sie bezugnehmend auf Ihre Datenbank SQL-Abfragen für die folgenden
Informationen:
1. Welche Artikel-Bezeichnung der Warengruppe "Schreibwaren" gibt es im TanteEmma-Laden?
2. In welchen Regalen ist noch Platz? Die Regalnummern sollen aufsteigend sortiert
sein.
3. Zu allen Standorten soll die Bezeichnung des Artikel ausgegeben werden, der dort
liegt. Leere Standorte sollen ebenfalls mit ausgegeben werden.
4. Wie viele Artikel haben einen Mehrwertsteuersatz von 19%?. Nur die Anzahl ist
gefragt.
b) Im Datenbank-System einer Schule sind folgende Tabellen implementiert:
Schueler Nummer Name
Vorname Geburtsdatum Lerngruppe
123
Müller
Martin
11.2.1992
9A
178
Schmitz Antonia
19.6.1989
12
Lerngruppen Klasse
9A
12
10C
Klassenlehrer
MEY
KSL
TAN
Faecher Abkuerzung Langname
M
Mathe
D
Deutsch
Kurse Bezeichnung
M-LK-12
D-9-A
F-GK-13
Lehrer Kuerzel
MEY
OP
TAN
Schueler SchuelerNr Kursbezeichnung
D-9-A
besucht 123
E-9-A
Kurs 123
123
M-9-A
178
M-LK-12
456
D-9-A
Stufe
12
9
13
Fach
M
D
F
Lehrer
MEY
OP
TAN
Langname
Meyer
Opermann
Tarnert-Nauber
Lehrer Lehrer Fach
D
unterrichtet OP
E
Fach OP
MEY M
MEY E
I)
Geben Sie zu allen Tabellen den Primärschlüssel an.
II)
Begründen Sie kurz, dass die Datenbank der 3. Normalform entspricht.
III) Zeichnen Sie ein Entity-Relationship-Diagramm ohne Attribute. Achten Sie darauf,
dass Sie alle Beziehungen zeichnen, die in den Relationen versteckt sind.
Geben Sie auch die Komplexitäten an.
IV) Welche Information erhält man durch folgende SQL-Abfragen?
1. select max(Schueler.Geburtsdatum)
from Schueler
2. select Lehrer.Langname, Lehrer_unterrichtet_Fach.Fach
from Lehrer left join Lehrer_unterrichtet_Fach on
Lehrer.Kuerzel = Lehrer_unterrichtet_Fach.Lehrer
order by Lehrer.Kuerzel
3. select Kurse.Fach
from (Schueler inner join Schueler_besucht_Kurs on
Schueler.Nummer = Schueler_besucht_Kurs.SchuelerNr)
inner join Kurse on
Schueler_besucht_Kurs.Kursbezeichnung= Kurse.Bezeichnung
where Schueler.Lerngruppe = "9A"
V)
Geben Sie SQL-Abfragen an, mit denen folgende Informationen gesucht werden:
1. Alle Fächer-Langnamen, die von dem Lehrer MEY unterrichtet werden.
2. Alle Geburtsdaten der Schüler der Stufe 9, absteigend sortiert.
3. Alle Schüler, deren Klassenlehrer Mathematik unterrichten kann.
Lösung:
Aufgabe 2:
a) I)
Besatand
Bezeichnung
EK
VK
Artikel
Bezeichnung
n
gehört
zu
c
1
liegt
in
RegalNr
II)
1
Mehrwertsteuer
Warengruppe
Standorte
PlatzNr
Material
Die Relationen sind:
Artikel(Bezeichnung, Bestand, EK, VK, Warengruppe, ArtikelNr)
Warengruppe(Bezeichnung, Mehrwertsteuersatz)
Standorte(RegalNr, PlatzNr, Material, ArtikelNr)
III) Die Relationen entsprechen noch nicht der 1. Normalform, da nicht alle Relationen
eine Primärschlüssel haben.
Artikel(ArtikelNr, Bezeichnung, Bestand, EK, VK, Warengruppe)
Warengruppe(Bezeichnung, Mehrwertsteuersatz)
Standorte(RegalNr, PlatzNr, Material, ArtikelNr)
IV) Die Relation Standorte entspricht nicht der 2. Normalform, da das Material nur von
RegalNr abhängig ist, d. h. nur von Teilen des Primärschlüssels
Artikel(ArtikelNr, Bezeichnung, Bestand, EK, VK, Warengruppe)
Warengruppe(Bezeichnung, Mehrwertsteuersatz)
Regale(RegalNr, Material)
Standorte(RegalNr, PlatzNr, ArtikelNr)
V)
b) I)
1. select Artikel.Bezeichnung
from Artikel
where Artikel.Warengruppe = "Schreibwaren"
2. select Standorte.RegalNr
from Standorte
where ArtikelNr is NULL
order by Artikel.RegalNr ASC
3. select Standorte.RegalNr, Standorte.PlatzNr, Artikel.Bezeichnung
from Standorte left join Artikel ON Standorte.ArtikelNr =
Artikel.ArtikelNr
4. select count(Artikel.Bezeichnung)
from Artikel inner join Warengruppe on Artikel.Warengruppe =
Warengruppe.Bezeichnung
where Warengruppe.Mehrwertsteuersatz = 0.19
Schüler(Nummer)
Lerngruppen(Klasse)
Kurse(Bezeichnung)
Faecher(Abkuerzung)
Lehrer(Kuerzel)
SchuelerBesuchtKurs(SchuelerNr, Kursbezeichnung)
LehrerUnterrichtetFach(Lehrer, Fach)
II)
Alle Attribute sind atomar und jede Relation hat einen Primärschlüssel => 1. NF
Alle Attribute sind vom vollständigen Primärschlüssel abhängig => 2. NF
Kein Attribut ist transitiv abhängig vom Primäschlüssel. => 3. NF
III)
Quelle: http://www.r-krell.de/k1if13m-07a.pdf
Ein Schüler gehört zu 1 Klasse, eine Klasse hat n Schüler
Ein Schüler besucht n Kurse, eine Kurs hat n Schüler
Ein Lehrer unterrichtet n Kurse, ein Kurs wird von 1 Lehrer unterrichtet
Ein Lehrer darf n Fächer unterrichten, Ein Fach wird von n Lehrern unterrichtet.
Ein Lehrer ist Klassenlehrer von 1 Klasse, Eine Klasse hat 1 Klassenlehrer
Ein Kurs gehört zu 1 Fach, ein Fach hat n Kurse.
IV) 1. Das größte Geburtsdatum aller Schüler.
2. Alle Lehrernamen mit den Fächern die sie unterrichten, sortiert nach Lehrer-Kürzel
3. Alle Fächer, die von der Klasse 9a belegt werden.
V)
1.
SELECT Faecher.Langname
FROM Faecher INNER JOIN Lehrer_unterrichtet_Fach ON
Faecher.Abkuerzung = Lehrer_unterrichtet_Fach.Fach
WHERE Lehrer_unterrichtet_Fach.Lehrer="MEY"
2.
SELECT Schueler.Geburtsdatum
FROM Schueler INNER JOIN
(Schueler_besucht_Kurs INNER JOIN Kurse ON
Schueler_besucht_Kurs.Kursbezeichnung = Kurse.Bezeichnung) ON
Schueler.Nummer = Schueler_besucht_Kurs.SchuelerNr
WHERE Kurse.Stufe="9"
ORDER BY Schueler.Geburtsdatum DESC
3.
SELECT Schueler.Name
FROM (Schueler INNER JOIN Lerngruppen ON
Schueler.Lerngruppe = Lerngruppen.Klasse) INNER JOIN
Lehrer_unterrichtet_Fach ON
Lerngruppen.Klassenlehrer = Lehrer_unterrichtet_Fach.Lehrer
WHERE Lehrer_unterrichtet_Fach.Fach = "M"
Aufgabe 1:
Datenbanken
a) Gegeben ist folgender Ausschnitt einer Fußball-Datenbank:
Fußballspieler können bei höchstens einem Verein gemeldet sein. Sie werden über ihre
Passnummer identifiziert und besitzen einen Namen. Die Vereine sind durch ihren
Namen und die Vereinsnummer gekennzeichnet. Zusätzlich wird registriert, in welchen
Ligen die Vereine vertreten sind. Die Namen der Ligen sind eindeutig in Kombination
mit der Altersklasse (es gibt mehrere Altersklassen). Jeder Verein ist mindestens in
einer dieser Ligen vertreten. Zudem wird das Datum erfasst, zu dem der Verein in die
jeweilige Liga aufgestiegen ist.
Modellieren Sie diesen Ausschnitt der Fußball-Datenbank im Entity-Relationship-Modell.
Verwenden Sie die grafische Notation des ER-Diagramm für Ihre Darstellung.
Markieren Sie die Primärschlüssel und geben Sie die Kardinalitäten der Beziehungen an.
b) Gegeben ist folgendes E-R-Modell einer Hotelketten-Verwaltung:
Sterne
Hotel
Adresse
Stadt
Kapazität
Land
Hotel-ID
1
Pers-ID
arbeitet
in
n
Name
Zimmer-ID
1
1
1
hat
IS A
IS A
c
c
n
Kategorie
Gehalt
Angestellter
Zimmer
n
Möbel
Preis
verantwortlich
m
Reinigungskraft
Arbeitsverhältnis
Manager
email
Telefon
Überführen Sie dieses E-R-Diagramm in das relationale Modell. Achten Sie dabei auf eine
optimierte Relationenanzahl.
c) Gegeben seien drei Relationen mit den folgenden Daten:
Unterrichtet
Fach Lehrer
M
ULO
E
RE
E
PL
D
PL
M
NOP
M
PL
Lehrbefähigung
Lehrer Stufe
ULO
SekI
RE
SekII
RE
SekI
PL
SekII
Verteilung
Fach
Stufe
M
SekI
M
SekII
D
SekI
E
SekII
D
RE
Bilden Sie die Selektion σLehrer = RE(Lehrbefähigung).
Lehrbefähigung. Geben Sie in eigenen Worten
Bilden Sie den Join Unterrichtet
an, welche Informationen diese Relation beinhaltet.
III) Geben Sie alle Fächer aus, die in Stufen unterrichtet werden, die der Lehrer PL
unterrichten darf. Geben Sie auch die entsprechende Operation der
Relationenalgebra an.
IV) Geben Sie alle Lehrer aus, die Fächer in der SekI unterrichten dürfen, die auch (evtl.
von anderen Lehrern) in der SekII unterrichtet werden. Formulieren Sie die Anfrage
mit Operationen der Relationenalgebra.
I)
II)
d) Im folgenden ist das Relationenmodell einer Schlüsselverwaltung angegeben:
Raum( Raum-ID, Raumbezeichnung )
Tür( Tür-ID, RaumVor, RaumHinter, Schloss-ID )
Schloss( Schloss-ID, Seriennummer )
schließt( Schloss-ID, Schlüssel-ID )
Schlüssel( Schlüssel-ID, Nummer )
Person( Person-ID, Vorname, Name )
Ausleihe( Ausleihe-ID, Person-ID, Schlüssel-ID, AusgabeDatum, RückgabeDatum )
I) Entwickeln Sie aus dem Relationenmodell ein E-R-Diagramm mit Angabe von
Attributen, Primärschlüsseln und Kardinalitäten.
II) Geben Sie unter Verwendung der Relationenalgebra die Operationen für folgende
Abfragen an:
(1) Welche Schlüssel (Nummer) müssen Weihnachten zurückgegeben werden?
(2) Welche Personen (Vorname, Name) haben irgendwann Schlüssel ausgeliehen
und schon wieder zurückgebracht?
(3) Welche Türen (Tür-ID) kann Gustav Gazelle aufschließen?
Lösung:
Aufgabe 1:
a)
Name
Passnr.
Vereinsnr
Spieler
n
gemeldet
in
1
Name
Verein
n
spielt
in
Aufst.Dat.
m
Liga
Name
Klasse
b) Hotel(Hotel-ID, Kapazität, Sterne, Stadt, Land)
Angestellter(Pers-ID, Name, Gehalt, Hotel-ID)
Zimmer(Zimmer-ID,Kategorie, Preis, Hotel-ID)
Möbel(Möbel-ID, Zimmer-ID, Bezeichnung)
Reinigungskraft(Pers-ID, Arbeitsverhältnis)
verantwortlich(Zimmer-ID, Pers-ID)
Manager(Pers-ID, email, Telefon)
c)
I) σLehrer = RE(Lehrbefähigung)
Lehrer Stufe
RE
SekII
RE
SekI
II) Unterrichtet
Lehrbefähigung
Fach Lehrer Stufe
M
ULO
SekI
E
RE
SekII
E
RE
SekI
E
PL
SekII
D
PL
SekII
M
PL
SekII
D
RE
SekII
D
RE
SekI
Es werden alle Kombinationen Fächer-Stufe ausgegeben, für die ein Lehrer existiert.
III) πFach(σLehrer = PL(Unterrichtet
Fach
E
M
Lehrbefähigung)
IV) πLehrer(σStufe=SekII(Verteilung
Lehrer
ULO
RE
σStufe=SekI(Lehrbefähigung)))))
(πFach, Lehrer(Unterrichtet
d)
Bezeichn.
Tür-ID
hinter
1
Raum
Raum-ID
n
1
vor
Tür
n
1
hat
Nummer
1
Schlüssel
Schl.-ID
n
passt
in
m
Schloss
n
Ausgabe
Sereinnr
ausgeliehen
SchlossID
Rückgabe
1
Person
Person-ID
Vorname
Name
(1) πNummer(σRückgabeDatum="24.12."(Schlüssel
(2) πVorname, Name(σRückgabeDatum<>""(Ausleihe
(3) πTür-ID(Tür
(schließt
(Ausleihe
Ausleihe))
Person))
σName="Gazelle" ∧ Vorname="Gustav"(Person))))
Aufgabe 1:
Datenbanken
Das "Schuhhaus König" möchte seinen Artikelbestand in einer Datenbank speichern. Die
Datenbank soll Angaben zu den einzelnen Herstellern (Name und Anschrift) und Daten der
einzelnen Schuhtypen (Hersteller, Fabrikat, Schuhart) enthalten. Unter Schuhart versteht das
Schuhhaus die Unterscheidung in "Sandale", "Stiefel", "Pantoffel", etc., welche in einer
eigenen Entität modelliert werden sollen.
Zusätzlich möchte das Schuhhaus für jeden Schuhtyp die Anzahl der auf dem Lager
befindlichen Größen und Farben speichern. Für ein Paar Schuhe wird demnach der Schuhtyp,
die Größe, die Farbe und der Lagerort gespeichert.
Die Lagerorte bestehen dabei stets aus einer Gangnummer und einer Regalnummer, welche −
unabhängig davon, ob Schuhe im Lagerort gelagert werden − in einer eigenen Tabelle
gespeichert werden sollen.
a) Modellieren Sie ein geeignetes Entity-Relationship-Modell und stellen Sie dieses als
Entity-Relationship-Diagramm dar. Führen Sie sinnvolle Primärschlüssel ein oder
definieren Sie geeignete Attribute als Primärschlüssel. Geben Sie auch die Kardinalitäten
Ihrer Beziehungen an.
b) Überführen Sie dieses ERM in das relationale Modell. Optimieren Sie die Tabellenanzahl.
Der Hersteller "Salamander" verwaltet seine Kunden und Artikel ebenfalls mithilfe einer
Datenbank. Die Modellierung im relationalen Modell sieht dabei wie folgt aus:
Kunde(Firmenname, Anschrift, Ansprechpartner, bestellterArtikel-ID, bestellteAnzahl)
Artikel(ID, Fabrikatname, Geschlecht, Größe)
Lagerort(HalleGangPlatz, Artikel-ID, Anzahl, Stapler)
Das Attribut HalleGangPlatz könnte z. B. "Halle5, Gang 3, Platz 17b" lauten. Es gibt
Lagerorte, in denen keine Artikel gelagert sind, es kann aber auch sein, dass an einem
Lagerort unterschiedliche Artikel gelagert werden.
Im Attribut Stapler soll lediglich festgehalten werden, ob ein Stapler für diesen Lagerort
benötigt wird, oder nicht.
c) Geben Sie die Datentypen an, welche für die Relation Lagerort bei der Anlage einer
entsprechenden MySQL-Tabelle gewählt werden müssen.
d)
Erläutern Sie, weshalb sich die Datenbank nicht in der 1. Normalform befindet.
Überführen Sie anschließend die Datenbank in die 1. Normalform.
e) Begründen oder widerlegen Sie, dass sich die Datenbank aus c) in der 3. Normalform
befindet. Überführen Sie diese gegebenenfalls in die 3. Normalform.
f) Geben Sie eine SQL-Anfrage an, mit der man herausfinden kann, an welchem Lagerort
die von der Firma "Schuhhaus König" bestellten Schuhe zu finden sind.
g) Geben Sie eine SQL-Anfrage an, die alle Lagerorte und – falls vorhanden – dort gelagerte
Artikel-IDs auflistet.
Erläutern Sie, weshalb der Inner Join hier nicht zum Ziel führt.
Lösung:
Aufgabe 1:
a) ... noch machen ...
b) Hersteller(ID, Name, Straße, PLZ, Ort)
Schuhtyp(ID, Hersteller-ID, Fabrikat, Schuhart-ID)
Schuhart(ID, Art)
Schuh(ID, Schuhtyp-ID, Größe, Farbe, Lagerort-ID)
Lagerort(ID, Gangnummer, Regalnummer)
c) HalleGangPlatz: VARCHAR(30)
Artikel-ID: INTEGER
Anzahl: INTEGER
Stapler: BOOLEAN
d) Die Attribute sind nicht atomar und es gibt keine Primärschlüssel.
Kunde(ID, Firmenname, Straße, PLZ, Ort, Ansprechpartner, bestellterArtikel-ID,
bestellteAnzahl)
Artikel(ID, Fabrikatname, Geschlecht, Größe)
Lagerort(ID, Halle, Gang, Platz, Artikel-ID, Anzahl, Stapler)
e) Die Attribute sind nicht alle vom Primärschlüssel abhängig. Es gibt zudem transitive
Abhänigkeiten (z. B. PLZ-Ort). Diese müssen in eigene Tabelle aufgelöst werden.
Kunde(ID, Firmenname, Straße, PLZ, Ansprechpartner)
Orte(PLZ, Ort)
bestellt(Kunden-ID, Artikel-ID, Anzahl)
Artikel(ID, Fabrikatname, Geschlecht, Größe)
lagert(Artikel-ID, Lagerort-ID, Anzahl)
Lagerort(ID, Halle, Gang, Platz, Stapler)
f) SELECT Lagerort.Halle, Lagerort.Gang, Lagerort.Platz
FROM Lagerort INNER JOIN lagert ON Lagerort.ID = lagert.Lagerort-ID
INNER JOIN Artikel ON lagert.Artikel-ID = Artikel.ID
INNER JOIN bestellt ON Artikel.ID = bestellt.Artikel-ID
INNER JOIN Kunde ON bestellt.Kunden-ID = Kunde.ID
WHERE Kunde.Firmenname = "Schuhhaus König"
g) SELECT Lagerort.Halle, Lagerort.Gang, Lagerort.Platz, Artikel.ID
FROM Lagerort LEFT OUTER JOIN lagert ON Lagerort.ID = lagert.Lagerort-ID
RIGHT JOIN Artikel ON lagert.Artikel-ID = Artikel.ID
Aufgabe 2:
Datenbanken
Sie importieren Daten aus einer Excel-Tabelle und erhalten die folgende Struktur.
GeräteNr Bezeichnung
Raum
Gebäude
Anschaffung
XV12942
CD-Player, Kenwood
517
5
DY96352
Radio, Toshiba
119
1
ZU66351
Fernseher, Panasonic
914
9
12.01.2004,
119 €
17.10.2000,
15,99 €
04.03.2009,
1199 €
Fachschaft
im Raum
GE, SP, E
GE, EK, SW
IF, M, PH
a) Normalisieren Sie die Daten in der 3. Normalform im relationalen Modell. Dokumentieren
Sie dabei ausführlich Ihre Schritte mit der entsprechenden Begründung.
b) Gesucht sind die Anschaffungspreise der Geräte, welche von der Fachschaft Informatik
verwendet werden. Geben Sie auf Grundlage des relationalen Modells aus Aufgabenteil a)
den zugehörigen Ausdruck in der Relationenalgebra an.
c) Formulieren Sie eine äquivalente SQL-Anfrage, welche die Anschaffungspreise
absteigend sortiert ausgibt.
Gegeben ist folgende Datenbank im relationalen Modell:
Person(PersonenNr, Name, Gehalt, Beruf, AbeitlungNr, ManagerNr, Ort)
Abteilung(AbteilungNr, AbteilungName, AbteilungOrt)
Dabei ist Person.ManagerNr Fremdschlüssel auf Person.PersonenNr, d. h. ein Manager ist
auch nur eine Entität des Entitätstyps Person.
Zudem ist Person.AbteilungNr Fremdschlüssel auf Abteilung.AbteilungNr.
d) Formulieren Sie für folgende Informationen Anfragen in SQL:
I)
Liste aller Angestellten aus Abteilung K15, die mehr als 5000 € verdienen.
II) Anzahl der Abteilungen, die in Köln angesiedelt sind.
III) Liste aller Abteilungen in Köln, die Angestellte beschäftigen, die auch in Köln
wohnen.
IV) Liste aller Programmierer (Name, Gehalt und Abteilungsname), die in Köln
beschäftigt sind.
V) Durchschnittsverdienst aller Mitarbeiter der Abteilung K15.
e)
Beschreiben Sie die Informationen, welche man durch die Ausdrücke E1 und
E2 der Relationenalgebra erhält.
I)
E1 = πName, Ort(σAbteilungName="K15"(Person
Abteilung))
der Join wird auf das Attribut AbteilungNr ausgeführt.
II) A1 = πPersonenNr(σName = "Müller"(Person))
;
E2 = πName(A1
Person)
der Join wird auf die Attribute A1.PersonenNr und Person.ManagerNr ausgeführt.
Lösung:
Aufgabe 2:
a) 1. Normalform: Attribute atomar machen:
Gerät(Geräte-Nr, Bezeichnung, Hersteller, Raum, Gebäude, Anschaffungsdatum,
Anschaffungspreis, Fachschaft)
2. Normalform: Abhängigkeit vom gesamten Primärschlüssel
Gerät(Geräte-Nr, Bezeichnung, Hersteller, Raum, Gebäude, Anschaffungsdatum,
Anschaffungspreis)
Fachschaft(Geräte-Nr, Fachschaft)
3. Normalform: keine transitiven Abhängigkeiten:
Gerät(Geräte-Nr, Bezeichnung, Raum, Anschaffungsdatum, Anschaffungspreis)
Fachschaft(Geräte-Nr, Fachschaft)
Hersteller(Bezeichnung, Hersteller)
Raum(Raum, Gebäude)
b) πAnschaffungspreis(σFachschaft = IF(Fachschaft)
Gerät)
c) SELECT Gerät.Anschaffungspreis
FROM Gerät INNER JOIN Fachschaft ON Gerät.Geräte-Nr = Fachschaft.GeräteNr
WHERE Fachschaft.Fachschaft = "IF"
ORDER BY Gerät.Anschaffungspreis DESC
d) I) SELECT *
FROM Person INNER JOIN Abteilung ON Person.AbteilungNr =
Abteilung.AbteilungNr
WHERE Abteilung.AbteilungName = "K15" AND Person.Gehalt > 5000
II) SELECT COUNT(*)
FROM Abteilung
WHERE Abteilung.AbteilungOrt = "Köln"
III) SELECT *
FROM Person INNER JOIN Abteilung ON Person.AbteilungNr =
Abteilung.AbteilungNr
WHERE Abteilung.AbteilungOrt = "Köln" AND Person.Ort = "Köln"
IV) SELECT Person.Name, Person.Gehalt, Abteilung.AbteilungName
FROM Person INNER JOIN Abteilung ON Person.AbteilungNr = Abteilung.AbteilungNr
WHERE Person.Beruf = "Programmierer" AND Abteilung.AbteilungOrt = "Köln"
V) SELECT AVG(Person.Gehalt)
FROM Person INNER JOIN Abteilung ON Person.AbteilungNr =
Abteilung.AbteilungNr
WHERE Abteilung.AbteilungName = "K15"
e) I) Alle Namen und Wohnorte der Beschäftigten von Abteilung K15
II) Alle Mitarbeiter-Namen, die Müller als Manager haben.
Aufgabe 2:
Datenbanken
a) Ein Sportverein möchte seine Daten in einer Datenbank organisieren. Zunächst beschränkt
sich der Verein auf die reine Mitgliederverwaltung, welche folgende Informationen
verarbeiten soll:
• Zu jedem Vereinsmitglied soll der Name, welcher sich in Vor- und Nachname
aufschlüsselt und das Geburtsdatum gespeichert werden.
• Für jedes Vereinsmitglied soll die Adresse (aufgeteilt in Straße, Hausnummer,
Postleitzahl und Wohnort) gespeichert werden.
• Zur eindeutigen Identifizierung eines Vereinsmitgliedes soll eine fortlaufende
Nummer gespeichert werden.
• In der Datenbank soll außerdem erfasst werden, ob es sich bei einem Vereinsmitglied
um ein aktives oder passives Mitglied handelt.
o Für alle aktiven Mitglieder wird festgehalten, welche Sportart das Mitglied im
Verein betreibt und welchen Vereinsbeitrag das Mitglied dafür zu zahlen hat.
o Für passive Mitglieder wird festgehalten, welche ehrenamtlichen Tätigkeiten
das Mitglied angenommen hat. Dies können für ein passives Mitglied durchaus
mehrere Tätigkeiten sein.
Entwickeln Sie ein Entity-Relationship-Diagramm, das die beschriebene Struktur darstellt.
Geben Sie im folgenden (also bei allen Diagrammerweiterungen) auch die Kardinalitäten
der Beziehungen an und kennzeichnen Sie Primärschlüsselattribute.
b) Der Sportverein hat gute Erfahrungen mit der Datenbank gemacht und möchte für die
Mitgliederverwaltung durch eine Mannschaftsverwaltung ergänzen.
• Jede Mannschaft ist durch einen eindeutigen Namen identifizierbar und spielt in einer
Liga.
• In jeder Mannschaft spielen nur aktive Mitglieder des Vereins, wobei der Trainer der
Mannschaft stets ein passives Mitglied ist.
• Ein aktives Mitglied kann in mehreren Mannschaften mitspielen, dagegen kann ein
Trainer nur genau eine Mannschaft trainieren.
• Alle Mannschaften sind jeweils eindeutig den verschiedenen Abteilungen des Vereins
untergeordnet. In einer Abteilung können durchaus mehrere Mannschaften existieren.
• Eine Abteilung umfasst alle Mannschaften einer bestimmten Sportart und besitzt einen
eindeutigen Namen.
• Eine Abteilung wird stets von einem aktiven Mitglied geleitet. Dieses Mitglied kann
auch weitere Abteilungen leiten.
Zeichnen Sie ein neues Entity-Relationship-Diagramm, das die gesamte beschriebene
Struktur darstellt. Verzichten Sie bei den Entities aus Teilaufgabe a) auf die AttributAngabe.
c) Der Verein möchte nun zusätzlich alle gespielten Begegnungen seiner Mannschaften in
der Datenbank speichern. Am Ende eines Spieltages füllt dazu der Trainer der Mannschaft
immer den folgenden Spielbericht aus, welcher anschließend in die Datenbank
übernommen wird.
SPIELBERICHT
Heimspiel:
Name der Mannschaft:
Trainer:
Name der gegnerischen
Gastspiel:
(bitte ankreuzen)
___________________________
________________
Mannschaft:
Trainer:
Datum der Begegnung:
Uhrzeit der Begegnung:
Ergebnis:
___________________________
________________
____.____.20____
____:____Uhr
____:____-(Heim : Gast)
Zeichnen Sie ein neues Entity-Relationship-Diagramm, das die beschriebene Struktur
darstellt. Zeichnen Sie lediglich die Entities aus Teilaufgabe a) und b), welche in einer
Beziehung zur neuen Entity stehen. Verzichten Sie bei den Entities aus Teilaufgabe a) und
b) auf die Attribut-Angabe.
Kennzeichnen Sie den Primärschlüssel und begründen Sie Ihre Wahl.
Lösung:
Aufgabe 2:
a - c)
Datenbanken
Geburtsdatum
Nummer
Straße
Vorname
Hausnummer
Name
PLZ
Adresse
Mitglied
Ort
Nachname
is
a
Sportart
is
a
1
aktivesMitglied
n
Beitragssatz
Tätigkeiten
passiivesMitglied
1
1
spielt
in
1
trainiert
m
Name
Liga
Mannschaft
1
leitet
1
n
n
1
gehört
zu
spielt
Abteilung
Name
Trainer
bei
HeimGast
n
spielt
m
HeimErgebnis
n
Begegnung
Datum,Uhrzeit
GegnerMann
GastErgebnis
Sportart
GegnerTrainer
Aufgabe 1:
Datenbanken − ERM, Relationenalgebra
Sie erhalten den Auftrag, für das International Olympic Committee eine Datenbank für die
nächsten Olympischen Spiele zu erstellen, die folgenden Wirklichkeitsausschnitt enthalten
soll.
Die einzelnen Wettkämpfe der Olympischen Spiele sind durch den Namen der Sportart, den
Termin und die Sportstätte gekennzeichnet. An jedem Wettkampf nehmen mehrere Sportler
teil, die durch eine Startnummer identifiziert werden und außerdem einen Namen besitzen.
Jeder Wettkampf wird von einem Schiedsrichter geleitet, dem für diese Spiele eine
eindeutige Personalnummer zugeordnet wurde. Die Schiedsrichter werden bei einem
Wettkampf von verschiedenen Helfern unterstützt, die ebenfalls eine eindeutige
Personalnummer erhalten haben. Die Sportler und Schiedsrichter gehören jeweils einer
Nation an. Zu jeder Nation werden der Name der Nation, der Name des Mannschaftsleiters
sowie eine Telefonnummer für Rückfragen abgespeichert.
a)
Modellieren Sie diesen Ausschnitt der Datenbank im Entity-RelationshipModell. Verwenden Sie die grafische Notation des ER-Diagramms für Ihre Darstellung.
Geben Sie auch die Komplexitäten der Beziehungen an und markieren Sie die
Primärschlüssel. Führen Sie, wenn nötig und sinnvoll, einen im Wirklichkeitsausschnitt
nicht erwähnten Schlüssel ein.
b) Überführen Sie das ER-Modell in das relationale Modell. Optimieren Sie die Anzahl der
verwendeten Relationen.
Bei der Recherche nach einer geeigneten Modellierung Ihrer Datenbank im Internet finden
Sie die folgende Datenbankstruktur, die Ihnen zwar bei den Aufgabenteilen a und b nicht
weiterhilft, aber dennoch interessant ist:
Sportart(SportartNr, SportartName, Spielregel, Mannschaftsgröße, Hallensportart)
Mannschaft(MannschaftNr, MannschaftName, SportartNr, AnzahlSpieler)
spieltIn(SpielerNr, MannschaftNr, Position)
Spieler(SpielerNr, SpielerName, Straße, PLZ, Ort, Telefonnummer)
c) Stellen Sie die Datenbank als ER-Diagramm dar. Geben Sie auch die Komplexitäten an.
d) Geben Sie die Datentypen für die Attribute der Relation Sportart an.
e) Beschreiben Sie, welche Fragestellungen mithilfe der folgenden Operationen der
Relationenalgebra beantwortet werden können.
i) σHallensportart = 1(Sportart)
ii) πPosition(σName = "Basketball"(Sportart)
Mannschaft
spieltIn)
iii) πMannschaftName(σPLZ = 51519(Spieler)
σPosition = "Kapitän"(spieltIn)
Mannschaft)
f) Geben Sie die Operationen der Relationenalgebra an, mithilfe derer folgende
Informationen ermittelt werden können:
i) Alle Sportartnamen und deren Spielregeln
ii) Alle Sportarten, deren Mannschaften aus 2 Spielern bestehen.
iii) Alle Spielernamen, deren Position "Torwart" lautet und deren Mannschaft aus 7
Spielern besteht.
iv) Alle Orte, in denen Spieler wohnen, welche eine Hallensportart ausüben.
Lösung:
Datenbanken – ERM, Relationenalgebra
Aufgabe 1:
a)
Name
Personalnr
Startnr
Sportler
n
Schiedsrichter
n
ist
von
1
nimmt
teil
1
Helfer
n
Personalnr
ist
von
n
Wettkampf
Termin
wird
unterst.
n
leitet
1
Wettkampfnr
1
Sportart
Sportstätte
1
Nation
1
Telefonnr
Name
Manns.leiter
b) Sportler(Startnr, Name, Wettkampfnr, Nationname)
Schiedsrichter(Personalnr, Nationname)
Helfer(Personalnr, SchiedsrichterNr)
Wettkampf(Wettkampfnr, Termin, Sportstätte, Sportart, SchiedsrichterNr)
Nation(Name, Mannschaftsleiter, Telefonnr)
c)
SportartName
Spielregel
Mann.größe
SportartNr
Sportart
1
Hallensportart
spielt
Position
n
MannschaftsNr
Mannschaft
n
spielt
in
SpielerNr
m
Spieler
SpielerName
Straße
Mann.Name
AnzahlSpieler
Telefonnr
PLZ
d) SportartNr: INT
SportartName: VARCHAR(20)
Spielregel: VARCHAR(500)
Ort
Mannschaftsgröße: INTEGER
Hallensportart: BOOL
e) i) Alle Informationen über die Sportarten, die in der Halle gespielt werden.
ii) Alle Positionen, welche in einer Basketball-Mannschaft gespielt werden.
iii) Alle Mannschaftsnamen, deren Mannschaft einen Kapitän aus dem PLZ-Bezirk 51519
hat.
f) i)
ii)
iii)
iv)
πSportartName, Spielregel(Sportart)
σAnzahlSpieler=2(Mannschaft)
Sportart)
πSpielerName(Spieler
σPosition = "Torwart"(spieltIn)
σAnzahlSpieler=7(Mannschaft))
πOrt(Spieler
spieltIn
Mannschaft
σHallensportart = 1(Sportart))
Datenbanken − Normalisierung, SQL
Aufgabe 2:
Ein Unternehmen bietet ausschließlich Städtereisen an. Für verschiedene europäische Städte
existieren bestimmte Reiseangebote (jeweils genau eines), die Eigenschaften wie den Namen
der Stadt, den (konstanten) Preis sowie die Reisedauer (Anzahl der Übernachtungen) besitzen.
Für jedes Reiseangebot existieren verschiedene terminliche Ausprägungen (Reisen). Eine
solche konkrete Reise wird jeweils von genau einem Busfahrer durchgeführt. Die Busfahrer
des Unternehmens besitzen jeweils nur Kenntnisse für bestimmte Städte (Reiseangebote);
dementsprechend besitzen sie nur die Fähigkeit zur Durchführung bestimmter Reisen. Die
Kunden des Unternehmens sind ebenfalls in dem System abzubilden. Hierbei ist auch
abzubilden, welche Kunden welche konkreten Reisen gebucht haben.
Ein Vorschlag für die Modellierung im relationalen Modell lautet wie folgt:
Dauer Reisen (Nr, Termin)
Reiseangebote
Stadtname Preis
Paris
275,00 € 5
(121, 26.09.2009), (312, 15.10.2010), ...
London
545,00 € 6
(541, 12.12.2009), ...
...
Busfahrer
Kunden
Fahrername
Lukas Lunert
Lukas Lunert
Paul Parker
…
Stadtnamen
Paris
London
Paris
Kunden- Name
nummer
123456
Anton Acker
451271
...
Berta Burkert
fährt
Fahrername
Lukas Lunert
Lukas Lunert
Paul Parker
…
ReiseNr
121
541
312
Adresse
Reisen (Nr)
Roggenacker 23,
51519 Odenthal
Bananenweg 3,
51467 Bergisch Gladbach
(121), (541), (388),...
(121), (312), ...
a) Begründen Sie, dass mit dieser relationalen Datenbank der oben beschriebene
Wirklichkeitsausschnitt modelliert wurde. Gehen Sie dabei insbesondere auf die
Modellierung der Komplexitäten ein.
b) Begründen Sie mithilfe der Definition, dass sich diese Datenbank noch nicht in der ersten
Normalform befindet.
Überführen Sie anschließend die relationale Datenbank in die erste Normalform.
c) Geben Sie eine Definition dafür an, dass sich eine Datenbank in der zweiten Normalform
befindet.
d) Geben Sie eine Definition dafür an, dass sich eine Datenbank in der dritten Normalform
befindet.
Überführen Sie die Datenbank anschließend in die dritte Normalform.
e) Analysieren Sie die folgende SQL-Anweisung und erläutern deren Zweck.
i) SELECT Busfahrer.Fahrername
FROM Busfahrer
INNER JOIN Reiseangebote ON Busfahrer.Stadtname = Reiseangebot.Stadtname
WHERE Reiseangebot.Preis < 300.00
ii) SELECT Dauer, AVG(Preis) AS D_Preis
FROM Reiseangebote
GROUP BY Dauer
f)
Implementieren Sie SQL-Anweisungen auf Basis ihrer Datenbank in dritter
Normalform für folgende Abfragen:
i) Alle Reisetermine zur Stadt Paris.
ii) Alle Orte (PLZ reicht aus) in denen Kunden wohnen, die eine Reise nach Paris
gebucht haben.
iii) Alle Kundendaten der Kunden, die von "Lukas Lunert" zu ihrem Reiseziel geführt
wurden.
iv) Den Namen des Kunden mit der kleinsten Kundennummer.
v) Alle Busfahrernamen, die eine Reise leiten, welche mindestens 7 Tage dauert und
deren Reiseziel mit "P" anfängt (z. B. Prag, Paris, Podgorica, ...).
Lösung:
Aufgabe 2: Datenbanken – Normalisierung, SQL
a) Ein Reiseangebot gehört zu einer eindeutigen Stadt und hat einen festen Preis und eine
feste Dauer. Die einzelnen Reisen (terminliche Ausprägungen mit deren Nummer) werden
als Mehrfachattribut gespeichert. Damit können zu einem Angebot mehrere Reisen
gespeichert werden.
Die Busfahrer haben einen Namen und können (realisiert durch ein Mehrfachattribut)
beliebig viele Städte anfahren.
Welche Reisen nun tatsächlich von den Busfahrern gefahren werden steht in der Relation
fährt. Hier ist es möglich für jede Zuordnung Fahrer-Reise eine neue Zeile anzulegen.
Die Kundendaten Name, Adresse und Kundennummer aller Kunden und deren jemals
gebuchten Reisen (realisiert durch Mehrfachattribut) werden in der Tabelle Kunden
gespeichert.
b) Die Datenbank befindet sich nicht in erster Normalform, da nicht alle Tabellen über
Primärschlüssel verfügen sowie die Attribute nicht atomar sind.
Reiseangebote(Stadtname, Preis, Dauer)
Reisen(Stadtname, ReiseNr, Termin, FahrerNr)
Busfahrer(FahrerNr, Vorname, Nachname)
kannFahren(FahrerNr, Stadtname)
Kunden(KundenNr, Vorname, Nachname, Straße, PLZ, Ort)
gebucht(KundenNr, ReiseNr)
c) Eine Datenbank befindet sich in der 2. Normalform, wenn sie sich in der ersten
Normalform befindet und jedes Nichtschlüsselattribut vom gesamten Primärschlüssel
abhängig ist.
d) Eine Datenbank befindet sich in der 3. Normalform, wenn sie sich in der zweiten
Normalform befindet und jedes Attribut direkt (und nicht transitiv) vom Gesamtschlüssel
abhängig ist.
Dies ist hier nicht der Fall, da z. B. Ort nur von PLZ abhängig ist.
Kunden(KundenNr, Name, Straße, PLZ)
Orte(PLZ, Ort)
e) i) Zuerst werden die Tabellen Busfahrer und Reiseangebote gejoint. Damit erhalten wir
alle Informationen über die Möglichen Kombinationen Stadt-Busfahrer. Hiervon
werden nun nur die Reisen ausgewählt, deren Preis unter 300 Euro liegt. Abschließend
wird projiziert auf den Namen des Busfahrers.
Es werden alle Busfahrer ausgegeben, die eine Stadt anfahren können, deren
Reisepreis unter 300 Euro liegt.
ii) Die Daten der Tabelle Reiseangebote werden nach ihrer Dauer gruppiert. Von jeder
Gruppe wird die Dauer und der durchschnittliche Reisepreis aller zu dieser Gruppe
gehörenden Reisen ausgegeben. Der durchschnittliche Reisepreis wird in einem
Attribut D_Preis ausgegeben.
f) i)
select Termin
from Reisen
where Stadtname = "Paris"
ii)
select Kunden.PLZ
from Kunden
inner join gebucht on Kunden.KundenNr = gebucht.KundenNr
inner join Reise on gebucht.ReiseNr = Reise.ReiseNr
where Reise.Stadtname = "Paris"
iii)
select Kunden.*
from Kunden
inner join gebucht on Kunden.KundenNr = gebucht.KundenNr
inner join Reise on gebucht.ReiseNr = Reise.ReiseNr
inner join Busfahrer on Reise.FahrerNr = Busfahrer.FahrerNr
where Busfahrer.Vorname = "Lukas"
and Busfahrer.Nachname = "Lunert"
iv)
select Vorname, Nachname
from Kunden
where KundenNr = (
select min(KundenNr)
from Kunden
)
v)
select Busfahrer.Nachname, Busfahrer.Vorname
from Busfahrer
inner join Reise on Busfahrer.FahrerNr = Reise.FahrerNr
inner join Reiseangebot on Reise.Stadtname = Reiseangebot.Stadtname
where Reiseangebot.Dauer >= 7
and Reiseangebot.Stadtname like "P%"