Übungsblatt 5 - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
F. Wenzel, D. Köppl
WS 2013/2014
22. November 2013
Lösungsblatt 5
Datenbanksysteme I
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 1: Aggregatsfunktionen, Self-Joins und NULL-Werte
Lösungsvorschläge:
a) Bei der Ansicht von s emp wird deutlich, dass nur für vier Mitarbeiter ein Wert für commission pct definiert ist,
für alle anderen Mitarbeiter ist dieser Wert NULL. Die Aggregatsfunktion AVG() berechnet den Durchschnitt aller
vorhandenen Werte, NULL-Werte werden dabei also nicht berücksichtigt:
SELECT DISTINCT AVG(commission_pct) FROM s_emp;
Diese Anfrage liefert als Ergebnis 12.5 zurück, da dies der korrekte Durchschnitt der 4 eingetragenen KommissionsWerte ist. Will man allerdings den Durchschnitt über alle Mitarbeiter bilden, einen NULL-Wert also als 0 interpretieren, so muss der Durchschnitt selbst gebildet werden:
SELECT DISTINCT (SELECT DISTINCT sum(commission_pct) FROM s_emp) /
(SELECT DISTINCT count(*) from s_emp) FROM s_emp;
Diese Anfrage liefert entsprechend den Wert 2 als Durchschnitt zurück.
b) Die Manager unter den Mitarbeitern lassen sich durch einen Selbst-Join bestimmen:
SELECT DISTINCT m.* FROM s_emp e, s_emp m WHERE e.manager_id = m.id;
c) Anfrage mit Subquery:
SELECT DISTINCT id from s_emp WHERE id NOT IN
(SELECT DISTINCT manager_id FROM s_emp WHERE manager_id IS NOT NULL);
Der Zusatz IS NOT NULL in der Subquery ist entscheidend, da die Subquery sonst NULL in der Ergebnismenge
enthält. Ein Vergleich mit NULL liefert immer unknown zurück, somit wäre das Gesamtergebnis der Haupt-Query
leer.
Anfrage mit MINUS:
(SELECT DISTINCT id FROM s_emp) MINUS (select DISTINCT manager_id FROM s_emp);
d) Um die geforderte Anzahl zu ermitteln ist eine einfache Anfrage offensichtlich:
SELECT DISTINCT count(*) FROM s_emp WHERE commission_pct <= 12.5;
Auch hier stellt sich wieder die Frage, wie NULL-Werte interpretiert werden sollen. Soll NULL als 0 gesehen
werden, so ist eine erweiterte Anfrage notwendig:
SELECT DISTINCT count(*) FROM s_emp WHERE id NOT IN
(SELECT DISTINCT id from s_emp WHERE commission_pct > 12.5);
1
Aufgabe 2: Umwandlung von SQL-Anfragen in relationale Algebra
Lösungsvorschläge:
a) πid (σprice≥30 (s item))
b) πname,city,state (ρEid←id (s emp) ./sales rep id=id (σcountry=0 U S 0 (s customer)))
c) πid (s region)\πregion id (s dept)
Aufgabe 3: Datendefinition und Integritätsbedingungen
Lösungsvorschläge:
a) Erstellen der gewünschten Relation:
CREATE TABLE programmer
(EmpNo VARCHAR(3),
Last_Name VARCHAR(25) NOT NULL,
First_Name VARCHAR(25),
Hire_Date Date,
Project NUMBER(3),
Language VARCHAR(15),
TaskNo NUMBER(2),
Clearance VARCHAR(25),
CONSTRAINT programmer_pk PRIMARY KEY(EmpNo)
);
b) Die project Relation wird folgendermaßen erstellt:
CREATE TABLE project
(project_id NUMBER(3),
project_name VARCHAR(50),
CONSTRAINT project_pk PRIMARY KEY(project_id)
);
c) Anschließend wird ein Foreign-Key in der programmer Relation hinzugefügt:
ALTER TABLE programmer ADD CONSTRAINT programmer_fk FOREIGN KEY(Project)
REFERENCES project(project_id) ON DELETE CASCADE;
Wird ein Projekt Tupel aus project gelöscht, so werden über die Foreign-Key Bedingung ON DELETE CASCADE
auch verbundene Tupel in programmer gelöscht.
d) Erweitern der project Relation:
ALTER TABLE project ADD(manager_id INTEGER);
Dezimieren der programmer Relation:
ALTER TABLE programmer DROP(clearance);
2
e) Einfügen von Tupeln in die project Relation:
INSERT INTO project VALUES(1, ’projekt1’, 22);
INSERT INTO project VALUES(2, ’projekt2’, 21);
INSERT INTO project VALUES(3, ’projekt3’, 22);
Einfügen von Tupeln in die programmer Relation:
INSERT INTO programmer VALUES(’1’, ’Maier’ , ’Hans’,
TO_DATE(’04-03-1983’, ’MM-DD-YYYY’), 1, ’Java’, 44);
INSERT INTO programmer VALUES(’2’, ’Huber’ , ’Paul’,
TO_DATE(’11-23-2004’, ’MM-DD-YYYY’), 2, ’C++’, 43);
Löschen des Eintrags von Projekt 1:
DELETE FROM project WHERE project_id=1;
Beim Löschen von Projekt 1 werden alle Programmierer aus programmer gelöscht, die über den Fremdschlüssel
mit Projekt 1 verbunden sind. Dies war in Teilaufgabe c) bereits explizit so vorgesehen.
Aufgabe 4: Drei-wertige Logik
a) Werten Sie nun folgende Ausdrücke aus:
i) Da t ∧ ω = ω und ω ∧ f = f folgt σa∧b (R) = ∅
ii) Da ¬(t ∨ ω) = ¬t = f und ¬(f ∨ ω) = ¬ω = ω folgt σ¬(a∨b) (R) = ∅
iii) Da ¬t ∨ ¬ω = f ∨ ω = ω und ¬ω ∨ ¬f = ω ∨ t = t folgt σ¬a∨¬b (R) = {(ω, f )}
b) Wir wählen 0 für f und 1 für t.
i) σa6=ω (R)
• SELECT * FROM R WHERE a IS NOT NULL;
• Rückgabe: {(1, ω)}
ii) σ¬(a=t) (R)
• Da die Formel F (a) := ¬(a = t) für a = ω den Wert F (a) = t hat, brauchen wir eine zusätzliche
Abfrage:
• SELECT * FROM R WHERE a IS NULL OR a <> 1;
• Rückgabe: {(ω, f )}
iii) σ¬b=¬f (R)
• Da F (b) := (¬b = ¬f ) ⇔ F (b) = (¬b = t) ⇔ F (b) = (b = f ) (Achtung: F (b) = (b 6= t) ist falsch!)
haben wir
• SELECT * FROM R WHERE b = 0;
• Rückgabe: {(ω, f )}
3