\documentclass{bschlangaul-aufgabe}
\bLadePakete{rmodell,syntax,relationale-algebra}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 2},
  Thematik = {Medikamente},
  Referenz = 66116-2019-F.T1-TA1-A2,
  RelativerPfad = Examen/66116/2019/03/Thema-1/Teilaufgabe-1/Aufgabe-2.tex,
  ZitatSchluessel = examen:66116:2019:03,
  BearbeitungsStand = mit Lösung,
  Korrektheit = unbekannt,
  Ueberprueft = {unbekannt},
  Stichwoerter = {SQL, SQL mit Übungsdatenbank, Relationale Algebra},
  EinzelpruefungsNr = 66116,
  Jahr = 2019,
  Monat = 03,
  ThemaNr = 1,
  TeilaufgabeNr = 1,
  AufgabeNr = 2,
}

\let\a=\bAttribut
\let\f=\bFremd
\let\p=\bPrimaer
\let\r=\bRelationMenge

Gegeben sei der folgende Ausschnitt des Schemas für die Verwaltung der
Einnahme von Medikamenten:
\index{SQL}
\footcite{examen:66116:2019:03}

\begin{bRelationenModell}
\r{Person}{
  ID : INTEGER,
  Name : VARCHAR(255),
  Wohnort : VARCHAR(255)
}

\r{Hersteller}{
  ID : INTEGER,
  Name : VARCHAR(255),
  Standort : VARCHAR(255),
  AnzahlMitarbeiter : INTEGER
}

\r{Medikament}{
  ID : INTEGER
  Name : VARCHAR(255),
  Kosten : INTEGER,
  Wirkstoff : VARCHAR(255),
  produziert\_von : INTEGER
}

\r{nimmt}{
  Person : INTEGER,
  Medikament : INTEGER,
  von : DATE,
  bis : DATE
}

\r{hat\_Unverträglichkeit\_gegen}{
  Person : INTEGER,
  Medikament : INTEGER
}
\end{bRelationenModell}

% Datenbankname: medikamente
\begin{minted}{sql}
CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  Name VARCHAR(255),
  Wohnort VARCHAR(255)
);

CREATE TABLE Hersteller (
  ID INTEGER PRIMARY KEY,
  Name VARCHAR(255),
  Standort VARCHAR(255),
  AnzahlMitarbeiter INTEGER
);

CREATE TABLE Medikament (
  ID INTEGER PRIMARY KEY,
  Name VARCHAR(255),
  Kosten INTEGER,
  Wirkstoff VARCHAR(255),
  produziert_von INTEGER REFERENCES Hersteller(ID)
);

CREATE TABLE nimmt (
  Person INTEGER,
  Medikament INTEGER,
  von DATE,
  bis DATE,
  PRIMARY KEY (Person, Medikament, von, bis)
);

CREATE TABLE hat_Unverträglichkeit_gegen (
  Person INTEGER REFERENCES Person(ID),
  Medikament INTEGER REFERENCES Medikament(ID)
);

INSERT INTO Person VALUES
  (1, 'Walter Müller', 'Holzapfelkreuth'),
  (2, 'Dilara Yildiz', 'Fürth');

INSERT INTO Hersteller VALUES
  (1, 'Hexal', 'Holzkirchen', 3700),
  (2, 'Ratiopharm', 'Ulm', 563);

INSERT INTO Medikament VALUES
  (1, 'IbuHexal', 3, 'Ibuprofen', 1),
  (2, 'Ratio-Paracetamol', 2, 'Paracetamol', 2),
  (3, 'BudeHexal', 4, 'Budesonid', 1),
  (4, 'Ratio-Budesonid', 5, 'Budesonid', 2);

INSERT INTO nimmt VALUES
  (1, 1, '2021-07-12', '2021-07-23'),
  (1, 3, '2021-07-12', '2021-07-23'),
  (2, 4, '2021-02-13', '2021-03-24');

INSERT INTO hat_Unverträglichkeit_gegen VALUES
  (1, 1),
  (1, 3),
  (2, 2);
\end{minted}
\index{SQL mit Übungsdatenbank}

Die Tabelle \a{Person} beschreibt Personen über eine eindeutige ID,
deren Namen und Wohnort. Die Tabelle \a{Medikament} enthält
Informationen über Medikamente, unter anderem deren Namen, Kosten,
Wirkstoffe und einen Verweis auf den Hersteller dieses Medikaments. Die
Tabelle \a{Hersteller} verwaltet verschiedene Hersteller von
Medikamenten. Die Tabelle \a{hat\_Unverträglichkeit\_gegen} speichert
die IDs von Personen zusammen mit den IDs von Medikamenten, gegen die
diese Person eine Unverträglichkeit hat. Die Tabelle \a{nimmt} hingegen
verwaltet die Einnahme der verschiedenen Medikamente und speichert zudem
in welchem Zeitraum eine Person welches Medikament genommen hat bzw.
nimmt.

Beachten Sie bei der Formulierung der SQL-Anweisungen, dass die
Ergebnisrelationen keine Duplikate enthalten dürfen. Sie dürfen
geeignete Views definieren.
\begin{enumerate}

%%
% a)
%%

\item Schreiben Sie SQL-Anweisungen, die für die bereits existierende
Tabelle \a{nimmt} alle benötigten Fremdschlüsselconstraints anlegt.
Erläutern Sie kurz, warum die Spalten \a{von} und \a{bis} Teil des
Primärschlüssels sind.

