\documentclass{bschlangaul-aufgabe}
\bLadePakete{sql,mathe,rmodell}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 2},
  Thematik = {Polizei},
  Referenz = 66116-2016-F.T1-TA1-A2,
  RelativerPfad = Examen/66116/2016/03/Thema-1/Teilaufgabe-1/Aufgabe-2.tex,
  ZitatSchluessel = examen:66116:2016:03,
  BearbeitungsStand = mit Lösung,
  Korrektheit = unbekannt,
  Ueberprueft = {unbekannt},
  Stichwoerter = {Entity-Relation-Modell, SQL mit Übungsdatenbank, Relationale Algebra, SQL, VIEW, WITH, UNION},
  EinzelpruefungsNr = 66116,
  Jahr = 2016,
  Monat = 03,
  ThemaNr = 1,
  TeilaufgabeNr = 1,
  AufgabeNr = 2,
}

\noindent
Gehen\index{Entity-Relation-Modell}
\footcite{examen:66116:2016:03} Sie dabei von dem dazugehörigen relationalen Schema aus:
\footcite[Aufgabe 4]{db:pu:3}

\begin{bRelationenModell}
\bRelationMenge{Polizist}{\bPrimaer{PersNr}, DSID, Vorname, Nachname, Dienstgrad, Gehalt}
\bRelationMenge{Dienststelle}{\bPrimaer{DSID}, Name, Strasse, HausNr, Stadt}
\bRelationMenge{Fall}{\bPrimaer{AkZ}, Titel, Beschreibung, Status}
\bRelationMenge{Arbeitet\_An}{\bPrimaer{PersNr}, AkZ, Von, Bis}
\bRelationMenge{Vorgesetzte}{\bPrimaer{PersNr}, PersNr, Vorgesetzter}
\end{bRelationenModell}

\begin{bAdditum}[Übungsdatenbank]
% Datenbankname: polizei
\begin{minted}{sql}
CREATE TABLE Fall (
  AkZ VARCHAR (30) PRIMARY KEY,
  Titel VARCHAR (30),
  Beschreibung VARCHAR (50),
  Status VARCHAR (30)
);

CREATE TABLE Dienststelle (
  DSID INTEGER PRIMARY KEY,
  Name VARCHAR (50),
  Strasse VARCHAR (30),
  HausNr VARCHAR (30),
  Stadt VARCHAR (30)
);

CREATE TABLE Polizist (
  PersNr INTEGER Primary KEY,
  DSID INTEGER REFERENCES Dienststelle(DSID),
  Vorname VARCHAR(30),
  Nachname VARCHAR(30),
  Dienstgrad VARCHAR(30),
  Gehalt INT
);

CREATE TABLE Arbeitet_An (
  PersNr INTEGER REFERENCES Polizist(PersNr),
  AkZ VARCHAR(30) REFERENCES Fall(AkZ),
  Von DATE,
  Bis DATE,
  PRIMARY KEY (PersNr, AkZ)
);

CREATE TABLE Vorgesetzte (
  PersNr INTEGER REFERENCES Polizist(PersNr),
  PersNr_Vorgesetzter INTEGER REFERENCES Polizist(PersNr),
  PRIMARY KEY (PersNr, PersNr_Vorgesetzter)
);

INSERT INTO Dienststelle VALUES
  (10, 'Dienstelle München (Marienplatz)', NULL, NULL, 'München'),
  (11, 'Dienststelle Nürnberg (Mitte)', NULL, NULL, 'Nürnberg'),
  (12, 'Dieststelle Augsburg Ost', NULL, NULL, 'Augsburg');

INSERT INTO Polizist VALUES
  (1, 10, 'Hans', 'Müller', 'Polizeimeister', 40000),
  (2, 11, 'Josef', 'Fischer', 'Polizeihauptmeister', 45000),
  (3, 10, 'Andreas', 'Schmidt', 'Polizeikommisar', 50000),
  (4, 12, 'Stefan', 'Hoffmann', 'Polizeidirektor', 70000),
  (5, 11, 'Sebastian', 'Wagner', 'Polizeioberkommisar', 60000);

INSERT INTO Fall VALUES
  ('VR30932', 'Mord im Fussballstadion', 'Toter BVB-Fan', 'bearbeitet'),
  ('XZ1508', 'Steuerhinterziehung', 'Durchsuchung eines Hauses', 'bearbeitet');

INSERT INTO Arbeitet_An
  (PersNr, AkZ, Von, Bis)
VALUES
  (1, 'VR30932', '2011-02-15', '2011-06-06'),
  (2, 'VR30932', '2011-02-15', '2011-06-06'),
  (2, 'XZ1508', '2012-02-13', '2012-02-14');

INSERT INTO Vorgesetzte
  (PersNr, PersNr_Vorgesetzter)
VALUES
  (1, 3),
  (1, 4),
  (2, 5),
  (2, 4);
\end{minted}
\index{SQL mit Übungsdatenbank}
\end{bAdditum}

\noindent
Gegeben sei folgendes ER-Modell, welches Polizisten, deren Dienststelle
und Fälle, an denen sie arbeiten, speichert:

