Angabe zum dritten Beispiel des Laborübungsteils aus Datenmodellierung WS08

Günther Charwat, Verena Charwat, Markus Pichlmair, Katrin Seyr

dm@dbai.tuwien.ac.at

• Allgemeines
• Das Abgabegespräch
• Schokoladefabrik - Die Datenbank
  • Beschreibung
  • Das Relationenmodell
  • Die Implementierung der Tabellen
  • Die SQL-Anfragen
  
  

  Allgemeines

  In diesem Übungsteil sollten Sie das Erstellen von SQL-Anfragen üben. Dazu wird unter http://websql.dbai.tuwien.ac.at (WS08 FIXED) ein Interface zum Zugriff auf eine Postgres Datenbank (v.8.1) zur Verfügung gestellt. Falls Sie lieber auf Ihrer eigenen Datenbank arbeiten, können Sie die Testdaten für dieses Beispiel auf der Webseite als daten.zip herunterladen.

  Bitte versuchen Sie nach Möglichkeit die Beispiele eigenständig zu lösen, denn in der Praxis (und bei der Prüfung) sind Sie auch auf sich alleine gestellt.

  
  

  Das Abgabegespräch

  1. Sie müssen sich über den LVAManager zu einem Abgabegespräch anmelden. Bitte machen Sie das rechtzeitig, je später Sie sich anmelden, umso eingeschränkter ist das Terminangebot.

  2. Sie müssen die ersten zwölf der unten stehenden Anfragen lösen. Weiters müssen Sie fünf weitere Anfragen lösen, die Sie frei wählen können.

  3. Sie müssen rechtzeitig innerhalb der Upload-Frist Ihre Lösung über den LVAManager abgeben (in txt-Format). Sollten Sie sich zu einem Abgabegespräch angemeldet haben, allerdings bis zum Ende der Upload-Frist keine Datei hochgeladen haben, so wird Ihre Anmeldung für das Abgabegespräch gelöscht und es ist auch keine Abgabe möglich. Beachten Sie beim Upload Ihrer Lösung die im LVAManager angezeigten Hinweise.

  4. Sie kommen im Informatiklabor (http://www.inflab.tuwien.ac.at) mit Studentenausweis zu der von Ihnen reservierten Zeit vorbei, und absolvieren das Abgabegespräch. Grundlage ist das im LVAManager vorhandene File.

  5. Sie haben maximal 20 Minuten für Ihre Abgabe.

  6. Sie können maximal 15 Punkte bei der Abgabe erreichen. Sie werden dabei zu drei von Ihren siebzehn Anfragen befragt. Dabei können Sie jeweils maximal fünf Punkte erreichen, wobei nicht nur die Korrektheit der Abfrage überprüft wird, sondern Sie müssen die Abfragen auch erklären und modifizieren können. Weiters wird erwartet, dass Sie wissen, was die von Ihnen in einem SQL Statement verwendeten Ausdrücke bedeuten bzw. was sie bewirken.

  7. Die Tutoren tragen ein Protokoll des Abgabegesprächs in den LVAManager ein und Sie ersehen ebendort, wieviele Punkte Sie bekommen haben.

  Schokoladefabrik - Die Datenbank

  Für eine Schokoladefabrik existiert folgende Datenbank:

  Beschreibung

  Personen haben eine eindeutige Nummer, einen Vornamen und einen Nachnamen. Sie werden in Künstler und Mitarbeiter eingeteilt. Bei Mitarbeitern ist zusätzlich das Einstellungsdatum, und falls bereits wieder entlassen, das Kündigungsdatum bekannt. Zu Künstlern wird der Bekanntheitsgrad vermerkt. Der Bekanntheitsgrad ist ein Integerwert von 0 bis 10, wobei 0 bedeutet, dass der Künstler unbekannt ist und 10, dass er weltberühmt ist.

  In der Schokoladefabrik werden verschiedene Produkte hergestellt, die über eine Nummer eindeutig identifiziert werden und von denen eine Bezeichnung sowie das Gewicht (in Gramm) bekannt sind. Zu den Produkten gehören das Standardsortiment, für welches ein Verkaufspreis und die Verpackungsart gespeichert werden, sowie Kunstwerke, deren Schätzwerte in der Datenbank vermerkt werden (Kunstwerke sind spezielle Schokolade- oder sonstige Skulpturen, die jeweils als Sonderanfertigung produziert werden).

  Die Schokoladefabrik besitzt verschiedene Maschinen, von denen eine eindeutige Nummer sowie eine Beschreibung gespeichert werden. Mitarbeiter bedienen die Maschinen und jede Maschine kann für die Produktion von betimmten Produkten eingeteilt sein. Weiters existieren Lager, welche von mindestens einem Mitarbeiter betreut werden. Ein Mitarbeiter betreut allerdings maximal ein Lager. Ein Lager hat eine es identifizierende Bezeichnung und eine bestimmte Fläche (in m2). Produkte werden eingelagert, wobei für jedes Produkt bekannt ist, welche Menge (Stückzahl) sich davon in welchem Lager befindet.

  Die Fabrik bekommt von verschiedenen Firmen Produktionsaufträge. Jede Firma wird durch ihren Namen identifiziert und es sind weiters eine Kontaktadresse und Telefonnummer bekannt.

  Pro Firma werden verschiedene Aufträge vergeben, über welche beliebig viele Produkte bei der Fabrik bestellt werden. Ein Auftrag wird durch eine pro Firma eindeutige Nummer identifiziert. Weiters sind das Auftragsdatum und der aktuelle Status bekannt.

  Die produzierten Kunstwerke werden von den dafür verantwortlichen Künstlern bei verschiedenen Kunstschauen hergezeigt. Hier wird vermerkt, welchen Platz ein Künstler mit seinem Kunstwerk bei der Schau erreicht. Die Kunstschauen selbst werden durch einen Namen und ein Datum identifiziert und es ist bekannt, in welchem Land und welchem Ort die Kunstschau stattfindet.

  <

  Das Relationenmodell

  Person(Nummer, Vorname, Nachname)
  Mitarbeiter(Person.Nummer, Einstellungsdatum)
  Kuendigung(Mitarbeiter.Nummer, Kuendigungsdatum)
  Kuenstler(Person.Nummer, Bekanntheit)
  

  Produkt(Nummer, Bezeichnung, Gewicht)
  Standardsortiment(Produkt.Nummer, Preis, Verpackung)
  Kunstwerk(Produkt.Nummer, Schaetzwert)
  

  Maschine(Nummer, Beschreibung)
  bedient(Mitarbeiter.Nummer, Maschine.Nummer)
  erzeugt(Maschine.Nummer, Produkt.Nummer)
  

  Lager(Bezeichnung, Flaeche)
  betreut(Mitarbeiter.Nummer, Lager.Bezeichnung)
  lagert(Lager.Bezeichnung, Produkt.Nummer, Menge)
  

  Kunde(Firmenname, Adresse, Telefonnummer)
  Auftrag(Kunde.Firmenname, Nummer, Datum, Status)
  enthaelt(Auftrag.Firmenname, Auftrag.Nummer, Produkt.Nummer, Menge)
  

  Kunstschau(Datum, Name, Ort, Land)
  zeigt(Kuenstler.Nummer, Kunstwerk.Nummer, Kunstschau.Datum, Kunstschau.Name, Platz)
  

  
  

  Die Implementierung der Tabellen

  Person(nummer, vorname, nachname)
  Mitarbeiter(nummer, einstellungsdatum)
  Kuendigung(nummer, kuendigungsdatum)
  Kuenstler(nummer, bekanntheit)
  

  Produkt(nummer, bezeichnung, gewicht)
  Standardsortiment(nummer, preis, verpackung)
  Kunstwerk(nummer, schaetzwert)
  

  Maschine(nummer, beschreibung)
  bedient(mitnummer, maschnummer)
  erzeugt(maschnummer, pnummer)
  

  Lager(bezeichnung, flaeche)
  betreut(mitnummer, bezeichnung)
  lagert(bezeichnung, pnummer, menge)
  

  Kunde(firmenname, adresse, telefonnummer)
  Auftrag(firmenname, nummer, datum, status)
  enthaelt(firmenname, nummer, pnummer, menge)
  

  Kunstschau(datum, name, ort, land)
  zeigt(knummer, kunstwerknummer, datum, name, platz)
  

  
  

  Die SQL-Anfragen

  1. Geben Sie die Bezeichnung und das Gewicht aller Produkte aus.

  2. Geben Sie die Bezeichnung und den Schätzwert aller Kunstwerke aus und sortieren Sie die Liste nach Gewicht.

  3. Geben Sie die Bezeichnung und das Gewicht aller Kunstwerke aus, für die der Schätzwert kleiner als 5000 Euro ist und sortieren Sie das Ergebnis nach dem Schätzwert.

  4. Geben Sie für alle gekündigten Mitarbeiter den Namen (Vorname und Nachname) und das Kündigungsdatum aus.

  5. 1. Wieviele Maschinen werden benötigt um 'Mika Fender' herzustellen?
     2. Geben Sie die Namen der Produkte aus, die von Maschinen erzeugt wurden und geben Sie die Anzahl der benötigten Maschinen aus.
     3. Geben Sie an, welche Produkte die meisten Maschinen für deren Herstellung benötigen. Geben Sie dafür die Bezeichnung und die Anzahl aus.

  6. Geben Sie den durchschnittlichen Preis aller Produte des Standardsortiments aus, die in Plastik ('Plastiksackerl' oder 'Plastikfolie bedruckt') verpackt sind.

  7. Geben Sie für Standardprodukte (Nummer und Bezeichnung) aus, mit welchen Maschinen (Nummer und Beschreibung) sie erzeugt wurden.

  8. 1. Geben Sie eine Liste ALLER Kunstwerke (Bezeichnung) aus und bei welchen Kunstschauen (Name und Datum) sie ausgestellt wurden. Wenn ein Kunstwerk nicht ausgestellt wurde geben Sie an Stelle des Namens der Ausstellung 'unter Verschluss' und statt dem Datum das aktuelle Datum aus (Stichwort COALESCE).
     2. Geben Sie eine Liste ALLER Kunstwerke aus und wenn das Kunstwerk in Österreich ausgestellt wurde, bei welchen Kunstschauen (Name und Datum) es ausgestellt wurden. Wenn ein Kunstwerk nicht oder nicht in Österreich ausgestellt wurde geben Sie anstelle des Datums das aktuelle Datum und anstelle des Namens 'keine Ausstellung in AUT'

  9. 1. Geben Sie das erste Kündigungsdatum aus.
     2. Geben Sie die Namen und Vornamen jener Mitarbeiter aus, die als erstes gekündigt wurden.

  10. Geben Sie jene Künstler aus (Name und Vorname), die an allen Kunstschauen in Graz teilgenommen haben.

  11. Geben Sie Produktnummer und Bezeichnung aller Produkte aus, die in der Produktion nicht durch die Füllanlage 1 gehen.

  12. 1. Geben Sie für jeden Kunden (Name und Adresse) die Anzahl der jeweiligen Aufträge aus, die noch nicht abgeschlossen wurden.
      2. Geben Sie aus, wieviele Kunden mit nicht abgeschlossen Aufträgen es gibt.

  13. Geben Sie Namen und Vornamen des bekanntesten Künstlers aus, der nicht auch Mitarbeiter im Unternehmen ist.

  14. Geben Sie die Namen und Vornamen jener Künstler aus, die auch Mitarbeiter des Unternehmens sind und einen größeren Bekanntheitsgrad als 6 haben.

  15. Geben Sie Name, Vorname und Nummer jener Künstler aus, die bei Kunstschauen die meisten Werke ausgestellt haben.

  16. Gesucht sind Nummer, Vorname und Nachname jener Mitarbeiter, die alle Maschinen bedienen können.

  17. Geben Sie eine Liste ALLER Lager (Bezeichnung und Fläche) aus mit folgenden Infos: die Fläche des Lagers, die Anzahl der verschiedenen Produkte die darin gelagert werden, sowie die Gesamtmenge aller Produkte.

  18. Bei wievielen Kunstschauen in Österreich konnten Künstler einen Platz unter den ersten drei machen?

  19. Geben Sie eine nach Plätzen sortierte Liste der Plätze aus, die je mit Kunstwerken in Österreich gemacht wurden, und dazu die Anzahl der Plätze.

  20. Gesucht sind Vorname und Nachname jener Personen, die keine Künstler sind, nicht gekündigt wurden und mindestens einen Mischer bedienen können.

  21. Schnäppchen sind entweder Kunstwerke mit einem Schätzwert unter 2000 Euro oder Süßigkeiten unter 3 Euro. Geben Sie eine Liste von Schnäppchen (bezeichnung und gewicht) aus. Vermerken Sie dabei auch in einer eigenen Spalte, die Sie 'ist' nennen, ob es sich um ein Kunstwerk oder ein Produkt aus dem Standardsortiment handelt. (Hinweis: Verwenden Sie das keyword union).

  22. Erstellen Sie eine Liste ALLER Kunden (Firmennamen) und geben Sie zu jedem Kunden den Gesamtpreis der in sämtlichen Aufträgen bestellten Produkten aus dem Standardsortiment. Nennen Sie diese Spalte Gesamtpreis und sortieren Sie die Liste danach.

  23. Geben Sie eine Liste ALLER Künstler (Vor- und Nachname) aus und das Datum des letzten Kunstschau, an der dieser Künstler teilgenommen hat.

  24. Geben Sie die Bezeichung und die Fläche jener Lager aus in denen mehr als 20% der Produkte lagern.