Musterlösung

Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
In SQL-artiger Notation:
Objektrelationale, erweiterbare Datenbanken WS 04/05
Übung 2
Beispiellösung
Aufgabe 1: Geschachtelte Tabellen/NF2
1. Modellierung geschachtelter Tabellen
Setzen Sie die Klassen Kunde, Bestellung und Position aus dem Anwendungsszenario in
eine geschachtelte Tabelle um. Achten Sie darauf, dass in dieser geschachtelten Tabelle die Assoziation zur Klasse Artikel nicht verloren geht! Die Klasse Artikel soll jedoch
als eigenständige Tabelle umgesetzt werden. Hierbei können die Subklassen von Artikel
vernachlässigt werden.
Lösung:
{Kunde}
Name GebDatum Adresse {TelNums} KundenNr Limit
{Bestellung}
TelNr
BestNr Eingangsdatum Status {Position}
Anzahl ArtNr
Nr
{Artikel}
Bez Preis
Unter welchen Umständen wäre es sinnvoll, die Klasse Artikel als weitere Ebene in die
geschachtelte Tabelle hinzuzunehmen?
Lösung: Dies wäre in erster Linie nur dann sinnvoll, falls einer Position n Artikel zugeordnet
würden. Ansonsten hätte man immer eine Subtabelle mit genau einem Tupel.
Ausserdem gilt es folgende Probleme zu beachten:
(a) Redundanzproblem: Stabile Artikeldaten wie Bezeichnung würden redundant in vielen
Positionen auftauchen. Dies führt zu unnötigen Speicherplatzverbrauch; bietet aber
eventuell bessere Antwortzeiten bei bestimmten Anfragearten.
(b) Anfragen, die zum Beispiel über alle Artikel sind schwerer zu formulieren, weil drei
Schachtelungsebenen zu berücksichtigen sind.
2. Formulieren Sie folgende Anfragen:
π
σ
PROJECT
SELECT
ν
µ
NEST
UNNEST
−
JOIN
EXCEPT
Geben Sie alle Daten des Kunden mit der Nummer 300175 aus!
σ [KundenNr = 300175] (Kunde)
1
SELECT *
FROM Kunde
WHERE KundenNr = 300175
Welche Kunden haben am 21.10.2002 eine Bestellung aufgegeben? Geben Sie den Namen
und die Nummer dieser Kunden aus!
Lösung 1 mit Entschachteln des relationenwertigen Attributs “Bestellung”:
π [Name, KundenNr] (σ [Eingangsdatum = 21.10.2002] (µ [Bestellung] (Kunde)))
In SQL-artiger Notation:
SELECT k.Name, k.KundenNr
FROM Kunde k, UNNEST(k.Bestellung) b
WHERE b.Eingangsdatum = 21.10.2002
Lösung 2 ohne Entschachteln des relationenwertigen Attributs “Bestellung”:
π [Name, KundenNr] (σ [∅ = σ [Eingangsdatum = 21.10.2002] (Bestellung)] (Kunde))
In SQL-artiger Notation:
SELECT k.Name, k.KundenNr
FROM Kunde k
WHERE EXISTS(SELECT *
FROM k.Bestellung AS b
WHERE b.Eingangsdatum = 21.10.2002)
Wie lauten die Namen der Artikel, die (bisher) von keinem Kunden bestellt wurden? Geben
Sie die Nummer und die Bezeichnung dieser Artikel aus!
Lösung 1 mit Entschachteln, aber ohne Join:
π [Nr, Bez] (σ [Nr ∈ π [ArtNr] (µ [Position] (µ [Bestellung] (Kunde)))] (Artikel))
In SQL-artiger Notation:
SELECT a.Nr, a.Bez
FROM Artikel a
WHERE a.NR NOT IN (SELECT p.ArtNr
FROM Kunde k,
UNNEST(k.Bestellung) b,
UNNEST(b.Position) p)
Lösung 2 mit Entschachteln und Join:
π [Nr, Bez]
(Artikel (π [Nr] (Artikel) − π [ArtNr] (µ [Position] (µ [Bestellung] (Kunde)))))
In SQL-artiger Notation:
2
SELECT a.Nr, a.Bez
FROM Artikel a, (SELECT Nr FROM Artikel
EXCEPT
SELECT p.ArtNr
FROM Kunde k,
UNNEST(k.Bestellung) b,
UNNEST(b.Position) p) x
WHERE a.Nr = x.Nr
3. Nestung/Entnestung von Attributen
Mit welchen Operationen kann die geschachtelte Tabelle in die 1. Normalform überführt
werden?
Lösungsvarianten:
• µ [Position] (µ [Bestellungen] (µ [TelNums] (Kunde)))
• µ [Position] (µ [TelNums] (µ [Bestellungen] (Kunde)))
• µ [TelNums] (µ [Position] (µ [Bestellungen] (Kunde)))
Wie kann man dies wieder rückgängig machen?
Lösungsvarianten: Sei Kunde’ das Ergebnis der obigen Operation
• ν [(BestNr, Eingangsdatum, Status, Position); Bestellung]
(ν [(Anzahl, ArtNr); Position] (ν [(TelNr); TelNums] (Kunde’)))
• ν [(BestNr, Eingangsdatum, Status, Position); Bestellung]
(ν [(TelNr); TelNums] (ν [(Anzahl, ArtNr); Position] (Kunde’)))
• ν [(TelNr); TelNums] (ν [(BestNr, Eingangsdatum, Status, Position); Bestellung]
(ν [(Anzahl, ArtNr); Position] (Kunde’)))
Aufgabe 2: Relationale Umsetzung von geschachtelten Tabellen
Setzen Sie die geschachtelte Tabelle Kunde“ sowie die Tabelle Artikel“ aus Aufgabe 1 mittels
”
”
flachen Tabellen in DB2-SQL um. Formulieren Sie auch die Anfragen aus Aufgabe 1 basierend
auf Ihrer relationalen Umsetzung.
Lösung: Die Tabellendefinitionen sehen in DB2-SQL wie folgt aus:
CREATE TABLE Artikel (
Nr INT NOT NULL PRIMARY KEY,
Bez VARCHAR(30),
Preis DECIMAL(12,2)
) $
Adresse VARCHAR(40),
Email VARCHAR(40),
Limit DECIMAL(12,2) CHECK(Limit < 5000)
) $
CREATE TABLE TelNums (
KundenNr INT NOT NULL REFERENCES Kunde(KundenNr),
TelNr VARCHAR(20) NOT NULL,
UNIQUE(KundenNr, TelNr)
) $
CREATE TABLE Bestellung (
KundenNr INT NOT NULL REFERENCES Kunde(KundenNr),
BestNr INT NOT NULL UNIQUE,
Eingangsdatum Date,
Status VARCHAR(15)
) $
CREATE TABLE Position (
BestNr INT NOT NULL REFERENCES Bestellung(BestNr),
PosNr INT NOT NULL,
ArtNr INT NOT NULL REFERENCES Artikel(Nr),
Anzahl INT NOT NULL,
UNIQUE(BestNr, PosNr)
) $
Die Anfragen werden wie folgt in DB2-SQL formuliert:
SELECT k.*, t.TelNr, b.BestNr, b.Eingangsdatum, b.Status,
p.PosNr, p.Anzahl, p.ArtNr, a.Bez, a.Preis
FROM Kunde k, TelNums t, Bestellung b, Position p, Artikel a
WHERE k.KundenNr = 300175 AND
k.KundenNr = b.KundenNr AND
b.KundenNr = t.KundenNr AND
b.BestNr = p.BestNr AND
p.ArtNr = a.Nr $
SELECT k.Name, k.KundenNr
FROM Kunde k JOIN Bestellung b ON k.KundenNr = b.KundenNr
WHERE b.Eingangsdatum = ’2002-10-21’ $
SELECT a.Nr, a.Bez
FROM Artikel a
WHERE a.NR NOT IN (SELECT p.ArtNr FROM Position p) $
CREATE TABLE Kunde (
KundenNr INT NOT NULL PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
GebDatum Date,
3
4
Aufgabe 3: Inversität von Nestung und Entnestung?
1. Zeigen Sie anhand eines konkreten Beispiels, daß die Nestung nicht allgemein invers zu
einer vorangegangenen Entnestung ist.
Lösung:
A
1
1
7
D
B C
5 8
6 9
2 3
1 4
−→
µ[D]
A B C
1 5 8
1 6 9
1 2 3
7 1 4
A
−→
ν[(B, C); D]
1
7
D
B C
5 8
6 9
2 3
1 4
2. Machen Sie sich klar, welche Eigenschaft eine geschachtelte Relation besitzen muß, damit
die Nestung und Entnestung immer invers zueinander sind.
Lösung: Eine wichtige Teilklasse von geschachtelten Relationen sind die sogenannten PNFRelationen (Partitioned Normal Form). PNF-Relationen können immer entschachtelt durch
eine äquivalente 1NF-Relation dargestellt werden. PNF-Relationen haben auf jeder Stufe
der Schachtelung einen flachen Schlüssel. Diese Eigenschaft ist bei der Schachtelung für
das richtige Zuordnen der Tupeln zu den inneren Relationen notwendig.
5