\begin{enumerate}

%%
% (a)
%%

\item Formulieren Sie eine Anfrage in relationaler
Algebra\index{Relationale Algebra}, welche den \emph{Vornamen} und
\emph{Nachnamen} von Polizisten zurückgibt, deren Dienstgrad
\emph{„Polizeikommissar“} ist und die mehr als 1500 Euro verdienen.

\begin{bAntwort}
$\pi_{\text{Vorname,Nachname}}(
  \sigma_{
    \text{Dienstgrad} = \mlq \text{Polizeikommissar} \mrq
      \land
    \text{Gehalt} > 1500
  }(\text{Polizist})
)$
\end{bAntwort}

%%
% (b)
%%

\item Formulieren Sie eine Anfrage in relationaler Algebra, welche die
\emph{Titel} der \emph{Fälle} ausgibt, die von \emph{Polizisten} mit dem
\emph{Nachnamen} \emph{„Mayer“} bearbeitet wurden.

\begin{bAntwort}
$
\pi_{\text{Titel}}(
  \sigma_{\text{Nachname} = \mlq \text{Mayer} \mrq}(\text{Polizist})
  \bowtie_{\text{PersNr}}
  \text{Arbeitet\_An}
  \bowtie_{\text{AkZ}}
  \text{Fall}
)
$
\end{bAntwort}

%%
% (c)
%%

\item Formulieren Sie eine SQL-Anfrage\index{SQL}, welche die Anzahl der
Polizisten ausgibt, die in der Stadt \emph{„München“} arbeiten und mit
Nachnamen \emph{„Schmidt“} heißen.

\begin{bAntwort}
\begin{minted}{sql}
SELECT COUNT(*) AS Anzahl_Polizisten
FROM Polizist p, Dienststelle d
WHERE
  p.DSID = d.DSID AND
  d.Stadt = 'München' AND
  p.Nachname = 'Schmidt';
\end{minted}

\begin{verbatim}
 anzahl_polizisten
-------------------
                 1
(1 row)
\end{verbatim}
\end{bAntwort}

%%
% (d)
%%

\item Formulieren Sie eine SQL-Anfrage, welche die \emph{Namen} der
\emph{Dienststellen} ausgibt, die am \emph{14.02.2012} an dem Fall mit
dem \emph{XZ1508} beteiligt waren. Ordnen Sie die Ergebnismenge
alphabetisch (aufsteigend) und achten Sie darauf, dass keine Duplikate
enthalten sind.

\begin{bAntwort}
\begin{minted}{sql}
SELECT DISTINCT d.Name
FROM Dienststelle d, Polizist p, Arbeitet_An a
WHERE
  a.AkZ = 'XZ1508' AND
  p.PersNr = a.PersNr AND
  p.DSID = d.DSID AND
  a.Von <= '2012-02-14' AND
  a.Bis >= '2012-02-14'
ORDER BY d.Name ASC;
\end{minted}

\begin{verbatim}
             name
-------------------------------
 Dienststelle Nürnberg (Mitte)
(1 row)
\end{verbatim}
\end{bAntwort}

%%
% (e)
%%

\item Definieren Sie die View \emph{„Erstrebenswerte Dienstgrade“},
welche Dienstgrade enthalten soll, die in \emph{München} mit
durchschnittlich mehr als \emph{2500} Euro besoldet werden.
\index{VIEW}

\begin{bAntwort}
\begin{minted}{sql}
CREATE VIEW ErstrebenswerteDienstgrade AS (
  SELECT DISTINCT p.Dienstgrad
  FROM Polizist p, Dienststelle d
  WHERE
    p.DSID = d.DSID AND
    d.Stadt = 'München'
  GROUP BY Dienstgrad
  HAVING (AVG(Gehalt) > 2500)
);

SELECT * FROM ErstrebenswerteDienstgrade;
\end{minted}

\begin{verbatim}
   dienstgrad
-----------------
 Polizeikommisar
 Polizeimeister
(2 rows)
\end{verbatim}
\end{bAntwort}

%%
% (f)
%%

\item Formulieren Sie eine SQL-Anfrage, welche \emph{Vorname},
\emph{Nachname} und \emph{Dienstgrad} von \emph{Polizisten} mit
\emph{Vorname}, \emph{Nachname} und \emph{Dienstgrad} ihrer
\emph{Vorgesetzten} als ein Ergebnis-Tupel ausgibt (siehe
Beispiel-Tabelle). Dabei sind nur \emph{Polizisten} zu selektieren, die
an Fällen gearbeitet haben, deren Titel den Ausdruck „Fussball“
beinhalten. An \emph{Vorgesetzte} sind keine Bedingungen gebunden.
Achten Sie darauf, dass Sie nicht nur direkte Vorgesetzte, sondern alle
Vorgesetzte innerhalb der Vorgesetzten-Hierarchie betrachten. Ordnen Sie
ihre Ergebnismenge alphabetisch (absteigend) nach Nachnamen des
Polizisten.\index{WITH}\index{UNION}

Hinweis: Sie dürfen Views verwenden, um Teilergebnisse auszudrücken.

