Aufg. Thema bearbeitet Punkte von 1 Allgemeine Fragen
Werbung
Klausur „Datenbanken und Informationssysteme“ Studiengänge AI, MI und WI 11.03.2002 Lösung Dozentin: Dr. H. Faeskorn – Woyke Name ...................................... Vorname.............................................. Matrikel-Nr ...................................... Unterschrift ....................................... eingeschrieben seit ........................... Studiengang ........................................ Bitte beachten Sie: • • • • • Es sind keinerlei schriftliche Unterlagen als Hilfsmittel zugelassen. Zum Bestehen der Klausur sind 50 % der insgesamt erreichbaren Punkte notwendig. Heftung bitte nicht lösen ! Bearbeitungszeit maximal 120 Minuten. Lösungen bitte auf dem hinter der Aufgabenstellung vorgesehenen Platz durchführen. Falls dieser Platz nicht ausreicht, können Sie Zusatzblätter verwenden. Aufgabenteil bitte auf Zusatzblatt angeben und bei entsprechender Aufgabenstellung einlegen. • Jedes Zusatzblatt bitte mit Namen und Matrikelnummer versehen. • Bearbeitete Aufgaben bitte auf der Tabelle im Deckblatt ankreuzen. Aufg. Thema 1 Allgemeine Fragen 2 ER-Modell 3 Vermischtes 4 Normalformen 5 SQL-Abfragen 6 B*-Baum bearbeitet GESAMT 0 Punkte von 25 20 15 12 18 10 100 Aufgabe 2: ER-Modellierung 15 + 5 Punkte Ein Gourmet blickt nicht mehr durch. Welche Weinflaschen hat er im Keller und was passt zu welchem Gericht? Deshalb will er sich die Mühe machen, seinen Weinbestand und seine Lieblingsgerichte in einer Datenbank zu speichern. Können Sie ihm helfen ? Er möchte abspeichern: - - - Über seine Weinflaschen: Die Rebsorte, z.B., Riesling, die Lage, das Anbaujahr und den Alkoholgehalt. Lagerbestand: Welche Weinflaschen er von welcher Wein auf dem Lager hat, in welcher Menge und bei welchem Mindestbestand eine Neubestellung erforderlich ist. Über die Rebsorten: Die Sorte, z.B. Riesling und die Farbe, z.B. rot, rose und weiß Welches Gericht passt zu welcher Rebsorte, z.B. Riesling zu Seezunge. Die Rezepte der Gerichte a) Erstellen Sie ein ER-Modell als konzeptionelles Schema! b) Lösen Sie das Schema in ein relationales Datenbankschema auf! Relationen in der Form Tabellename(Spaltenname, ...) beschreiben. a) Weinflasche Id Weinsorte Art (FK) Lage Jahr Alkoholgehalt Art Farbe Lagerbestand Id (FK) Verkaufspreis Bestand Mindestbestand Gericht Gerichtname Rezept Weinflasche Id: NUMBER Weinsorte Art: VARCHAR2(20) Lage: VARCHAR2(20) Jahr: NUMBER Alkoholgehalt: NUMBER Art: VARCHAR2(20) Farbe: VARCHAR2(20) Weinsorte_Gericht Art: VARCHAR2(20) Gerichtname: VARCHAR2(20) Lagerbestand Id: NUMBER Verkaufspreis: NUMBER Bestand: CHAR(18) Mindestbestand: NUMBER Gericht Gerichtname: VARCHAR2(20) Rezept: VARCHAR2(20) b) 1 Aufgabe 3: (Vermischtes) 15 Punkte Bitte wählen Sie zu jeder der folgenden Teilaufgaben die richtige(n) Antwort(en) durch ankreuzen () bzw. tragen Sie die richtige Lösung in das dafür vorgesehene Feld ( [___] ) ein. (je richtige Antwort: +1 Punkt; je falsch angekreuzte Antwort: -1 Punkt; je Teilaufgabe: mind. 0 Punkte) 3.1. Welche der folgenden Aussagen trifft zu? Das konzeptionelle Schema ist unabhängig vom konkreten Datenbanksystem. Das konzeptionelle Schema ist abhängig vom konkreten Datenbanksystem. 3.2. Welche der folgenden Operatoren gehören nicht zur relationalen Algebra Multiplikation Projektion Differenz Division Addition 3.3. Welche der folgenden Aussagen trifft zu? Von einem eindeutigen Schlüssel sind alle anderen Attribute funktional abhängig. Ein Relation kann nur einen eindeutigen Schlüssel haben. Ein eindeutiger Schlüssel besteht immer aus einem einzigen Attribut. Die Nicht-Schlüsselattribute sind von keiner echten Teilmenge des Schlüssels funktional abhängig 3.4. In einer SELECT- Anweisung sind die Zeilen durcheinander geraten. Sortieren Sie die im Folgenden angegebenen Zeilen so, dass sich wieder eine korrekte SQL – Anweisung ergibt. Nummerieren Sie dazu die Zeilen in einer richtigen Reihenfolge, bei 1 beginnend, fortlaufend durch. Zeile Zeilen-Nr.: SELECT K.Kun_Nr, K.Nachname, K.Ort, COUNT(A.AuftragsNr) [ 1 ] ORDER BY K.Nachname [___6__] GROUP BY K.Kun_Nr, K.Nachname, K.Ort [___4__] WHERE K.Kun_Nr = A.Kun_Nr AND K.Ort = 'Köln' [___3__] FROM Kunden K, Auftraege A [___2__] HAVING COUNT(*) > 1; [__ 5__] 3.5. In welcher der folgenden Zeilen tritt ein Fehler auf? SELECT Abt_Nr, avg(Gehalt) FROM Angestellte GROUP BY Ang_Nr. Es tritt kein Fehler in dieser SQL – Anweisung auf. 3.6 Welche der folgenden Funktionen ist keine Gruppenfunktion ? AVG SQRT COUNT 2 SUM MAX 3.7 Welche der Abfragen liefert das gewünschte Ergebnis? Der Theaterdirektor Herr Gucklos möchte sein Theater reformieren. Er möchte wissen, ob es Schauspieler gibt, die alle Rollen dargestellt haben. In seiner Datenbank finden sich folgende Relationen Schauspieler( PNr, Name, Gehalt...) Rolle(Figur, Titel, Typ, ...) Stellt_dar(PNr, Figur) Die folgenden Abfragen liegen ihm vor: SELECT PNr SELECT PNr FROM Schauspieler WHERE PNr NOT IN (SELECT PNr FROM Stellt_dar WHERE Figur NOT IN (SELECT Figur FROM Rolle WHERE Schauspieler.PNr = stellt_dar.PNr FROM Schauspieler WHERE NOT EXISTS (SELECT * FROM Rolle WHERE NOT EXISTS (SELECT * FROM Stellt_dar WHERE Schauspieler.PNr = AND stellt_dar.Figur = stellt_dar.PNr rolle.Figur)); AND stellt_dar.Figur = rolle.Figur)); SELECT PNr, count(Figur= FROM Stellt_dar GROUP BY PNr HAVING COUNT(DISTINCT Figur) = (SELECT COUNT(DISTINCTfigur) FROM rolle); SELECT PNr FROM Stellt_dar WHERE Figur = ALL( SELECT Figur FROM Rolle); Keine dieser Anweisungen liefert das gewünschte Ergebnis. 3.8 Welche der Abfragen liefert das gewünschte Ergebnis? Der Theaterdirektor Herr Gucklos möchte auch wissen, welcher Schauspieler das höchste Gehalt hat. Die folgenden Abfragen liegen ihm vor: SELECT MAX(Gehalt) FROM Schauspieler; SELECT PNR, Gehalt FROM Schauspieler WHERE Gehalt = (SELECT MAX(Gehalt) FROM Schauspieler); SELECT PNr, Gehalt FROM Schauspieler WHERE GEHALT = MAX(Gehalt); SELECT PNR, MAX(Gehalt) FROM Schauspieler; 0 Aufgabe 4: Normalformen 3 +3+3+3 Punkte Betrachten Sie die folgende Auftragsrelation: Auftrag (ProduktNr, ProduktName, KundenNr, KundenName, Datum, Stückpreis, Anzahl, Mehrwertsteuersatz,) a) Bestimmen Sie die funktionalen Abhängigkeiten in der Auftrag-Relation. Was sind die Schlüsselkandidaten? b) In welcher Normalform ist diese Relation? Begründen Sie, warum sie nicht in einer entsprechend höheren Normalform ist. c) Überführen Sie die Relation in die zweite Normalform! d) Überführen Sie die Relation in die dritte Normalform ! Hinweise: Beachten Sie, dass der Mehrwertsteuersatz vom Produkt anhängt (z. B. 8 % für Bücher oder 16 % für Luxusartikel). Geben Sie das Datenbankschema in c) und d) in der Form Relation(Attributname, ..) an. Kennzeichnen Sie Primärschlüssel und Fremdschlüssel! a) ProduktNr -> ProduktName, StückPreis, Mehrwertsteuersatz KundenNr -> KundenName ProduktNr, KundenNr -> Datum, Anzahl Schlüsselkandidaten ; ProduktNr, KundenNr b) Erste Normalform. Es treten noch transitive Abhängigkeiten auf. c) 2NF: Produkt(ProduktNr, StückPreis, Mehrwertsteuersatz) Kunde(KundenNr, KundenName) Auftrag(KundenNr, ProduktNr, Anzahl) d) 3NF = 2NF 1 Aufgabe 5: SQL-Abfragen. 18 Punkte Die Schulungsabteilung einer Großfirma bietet Kurse an, die als Kursläufe zu bestimmten Zeiten und an unterschiedlichen Orten durchgeführt werden. Hörer nehmen an bestimmten Kursläufen teil. Die Datenbank SCHULUNG hat die folgende Form: HOERER HoererNr 123 321 234 Name Weber Zwickel Ast Vorname Peter Christa Thomas KURSLAUF LauflNr KursNr 111 987 222 876 333 987 444 765 Ort München Berlin Stuttgart München TEILNAHME LaufNr HoererNr 111 123 444 234 333 234 ... ... Note 1,3 2,7 1,0 ...... GebDat 12.01.70 22.07.71 03.02.72 DatumAb 04.10.2000 10.10.2000 16.10.2000 16.10.2000 Adresse München Ottobrunn Starnberg TelefonNr 123456 234567 345678 Beruf Buchhalt. Informatiker Konstrukt. Abteilung GS12 RZ01 KO03 DatumBis 06.10.2000 13.10.2000 18.10.2000 19.10.2000 KURS KursNr 987 876 765 ... Kursname WindowsNT MSWordMSWord... Abstract BlaBla LaberLaber GluGlu ... Bitte bearbeiten Sie folgende Anforderungen in SQL! a) Geben Sie alle Hörer an (HoererNr, Name, Vorname), die an dem Kurs ’WindowsNT’ teilgenommen haben. b) Geben Sie für alle Kurse die Durchschnittsnote an (Kursname, Durchschnittsnote). c) Geben Sie den Ort / die Orte mit den meisten Kursläufen an. d) Geben Sie die Hörer an, die an allen angebotenen Kursen teilgenommen haben. e) Ändern Sie die Telefonnummer des Hörers mit der HoererNr = 321 auf 765432. a) SELECT HoererNr, Name, Vorname 4 Punkte FROM Hörer h, Teilnahme t , Kurs k, Kursläufe KK WHERE h.HoererNr = t.HoererNr AND k.KursNr = KK.KursNr AND t.LaufNr = k.LaufNr AND K.Kursname = ’WindowsNT’; b) SELECT k.NAme, avg(t.note) AS Durchschnittsnote 4 Punkte FROM Teilname t , Kurs K , Kurslauf L WHERE l.LaufNr = T.LaufNr AND L.KursNr = l.KursNr GROUP BY k.Name c) SELECT Ort, COUNT(Ort) 4 Punkte FROM Kurslauf GROUP BY Ort HAVING COUNT(Ort) >= ALL (SELECT COUNT(ort) GROUP BY Ort); e) SELECT L.HoererNr 4 Punkte FROM Kurslauf L, Teilnahme T WHERE L.HoererNr = K.HoererNr GROUP BY T.HoererNr HAVING COUNT(L.KursNr) = (SELECT COUNT(KursNr) FROM KURS); f) UPDATE HOERER SET TelefonNr = ‘765432’ WHERE HoererNr = 321; 2 Punkte 2 Aufgabe 6: B*Baum 10 Punkte Fügen Sie in einen B*-Baum vom Typ 1 nacheinander folgende Zahlen ein: 1 , 3 , 5 , 7 , 9 , 10, 8, 6, 4 und 2 ! 1
