Blatt 2- DBS (2024-25)

Aufgabe 1: Schlüssel

Stellen Sie sich eine Universitätsdatenbank vor, die Informationen zu Kursangeboten speichert. Die Datenbank enthält unter anderem die Attribute KursID, Kursname und Semester. Diskutieren Sie die Vor- und Nachteile, Kursname und Semester als zusammengesetzten Primärschlüssel zu verwenden, anstatt einen künstlichen, einzigartigen Primärschlüssel wie KursID einzuführen. Unter welchen Umständen wäre jede der beiden Optionen vorteilhaft bzw. überhaupt möglich?

• Ein Primärschlüssel sollte soweit es geht immer minimal sein und eindeutig, wobei mit minimal die Anzahl der Attribute gemeint ist.
• Kursname und Semester als Primärschlüssel zu verwenden, hat den Vorteil, dass beide natürliche Attribute sind, was bedeutet, dass sie Informationen über die Entity aussagen, nicht wie KursID, welches ein künstliches Attribut zum Identifizieren darstellt.
• Die Kombination aus Kursname und Semester läuft jedoch Gefahr, die Datenintegrität und Änderungsanfälligkeit zu verletzen, da sich die Attribute wie Kursname oder Semester, in dem der Kurs stattfindet, ändern können, was dann zu Problemen bei Abfragen und referenziellen Integritäten führen kann.
• KursID bleibt jedoch immer eindeutig, erfordert jedoch ein wenig mehr Verwaltungsaufwand, da es ein Attribut mehr in der Relation ist, welches einzigartig belegt werden muss. Ist jedoch bei Abfragen schneller, da INT-Attribute schneller abfragbar sind als Textattribute.
• Ein einzelner Schlüssel vereinfacht zudem die Referenzierung als Fremdschlüssel, da nur noch ein Attribut referenziert wird, statt 2.

Erklären Sie das Konzept der referenziellen Integrität im Kontext von Fremdschlüsseln anhand einer Universitätsdatenbank. Gegeben sind die Tabellen Einschreibungen (EinschreibungsID, StudentenID, KursID, Semester) und Studenten (StudentenID, Name). Definieren Sie eine Fremdschlüsselbedingung in Einschreibungen, die auf Studenten verweist. Welche potenziellen Probleme könnten auftreten, wenn diese Bedingung nicht eingehalten wird?

• Die Fremdschlüsselbedingung verlangt, dass das durch einen Fremdschlüsselwert referenzierte Tupel in der Datenbank existiert, d. h., dass in der referenzierten Relation ein entsprechender Primärschlüsselwert definiert sein muss (referentielle Integrität).
• Bei Nichteinhaltung können folgende Probleme entstehen:
  • Orphan Records (Dangling References): Einschreibungen könnten auf nicht existierende Studenten verweisen, was zu dangling references führt.
  • Dateninkonsistenzen: Inkonsistente und unvollständige Daten entstehen, da der Fremdschlüssel fehlt.
  • Probleme bei Operationen: Aktionen wie das Löschen eines Studenten könnten problematisch werden, wenn entsprechende Einschreibungen bestehen bleiben oder falsch referenzieren.

$$\text{Einschreibung}{\bowtie }_{\text{StudentenID}}\text{Studenten}$$

oder

$${\sigma }_{\text{StudentenID}=\text{StudentenID}}(\text{Einschreibung}\bowtie \text{Studenten})$$

oder in SQL:

ALTER TABLE Einschreibungen
ADD CONSTRAINT fk_student
FOREIGN KEY (StudentenID)
REFERENCES Studenten(StudentenID);

---

Aufgabe 2: ER-Modellierung

Zur Organisation der nächsten FIFA-Weltmeisterschaft sollen die Daten von Spielern, Mannschaften und Hauptquartieren in einer relationalen Datenbank gespeichert werden. Jeder Spieler spielt für genau eine Mannschaft. Jede Mannschaft bezieht ein eigenes Hauptquartier, in dem sie ungestört wohnen und trainieren kann. Spiele zwischen jeweils zwei Mannschaften finden zu bestimmten Terminen statt. Zur Entwicklung der Datenbank wird das folgende E/R-Diagramm verwendet.

Überführen Sie das E/R-Diagramm in ein relationales Datenbankschema. Bestimmen Sie für jede Relation einen Schlüssel.

Hauptquartier

$$\text{Hauptquartier}(\underline{\text{Quartiername}},\overline{\text{Mannschaft}},\text{Ort})$$

könnte aber auch wie unten sein, wenn Mannschaft stattdessen auf HQ referenziert

$$\text{Hauptquartier}(\underline{\text{Quartiername}},\text{Ort})$$

• Jedes Quartier wird durch seinen Namen identifiziert, unter der Annahme, dass verschiedene Quartiere nicht den gleichen Namen haben können.
• Jedes Quartier

Mannschaft

$$\text{Mannschaft}(\underline{\text{Nation}},\overline{\text{Hauptquartier}},\text{Gruppe})$$

könnte aber auch wie unten sein, wenn HQ stattdessen auf Mannschaft referenziert

$$\text{Mannschaft}(\underline{\text{Nation}},\text{Gruppe})$$

• Jede Mannschaft ist einzigartig durch ihre Nation identifizierbar.
• Jede Mannschaft ist einer Gruppe zugeordnet.

Spieler

SpielerID wäre hier angemessen

Wir können nicht garantieren, dass Name und Geburtstag einzigartig sind

