04loesung - Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
F. Wenzel, L. Rudenko
WS 2014/2015
14. Nov. 2014
Lösungsblatt 4
Datenbanksysteme I
Aufgabe 1: Relationale Algebra und Relationen-Kalkül
Antworten:
a) Relationale Algebra:
i) Selektiere Name und Wohnort der Mitarbeiter, die in der Forschung arbeiten.
πN ame,W ohnort (M itarbeiter ⋊
⋉AbtN r=N r (σAbtN ame=′ F orschung′ (Abteilung)))
Bemerkung Alternativ ist es auch möglich, die Selektion erst nach dem Join auszuwerten. Bei der Optimierung (späteres Kapitel) werden wir darauf noch genauer eingehen.
ii) Liefere für jedes Projekt in Weimar dessen Nummer, die Nummer der durchführenden Abteilung sowie Name
und Wohnort des Abteilungsmanagers.
πP N r,AbtN r,N ame,W ohnort ((ρP N r←N r (σOrt=′ W eimar′ (P rojekt)))
⋊
⋉AbtN r=N r (Abteilung) ⋊
⋉Manager=P ersN r M itarbeiter)
Bemerkung Der Operator ρa←b benennt ein Attribut b in a um (äquivalent zum ’AS’ Operator in SQL).
b) Domänen-Kalkül:
i) Selektiere Name und Wohnort der Mitarbeiter, die in der Forschung arbeiten
{(x2 , x3 )|∃x1 ∃x4 ∃x5 (M itarbeiter(x1 , x2 , x3 , x4 , x5 ) ∧ ∃y3 (Abteilung(x5 ,′ F orschung ′ , y3 )))}
Sichere DRC-Anfrage: Die Anfrage ist sicher, da x1 , x4 , x5 , y3 durch Quantoren gebunden sind und ferner
x2 , x3 in der entsprechenden maximalen Teilformel der Konjunktion weder in der Form x2 θx3 noch negiert
vorkommen. Somit sind alle vorkommenden freien Variablen beschränkt.
ii) Liefere für jedes Projekt in Weimar dessen Nummer, die Nummer der durchführenden Abteilung sowie Name
und Wohnort des Abteilungsmanagers.
{(x1 , x4 , y2 , y3 ) | ∃x2 ∃x3 ∃y1 ∃y4 ∃y5 ∃z2 (P rojekt(x1 , x2 , x3 , x4 ) ∧
M itarbeiter(y1 , y2 , y3 , y4 , y5 ) ∧ Abteilung(x4 , z2 , y1 ) ∧ x3 =′ W eimar′ )}
Sichere DRC-Anfrage: Die Anfrage ist sicher, da x2 , x3 , y1 , y4 , y5 , z2 durch Quantoren gebunden sind und
ferner x1 , x4 , y2 , y3 in deren entsprechenden maximalen Teilformeln der Konjunktion weder in der Form
t1 θt2 noch negiert vorkommen.
1
Aufgabe 2: Domänenkalkül
⋉b.P reis<a.P reis lief ert b)
a) πa.ArtN ame σb.ArtN ame=′ ON KO′ (lief ert a ⋊
b) Auswertung der Anfragen
i) Relationale Algebra:
Der Join liefert folgendes Zwischenergebnis:
a.LName
’Meier’
’Meier’
’Meier’
’Meier’
’Meier’
’Meier’
’Meier’
’Huber’
’Huber’
’Huber’
a.ArtName
’Melitta’
’Melitta’
’Melitta’
’Melitta’
’ONKO’
’ONKO’
’ONKO’
’ONKO’
’ONKO’
’Melitta’
a.Preis
6.00
6.00
6.00
6.00
5.99
5.99
5.99
5.89
5.89
2.88
b.LName
’Meier’
’Meier’
’Huber’
’Huber’
’Meier’
’Huber’
’Meier’
’Meier’
’Huber’
’Meier’
b.ArtName
’ONKO’
’Philips’
’ONKO’
’Melitta’
’Philips’
’ONKO’
’Melitta’
’Philips’
’Melitta’
’Philips’
b.Preis
5.99
0.31
5.89
2.88
0.31
5.89
2.88
0.31
2.88
0.31
Nach Anwendung der Selektion bleiben noch Tupel 1, 3 und 6 übrig, die anschließende Projektion liefert das
Endergebnis: {′ M elitta′,′ ON KO′ }
ii) Zum Domänenkalkül - wir werten zunächst nur den Join aus, ohne Selektion (siehe dazu auch Hinweis unten):
W(liefert(’Meier’, ’Melitta’, 6.00)∧liefert(’Meier’, ’Melitta’, 6.00)∧6.00 < 6.00)→false
W(liefert(’Meier’, ’Melitta’, 6.00)∧liefert(’Meier’, ’ONKO’, 5.99)∧5.99 < 6.00)→true
W(liefert(’Meier’, ’Melitta’, 6.00)∧liefert(’Meier’, ’Philips’, 0.31)∧0.31 < 6.00)→true
W(liefert(’Meier’, ’Melitta’, 6.00)∧liefert(’Huber’, ’ONKO’, 5.89)∧5.89 < 6.00)→true
W(liefert(’Meier’, ’Melitta’, 6.00)∧liefert(’Huber’, ’Melitta’, 2.88)∧2.88 < 6.00)→true
W(liefert(’Meier’, ’ONKO’, 5.99)∧liefert(’Meier’, ’Melitta’, 6.00)∧6.00 < 5.99)→false
W(liefert(’Meier’, ’ONKO’, 5.99)∧liefert(’Meier’, ’ONKO’, 5.99)∧5.99 < 5.99)→false
W(liefert(’Meier’, ’ONKO’, 5.99)∧liefert(’Meier’, ’Philips’, 0.31)∧0.31 < 5.99)→true
W(liefert(’Meier’, ’ONKO’, 5.99)∧liefert(’Huber’, ’ONKO’, 5.89)∧5.89 < 5.99)→true
W(liefert(’Meier’, ’ONKO’, 5.99)∧liefert(’Huber’, ’Melitta’, 2.88)∧2.88 < 5.99)→true
W(liefert(’Meier’, ’Philips’, 0.31)∧liefert(’Meier’, ’Melitta’, 6.00)∧6.00 < 0.31)→false
W(liefert(’Meier’, ’Philips’, 0.31)∧liefert(’Meier’, ’ONKO’, 5.99)∧5.99 < 0.31)→false
W(liefert(’Meier’, ’Philips’, 0.31)∧liefert(’Meier’, ’Philips’, 0.31)∧0.31 < 0.31)→false
W(liefert(’Meier’, ’Philips’, 0.31)∧liefert(’Huber’, ’ONKO’, 5.89)∧5.89 < 0.31)→false
W(liefert(’Meier’, ’Philips’, 0.31)∧liefert(’Huber’, ’Melitta’, 2.88)∧2.88 < 0.31)→false
W(liefert(’Huber’, ’ONKO’, 5.89)∧liefert(’Meier’, ’Melitta’, 6.00)∧6.00 < 5.89)→false
W(liefert(’Huber’, ’ONKO’, 5.89)∧liefert(’Meier’, ’ONKO’, 5.99)∧5.99 < 5.89)→false
W(liefert(’Huber’, ’ONKO’, 5.89)∧liefert(’Meier’, ’Philips’, 0.31)∧0.31 < 5.89)→true
W(liefert(’Huber’, ’ONKO’, 5.89)∧liefert(’Huber’, ’ONKO’, 5.89)∧5.89 < 5.89)→false
W(liefert(’Huber’, ’ONKO’, 5.89)∧liefert(’Huber’, ’Melitta’, 2.88)∧2.88 < 5.89)→true
W(liefert(’Huber’, ’Melitta’, 2.88)∧liefert(’Meier’, ’Melitta’, 6.00)∧6.00 < 2.88)→false
W(liefert(’Huber’, ’Melitta’, 2.88)∧liefert(’Meier’, ’ONKO’, 5.99)∧5.99 < 2.88)→false
W(liefert(’Huber’, ’Melitta’, 2.88)∧liefert(’Meier’, ’Philips’, 0.31)∧0.31 < 2.88)→true
W(liefert(’Huber’, ’Melitta’, 2.88)∧liefert(’Huber’, ’ONKO’, 5.89)∧5.89 < 2.88)→false
W(liefert(’Huber’, ’Melitta’, 2.88)∧liefert(’Huber’, ’Melitta’, 2.88)∧2.88 < 2.88)→false
Nach der Selektion von ’ONKO’ bekommen wir auch hier das Endergebnis: {′ M elitta′,′ ON KO′ }
Hinweis: Im Vorgriff auf Kapitel 6 (Algebraische Query-Optimierung) lässt sich die Query in Relationaler Algebra
auch wie folgt schreiben:
πa.ArtN ame (lief ert a ⋊
⋉b.P reis<a.P reis (σb.ArtN ame=′ ON KO′ (lief ert b)))
2
Aufgabe 3: SQL-Queries
Lösungsvorschläge:
a) Name aller Angestellten, die in Abteilung 41 arbeiten, alphabetisch sortiert nach Nachname:
SELECT last_name, first_name
FROM s_emp
WHERE dept_id = 41
ORDER BY last_name, first_name;
b) Nachname und Vorname aller Angestellten samt Name der Abteilung, in der sie arbeiten sowie der dazugehörigen
Region:
SELECT s_emp.last_name, s_emp.first_name,
s_dept.name AS DEPT_NAME, s_region.name AS REGION
FROM s_emp, s_dept, s_region
WHERE s_emp.dept_id = s_dept.id AND s_dept.region_id = s_region.id;
c) Name aller Kunden, die noch keinem Kundenbetreuer zugeordnet wurden:
SELECT s_customer.name
FROM s_customer
WHERE s_customer.id NOT IN
(SELECT c.id FROM s_emp e, s_customer c WHERE e.id = c.sales_rep_id);
d) Liste aller Kunden mit GOOD oder EXCELLENT Kredit-Rating:
SELECT *
FROM s_customer
WHERE credit_rating = ’GOOD’ OR credit_rating = ’EXCELLENT’;
e) Daten aller Lager samt zugehöriger Manager plus Angestellte, die kein Manager eines Lagers sind:
SELECT w.id, w.address , w.city, w.state, w.country, e.last_name, e.first_name
FROM s_warehouse w RIGHT OUTER JOIN s_emp e
ON w.manager_id = e.id;
3