\begin{bAntwort}
Vorarbeiten:
\begin{minted}{sql}
  SELECT p.Vorname, p.Nachname
  FROM Polizist p, Arbeitet_An a, Fall f
  WHERE
    p.PersNr = a.PersNr AND
    a.AkZ = f.Akz AND
    f.Titel LIKE '%Fussball%';
\end{minted}

\begin{verbatim}
 vorname | nachname
---------+----------
 Hans    | Müller
 Josef   | Fischer
(2 rows)
\end{verbatim}

\bPseudoUeberschrift{Lösungsansatz 1}

\begin{minted}{sql}
WITH RECURSIVE Fussball_Vorgesetzte (PersNr, VN, NN, DG, PN_VG, VN_VG, NN_VG, DG_VG) AS
(
  SELECT
    p1.PersNr,
    p1.Vorname AS VN,
    p1.Nachname AS NN,
    p1.Dienstgrad AS DG,
    p2.PersNr AS PN_VG,
    p2.Vorname AS VN_VG,
    p2.Nachname AS NN_VG,
    p2.Dienstgrad AS DG_VG
  FROM Polizist p1, Fall f, Arbeitet_An a, Vorgesetzte v
  LEFT JOIN Polizist p2 ON v.PersNr_Vorgesetzter = p2.PersNr
  WHERE
    p1.PersNr = a.PersNr AND
    a.AkZ = f.Akz AND
    f.Titel LIKE '%Fussball%' AND
    p1.PersNr = v.PersNr

  UNION ALL

  SELECT
    m.PersNr,
    m.VN AS VN,
    m.NN AS NN,
    m.DG AS DG,
    p.PersNr AS PN_VG,
    p.Vorname AS VN_VG,
    p.Nachname AS NN_VG,
    p.Dienstgrad AS DG_VG
  FROM Fussball_Vorgesetzte m, Vorgesetzte v
  LEFT JOIN Polizist p ON v.PersNr_Vorgesetzter = p.PersNr
  WHERE m.PN_VG = v.PersNr
)

SELECT VN, NN, DG, VN_VG, NN_VG, DG_VG
FROM Fussball_Vorgesetzte
ORDER BY NN DESC;
\end{minted}

\begin{verbatim}
  vn   |   nn    |         dg          |   vn_vg   |  nn_vg   |        dg_vg
-------+---------+---------------------+-----------+----------+---------------------
 Hans  | Müller  | Polizeimeister      | Andreas   | Schmidt  | Polizeikommisar
 Hans  | Müller  | Polizeimeister      | Stefan    | Hoffmann | Polizeidirektor
 Josef | Fischer | Polizeihauptmeister | Stefan    | Hoffmann | Polizeidirektor
 Josef | Fischer | Polizeihauptmeister | Sebastian | Wagner   | Polizeioberkommisar
(4 rows)
\end{verbatim}

\bPseudoUeberschrift{Lösungsansatz 2}

\begin{minted}{sql}
CREATE VIEW naechste_Vorgesetzte AS
  SELECT
    p.PersNR,
    p.Vorname,
    p.Nachname,
    p.Dienstgrad,
    v.PersNr_Vorgesetzter AS Vorgesetzter
  FROM Polizist p LEFT JOIN Vorgesetzte v
  ON p.PersNr = v.PersNr;

WITH RECURSIVE Fussball_Vorgesetzte (VN, NN, DG, VN_VG, NN_VG, DG_VG) AS (
  SELECT
    x.Vorname AS VN,
    x.Nachname AS NN,
    x.Dienstgrad AS DG,
    y.Vorname AS VN_VG,
    y.Nachname AS NN_VG,
    y.Dienstgrad AS DG_VG
  FROM naechste_Vorgesetzte x, Fall f, Arbeitet_An a,
  naechste_Vorgesetzte y
  WHERE
    f.Titel LIKE '%Fussball%' AND
    f.AkZ = a.AkZ AND
    x.PersNr = a.PersNr AND
    x.Vorgesetzter = y.PersNr
  UNION ALL
  SELECT
    a.Vorname AS VN,
    a.Nachname AS NN,
    a.Dienstgrad AS DB,
    Vorname AS VN_VG,
    Nachname AS NN_VG,
    Dienstgrad AS DG_VG
  FROM naechste_Vorgesetzte a INNER JOIN Fussball_Vorgesetzte
  ON a.Vorgesetzter = PersNr
)

SELECT *
FROM Fussball_Vorgesetzte;
\end{minted}

\begin{verbatim}
  vn   |   nn    |         dg          |   vn_vg   |  nn_vg   |        dg_vg
-------+---------+---------------------+-----------+----------+---------------------
 Hans  | Müller  | Polizeimeister      | Andreas   | Schmidt  | Polizeikommisar
 Hans  | Müller  | Polizeimeister      | Stefan    | Hoffmann | Polizeidirektor
 Josef | Fischer | Polizeihauptmeister | Stefan    | Hoffmann | Polizeidirektor
 Josef | Fischer | Polizeihauptmeister | Sebastian | Wagner   | Polizeioberkommisar
(4 rows)
\end{verbatim}

\end{bAntwort}
\end{enumerate}

\end{document}