Übungsblatt 5 - Informatik

Universität Augsburg, Institut für Informatik
Dr. W.-T. Balke
M. Endres, A. Huhn, T. Preisinger
WS 2006/2007
27. Nov. 2006
Lösungsblatt 5
Datenbanksysteme I
Aufgabe 1: Projektion
πA1 ,...,An (πB1 ,...,Bm (R)) = πA1 ,...,An (R) gilt, wenn {Ai | i = 1, ..., n} ⊆ {Bi | i = 1, ..., m}, d.h.
wenn die Attribute Ai in der Menge der Attribute Bi enthalten sind.
Aufgabe 2: Division in relationaler Algebra
Zu beweisen:
R ÷ S = πA (R) \ πA ((πA (R) × S) \ R)
Beweis:
Wir beweisen die Aussage wie in der Mengenlehre und veranschaulichen die Äquivalenzen anhand
folgendem Beispiel:
R
Programmierer
Müller
Müller
Müller
Huber
Huber
Sprache
Java
Basic
C++
C++
Java
S
und
Sprache
Basic
C++
Java
Frage: Welche Programmierer programmieren in allen Sprachen
R÷S
Programmierer
Müller
• ”⊆”: Sei r ∈ R ÷ S ⇒
1. ∀s ∈ S : [rs] ∈ R
2. r ∈ πA (R)
Wegen 2. genügt es zu zeigen, dass r ∈
/ πA ((πA (R) × S) \ R)
⇔ ∀x ∈ S : [rx] ∈
/ (πA (R) × S) \ R
⇔ ∀x ∈ S : [rx] ∈
/ πA (R) × S ∨ [rx] ∈ R
Die erste Bedingung [rx] ∈
/ πA (R) × S wird immer zu false ausgewertet, die zweite zu true
für alle x ∈ S, d.h. die Bedingung ist wahr.
• ”⊇”: Sei r ∈ πA (R) \ πA ((πA (R) × S) \ R) ⇒
1. r ∈
/ πA ((πA (R) × S) \ R)
2. r ∈ πA (R)
Zu zeigen: ∀s ∈ S : [rs] ∈ R
aus 1. folgt: ∀x ∈ S : [rx] ∈
/ πA (R) × S ∨ [rx] ∈ R.
Restliche Argumentation wie bei ⊆.
1
Der Divisionoperator erhöht die Ausdruckskraft der Relationenalgebra nicht, sondern wurde nur
zur Vereinfachung eingeführt.
Aufgabe 3: Relationale Algebra
Unter Verwendung von
R ÷ S = πA (R) \ πA ((πA (R) × S) \ R)
und den Zuweisungen
• R→R×S
• A→R
• S→S
ergibt sich
(R × S) ÷ S = πR (R × S) \ πR ((πR (R × S) × S) \ (R × S))
R \ πR ((R × S) \ (R × S))
R \ πR (∅)
R\∅
R
Aufgabe 4: Aggregation und Gruppierung in SQL
Erstellen der Relation Produkte:
CREATE TABLE Produkte (
ID INTEGER Primary Key,
Anzahl_Lager INTEGER,
Anzahl_Shops INTEGER);
insert
insert
insert
insert
insert
insert
insert
insert
insert
into
into
into
into
into
into
into
into
into
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Produkte
Values
Values
Values
Values
Values
Values
Values
Values
Values
(1,
(2,
(3,
(4,
(5,
(6,
(7,
(8,
(9,
1, 1);
1, 2);
2, 1);
2, 2);
NULL, 1);
NULL, 2);
1, NULL);
2, NULL);
NULL, NULL);
2
a) Anfrage:
SELECT Anzahl_Lager,
SUM(Anzahl_Shops) as a_sum,
COUNT(*) as a_count,
COUNT(DISTINCT Anzahl_Shops) as a_d_count,
MAX(Anzahl_Shops) as a_max,
MIN(Anzahl_Shops) as a_min,
AVG(Anzahl_Shops) as a_avg
FROM Produkte
GROUP BY Anzahl_Lager;
ANZAHL LAGER
1
2
NULL
A SUM
3
3
3
A COUNT
3
3
3
A D COUNT
2
2
2
A MAX
2
2
2
A MIN
1
1
1
A AVG
1,5
1,5
1,5
• Im Ergebnis kommt nur eine Gruppe für NULL-Werte vor. Bei Gruppierung werden NULLs
also als gleich betrachtet.
• Die Summe ist die Summe der tatsächlichen Werte für Anzahl Shops. NULL hat hier keine
Auswirkungen.
• COUNT(*) zählt alle Tupel, auch das mit Anzahl Shops = NULL. Im Gegensatz dazu zählt
COUNT(DISTINCT Anzahl Shops) Einträge mit Anzahl Shops = NULL nicht.
• MAX(Anzahl Shops) und MIN(Anzahl Shops) liefern das Maximum bzw. das Minimum
ohne NULLs zu berücksichtigen, obwohl NULL beim Sortieren von Oracle als größter
Wert angesehen wird.
• AVG(Anzahl Shops) liefert den Duchschnitt der Tupelwerte ungleich NULL. NULL-Werte
beeinflussen den Durchschnitt also nicht.
b) Anfrage:
SELECT *
FROM Produkte P1, Produkte P2
WHERE P1.Anzahl_Lager = P2.Anzahl_Lager;
Ergebnis:
3
ID
7
2
1
7
2
1
8
4
3
8
4
3
7
2
1
8
4
3
ANZAHL LAGER
1
1
1
1
1
1
2
2
2
2
2
2
1
1
1
2
2
2
ANZAHL SHOPS
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
NULL
2
1
ID
1
1
1
2
2
2
3
3
3
4
4
4
7
7
7
8
8
8
ANZAHL LAGER ANZAHL SHOPS
1
1
1
1
1
1
1
2
1
2
1
2
2
1
2
1
2
1
2
2
2
2
2
2
1
NULL
1
NULL
1
NULL
2
NULL
2
NULL
2
NULL
Tupel mit P1.Anzahl Lager = NULL bzw. P2.Anzahl Lager = NULL fehlen. Das bedeutet
NULLs werden hier als unterschiedliche Werte betrachtet (NULL <> NULL).
Aufgabe 5: Count-Bug
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.
4
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
MINUS
(SELECT
WHERE
GROUP
HAVING
dept_nr FROM R)
dept_nr FROM R
job = ’clerk’
BY dept_nr
COUNT(*) > 2);
5