Prof. Dr. Stephan Kleuker Hochschule Osnabrück Fakultät Ing.-Wissenschaften und Informatik - Software-Entwicklung - Datenbanksysteme Wintersemester 2016/17 6. Aufgabenblatt Aufgabe 16 (2 Punkte) Geben seien folgende Tabellen aus der Aufgabe 12. Bestellung Besteller TELEFON -----------04321 03456 02345 NAME ---------Ronny Bonny Johnny ORT -------Itzehoe Elmshorn Horst Anruf NUMMER -----1 2 3 4 TELEFON -------04321 03456 03456 04321 DATUM ---------01.11.2016 01.11.2016 01.11.2016 01.11.2016 UHRZEIT ----16:45 16:50 18:30 18:45 ANUMMER PNUMMER MENGE -------- -------- ----1 1 2 1 2 3 2 2 4 3 1 1 4 1 2 4 2 3 Pizza NUMMER ------1 2 3 NAME PREIS -------- -------Mafiosa 6.5 Tofu 5.5 Western 8.3 Formulieren Sie folgende Anfragen in SQL. a) Zu welchen Uhrzeiten hat Bonny bestellt? b) Geben Sie eine Tabelle mit den Telefonnummern des Anrufs und den zugehörigen Pizzanamen aus. c) Welche Pizzen (Namen) hat Ronny bestellt? d) Geben Sie eine Liste Bestellername, Pizzanummer und Gesamtpreis der jeweiligen Pizzen aus (nur als Preis*Menge ausgeben, keine Aggregatsfunktionen nutzen). Aufgabe 17 (6 Punkte) Gegeben seien die Mondial-Tabellen, formulieren Sie folgende SQL-Anfragen. a) Geben Sie die Namen aller Städte in Deutschland aus. b) Geben Sie die Namen aller Länder in Europa aus. c) Geben Sie die Namen aller Städte von Ländern in Europa mit mehr als 300000 Einwohnern aus. d) Wie viele Länder sind in der NATO? e) Geben Sie für Deutschland die Namen aller Nachbarländer an. (borders ist nicht symmetrisch) f) Geben Sie alle Städtenamen die mindestens zweimal in der Datenbank vorkommen zusammen mit dem Namen der zugehörigen Länder, geordnet nach den Städtenamen aus. Dabei soll der erste Namen des Landes alphabetisch vor dem Namen des zweiten Landes stehen. (Beispiele: Barcelona Spain Venezuela, Arlington United States United States) g) Geben Sie die Namen aller Länder aus, in denen es mindestens zwei verschiedene Städte gleichen Namens gibt. (2 Zeilen) h) Bestimmen sie die Durchschnittswerte der geographischen Breite und Länge aller Städte (nutzen Sie einmal AVG, dann in einer zweiten Anfrage SUM und COUNT, warum sind die Ergebnisse wahrscheinlich unterschiedlich). Überprüfen Sie für a) - g) die Ergebnisse mit dem SQLChecker, der auch bei der Abnahme genutzt wird. und von der Web-Seite der Veranstaltung geladen werden kann, Nutzungsdetails sind in der NetBeans-Anleitung am Ende ergänzt worden: http://home.edvsz.hsosnabrueck.de/skleuker/querschnittlich/NetbeansNutzung.pdf. Die Aufgaben stehen im Zweig „Grundlagen“. Seite 1 von 3 Prof. Dr. Stephan Kleuker Hochschule Osnabrück Fakultät Ing.-Wissenschaften und Informatik - Software-Entwicklung - Datenbanksysteme Wintersemester 2016/17 6. Aufgabenblatt Tragen Sie zunächst unter „Datenbankverbindungen“ Ihre Datenbankverbindung ein, die u. a. in einer NetBeans-Verbindung angezeigt wird und folgende Form hat: jdbc:derby://localhost:1527/[Name Ihrer DB];user=[Nutzername];password=[Passwort] also z. B. jdbc:derby://localhost:1527/Mondial;user=kleuker;password=kleuker Aufgabe 18 (0 Punkte, freiwillig, wird bei Bedarf besprochen) Der Inhaber eines Möbelgeschäfts vermerkt alle Aufträge seines kleinen Unternehmens mit Hilfe eines Tabellenkalkulationsprogramms. Dabei wird lediglich eine einzige Tabelle Auftrag (siehe unten mit Beispielfüllung) benutzt. Kauft ein Kunde gleichzeitig mehrere unterschiedliche Artikel, werden in die Tabelle entsprechend viele Zeilen aufgenommen. (Der folgenden Tabelle lässt sich daher beispielsweise entnehmen, dass ein Herr Meier aus Köln am 24.04.06 zwei Lampen und einen Sessel gekauft hat.) Die Tabelle enthält nur Beispieldaten. AuftragsNr Datum KundenNr Name Ort ArtikelNr Bezeichnung Menge 135 24.04.06 68 Meier Köln 23 Lampe 2 135 24.04.06 68 Meier Köln 26 Sessel 1 136 25.04.06 174 Müller Bonn 23 Lampe 3 137 25.04.06 210 Kunz Berlin 12 Lampe 4 138 26.04.06 68 Meier Köln 23 Lampe 2 Zwischen den Attributen der Tabelle gibt es folgende vollständig funktionalen Abhängigkeiten, alle weiteren funktionalen Abhängigkeiten können davon abgeleitet werden: o {AuftragsNr} {Datum, KundenNr} o {KundenNr} {Name, Ort} o {ArtikelNr} {Bezeichnung} o {AuftragsNr, ArtikelNr} {Menge} Seite 2 von 3 Prof. Dr. Stephan Kleuker Hochschule Osnabrück Fakultät Ing.-Wissenschaften und Informatik - Software-Entwicklung - Datenbanksysteme Wintersemester 2016/17 6. Aufgabenblatt a) Identifizieren Sie alle Schlüsselkandidaten der Tabelle und begründen Sie, warum es sich um Schlüsselkandidaten handelt. b) Begründen Sie formal, warum {AuftragsNr} {Ort} gilt. c) Befindet sich die Tabelle in der ersten Normalform? Begründen Sie Ihre Antwort. d) Erläutern Sie, ob sich die Tabelle in der zweiten Normalform (2NF) befindet. Falls nicht, überführen Sie die Tabelle in die 2NF. Nehmen Sie dabei nur die unbedingt notwendigen Veränderungen vor. Markieren Sie die Schlüsselkandidaten der Tabellen. e) Erläutern Sie, ob sich alle Tabellen aus d) in der dritten Normalform (3NF) befinden. Falls nicht, überführen Sie die Tabellen in die 3NF. Nehmen Sie dabei nur die unbedingt notwendigen Veränderungen vor. Markieren Sie die Schlüsselkandidaten der Tabellen. Seite 3 von 3