Thema 1: Datenbanken
Werbung
TU Bergakademie Freiberg Institut für Informatik Modul „Einführung in die Informatik“ Großübung Modul „Einführung in die Informatik“: Großübung 6 Thema: Datenbankentwurf, SQL; Python (Typ Dictionary) - Aufgaben - Thema 1: Datenbanken Aufgabe 1) Datenbankentwurf (ERD) sowie SQL (aus der Klausur vom 16.02.2006, damals noch 2/2 SWS) ---------------------------------------------------------------------------------------------------------------------------Datenbanksysteme (ERM und SQL) Gegeben sei die folgende relationale Datenbank „Online-Werkzeug-Bestellsystem“ mit den folgenden Tabellen (Relationen) Kunde, Werkzeug und Kauf: *********************************************************************** Kunde (KNr, Name, Adresse, TelNr) Kundennummer (Primärschlüssel), TelNr ... KNr ... Telefonnummer Werkzeug (WId, Bezeichnung, Preis, Lagerort) Werkzeugidentifikator (Primärschlüssel) WId ... Kauf (BNr, KNr, WId, LDatum, Stueck) BNr … Bestellnummer (Primärschlüssel), KNr, WId ... Fremdschlüssel (siehe oben) LDatum ... Lieferdatum, Stueck … bestellte Stückzahl ***************************************************************************** (Teil 1) Entity-Relationship-Modell-Diagramm (ERM-Diagramm) Zeichnen Sie für die oben angegebene Datenbank „Online-Werkzeug-Bestellsystem“ ein EntityRelationship-Diagramm mit Entity- und Relationship-Typen (einschließlich der Attribute). Benutzen Sie dazu die Methodik und die Symbole aus der Vorlesung. Fremdschlüssel sollen in Relationshipsets nicht aufgeführt werden. (Hinweis: Vergessen Sie nicht die Zuordnungscharakteristika.) ------------------------------------------------------------------------------------------------------------------------------- (Teil 2) SQL-Kommandos (SQL … Structured Query Language) Alle folgenden Aufgaben sind jeweils mit nur einer SQL-Anweisung zu lösen: a) Geben Sie alle Daten für alle Kunden aus, ohne die Attribute in der Anfrage einzeln namentlich aufzuführen, und zwar absteigend sortiert nach Name und bei gleichem Namen aufsteigend sortiert nach der Kundennummer. b) Geben Sie den Werkzeugidentifikator und die Bezeichnung aller Werkzeuge mit einem Preis von mehr als 100 aus, die im Lagerort Freiberg oder im Lagerort Dresden liegen. c) Nehmen Sie eine Preissenkung vor: Für alle Werkzeuge, deren Preis über 200 (EUR) liegt, ist in der Datenbank deren Preis um 20% zu senken. d) Fälschlicherweise wurde in mehreren Zeilen als Bezeichnung Hammel statt korrekt Hammer eingetragen. Korrigieren Sie diesen Fehler in der Datenbank. 1 0112 TU Bergakademie Freiberg Institut für Informatik Modul „Einführung in die Informatik“ Großübung e) Geben Sie für den Kunden mit der Kundennummer 4812 für alle von ihm gekauften Werkzeuge den Namen des Kunden und den Werkzeugidentifikator aus, jeden Werkzeugidentifikator jedoch nur einmal. ------------------------------------------------------------------------------------------------------------------------------ Aufgabe 2) SQL (Datenbank personal) Gegeben sei eine relationale Datenbank personal, die aus drei Relationen (Tabellen) besch, abt und gehstufe besteht. Diese drei Relationen werden im Folgenden in der Form Relationsname(Attributname_1, Attributname_2, ...) erläutert (Primärschlüssel sind dabei unterstrichen): besch(bnr, name, taet, ltr, e_datum, geh, zuschl, abtnr) - Erläuterung der Relations- bzw. Attributnamen: besch ... Beschäftigte; bnr ... Beschäftigtennummer; name ... Name; taet ... Tätigkeit; ltr ... Beschäftigtennummer des Leiters; e_datum ... Einstellungsdatum; geh ... Gehalt; zuschl ... Zuschlag zum Gehalt; abtnr ... Abteilungsnummer der Abteilung, zu der der Beschäftigte gehört; abt(abtnr, abtname, ort) - Erläuterung der Relations- bzw. Attributnamen: abt ... Abteilungen; abtname ... Abteilungsname; abtnr ... ort ... Abteilungsnummer; Ort; gehstufe(stufe, von, bis) - Erläuterung der Relations- bzw. Attributnamen: gehstufe ... Gehaltsstufen; stufe ... Gehaltsstufe; von ... Die Gehaltsstufe beginnt bei diesem Gehaltsbetrag; bis ... Die Gehaltsstufe endet bei diesem Gehaltsbetrag; Bemerkung: Mit dieser Datenbank wird auch in der 7. Poolübung gearbeitet. ------------------------------------------------------------------------------------------------------------------------------ Aufgaben zu SQL : (alle Aufgaben beziehen sich auf die oben erläuterte Datenbank personal): Die folgenden Aufgaben sind jeweils mit nur einer SQL-Anweisung zu lösen. (Ausnahmen: bei b), c) und d) sind mehrere SQL-Anweisungen verlangt.) a) Eine Datenbank personal mit den beschriebenen Relationen (Tabellen) besch, abt und gehstufe ist anzulegen. Zunächst ist die Datenbank zu definieren (anzulegen). (Bemerkung: Das hier benötigte Kommando CREATE DATABASE wurde in der Vorlesung nur kurz erwähnt. Für das Verständnis der weiteren Aufgabe ist es aber von Nutzen.) b) Die drei Tabellen besch, abt und gehstufe der eben definierten Datenbank personal sind zu definieren (anzulegen). (Bemerkung: Auch das hier benötigte Kommando CREATE TABLE wurde in der Vorlesung nur kurz behandelt.) 2 0112 TU Bergakademie Freiberg Institut für Informatik Modul „Einführung in die Informatik“ Großübung c) Die drei Tabellen besch, abt und gehstufe sind mit sinnvollen Daten zu füllen. d) Die drei Tabellen der Datenbank personal sollen vollständig ausgegeben werden. e) Für alle Beschäftigten sind Name, Einstellungsdatum und Gehalt auszugeben, und zwar in der eben aufgeführten Reihenfolge. f) Welche Tätigkeit übt Schmidt aus? g) Auszugeben sind alle Daten für alle Verkaeufer und Sachbearbeiter, die über ein Gehalt von mehr als 2700,- (EUR) verfügen. h) Gesucht sind die Namen aller Abteilungen (ohne Mehrfachnennung), in denen Sachbearbeiter arbeiten. Diese Aufgabe ist in zwei Varianten zu lösen: h1) mit einem Join (Equi-Join) h2) mit einer geschachtelten Anfrage i) Welche Gehaltsstufe hat der Beschäftigte Thomas? j) Der Beschäftigte Mueller wechselt in die Abteilung mit der abtnr 20. Die Tabelle besch ist entsprechend zu ändern. Anschließend ist zur Kontrolle die gesamte Relation besch auszugeben. k) Der Datensatz für die Gehaltsstufe 5 ist aus der Relation gehstufe zu löschen. Anschließend ist zur Kontrolle die gesamte Relation gehstufe auszugeben. l) Nach einem Jahr wird die (nun veränderte) Gehaltsstufe 5 wieder eingeführt. Sie reicht jetzt von 5201 bis 11500 [EUR]. Dieser Datensatz ist in die Tabelle gehstufe einzufügen. Anschließend ist zur Kontrolle die gesamte Relation gehstufe auszugeben. m) Die Tabelle gehstufe ist einschließlich der Tabellenbeschreibung zu löschen. -----------------------------------------------------------------------------------------------------------------------------An dieser Stelle ist der originäre Zustand der Datenbank durch Ausführung des SQL-Scripts personal.sql wieder herzustellen. -----------------------------------------------------------------------------------------------------------------------------n) Auszugeben sind die Namen aller Personen, die keinen Zuschlag erhalten. Hinweis: In diesem Falle kann unter zuschl sowohl die Zahl 0 als auch ein Leereintrag (NULL) stehen. o) Gesucht sind alle (unterschiedlichen) Tätigkeiten der Abteilung 30, die auch in der Abteilung 20 vorkommen. p) Gesucht sind alle Daten aller Beschäftigten, die mehr verdienen als mindestens ein (irgendein) Beschäftigter der Abteilung 20. q) Was ist in der Lösung von p) zu ändern, um die folgende Aufgabe zu lösen: Gesucht sind alle Daten aller Beschäftigten, die mehr verdienen als alle Beschäftigten der Abteilung 20. 3 0112 TU Bergakademie Freiberg Institut für Informatik Modul „Einführung in die Informatik“ Großübung r) Benötigt werden die Abteilungsnummern aller Abteilungen, die mehr als 4 Beschäftigte haben. ------------------------------------------------------------------------------------------------------------------------------- Thema 2: Der Typ Dictionary Aufgabe 3) Eine kleine Python-Datenbank als Liste von Dictionaries - Teilaufgabe 1: Dictionary für einen Studenten (entspricht einem Datensatz einer Datenbank, d.h. es handelt sich hier noch nicht um eine Liste) Es ist eine Funktion für die Eingabe der Werte eines Dictionary zu schreiben, das für einen Studenten die Schlüssel Name, Vorname, Studiengang (kurz: Stdgang) und Matrikelnummer (kurz: Matrikelnr) besitzt. Die Funktion soll keine formalen Parameter besitzen und das eingelesene Dictionary zurückgeben. In der Funktion ist nicht mit globalen Variablen zu arbeiten. In einem Testrahmen (Hauptprogramm) ist diese Funktion aufzurufen und nach der Rückkehr aus der Funktion der Inhalt des Dictionary unformatiert und formatiert auf den Bildschirm auszugeben. ------------------------------------------------------------------------------------------------------------------------------ - Teilaufgabe 2: Liste mehrerer Studenten-Dictionaries (Eine kleine Python-Studenten-Datenbank) - Eingabe und Testanfragen an die Datenbank Der Quelltext aus Teilaufgabe 1 ist nun zu vervollkommnen: Es ist eine Funktion für die Eingabe einer Liste von (mehreren) Dictionaries zu schreiben. Jedes Dictionary dieser Liste soll die in Teilaufgabe 1 erläuterten Schlüssel-Werte-Paare für einen Studenten besitzen. Die Eingabe der Listenelemente (Dictionaries) soll beendet werden, wenn als Name 'Ende' in irgendeiner Schreibweise eingegeben wird. Die Funktion soll keine formalen Parameter besitzen und die eingelesene Liste zurückgeben. In der Funktion ist nicht mit globalen Variablen zu arbeiten. In einem Testrahmen (Hauptprogramm) ist diese Funktion aufzurufen und nach der Rückkehr aus der Funktion der gesamte Inhalt der Datenbank (Liste von Dictionaries) unformatiert und formatiert auf den Bildschirm auszugeben. Schließlich sind vom Testrahmen aus zwei Test-Anfragen an die Datenbank zu richten: - Name und Vorname aller im Studiengang BNC studierenden Studenten - Studiengang und Matrikelnummer des Studenten Peter Heinze Dabei sei nicht bekannt, in welcher Schreibweise BNC bzw. Peter Heinze in der Datenbank eingetragen sind. Auch wenn kein BNC-Student bzw. kein Student Peter Heinze in der Datenbank vorhanden ist, ist eine entsprechende Ausgabe auf den Bildschirm auszugeben. (Sinnvollerweise sollte man beim Test auch entsprechende Daten für diese Anfragen eingeben.) 4 0112
