# IMPL
###### tags: `fh` `db`
(c) Doiber Sebastian, Schwinger Johannes

# 1 Formen Sie die IST-Beziehungen vom ER-Diagramm in das relationale Schema um. Diskutieren Sie mögliche Varianten.
:::info
individuell gelöst
:::

# 2 Formen Sie die einen Selbstbezug (Vorlesung und Voraussetzung) vom ER-Diagramm in das relationale Schema um. Diskutieren Sie
**Vorlesung**={Bezeichnung, Semesterwochenstundenzahl, Semester, Studiengang, Voraussetzungen} mit

$K_{Vorlesung}$={{Bezeichnung, Semester}(primary)}

---

**Voraussetzung**={V_Bezeichnung, Voraussetzung} mit

$K_{Voraussetzung}$={{V_Bezeichnung, Voraussetzung}(primary)}

**FK:** Vorraussetzung -> Vorlesung(Bezeichnung, Semester),
V_Bezeichnung -> Vorlesung(Bezeichnung, Semester)

Potentielle schwächen: Endlose zyklen / deadlocks.
Sowas sollte immer einen Baum bzw in großer menge einen Wald bilden.

# 3 Formen Sie eine dreistellige n:m-Beziehung vom ER-Diagramm in das relationale Schema um. Diskutieren Sie mögliche Varianten
PROFESSOR={AngestelltenNrm Fachbereich,Raum,Lehrstuhlbezeichnung,Stufe}
mit $K_{Professoren}$={{AngestelltenNr}}
LEHRBUCH={ISBN,Titel,Erscheinungsjahr,Auflage}
mit $K_{Lehrbuch}$={{ISBN}}
VORLESUNG={Bezeichnung, ECTS, Semester, Studiengang}
mit $K_{Vorlesung}$={{Bezeichnung}}

[0,1]
EMPFEHLUNG={AngestelltenNr, ISBN, Bezeichnung}
mit $K_{Bezeichnung}$={{Bezeichnung}}
FK:AbgestelltenNr-> PROFESSOR(AngestelltenNr)not null
FK:ISBN ->LEHRBUCH(ISBN)not null
FK:BEZEICHNUNG -> VORLESUNG(Bezeichnung)

[1,1]
EMPFEHLUNG={AngestelltenNr, ISBN, Bezeichnung, ECTS, Semester, Studiengang}
mit $K_{Bezeichnung}$={{Bezeichnung}}
FK:AbgestelltenNr-> PROFESSOR(AngestelltenNr)not null
FK:ISBN ->LEHRBUCH(ISBN)not null

0...1
K={{AngestelltenNr, Bezeichnung}}
rest ist gleich wie zuvor

# 4 Formen Sie die Beziehung "Buch hat Stichwort" in das relationale Schema um. Diskutieren Sie mögliche Varianten
**Buch**={Inventarnummer, ISBN, Titel, Erscheinungsjahr, Auflage, Stichwörtern}
$K_{Buch}$={{ISBN}(primary), {Stichwort}(unique), {Inventarnummer}(unique, not null)}
**FK**: Stichwörter -> Stichwörter(Stichwörte)

# 5 Implementieren Sie das relationale Schema "Buchexemplar von Buch" mittels SQL.

CREATE TABLE Buch_Exemplare (
  Inventarnr NUMBER(5),
  ISBN VARCHAR2(20)
      CONSTRAINT nn_Buch_Exemplare_ISBN NOT NULL,
  Auflage NUMBER(2)
      CONSTRAINT nn_Buch_Exemplare_Auflage NOT NULL,
  CONSTRAINT pk_Buch_Exemplare PRIMARY KEY (Inventarnr),
  CONSTRAINT fk_Buch_Exemplare_Buch_Vers FOREIGN KEY (ISBN, Auflage) REFERENCES Buch_Versionen (ISBN, Auflage) ON DELETE CASCADE
  );

Besonderes zu beachten ist hier `Zeile 2`, da sie genau "Buchexemplare von Büchern" referenziert.

FOREIGN KEY IST KEIN SCHLÜSSEL! Darf nur mit Werten befüllt werden.

# 6 Implementieren Sie das relationale Schema "Professor hat Lehrstuhl" mittels SQL.

