Übungsblatt 4 - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. M. Endres, Dr. S. Mandl, F. Wenzel
WS 2011/2012
25. Nov. 2011
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)))
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,N r,N ame,W ohnort ((ρP N r←N r (σOrt=′ W eimar′ (P rojekt)))
⋊
⋉AbtN r=N r (ρAN ame←N ame (Abteilung)) ⋊
⋉Manager=P ersN r M itarbeiter)
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 ) ∧
∃y1 ∃y3 (Abteilung(y1,′ F orschung ′ , y3 ) ∧ y1 = x5 ))}
Sichere DRC-Anfrage: Die Anfrage ist sicher, da x1 , x4 , x5 , y1 , 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 ∃z1 ∃z2 ∃z3 (P rojekt(x1 , x2 , x3 , x4 ) ∧
M itarbeiter(y1 , y2 , y3 , y4 , y5 ) ∧ Abteilung(z1, z2 , z3 ) ∧ x3 =′ W eimar′ ∧ x4 = z1 ∧ y1 = z3 )}
Sichere DRC-Anfrage: Die Anfrage ist sicher, da x2 , x3 , y1 , y4 , y5 , z1 , z2 , z3 durch Quantoren gebunden sind
und ferner x1 , x4 , y2 , y3 in deren entsprechenden maximalen Teilformeln weder in der Form t1 θt2 noch negiert
vorkommen. Die freien Variablen der restlichen maximalen Teilformeln sind ebenfalls beschränkt, nämlich x4
und z1 durch den Vergleich zweier bereits beschränkter Variablen.
1
Aufgabe 2: Count-Bug
Lösungen:
a) Relation erstellen:
CREATE TABLE R (
dept_nr INTEGER,
emp_nr INTEGER,
job varchar(50),
PRIMARY KEY (dept_nr, emp_nr));
b) Tupel einfügen:
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
R
R
R
R
R
VALUES(1,1,’programmer’);
VALUES(1,2,’clerk’);
VALUES(2,3,’clerk’);
VALUES(2,4,’clerk’);
VALUES(3,5,’sales’);
c) Der erste intuitive Ansatz
SELECT dept_nr
FROM emp
WHERE job = ’clerk’
GROUP BY dept_nr
HAVING COUNT(*) <= 2
schlägt fehl. In der WHERE-Klausel werden alle Einträge außer denen mit job = ’clerk’ eliminiert. Dadurch fallen
sämtliche Einträge zum Department 3 weg. Das Department 3 fehlt also auch im Ergebnis, obwohl es rein intuitiv
auch gesucht war.
Ein anderer Lösungsansatz
SELECT
FROM
GROUP
HAVING
dept#
R
BY dept#
COUNT(*) <= 2 AND job = ’clerk’;
ist syntaktisch falsch, da Nicht-Gruppierungsattribute wie job nur in Form von Aggregationsausdrücken in einer
HAVING-Klausel vorkommen dürfen.
Korrekte SQL-Anfrage:
(SELECT dept_nr FROM R)
MINUS
(SELECT dept_nr FROM R
WHERE job = ’clerk’
GROUP BY dept_nr
HAVING COUNT(*) > 2);
2
d) Ein ähnliches Phänomen lässt sich in der s inventory Relation beobachten. Gegeben sei dazu folgende Query:
SELECT warehouse_id
FROM s_inventory
WHERE product_id =’10013’ AND amount_in_stock <1
GROUP BY warehouse_id;
Gesucht werden hier alle Lager, in denen der Artikel mit Nummer 10013 nicht vorrätig ist, also auch diejenigen,
die den entsprechenden Artikel nicht führen. Die Query gibt jedoch ein leeres Ergebnis zurück, da Lager 101
noch 400 Exemplare des Artikels vorrätig hat, Lager 10501 noch 314. Dabei bleiben Lager 201, 301 und 401
unberücksichtigt, da diese den Artikel generell nicht im Sortiment haben. Laut Verbalisierung der gewünschten
Query sollten aber auch diese Lager im Ergebnis enthalten sein.
Dieses Beispiel verdeutlicht nochmals, dass der sogenannte Count-Bug nicht ein inhärentes Problem der CountFunktion ist, sondern vielmehr ein generelles Problem von SQL darstellt. Analog zu c) liefert folgende Query das
gewollte Ergebnis:
(SELECT warehouse_id FROM s_inventory)
MINUS
(SELECT warehouse_id FROM s_inventory
WHERE product_id=’10013’AND
amount_in_stock>1
GROUP BY warehouse_id);
Hinweis: Die in diesem Lösungsblatt vorgestellten SQL-Queries sind alle auf das Sporting Goods Schema anwendbar.
Bei der Ausführung der folgenden Musterlösungen ist jedoch zu beachten, dass die kursiven einfachen Anführungszeichen
durch die entsprechenden von SQL gewünschten einfachen Anführungszeichen ersetzt werden müssen.
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 "DEP 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_customer c , s_emp e
WHERE c.sales_rep_id = e.id);
3
d) Liste aller Kunden mit GOOD oder EXCELLENT Kredit-Rating:
SELECT name
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;
4