h—ten˜—nksysteme - IFIS Uni Lübeck

Prof. Dr. V. Linnemann
Lübeck, den 22. November 2004
Dr. D. Kukulenz
Universität zu Lübeck
Institut für Informationssysteme
Datenbanksysteme
Wintersemester 2004/2005
6. Übungsblatt
Aufgabe 1: Sql
Die Datenbank eines Kaufhauses wird mit folgenden SQL-Befehlen deniert:
;
connect to dbs;
DROP
DROP
DROP
DROP
DROP
DROP
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
Liefert;
Bietet_An;
Lieferant;
Artikel;
Angestellter;
Abteilung;
CREATE TABLE Abteilung(
Name
CHAR(20) NOT NULL,
Chefnummer SMALLINT,
PRIMARY KEY(Name)
);
CREATE TABLE Angestellter(
Nummer
SMALLINT NOT NULL,
Name
CHAR(30) NOT NULL,
Adresse
CHAR(50) NOT NULL,
1/4
Abteilungsname CHAR(20) NOT NULL,
Chefnummer
SMALLINT NOT NULL,
PRIMARY KEY(Nummer),
FOREIGN KEY(Abteilungsname) REFERENCES Abteilung,
FOREIGN KEY(Chefnummer) REFERENCES Angestellter
);
ALTER TABLE Abteilung
ADD CONSTRAINT FK_CHEFNR FOREIGN KEY (Chefnummer) REFERENCES Angestellter;
CREATE TABLE Artikel(
Artikelnr
SMALLINT NOT NULL,
Bezeichnung CHAR(50) NOT NULL,
Verk_Preis DECIMAL(10,2) NOT NULL,
PRIMARY KEY(Artikelnr)
);
CREATE TABLE Lieferant(
Name
CHAR(20) NOT NULL,
Adresse CHAR(50) NOT NULL,
PRIMARY KEY(Name)
);
CREATE TABLE Bietet_An(
Abteilungsname CHAR(20)
NOT NULL,
Artikelnr
SMALLINT NOT NULL,
PRIMARY KEY(Abteilungsname, Artikelnr),
FOREIGN KEY(Abteilungsname) REFERENCES Abteilung,
FOREIGN KEY(Artikelnr) REFERENCES Artikel
);
CREATE TABLE Liefert(
Lieferantenname CHAR(20)
NOT NULL,
Artikelnr
SMALLINT NOT NULL,
Produktnr
SMALLINT NOT NULL,
Eink_Preis
DECIMAL(10,2) NOT NULL,
PRIMARY KEY(Lieferantenname, Artikelnr),
FOREIGN KEY(Lieferantenname) REFERENCES Lieferant,
FOREIGN KEY(Artikelnr) REFERENCES Artikel
);
connect reset;
2/4
--;
Abteilung, Angestellter , Artikel und Lieferant sind selbsterklärend. Die
Relation Bietet_An speichert die Beziehung Abteilung bietet Artikel an. Die Tabelle Liefert speichert die Beziehung Lieferant liefert Artikel, wobei Produktnr eine vom
Die Tabellen
Lieferanten vergebene Nummer ist.
Formulieren Sie folgende SQL-Anfragen (mit jeweils
einer
SQL-Anweisung; ohne zusätzli-
che Tabellen/Sichten anzulegen):
a) Namen von Lieferanten, die Artikel mit der Bezeichnung Wasserkocher mit dem
Verkaufspreis unter 10 EUR liefern?
b) Wie lauten die Artikel, die in mehr als einer Abteilung angeboten werden? Geben
Sie die Artikelbezeichnung und den Namen der Abteilung aus.
c) Welche Lieferanten liefern keine Artikel?
d) Welche Angestellten wohnen in Lübeck oder arbeiten in der Abteilung 'Buch' ? Geben
Sie 2 Varianten an!.
e) Bei welchen Artikeln wird ein Gewinn von mehr als 10 EUR erzielt? Geben Sie sowohl
die Artikelbezeichnung als auch den tatsächlichen Gewinn aus.
f ) Finden Sie Artikelbezeichnungen von Artikeln, die von mindestens zwei Lieferanten
geliefert werden, die beide den Artikel zu einem Einkaufspreis von unter 10,50 EUR
anbieten.
g) Welche Artikel werden von mindestens 3 Lieferanten geliefert werden? Geben Sie die
Artikelbezeichnung und die Anzahl der Lieferanten aus, die den Artikel liefern.
h) Welche Artikel werden von mindestens zwei unterschiedlichen Lieferanten zu unterschiedlichen Preisen geliefert? Geben Sie die Artikelbezeichnung, die Lieferanten und
die Preise aus.
i) Artikel mit dem höchsten Verkaufspreis? Auszugeben sind Artikelbezeichnung und
der Verkaufspreis.
j) Artikel mit dem zweithöchsten Verkaufspreis? Auszugeben sind Artikelbezeichnung
und der Verkaufspreis.
k) Berechnen Sie für jede Abteilung die Anzahl der angebotenen Artikel, die Gesamtsumme der Verkaufspreise, sowie den durchschnittlichen Verkaufspreis.
(20 Punkte)
3/4
Hinweise zu DB2
•
Das Skript
kh_create.sql zur Erzeugung der Tabellen sowie ein Skript kh_daten.sql
zur Erzeugung einiger Testdaten können von der Webseite der Vorlesung heruntergeladen werden.
•
Tragen Sie die
Sql-Anfragen in eine Datei kh_select.sql ein; drucken Sie diese aus
und geben Sie in der Vorlesung ab.
•
Das Ergebnis der Ausführung der SQL-Anweisungen sollen in einer Text-Datei namens
dbpNN.txt (dbpNN ist die Account-ID Ihrer Gruppe) gespeichert und per Email
abgegeben werden.
Abgabetermin:
Montag, den 29. November 2004 nach der Vorlesung
4/4