SQL (III) - Technologie der Informationssysteme

Christian-Albrechts-Universität zu Kiel
Institut für Informatik
Arbeitsgruppe Technologie der Informationssysteme
Prof. Dr. B. Thalheim, Dipl.-Inf. G. Fiedler
SS 2007
Übungsblatt 7
Übungen zur Vorlesung Datenbanksysteme I
Übungsblatt 7
Hinweis: Die Aufgaben dieses Übungsblatts beziehen sich auf die Datenbank Wetterdaten“. Im
”
Anhang zu diesem Übungsblatt finden Sie eine Übersicht über das Schema und eine Beschreibung der Tabellen. Klicken Sie vor Beginn der Bearbeitung im Webinterface auf den Button
Wetterdaten wiederherstellen“, um die aktuellen Wetterdaten in ihr Schema zu übernehmen.
”
Aufgabe 1
Informieren Sie sich im DB2-Handbuch über
•
•
•
•
•
create view und create table
insert into
Aggregatfunktionen, group by und having
order by
case
Aufgabe 2
Die Wetterdaten ( Faktentabellen“) der einzelnen Wetterstationen befinden sich in drei verschie”
denen Tabellen: Wetter_KI, Wetter_Teneriffa und Wetter_PB. Erzeugen Sie eine Sicht, welche
die Daten dieser drei Tabellen sinnvoll vereinigt. Die Sicht soll über dem Schema
Wetter(
{Station,
Erfassungsdatum,
Erfassungszeit,
Lufttemperatur,
Wassertemperatur,
solareEinstrahlung,
Luftdruck,
Pegelstand,
Niederschlag,
Feuchte,
Windrichtung,
Windgeschwindigkeit}
)
definiert sein. Fehlende Werte sollen mit null belegt werden. Beachten Sie die Beschreibungen
der Attribute der Ausgangstabellen und schauen Sie sich die Werte in der Beispieldatenbank an.
Beschreiben Sie ihr Vorgehen.
Aufgabe 3
Stellen Sie folgende Anfragen in SQL an die in Aufgabe 2 definierte Sicht. Nutzen Sie bei
Bedarf zusätzlich die Tabellen Land, Region, Ort und Station. Wenn nicht anders angegeben,
ist das Anlegen von zusätzlichen Tabellen oder Sichten mittels create-Anweisung nicht gestattet.
Beachten Sie zusätzlich die Hinweise zu SQL im Anhang zu diesem Übungsblatt.
a. Wie hoch war die minimale, maximale und durchschnittliche Lufttemperatur in Kiel am
Westufer (Station 1) im Dezember 2005?
b. Wie hoch ist die durchschnittliche Wassertemperatur der Ostsee in Kiel?
c. Gib eine Übersicht über die minimale, maximale und durchschnittliche Windgeschwindigkeit (als Einheit: kn) in Paderborn (Station=3)
• für jeden Monat
• für jedes Jahr
• für den gesamten Messzeitraum
Fassen Sie das Ergebnis in einer einzigen Tabelle zusammen, indem sie den Jahreswerten
null als Monat und dem Gesamtwert null für Monat und Jahr zuweisen. Die Ergebnistabelle soll demnach folgende Gestalt haben:
Jahr
null
2005
...
2005
...
Monat
null
null
null
1
...
Minimum
min gesamt
min in 2005
Maximum
max gesamt
max in 2005
Durchschnitt
Durchschnitt gesamt
Durchschnitt 2005
min in Jan. 2005
max in Jan. 2005
Durchschnitt Jan. 2005
d. Für Windgeschwindigkeiten existiert die Beaufort-Skala. Den Windstärken der BeaufortSkala sind Kategorien zugeordnet, z.B. Windstille“, Frische Brise“ oder Orkan“. Hinwei”
”
”
se zur Beaufort-Skala finden Sie unter http://de.wikipedia.org/wiki/Beaufortskala.
Geben Sie eine Übersicht über die absolute Häufigkeit jeder Windkategorie in Paderborn
(Station=3), d.h. zählen Sie für jede Kategorie die Tage, an denen die maximale Windgeschwindigkeit des Tages in diese Kategorie fällt. Es interessieren nur die Kategorien, die im
Messzeitraum aufgetreten sind. Ordnen Sie die Liste nach der Windkategorie ( Windstille“
”
< Leiser Zug“ < ... < Orkan“). Sie dürfen eine zusätzliche Tabelle für die Darstellung
”
”
der Beaufort-Skala anlegen, füllen und benutzen.
e. Gib für die Station IFM-GEOMAR (Institut)“ in Kiel“, Norddeutschland“, D“ die Ge”
”
”
”
samtmenge des Niederschlags, der in der Nacht vom 15.12.2005 (19.00 Uhr) zum 16.12.2005
(07.00 Uhr) gefallen ist.
f. Wo (gesucht sind Land, Region, Ort) herrschte in welchem Monat (als Bezeichnung Ja”
nuar“ bis Dezember“) eine Durchschnitts-Lufttemperatur von mehr als 15◦ C? Sie dürfen
”
eine zusätzliche Tabelle mit den Monatsnamen anlegen, füllen und benutzen. Ordnen Sie
die Liste nach den Monatsnamen ( Januar“ < Februar“ < ... < Dezember“)
”
”
”
g. An wievielen Tagen war in Kiel (Station=1) die durchschnittliche Lufttemperatur größer
als die durchschnittliche Wassertemperatur?
Aufgabe 4
Wie groß war die Durchschnittstemperatur in Deutschland im Jahr 2005? Werten Sie die Anfrage bzgl. der gegebenen Beispieldatenbank aus und bewerten Sie die Qualität des Ergebnisses.
Begründen Sie ihre Bewertung. Geben Sie informal Maßnahmen an, um die Aussagekraft der
Statistik zu erhöhen.
Besprechung der Lösungen: 30.05., 07.06., 01.06.
Viel Erfolg!
Beschreibung der Wetter-Datenbank
Die Wetter“-Datenbank speichert Messdaten von Wetterstationen. Sie besteht aus 7 Tabellen:
”
Tabelle: Land enthält die Kürzel der Länder, in denen sich Wetterstationen befinden:
Attribut Beschreibung
Name
Kürzel des Landes (z.B. D für Deutschland)
Tabelle: Region enthält die Regionen eines Landes. Das Attribut Land ist Fremdschlüssel auf
die Tabelle Land.
Attribut Beschreibung
Land
Land, in dem sich die Region befindet.
Name
Bezeichnung der Region
Tabelle: Ort enthält die Namen der Orte, in denen sich Wetterstationen befinden. (Land,Region)
ist Fremdschlüssel bzgl. der Tabelle Region.
Attribut Beschreibung
Land
Land, in dem sich der Ort befindet.
Region
Bezeichnung der Region, in der sich der Ort befindet
Name
Ortsname
Tabelle: Station enthält die Wetterstationen, die Daten für die Wetterdatenbank liefern. Jede
Station hat eine eindeutige nummerische Id, diese Id fungiert als Primärschlüssel der Tabelle.
Außerdem hat jede Station innerhalb ihres Ortes einen eindeutigen Namen. (Land,Region,Ort)
ist Fremdschlüssel bzgl. der Tabelle Ort.
Attribut Beschreibung
id
Identifikator der Station
Land
Land, in dem sich die Station befindet.
Region
Bezeichnung der Region
Ort
Ortsname
Name
Bezeichnung der Station
Die Wetterdaten sind in drei Tabellen ( Faktentabellen“) untergebracht. Das Attribut Station
”
ist jeweils Fremdschlüssel bzgl. der Tabelle Station. Primärschlüssel der Tabelle ist jeweils die
Kombination (Station,Erfassungsdatum,Erfassungszeit).
Tabelle: Wetter KI enthält die Wetterdaten der Stationen 1 und 2. Es wird ca. alle 8 Minuten
ein Datensatz gespeichert.
Attribut
Typ
Beschreibung
Station
int
Identifikator der Station (1 oder 2)
Erfassungsdatum
date
Datum, an dem die Messwerte aufgenommen wurden
Erfassungszeit
time
Zeitpunkt, zu dem die Messwerte aufgenommen wurden
Windrichtung
double Richtung des Windes in ◦
Windgeschwindigkeit double Geschwindigkeit des Windes in m · s−1
Lufttemperatur
double Temperatur der Luft in ◦ C
Wassertemperatur
double Temperatur des Wassers der Kieler Förde in ◦ C
solareEinstrahlung
double auftreffende Strahlungsleistung der Sonne in W · m−2
Luftdruck
double Luftdruck in hPa
Pegelstand
double Pegelstand der Ostsee in m relativ zu NN
Niederschlag
double Menge des Niederschlags in mm seit der letzten Messung (07.00 Uhr bzw. 19.00 Uhr)
Feuchte
double relative Luftfeuchtigkeit in Prozent
Tabelle: Wetter PB enthält die Wetterdaten der Station 3. Für jeden Tag im Messzeitraum
existieren 8 Messwerte.
Attribut
Typ
Beschreibung
Station
int
Identifikator der Station (immer 3)
Erfassungsdatum
date
Datum, an dem die Messwerte aufgenommen wurden
Erfassungszeit
time
Zeitpunkt, zu dem die Messwerte aufgenommen wurden
Lufttemperatur
double
Temperatur der Luft in ◦ C
Feuchte
double
relative Luftfeuchtigkeit in Prozent
Luftdruck
double
Luftdruck in hPa
Niederschlag
double
Menge des Niederschlags in mm seit der letzten Messung (07.00 Uhr bzw. 19.00 Uhr)
Windgeschwindigkeit double
Geschwindigkeit des Windes in m · s−1
Windrichtung
varchar(3) Richtung des Windes bzgl. der Windrose (N, NNE,
NE, ENE, E, ESE, SE, SSE, S, SSW, SW, WSW, W,
WNW, NW, NNW)
Tabelle: Wetter Teneriffa enthält die Wetterdaten der Station 4. Es wird ca. alle 15 min. ein
Messwert gespeichert.
Attribut
Typ
Beschreibung
Station
int
Identifikator der Station (immer 4)
Erfassungsdatum
date
Datum, an dem die Messwerte aufgenommen wurden
Erfassungszeit
time
Zeitpunkt, zu dem die Messwerte aufgenommen wurden
Lufttemperatur
double Temperatur der Luft in ◦ C
Feuchte
double relative Luftfeuchtigkeit in Prozent
Luftdruck
double Luftdruck in hPa
Niederschlag
double Menge des Niederschlags in mm seit der letzten Messung (07.00 Uhr bzw. 19.00 Uhr)
Windgeschwindigkeit double Geschwindigkeit des Windes in km · h−1
Windrichtung
double Richtung des Windes in ◦
Hinweise zu SQL
• Mit den Funktionen YEAR, MONTH, DAY, HOUR, MINUTE und SECOND kann man aus Datumsund Zeitwerten die Komponenten extrahieren.
• Mittels CAST(<Expr> as <Type>) lässt sich der Typ des Ausdrucks <Expr> in den Typ
<TYPE> umwandeln.
• Der Null-Wert hat keinen bestimmten Typ. Möchte man konstant null zuweisen, so muss
man dies als CAST(null as <Type>) schreiben, wenn sich der Typ nicht aus dem Kontext
ergibt.
• In DB2 existiert eine nicht-leere Dummy-Tabelle namens SYSIBM.SYSDUMMY1.