$$\text{Spieler}(\underline{\text{Name}},\underline{\text{Geburtsdatum}},\overline{\text{Mannschaft}},\text{Position})$$

• Name und Geburtsdatum als Primärschlüssel, da diese die Spieler relativ sicher identifizieren. Ich hatte sonst über die unten stehenden Keys noch nachgedacht, Mannschaft als FK und PK zu benutzen statt Geburtsdatum. Ich bin mir aber nicht sicher, ob diese Modellierung den Anforderungen vollständig entspricht, aber ich halte sie für praktikabel.

$$\text{Spieler}(\underline{\text{Name}},\underline{\overline{\text{Mannschaft}}},\text{Geburtsdatum},\text{Position})$$

Spiele

$$\text{Spiele}(\underline{\text{Datum}},\underline{\text{Ort}},\overline{\text{Mannschaft1}},\overline{\text{Mannschaft2}},\text{Ort})$$

• Spiele werden identifiziert durch Datum und Ort, da zur gleichen Zeit und am gleichen Ort nur ein Spiel stattfinden kann.
• Mannschaft1 und Mannschaft2 sind Fremdschlüssel auf die zwei Mannschaften, die spielen.

---

Aufgabe 3: SQL-DDL

Gegeben sei die Datenbank eines Softwareunternehmens, welches spezielle Anwendungen für unterschiedliche Kunden entwickelt. Sie enthält die Relationen Kunde $K$, Team $T$ und Leistung $L$. Zusätzlich existiert eine Relation $KTL$, welche die Aufträge beinhaltet und so die Beziehungen der vorgenannten Relationen modelliert:

• $K(\underline{\text{KNr}},\text{KName},\text{Ansprechpartner})$
• $T(\underline{\text{TNr}},\text{TLeiter},\text{TGro¨ße},\text{Stundensatz})$
• $L(\underline{\text{LNr}},\text{Bezeichnung},\text{Komplexita¨t})$
• $KTL(\underline{\text{KNr},\text{TNr},\text{LNr}},\text{Volumen},\text{Datum})$

Die Schlüssel der jeweiligen Relationen sind Integer-Werte. Das Attribut $TGr\ddot{o}ße$ ist eine positive ganze Zahl. Das Attribut $Komplexit\ddot{a}t$ ist eine ganze Zahl kleiner oder gleich 10. Das Attribut $Stundensatz$ ist eine Festkommazahl mit insgesamt 5 Stellen, davon 2 Nachkommastellen. Das Attribut $Volumen$ ist eine Fließkommazahl. Das Attribut $Datum$ ist ein String der Länge 10. Alle übrigen Attribute sind variable Strings mit maximaler Länge von 50 Zeichen. Die Attribute $KName$, $TLeiter$ und $Bezeichnung$ müssen immer einen Wert enthalten.

a) Geben Sie CREATE TABLE-Befehle mit den dafür notwendigen Constraints zur Definition des o. g. Datenbankschemas an.

CREATE TABLE K (
  KNr INT PRIMARY KEY ,
  KName VARCHAR(50) NOT NULL,
  Ansprechpartner VARCHAR(50)
);
 
CREATE TABLE T (
  TNr INT PRIMARY KEY,
  TLeiter VARCHAR(50) NOT NULL,
  TGrösse INT CHECK(TGrösse > 0),
  Stundensatz DECIMAL(5,2)
);
 
CREATE TABLE L (
  LNr INT PRIMARY KEY ,
  Bezeichnung VARCHAR(50) NOT NULL,
  Komplexität INT CHECK(Komplexität <= 10)
);
 
CREATE TABLE KTL (
  KNr INT,
  TNr INT,
  LNr INT,
  Volumen FLOAT,
  Datum CHAR(10),
 
  PRIMARY KEY (KNr, TNr, LNr),
  FOREIGN KEY (KNr) REFERENCES K(KNr),
  FOREIGN KEY (TNr) REFERENCES T(TNr),
  FOREIGN KEY (LNr) REFERENCES L(LNr)
);

b) Fügen Sie in die Kunden-Relation $K$ eine weitere Spalte $Branche$ als variablen Text der Länge 30 mit dem Default-Wert ‘Automobil’ ein.

ALTER TABLE K
ADD Branche VARCHAR(30) DEFAULT 'Automobil';

c) Löschen Sie die Spalte $TGr\ddot{o}ße$ aus der Team-Relation $T$.

ALTER TABLE T
DROP TGrösse;

d) Ändern Sie den Datentyp des Attributs $Volumen$ aus der Relation $KTL$ in eine ganze Zahl größer als 0.

ALTER TABLE KTL
ALTER COLUMN Volumen TYPE INT;
 
ALTER TABLE KTL
ADD CONSTRAINT chk_volumen_positive CHECK (Volumen > 0);

e) Nun sollen Sie alle Tabellen wieder löschen ohne die referenzielle Integrität zu verletzen.

DROP TABLE KTL;
DROP TABLE K;
DROP TABLE T;
DROP TABLE L;

oder besser:

DROP TABLE KTL CASCADE;
DROP TABLE K CASCADE;
DROP TABLE T CASCADE;
DROP TABLE L CASCADE;

• CASCADE sorgt dafür, dass alle abhängigen Daten (z. B. Einträge in referenzierten Tabellen, Fremdschlüsselbeziehungen) automatisch mitgelöscht werden, wenn eine Tabelle gelöscht wird. Dadurch bleibt die referenzielle Integrität erhalten.

---