Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 1 Max. Pkte. 10 Ihre Pkte. 2 15 3 15 4 15 5 15 6 15 7 15 Gesamt 100 Aufgabe 1 Grundlagen (10 Punkte) a) Erläutern Sie kurz (3 Argumente), warum Datenbanksysteme besser geeignet sind, große Datenmengen zu speichern als Dateisysteme b) Benennen Sie die Ebenen der Datenbankarchitektur und erläutern Sie kurz (max. 1 Satz), wozu jede Ebene dient. Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 2 B-Baum (15 Punkte) Gegeben sei der folgende B-Baum der Ordnung n = 2: 27 8 19 2 3 4 6 10 12 14 17 33 42 21 22 23 25 28 31 35 39 45 49 a) Zeichnen Sie den B-Baum nach Löschen des Schlüssels 31 auf. b) Fügen Sie in den B-Baum folgenden Schlüssel ein: 18 und zeichnen Sie anschließend den entstehenden Baum auf. Zwei Zusatzpunkte können Sie erwerben, wenn Sie in b) den Ergebnisbaum aus a) verwenden. Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 3 Datenmodellierung (15 Punkte) Gegeben sei die folgende informelle Beschreibung: Ein Material zeichnet sich durch die Attribute Materialnummer, Name und Gewicht aus. Materialien können aus mehreren Materialien bestehen oder auch nicht. Materialien können Bestandteil von mehreren Materialien sein oder auch nicht. Für Bestandteile wird die Anzahl gespeichert. Es gibt Metalle und Holz als spezielle Materialien. Es gibt noch weitere spezielle Materialien, die jedoch (noch) nicht im Modell aufgenommen sind. Metalle haben als zusätzliche Eigenschaften Biegefähigkeit, Holz hat zusätzlich die Dichte. Es gibt auch gemischte Holz-/ Metall-Materialien. Entwickeln Sie zu der oben genannten Beschreibung ein Entity-Relationship-Modell mit Entitäts-Typen, Relationship-Typen, Attributen und Kardinalitäten in Tupelschreibweise. Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 4 SQL (15 Punkte) In einer relationalen Datenbank seien die folgenden Tabellen gegeben: TIER: TNR 1 2 3 4 5 TNAME Benno Hasso Karla Frieda Chita FUTTERMITTEL: FNR 1 2 3 4 ART Orang-Utah Zebra Gorilla Giraffe Schimpanse FNAME Banane Apfel Hafer Gerste ALTER 9 8 15 12 7 PREIS 2,50 2,00 2,20 1,80 TAGESRATION: TNR 1 1 2 3 5 3 FNR 1 2 3 1 1 2 Menge 10 5 3 20 8 7 VORRAT 200 500 100 150 Für die folgenden Teilaufgaben ist jeweils der passende SQL-Befehl zu formulieren und die Bildschirmausgabe anzugeben. a) Gib das mittlere Alter aller Tiere an, die Bananen fressen. SQL-Ausdruck: Ergebnis: b) Gib die Nummern und Namen der Tiere aus, die wenigstens 10 Bananen fressen und älter als12 Jahre sind. SQL-Ausdruck: Ergebnis: Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 5 Relationenalgebra (15 Punkte) Für die folgende Aufgabe gelten die Tabellen aus Aufgabe 3. a) Formulieren Sie den passenden Ausdruck der Relationenalgebra und geben Sie das Ergebnis an zu der folgenden Anfrage: Gib die Nummer und Namen der Futtermittel aus, die von Gorillas gefressen werden und deren Preis größer als 2 ist. Ausdruck der Relationenalgebra: Ergebnis: b) Wandeln Sie den folgenden SQL-Befehl in einen äquivalenten Ausdruck der Relationenalgebra um und geben Sie das Ergebnis an zu der folgenden Anfrage: select FNAME, PREIS from FUTTERMITTEL where FNR in (select FNR from TAGESRATION where ANZAHL < 6 and TNR in (select TNR from TIER where ALTER < 10 ) ) Ausdruck der Relationenalgebra: Ergebnis: Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 6 ER-Modell -> Relationales Modell (15 Punkte) Ein Land besitzt die Attribute LNR (Primärschlüssel), LNAME und EINWOHNERZAHL. Ein Produkt besitzt die Attribute PNR (Primärschlüssel), PNAME und GEWICHT. Länder exportieren verschiedene Produkte, mindestens aber eines. Ein Produkt wird entweder von keinem oder mehreren Ländern exportiert. Jeder Export findet über einen bestimmten Zeitraum statt. a) Modellieren Sie den o.a. Sachverhalt in einem ER-Diagramm mit Kardinalitäten in TupelSchreibweise. b) Wandeln Sie das ER-Diagramm in ein Relationenmodell um, indem Sie die „create table“-Befehle (inkl. Schlüssel- und Fremdschlüssel-Definition) aufzeichnen. Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 7 Normalisierung (15 Punkte) In dem Relationenschema LANDWIRTSCHAFT (LW) kommen folgende Attribute vor: Bauernnummer (B), Kuhnummer (K), LiterMilch (L), Hektarbesitz (H), PreisFürMilch (P). Zu jeder Bauernnummer (B) gibt es genau einen eindeutigen Hektarbesitz (H), zu jeder Kuhnummer gibt es genau eine LiterMilch (L) - Produktion und zu jeder LiterMilch (L) Produktion gibt es einen eindeutigen PreisFürMilch (P). a) Schreiben Sie das o.a. Relationenschema so mit den Funktionalen Abhängigkeiten auf, dass die in Klammern stehenden Buchstaben für die Attribute stehen. b) Geben Sie den Schlüssel des Relationenschemas an. c) Begründen Sie, weshalb das o.a. Relationenschema nicht in 2. Normalform ist. d) Begründen Sie, weshalb das o.a. Relationenschema nicht in 2. Normalform ist. e) Zerlegen Sie das Relationenschema so, dass im Ergebnis nur Relationenschemata in 3. Normalform vorkommen. Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 8 Datenschutz und Datensicherheit (10 Punkte) a) Erläutern Sie die ACID-Eigenschaften von Datenbanken A C I D b) Erläutern Sie kurz den Begriff Uncommited Dependency Problem Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 9 Views (15 Punkte) a) Gegeben sei folgende Relation: ANGESTELLTER: ANR 1 2 3 4 NAME Maier Müller Schulze Kirst ORT Wiesbaden Darmstadt Kirst Frankfurt GEHALT 4444 5555 3333 2222 und folgende View-Definition create view V1 as select ANR, NAME, ORT from ANGESTELLTER where GEHALT <any (select GEHALT from ANGESTELLTER) -- any bedeutet “irgend ein” Was liefert die folgende Anfrage: select NAME from V1 where ORT = ´Darmstadt´; b) In einer Datenbank gibt es folgende Relationenschemata: PFERDE: STÄLLE: PNR ZÜCHTERNR NAME ALTER ZÜCHTERNR VATERNR NAME PLZ ORT STRASSE Zu beachten: Die VATERNR in dem Schema PFERDE gibt eine Nummer an, zu der es ein „passendes“ Pferd geben muss. Beantworten Sie folgende Fragen mit SQL: b1) Welche dreijährigen Pferde (auszugeben ist nur der NAME) sind in den Ställen von Frankfurter Züchtern untergebracht? b2) Welche Züchter (auszugeben ist nur der NAME des STALLES) haben in ihren Ställen mindestens 1 Kind von dem VATER mit Namen ´Hermes´? Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 10 PL/SQL – Interpretation (15 Punkte) Gegeben sei die folgende Relation mit den nachfolgenden Inhalten: SNR 1 3 16 21 13 51 55 69 60 59 SNAME Lehmann Friedrich Lahm Metzelder Ballack Buffon Canavaro Zambrotta Totti Toni SALTER 36 27 22 25 29 28 32 29 29 29 SGEHALT 3000 1500 2000 1800 5000 3500 2000 2500 3200 2700 Interpretieren Sie das folgenden PL/SQL-Programm, indem Sie a) in einem Satz beschreiben, was dieses Programm tut: b) die Bildschirmausgabe aufschreiben declare cursor c1 is select sname, salter, sgehalt, mname from spieler; declare cursor c1 is select sname, salter, sgehalt, mname from spieler; Ergebnis a) s_N s_A s_G s_MN spieler.sname%type; spieler.salter%type; spieler.sgehalt%type; spieler.mname%type; ALT GEH ANZ integer; integer; integer; begin open c1; fetch c1 into s_N, s_A, s_G, s_MN; ALT := 0; ANZ := 0; while c1%found loop if s_MN = 'Deutschland' then ALT := ALT + s_A; ANZ := ANZ + 1; dbms_output.put_line('ALT = '||ALT||' ANZ = '||ANZ); Ergebnis b) end if; fetch c1 into s_N, s_A, s_G, s_MN; end loop; close c1; dbms_output.put_line('Erg = '||ALT/ANZ); end; / MNAME Deutschland Deutschland Deutschland Deutschland Deutschland Italien Italien Italien Italien Italien Prof. Dr. Stephan Karczewski Beispielaufgaben Datenbanken Name:.............................................................. Matrikelnummer:............................................ Aufgabe 11 PL/SQL – Eigenentwicklung (15 Punkte) Die folgende Aufgabe bezieht sich auf das Beispiel aus Aufgabe 4. Formulieren Sie ein PL/SQL-Programm, das als Ausgabe die Differenz der Durchnittsgehälter deutscher und italienischer Spieler ausgibt und geben Sie das entsprechende Ergebnis an: