Datenbanken

Transcript

Datenbanken M. Jakob Gymnasium Pegnitz 20. Februar 2016 Inhaltsverzeichnis 1 Grundlagen eines Datenbankmanagementsystems (DBMS) 2 Grundlegende Datenbankabfragen ohne verknüpfte Selektionen 3 Erweiterte Abfragen an eine Tabelle 4 Gruppieren und Zusammenfassen von Datensätzen 5 Verknüpfung von Tabellen 6 Syntaxdiagramme 7 Beziehungen zwischen Klassen In diesem Abschnitt Grundlagen eines Datenbankmanagementsystems (DBMS) 1.1 Speichern großer Datenmengen 1.2 Klassendiagramme und Datentypen 1.3 Aufbau von Datentabellen 1 1 1 Speichern großer Datenmengen Grundlagen Speichern großer Datenmengen Beim Suchen bestimmter Artikel wird eine interne Datenbank befragt und die Ergebnisse beliebig sortiert ausgegeben. Gleichzeitig greifen weltweit viele Benutzer gleichzeitig auf die Datenbank zu. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 4 / 138 1 1 Verwendung von Datenbanken Grundlagen Speichern großer Datenmengen Heutige tägliche Kommunikationsabläufe sind ohne Datenbanken nicht mehr denkbar. Alle möglichen Internetseiten (CD-Datenbanken, Datenbank für Online-Spiele) Kundenverwaltung der Telekom Flugbuchungssysteme von Reisebüros Personendaten Einwohnermeldeämter Jede Form der Lagerverwaltung (Kaufhäuser, Supermärkte, Materiallager) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 5 / 138 1 1 Anforderungen an eine Datenbank Grundlagen Speichern großer Datenmengen 1 Die Daten sollten einfach erfasst, gespeichert und abgerufen werden können. 2 Eingabefehler sollten vom System erkannt werden. 3 Durch Abfragen sollen Informationen gezielt abgerufen werden können. 4 Verschiedene Sachbearbeiter (Anwendungen) müssen unabhängig voneinander die Datenbestand bearbeiten können. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 6 / 138 Grundlagen 1 1 Speichern großer Datenmengen Zusammenfassung Datenbank Große Datenmengen werden so gespeichert, dass gezielt ausgewählte Teile je nach Fragestellung geeignet zusammengestellt werden können. Der Anwender sollte dabei niemals direkt auf die Daten zugreifen sondern ein Datenbankmanagementsysteme (DBMS) verwenden. So sind die Daten vor unsachgemäßer Nutzung geschützt. Anwendung Anwendung Anwendung Anwendung Anwendung M. Jakob (Gymnasium Pegnitz) DBMS Daten Datenbank Datenbanken 20. Februar 2016 7 / 138 Grundlagen 1 1 Speichern großer Datenmengen Übung Ü 1.1: Grenzen von normalen Datentabellen Betrachte die Datei Einkauf.ods, sie zeigt eine Zusammenstellung von gekauften Waren. Beantworte folgende Fragen und gib an, für warum und wen diese Frage von Interesse sein könnte. (a) Welches Produkt war am teuersten? (b) Wie viele Artikel wurden bei Karma gekauft? (c) Wer hat insgesamt am meisten Geld ausgegeben? (d) Wie viele Artikel wurden bei Oldi am 26.8.2007 gekauft? (e) Formuliere in einem Satz, warum die Datei Einkauf.ods nicht geeignet ist, große Datenmengen zu speichern. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 8 / 138 Grundlagen 1 1 Speichern großer Datenmengen Übung Ü 1.2: Grenzen von normalen Datentabellen Nenne zu jeder oben aufgeführten Anforderungen an eine Datenbank mindestens ein Beispiel aus der Datei Einkauf.ods, bei dem diese Anforderung nicht erfüllt ist. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 9 / 138 In diesem Abschnitt Grundlagen eines Datenbankmanagementsystems (DBMS) 1.1 Speichern großer Datenmengen 1.2 Klassendiagramme und Datentypen 1.3 Aufbau von Datentabellen 1 1 2 Beispiel Bibliotheksverwaltung Grundlagen Klassendiagramme und Datentypen Jedes Attribut einer Klasse besitzt einen bestimmten Datentyp. Nur so kann das DBMS wissen, wie die Daten interpretiert werden sollen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 11 / 138 Klassendiagramme und Datentypen 1 2 Datentypen in SQL (unserer DB-Sprache) Grundlagen Datentyp VARCHAR[n] CHAR[n] INT FLOAT DOUBLE DECIMAL[n;d] DATE TIME DATETIME M. Jakob (Gymnasium Pegnitz) Beschreibung Variable Zeichenfolge von max. n Zeichen Zeichenfolge von genau n Zeichen (Rest Leerzeichen) Ganze Zahlen (etwa von -2Mrd. Bis +2Mrd.) Kommazahlen mit 7 gültigen Ziffern Kommazahlen mit 15 gültigen Ziffern Kommazahl mit d Dezimalstellen und insgesamt n Ziffern Datumsangaben Zeitangaben Kombination aus Datums- und Zeitangabe Datenbanken 20. Februar 2016 12 / 138 Grundlagen 1 2 Klassendiagramme und Datentypen Übung Ü 1.3: Erweiterung Bibliotheksverwaltung Öffne die Zeichnungen 02_Aufg3_Bibliothek.graphml. Dort findest du eine Vorlage des oben abgebildeten Klassendiagramms. (a) Ergänze die fehlenden Einträge. (b) Füge die Klasse VERLAG mit folgenden Attributen hinzu: Name, Strasse, PLZ, Ort, Telefon, Fax, email, website. Lege selbst sinnvolle Datentypen fest. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 13 / 138 Grundlagen 1 2 Klassendiagramme und Datentypen Übung Ü 1.4: Klassenkarte Einkauf Erstelle zu der Tabelle Einkauf.ods eine Klassenkarte. Lege selbst sinnvolle Datentypen fest (Vorlage 2_Aufg4_Einkauf.graphml) Wo finden sich der Klassenname und die Attribute des Klassendiagramms in der Tabelle wieder? M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 14 / 138 Grundlagen 1 2 Klassendiagramme und Datentypen Übung Ü 1.5: Verbesserung Einkauf Die Klasse Einkauf soll in vier Klassen Kauf, Person, Artikel und Geschäft aufgeteilt werden. (a) Öffne die Vorlage 02_Aufg5_Einkauf.graphml und erstelle die fehlenden Klassenkarten. Ordne dabei die Attribute Name (des Kunden), Geschlecht, Sparte, Warenbezeichnung, Preis, Zahlungsart, Name (für des Geschäftes) und Kaufdatum der richtigen Klasse zu. (b) Lege sinnvolle Relationen zwischen den Klassen fest. (c) Warum kann es sinnvoll sein, die eine Tabelle Einkauf in diese vier Tabellen aufzuteilen? M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 15 / 138 In diesem Abschnitt Grundlagen eines Datenbankmanagementsystems (DBMS) 1.1 Speichern großer Datenmengen 1.2 Klassendiagramme und Datentypen 1.3 Aufbau von Datentabellen 1 1 Aufbau von Datentabellen Grundlagen 3 Aufbau von Datentabellen Beispiel: Tabelle city einer Datenbank world Die Objekte einer Tabelle sind Datensätze. Ihre Attribute stehen in den Spaltenbezeichnungen. Die Attributwerte der einzelnen Attribute finden sich in den zugehörigen Spalten. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 17 / 138 Aufbau von Datentabellen 1 3 Definition Tabellenschema und Schlüssel Grundlagen Definition Tabellenschema und Schlüssel Jeder Datensatz muss eindeutig identifizierbar sein. Spalten(-kombination) die jeden Datensatz eindeutig festlegt, nennt man Schlüssel. Wenn sich keine Spaltenkombination als Schlüssel eignet, verwendet man künstliche Schlüssel. Bekannte künstliche Schlüssel sind die EAN (Strichcode) von Waren oder die Personalausweisnummer. Im Tabellenschema werden der Tabellenname, und die Attribute samt Datentypen aufgeführt und der Schlüssel unterstrichen. Tabellenschema für obige Tabelle: city(ID: INT; Name: VARCHAR[40], ContryCode: CHAR[3], Destrict: VARCHAR[40], Population: INT) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 18 / 138 Grundlagen 1 3 Aufbau von Datentabellen Übung åÜ 1.6: TERRA-Datenbank erforschen Die Datenbank TERRA enthält geographische Informationen über der Erde. Besuche die Seite åhttp://www.sn.schule.de/ reimegym/terra/index.html (a) Surf dich durch die Seite und verschaffe dir so einen Überblick. (b) Wie lautet das Tabellenschema der Tabelle LAND? Welchen Schlüssel, wie viele Atribute und wie viele Spalten hat diese Tabelle? Was fehlt in dem Tabellenschema? (c) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als Schlüssel? (d) Warum reicht es in der Tabelle STADT nicht, nur den Namen der Stadt als Schlüssel zu verwenden? (e) Stelle eine Vermutung auf, welche Schlüssel künstlich sind. (f) Ergänze die Attribute der Tabellen Land, Stadt und Berg um sinnvolle Datentypen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 19 / 138 Grundlagen 1 3 Aufbau von Datentabellen Übung åÜ 1.7: Datenbank Bundesliga erforschen Die Datenbank Bundesliga enthält Informationen über die Fußball Bundesliga. Besuche die Seite åhttp://dbup2date.uni-bayreuth.de/ (a) Surf dich durch die Seite und verschaffe dir so einen Überblick. (b) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als Schlüssel? (c) Stelle eine Vermutung auf, welche Schlüssel künstlich sind. (d) Erstelle die Klassenkarten zu den angegebenen Tabellenschamata M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 20 / 138 Grundlagen 1 3 Aufbau von Datentabellen Übung åÜ 1.8: Datenbank Wetter in Deutschland erforschen Die Datenbank Wetter enthält Informationen über das Wetter in Deutschland. Besuche die Seite åhttp://dbup2date.uni-bayreuth.de/ (a) Surf dich durch die Seite und verschaffe dir so einen Überblick. (b) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als Schlüssel? (c) Stelle eine Vermutung auf, welche Schlüssel künstlich sind. (d) Erstelle die Klassenkarten zu den angegebenen Tabellenschamata M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 21 / 138 In diesem Abschnitt Grundlegende Datenbankabfragen ohne verknüpfte Selektionen 2.1 Seletion und Projektion 2.2 Beispiele aus der TERRA-Datenbank 2 Grundlegende Datenbankabfragen 2 1 Seletion und Projektion Das DBMS MySQL Das DBMS MySQL Das bekanntestes Open Source DBMS ist MySQL. Es verwendet die Datenbanksprache SQL (Structured Query Language), mit der man aus einer Datenbankbasis Informationen mit bestimmten Eigenschaften herausfiltern und anzeigen kann. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 23 / 138 Grundlegende Datenbankabfragen 2 1 Seletion und Projektion Selektion und Projektion Selektion und Projektion Die . . . Projektion filtert Spalten (Attribute) Selektion filtert Zeilen mit bestimmten Eigenschaften aus der Tabelle. Selektion und Projektion werden normalerweise miteinander kombiniert M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 24 / 138 2 1 Bespiel: Selektion und Projektion Grundlegende Datenbankabfragen M. Jakob (Gymnasium Pegnitz) Datenbanken Seletion und Projektion 20. Februar 2016 25 / 138 2 1 SQL-Syntax einer Tabellenanfrage Grundlegende Datenbankabfragen Bisher Seletion und Projektion Jetzt SELECT( Spalten ; Tabellen ; Bedingung) SELECT S p a l t e n FROM T a b e l l e n WHERE Bedingung M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 26 / 138 In diesem Abschnitt Grundlegende Datenbankabfragen ohne verknüpfte Selektionen 2.1 Seletion und Projektion 2.2 Beispiele aus der TERRA-Datenbank 2 2 2 Beispiele aus der TERRA-Datenbank Grundlegende Datenbankabfragen Beispiele Alle nachfolgenden Beispiele sind aus der åTERRA-Datenbank entnommen. Dort sind auch die Tabellenschemata zu finden. Wir verwenden zunächst nur die Tabelle BERG (B_NAME, GEBIRGE, HOEHE, JAHR, LAENGE, BREITE) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 28 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 1 Es soll die gesamte Tabelle Berg ausgegeben werden. SELECT * FROM BERG Der Asterix „*“ ist eine Abkürzung, wenn alle Spalten selektiert werden sollen. WHERE kann entfallen, wenn keine speziellen Zeilen ausgewählt werden sollen. TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 29 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 2 Aus der Tabelle Berg sollen nur die Spalten B_NAME und HOEHE selektiert werden. SELECT B_NAME, HOEHE FROM BERG TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 30 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 3 Aus der Tabelle Berg sollen alle Berge (Name und Höhe) des Himalaya ausgegeben werden. SELECT B_NAME, HOEHE FROM BERG WHERE GEBIRGE = " Himalaya " Name und Höhe der Berge sind Projektionen (Spalten) Himalaya ist eine Selektion zur Auswahl der Zeilen, die als Gebirge den Eintrag “Himalaya“ haben Datentypen die Zeichenfolgen enthalten (Strings) müssen in doppelte Hochkommata gesetzt werden. TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 31 / 138 Beispiele 2 2 Vergleichsoperatoren für die Selektion WHERE, 1. Teil Grundlegende Datenbankabfragen Operatoren für die Selektion können sein: <, >, <>, =, >=, <= wie aus der Tabellenkalkulation bekannt. between ... and ... filtert Zeilen deren Merkmal zwischen den angegebenen Werten liegt. Beides kann auf alle Datentypen angewandt werden. Bei Zeichenketten wird die alphabetische Ordnung zugrunde gelegt. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 32 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 4 Gesucht sind alle Berge, die mindestens 7000 Meter hoch sind. SELECT * FROM BERG WHERE HOEHE >= 7000 M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 33 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 5 Gesucht sind alle Berge, die zwischen 1900 und 1950 erstmals bestiegen wurden. SELECT * FROM BERG WHERE j a h r between 1900 and 1950 M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 34 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Beispiel 6 Gesucht sind alle Berge, mit einem „M“ oder „N“ beginnen. SELECT * FROM BERG WHERE B_NAME between "M" and "O" M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 35 / 138 Beispiele 2 2 Bisherige SQL-Syntax einer Tabellenanfrage Grundlegende Datenbankabfragen Für bedingung gilt folgende Syntax: M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 36 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Übung åÜ 2.1: TERRA-Datenbank Bearbeite die Aufgaben 1-4b der Terra-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 37 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele åÜ 2.2: Bundesliga-Datenbank Gib die SQL-Querries zu folgenden Anfragen an die Bundesliga-Datenbank an. (a) Liste alle Verein der Datenbank auf. (b) Liste alle Vereine der zweiten Liga auf. (c) Liste alle Spieler auf, die weniger als 3 Tore geschossen haben (d) Liste alle brasilianischen Spieler auf (e) Liste alle ausländischen Spieler auf (f) Gesucht sind alle Spiele, bei der die Heimmannschaft kein Tore geschossen hat. (g) Gesucht sind alle Vereine, die nicht in der ersten Liga spielen (h) Gesucht sind alle Spiele des ersten Spieltags (i) Gesucht sind alle Spiele, die um 18.30 Uhr begonnen haben. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 38 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele åÜ 2.3: Wetter-Datenbank Gib die SQL-Querries zu folgenden Anfragen an die Wetter-Datenbank an. (a) Liste alle Wetterstation der Datenbank auf. (b) Liste alle Wettermessungen (nur Stations_ID und Datum) auf, die keinen Sonnenschein gemessen haben. (c) Liste alle Wettermessungen auf (Stations_ID, Datum, Niederschlagshoehe), die zwischen 10 und 50 mm Niederschlag gemessen haben. (d) Liste Wetterstationen auf, die nicht vom DWD betrieben werden. (e) Welche Wetterstationen liegen weiter über dem Meerespiegel als Pegnitz? M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 39 / 138 Grundlegende Datenbankabfragen 2 2 Beispiele Übung Ü 2.4: Eigene DB-Abfrage formulieren Formuliere jweils ein Fragestellungen und den dazugehörigen SQL-Befehl mit der angegebenen Bediungung an eine Datenbank deiner Wahl. (a) Es sollen alle Zeilen ausgegeben werden aber nicht alle Spalten (b) Es soll nur eine Projektion aber keine Selektion stattfinden (c) Es sollen alle Spalten ausgegeben werden aber nicht alle Zeilen (d) Es soll nur eine Selektion aber keine Projektion stattfinden (e) Es soll eine Selektion und eine Projektion stattfinden åcia-Datenbank åTERRA-Datenbank åBundesliga-Datenbank åWetter-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 40 / 138 In diesem Abschnitt 3 3.1 3.2 3.3 3.4 Erweiterte Abfragen an eine Tabelle Bedingungen verbinden Zeichenfolgen suchen Ausgaben Sortieren und begrenzen Vollständiger select-Term Erweiterte Abfragen an eine Tabelle 3 1 Bedingungen verbinden Beispiel 1 Gesucht sind alle Berge des Himalaya, die mindestens 7000 Meter hoch sind. SELECT * FROM BERG WHERE HOEHE >= 7000 AND Gebirge = ’Himalaya ’ M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 42 / 138 Erweiterte Abfragen an eine Tabelle 3 1 Bedingungen verbinden Beispiel 2 Gesucht sind alle Berge des Himalaya und der Anden. SELECT * FROM BERG WHERE Gebirge = ’Anden ’ OR Gebirge = ’Himalaya ’ M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 43 / 138 Erweiterte Abfragen an eine Tabelle 3 1 Bedingungen verbinden Beispiel 3 Gesucht sind alle Berge über 8000 m außerhalb des Himalaya. SELECT * FROM BERG WHERE HOEHE >=8000 AND NOT( Gebirge = ’Himalaya ’ ) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 44 / 138 Bedingungen verbinden 3 1 Logische Operatoren für die Selektion WHERE Erweiterte Abfragen an eine Tabelle Die logischen Operatoren für die Selektion sind NOT, AND, OR wie aus der Tabellenkalkulation bekannt. Beachte: AND ist genau dann wahr ist, wenn beide Ausdrücke wahr sind, OR ist genau dann wahr ist, wenn mindestens ein Ausdruck wahr ist und NOT ist genau dann wahr ist, wenn der Ausdruck falsch ist. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 45 / 138 Bedingungen verbinden 3 1 Erweiterte Syntaxdiagramme der WHERE-Clause Erweiterte Abfragen an eine Tabelle Für bedingung ist das Syntaxdiagramm noch unverändert: M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 46 / 138 In diesem Abschnitt 3 3.1 3.2 3.3 3.4 Erweiterte Abfragen an eine Tabelle Bedingungen verbinden Zeichenfolgen suchen Ausgaben Sortieren und begrenzen Vollständiger select-Term 3 Zeichenfolgen Selektieren Erweiterte Abfragen an eine Tabelle 2 Zeichenfolgen suchen Mit dem like-Operator kann man in Zeichenfolgen nach Mustern suchen. % steht für eine beliebige Zeichenfolge (auch für eine der Länge Null) und _ steht für ein einzelnes beliebiges Zeichenfolge. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 48 / 138 Zeichenfolgen suchen 3 2 Erweitertes Syntaxdiagramm der Bedingung Erweiterte Abfragen an eine Tabelle Unverändertes Syntaxdiagramm der WHERE-Clause Erweitertes Syntaxdiagramm der bedingung M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 49 / 138 Erweiterte Abfragen an eine Tabelle 3 2 Zeichenfolgen suchen Beispiel 1 Gesucht sind alle Berge der Alpen. SELECT * FROM BERG WHERE GEBIRGE l i k e ’% Alpen %’ M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 50 / 138 Erweiterte Abfragen an eine Tabelle 3 2 Zeichenfolgen suchen Beispiel 2 Gesucht sind alle Berge der Alpen, die eine ‘r‘ am zweiter Stelle haben. SELECT * FROM BERG WHERE GEBIRGE l i k e ’% Alpen %’ AND B_NAME l i k e ’_r%’ M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 51 / 138 Erweiterte Abfragen an eine Tabelle 3 2 Zeichenfolgen suchen Übung åÜ 3.1: TERRA-Datenbank (a) Bearbeite die Aufgaben 4c, d, e, 5, 8, 9, 11 der Terra-Datenbank (b) Gehe zum letzten Abschnitt des Skriptes (Syntaxdiagramme) und gib den Weg deiner Abfragen durch die Diagramme an. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 52 / 138 Erweiterte Abfragen an eine Tabelle 3 2 Zeichenfolgen suchen åÜ 3.2: cia-Datenbank Gib die SQL-Querries und Syntaxwege zu folgenden Anfragen an die cia-Datenbank an. (a) Gib die Namen aller asiatischen Länder aus, die weniger als 10 Millionen Einwohner haben. (5) (b) Gib die Namen aller Länder aus, die kleiner als 500000 Quadratkilometer sind oder deren BIP unter 100 Mio Dollar liegt. (12) (c) Gib die Namen aller nicht-afrikanischen Länder aus, deren BIP unter 100 Mio Dollar liegt. (10) (d) Gib die Namen aller europäischen Länder aus, die weniger als 10 Millionen Einwohner haben. (30 − 40) (e) Gib die Namen aller amerikanischen Länder aus, die weniger als 10 Millionen Einwohner haben. (35 − 45) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 53 / 138 Erweiterte Abfragen an eine Tabelle 3 2 Zeichenfolgen suchen Übung Ü 3.3: Eigene DB-Abfrage formulieren Formuliere, passend zu diesem Abschnitt, vier eigene Anfragen mit steigendem Schwierigkeitsgrad an eine unserer Datenbanken. åcia-Datenbank åTERRA-Datenbank åBundesliga-Datenbank åWetter-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 54 / 138 In diesem Abschnitt 3 3.1 3.2 3.3 3.4 Erweiterte Abfragen an eine Tabelle Bedingungen verbinden Zeichenfolgen suchen Ausgaben Sortieren und begrenzen Vollständiger select-Term Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Ausgaben sortieren Mit der ORDER BY-Clause kann man Ausgaben sortieren. Das Schlüsselwort ASC sortiert aufsteigend (Voreinstellung) DESC sortiert absteigend. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 56 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Ausgaben begrenzen Mit der LIMIT-Clause kann man bei langen Tabellen die Anzahl der Ergebnisse begrenzen. LIMIT n liefert die ersten n Datensätze, LIMIT n, m lässt die ersten n Datensätze weg und zeigt die nächsten m. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 57 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Beispiel 1 (TERRA) Gesucht sind alle Länder, sortiert nach der Fläche mit dem kleinsten beginnend. SELECT * FROM LAND ORDER BY FLAECHE TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 58 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Beispiel 2 (TERRA) Gesucht sind alle Länder, sortiert nach der Fläche mit dem größten beginnend. SELECT * FROM LAND ORDER BY FLAECHE DESC TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 59 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Beispiel 3 (TERRA) Gesucht sind die drei flächengrößten Länder. SELECT * FROM LAND ORDER BY FLAECHE DESC LIMIT 3 TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 60 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Beispiel 4 (TERRA) Gesucht sind die Länder mit der viert- bis zehntgrößten Fläche SELECT * FROM LAND ORDER BY FLAECHE DESC LIMIT 3 , 7 TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 61 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Beispiel 5 (TERRA) Gesucht sind die Berge sortiert nach dem Gebirge und dann nach der Berghöhe. SELECT * FROM BERG ORDER BY GEBIRGE ASC,HOEHE DESC TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 62 / 138 Ausgaben Sortieren und begrenzen 3 3 Syntaxdiagramm: order by-Term (vollständig) Erweiterte Abfragen an eine Tabelle M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 63 / 138 Ausgaben Sortieren und begrenzen 3 3 Syntaxdiagramm: limit-Term (vollständig) Erweiterte Abfragen an eine Tabelle M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 64 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Übung åÜ 3.4: Wetter-Datenbank Gib die SQL-Querries und Syntaxwege an. (a) Gib die Wetterstationen alphabetisch sortiert aus. (b) Gib Standort und geographische Breite der Wetterstation von Nord nach Süd sortiert an. (c) Gib die zehn höchstgelegenen Wetterstationen an. (d) An welchen Tagen hat es in München (Stations_ID = 10870) nicht geregnet? Sortiere nach dem Datum aufsteigend. (e) Gib alle Wettermessungen an, die weder Regen noch Sonnenschein und maximale Windgeschwindigkeit kleiner 10 gemessen haben. Die Sortierung soll nach der Wetterstation erfolgen und innerhalb dieser Sortierung soll nach der max. Windgeschwindigkeit sortiert werden. (f) Gib die 20 windstärksten Wettermessungen aus. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 65 / 138 Erweiterte Abfragen an eine Tabelle 3 3 Ausgaben Sortieren und begrenzen Übung Ü 3.5: Eigene DB-Abfrage formulieren Formuliere, passend zu diesem Abschnitt, vier eigene Anfragen mit steigendem Schwierigkeitsgrad an eine der unten angegebenen Datenbanken. åcia-Datenbank åTERRA-Datenbank åBundesliga-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 66 / 138 In diesem Abschnitt 3 3.1 3.2 3.3 3.4 Erweiterte Abfragen an eine Tabelle Bedingungen verbinden Zeichenfolgen suchen Ausgaben Sortieren und begrenzen Vollständiger select-Term 3 4 Mehrfachnennungen vermeiden Erweiterte Abfragen an eine Tabelle Vollständiger select-Term Gesucht sind alle Gebirge die in der Tabelle Berg enthalten sind. SELECT GEBIRGE FROM BERG Erzeugt Mehrfachnennungen. TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 68 / 138 3 4 Mehrfachnennungen vermeiden Erweiterte Abfragen an eine Tabelle Vollständiger select-Term Das Schlüsselwort DISTINCT nach dem Schlüsselwort SELECT vermeidet Mehrfachnennungen M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 69 / 138 Erweiterte Abfragen an eine Tabelle 3 4 Vollständiger select-Term Ausdrücke in Spalten Gib die Berge der TERRA-Datenbank mit der Angabe der vollständigen Tausender an. SELECT BERG, HOEHE, round (HOEHE / 1000) FROM BERG TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 70 / 138 Erweiterte Abfragen an eine Tabelle 3 4 Vollständiger select-Term Ausdrücke in Spalten In Spalten können Rechenausdrücke mit den üblichen Rechenzeichen angegeben werden. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 71 / 138 3 Umbenennen von Spalten Erweiterte Abfragen an eine Tabelle 4 Vollständiger select-Term Gib der Spalte der vollständigen Tausender den Name „Tausender“ SELECT BERG, HOEHE, round (HOEHE / 1000) AS ’ Tausender ’ FROM BERG TERRA M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 72 / 138 3 Umbenennen von Spalten Erweiterte Abfragen an eine Tabelle 4 Vollständiger select-Term Mit dem Schlüsselwort AS lassen sich Spalten umbenennen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 73 / 138 3 4 åÜ 3.6: cia-Datenbank Erweiterte Abfragen an eine Tabelle Übung Vollständiger select-Term Gib die SQL-Querries an. (a) Welche Regionen sind die der Datenbank verzeichnet? (b) Gib den Namen die Fläche, die Einwohnerzahl und die Einwohner pro Fläche in Tausenden an. Diese letztes Spalte soll die Überschrift Einw. pro qkm in Tsd bekommen. Sortiere nach dieser letzten Spalte. (c) Gib den Namen das BIP, die Einwohnerzahl und das BIP pro Einwohner an. Diese letztes Spalte soll die Überschrift BIP pro Einw bekommen. Sortiere nach dieser letzten Spalte. Es sollen nur die ersten 20 Datensätze ausgegeben werden. (d) Gib für alle europäischen Länder den Namen, die Region und das BIP pro Quadratkilometer an. Sortiere alphabetisch nach der Region und innerhalb einer Region nach dem BIP pro Quadratkilometer. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 74 / 138 Vollständiger select-Term 3 4 åÜ 3.7: Bundesliga-Datenbank Erweiterte Abfragen an eine Tabelle Übung Gib die SQL-Querries und Syntaxwege an. (a) Welche Nationalitäten gibt es unter den Spielern der Bundesliga? Die Ausgabe soll alphabetisch sortiert sein und die Überschrift vertretene Nationen besitzen. (b) Welche Trikot-Nummern wurden vergeben? Ergebnis sortiert angeben! (c) Gib alle Spalten und die Gesamtzahl der Tore von den Spielen aus, bei denen mehr als vier Tore gefallen sind. Sortiere nach dem Datum. (d) Gib die Spiele sortiert aus, die 2013 nach 18.00 Uhr stattfanden. Bezeichne die Uhrzeitspalte mit Abendspiel. (e) Die Torgefährlichkeit eines Spielers berechnet man mittels 2· Torzahl + Vorlagenzahl. Gib die Spieler„ die nach dieser Formel am torgefährlichsten sind, und ihre Torgefährlichkeit sortiert aus. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 75 / 138 Erweiterte Abfragen an eine Tabelle 3 4 Vollständiger select-Term Übung Ü 3.8: Eigene DB-Abfrage formulieren Formuliere, passend zu diesem Abschnitt, zwei eigene Anfragen an eine der unten angegebenen Datenbanken. åcia-Datenbank åTERRA-Datenbank åBundesliga-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 76 / 138 3 4 åÜ 3.9: TERRA-Datenbank Erweiterte Abfragen an eine Tabelle Übung Vollständiger select-Term Formuliere, zu den angegebenen Syntaxwegeg je eine Anfrage an die TERRA-Datenbank (a) s 2−6−8−4−2 f/f w 2 b 1 −4/b /w o 1 /o /s (b) s 2−7−8−4−2 f/f o 1−4 / o l 1 /l /s M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 77 / 138 In diesem Abschnitt Gruppieren und Zusammenfassen von Datensätzen 4.1 Aggregatfunktionen 4.2 Grupierungen 4.3 Auswahl von Gruppen 4 Gruppieren und Zusammenfassen von Datensätzen 4 1 Aggregatfunktionen åBeispiel 1 (TERRA) Wie viele Flüsse sind in der Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 79 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 1 Aggregatfunktionen åBeispiel 1 (TERRA) Wie viele Flüsse sind in der Datenbank SELECT count ( * ) as ’Anzahl der Flüsse ’ FROM FLUSS M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 79 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 1 Aggregatfunktionen Aggregatfunktionen Aggregatfunktionen werten Tabellen statistisch aus. Im select-Term sind erlaubt: COUNT (Ausdruck) Anzahl der Datensätze SUM (Ausdruck) Summe, AVG (Ausdruck) Durchschnitt, der Werte von Ausdruck MIN (Ausdruck) Minimum MAX (Ausdruck) Maximum aller Werte von Ausdruck M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 80 / 138 In diesem Abschnitt Gruppieren und Zusammenfassen von Datensätzen 4.1 Aggregatfunktionen 4.2 Grupierungen 4.3 Auswahl von Gruppen 4 Gruppieren und Zusammenfassen von Datensätzen 4 2 Gruppierungen åBeispiel 1 (TERRA) Liste die Anzahl der Zuflüsse in jedes Meer auf SELECT Meer , count ( * ) as Z u f l ü s s e FROM FLUSS Group by Meer M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 82 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 2 Gruppierungen åBeispiel 2 (TERRA) Liste die Anzahl der Wüsten, gruppiert nach der Wüstenart auf SELECT WUESTENART, count ( * ) FROM WUESTE Group by WUESTENART M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 83 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 2 Gruppierungen group by-Klausel group by gruppiert Datensätze nach bestimmten Eigenschaften. Die Aggregatfunktionen werden dann auf die Gruppen einzeln angewendet. Außer den Aggregatfunktionen dürfen in der select-Klausel nur Attribute vorkommen, die in der group by-Klausel aufgeführt sind. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 84 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 2 Gruppierungen åBeispiel 3 (TERRA) Liste die Gesamtfläche der Wüstenarten SELECT WUESTENART, sum(FLAECHE) AS Gesamtflaeche FROM WUESTE group by WUESTENART ORDER BY Gesamtflaeche M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 85 / 138 In diesem Abschnitt Gruppieren und Zusammenfassen von Datensätzen 4.1 Aggregatfunktionen 4.2 Grupierungen 4.3 Auswahl von Gruppen 4 Gruppieren und Zusammenfassen von Datensätzen 4 3 Auswahl von Gruppen having-Klausel having-Klausel Oft werden bei einer Gruppierung nur die Gruppen gewünscht, die bestimmte Bedingungen erfüllen. Diese Bedingungen werden in der having-Kausel formuliert. Dabei dürfen Gruppierungsausdrücke verwendet werden. Die Anfragen werden in folgender Reihenfolge ausgewertet: 1 Auswahl der Zeilen durch die WHERE-Klausel, 2 Bildung der Gruppen durch die GROUP BY-Klausel, 3 Auswahl der Gruppen, die die HEAVING-Klausel erfüllen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 87 / 138 Gruppieren und Zusammenfassen von Datensätzen 4 3 Auswahl von Gruppen åBeispiel 1 (TERRA) Liste die Anzahl der Nachbarländer für jedes Land auf, das mehr als 5 Nachbarn hat. s e l e c t LAND1, count ( * ) AnzahlNachbarlaender from IST_BENACHBART_ZU group by LAND1 having count ( * ) > 5 order by anzahlNachbarlaender desc M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 88 / 138 Auswahl von Gruppen 4 3 åÜ 4.1: cia-Datenbank (analog Übg 2 cia) Gruppieren und Zusammenfassen von Datensätzen Gib die SQL-Querries an. (a) Wie viele Länder enthält die cia-Datenbank? (b) Ermittle die Weltbevölkerung. (c) Gib das Durchschnitts-Bruttoinlandsprodukt an. (d) Wie groß sind Bevölkerung und Bruttoinlandsprodukt für ganz Europa? (e) Ermittle die Flächen des kleinsten und größten Landes. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 89 / 138 Auswahl von Gruppen 4 3 åÜ 4.2: cia-Datenbank (analog Übg 4 Aufg 1-5 cia) Gruppieren und Zusammenfassen von Datensätzen Gib die SQL-Querries an. (a) Zeige von jeder Region den Namen und die Anzahl der Länder an. (b) Zeige für alle Regionen den Namen und die Anzahl der Länder mit mehr als 10 Millionen Einwohnern an. (c) Welche Regionen haben eine Gesamtbevölkerung von mindestens 100 Millionen? (d) Stelle die Regionen der Erde mit Einwohnerzahl und Gesamtfläche dar, geordnet nach der Einwohnerzahl. (e) Wie vorhergehende Aufgabe aber nur die Regionen von Amerika. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 90 / 138 Auswahl von Gruppen 4 3 åÜ 4.3: WM-Titel-Datenbank (analog Übg 4 Aufg 6-10 WMTitel) Gruppieren und Zusammenfassen von Datensätzen Gib die SQL-Querries an. (a) Ermittle die von jedem Weltmeister erreichte Gesamtzahl von WM-Punkten und stelle das Ergebnis nach WM-Punkten geordnet dar. (b) Welche Gesamtpunktzahlen der Konstrukteursweltmeisterschaft haben die Teams in den neunziger Jahren erreicht? (c) Wie vorherige Aufgabe, allerdings sollen nur Teams mit mindestens 100 Punkten ausgegeben werden. (d) Ermittle für die Jahre, in denen Michael Schumacher gefahren ist, die durchschnittliche Zahl der erreichten Team-Punkte des Konstrukteursweltmeisters und stelle die Liste geordnet dar. (e) Ermittle ab 1995 in geordneter Reihenfolge die Gesamtzahl der Team-Punkte für Teams mit mindestens 200 Punkten. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 91 / 138 4 3 åÜ 4.4: TERRA-Datenbank Gruppieren und Zusammenfassen von Datensätzen Auswahl von Gruppen Gib die SQL-Querries an. (a) Gesucht sind alle Flüsse (Name), die in die Ostsee oder Nordsee münden und über 1000 km lang sind. [4] (b) Gesucht sind alle Wüsten (Name) der Art ’Sandwueste’ mit einer Fläche größer als 25000 qkm. [9] (c) Gib für alle Millionenstädte, die in den Tropen liegen, die Namen und ihre Koordinaten an. (Die Tropen liegen zwischen 23.27 Grad nördlicher und 23.27 Grad südlicher Breite.) [65] (d) Gib die Ländernamen und die Zahl der Einwohner für alle Länder an, die mehr als 45 Millionen Einwohner haben. Ordnen Sie nach der Einwohnerzahl! [25] (e) Gesucht ist der Anteil der Meere an der Erdoberfläche (Angabe in Prozent). Hinweise: Oberflächenformel: O = 4πr 2 , Erdradius gleich 6370 km. [1] M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 92 / 138 4 3 åÜ 4.5: TERRA-Datenbank Gruppieren und Zusammenfassen von Datensätzen Auswahl von Gruppen Gib die SQL-Querries an. (a) Gesucht ist für alle Inselgruppen deren Gesamtfläche. [41] (b) Gesucht ist von jeder Inselgruppe die Fläche der größten Insel. [41] (c) Gesucht sind alle Inselgruppen sowie die Anzahl der zugehörigen Inseln für alle Inselgruppen mit mehr als einer Insel. [21] (d) Gesucht sind alle Flüsse mit mehr als zwei an ihnen liegenden Städten, sortiert nach dieser Anzahl. [21] (e) Gesucht sind alle Städte durch die mehr als einem Fluss fließt, sortiert nach dieser Anzahl. [8] (f) Gesucht sind alle Städte, in denen mehrere Organisationen ihren Sitz haben. [10] (g) Welche Länder haben mehr als 4 Millionenstädte? [7] M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 93 / 138 In diesem Abschnitt Verknüpfung von Tabellen 5.1 Basisabfragen 5.2 Schema für Join-Abfragen 5 Verknüpfung von Tabellen 5 1 Basisabfragen Bespiel Gesucht ist der Name des Klassenleiters der 5. Klasse. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 95 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Joins Joins Oft werden bei einer SQL-Anfrage Daten gewünscht, die aus verschiedenen Tabellen stammen. Dazu müssen die Tabellen verknüpft werden. Solche Verknüpfungen nennt man Joins. Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten Tabellen in der From- Klausel. Bei gleichen Spaltennamen aus verschiedenen Tabellen muss in der Abfragen zusätzlich der Tabellenname angegeben werden. Man schreibt: Tabellenname.Attributname. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 96 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Beispiel 1 M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 97 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Beispiel 1 Die Spalte Name ist zweideutig (Lehrernamen, Klassenname). Es muss der Klassenname mit Klasse.Name und der Name der Lehrkraft mit Lehrkraft.Name angesprochen werden. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 97 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Select Anfrage Gewünscht ist eine Liste der Klassenleiter mit den jeweiligen Klassen. SELECT Klasse . Name, L e h r k r a f t . Name FROM L e h r k r a f t , Klasse WHERE PersNr = K l a s s e n l e i t u n g ; M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 98 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen åBeispiel 1 (TERRA) Gewünscht ist eine Liste welches Land zu welchem Kontinent gehört. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 99 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen åBeispiel 1 (TERRA) Gewünscht ist eine Liste welches Land zu welchem Kontinent gehört. SELECT L_Name , K_Name FROM LAND, UMFASST WHERE LAND . L_ID = UMFASST. L_ID M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 99 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Joins Joins Lässt man die WHERE-Klausel bei Joins mit zwei Tabellen weg, werden in der Ergebnistabelle alle Zeilen der ersten Tabelle mit allen Zeilen der zweiten Tabelle kombiniert. Wir erhalten also eine Tabelle deren Zeilenzahl so groß ist, wie das Produkt der Zeilenzahlen der Ausgangstabelle. Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten Tabellen in der From- Klausel. Bei gleichen Spaltennamen aus verschiedenen Tabellen muss in der Abfragen zusätzlich der Tabellenname angegeben werden. Man schreibt: Tabellenname.Attributname. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 100 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Bespiel SELECT Klasse . Name, L e h r k r a f t . Name FROM L e h r k r a f t , Klasse liefert also 7 ∗ 2 = 14 Zeilen und 5 + 3 Spalten. Viele davon sind unsinnig. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 101 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen Bespiel M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 102 / 138 Verknüpfung von Tabellen 5 1 Basisabfragen åBeispiel (TERRA) SELECT L_Name , K_Name FROM LAND, UMFASST Es werden alle Länder(190) mit allen UMFASST-Zeilen (188) kombiniert es entstehen 35720 Zeilen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 103 / 138 5 1 åÜ 5.1: TERRA-Datenbank Verknüpfung von Tabellen Basisabfragen Gib die SQL-Querries an. (a) Gesucht ist eine Aufstellung der Länder mit ihren Landesteilen. [551] (b) Gesucht ist eine Aufstellung aller deutschen Städte. [114] (c) Gesucht ist eine Aufstellung aller Organisationen mit ihrem Hauptsitz. [55] (d) Gesucht ist eine Aufstellung aller Flüsse, mit ihren Ländern, durch die sie fließen. [185] M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 104 / 138 5 1 åÜ 5.2: Bundesliga-Datenbank Verknüpfung von Tabellen Basisabfragen Gib die SQL-Querries an. (a) Gesucht ist die Vereinskader (Vereinsname, Spielername). (b) Gesucht ist die Größe der Vereinskader (c) Was liefert folgende Abfrage? Warum ist eine Abfrage über 3 Tabellen nötig? Select S p i e l t a g , h . Name as Heimmannschaft , g . Name as Gastmannschaft , Tore_Heim , Tore_Gast from V e r e i n as h , S p i e l , V e r e i n as g where g . V_ID = Gast and h . V_ID = Heim and h . L i g a = 1 and Tore_Heim = Tore_Gast Order By S p i e l t a g (d) Es soll für jeden Spieltag die Anzahl der Unentschieden in der ersten Liga angegeben werden. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 105 / 138 In diesem Abschnitt Verknüpfung von Tabellen 5.1 Basisabfragen 5.2 Schema für Join-Abfragen 5 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen Gewünscht ist eine Liste der Inseln samt Fläche, die größer als 50000 Quadratkilometer sind und die dazugehörigen Landesteile und Länder. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 107 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 1. Schritt: Welche Tabellen beinhalten die gewünschten Informationen? LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT, LT_ID) LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE, HAUPTSTADT) INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE) GEO_INSEL(LT_ID, L_ID, I_NAME) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 108 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 2. Schritt: Welche Spalten beinhalten die gewünschten Informationen? LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT, LT_ID) LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE, HAUPTSTADT) INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE) GEO_INSEL(LT_ID, L_ID, I_NAME) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 109 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 3. Schritt: Eine der Tabellen komplett ausgeben lassen SELECT * FROM LAND M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 110 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 4. Schritt: Durch welche Spalte ist die Tabelle mit einer anderen verbunden? LAND (L_NAME, L_ID , EINWOHNER, FLAECHE, HAUPTSTADT, LT_ID) LANDTEIL (LT_NAME, LT_ID, L_ID , EINWOHNER, LAGE, HAUPTSTADT) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 111 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 5. Schritt: Tabellen verjoinen, d.h. zweite Tabelle in die from-Klausel Verknüpfungsspalten in die where-Klausel SELECT * FROM LAND as l , LANDTEIL as l t WHERE l . L_ID = l t . L_ID M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 112 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 6. Schritt: Spalten einschränken SELECT L_Name , LT_NAME FROM LAND as l , LANDTEIL as l t WHERE l . L_ID = l t . L_ID M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 113 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 7. Schritt: Nach dem gleichen Schema die nächste Tabelle dazunehmen nächste Tabelle in die from-Klausel Verknüpfungsspalten in die where-Klausel Spalten, die die Richtigkeit der Ausgabe anzeigen können in die select-Klausel SELECT L_Name , LT_NAME, g . LT_ID , g . L_ID , I_NAME FROM LAND as l , LANDTEIL as l t , GEO_INSEL as g WHERE l . L_ID = l t . L_ID and l . L_ID = g . L_ID and l t . LT_ID = g . LT_ID M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 114 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 8. Schritt: Letzte Tabelle dazunehmen LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT, LT_ID) LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE, HAUPTSTADT) INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE) GEO_INSEL(LT_ID, L_ID, I_NAME) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 115 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 8. Schritt: Letzte Tabelle dazunehmen SELECT L_Name , LT_NAME, g . LT_ID , g . L_ID , g . I_NAME , i . FLAECHE FROM LAND as l , LANDTEIL as l t , GEO_INSEL as g , INSEL as i WHERE l . L_ID = l t . L_ID and l . L_ID = g . L_ID and l t . LT_ID = g . LT_ID and g . I_NAME = i . I_NAME M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 116 / 138 5 Schema für Join-Abfragen åSchema für Joins (TERRA) Verknüpfung von Tabellen 9. Schritt: Nur die benötigten Spalten und Zeilen ausgeben SELECT L_Name , LT_NAME, g . I_NAME , i . FLAECHE FROM LAND as l , LANDTEIL as l t , GEO_INSEL as g , INSEL as i WHERE l . L_ID = l t . L_ID and l . L_ID = g . L_ID and l t . LT_ID = g . LT_ID and g . I_NAME = i . I_NAME and i . FLACHE > 50000 M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 117 / 138 5 Schema für Join-Abfragen åÜ 5.3: TERRA-Datenbank Verknüpfung von Tabellen Gib die SQL-Querries an. Achte auf eine möglichst kurze Anfrage ohne unnötige joins. (a) Gesucht ist eine Aufstellung aller Länder mit ihren Nachbarländern. (b) Gesucht ist eine Aufstellung aller Länder und der Anzahl ihrer Nachbarländer sortiert nach dieser Anzahl. (c) Gesucht ist eine Aufstellung aller Länder und ihrer Nachbarländer, die größer sind als das Land selbst. (d) Gesucht ist eine Aufstellung aller Länder und der Anzahl der Nachbarländer, die größer sind als das Land selbst. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 118 / 138 5 Schema für Join-Abfragen Ü 5.4: Eigene Abfragen formulieren Verknüpfung von Tabellen Gib die SQL-Querries und Syntaxdiagramme von selbst gewählten Abfragen an, die sinnvoll sind und (a) Einen Join über zwei Tabellen enthält. (b) Einen Join über drei Tabellen enthält. (c) Einen Join über drei Tabellen enthält und alle Pfade eines SQL-Querrys enthält. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 119 / 138 Syntaxdiagramme 6 SQL-Querry M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 120 / 138 Syntaxdiagramme 6 select- und From-Term M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 121 / 138 6 Bedingungen in where- und having-Clause Syntaxdiagramme M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 122 / 138 6 order by-, limit- und group bySyntaxdiagramme M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 123 / 138 In diesem Abschnitt Beziehungen zwischen Klassen 7.1 Wiederholung Abschnitt 1 7.2 Beziehungen und Kardinalitäten 7 7 1 Beispiel: Bibliotheksverwaltung Klassenbeziehungen M. Jakob (Gymnasium Pegnitz) Datenbanken Wiederholung Abschnitt 1 20. Februar 2016 125 / 138 Wiederholung Abschnitt 1 7 1 Datentypen in SQL (unserer DB-Sprache) Klassenbeziehungen Datentyp VARCHAR[n] CHAR[n] INT FLOAT DOUBLE DECIMAL[n;d] DATE TIME DATETIME M. Jakob (Gymnasium Pegnitz) Beschreibung Variable Zeichenfolge von max. n Zeichen Zeichenfolge von genau n Zeichen (Rest Leerzeichen) Ganze Zahlen (etwa von -2Mrd. Bis +2Mrd.) Kommazahlen mit 7 gültigen Ziffern Kommazahlen mit 15 gültigen Ziffern Kommazahl mit d Dezimalstellen und insgesamt n Ziffern Datumsangaben Zeitangaben Kombination aus Datums- und Zeitangabe Datenbanken 20. Februar 2016 126 / 138 Wiederholung Abschnitt 1 7 1 Definition Tabellenschema und Schlüssel Klassenbeziehungen Definition Tabellenschema und Schlüssel Jeder Datensatz muss eindeutig identifizierbar sein. Spalten(-kombination) die jeden Datensatz eindeutig festlegt, nennt man Schlüssel. Häufig verwendet man künstliche Schlüssel (z.B. EAN/Strichcode von Waren, Personalausweisnummer) Im Tabellenschema werden der Tabellenname, und die Attribute samt Datentypen aufgeführt und der Schlüssel unterstrichen. Tabellenschema für obige Tabelle: city(ID: INT; Name: VARCHAR[40], ContryCode: CHAR[3], Destrict: VARCHAR[40], Population: INT) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 127 / 138 Wiederholung Abschnitt 1 7 1 Gleicher Sachverhalt verschiedene Darstellungen Klassenbeziehungen Tabellenschema und Klassenkarte sind nur zwei verschiedene Darstellungen des gleichen Sachverhalts. BUCH(Titel: VARCHAR[99], Fachbereich: VARCHAR[40], Autor: VARCHAR[40], Verlag: VARCHAR[40], ISBN: VARCHAR[20], Preis: DECIMAL[4;2], Seitenzahl: INT, Erscheinungsjahr: DATE, Exemplaranzahl: INT) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 128 / 138 Wiederholung Abschnitt 1 7 1 ER-Diagramme — Noch ’ne andere Darstellung Klassenbeziehungen Zur Verbesserung der Übersicht werden die Datentypen oft weggelassen. Gruppiert man die Attribute als Blasen um die Klassenkarte erhält man sogenannte Entity-Relationship-Darstellung. BUCH(Titel, Fachbereich, Autor, Verlag, ISBN, Preis, Seitenzahl, Erscheinungsjahr, Exemplaranzahl) M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 129 / 138 In diesem Abschnitt Beziehungen zwischen Klassen 7.1 Wiederholung Abschnitt 1 7.2 Beziehungen und Kardinalitäten 7 7 Beziehungen (Relationen) Klassenbeziehungen 2 Beziehungen und Kardinalitäten Klassen können in bestimmten Beziehungen zueinander stehen. Diese Relation wird auf die Verbindungslinie der Klassen geschrieben. Durch die Klassen ist die Beziehung noch nicht eindeutig festgelegt. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 131 / 138 Klassenbeziehungen 7 2 Beziehungen und Kardinalitäten Kardinalitäten Die Kardinalität gibt an, wie viele Objekte der Nachbarklasse zu einem Objekt der Heimatklasse gehören. Wenn ich einen Schüler (Heimatklasse) herausgreife so kann er nur in einer Schulklasse (Nachbarklasse) sein. Also kommt ein 1 an die Schulklasse. Wenn ich eine Schulklasse (Heimatklasse) herausgreife so können dazu mehrere Schüler gehören. Also kommt ein Buchstabe (z.B. n) an die Klasse Schüler. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 132 / 138 7 Klassenbeziehungen 2 Beziehungen und Kardinalitäten Arten der Kardinalität Art 1 n 0,1 1 .. n M. Jakob (Gymnasium Pegnitz) Beschreibung Genau ein Objekt Keines, eines oder mehrere Objekte Kein oder ein Objekt Ein oder mehrere Objekte Datenbanken 20. Februar 2016 133 / 138 7 2 Klassendiagramme umfassen die Klassenbeziehungen Beziehungen und Kardinalitäten Klassenkarten mit Attributlisten und Datentypen, Schlüssel und Relationen mit Kardinalitäten. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 134 / 138 Beziehungen und Kardinalitäten 7 2 ER-Diagramm statt Klassendiagramm Klassenbeziehungen M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 135 / 138 7 Ü 7.1: Schulverwaltung Klassenbeziehungen 2 Beziehungen und Kardinalitäten In der Schulverwaltung gibt es folgende fünf Klassen Schueler, Schulklasse, Lehrer, Raum und Unterrichtsfach. elf Relationen Schüler hat (Lehrer, Unterrichtsfach), Lehrer unterrichtet (Unterrichtsfach, Schulklasse), ist_Klassenkamerad_von, ist_Fachbetreuer_von, ist_Klassensprecher_von, ist_Klassenleiter_von, hat_Klassenzimmer, hat_Fachraum, hat_Lieblingsfach. Erstelle ein übersichtliches(!) und kreuzungsfreies ER-Diagramm (Vorlage Schulverwaltung.graphml). Ergänze ggf. weitere Relationen. M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 136 / 138 Klassenbeziehungen 7 2 Beziehungen und Kardinalitäten Ü 7.2: Relationen (a) Welche Darstellungsarten werden bei den unten aufgeführten Datenbanken verwendet? Gib auch an, wenn in der Darstellung etwas fehlt und formatiere die Tabelle sinnvoll (Vorlage: Relationen_a.ods). (b) Verwandle das Klassendiagramm (guckst du gelber Link bei Bundesliga-Datenbank) in ein ER-Diagramm. Verwende dazu yEd. (Vorlage: keine) (c) Erstelle ein Klassendiagramm aus der TERRA-Datenbank, das die Tabellen LAND, LANDTEIL, KONTINENT und INSEL umfasst. Es gibt eine Relation von LAND zu LAND. Trage auch diese in das Klassendiagramm ein. (Vorlage: keine) åBundesliga / Wetter-Datenbank åTERRA-Datenbank åcia-Datenbank M. Jakob (Gymnasium Pegnitz) Datenbanken 20. Februar 2016 137 / 138