\begin{bAntwort}
\begin{minted}{sql}
ALTER TABLE nimmt
ADD CONSTRAINT FK_Person
  FOREIGN KEY (Person) REFERENCES Person(ID),
ADD CONSTRAINT FK_Medikament
  FOREIGN KEY (Medikament) REFERENCES Medikament(ID);
\end{minted}
\end{bAntwort}

%%
% b)
%%

\item Schreiben Sie eine SQL-Anweisung, welche sowohl den \a{Namen} als
auch die \a{ID} von \a{Personen} und \a{Medikamenten} ausgibt, bei denen
die Person das jeweilige Medikament nimmt.

\begin{bAntwort}
\begin{minted}{sql}
SELECT DISTINCT
  p.ID as Personen_ID,
  p.Name as Personen_Name,
  m.ID as Medikamenten_ID,
  m.Name as Medikamenten_Name
FROM Person p, Medikament m, nimmt n
WHERE
  n.Person = p.ID AND
  n.Medikament = m.ID;
\end{minted}
\end{bAntwort}

%%
% c)
%%

\item Schreiben Sie eine SQL-Anweisung, welche die ID und den Namen der
Medikamente mit den niedrigsten Kosten je Hersteller bestimmt.

\begin{bAntwort}
\begin{minted}{sql}
SELECT m.ID, m.Name
FROM Medikament m, Medikament n
WHERE
  m.produziert_von = n.produziert_von AND
  m.Kosten >= n.Kosten
GROUP BY m.ID, m.Name
HAVING COUNT(*) <= 1;
\end{minted}
\end{bAntwort}

%%
% d)
%%

\item Schreiben Sie eine SQL-Anweisung, die die Anzahl aller Personen
ermittelt, die ein Medikament genommen haben, gegen welches sie eine
Unverträglichkeit entwickelt haben.

\begin{bAntwort}
\begin{minted}{sql}
SELECT COUNT(DISTINCT p.ID)
FROM
  Person p,
  nimmt n,
  hat_Unverträglichkeit_gegen u
WHERE
  p.ID = n.Person AND
  u.Medikament = n.Medikament;
\end{minted}
\end{bAntwort}

%%
%
%%

\item Schreiben Sie eine SQL-Anweisung, die die ID und den Namen
derjenigen Personen ermittelt, die weder ein Medikament mit dem
Wirkstoff Paracetamol noch ein Medikament mit dem Wirkstoff Ibuprofen
genommen haben.

\begin{bAntwort}
\begin{minted}{sql}
SELECT ID, Name FROM Person
EXCEPT
SELECT p.ID, p.Name
FROM Person p, nimmt n, Medikament m
WHERE
  p.ID = n.Person AND
  n.Medikament = m.ID AND
  m.Wirkstoff IN ('Ibuprofen', 'Paracetamol');
\end{minted}
\end{bAntwort}

%%
%
%%

\item Schreiben Sie eine SQL-Anweisung, welche die Herstellernamen und
die Anzahl der bekannten Unverträglichkeiten gegen Medikamente dieses
Herstellers ermittelt. Das Ergebnis soll aufsteigend nach der Anzahl der
bekannten Unverträglichkeiten sortiert werden.

\begin{bAntwort}
\begin{minted}{sql}
SELECT p.Name, (
SELECT COUNT(h.ID)
  FROM Hersteller h, Medikament m, hat_Unverträglichkeit_gegen u
  WHERE
    m.produziert_von = h.ID AND
    u.Medikament = m.ID AND
    h.ID = p.ID
) AS Unverträglichkeiten
FROM Hersteller p
ORDER BY Unverträglichkeiten ASC;
\end{minted}
\end{bAntwort}

%%
%
%%

\item Formulieren Sie eine Anfrage in relationaler Algebra, welche die
Wohnorte aller Personen bestimmt, welche ein Medikament mit dem
Wirkstoff Paracetamol nehmen oder genommen haben. Die Lösung kann als
Baum- oder als Term-Schreibweise angegeben werden.
\index{Relationale Algebra}

\begin{bAntwort}
$\pi_{\text{Person.Wohnort}} \left(
  \sigma_{\text{Medikament.Wirkstoff }= '\text{Paracetamol}'}
  (
    \text{Person}
    \bowtie_{\text{Person.ID} = \text{nimmt.Person}}
    \text{nimmt}
    \bowtie_{\text{nimmt.Medikament} = \text{Medikament.ID}}
    \text{Medikament}
  )
\right)$
\end{bAntwort}

%%
%
%%

\item Formulieren Sie eine Anfrage in relationaler Algebra, welche die
Namen aller Personen bestimmt, die von allen bekannten Herstellern,
deren Standort München ist, Medikamente nehmen oder genommen haben. Die
Lösung kann als Baum- oder als Term-Schreibweise angegeben werden.

\begin{bAntwort}
\begin{bAntwort}
$\pi_{\text{Person.Name}} \left(
  \sigma_{\text{Hersteller.Standort }= '\text{München}'}
  (
    (
      \text{Person}
      \bowtie_{\text{Person.ID} = \text{nimmt.Person}}
      \text{nimmt}
    )
    \bowtie_{\text{nimmt.Medikament} = \text{Medikament.ID}}
    (
      \text{Medikament}
      \bowtie_{\text{Medikament.produziert\_von} = \text{Hersteller.ID}}
      \text{Hersteller}
    )
  )
\right)$
\end{bAntwort}
\end{bAntwort}

\end{enumerate}
\end{document}