CREATE TABLE Professoren (
  PANr NUMBER(5),
  Lehrstuhlbezeichnung VARCHAR2(50) 
      CONSTRAINT unq_Professoren_Lehrstuhlbez UNIQUE
      CONSTRAINT nn_Professoren_Lehrstuhlbez NOT NULL,
  Stufe VARCHAR2(50) 
      CONSTRAINT nn_Professoren_Stufe NOT NULL 
      CONSTRAINT chk_Professoren_Stufe CHECK (Stufe IN ('C4','C3','C2','C1')),
  CONSTRAINT pk_Professoren PRIMARY KEY (PANr),
  CONSTRAINT fk_Professoren_Mitarbeiter FOREIGN KEY (PANr) REFERENCES Mitarbeiter (PANr) ON DELETE CASCADE,
  CONSTRAINT fk_Professoren_Lehrstuehle FOREIGN KEY (Lehrstuhlbezeichnung) REFERENCES Lehrstuehle (Lehrstuhlbezeichnung)
  );

Zeile 11 ist für dieses Beispiel essenziell, da es die Beziehung "Professor hat Lehrstuhl" darstellt.

# 7 Implementieren Sie das relationale Schema "Vorlesung und Voraussetzung" mittels SQL.

CREATE TABLE Vorl_Voraus (
  V_Bezeichnung VARCHAR2(50),
  Voraussetzung VARCHAR2(50),
  CONSTRAINT pk_Vorl_Voraus PRIMARY KEY (V_Bezeichnung, Voraussetzung),
  CONSTRAINT fk_Vorl_Voraus_Vorlesungen1 FOREIGN KEY (V_Bezeichnung) REFERENCES Vorlesungen (V_Bezeichnung) ON DELETE CASCADE,
  CONSTRAINT fk_Vorl_Voraus_Vorlesungen2 FOREIGN KEY (Voraussetzung) REFERENCES Vorlesungen (V_Bezeichnung) ON DELETE CASCADE
  );

# 8 Implementieren Sie das relationale Schema "Professor IST Mitarbeiter IST Person" mittels SQL.

CREATE TABLE Professoren (
  PANr NUMBER(5),
  Lehrstuhlbezeichnung VARCHAR2(50) 
      CONSTRAINT unq_Professoren_Lehrstuhlbez UNIQUE
      CONSTRAINT nn_Professoren_Lehrstuhlbez NOT NULL,
  Stufe VARCHAR2(50) 
      CONSTRAINT nn_Professoren_Stufe NOT NULL 
      CONSTRAINT chk_Professoren_Stufe CHECK (Stufe IN ('C4','C3','C2','C1')),
  CONSTRAINT pk_Professoren PRIMARY KEY (PANr),
  CONSTRAINT fk_Professoren_Mitarbeiter FOREIGN KEY (PANr) REFERENCES Mitarbeiter (PANr) ON DELETE CASCADE,
  CONSTRAINT fk_Professoren_Lehrstuehle FOREIGN KEY (Lehrstuhlbezeichnung) REFERENCES Lehrstuehle (Lehrstuhlbezeichnung)
  );

`Zeile 10` zeigt die Relation zum Mitarbeiter, welcher im Weiteren wieder mit Person in einer IST-Beziehung steht -> Prof->Mitarbeiter->Person.

---

# 9 Erklären Sie die SQL Anweisungen "drop table" und "alter table".
## drop table
Entfernt Relationenschema aus dem Data Directory als auch die eigentliche Basisrelation aus der Datenbank.
Mit option cascade kann erzwungen werden, dass auch alle Sichten und Integritätsbedingungen, die zu dieser Basisrelation gehören gelöscht werden.

drop table WEINE

## alter table
Änderungen an Spalten werden über die `alter table`-Anweisung eingeleitet.
Zur Änderung des Datenbankschemas ist die Anweisungen `alter table` vorgesehen.

Für Spaltendefinitionen sind folgende Änderungen (modifikation) möglich:
+ add column spaltendefinition fügt eine neue Spalte hinzu, wobei spaltendefinition den in Abschnitt 8.1.1 eingeführten Aufbau aus Name, Typ, optional Defaultwert und Spaltenbedingung hat. Alle bereits in der Tabelle existierenden Tupel erhalten als Wert der neuen Spalte den angegebenen Defaultwert bzw. den null-Wert, wenn kein Defaultwert angegeben ist.
+ drop column spaltenname löscht die angegebene Spalte. Wie beim Löschen von Tabellen sind auch hier wieder die restrict- bzw. cascade-Modi möglich.
+ alter column spaltenname set default defaultwert erlaubt des Verändern des Defaultwertes der Spalte

alter table WEINE
    add column Preis decimal(5,2)