Datenbanken 11. Aufgabenblatt 1. Aufgabe Geografische Koordinaten können in folgender Form als komplexes Attribut modelliert werden: create type geokoord as object (laengengrad number, breitengrad number); / Dieses komplexe Attribut kann ebenso wie andere Attribute in Tabellendefinitionen verwendet werden, z.B. create table berg (name varchar2(20), hoehe number, koordinaten geokoord); 1. Implementiere den Objekttyp geokoord und die Tabelle berg in der Oracle-DB. Anschließend füge die 10 höchsten Erhebungen des Bayr. Waldes in die Tabelle berg ein. Die dazu nötigen Daten beschaffe aus dem Internet 2. Gib alle Attribute der jeweiligen Tupel von der Tabelle "berg" (nach dem Füllen der Tabelle mit Daten) aus. 3. Gib folgende Tabelle mit folgenden Spaltenüberschriften aus. Name Koordinaten.Laengengrad b.Koordinaten.Breitengrad -------------------------------------------------------------------------... 4. Kann zur Lösung von 3. folgende select-Anweisung herangezogen werden? __________________ select name, berg.koordinaten.laengengrad, berg.koordinaten.breitengrad from berg 5. Falls die unter 4. angegebene select-Anweisung nicht funktioniert, gib an, nach welcher Korrektur diese Anweisung korrekt arbeitet. 2. Aufgabe Gegeben ist die folgende verschachtelte Tabelle "geschachtelteSprachen" geschachtelteSpachen Land Sprachen %-Satz D Deutsch 100 CH Deutsch Französisch Italienisch Romanisch 65 18 12 1 FL NULL SK Slovakisch Ungarisch 95 5 1 Datenbanken 1. Modelliere die in den einzelnen Ländern gesprochenen Sprachen durch geschachtelte Tabellen. 2. Fülle die unter 1. erzeugte Tabelle mit den in der Vorgabe angegebenen Daten. 3. Gib an, welche Sprachen mit welchem Prozentsatz von der Bevölkerung in der Schweis gesprochen werden. 4. Gib die select-Anweisung an, die folgende Tabelle ausgibt Name Prozentsatz -----------------------Deutsch 65 Französisch 18 Italienisch 12 Romanisch 1 5. Es sollen alle Länder ausgegeben werden, in denen "Deutsch" gesprochen wird. 6. Es sollen alle Paare (Land L, Sprachen S) ausgegeben werden, so dass die Sprache S im Land L gesprochen wird. a) Zunächst soll nur ein Land, Z.B. "CH" ausgewählt werden. Gib die zugehörige SQL-Anweisung an. b) Es sollen alle Länder betrachtet werden. Gib die zugehörige SQL-Anweisung an. 3. Aufgabe Beschaffe aus dem Internet die Abschlusstabelle der deutschen Fußball-Bundesliga. 1. Erzeuge eine Objekttabelle, die die Daten aus der Abschlusstabelle der Bundesliga aufnehmen kann. Die Tabelle muß umfassen: Name der Mannschafft, den Ort an dem das Team residiert, Punkte, Tore, Gegentore. 2. Fülle diese Tabelle mit den Daten der Abschlusstabelle. 3. Gib die implementierte Tabelle aus. 2 Datenbanken 4. Aufgabe: Abfragen auf geschachtelten Tabellen Gegeben ist die folgende verschachtelte Tabelle Abteilungen A AN 1 Forschung {VA} {TA} {LG} VNR VTB TNR TB LN 121 122 130 Bibliothek Sekretariat Übersetzung 511 Programmentwicklung 1 2 5 75 76 79 140 Patente 652 Grundlagen 1 2 82 76 169 Sekretariat 678 Planung 2 4 76 82 2 Entwicklung 119 125 Sekretariat Übersetzung 650 Design 1 4 135 Beschaffung 780 981 Wartung Planung 1 2 3 4 Test J NULL 952 579 Design 1 4 75 77 75 81 82 75 77 Planung 1. Setze die verschachtelte Tabelle um mit Tabellen-und Objekttypen, die über die Tabelle abteilungen schließlich zusammengefasst werden sollen. 2. Fülle die Tabelle abteilungen mit den angegebenen Daten aus der vorstehenden Darstellung der geschachtelten Tabelle. 3. Welche Select-Anweisung erzeugt aus der geschachtelten Tabelle abteilungen die folgenden Projektion? A {TA} TNR TTB 1 511 552 678 Programmentwickling Grundlagen Planung 2 650 780 981 Design Wartung Planung 4 952 573 Design Planung 4. Welche SQL-Anweisungen erzeugen aus der geschachtelten Tabelle abteilungen folgende Projektion (Variante zu 3. mit Ergebnistabellentyp) 3 Datenbanken A TA(NR,TB) 1 ATAB(ATYP(511,Programmentwicklung), ATYP(552,Grundlagen), ATYP(678,Planung)) 2 ATAB(ATYP(650,Design), ATYP(780,Warung), ATYP(981,Planung)) 3 ATAB(ATYP(952,Design), ATYP(573,Planung)) 5. Welche Select-Anweisung erzeugt folgende Tabelle aus der geschachtelten Tabelle abteilung? A 1 AN Forschung {VA} VNR VTB 121 122 130 140 169 Bibliothek Sekretariat Übersetzung Patente Sekretariat {TA} TNR TTB {LG} LN J 511 Programmentwicklung 552 Grundlagen 678 Planung 1 2 5 1 2 2 4 75 76 79 82 79 76 82 6. Welche Select-Anweisung erzeugt folgende Tabelle aus der geschachtelten Tabelle abteilung? (geschachtelte Selektion innerhalb einer Projektion) A {VA} VNR 1 122 169 2 119 4 NULL 7: Welche Select-Anweisung erzeugt folgende Tabelle aus der geschachtelten Tabelle abteilung? (geschachtelte Projektion innerhalb einer Selektion) A 2 4 AN Entwicklung Test {VA} VNR VTB 119 Sekretariat 125 Übersetzung 135 Beschaffung NULL {TA} TNR TTB 650 Design 780 Wartung 981 Planung 952 Design 573 Planung {LG} LN J 1 75 4 74 1 15 2 81 3 82 1 75 4 77 3 82 8. Welche Select-Anweisung führt die folgende Entschachtelung aus der geschachtelten Tabelle abteilung durch? 4 Datenbanken A 1 1 1 1 1 2 2 2 AN Forschung Forschung Forschung Forschung Forschung Entwicklung Entwicklung Entwicklung VNR 121 122 130 140 169 119 125 135 VTB Bibliothek Sekretariat Übersetzung Patente Sekretariat Sekretariat Sekretariat Beschaffung 9. Die Entschachtelung entfernt äußere Tupel/Objekte, deren innere Tabelle NULL ist. Wie kann das (analog zum Outer Join) verhindert werden? Die entschachtelte Tabelle soll damit folgendes Aussehen haben. A 1 1 1 1 1 2 2 2 4 AN Forschung Forschung Forschung Forschung Forschung Entwicklung Entwicklung Entwicklung Test VNR 121 122 130 140 169 119 125 135 NULL VTB Bibliothek Sekretariat Übersetzung Patente Sekretariat Sekretariat Sekretariat Beschaffung NULL 10. Welche Select Anweisung zeigt die folgende komplett entschachtelte Darstellung? A 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 AN Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung Entwicklung VNR 119 119 119 119 119 125 125 125 125 125 135 135 135 135 135 VTB Sekretariat Sekretariat Sekretariat Sekretariat Sekretariat Übersetzung Übersetzung Übersetzung Übersetzung Übersetzung Beschaffung Beschaffung Beschaffung Beschaffung Beschaffung TNR 650 650 780 981 981 650 650 650 650 650 650 650 780 981 981 11. Welche Spalten würde select * from abteilungen abt, table(abt.va) v, table(abt.ta) t, table(t.lg) l where abt.a = 2; liefern? Lösungen: aufg11.sql, aufg11b.sql 5 TTB Design Design Wartung Planung Planung Design Design Wartung Planung Planung Design Design Wartung Planung Planung LN 1 4 1 2 3 1 4 1 2 3 1 4 1 2 3 J 75 77 75 81 82 75 77 75 81 82 75 77 75 81 82