\documentclass{bschlangaul-aufgabe}
\bLadePakete{er,rmodell,syntax}
\begin{document}
\bAufgabenMetadaten{
  Titel = {Aufgabe 1},
  Thematik = {Einwohnermeldeamt},
  Referenz = 66116-2020-F.T1-TA2-A1,
  RelativerPfad = Examen/66116/2020/03/Thema-1/Teilaufgabe-2/Aufgabe-1.tex,
  ZitatSchluessel = examen:66116:2020:03,
  BearbeitungsStand = mit Lösung,
  Korrektheit = unbekannt,
  Ueberprueft = {unbekannt},
  Stichwoerter = {Entity-Relation-Modell},
  EinzelpruefungsNr = 66116,
  Jahr = 2020,
  Monat = 03,
  ThemaNr = 1,
  TeilaufgabeNr = 2,
  AufgabeNr = 1,
}

Gegeben sei folgendes ER-Diagramm:
\index{Entity-Relation-Modell}
\footcite{examen:66116:2020:03}

\begin{enumerate}

%%
% a)
%%

\item Übernehmen Sie das ER-Diagramm auf Ihre Bearbeitung und ergänzen
Sie die Funktionalitätsangaben im Diagramm.

\begin{center}
\begin{tikzpicture}[er2,scale=0.7,transform shape]
% Menschen
\node[entity] (Menschen) {Menschen};
\node[attribute,above left=1cm of Menschen] {\key{Steuernummer}} edge (Menschen);
\node[attribute,above right=1cm of Menschen] {Vorname} edge (Menschen);
\node[attribute,above=1cm of Menschen] {Name} edge (Menschen);

\node[relationship,left=1cm of Menschen] (verheiratet) {verheiratet\_mit};
\node[relationship,right=1cm of Menschen] (befreundet) {befreundet\_mit};

\draw (verheiratet.north) edge[auto] node{$\bigcirc$} (Menschen);
\draw (verheiratet.south) edge[auto,swap] node{$\bigcirc$} (Menschen);

\draw (befreundet.north) edge[auto,swap] node{$\bigcirc$} (Menschen);
\draw (befreundet.south) edge[auto] node{$\bigcirc$} (Menschen);

\node[relationship,below=1cm of Menschen] (geboren) {geboren}
  edge[auto] node{$\bigcirc$} (Menschen);
\node[attribute,right=1cm of geboren] {Datum}
  edge (geboren);

% Orte
\node[entity,below=3cm of Menschen] (Orte) {Orte}
 edge[auto] node{$\bigcirc$} (geboren);
\node[attribute,below left=1cm of Orte] {Einwohneranzahl} edge (Orte);
\node[attribute,left=1cm of Orte] {\key{Bundesland}} edge (Orte);
\node[attribute,above left=1cm of Orte] {\key{Name}} edge (Orte);
\end{tikzpicture}
\end{center}

\begin{bAntwort}
\begin{center}
\begin{tikzpicture}[er2,scale=0.7,transform shape]
% Menschen
\node[entity] (Menschen) {Menschen};
\node[attribute,above left=1cm of Menschen] {\key{Steuernummer}} edge (Menschen);
\node[attribute,above=1cm of Menschen] {Vornamen} edge (Menschen);
\node[attribute,above right=1cm of Menschen] {Name} edge (Menschen);

\node[relationship,left=1cm of Menschen] (verheiratet) {verheiratet\_mit};
\node[relationship,right=1cm of Menschen] (befreundet) {befreundet\_mit};

\draw (verheiratet.north) edge[auto] node{1} (Menschen);
\draw (verheiratet.south) edge[auto,swap] node{1} (Menschen);

\draw (befreundet.north) edge[auto,swap] node{n} (Menschen);
\draw (befreundet.south) edge[auto] node{m} (Menschen);

\node[relationship,below=1cm of Menschen] (geboren) {geboren}
  edge[auto] node{n} (Menschen);
\node[attribute,right=1cm of geboren] {Datum}
  edge (geboren);

% Orte
\node[entity,below=3cm of Menschen] (Orte) {Orte}
 edge[auto] node{1} (geboren);
\node[attribute,below left=1cm of Orte] {Einwohneranzahl} edge (Orte);
\node[attribute,left=1cm of Orte] {\key{Bundesland}} edge (Orte);
\node[attribute,above left=1cm of Orte] {\key{Name}} edge (Orte);
\end{tikzpicture}
\end{center}
\end{bAntwort}

%%
% b)
%%

\item Übersetzen Sie das ER-Diagramm in ein relationales Schema. -
Datentypen müssen nicht angegeben werden.

\begin{bAntwort}

\begin{bRelationenModell}
Menschen(\bPrimaer{Steuernummer}, Name, Vorname)

Orte(\bPrimaer{Name}, \bPrimaer{Bundesland}, Einwohneranzahl)

verheiratet\_mit(\bFremd{Mensch[Menschen]}, \bFremd{Ehepartner[Menschen]})

befreundet\_mit(\bFremd{Mensch[Menschen]}, \bFremd{Freund[Menschen]})

geboren(Datum, \bFremd{Steuernummer}, \bFremd{Geburtsort[Orte]}, \bFremd{Geburtsbundesland[Orte]})

\end{bRelationenModell}
\end{bAntwort}

%%
% c)
%%

\item Verfeinern Sie das Schema aus Teilaufgabe b) indem Sie die
Relationen zusammenfassen.

\begin{bAntwort}
\begin{bRelationenModell}
Menschen(%
  \bPrimaer{Steuernummer},
  Name,
  Vorname,
  \bFremd{Ehepartner[Menschen]},
  Geburtsdatum,
  \bFremd{Geburtsort[Orte]},
  \bFremd{Geburtsbundesland[Orte]}%
)

Orte(\bPrimaer{Name}, \bPrimaer{Bundesland}, Einwohneranzahl)

befreundet\_mit(\bFremd{Mensch[Menschen]}, \bFremd{Freund[Menschen]})
\end{bRelationenModell}
\end{bAntwort}

%%
% d)
%%

\item Geben Sie sinnvolle SQL Datentypen für Ihr verfeinertes Schema an.

\begin{bAntwort}
\begin{minted}{sql}
CREATE TABLE Menschen (
  Steuernummer BIGINT PRIMARY KEY,
  Name VARCHAR(30),
  Vorname VARCHAR(30),
  Ehepartner BIGINT REFERENCES Steuernummer,
  Geburtsdatum DATE,
  Geburtsort VARCHAR(30) REFERENCES Orte(Name),
  Geburtsbundesland VARCHAR(30) REFERENCES Orte(Bundesland)
);

CREATE TABLE Orte (
  Name VARCHAR(30),
  Bundesland VARCHAR(30),
  Einwohneranzahl INTEGER,
  PRIMARY KEY (Name, Bundesland)
);

CREATE TABLE befreundet_mit (
  Mensch BIGINT REFERENCES Mensch(Steuernummer),
  Freund BIGINT REFERENCES Mensch(Steuernummer),
  PRIMARY KEY (Mensch, Freund)
);
\end{minted}
\end{bAntwort}

\end{enumerate}
\end{document}