Ludwig-Maximilians-Universität München Institut für Informatik Prof. Dr. Christian Böhm Stefan Schönauer, Stefan Brecheisen, Eshref Januzaj München, 23.01.2004 Datenbanksysteme I WS 2003/04 Übungsblatt 12 Keine Abgabe Besprechung im Rahmen der Fragestunde: 02.02. – 06.02.2004 Die Aufgaben auf diesem Blatt dienen der Klausurvorbereitung. Sie decken aber weder den gesamten klausurrelevanten Stoff ab, noch geben sie Aufschluß über den Umfang der Klausuraufgaben. Es sind keine Lösungen abzugeben, Sie sollten aber die Aufgaben vor der Fragestunde zu Hause bearbeiten. Sie haben während der Fragestunde Gelegenheit, Fragen zu diesem Blatt zu stellen, es wird aber keine vollständige Lösung für dieses Blatt vorgeführt. Aufgabe 12-1 Datenmodelle Gegeben sei das folgende Relationenschema: Student (MatrNr, Name, Semester) Vorlesung (VorlNr, Titel, SWS) Professor (PersNr, Name, Rang, Raum) Assistent (PersNr, Name, Fachgebiet, Chef) hören (MatrNr, VorlNr) halten (PersNr, VorlNr) prüfen (PersNr, MatrNr, VorlNr, Note) Das Schema stellt einen Ausschnitt aus der Datenbank einer Universität dar. Studenten hören Vorlesungen und werden von einem Professor zu ein oder mehreren Vorlesungen geprüft. Ein Professor hält mehrere Vorlesungen. Eine Vorlesung wird jedoch nur von genau einem Professor gehalten. Mehrere Assistenten arbeiten für einen Professor (Attribut Chef“). ” (a) Erläutern Sie kurz anhand des Beispiels, welche Möglichkeiten das Relationale Modell zur Modellierung von 1:n-Beziehungen bietet. (b) Erstellen Sie für obiges Datenbankschema ein Entity-Relationship-Diagramm. 1 Aufgabe 12-2 Relationale Algebra und Kalküle Gegeben sei das folgende Relationenschema. Dabei sind die Schlüsselattribute jeweils unterstrichen. Produkt (PNR, Name, Gewicht, Farbe) Filiale (FNR, Name, Ort) Angestellter (PersNr, Name, Gehalt, FNR) Verkauf (PNR, PersNr, Datum, VKPreis) Geben Sie für die folgende verbal formulierten Anfrage einen äquivalenten Ausdruck in Relationaler Algebra und wahlweise in relationalem Tupel- oder Bereichskalkül an. Sie können an passender Stelle auch den JoinOperator benutzen. • Bestimmen Sie Name und Verkaufspreis aller Produkte, die der Angestellte Peter Müller am 15.01.2002 verkauft hat. Aufgabe 12-3 SQL Gegeben sei das folgende Relationenschema. Dabei sind die Schlüsselattribute jeweils unterstrichen. Zusätzlich ist für jedes Attribut der Typ angegeben. Fahrzeug (mnr: int(3), fznr: char(12), baujahr: int(4), km stand: int(6), preis: int(6)) Modell (mnr: int(3), hnr: int(3), typ: char(20), neupreis: int(6), ps: int(3)) Hersteller (hnr: int(3), name: char(20)) Formulieren Sie die folgenden Anfragen in SQL. (a) Geben Sie die Anweisung in SQL-DDL an, die notwendig ist, um die Relation Fahrzeug zu erzeugen. Achten Sie dabei darauf, den Primärschlüssel der Relation zu kennzeichnen. (b) Bestimmen Sie den Durchschnittspreis, bezogen auf das Attribut preis“ der Fahrzeuge eines jeden Her” stellers. (c) Bestimmen Sie die Namen aller Hersteller von denen mindestens fünf Fahrzeuge eines beliebigen Modells in der Datenbank gespeichert sind. 2 Aufgabe 12-4 Normalisierung In der folgenden Datenbank sind die Ausleihvorgänge einer Bibliothek gespeichert. Ausleihe LNr 1 1 2 2 3 4 5 Name Müller Müller Huber Huber Meier Meier Müller Adresse Oettingenstr. Oettingenstr. Leopoldstr. Leopoldstr. Helene-Mayr-Ring Türkenstr. Oettingenstr. BNr 1 4 2 3 1 5 2 Titel Datenbanksysteme OODBS Anatomie I Harry Potter Datenbanksysteme Pippi Langstrumpf Anatomie I Kategorie Informatik Informatik Medizin Kinderbuch Informatik Kinderbuch Medizin ExNr 1 1 5 20 2 1 3 Für die Datenbank gilt: Jeder Leser hat eine eindeutige Lesernummer (LNr), einen Namen und eine Adresse. Ein Buch hat eine Buchnummer (BNr), einen Titel und eine Kategorie. Es kann mehrere Exemplare eines Buches geben, welche durch eine innerhalb einer Buchnummer eindeutigen Exemplarnummer (ExNr) unterschieden werden. Nachfolgend sind alle nicht-trivialen funktionalen Abhängigkeiten (inkl. der transitiven), welche in der obigen Datenbank gelten, angegeben: LNr → Name LNr → Adresse BNr → Titel BNr → Kategorie LNr, BNr, ExNr → Name, Adresse, Titel, Kategorie Einziger Schlüsselkandidat ist {LNr, BNr, ExNr}. (a) Entscheiden Sie, ob das Relationenschema die 2. Normalform erfüllt. Begründen Sie Ihre Antwort. Wenn nötig, überführen Sie das Schema in die 2. Normalform. (b) Entscheiden Sie, ob das aus (a) resultierende Relationenschema die 3. Normalform erfüllt. Begründen Sie Ihre Antwort. Wenn nötig, überführen Sie das Schema in die 3. Normalform. 3