Datenbanksprache (SQL) - BFH

Vorlesung Datenbanken: Übungen
P. Fierz / FS 2014
Kapitel 5
Datenbanksprache (SQL)
5.1
Das Entity-Relationship Diagramm
ist_vorgesetzter
0..1
0..*
<<Entity>>
Mitarbeiter
<<PK>> mNr : long
Name : string
AhvNr : string
Plz : string
Ort : string
Geschlecht : {M,F}
Geburtsdatum : date
1..*
gehört_zu
1
leitet
1
0..1
<<Entity>>
Abteilung
<<PK>> aNr : long
AName : string
Standort : string [1..*]
<<Entity>>
Leitung
ldate : date
0..*
1
0..*
<<Entity>>
Angehöriger
AGeburtsdatum : d...
AGeschlecht : {M,F}
Partner : boolean
führt_durch
arbeitet_für
0..*
<<Entity>>
Projekt
<<PK>> pNr : long
PName : string
PStandort : string
0..*
<<Entity>>
Anteil
WochenStunden : float
5.2
Das relationen Modell
Mitarbeiter
= ({mNr, Name, AhvNr, Plz, Ort, Geschlecht,
Geburtsdatum, aNr, Vorgesetzter nw},
{mNr},
ks({AhvNr}),
fs({Vorgesetzter}, Mitarbeiter, null),
fs({aNr}, Abteilung, res))
Angehoeriger = ({mNr, Vorname, AGeburtsdatum, AGeschlecht, Partner},
{mNr, Vorname},
fs({mNr}, Mitarbeiter, cas))
Abteilung
= ({aNr, AName, mNr, LeiterSeit},
{aNr},
5-1
Vorlesung Datenbanken: Übungen
P. Fierz / FS 2014
ks({AName}),
ks({mNr}),
fs({mNr}, Mitarbeiter, res))
Standort
= ({aNr, Standort},
{aNr, Standort},
fs({aNr}, Abteilung, cas))
Projekt
= ({pNr, PName, paNr, PStandort},
{pNr},
ks({PName}),
fs({paNr}, Abteilung, res))
MitProj
= ({mNr, pNr, WochenStunden},
{mNr, pNr},
fs({mNr}, Mitarbeiter, cas),
fs({pNr}, Projekt, cas))
Bemerkung 5.1 [Implementation]
• Das Mehrwertige Attribut “Standort” in Abteilung ist durch die Relation “Standort”
implementiert.
• Die Beziehung “arbeitet_für“ ist durch die Relation ”MitProj“ implementiert.
5.3
Queries
Aufgabe 5.1 [Queries Firma] Geben Sie SQL Ausdrücke an, welche die folgenden Aufgaben
lösen.
a) Bestimmen von mNr und Name aller Mitarbeiter, die in Bern wohnen und für die kein
Vorgesetzter eingetragen ist.
b) Bestimmen von mNr, Name, LeiterSeit, aNr und AName aller Abteilungleiter, die die
Abteilung leiten zu der sie gehören.
c) Bestimmen von mNr, Name, LeiterSeit, aNr und AName aller Abteilungleiter, die die
Abteilung leiten zu der sie nicht gehören.
d) Erstellen Sie eine Liste aller Mitarbeiter mit mNr, Name und die Anzahl Projekte an
denen diese Arbeiten. Mitarbeiter, die an keinem Projekt arbeiten sollen in der Liste
mit 0 Projekten erscheinen. Die Liste soll nach der Anzahl Projekte sortiert sein.
Lösen Sie die Aufgabe auch ohne den OUTER JOIN Befehl.
e) Bestimmen von mNr und Name aller Mitarbeiter, die ein Angehoeriger mit Vorname =
Jana haben.
f) Bestimmen von aNr und AName aller Abteilungen, die sowohl Thun wie auch Zuerich
als Standort haben.
5-2
Vorlesung Datenbanken: Übungen
P. Fierz / FS 2014
g) Bestimmen von aNr und AName aller Abteilungen, die genau Thun und Bern als Standort haben.
h) Bestimmen von mNr, Name, pNr, PName, Wochenstunden und Pstandort aller Projekte
mit den entsprechenden Mitarbeiter. Projekte an denen noch keine Mitarbeiter beteiligt
sind sollen in der Liste auch erscheinen.
i) Bestimmen aller Abteilungen, für die sowohl Mitarbeiter aus der Abteilung 1 wie auch
aus der Abteilung 3 an Projekten mitarbeiten.
j) Bestimmen aller Abteilungen, die ein Projekt führen für das sowohl Mitarbeiter der
Abteilung 1 wie der Abteilung 4 arbeiten.
k) Bestimmen von mNr, Name und AhvNr aller Mitarbeiter, die nur an Projekte arbeiten
die durch ihre Abteilung geführt werden.
l) Bestimmen aller Paare (mNr1, mNr2) von Mitarbeitern, die mindestens an einem
gemeinsamen Projekt arbeiten.
m) Bestimmen aller Mitarbeiter (mNr, Name), die Vorgesezte von Mitarbeitern sind, die
selbst auch Vorgesetzte von Mitarbeitern sind.
n) Bestimmen aller Mitarbeiter (mNr, Name), die Vorgesezte von Mitarbeitern sind, die
selber keine Vorgesetzten sind.
o) Bestimmen von pNr, PName, PStandort, paNr und AName der Projekte, an denen nur
Mitarbeiter der Abteilung arbeiten, die das Projekt führt.
p) Bestimmem der Abteilungen, die mindestens dieselben Standorte haben wie die Abteilung
mit aNr=1.
q) Bestimmen der Paare (mNr1, Name1, mNr2, Name2) von Mitarbeitern die genau an
denselben Projekten arbeiten. Zusätzlich soll die gemeinsame Anzahl Projekte ausgegeben
werden.
5-3