Datenbanksysteme I Lösungsskizze zu ¨Ubungsblatt 7

Ludwig-Maximilians-Universität München
Institut für Informatik
Prof. Dr. Hans-Peter Kriegel
Stefan Schönauer
Datenbanksysteme I
WS 2002/03
Lösungsskizze zu Übungsblatt 7
Aufgabe 1
Anfragen in SQL
a) Bestimme die Namen der Lieferanten zusammen mit der Anzahl der Projekte, die jeder Lieferant beliefert.
SELECT
FROM
WHERE
GROUP BY
L.LNR, LNAME, count(DISTINCT PNR)
L, LTP
L.LNR = LTP.LNR
L.LNR, LNAME
b) Bestimme für jedes Teil die gesamte gelieferte Menge.
SELECT
FROM
WHERE
GROUP BY
T.TNR, TNAME, sum(MENGE)
T, LTP
T.TNR = LTP.TNR
T.TNR, TNAME
c) Bestimme die Lieferanten, die mehr als zwei Projekte beliefern.
SELECT
FROM
WHERE
GROUP BY
HAVING
L.LNR, LNAME
L, LTP
L.LNR = LTP.LNR
L.LNR, LNAME
count(DISTINCT PNR) > 2
d) Bestimme das Gesamtgewicht der Teile, die sowohl an Projekt P1 als auch an Projekt P2 geliefert werden.
SELECT
FROM
WHERE
AND
AND
Aufgabe 2
sum(GEWICHT * MENGE)
T, LTP
T.TNR = LTP.TNR
T.TNR IN ( SELECT
FROM
WHERE
AND
AND
PNR IN (’P1’, ’P2’)
X1.TNR
LTP X1, LTP X2
X1.TNR = X2.TNR
X1.PNR =’P1’
X2.PNR = ’P2’ )
Anfragen in QUEL
Im folgenden können die RANGE-OF Ausdrücke jeweils weggelassen werden.
1
a) Bestimme die Nummern aller blauen Teile.
RANGE OF Tx IS T
RETRIEVE UNIQUE (Tx .TNR)
WHERE Tx .FARBE = ’BLAU’
b) Bestimme Namen und Nummer aller roten Teile, die mehr als 50g wiegen.
RANGE OF Tx IS T
RETRIEVE UNIQUE (Tx .NAME, Tx .TNR)
WHERE Tx .FARBE = ’ROT’
AND Tx .GEWICHT > 50
c) Bestimme Namen und Farbe aller Teile, die von Lieferanten aus Berlin geliefert werden.
RANGE OF Tx IS T
RANGE OF LT Px IS LTP
RANGE OF Lx IS L
RETRIEVE UNIQUE (Tx .NAME, Tx .FARBE)
WHERE Tx .TNR = LT Px .TNR
AND Lx .LNR = LT Px .LNR
AND Lx .SITZ = ’Berlin
d) Bestimme für jeden Lieferanten das Gesamtgewicht der von ihm gelieferten Teile.
RANGE OF Lx IS L
RANGE OF LT Px IS LTP
RANGE OF Tx IS T
RETRIEVE (Lx .LNR, GG = sum(LT Px .MENGE * Tx .GEWICHT BY Lx .LNR))
WHERE LT Px .LNR = Lx .LNR
AND LT Px .TNR = Tx .TNR
e) Bestimme die Namen der Lieferanten, die jeweils alle Projekte in Berlin beliefern.
RANGE OF Lx IS L
RANGE OF LT Px IS LTP
RANGE OF Px IS P
RETRIEVE UNIQUE (Lx .LNAME)
WHERE count((Px .PNR BY Lx .LNR)
WHERE Px .ORT = ’BERLIN’
AND count((LT Px .LNR BY Lx .LNR, Px .PNR)
WHERE LT Px .PNR = Px .PNR
AND LT Px .LNR = Lx .LNR
) = 0
) = 0
f) Erzeuge eine neue Relation ’Katalog’ mit den Lieferantennummern und den Nummern der lieferbaren
Teile und fülle sie mit Tupeln, soweit aus LTP bekannt. Füge in diese Relation ein weiteres Tupel (’L1’,
’T2’) ein.
2
CREATE Katalog
( LNR
TNR
)
CHAR(3) NOT NULL,
CHAR(3) NOT NULL
RANGE OF LT Px IS LTP
APPEND TO Katalog (LNR = LT Px .LNR, TNR = LT Px .TNR)
APPEND TO Katalog (LNR = ’L1’, TNR = ’T2’)
3