Beispielklausur LK

Aus Informatik
Wechseln zu: Navigation, Suche

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.

a) Geben Sie die in der Relation Personal auftretenden Fremdschlüssel und ihren Bezug an.

abtnr (aus Abteilung) und vorgesetzter (pnr aus Personal)

b) 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.

c) 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.

d) In welchem Attribut der Relation Personal können Null-Werte enthalten sein. 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.

e) 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.

True Unknown False B
AND False Unknown True NOT B
True False Unknown True
Unknown False Unknown Unknown
False False False False
A


Im folgenden Teil geht es rund um SQL.

f) Wenn man mit Datenbanken arbeitet "stolpert" man ständig über die Akronyme SQL, DDL, DML und DQL. Wofür stehen diese Abkürzungen?

SQL – Structured Query Language
DDL – Data Definition Language
DML – Data Manipulation Language
DQL – Data Query Language
wobei SQL der Oberbegriff der anderen drei Begriffe ist.

g) SQL und Pascal sind zwei Programmiersprachen. Was unterscheidet sie? 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 Pascal (Basic,...) muss der "Weg", d. h. der Algorithmus zu den gewünschten Daten beschrieben werden.
Beispiele sind zu beschreiben …

h) 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 char(1) NOT NULL CHECK geschlecht IN (’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)
);

i) 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. Formulieren Sie die entsprechende SQL-Anweisung. 

INSERT INTO Personal
  (nachname, vorname, geschlecht, eintrittsdatum, grundgehalt)
  VALUES
  ('Müller', 'Herbert', ’m’, '1.6.2006', 2800);

j) Was bewirkt die Anweisung DELETE FROM Personal WHERE nachname='Meier'? Diskutieren Sie auch die Konsequenzen für die anderen Relationen. Wie kann man dies berücksichtigen?

Alle Mitarbeiter mit dem Nachamen "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.

k) Da es der Firma sehr schlecht geht, haben die Mitarbeiter einer 2-prozentigen Grundgehaltskürzung zugestimmt. Die Datenbasis muss entsprechend aktualisiert werden. Formulieren Sie den entsprechenden SQL-Befehl. 

UPDATE Personal
  SET grundgehalt = grundgehalt * 0,98;

l) 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]]

m) Formuliere 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;

n) 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);

o) 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. Geben Sie die Abfragen an und erläutern Sie diese. 

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 nur die Mitarbeiter mit ihrem 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;
(Es werden alle Mitarbeiter, auch die ohne Vorgesetzten, ausgegeben.)

p) 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;

q) 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;

r) Wie ändert sich die Abfrage, wenn man nur die Aufträge aufgelistet haben will, 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;

s) 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;

t) 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);

u) 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;

v) 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

Auch bei der kommenden 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)
Begegnung (bnr, tag, ortid, mid1, mid2, ergebnis, schiedsrichter)
Orte (ortid, spielort, stadionname, kapazität)

a) 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.

b) Was leistet die folgende SQL-Abfrage? 

SELECT schiedsrichter
  FROM Begegnung, Mannschaft
  WHERE (mid=mid1 OR mid=mid2)
    AND trainer='Klinsmann';

Es werden alle Schiedsrichter ausgegeben, die ein Spiel leiten / geleitet haben, bei dem eine Mannschaft mit dem Trainers Klinsmann teilnimmt / teilgenommen hat.

c) Wie lautet die 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;

d) Wie lautet die 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;

e) Was liefert die folgende SQL-Abfrage für geeignete Tabellen? 

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);

Es werden alle Begegnungen (bnr, Tag und beteiligte Mannschaften) angegeben, bei denen zwei Mannschaften mehr als einmal während des Turniers gegeneinander spielen.

f) Es soll mit SQL eine Datensicht TagOrt geschaffen werden, 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;

g) Egal wie es ausgeht: Wir alle wissen, dass es nur einen Rudi Völler geben kann!!! Wie kann man also doppelte Einträge in der Spalte Trainer in der Relation Mannschaft verhindern? (Beschreibung genügt.)

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.)

h) Legen Sie einen Benutzer organisator an, der volle Beutzerrechte für die gesamte Datenbank wm2006 erhalten soll. 

CREATE USER 'organisator';
GRANT ALL
  ON wm2006.*
  TO 'organisator';

i) 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';

j) 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. Erstellen Sie dafür einen geeigneten Benutzer. 

CREATE USER 'trainer';
GRANT SELECT
  ON wm2006.*
  TO trainer;
GRANT INSERT
  ON wm2006.nimmtTeil
  TO trainer;

k) 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';

l) 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';
Abgerufen von „http://info-wsf.de/index.php?title=Beispielklausur_LK&oldid=8791“