Prüfung Informatik für Ökonomen II 14. Januar 2009 Teil 1
Werbung
Name Vorname Matrikelnummer DB Prüfung Informatik für Ökonomen II 14. Januar 2009 Teil 1: Datenbanktechnik Musterlösungen Bitte freilassen! 1.1 1.2 1.3 Summe Aufgabe 1.1 Gegenstand-Beziehungs-Modell (Total: 11 Punkte) Es soll eine Datenbank aufgebaut werden, welche Informationen über Spieler, Vereine und Nationalverbände verwaltet. Stellen Sie für den konzeptuellen Datenbankentwurf folgende Informationen (und nur diese!) in einem GegenstandsBeziehungs-Diagramm dar: • Zu den Vereinen sind jeweils das Gründungsjahr, die Adresse des Vereins und der Vereinsname angegeben. Mittels Vereinsnamen und Gründungsjahr kann der Verein identifiziert werden. • Jeder Verein muss mindestens 3 Spieler als Mitglieder besitzen, da er ansonsten keine Spieltätigkeit aufnehmen kann. • Jeder Spieler wird durch seine Spieler-Nummer identifiziert. Überdies werden vom Spieler noch sein Geburtsdatum, sein Vorname, sein Nachname und seine Adresse erfasst. Spieler können höchstens einem Verein angehören. • Zudem gibt es Konföderationen. Eine Konföderation besitzt mindestens einen Nationalverband. Nationalverbände können jeweils nur einer Konföderation angehören. • Die Konföderation wird durch ihr internationales Kürzel eindeutig ausgewiesen. Zusätzlich sind der Name und der aktuelle Präsident der Konföderation bekannt. • Jeder Verein gehört einem Nationalverband an. Jedem Nationalverband müssen jedoch mindestens 10 Vereine angehören. Nationalverbände werden über ihren Namen und der Konföderation zu der sie gehören identifiziert. Zu jedem Nationalverband wird zusätzlich wird das Land zudem dieser Nationalverband gehört sowie das Gründungsjahr des Verbandes festgehalten. Hinweise: • Stellen Sie nur Gegenstandstypen, Beziehungen und Attribute dar, die in der Beschreibung vorkommen. • Geben Sie sämtliche Kardinalitäten zu den Beziehungstypen in (min/max)-Notation an. • Verwenden Sie ausschliesslich die in der Vorlesung verwendete Notation. Vorgehensweise: • Modellieren Sie alle Gegenstandstypen, die in der Datenbank vorkommen. • Ergänzen Sie zu jedem Gegenstandstypen dessen Attribute und bestimmen Sie dessen Schlüsselattribute. • Modellieren Sie die Beziehungstypen zwischen den Gegenstandstypen und ergänzen Sie diese um eventuelle Attribute. • Geben Sie sämtliche Kardinalitäten zu den Beziehungstypen in (min/max)-Notation an. DB: Seite 2 (von 10) Musterlösung Nummer GDatum VName Name NName GJahr Verein (3,n) gehört zu (0,1) Spieler Adresse Adresse (1,1) gehört zu Name Land GJahr (10,n) NatVerb Kürzel (1,1) gehört zu (1,n) Konf Name Präs DB: Seite 3 (von 10) Aufgabe 1.2 Relationenschema (Total: 8 Punkte) Überführen Sie folgendes Gegenstands-Beziehungs-Diagramm nach den in der Vorlesung vorgestellten Regeln in ein verfeinertes relationales Schema. Die einzelnen Relationenschemata sind wie folgt anzugeben: • Relationenschema (Attribut 1, ..., Attribut N). Primärschlüssel sind zu unterstreichen. Es werden keine Zwischenschritte verlangt. Korrigiert wird nur das komplette Schema, das Sie am Ende erhalten. Kennzeichnen Sie das Endergebnis deutlich. x1 a1 b1 a2 A (1,n) x (1,n) B b21 a3 (1,n) (0,1) b2 b22 E Relation e1 c1 e2 (1,n) d1 c2 C (1,n) y (1,1) D d2 c3 DB: Seite 4 (von 10) Musterlösung A (a1, a2, a3, c1) B (b1, b21, b22) C (c1, c3) D (d1, c1, d2) E (e1, e2) C2(c1, c2) X (a1, a2, e1, b1, x1) DB: Seite 5 (von 10) Aufgabe 1.3 SQL (Total: 16 Punkte) Gegeben sei folgende Datenbasis (Schema und Ausprägung) der National Basketball Association (NBA) in den USA, über die Angestellten, die Clubs und die Verträge zwischen Angestellten und Clubs. Angestellte AngID Name Position 1 2 3 4 5 6 7 8 o’Neal James Kupchak Ferry Walsh Bryant D’Antoni Fisher Spieler Spieler GM GM GM Spieler Coach Spieler Club ClubID Clubname Division Owner 1 2 3 Los Angeles Lakers Cleveland Cavalliers New York Knicks Pacific Central Atlantic Buss Gilbert Madison Square Garden L.P. Vertrag ClubID AngID Gehalt 1 1 1 2 2 2 3 3 3 6 8 1 2 4 5 7 4000 23000 30000 20000 17000 2000 5000 3000 Hinweise: • Das Attribut ClubID der Relation Vertrag ist ein Fremdschlüssel zu dem Attribut ClubID der Relation Club • Das Attribut AngID der Relation Vertrag ist ein Fremdschlüssel zu dem Attribut AngID der Relation Angestellte DB: Seite 6 (von 10) Frage 1.3.1 (2 Punkte) Verfassen Sie einen SQL-Befehl, der diejenigen Angestellten bestimmt, deren Gehalt über 10000 liegt. Duplikate sollen vermieden werden. Geben Sie nur die Namen der Angestelllten an. Hinweise: • Verwenden Sie für diese Aufgabe NICHT die beispielhafte Ausprägung des Datenbankschemas. Musterlösung SELECT DISTINCT a.Name FROM Angestellte a, Vertrag v WHERE a.AngID = v.AngID AND v.Gehalt > 10000; Frage 1.3.2 (4 Punkte) Welche Resultate liefert die folgende Datenbankabfrage? Geben Sie die Ergebnisrelation entsprechend sortiert und inklusive Schema an. Hinweise: • Verwenden Sie für diese Aufgabe die beispielhafte Ausprägung des Datenbankschemas. SELECT FROM WHERE AND AND AND ORDER BY a.Name, c.Clubname Angestellte a, Club c, Vertrag v v.ClubID = c.ClubID a.AngID = v.AngID a.Position = ’Spieler’ v.Gehalt > 20000; a.Name ASC DB: Seite 7 (von 10) Musterlösung Name Teamname Bryant Fisher Los Angeles Lakers Los Angeles Lakers DB: Seite 8 (von 10) Frage 1.3.3 (5 Punkte) Der Spieler Fisher beendet seine aktive Spielerkarriere. Daraufhin wird sein Vertrag ausbezahlt und gelöscht. Nach den Regeln der NBA verbleibt Fisher aber Angestellter der NBA für die ersten 2 Jahre nach seinem Rücktritt. Nach einem halben Jahr schliesst er einen neuen Vertrag mit den Los Angeles Lakers ab, um dort die Position des Coaches einzunehmen, sein neues Salär beträgt 2000. Formulieren Sie dafür die entsprechendenen SQL-Befehle um die Datensbasis zu aktualisieren. Hinweise: • Verwenden Sie für diese Aufgabe die beispielhafte Ausprägung des Datenbankschemas. Musterlösung DELETE FROM Vertrag WHERE a.AngID = ’8’ INSERT INTO Vertrag(ClubID, AngID, Gehalt) SELECT c.ClubID, a.AngID, 2000 FROM Angestellte a, Club c WHERE a.AngID = ’Fisher’ AND c.ClubID = ’1’ UPDATE Angestellte SET Position = ’Coach’ WHERE AngID = 8; DB: Seite 9 (von 10) Frage 1.3.4 (5 Punkte) Schreiben Sie eine Anfrage, die, nach Gehalt geordnet, die AngID, den Namen, die Position und das Gehalt aller Angestellten anzeigt, die mehr als das durchschnittliche Gehalt verdienen. Hinweise: • Verwenden Sie für diese Aufgabe NICHT die beispielhafte Ausprägung des Datenbankschemas. Musterlösung SELECT FROM WHERE AND AND ORDER BY c.Teamname, a.AngID, a.Name, v.Gehalt Club c, Vertrag v, Angestellte a v.AngID = a.AngID v.ClubID = c.ClubID v.Gehalt > (SELECT AVG(Gehalt) FROM Vertrag) v.Gehalt ODER auch möglich: SELECT FROM WHERE AND AND ORDER BY c.Teamname, a.AngID, a.Name, v.Gehalt Club c, Vertrag v, Angestellte a v.AngID = a.AngID v.ClubID = c.ClubID v.Gehalt > (SELECT AVG(sumGehalt) FROM (SELECT SUM(Gehalt) AS sumGehalt FROM Vertrag GROUP BY AngID) ) v.Gehalt DB: Seite 10 (von 10)
