Inhaltsverzeichnis
Verbergen
Alte Klausur (Lösung)
Aufgabe 1
Die Datenbasis einer relationalen Datenbank zur Verwaltung des innerbetrieblichen Ablaufs (Personal, Abteilungen, Gehälter, Personalstruktur, Auftragsabwicklung, …) wird durch folgende Relationen festgelegt. Diese Miniwelt ist nur ein unvollkommener Ausschnitt des wirklichen Geschäftsablaufes.
Hinweis: Die Primär- und Fremdschlüssel sind nur z. T. festgelegt. Bei den Fremdschlüsseln ist festgelegt auf welche Tabelle sie sich beziehen.
personal (pnr, nachname, vorname, geschlecht, eintrittsdatum, grundgehalt, abtnr, vorgesetzter)
monatsgehälter (↑pnr (aus personal), monat, jahr, mgehalt)
abteilung (abtnr, abtname, ↑abtleiter (pnr aus personal))
auftrag (auftragsnr, auftragswert, abrechnungsdatum, bezahlt)
tätigkeitsposten (pnr, auftragsnr, tätigkeitsnr, tätigkeitsstunden)
tätigkeitsarten (tätigkeitsnr, tätigkeitsbez, tätigkeitspreis, prämienbetrag)
Es folgen noch ein paar Erläuterungen zu den Geschäftsregeln:
- Vorgesetzte gehören natürlich auch zum Personal.
- Wenn eine Abteilung gelöscht wird, so werden die Mitarbeiter standardmäßig erst mal kei-ner Abteilung zugeordnet.
- Wird ein Vorgesetzter gelöscht, so werden seine Mitarbeiter standardmäßig erst mal keinem Vorgesetzten zugeordnet.
- Zu dem normalen Grundgehalt kommen für jeden Mitarbeiter im Monat noch Prämien, die sich aus dem Prämienbetrag der monatlich wechselnden Tätigkeiten und den Tätigkeitsstunden berechnet. Das Gesamtgehalt wird im Attribut mgehalt gespeichert.
- Mitarbeiter können an einem Auftrag gleichzeitig mehrere Tätigkeiten ausüben.
- Der Abrechnungswert eines Auftrags berechnet sich aus den zum Auftrag gehörigen Tätigkeiten und den daraus entstehenden Kosten.
Es folgen ein paar allgemeine Fragen zu dieser Datenbasis.
- Geben Sie die in der Relation Personal auftretenden Fremdschlüssel und ihren Bezug an.
- Definieren Sie einen geeigneten Primärschlüssel für die Relation Monatsgehälter.
- Definieren Sie einen geeigneten Primärschlüssel für die Relation Tätigkeitsposten und geben Sie die Fremdschlüssel und ihren Bezug an.
- Geben Sie Attribute der Relation Personal an, die Null-Werte enthalten können. Welche Bedeutung haben sie dann jeweils?
- Im Zusammenhang mit Null-Werten kommt der Begriff der dreiwertigen Logik vor. Erstellen Sie eine Relationstafel für den logischen Term A AND NOT B für alle denkbaren Wertbelegungen für A und B in dreiwertiger Logik.
Im folgenden Teil geht es rund um SQL.
- Wenn man mit Datenbanken arbeitet „stolpert“ man ständig über die Akronyme SQL, DDL, DML und DQL. Definieren Sie diese Akronyme.
- SQL und Java sind zwei Programmiersprachen. Beschreiben Sie die Unterschiede zwischen den Klassen der Sprachen, der sie angehören. Erläutern Sie allgemein an einem Beispiel.
- Definieren Sie in der Datenbanksprache SQL die Relationen Personal und Monatsgehälter. Beachten Sie dabei auch die referentielle Integrität mit entsprechenden Aktionsregeln und die Überprüfung der Domain-Integrität für das Geschlecht der Person.
- Der Mitarbeiter „Herbert Müller“ wird am 1.6.2006 mit einem Grundgehalt von 2800 € eingestellt. Im Moment wird er noch keiner Abteilung und keinem Vorgesetzten zugeordnet. Die Personalnummer vergibt das System natürlich automatisch. Implementieren Sie eine entsprechende SQL-Anweisung.
- Was bewirkt die Anweisung
DELETE FROM Personal WHERE nachname=’Meier‘?
Diskutieren Sie auch die Konsequenzen für die anderen Relationen. Beschreiben Sie Möglichkeiten auf diese Konsequenzen zu reagieren. - Da es der Firma sehr schlecht geht, haben die Mitarbeiter einer 2-prozentigen Grundgehaltskürzung zugestimmt. Die Datenbasis muss entsprechend aktualisiert werden. Implementieren Sie einen entsprechenden SQL-Befehl.
- In SQL formulierte Abfragen (SELECT) liefern Projektionen, Selektionen, Gruppierungen usw. die sich auf Relationen beziehen. Geben Sie die vollständige (Grund-)Struktur einer in SQL formulierten Abfrage mit allen optionalen Klauseln (Setzen Sie diese bitte in eckigen Klammern.) an.
- Implementieren Sie eine Abfrage, die alle Namen, Vornamen und Grundgehälter der Mitarbeiter alphabetisch sortiert ausgibt, deren Nachname mit „M“ beginnt und deren Grundgehalt zwischen 2500 € und 3500 € liegt.
- Es sollen alle Mitarbeiter mit allen Daten der Relation Personal ausgegeben werden, die keinen Vorgesetzten haben.
- Es sollen die Nachnamen und Vornamen aller Mitarbeiter mit ihren Vorgesetzten ausgegeben werden. Es gibt Abfragemöglichkeiten mit unterschiedlichen Ergebnissen, je nachdem, wie man das Wort „mit“ interpretiert. Implementieren Sie die Abfragen und erläutern Sie diese.
- Bestimmen Sie die Anzahl aller Mitarbeiter mit der virtuellen Spalte Mitarbeiteranzahl und das durchschnittliche Grundgehalt mit der virtuellen Spalte Durchschnittsgehalt aller Mitarbeiter der Firma.
- Für alle Aufträge soll jeweils die Auftragsnummer und alle für den Auftrag benötigten Arbeitsstunden geordnet nach der Zahl der Arbeitsstunden ausgegeben werden, beginnend mit der größten Zahl.
- Schränken Sie die Ausgabe für die Abfrage 17 so ein, dass man nur die Aufträge auflistet, für die mehr als 500 Arbeitsstunden geleistet worden sind.
- Es soll für den Auftrag mit der Nummer 27 der gesamte Auftragswert unter dem Namen Gesamtkosten, der sich aus den Tätigkeitsstunden und dem Tätigkeitslohn berechnen lässt, ermittelt werden.
- Für den Auftrag mit der Nummer 27 soll nach Beendigung, dies ist der 2.6.2006, der Auftragswert berechnet und in der entsprechenden Relation gespeichert werden.
- Es soll eine neue Datensicht unter dem Namen „Tätigkeiten“ erstellt werden, die von jedem Mitarbeiter den Nachnamen, Vornamen und alle seine ausgeübten Tätigkeiten, d. h. ihre Bezeichnungen ausgibt. Dopplungen der Tätigkeitsbezeichnungen sollen nicht aufgeführt werden.
- Gesucht sind alle Daten der Mitarbeiter, die nicht an einem Auftrag arbeiten.
Aufgabe 2
Auch bei der Fußball-Weltmeisterschaft in Deutschland benötigt man eine Datenbank wm2006 zum Erfassen der Spiele. Dabei sei das folgende Relationenmodell gegeben. mid, mid1 und mid2 verwenden den gleichen Wertebereich.
mannschaft (mid, land, trainer, gruppe)
nimmtTeil (sname, bnr, bemerkung)
spieler (snr, sname, gebjahr, ↑mid)
segegnung (bnr, tag, ↑ortid, ↑mid1, ↑mid2, ergebnis, schiedsrichter)
orte (ortid, spielort, stadionname, kapazität)
- Wo könnten Sie sich in dem Beispiel zu Beginn des Turniers Nullwerte vorstellen, weil Werte nicht zutreffend oder noch unbekannt sind?
- Beschreiben Sie den Aufbau und die inhaltliche Bedeutung der folgenden Abfrage.
SELECT schiedsrichter
FROM Begegnung, Mannschaft
WHERE (mid=mid1 OR mid=mid2)
AND trainer='Klinsmann';
- Implementieren Sie eine SQL-Abfrage, die alle Mannschafts-IDs und Ländernamen der Mannschaften liefert, die in Gruppe „E“ spielten.
- Implementieren Sie eine SQL-Abfrage, die alle Spielernamen (SName) der deutschen Mannschaft (mid=GER) liefert mit der Anzahl der Begegnungen, in denen sie spielten, z. B. 7 bei Lehmann, 5 bei Podolski usw..
- Beschreiben Sie den Aufbau und die inhaltliche Bedeutung der folgenden Abfrage.
SELECT a.bnr, a.tag, a.mid1, a.mid2
FROM begegnung AS a, begegnung b
WHERE (a.mid1=b.mid1 AND a.mid2=b.mid2 AND a.bnr<>b.bnr)
OR (a.mid1=b.mid2 AND a.mid2=b.mid1 AND a.bnr<>b.bnr);
- Implementieren Sie eine Datensicht TagOrt, die alle Spieltage (Tag aus Begegnung) und die zugehörigen Spielorte (Spielort aus Orte) angibt.
- Egal wie es ausgeht: Wir alle wissen, dass es nur einen Rudi Völler geben kann!!! Beschreiben Sie, wie doppelte Einträge in der Spalte Trainer in der Relation Mannschaft verhindert werden können.
- Legen Sie einen Benutzer organisator an, der volle Benutzerrechte für die gesamte Datenbank wm2006 erhalten soll.
- Legen Sie einen Benutzer journalist an, der nach Anmeldung mit einem Passwort Einsicht in die Datenbank erhält, allerdings lediglich Abfragen (SELECT) stellen darf.
- Die Trainer aller Mannschaften müssen vor einem Spiel ihre Mannschaftsaufstellungen eingeben. Dafür benötigen Sie die Möglichkeit an die gesamte Datenbank Abfragen (SELECT) zu stellen und Datensätze an die Tabelle nimmtTeil anzuhängen. Generieren Sie dafür einen geeigneten Benutzer.
- Ein Schiedsrichter soll nach Beendigung einer Begegnung das Ergebnis eintragen können, aber keinerlei weitere Rechte erhalten. Legen Sie einen geeigneten Benutzer an.
- Am Ende des Turniers sollen die Schiedsrichter aus der User-Datenbank gelöscht werden; die Trainer der Mannschaften sollen lediglich weitere Abfragen stellen dürfen.
