Datenbanken SoSe2014
Oracle-Datumskonvertierung
Wann immer man in einem SQL-Statement ein Datum als String angibt, sollte man dieses über die Funktion "to_date" eingeben,
denn hier kann man das Format des Datums deklarieren. Oracle akzeptiert sogar eine Stringangabe wie "16.06.2014". Egal ob man diese Variante
oder die Variante mit "to_date" ohne Angabe des Datumsformats verwendet, bekommt man spätestens dann Probleme, wenn der Server englisches
Datumsformat verwendet, im SQL-String aber ein deutsches Datum steht. Im günstigsten Fall gibt das eine Fehlermeldung, aber es könnte auch
passieren, dass etwas falsches herauskommt.
Beispielaufruf (deutsches Format: to_date('29.02.1990', 'DD.MM.YYYY')
Umgekehrt kann man ein Datum in einen String eines bestimmten Formats verwandeln mit "TO_DATE" (hier im englischen Format):
select 'Order date: ' || TO_CHAR(Datum, 'YYYY-MM-DD') from Auftrag;
Projektaufgabe
Wie ihr hoffentlich schon bemerkt habt, gibt es keine weiteren kleinen Aufgaben, sondern direkt die Projektaufgabe.
Im Rahmen des Praktikums werden wir uns trotzdem mit ein paar alten Aufgabenblättern beschäftigen:
Uebungsblatt7.pdf, Uebungsblatt8.pdf und Uebungsblatt9.pdf
Blatt 9 (Trigger): hier findet ihr die im Praktikum gezeigten Lösungen für Aufgabe 23b: trigger.sql
Als erstes kommt ein Blatt, für das ich nur eine Musterlösung von Hr. Heimrich bekommen habe und was wohl vor obigen Blättern kam:
1. Geben Sie den Ländernamen, den Namen der zugehörigen Hauptstadt und die Einwohnerzahl dieser Hauptstadt aller Länder in Amerika
sortiert nach den Ländernamen aus.
==>36 Treffer
2. Geben Sie alle Länder an, von denen die Datenbank weiß, dass es einen Ort südlich des Äquators in diesem Land gibt
==>36 Treffer
3. Geben Sie für jedes Land (so weit bekannt) an, wie viele Menschen englisch sprechen
==>10 Treffer (Beispiel: Liberia: 421.957,8)
4. Geben Sie für jedes Land in Europa an, welchen Anteil die Landesfläche an der Fläche von Gesamteuropa hat
==>45 Treffer (Beispiel: Deutschland = 3,73%, Russland = 35,71%)
5. Geben Sie die Namen aller Länder aus, für die bei jeder Stadt dieses Landes keine Einwohnerzahl eingetragen ist.
==>43 Treffer (u.a. Slovenia und Niger)
6. Geben Sie die Namen aller Länder aus, deren Hauptstadt weniger als 500000 Einwohner hat und für die mehr als fünf Städte in der
Datenbank eingetragen sind.
==>10 Treffer (u.a. Costa Rica und Switzerland)
7. Geben Sie alle Nachbarländer von Ländern Europas an (genauer, von Ländern die zumindest teilweise in Europa liegen), die selbst
nicht zu Europa gehören
==>10 Treffer (u.a. Armenia und Syria)
Einige Lösungen:
--Aufgabe 1, Variante 1:
select country.code, country.name, city.name from encompasses, country, city where
encompasses.country = country.code and
country.capital = city.name and
country.province = city.province and
continent = 'America' order by encompasses.country;
--Aufgabe 1, Variante 2 (JOIN):
select country.code, country.name, city.name from encompasses join country on encompasses.country = country.code
join city on country.capital = city.name and country.province = city.province
where
continent = 'America' order by encompasses.country;
--Aufgabe 2:
select country.code, country.name from country where
code in (select country from city where latitude is not null and latitude < 0);
--Aufgabe 4 (Anteil an Europa):
select (country.area * (encompasses.percentage / 100) / continent.area), country.name
from country, encompasses, continent where encompasses.continent = 'Europe' and
country.code = encompasses.country and
continent.name = 'Europe';
--Aufgabe 5: Problem dabei: gibt auch Länder aus, die garkeine Städte haben!
--Lösung 1 by Knauf
select * from country where code not in (select country from city where population is not null);
--Lösung 2 by Heimrich
select * from country where not exists (select * from city where city.country = country.code and
city.population is not null);
--Lösung 3 (ohne Länder OHNE Städte):
select * from country where code not in (select country from city where population is not null)
and code in (select country from city);
SQLDeveloper
Ausgabe von "dbms_output.put_line"
Die Ausgabe oben genannter Prozedur finden wir auf dem Karteireiter "DBMS-Ausgabe" wieder. Eventuell muss man sich das Fenster erst über Menü "Ansicht" - "DBMS-Ausgabe" dazuschalten.
Damit es etwas anzeigt, muss zuerst der Button "DBMS-Ausgabe aktivieren" geklickt werden. Anschließend muss man die Verbindung wählen, für die der Output
angeschaltet werden soll. Danach erscheint ein neuer Unter-Karteireiter mit der Ausgabe.
Troubleshooting
Wenn beim Anlegen einer Prozedur die Meldung "Warnung: Prozedur wurde mit Kompilierungsfehlern erstellt" kommt,
dann hilft dieser Befehl:
show errors;
Es empfiehlt sich, hier den Namen der nicht compilierten Procedure bzw. Function anzugeben, da man sonst alle im System vorliegenden Compilefehler angezeigt bekommt:
show errors PROCEDURE myProcedure;
show errors FUNCTION myFunction;
show errors TRIGGER myTrigger;
Zugriff auf FH-Oracle
Linux
Hr. Heimrich sagt:
Zuerst braucht man einen Tunnel von außen zum Server. Mit Ubuntu kann man das z.B. so machen:
ssh heimrich@login2.cs.hs-rm.de -L 1521:oracle2:1521
Dann muss man den SQl-Developer starten und eine neue Verbindung
einrichten. Im folgenden Bild sieht man, was eingegeben werden muss.
Wichtig ist, dass bei "SID" p001 eingegeben wird. So heißt die Datenbank.
Windows
Basierend auf: http://adamsquicklog-webissues.blogspot.de/2011/02/connecting-sqldeveloper-to-remote-db.html
- "putty.exe" herunterladen von http://www.putty.org/
- "putty.exe" starten.
- In "Connection" -> "SSH" -> "Tunnels" gehen. Im Feld "Source port" wird "127.0.0.1:1521" eingetragen, im Feld "Destination" wird "oracle2:1521" eingetragen - die Adresse der
FH-Oracle:
Danach auf "Add" klicken.
Jetzt taucht die Verbindung unter "Forwarded ports" auf:
Auf "Session" wechseln und als Hostname "login1.cs.hs-rm.de" eingeben. Ich empfehle, das als Session zu speichern, damit man den Tunnel nicht jedesmal neu einrichten muss:
Jetzt auf "Open" klicken.
Es kommt ein Anmeldefenster. Dort mit dem FH-Login anmelden.
Nach erfolgreichem Login belassen wir das Fenster in diesem Zustand und ...
- ...melden uns im SQLDeveloper an. Hier gelten die gleichen Einstellungen wie im obigem Linux-Beispiel: Hostname = "localhost", SID = "p001".
Übungsblätter
Blatt 5
Hier der Operatorbaum aus Hr. Heimrichs Musterlösung von Aufgabe 1.5: A1.5_Musterloesung.pdf
(hier klappt der Fall "Eine Vorlesung hat zwar einen direkten Vorgänger, aber keinen Vor-Vorgänger" nicht)
Alternativlösung, die diesen Fall abdeckt: A1.5_Knauf.pdf
Abgabe am 28.5.: Aufgabe 1.6 und Aufgabe 2, außerdem Aufgabe 1.5 mit funktionierender Lösung für den Fall "Eine Vorlesung hat zwar einen direkten Vorgänger,
aber keinen Vor-Vorgänger"
Blatt 2
Zusatzaufgabe zur ER-Modellierung für den 16.4.2014:
http://www.dieseebergers.de/tigon/uebung1.pdf
Hier das Bild mit der im Praktikum erarbeiteten Lösung für die Fußball-Aufgabe: fussball.png
Und hier mein Vorschlag für die Reisebüro-Aufgabe, als Grundlage für eine Umsetzung in Tabellen: reisebuero.png
Tools für ER-Diagramme:
DIA
yEd
Blatt 1
Ein Link zu einer Erklärung des 3-Schichten-Modells: http://wikis.gm.fh-koeln.de/wiki_db/Datenbanken/ANSI-3-Ebenenmodell
Stand 06.07.2014
Das verwendete Icon stammt von hier und steht
unter CreativeCommons-Lizenz
Historie:
16.04.2014: Erstellt
16.04.2014: Beispiellösung für ER-Zusatzaufgabe "Fußballsaison"
28.04.2014: Bewertung Blatt 2, Beispiellösung für die 2. ER-Zusatzaufgabe "Reisebüro"
06.05.2014: ER-Zusatzaufgabe "Reisebüro": Diagramm überarbeitet
08.05.2014: Bewertung Blatt 3
15.05.2014: Bewertung Blatt 4
21.05.2014: Hinweise zu Blatt 5, Verbindung zur FH-Oracle per SQLDeveloper
28.05.2014: Bewertung Blatt 5
29.05.2014: letzte fehlende Bewertung Blatt 5
04.06.2014: Hinweise zu Blatt 7
06.06.2014: Bewertung Blatt 6
10.06.2014: Weitere inoffizielle Aufgabenblätter
22.06.2014: SQLDeveloper-Hinweise
26.06.2014: Trigger-Lösung
06.07.2014: Bewertung Projektaufgabe