Alte Klausur

Aufgabe 1

Es folgen ein paar allgemeine Fragen zu dieser Datenbasis.

  1. Geben Sie die in der Relation Personal auftretenden Fremdschlüssel und ihren Bezug an.
    abtnr (aus Abteilung) und vorgesetzter (pnr aus Personal)
  2. Definieren Sie einen geeigneten Primärschlüssel für die Relation Monatsgehälter.
    Da die Monatsgehälter monatlich wechseln können, muss die Attributkombination pnr (aus Personal), monat, jahr als Primärschlüssel festgelegt werden.
  3. Definieren Sie einen geeigneten Primärschlüssel für die Relation Tätigkeitsposten und geben Sie die Fremdschlüssel und ihren Bezug an.
    Primärschlüssel muss die Kombination pnr, auftragsnr, tätigkeitsnr sein, da nur sie eindeutig die Stundenzahl festlegt. Alle drei Attribute sind Fremdschlüssel aus den Tabellen Personal, Auftrag und Tätigkeitsart.
  4. Geben Sie Attribute der Relation Personal an, die Null-Werte enthalten können. Welche Bedeutung haben sie dann jeweils?
    In der Relation Personal können in den Attributen abtnr und vorgesetzter laut Geschäftsregeln NULL-Werte vorhanden sein. Sie geben dann jeweils an, dass die Informationen noch nicht bekannt sind, welcher Abteilung bzw. welchem Vorgesetzten der Mitarbeiter zugeordnet wird, oder dass der Mitarbeiter keinen Vorgesetzten hat, bzw. keiner Abteilung zugeordnet wird.
  5. 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.
TrueUnknownFalseB
ANDFalseUnknownTrueNOT B
TrueFalseUnknownTrue
UnknownFalseUnknownUnknown
FalseFalseFalseFalse
A

Im folgenden Teil geht es rund um SQL.

  1. Wenn man mit Datenbanken arbeitet “stolpert” man ständig über die Akronyme SQL, DDL, DML und DQL. Definieren Sie diese Akronyme.
    SQL – Structured Query Language ( Oberbegriff der anderen drei Begriffe)
    DDL – Data Definition Language (Aufbau einer Datenbank)
    DML – Data Manipulation Language (einfügen, ändern und löschen von Daten)
    DQL – Data Query Language (Abfragen)
  2. 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.
    Die gewünschten Daten werden unter SQL (ebenso Prolog, …) durch sog. Prädikate (logische Bedingungen) charakterisiert. SQL untersucht die definierten Bedingungen und legt dann fest, wie die gewünschten Daten gefunden werden. Solche nicht prozeduralen Sprachen nennt man auch deklarative Sprachen. Für den Anwender sind nur die logischen Bedingungen, nicht der eigentlichen “Weg” zu den Daten interessant.
    In prozeduralen, imperativen Sprachen wie Java (Pascal, Basic, …) muss der “Weg”, d. h. der Algorithmus zu den gewünschten Daten beschrieben werden.
    Beispiele sind zu beschreiben …
  3. 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.
CREATE TABLE personal (
  pnr integer NOT NULL PRIMARY KEY,
  nachname varchar(30) NOT NULL,
  vorname varchar(20) NOT NULL,
  geschlecht ENUM (’m’,’w’),
  eintrittsdatum date NOT NULL,
  grundgehalt real,
  abtnr integer
    FOREIGN KEY REFERENCES abteilung ON DELETE SET NULL DEFAULT NULL,
  vorgesetzter integer
    FOREIGN KEY REFERENCES personal (pnr) ON DELETE SET NULL DEFAULT NULL
);

CREATE TABLE monatsgehaelter (
  pnr integer
    FOREIGN KEY REFERENCES Personal ON DELETE CASCADE,
  monat integer NOT NULL,
  jahr integer NOT NULL,
  mgehalt real,
  PRIMARY KEY (pnr,monat,jahr)
);
  1. 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.
INSERT INTO personal
  (nachname, vorname, geschlecht, eintrittsdatum, grundgehalt)
  VALUES
  ('Müller', 'Herbert', ’m’, '1.6.2006', 2800);
  1. 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.
    Alle Mitarbeiter mit dem Nachnamen “Meier” werden gelöscht. Dies hat Konsequenzen für alle Tabellen in denen “Meier” als Fremdschlüssel auftritt. Durch Festlegung der Aktionen bei der Löschung können mit Hilfe der Überprüfung der referentiellen Integrität die Detaildatensätze mit den Optionen restricted, cascade oder set … behandelt werden.
  2. 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.
UPDATE personal
  SET grundgehalt = grundgehalt * 0.98;
  1. 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.
    SELECT [DISTINCT] {spalten}
    FROM tabelle [alias tabelle|Joins]
    [WHERE {bedingung|unterabfrage}]
    [GROUP BY spalten]
    [HAVING {bedingung|unterabfrage}]
    [ORDER BY spalten [ASC|DESC]]
  2. 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.
SELECT nachname, vorname, grundgehalt
  FROM personal
  WHERE nachname LIKE 'M%' 
    AND grundgehalt BETWEEN 2500 AND 3500
  ORDER BY nachname, vorname;
  1. Es sollen alle Mitarbeiter mit allen Daten der Relation Personal ausgegeben werden, die keinen Vorgesetzten haben.
SELECT *
  FROM personal
  WHERE vorgesetzter IS NULL;

oder

SELECT *
  FROM personal
  WHERE ISNULL(vorgesetzter);
  1. 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.
// Es werden nur die Mitarbeiter mit ihrem Vorgesetzten ausgegeben.
SELECT p.nachname, p.vorname, pv.nachname, pv.vorname
  FROM personal AS p INNER JOIN personal AS pv
    ON p.vorgesetzter = pv.pnr;

// Es werden alle Mitarbeiter, auch die ohne Vorgesetzten, ausgegeben.
SELECT p.nachname, p.vorname, pv.nachname, pv.vorname 
  FROM personal AS p LEFT [OUTER] JOIN personal AS pv
    ON p.vorgesetzter = pv.pnr;
  1. Bestimmen Sie die Anzahl aller Mitarbeiter mit der virtuellen Spalte Mitarbeiteranzahl und das durchschnittliche Grundgehalt mit der virtuellen Spalte Durchschnittsgehalt aller Mitarbeiter der Firma.
SELECT COUNT(*) AS Mitarbeiterzahl, AVG(grundgehalt) AS Durchschnittsgehalt
  FROM personal;
  1. 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.
SELECT auftragsnr, SUM(taetigkeitsstunden) AS Gesamtstunden
  FROM taetigkeitsposten
  GROUP BY auftragsnr
  ORDER BY Gesamtstunden DESC;
  1. 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.
SELECT auftragsnr, SUM(taetigkeitsstunden) AS Gesamtstunden
  FROM taetigkeitsposten
  GROUP BY auftragsnr
  HAVING Gesamtstunden > 500
  ORDER BY Gesamtstunden;
  1. 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.
SELECT auftragsnr, SUM(tp.taetigkeitsstunden*ta.taetigkeitspreis) AS Gesamtkosten
  FROM taetigkeitsposten AS tp INNER JOIN taetigkeitsarten AS ta
    ON tp.taetigkeitsnr = ta.taetigkeitsnr
  GROUP BY auftragsnr
  HAVING auftragsnr = 27;
  1. 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.
UPDATE auftrag
  SET abrechnungsdatum = '2.6.2006', auftragswert = Gesamtpreis
  WHERE auftragsnr = 27
    AND Gesamtpreis =
         (SELECT SUM(tp.taetigkeitsstunden*ta.taetigkeitspreis)
            FROM taetigkeitsposten AS tp INNER JOIN taetigkeitsarten AS ta
              ON tp.taetigkeitsnr = ta.taetigkeitsnr
            GROUP BY auftragsnr
            HAVING auftragsnr = 27);
  1. 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.
CREATE VIEW taetigkeiten AS
  SELECT DISTINCT nachname, vorname, ta.taetigkeitsbez
    FROM personal AS p NATURAL JOIN taetigkeitsposten
                       NATURAL JOIN taetigkeitsarten AS ta;
  1. Gesucht sind alle Daten der Mitarbeiter, die nicht an einem Auftrag arbeiten.
SELECT *
  FROM personal
  WHERE NOT EXISTS 
              (SELECT *
                 FROM taetigkeitsposten
                 WHERE personal.pnr = taetigkeitsposten.pnr);

oder

SELECT *
  FROM personal
  WHERE NOT pnr IN
               (SELECT pnr
                  FROM taetigkeitsposten);

Aufgabe 2

  1. Wo könnten Sie sich in dem Beispiel zu Beginn des Turniers Nullwerte vorstellen, weil Werte nicht zutreffend oder noch unbekannt sind?
    Zu Beginn des Turniers sind z. B. von den Begegnungen ab dem Achtelfinale zwar die bnr, der Tag und der Spielort bekannt, aber nicht die teilnehmenden Mannschaften, das Ergebnis und der Schiedsrichter.
    Für die Vorrundenspiele sollte das Ergebnis unbekannt sein.
    In der Tabelle nimmtTeil kann das Attribut bemerkung NULL sein, da es durchaus Spieler geben kann, zu denen keine besondere Bemerkung vorliegt.
  2. Beschreiben Sie den Aufbau und die inhaltliche Bedeutung der folgenden Abfrage.
    Es wird ein Join über die Tabellen Begegnung und Mannschaft ausgeführt, zusätzlich werden alle Datensätze selektiert bei denen der Trainer “Klinsmann” heißt. Die Ergebnistabelle wird auf die Spalte Schiedsrichter projiziert.
    Es werden alle Schiedsrichter ausgegeben, die ein Spiel leiten / geleitet haben, bei dem eine Mannschaft mit dem Trainers Klinsmann teilnimmt / teilgenommen hat.
  3. Implementieren Sie eine SQL-Abfrage, die alle Mannschafts-IDs und Ländernamen der Mannschaften liefert, die in Gruppe “E” spielten.
SELECT DISTINCT mid, land
  FROM mannschaft
  WHERE gruppe=E;
  1. 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..
SELECT sname, count(*)
  FROM nimmtTeil NATURAL JOIN spieler
  WHERE mid=GER
  GROUP BY sname;
  1. Beschreiben Sie die inhaltliche Bedeutung der folgenden Abfrage.
    Es werden alle Begegnungen (bnr, Tag und beteiligte Mannschaften) angegeben, bei denen zwei Mannschaften mehr als einmal während des Turniers gegeneinander spielen.
  2. Implementieren Sie eine Datensicht TagOrt, die alle Spieltage (Tag aus Begegnung) und die zugehörigen Spielorte (Spielort aus Orte) angibt.
CREATE VIEW TagOrt AS
  SELECT b.Tag, o.Spielort
    FROM begegnung AS b NATURAL JOIN orte AS o;
  1. 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.
    Das Attribut der Tabelle Mannschaft müsste als UNIQUE definiert werden. (Alternativ ist auch möglich in der Tabelle Mannschaft das Attribut Trainer als Primärschlüssel zu definieren – nicht ganz so schön.)
  2. Legen Sie einen Benutzer organisator an, der volle Benutzerrechte für die gesamte Datenbank wm2006 erhalten soll.
CREATE USER 'organisator';
GRANT ALL
  ON wm2006.*
  TO 'organisator';
  1. Legen Sie einen Benutzer journalist an, der nach Anmeldung mit einem Passwort Einsicht in die Datenbank erhält, allerdings lediglich Abfragen (SELECT) stellen darf.
CREATE USER 'journalist' IDENTIFIED BY 'password';
GRANT SELECT
  ON wm2006.*
  TO 'journalist';   
  1. 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.
CREATE USER 'trainer';
GRANT SELECT
  ON wm2006.*
  TO trainer;
GRANT INSERT
  ON wm2006.nimmtTeil
  TO trainer;
  1. Ein Schiedsrichter soll nach Beendigung einer Begegnung das Ergebnis eintragen können, aber keinerlei weitere Rechte erhalten. Legen Sie einen geeigneten Benutzer an.
CREATE USER 'schiedsrichter';
GRANT UPDATE (ergebnis)
  ON wm2006.begegnung
  TO 'schiedrichter';
  1. 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.
DROP USER 'schiedsrichter';
REVOKE INSERT
  ON wm2006.nimmtTeil
  FROM 'trainer';
Schlagwörter: