Informatik/Jahrgangsstufe Q/001 Klausuren
Werbung
Aufgabe 3: Datenbanken – Entity-Relationship-Modell a) Geben sie die Komplexität der folgenden Situationen an. Erstellen Sie jeweils ein ERDiagramm. Formulieren Sie sinnvolle Geschäftsregeln. 1. Eine Ladenkette möchte Informationen über ihre Filialen und über deren Zulieferer speichern. Jeder Zulieferer beliefert mehrere Filialen, und jede Filiale kauft von mehreren Zulieferern. 2. Ein Installateur möchte über seine Handwerker und über die Häuser, in denen sie gerade arbeiten, Informationen speichern. b) Die Computerzubehörfirma Microtec GmbH möchte ihre Verwaltung auf EDV umstellen. Sie verkauft ein Sortiment von Artikeln, die sie von verschiedenen Herstellern bezieht. Außerdem hat sie einen bestimmten Kundenkreis, der bei ihr Bestellungen aufgibt. Eine Bestellung kann natürlich mehrere Artikel umfassen. Derselbe Artikel kann oft von mehreren Herstellern bezogen werden, und ein Hersteller liefert natürlich meist mehr als einen Artikel. Erstellen Sie im Entity-Relationship-Modell ein sinnvolles Datenmodell für die Firma, das Datenredundanz vermeidet. Wählen Sie geeignete Entities mit notwendigen Attributen und geben Sie die zwischen den Entities bestehenden Beziehungen mit ihrem Komplexitätsgrad an. c) Eine Fluggesellschaft will ein Informationssystem aufbauen, um diese Informationen zu speichern: 1. die Piloten, identifiziert durch die Personal-Nr mit Name und Alter, 2. die verwendeten Flugzeugtypen mit eindeutiger Typ-Bezeichnung und Sitzanzahl, 3. die Information, welcher Pilot welchen Flugzeugtyp fliegt, 4. die Flugstunden, die ein Pilot insgesamt geflogen ist, 5. die Flugstunden die er auf einem bestimmten Flugzeugtyp geflogen ist und 6. die Flugstunden, die mit einem Flugzeugtyp insgesamt geflogen wurden. I) Zeichnen Sie das entsprechende ER-Diagramm, unterstreichen Sie dabei die Schlüsselattribute. II) Überführen Sie das Modell in das relationale Modell. Lösung: Aufgabe 3: a.I) Zulieferer n beliefert cn Filialen Ein Zulieferer kann in der Datenbank vorhanden sein, obwohl er keine Filiale beliefert, Eine Filiale dagegen hat mindestens einen Zulieferer. a.II) Handwerker n arbeitet an m Häuser Ein Handwerker arbeitet mindestens an einem Haus. In einem Haus arbeiten mindestens ein Handwerker. b) Name HerstNr Anschrift Hersteller Bezeichnung ArtNr n liefert m Preis Artikel n gehört zu KundNr Kunde 1 gibt auf n Anzahl cm Bestellung RechnNr Name Anschrift Datum c.I) Name PersNr Alter Pilot Stunden n c.II) Pilot(PersNr, Name, Alter, Stunden) fliegt(PersNr, TypBez, Stunden) Flugzeugtyp(TypBez, Stunden, Sitze) Stunden fliegt TypBez m FlugzTyp Sitze Stunden Aufgabe 4: Datenbanken – Relationales Modell a) Gegeben seien drei Relationen mit den folgenden Daten: Besucht Serviert Mag Gast Bistro Bistro Getränk Gast Hans Uno Uno Wasser Hans Ede Uno Uno Kaffee Ede Ede Dos Dos Kaffee Ede Ede Chico Karl Karl Dos Karl Chico Heini Uno Getränk Wasser Wasser Kaffee Kaffee I) Bilden Sie Serviert × Mag. II) Bilden Sie Serviert Mag. Welche Informationen beinhaltet diese Relation? III) Geben Sie alle Bistros aus, die Getränke servieren, die Karl mag. Geben Sie auch die entsprechende Operation der Relationenalgebra an. IV) Geben Sie alle Gäste aus, die ein Bistro besuchen, das auch das Getränk serviert, das sie mögen. Formulieren Sie die Anfrage mit Operationen der Relationenalgebra. b) Gegeben seien folgende Relationen: Lieferanten (LieferantenNr, Name, Status, Stadt) Teile (TeileNr, Name, Farbe, Gewicht, Stadt) Projekte (ProjektNr, Name, Stadt) Lieferungen (LieferantenNr, TeileNr, ProjektNr, Anzahl) Hierbei bedeutet Stadt einmal die Stadt, in der ein Lieferant sitzt, die Stadt, in der das entsprechende Teil hergestellt wird, bzw. die Stadt, in der ein Projekt stattfindet. Lösen Sie die folgenden Aufgaben durch Operationen aus der Relationenalgebra: I) Finden Sie alle Lieferungen mit Anzahlen zwischen 300 und 750 und geben Sie alle dazu in der Relation Lieferungen verzeichneten Informationen aus. II) Geben Sie alle Städte aus, in denen Lieferanten sitzen. III) Finden Sie alle schwarzen Teile. Geben Sie ihre Nummer und ihren Namen aus. IV) Finden Sie alle Lieferanten, die in einer Einzellieferung mehr als 150 Teile geliefert haben. Geben Sie ihren Namen aus. V) Finden Sie alle Städte, in denen sowohl Projekte als auch Lieferanten beheimatet sind. c) Bilden Sie das folgende ER-Diagramm in das relationale Modell ab. Optimieren Sie die Relationenanzahl Lehrstuhlbezeichnun g PName Telefon Anschrift 1 Professor akad. Grad AName hat cn 1 Telefon Assistent 1 betreut hält ÜbungsNr Zeit Vorl-Nr cn 1 Vorlesung hat cn cn Übungsgruppe 1 Zeit Ort MatrikelNr Stundenzahl korrigiert IS A 1 Student c Ort Stundenzahl 1 Hiwi Vertrag Name Anschrift Fach d) Sie importieren Daten aus einer Excel-Tabelle und erhalten die folgende Struktur. Normalisieren Sie die Daten von der 1. bis zur 3. Normalform. Dokumentieren Sie dabei ausführlich Ihre Schritte mit der entsprechenden Begründung (Definition der Normalformen und ihre Folgerungen). PersNr Name Abteilung 513 134 599 Einkauf Verkauf Personal Lorenz, Christian Baumann, Peter Petersen, Anna AbteilungsNr 13 21 7 Lösung: Aufgabe 4: a.I) Bistro Uno Uno Uno Uno Uno Uno Getränk Wasser Wasser Wasser Wasser Kaffee Kaffee Gast Hans Ede Ede Karl Hans Ede Getränk Wasser Wasser Kaffee Kaffee Wasser Wasser Abteilungsort Stunden 51519 Odentahl 51467 Berg. Gl. 51515 Kürten 198, 201 120, 189, 43 120 Uno Uno Dos Dos Dos Dos Kaffee Kaffee Kaffee Kaffee Kaffee Kaffee Ede Karl Hans Ede Ede Karl Kaffee Kaffee Wasser Wasser Kaffee Kaffee a.II) Bistro Getränk Gast Uno Wasser Hans Uno Wasser Ede Uno Kaffee Ede Uno Kaffee Karl Dos Kaffee Ede Dos Kaffee Karl Die Tabelle enthält die Information darüber, in welchem Bistro welche Personen ihr Lieblingsgetränk erhalten könnte. a.III) πBistro (σGast = Karl (Serviert Bistro Uno Dos a.IV) πGast (Besucht Mag) (πBistro, Gast (Serviert Mag) Gast Hans Ede Karl b.I) b.II) σAnzahl ≥ 300 und Anzahl ≤ 750 (Lieferungen) πStadt (Lieferanten) b.IV) πTeileNr, Name (σFarbe = schwarz (Teile)) πName ((πLieferantenNr (σAnzahl > 150 (Lieferungen))) b.V) πStadt (Lieferanten b.III) Lieferanten) Projekte) c) Professor(PName, Lehrstuhlbez., Telefon, Anschrift) Assistent(AName, akad. Grad, Telefon, PName) Vorlesung(Vorl.Nr, Zeit, Ort, Std.zahl, PName) Übungsgruppe(Übungs-Nr, Zeit, Ort, Std.zahl, AName, Vorl.Nr, Vertrag, MatrikelNr) Student(MatrikelNr, Name, Adresse, Fach) d) 1. Normalform: eindt. Schlüssel und atomare Werte Hier: Name -> Vorname, Nachname Stunden auslagern in extra Tabelle, da Mehrfachattribut Stunden(PersNr,AbteilungsNr, StundenID, Stunden) Personal(PersNr, AbteilungsNr, Vorname, Nachname, Abteilung, AbteilungsPLZ, Abteilungsort) 2. Normalform: Attribute nur Abhängig vom gesamten Primärschlüssel. Hier: Abteilung (mit PLZ und Ort) nur von AbteilungsNr abhängig. => auslagern. Diese Lösung lässt auch zu, dass Personal in mehreren Abteilungen gleichzeitig eingesetzt werden kann. Stunden(PersNr,AbteilungsNr, StundenID, Stunden) Personal(PersNr, Vorname, Nachname) Personal_istIn_Abteilung(PersNr, AbteilungsNr) Abteilungen(AbteilungsNr, Abteilung, AbteilungsPLZ, Abteilungsort) 3. Normalform: keine transitiven Abhängigkeiten. Hier: Ort hängt von PLZ ab. => auslagern in PLZ-Ort-Tabelle Stunden(PersNr,AbteilungsNr, StundenID, Stunden) Personal(PersNr, Vorname, Nachname) Personal_istIn_Abteilungs(PersNr, AbteilungsNr) Abteilungen(AbteilungsNr, Abteilung, AbteilungsPLZ) Orte(PLZ, Ort) Aufgabe 2: Datenbanken a) Frau Emma möchte ihren Tante-Emma-Laden ab jetzt mit einer Datenbank verwalten. Für jeden Artikel will Frau Emma die Bezeichnung, den Bestand, den Einkaufspreis und den Verkaufspreis speichern. Die Artikel gehören sogenannten Warengruppen (Lebensmittel, Zeitschriften, etc.) an. Diese haben eine eindeutige Bezeichnung und einen Mehrwertsteuersatz. Neben den Artikeln und Warengruppen will Frau Emma auch ihre Verkaufsfläche auf EDV umstellen. In ihrem Laden gibt es verschiedene Standorte, welche durch die Kombination aus Regal-Nummer und Platz-Nummer eindeutig festgelegt sind. Es Gibt Standorte, welche noch leer sind, andernfalls ist im Standort vermerkt, welcher Artikel sich dort befindet. Da bestimmte Artikel nur in Regalen eines bestimmten Materials gelagert werden können, soll für jeden Standort zusätzlich das Material des Regals gespeichert werden. Die Relation Standorte könnte damit wie folgt aussehen: Standorte RegalNr PlatzNr ArtikelNr Material 11 15 17 Holz 11 16 NULL Holz 11 17 39 Holz 12 5 NULL Eisen 12 15 25 Eisen I) Erstellen Sie ein Entity-Relationship-Diagramm für dieses Problem (Bitte nur die oben angegebenen Attribute einfügen). Begründen Sie alle angegebenen Kardinalitäten. II) Überführen Sie das Diagramm in das relationale Modell. Optimieren Sie falls möglich die Anzahl der Relationen. III) Begründen Sie entweder, dass Ihr relationales Modell bereits der 1. Normalform entspricht. Oder überführen Sie Ihre Datenbank in die 1. Normalform. IV) Begründen Sie, dass Ihre Datenbank noch nicht den Anforderungen der 2. Normalform entspricht. Überführen Sie Ihre Datenbank anschließend in die 2. Normalform. V) Formulieren Sie bezugnehmend auf Ihre Datenbank SQL-Abfragen für die folgenden Informationen: 1. Welche Artikel-Bezeichnung der Warengruppe "Schreibwaren" gibt es im TanteEmma-Laden? 2. In welchen Regalen ist noch Platz? Die Regalnummern sollen aufsteigend sortiert sein. 3. Zu allen Standorten soll die Bezeichnung des Artikel ausgegeben werden, der dort liegt. Leere Standorte sollen ebenfalls mit ausgegeben werden. 4. Wie viele Artikel haben einen Mehrwertsteuersatz von 19%?. Nur die Anzahl ist gefragt. b) Im Datenbank-System einer Schule sind folgende Tabellen implementiert: Schueler Nummer Name Vorname Geburtsdatum Lerngruppe 123 Müller Martin 11.2.1992 9A 178 Schmitz Antonia 19.6.1989 12 Lerngruppen Klasse 9A 12 10C Klassenlehrer MEY KSL TAN Faecher Abkuerzung Langname M Mathe D Deutsch Kurse Bezeichnung M-LK-12 D-9-A F-GK-13 Lehrer Kuerzel MEY OP TAN Schueler SchuelerNr Kursbezeichnung D-9-A besucht 123 E-9-A Kurs 123 123 M-9-A 178 M-LK-12 456 D-9-A Stufe 12 9 13 Fach M D F Lehrer MEY OP TAN Langname Meyer Opermann Tarnert-Nauber Lehrer Lehrer Fach D unterrichtet OP E Fach OP MEY M MEY E I) Geben Sie zu allen Tabellen den Primärschlüssel an. II) Begründen Sie kurz, dass die Datenbank der 3. Normalform entspricht. III) Zeichnen Sie ein Entity-Relationship-Diagramm ohne Attribute. Achten Sie darauf, dass Sie alle Beziehungen zeichnen, die in den Relationen versteckt sind. Geben Sie auch die Komplexitäten an. IV) Welche Information erhält man durch folgende SQL-Abfragen? 1. select max(Schueler.Geburtsdatum) from Schueler 2. select Lehrer.Langname, Lehrer_unterrichtet_Fach.Fach from Lehrer left join Lehrer_unterrichtet_Fach on Lehrer.Kuerzel = Lehrer_unterrichtet_Fach.Lehrer order by Lehrer.Kuerzel 3. select Kurse.Fach from (Schueler inner join Schueler_besucht_Kurs on Schueler.Nummer = Schueler_besucht_Kurs.SchuelerNr) inner join Kurse on Schueler_besucht_Kurs.Kursbezeichnung= Kurse.Bezeichnung where Schueler.Lerngruppe = "9A" V) Geben Sie SQL-Abfragen an, mit denen folgende Informationen gesucht werden: 1. Alle Fächer-Langnamen, die von dem Lehrer MEY unterrichtet werden. 2. Alle Geburtsdaten der Schüler der Stufe 9, absteigend sortiert. 3. Alle Schüler, deren Klassenlehrer Mathematik unterrichten kann. Lösung: Aufgabe 2: a) I) Besatand Bezeichnung EK VK Artikel Bezeichnung n gehört zu c 1 liegt in RegalNr II) 1 Mehrwertsteuer Warengruppe Standorte PlatzNr Material Die Relationen sind: Artikel(Bezeichnung, Bestand, EK, VK, Warengruppe, ArtikelNr) Warengruppe(Bezeichnung, Mehrwertsteuersatz) Standorte(RegalNr, PlatzNr, Material, ArtikelNr) III) Die Relationen entsprechen noch nicht der 1. Normalform, da nicht alle Relationen eine Primärschlüssel haben. Artikel(ArtikelNr, Bezeichnung, Bestand, EK, VK, Warengruppe) Warengruppe(Bezeichnung, Mehrwertsteuersatz) Standorte(RegalNr, PlatzNr, Material, ArtikelNr) IV) Die Relation Standorte entspricht nicht der 2. Normalform, da das Material nur von RegalNr abhängig ist, d. h. nur von Teilen des Primärschlüssels Artikel(ArtikelNr, Bezeichnung, Bestand, EK, VK, Warengruppe) Warengruppe(Bezeichnung, Mehrwertsteuersatz) Regale(RegalNr, Material) Standorte(RegalNr, PlatzNr, ArtikelNr) V) b) I) 1. select Artikel.Bezeichnung from Artikel where Artikel.Warengruppe = "Schreibwaren" 2. select Standorte.RegalNr from Standorte where ArtikelNr is NULL order by Artikel.RegalNr ASC 3. select Standorte.RegalNr, Standorte.PlatzNr, Artikel.Bezeichnung from Standorte left join Artikel ON Standorte.ArtikelNr = Artikel.ArtikelNr 4. select count(Artikel.Bezeichnung) from Artikel inner join Warengruppe on Artikel.Warengruppe = Warengruppe.Bezeichnung where Warengruppe.Mehrwertsteuersatz = 0.19 Schüler(Nummer) Lerngruppen(Klasse) Kurse(Bezeichnung) Faecher(Abkuerzung) Lehrer(Kuerzel) SchuelerBesuchtKurs(SchuelerNr, Kursbezeichnung) LehrerUnterrichtetFach(Lehrer, Fach) II) Alle Attribute sind atomar und jede Relation hat einen Primärschlüssel => 1. NF Alle Attribute sind vom vollständigen Primärschlüssel abhängig => 2. NF Kein Attribut ist transitiv abhängig vom Primäschlüssel. => 3. NF III) Quelle: http://www.r-krell.de/k1if13m-07a.pdf Ein Schüler gehört zu 1 Klasse, eine Klasse hat n Schüler Ein Schüler besucht n Kurse, eine Kurs hat n Schüler Ein Lehrer unterrichtet n Kurse, ein Kurs wird von 1 Lehrer unterrichtet Ein Lehrer darf n Fächer unterrichten, Ein Fach wird von n Lehrern unterrichtet. Ein Lehrer ist Klassenlehrer von 1 Klasse, Eine Klasse hat 1 Klassenlehrer Ein Kurs gehört zu 1 Fach, ein Fach hat n Kurse. IV) 1. Das größte Geburtsdatum aller Schüler. 2. Alle Lehrernamen mit den Fächern die sie unterrichten, sortiert nach Lehrer-Kürzel 3. Alle Fächer, die von der Klasse 9a belegt werden. V) 1. SELECT Faecher.Langname FROM Faecher INNER JOIN Lehrer_unterrichtet_Fach ON Faecher.Abkuerzung = Lehrer_unterrichtet_Fach.Fach WHERE Lehrer_unterrichtet_Fach.Lehrer="MEY" 2. SELECT Schueler.Geburtsdatum FROM Schueler INNER JOIN (Schueler_besucht_Kurs INNER JOIN Kurse ON Schueler_besucht_Kurs.Kursbezeichnung = Kurse.Bezeichnung) ON Schueler.Nummer = Schueler_besucht_Kurs.SchuelerNr WHERE Kurse.Stufe="9" ORDER BY Schueler.Geburtsdatum DESC 3. SELECT Schueler.Name FROM (Schueler INNER JOIN Lerngruppen ON Schueler.Lerngruppe = Lerngruppen.Klasse) INNER JOIN Lehrer_unterrichtet_Fach ON Lerngruppen.Klassenlehrer = Lehrer_unterrichtet_Fach.Lehrer WHERE Lehrer_unterrichtet_Fach.Fach = "M" Aufgabe 1: Datenbanken a) Gegeben ist folgender Ausschnitt einer Fußball-Datenbank: Fußballspieler können bei höchstens einem Verein gemeldet sein. Sie werden über ihre Passnummer identifiziert und besitzen einen Namen. Die Vereine sind durch ihren Namen und die Vereinsnummer gekennzeichnet. Zusätzlich wird registriert, in welchen Ligen die Vereine vertreten sind. Die Namen der Ligen sind eindeutig in Kombination mit der Altersklasse (es gibt mehrere Altersklassen). Jeder Verein ist mindestens in einer dieser Ligen vertreten. Zudem wird das Datum erfasst, zu dem der Verein in die jeweilige Liga aufgestiegen ist. Modellieren Sie diesen Ausschnitt der Fußball-Datenbank im Entity-Relationship-Modell. Verwenden Sie die grafische Notation des ER-Diagramm für Ihre Darstellung. Markieren Sie die Primärschlüssel und geben Sie die Kardinalitäten der Beziehungen an. b) Gegeben ist folgendes E-R-Modell einer Hotelketten-Verwaltung: Sterne Hotel Adresse Stadt Kapazität Land Hotel-ID 1 Pers-ID arbeitet in n Name Zimmer-ID 1 1 1 hat IS A IS A c c n Kategorie Gehalt Angestellter Zimmer n Möbel Preis verantwortlich m Reinigungskraft Arbeitsverhältnis Manager email Telefon Überführen Sie dieses E-R-Diagramm in das relationale Modell. Achten Sie dabei auf eine optimierte Relationenanzahl. c) Gegeben seien drei Relationen mit den folgenden Daten: Unterrichtet Fach Lehrer M ULO E RE E PL D PL M NOP M PL Lehrbefähigung Lehrer Stufe ULO SekI RE SekII RE SekI PL SekII Verteilung Fach Stufe M SekI M SekII D SekI E SekII D RE Bilden Sie die Selektion σLehrer = RE(Lehrbefähigung). Lehrbefähigung. Geben Sie in eigenen Worten Bilden Sie den Join Unterrichtet an, welche Informationen diese Relation beinhaltet. III) Geben Sie alle Fächer aus, die in Stufen unterrichtet werden, die der Lehrer PL unterrichten darf. Geben Sie auch die entsprechende Operation der Relationenalgebra an. IV) Geben Sie alle Lehrer aus, die Fächer in der SekI unterrichten dürfen, die auch (evtl. von anderen Lehrern) in der SekII unterrichtet werden. Formulieren Sie die Anfrage mit Operationen der Relationenalgebra. I) II) d) Im folgenden ist das Relationenmodell einer Schlüsselverwaltung angegeben: Raum( Raum-ID, Raumbezeichnung ) Tür( Tür-ID, RaumVor, RaumHinter, Schloss-ID ) Schloss( Schloss-ID, Seriennummer ) schließt( Schloss-ID, Schlüssel-ID ) Schlüssel( Schlüssel-ID, Nummer ) Person( Person-ID, Vorname, Name ) Ausleihe( Ausleihe-ID, Person-ID, Schlüssel-ID, AusgabeDatum, RückgabeDatum ) I) Entwickeln Sie aus dem Relationenmodell ein E-R-Diagramm mit Angabe von Attributen, Primärschlüsseln und Kardinalitäten. II) Geben Sie unter Verwendung der Relationenalgebra die Operationen für folgende Abfragen an: (1) Welche Schlüssel (Nummer) müssen Weihnachten zurückgegeben werden? (2) Welche Personen (Vorname, Name) haben irgendwann Schlüssel ausgeliehen und schon wieder zurückgebracht? (3) Welche Türen (Tür-ID) kann Gustav Gazelle aufschließen? Lösung: Aufgabe 1: a) Name Passnr. Vereinsnr Spieler n gemeldet in 1 Name Verein n spielt in Aufst.Dat. m Liga Name Klasse b) Hotel(Hotel-ID, Kapazität, Sterne, Stadt, Land) Angestellter(Pers-ID, Name, Gehalt, Hotel-ID) Zimmer(Zimmer-ID,Kategorie, Preis, Hotel-ID) Möbel(Möbel-ID, Zimmer-ID, Bezeichnung) Reinigungskraft(Pers-ID, Arbeitsverhältnis) verantwortlich(Zimmer-ID, Pers-ID) Manager(Pers-ID, email, Telefon) c) I) σLehrer = RE(Lehrbefähigung) Lehrer Stufe RE SekII RE SekI II) Unterrichtet Lehrbefähigung Fach Lehrer Stufe M ULO SekI E RE SekII E RE SekI E PL SekII D PL SekII M PL SekII D RE SekII D RE SekI Es werden alle Kombinationen Fächer-Stufe ausgegeben, für die ein Lehrer existiert. III) πFach(σLehrer = PL(Unterrichtet Fach E M Lehrbefähigung) IV) πLehrer(σStufe=SekII(Verteilung Lehrer ULO RE σStufe=SekI(Lehrbefähigung))))) (πFach, Lehrer(Unterrichtet d) Bezeichn. Tür-ID hinter 1 Raum Raum-ID n 1 vor Tür n 1 hat Nummer 1 Schlüssel Schl.-ID n passt in m Schloss n Ausgabe Sereinnr ausgeliehen SchlossID Rückgabe 1 Person Person-ID Vorname Name (1) πNummer(σRückgabeDatum="24.12."(Schlüssel (2) πVorname, Name(σRückgabeDatum<>""(Ausleihe (3) πTür-ID(Tür (schließt (Ausleihe Ausleihe)) Person)) σName="Gazelle" ∧ Vorname="Gustav"(Person)))) Aufgabe 1: Datenbanken Das "Schuhhaus König" möchte seinen Artikelbestand in einer Datenbank speichern. Die Datenbank soll Angaben zu den einzelnen Herstellern (Name und Anschrift) und Daten der einzelnen Schuhtypen (Hersteller, Fabrikat, Schuhart) enthalten. Unter Schuhart versteht das Schuhhaus die Unterscheidung in "Sandale", "Stiefel", "Pantoffel", etc., welche in einer eigenen Entität modelliert werden sollen. Zusätzlich möchte das Schuhhaus für jeden Schuhtyp die Anzahl der auf dem Lager befindlichen Größen und Farben speichern. Für ein Paar Schuhe wird demnach der Schuhtyp, die Größe, die Farbe und der Lagerort gespeichert. Die Lagerorte bestehen dabei stets aus einer Gangnummer und einer Regalnummer, welche − unabhängig davon, ob Schuhe im Lagerort gelagert werden − in einer eigenen Tabelle gespeichert werden sollen. a) Modellieren Sie ein geeignetes Entity-Relationship-Modell und stellen Sie dieses als Entity-Relationship-Diagramm dar. Führen Sie sinnvolle Primärschlüssel ein oder definieren Sie geeignete Attribute als Primärschlüssel. Geben Sie auch die Kardinalitäten Ihrer Beziehungen an. b) Überführen Sie dieses ERM in das relationale Modell. Optimieren Sie die Tabellenanzahl. Der Hersteller "Salamander" verwaltet seine Kunden und Artikel ebenfalls mithilfe einer Datenbank. Die Modellierung im relationalen Modell sieht dabei wie folgt aus: Kunde(Firmenname, Anschrift, Ansprechpartner, bestellterArtikel-ID, bestellteAnzahl) Artikel(ID, Fabrikatname, Geschlecht, Größe) Lagerort(HalleGangPlatz, Artikel-ID, Anzahl, Stapler) Das Attribut HalleGangPlatz könnte z. B. "Halle5, Gang 3, Platz 17b" lauten. Es gibt Lagerorte, in denen keine Artikel gelagert sind, es kann aber auch sein, dass an einem Lagerort unterschiedliche Artikel gelagert werden. Im Attribut Stapler soll lediglich festgehalten werden, ob ein Stapler für diesen Lagerort benötigt wird, oder nicht. c) Geben Sie die Datentypen an, welche für die Relation Lagerort bei der Anlage einer entsprechenden MySQL-Tabelle gewählt werden müssen. d) Erläutern Sie, weshalb sich die Datenbank nicht in der 1. Normalform befindet. Überführen Sie anschließend die Datenbank in die 1. Normalform. e) Begründen oder widerlegen Sie, dass sich die Datenbank aus c) in der 3. Normalform befindet. Überführen Sie diese gegebenenfalls in die 3. Normalform. f) Geben Sie eine SQL-Anfrage an, mit der man herausfinden kann, an welchem Lagerort die von der Firma "Schuhhaus König" bestellten Schuhe zu finden sind. g) Geben Sie eine SQL-Anfrage an, die alle Lagerorte und – falls vorhanden – dort gelagerte Artikel-IDs auflistet. Erläutern Sie, weshalb der Inner Join hier nicht zum Ziel führt. Lösung: Aufgabe 1: a) ... noch machen ... b) Hersteller(ID, Name, Straße, PLZ, Ort) Schuhtyp(ID, Hersteller-ID, Fabrikat, Schuhart-ID) Schuhart(ID, Art) Schuh(ID, Schuhtyp-ID, Größe, Farbe, Lagerort-ID) Lagerort(ID, Gangnummer, Regalnummer) c) HalleGangPlatz: VARCHAR(30) Artikel-ID: INTEGER Anzahl: INTEGER Stapler: BOOLEAN d) Die Attribute sind nicht atomar und es gibt keine Primärschlüssel. Kunde(ID, Firmenname, Straße, PLZ, Ort, Ansprechpartner, bestellterArtikel-ID, bestellteAnzahl) Artikel(ID, Fabrikatname, Geschlecht, Größe) Lagerort(ID, Halle, Gang, Platz, Artikel-ID, Anzahl, Stapler) e) Die Attribute sind nicht alle vom Primärschlüssel abhängig. Es gibt zudem transitive Abhänigkeiten (z. B. PLZ-Ort). Diese müssen in eigene Tabelle aufgelöst werden. Kunde(ID, Firmenname, Straße, PLZ, Ansprechpartner) Orte(PLZ, Ort) bestellt(Kunden-ID, Artikel-ID, Anzahl) Artikel(ID, Fabrikatname, Geschlecht, Größe) lagert(Artikel-ID, Lagerort-ID, Anzahl) Lagerort(ID, Halle, Gang, Platz, Stapler) f) SELECT Lagerort.Halle, Lagerort.Gang, Lagerort.Platz FROM Lagerort INNER JOIN lagert ON Lagerort.ID = lagert.Lagerort-ID INNER JOIN Artikel ON lagert.Artikel-ID = Artikel.ID INNER JOIN bestellt ON Artikel.ID = bestellt.Artikel-ID INNER JOIN Kunde ON bestellt.Kunden-ID = Kunde.ID WHERE Kunde.Firmenname = "Schuhhaus König" g) SELECT Lagerort.Halle, Lagerort.Gang, Lagerort.Platz, Artikel.ID FROM Lagerort LEFT OUTER JOIN lagert ON Lagerort.ID = lagert.Lagerort-ID RIGHT JOIN Artikel ON lagert.Artikel-ID = Artikel.ID Aufgabe 2: Datenbanken Sie importieren Daten aus einer Excel-Tabelle und erhalten die folgende Struktur. GeräteNr Bezeichnung Raum Gebäude Anschaffung XV12942 CD-Player, Kenwood 517 5 DY96352 Radio, Toshiba 119 1 ZU66351 Fernseher, Panasonic 914 9 12.01.2004, 119 € 17.10.2000, 15,99 € 04.03.2009, 1199 € Fachschaft im Raum GE, SP, E GE, EK, SW IF, M, PH a) Normalisieren Sie die Daten in der 3. Normalform im relationalen Modell. Dokumentieren Sie dabei ausführlich Ihre Schritte mit der entsprechenden Begründung. b) Gesucht sind die Anschaffungspreise der Geräte, welche von der Fachschaft Informatik verwendet werden. Geben Sie auf Grundlage des relationalen Modells aus Aufgabenteil a) den zugehörigen Ausdruck in der Relationenalgebra an. c) Formulieren Sie eine äquivalente SQL-Anfrage, welche die Anschaffungspreise absteigend sortiert ausgibt. Gegeben ist folgende Datenbank im relationalen Modell: Person(PersonenNr, Name, Gehalt, Beruf, AbeitlungNr, ManagerNr, Ort) Abteilung(AbteilungNr, AbteilungName, AbteilungOrt) Dabei ist Person.ManagerNr Fremdschlüssel auf Person.PersonenNr, d. h. ein Manager ist auch nur eine Entität des Entitätstyps Person. Zudem ist Person.AbteilungNr Fremdschlüssel auf Abteilung.AbteilungNr. d) Formulieren Sie für folgende Informationen Anfragen in SQL: I) Liste aller Angestellten aus Abteilung K15, die mehr als 5000 € verdienen. II) Anzahl der Abteilungen, die in Köln angesiedelt sind. III) Liste aller Abteilungen in Köln, die Angestellte beschäftigen, die auch in Köln wohnen. IV) Liste aller Programmierer (Name, Gehalt und Abteilungsname), die in Köln beschäftigt sind. V) Durchschnittsverdienst aller Mitarbeiter der Abteilung K15. e) Beschreiben Sie die Informationen, welche man durch die Ausdrücke E1 und E2 der Relationenalgebra erhält. I) E1 = πName, Ort(σAbteilungName="K15"(Person Abteilung)) der Join wird auf das Attribut AbteilungNr ausgeführt. II) A1 = πPersonenNr(σName = "Müller"(Person)) ; E2 = πName(A1 Person) der Join wird auf die Attribute A1.PersonenNr und Person.ManagerNr ausgeführt. Lösung: Aufgabe 2: a) 1. Normalform: Attribute atomar machen: Gerät(Geräte-Nr, Bezeichnung, Hersteller, Raum, Gebäude, Anschaffungsdatum, Anschaffungspreis, Fachschaft) 2. Normalform: Abhängigkeit vom gesamten Primärschlüssel Gerät(Geräte-Nr, Bezeichnung, Hersteller, Raum, Gebäude, Anschaffungsdatum, Anschaffungspreis) Fachschaft(Geräte-Nr, Fachschaft) 3. Normalform: keine transitiven Abhängigkeiten: Gerät(Geräte-Nr, Bezeichnung, Raum, Anschaffungsdatum, Anschaffungspreis) Fachschaft(Geräte-Nr, Fachschaft) Hersteller(Bezeichnung, Hersteller) Raum(Raum, Gebäude) b) πAnschaffungspreis(σFachschaft = IF(Fachschaft) Gerät) c) SELECT Gerät.Anschaffungspreis FROM Gerät INNER JOIN Fachschaft ON Gerät.Geräte-Nr = Fachschaft.GeräteNr WHERE Fachschaft.Fachschaft = "IF" ORDER BY Gerät.Anschaffungspreis DESC d) I) SELECT * FROM Person INNER JOIN Abteilung ON Person.AbteilungNr = Abteilung.AbteilungNr WHERE Abteilung.AbteilungName = "K15" AND Person.Gehalt > 5000 II) SELECT COUNT(*) FROM Abteilung WHERE Abteilung.AbteilungOrt = "Köln" III) SELECT * FROM Person INNER JOIN Abteilung ON Person.AbteilungNr = Abteilung.AbteilungNr WHERE Abteilung.AbteilungOrt = "Köln" AND Person.Ort = "Köln" IV) SELECT Person.Name, Person.Gehalt, Abteilung.AbteilungName FROM Person INNER JOIN Abteilung ON Person.AbteilungNr = Abteilung.AbteilungNr WHERE Person.Beruf = "Programmierer" AND Abteilung.AbteilungOrt = "Köln" V) SELECT AVG(Person.Gehalt) FROM Person INNER JOIN Abteilung ON Person.AbteilungNr = Abteilung.AbteilungNr WHERE Abteilung.AbteilungName = "K15" e) I) Alle Namen und Wohnorte der Beschäftigten von Abteilung K15 II) Alle Mitarbeiter-Namen, die Müller als Manager haben. Aufgabe 2: Datenbanken a) Ein Sportverein möchte seine Daten in einer Datenbank organisieren. Zunächst beschränkt sich der Verein auf die reine Mitgliederverwaltung, welche folgende Informationen verarbeiten soll: • Zu jedem Vereinsmitglied soll der Name, welcher sich in Vor- und Nachname aufschlüsselt und das Geburtsdatum gespeichert werden. • Für jedes Vereinsmitglied soll die Adresse (aufgeteilt in Straße, Hausnummer, Postleitzahl und Wohnort) gespeichert werden. • Zur eindeutigen Identifizierung eines Vereinsmitgliedes soll eine fortlaufende Nummer gespeichert werden. • In der Datenbank soll außerdem erfasst werden, ob es sich bei einem Vereinsmitglied um ein aktives oder passives Mitglied handelt. o Für alle aktiven Mitglieder wird festgehalten, welche Sportart das Mitglied im Verein betreibt und welchen Vereinsbeitrag das Mitglied dafür zu zahlen hat. o Für passive Mitglieder wird festgehalten, welche ehrenamtlichen Tätigkeiten das Mitglied angenommen hat. Dies können für ein passives Mitglied durchaus mehrere Tätigkeiten sein. Entwickeln Sie ein Entity-Relationship-Diagramm, das die beschriebene Struktur darstellt. Geben Sie im folgenden (also bei allen Diagrammerweiterungen) auch die Kardinalitäten der Beziehungen an und kennzeichnen Sie Primärschlüsselattribute. b) Der Sportverein hat gute Erfahrungen mit der Datenbank gemacht und möchte für die Mitgliederverwaltung durch eine Mannschaftsverwaltung ergänzen. • Jede Mannschaft ist durch einen eindeutigen Namen identifizierbar und spielt in einer Liga. • In jeder Mannschaft spielen nur aktive Mitglieder des Vereins, wobei der Trainer der Mannschaft stets ein passives Mitglied ist. • Ein aktives Mitglied kann in mehreren Mannschaften mitspielen, dagegen kann ein Trainer nur genau eine Mannschaft trainieren. • Alle Mannschaften sind jeweils eindeutig den verschiedenen Abteilungen des Vereins untergeordnet. In einer Abteilung können durchaus mehrere Mannschaften existieren. • Eine Abteilung umfasst alle Mannschaften einer bestimmten Sportart und besitzt einen eindeutigen Namen. • Eine Abteilung wird stets von einem aktiven Mitglied geleitet. Dieses Mitglied kann auch weitere Abteilungen leiten. Zeichnen Sie ein neues Entity-Relationship-Diagramm, das die gesamte beschriebene Struktur darstellt. Verzichten Sie bei den Entities aus Teilaufgabe a) auf die AttributAngabe. c) Der Verein möchte nun zusätzlich alle gespielten Begegnungen seiner Mannschaften in der Datenbank speichern. Am Ende eines Spieltages füllt dazu der Trainer der Mannschaft immer den folgenden Spielbericht aus, welcher anschließend in die Datenbank übernommen wird. SPIELBERICHT Heimspiel: Name der Mannschaft: Trainer: Name der gegnerischen Gastspiel: (bitte ankreuzen) ___________________________ ________________ Mannschaft: Trainer: Datum der Begegnung: Uhrzeit der Begegnung: Ergebnis: ___________________________ ________________ ____.____.20____ ____:____Uhr ____:____-(Heim : Gast) Zeichnen Sie ein neues Entity-Relationship-Diagramm, das die beschriebene Struktur darstellt. Zeichnen Sie lediglich die Entities aus Teilaufgabe a) und b), welche in einer Beziehung zur neuen Entity stehen. Verzichten Sie bei den Entities aus Teilaufgabe a) und b) auf die Attribut-Angabe. Kennzeichnen Sie den Primärschlüssel und begründen Sie Ihre Wahl. Lösung: Aufgabe 2: a - c) Datenbanken Geburtsdatum Nummer Straße Vorname Hausnummer Name PLZ Adresse Mitglied Ort Nachname is a Sportart is a 1 aktivesMitglied n Beitragssatz Tätigkeiten passiivesMitglied 1 1 spielt in 1 trainiert m Name Liga Mannschaft 1 leitet 1 n n 1 gehört zu spielt Abteilung Name Trainer bei HeimGast n spielt m HeimErgebnis n Begegnung Datum,Uhrzeit GegnerMann GastErgebnis Sportart GegnerTrainer Aufgabe 1: Datenbanken − ERM, Relationenalgebra Sie erhalten den Auftrag, für das International Olympic Committee eine Datenbank für die nächsten Olympischen Spiele zu erstellen, die folgenden Wirklichkeitsausschnitt enthalten soll. Die einzelnen Wettkämpfe der Olympischen Spiele sind durch den Namen der Sportart, den Termin und die Sportstätte gekennzeichnet. An jedem Wettkampf nehmen mehrere Sportler teil, die durch eine Startnummer identifiziert werden und außerdem einen Namen besitzen. Jeder Wettkampf wird von einem Schiedsrichter geleitet, dem für diese Spiele eine eindeutige Personalnummer zugeordnet wurde. Die Schiedsrichter werden bei einem Wettkampf von verschiedenen Helfern unterstützt, die ebenfalls eine eindeutige Personalnummer erhalten haben. Die Sportler und Schiedsrichter gehören jeweils einer Nation an. Zu jeder Nation werden der Name der Nation, der Name des Mannschaftsleiters sowie eine Telefonnummer für Rückfragen abgespeichert. a) Modellieren Sie diesen Ausschnitt der Datenbank im Entity-RelationshipModell. Verwenden Sie die grafische Notation des ER-Diagramms für Ihre Darstellung. Geben Sie auch die Komplexitäten der Beziehungen an und markieren Sie die Primärschlüssel. Führen Sie, wenn nötig und sinnvoll, einen im Wirklichkeitsausschnitt nicht erwähnten Schlüssel ein. b) Überführen Sie das ER-Modell in das relationale Modell. Optimieren Sie die Anzahl der verwendeten Relationen. Bei der Recherche nach einer geeigneten Modellierung Ihrer Datenbank im Internet finden Sie die folgende Datenbankstruktur, die Ihnen zwar bei den Aufgabenteilen a und b nicht weiterhilft, aber dennoch interessant ist: Sportart(SportartNr, SportartName, Spielregel, Mannschaftsgröße, Hallensportart) Mannschaft(MannschaftNr, MannschaftName, SportartNr, AnzahlSpieler) spieltIn(SpielerNr, MannschaftNr, Position) Spieler(SpielerNr, SpielerName, Straße, PLZ, Ort, Telefonnummer) c) Stellen Sie die Datenbank als ER-Diagramm dar. Geben Sie auch die Komplexitäten an. d) Geben Sie die Datentypen für die Attribute der Relation Sportart an. e) Beschreiben Sie, welche Fragestellungen mithilfe der folgenden Operationen der Relationenalgebra beantwortet werden können. i) σHallensportart = 1(Sportart) ii) πPosition(σName = "Basketball"(Sportart) Mannschaft spieltIn) iii) πMannschaftName(σPLZ = 51519(Spieler) σPosition = "Kapitän"(spieltIn) Mannschaft) f) Geben Sie die Operationen der Relationenalgebra an, mithilfe derer folgende Informationen ermittelt werden können: i) Alle Sportartnamen und deren Spielregeln ii) Alle Sportarten, deren Mannschaften aus 2 Spielern bestehen. iii) Alle Spielernamen, deren Position "Torwart" lautet und deren Mannschaft aus 7 Spielern besteht. iv) Alle Orte, in denen Spieler wohnen, welche eine Hallensportart ausüben. Lösung: Datenbanken – ERM, Relationenalgebra Aufgabe 1: a) Name Personalnr Startnr Sportler n Schiedsrichter n ist von 1 nimmt teil 1 Helfer n Personalnr ist von n Wettkampf Termin wird unterst. n leitet 1 Wettkampfnr 1 Sportart Sportstätte 1 Nation 1 Telefonnr Name Manns.leiter b) Sportler(Startnr, Name, Wettkampfnr, Nationname) Schiedsrichter(Personalnr, Nationname) Helfer(Personalnr, SchiedsrichterNr) Wettkampf(Wettkampfnr, Termin, Sportstätte, Sportart, SchiedsrichterNr) Nation(Name, Mannschaftsleiter, Telefonnr) c) SportartName Spielregel Mann.größe SportartNr Sportart 1 Hallensportart spielt Position n MannschaftsNr Mannschaft n spielt in SpielerNr m Spieler SpielerName Straße Mann.Name AnzahlSpieler Telefonnr PLZ d) SportartNr: INT SportartName: VARCHAR(20) Spielregel: VARCHAR(500) Ort Mannschaftsgröße: INTEGER Hallensportart: BOOL e) i) Alle Informationen über die Sportarten, die in der Halle gespielt werden. ii) Alle Positionen, welche in einer Basketball-Mannschaft gespielt werden. iii) Alle Mannschaftsnamen, deren Mannschaft einen Kapitän aus dem PLZ-Bezirk 51519 hat. f) i) ii) iii) iv) πSportartName, Spielregel(Sportart) σAnzahlSpieler=2(Mannschaft) Sportart) πSpielerName(Spieler σPosition = "Torwart"(spieltIn) σAnzahlSpieler=7(Mannschaft)) πOrt(Spieler spieltIn Mannschaft σHallensportart = 1(Sportart)) Datenbanken − Normalisierung, SQL Aufgabe 2: Ein Unternehmen bietet ausschließlich Städtereisen an. Für verschiedene europäische Städte existieren bestimmte Reiseangebote (jeweils genau eines), die Eigenschaften wie den Namen der Stadt, den (konstanten) Preis sowie die Reisedauer (Anzahl der Übernachtungen) besitzen. Für jedes Reiseangebot existieren verschiedene terminliche Ausprägungen (Reisen). Eine solche konkrete Reise wird jeweils von genau einem Busfahrer durchgeführt. Die Busfahrer des Unternehmens besitzen jeweils nur Kenntnisse für bestimmte Städte (Reiseangebote); dementsprechend besitzen sie nur die Fähigkeit zur Durchführung bestimmter Reisen. Die Kunden des Unternehmens sind ebenfalls in dem System abzubilden. Hierbei ist auch abzubilden, welche Kunden welche konkreten Reisen gebucht haben. Ein Vorschlag für die Modellierung im relationalen Modell lautet wie folgt: Dauer Reisen (Nr, Termin) Reiseangebote Stadtname Preis Paris 275,00 € 5 (121, 26.09.2009), (312, 15.10.2010), ... London 545,00 € 6 (541, 12.12.2009), ... ... Busfahrer Kunden Fahrername Lukas Lunert Lukas Lunert Paul Parker … Stadtnamen Paris London Paris Kunden- Name nummer 123456 Anton Acker 451271 ... Berta Burkert fährt Fahrername Lukas Lunert Lukas Lunert Paul Parker … ReiseNr 121 541 312 Adresse Reisen (Nr) Roggenacker 23, 51519 Odenthal Bananenweg 3, 51467 Bergisch Gladbach (121), (541), (388),... (121), (312), ... a) Begründen Sie, dass mit dieser relationalen Datenbank der oben beschriebene Wirklichkeitsausschnitt modelliert wurde. Gehen Sie dabei insbesondere auf die Modellierung der Komplexitäten ein. b) Begründen Sie mithilfe der Definition, dass sich diese Datenbank noch nicht in der ersten Normalform befindet. Überführen Sie anschließend die relationale Datenbank in die erste Normalform. c) Geben Sie eine Definition dafür an, dass sich eine Datenbank in der zweiten Normalform befindet. d) Geben Sie eine Definition dafür an, dass sich eine Datenbank in der dritten Normalform befindet. Überführen Sie die Datenbank anschließend in die dritte Normalform. e) Analysieren Sie die folgende SQL-Anweisung und erläutern deren Zweck. i) SELECT Busfahrer.Fahrername FROM Busfahrer INNER JOIN Reiseangebote ON Busfahrer.Stadtname = Reiseangebot.Stadtname WHERE Reiseangebot.Preis < 300.00 ii) SELECT Dauer, AVG(Preis) AS D_Preis FROM Reiseangebote GROUP BY Dauer f) Implementieren Sie SQL-Anweisungen auf Basis ihrer Datenbank in dritter Normalform für folgende Abfragen: i) Alle Reisetermine zur Stadt Paris. ii) Alle Orte (PLZ reicht aus) in denen Kunden wohnen, die eine Reise nach Paris gebucht haben. iii) Alle Kundendaten der Kunden, die von "Lukas Lunert" zu ihrem Reiseziel geführt wurden. iv) Den Namen des Kunden mit der kleinsten Kundennummer. v) Alle Busfahrernamen, die eine Reise leiten, welche mindestens 7 Tage dauert und deren Reiseziel mit "P" anfängt (z. B. Prag, Paris, Podgorica, ...). Lösung: Aufgabe 2: Datenbanken – Normalisierung, SQL a) Ein Reiseangebot gehört zu einer eindeutigen Stadt und hat einen festen Preis und eine feste Dauer. Die einzelnen Reisen (terminliche Ausprägungen mit deren Nummer) werden als Mehrfachattribut gespeichert. Damit können zu einem Angebot mehrere Reisen gespeichert werden. Die Busfahrer haben einen Namen und können (realisiert durch ein Mehrfachattribut) beliebig viele Städte anfahren. Welche Reisen nun tatsächlich von den Busfahrern gefahren werden steht in der Relation fährt. Hier ist es möglich für jede Zuordnung Fahrer-Reise eine neue Zeile anzulegen. Die Kundendaten Name, Adresse und Kundennummer aller Kunden und deren jemals gebuchten Reisen (realisiert durch Mehrfachattribut) werden in der Tabelle Kunden gespeichert. b) Die Datenbank befindet sich nicht in erster Normalform, da nicht alle Tabellen über Primärschlüssel verfügen sowie die Attribute nicht atomar sind. Reiseangebote(Stadtname, Preis, Dauer) Reisen(Stadtname, ReiseNr, Termin, FahrerNr) Busfahrer(FahrerNr, Vorname, Nachname) kannFahren(FahrerNr, Stadtname) Kunden(KundenNr, Vorname, Nachname, Straße, PLZ, Ort) gebucht(KundenNr, ReiseNr) c) Eine Datenbank befindet sich in der 2. Normalform, wenn sie sich in der ersten Normalform befindet und jedes Nichtschlüsselattribut vom gesamten Primärschlüssel abhängig ist. d) Eine Datenbank befindet sich in der 3. Normalform, wenn sie sich in der zweiten Normalform befindet und jedes Attribut direkt (und nicht transitiv) vom Gesamtschlüssel abhängig ist. Dies ist hier nicht der Fall, da z. B. Ort nur von PLZ abhängig ist. Kunden(KundenNr, Name, Straße, PLZ) Orte(PLZ, Ort) e) i) Zuerst werden die Tabellen Busfahrer und Reiseangebote gejoint. Damit erhalten wir alle Informationen über die Möglichen Kombinationen Stadt-Busfahrer. Hiervon werden nun nur die Reisen ausgewählt, deren Preis unter 300 Euro liegt. Abschließend wird projiziert auf den Namen des Busfahrers. Es werden alle Busfahrer ausgegeben, die eine Stadt anfahren können, deren Reisepreis unter 300 Euro liegt. ii) Die Daten der Tabelle Reiseangebote werden nach ihrer Dauer gruppiert. Von jeder Gruppe wird die Dauer und der durchschnittliche Reisepreis aller zu dieser Gruppe gehörenden Reisen ausgegeben. Der durchschnittliche Reisepreis wird in einem Attribut D_Preis ausgegeben. f) i) select Termin from Reisen where Stadtname = "Paris" ii) select Kunden.PLZ from Kunden inner join gebucht on Kunden.KundenNr = gebucht.KundenNr inner join Reise on gebucht.ReiseNr = Reise.ReiseNr where Reise.Stadtname = "Paris" iii) select Kunden.* from Kunden inner join gebucht on Kunden.KundenNr = gebucht.KundenNr inner join Reise on gebucht.ReiseNr = Reise.ReiseNr inner join Busfahrer on Reise.FahrerNr = Busfahrer.FahrerNr where Busfahrer.Vorname = "Lukas" and Busfahrer.Nachname = "Lunert" iv) select Vorname, Nachname from Kunden where KundenNr = ( select min(KundenNr) from Kunden ) v) select Busfahrer.Nachname, Busfahrer.Vorname from Busfahrer inner join Reise on Busfahrer.FahrerNr = Reise.FahrerNr inner join Reiseangebot on Reise.Stadtname = Reiseangebot.Stadtname where Reiseangebot.Dauer >= 7 and Reiseangebot.Stadtname like "P%"
