\documentclass[12pt]{scrartcl}

\usepackage[top=3cm,bottom=3cm]{geometry} % Seitenränder anpassen
\usepackage[ngerman]{babel}    % deutsche Sprache
\usepackage[utf8]{inputenc}    % UTF-8 Encoding der .tex Datei

% Pakete und Libraries für ER-Diagramme
\usepackage{tikz,tikz-er2}
\usetikzlibrary{calc,positioning}

\title{NAWI Datenbank}
\date{}\author{}

\begin{document}

\maketitle

\section*{Anforderungen}

\begin{itemize}
  
\item Die NAWI ist in Fachbereiche gegliedert.  Jeder Fachbereich hat
  einen Namen, eine Nummer und einen Mitarbeiter, der den Fachbereich
  führt.  Die Fachbereichsleitung beginnt ab einem bestimmten
  Datum. Ein Fachbereich kann an unterschiedlichen Standorten
  untergebracht sein.
  
  
\item Jeder Fachbereich ist für eine Anzahl von Projekten
  verantwortlich.  Jedes Projekt hat einen eindeutigen Namen, eine
  eindeutige Nummer und wird an einem einzigen Standort durchgeführt.
  
  
\item Von jedem Mitarbeiter erfassen wir Sozialversicherungsnummer,
  Adresse, Lohn, Geschlecht und Geburtsdatum.  Jeder Mitarbeiter
  arbeitet für nur einen Fachbereich, kann aber an mehreren Projekten
  arbeiten.  Die Anzahl der Wochenstunden pro Projekt werden erfasst.
  Jeder Mitarbeiter hat einen direkten Vorgesetzten.
  
  
\item Jeder Mitarbeiter kann eine Anzahl von abhängigen Personen
  haben.  Von jeder abhängigen Person erfassen wir Name, Geschlecht,
  Geburtstag und Art der Beziehung.
  
\end{itemize}

\newpage

\section*{ER-Diagramm}

\begin{center}
  \scalebox{0.6}{
    \begin{tikzpicture}[node distance=1.5cm]
      
      \node[entity] (employee) {Angestellte};
      \node[attribute] (ssn) [above left  = 2cm of employee] {\key{SVN}}
      edge (employee);
      \node[attribute] (bdate) [above = of ssn] {GDatum} edge (employee);
      \node[attribute] (name) [right = 0.1cm of bdate] {Name} edge (employee);
      \node[attribute] (fname) [above left= of name] {VName} edge (name);
      \node[attribute] (lname) [above right = of name] {NName} edge (name);         
      \node[attribute] (adress) [right = 0.1cm of name] {Adressee} edge (employee);
      \node[attribute] (salary) [right = 0.1cm of adress] {Lohn} edge (employee);
      \node[attribute] (sex) [below = of salary] {Geschlecht} edge (employee);

      \node[relationship] (supervision) [below left = 3cm of employee] {führen};
      \draw[link] (employee.-100) -- node[pos=0.6,auto] {Unterstellter}
      node[pos=1,auto] {N} (supervision.0);
      \draw[link] (employee.-180) -- node[pos=0.6,auto,swap] {Vorgesetzter}
      node[pos=1,auto,swap] {1} (supervision.90);

      \node[relationship] (worksfor) [below right =  1cm of sex] {arbeitenFür}
      edge [total] node[pos=0.1,auto,swap] {N} (employee); 
      \node[relationship] (manages) [below  =  of worksfor] {leiten}
      edge node[pos=0.1,auto,swap] {1} (employee); 
      \node[attribute] (startdate) [above  = 0.5cm of manages] {StartDatum}
      edge (manages);

      \node[entity] (department) [right = 10 cm of employee] {Fachbereiche}
      edge [total]  node[pos=0.9,auto,swap] {1} (worksfor)
      edge [total] node[pos=0.9,auto,swap] {1} (manages);
      \node[attribute] (dname) [above left  = of department] {\key{Name}}
      edge (department);
      \node[multi attribute] (dnumber) [above = of department] {Standorte}
      edge (department);
      \node[attribute] (dlocation) [above right = of department] {\key{Nummer}}
      edge (department);
      \node[derived attribute] (nemployees) [right = .3cm of department]
      {AnzAngestellte}  edge (department);

      \node[relationship] (controls) [below = 0.7cm of department] {kontrollieren}
      edge node[pos=0.1,auto,swap] {1} (department);
      \node[relationship] (workson) [below = 0.2cm of manages] {arbeitenAn}
      edge [total] node[pos=0.1,auto,swap] {M} (employee);
      \node[attribute] (hours) [above right = 0.4cm of workson] {Stunden}
      edge (workson);
      \node[entity] (project) [below = 0.7cm of controls] {Projekte}
      edge node[pos=0.9,auto,swap] {N} (controls)
      edge [total] node[pos=0.9,auto,swap] {N} (workson);
      \node[attribute] (pname) [below = 0.4cm of project] {\key{Name}}
      edge (project);
      \node[attribute] (pnumber) [right = 0.1cm of pname] {\key{Nummer}}
      edge (project);
      \node[attribute] (plocation) [above right = 0.1cm of pnumber] {Standort}
      edge (project);

      \node[ident relationship] (dependentsof) [left = 0.8cm of workson]
      {habenAbhängige} edge node[pos=0.1,auto,swap] {1} (employee);
      \node[weak entity] (dependent) [below = 1cm of dependentsof] {Abhängige}
      edge [total] node[pos=0.9,auto,swap] {N} (dependentsof);
      \node[attribute] (dename) [below left = 0.6cm of dependent]
      {\discriminator{Name}} edge (dependent);            
      \node[attribute] (desex) [right = 0.1cm of dename] {Geschlecht} edge (dependent);
      \node[attribute] (debirthdate) [right = 0.1cm of desex] {GDatum}
      edge (dependent);
      \node[attribute] (derelationship) [right = 0.1cm of debirthdate]
      {Beziehung} edge (dependent);

    \end{tikzpicture}
  }
\end{center}

\section*{Relationales Schema}

\begin{center}
  \scalebox{.75}{
    \begin{tikzpicture}[node distance=0cm]
      \tikzstyle{grayBox} = [anchor=north west,top color=gray!30,
      bottom color=gray!30, draw=black,minimum width = 2cm,minimum
      height=0.7cm]        
      \tikzstyle{relName} = [anchor=west,font=\sf \large]
      
      \node[relName] (employee) {Angestellte};
      \node[below=of employee.south west,grayBox] (fname) {VName};
      \node[grayBox] (lname) [right=of fname] {NName};
      \node[grayBox] (ssn) [right=of lname] {\key{SVN}};
      \node[grayBox] (bdate) [right=of ssn] {GDatum};
      \node[grayBox] (adress) [right=of bdate] {Adresse};
      \node[grayBox] (sex) [right=of adress] {Geschlecht};
      \node[grayBox] (salary) [right=of sex] {Lohn};
      \node[grayBox] (superssn) [right=of salary] {VorgSVN};
      \node[grayBox] (dno) [right=of superssn] {FNummer};
      
      \node[below=2.1cm of employee.west,relName] (department) {Fachbereiche}; 
      \node[below=of department.south west,grayBox] (dname) {FName};
      \node[grayBox] (dnumber) [right=of dname] {\key{FNummer}};
      \node[grayBox] (mgrssn) [right=of dnumber] {LeiterSVN};
      \node[grayBox] (mgrstartdate) [right=of mgrssn] {StartDatum};
      
      \node[below=2.1cm of department.west,relName] (deptlocations)  {FBStandorte}; 
      \node[below=of deptlocations.south west,grayBox] (dldnumber) {\key{FNummer}};
      \node[grayBox] (dlocation) [right=of dldnumber] {\key{Standort}};
      
      \node[below=2.1cm of deptlocations.west,relName] (project) {Projekte};
      \node[below=of project.south west,grayBox] (pname) {PName};
      \node[grayBox] (pnumber) [right=of pname] {\key{PNummer}};
      \node[grayBox] (plocation) [right=of pnumber] {PStandort};
      \node[grayBox] (dnum) [right=of plocation] {FNummer};
      
      \node[below=2.1cm of project.west,relName] (workson) {arbeitenAn};
      \node[below=of workson.south west,grayBox] (essn) {\key{AngSVN}};
      \node[grayBox] (pno) [right=of essn] {\key{PNummer}};
      \node[grayBox] (hours) [right=of pno] {Stunden};
      
      \node[below=2.1cm of workson.west,relName] (dependent) {Abhängige};
      \node[below=of dependent.south west,grayBox] (dessn) {\key{AngSVN}};
      \node[grayBox] (depname) [right=of dessn] {\key{AbhName}};
      \node[grayBox] (sex) [right=of depname] {Geschlecht};
      \node[grayBox] (bdate) [right=of sex] {GDatum};
      \node[grayBox] (relationship) [right=of bdate] {Beziehung};
      
      \draw (superssn) -- ++(0,-0.8) --($(ssn)-(-0.2,0.8)$) [->] --
      ($(ssn)+(0.2cm,-0.35cm)$);
      
      \draw let \p1 = (dno), \p2 = (dnumber) in (dno) -- ++(0,\y2) 
      [->]-| ($(dnumber)+(0.15cm,-0.35cm)$);
      
      \draw let \p1 = (ssn), \p2 = (mgrssn) in (mgrssn) -- ++(0,-\y1) [->]-|
      ($(ssn)+(-0.4cm,-0.35cm)$);
      
      \draw let \p1 = (dldnumber), \p2 = (dnumber) in (dldnumber) -- ++(0,-1) --
      ++(5,0) -- ++(0,\y2-\y1)  [->]-| ($(dnumber)+(-0.15cm,-0.35cm)$);
      
      \draw let \p1 = (dnum), \p2 = (dnumber) in (dnum) -- ++(0,-0.5) -- ++(2,0)
      -- ++($(0,\y2-\y1)+(0,-0.3)$) [->]-| ($(dnumber)+(0cm,-0.35cm)$);
      
      \draw let \p1 = (essn), \p2 = (ssn) in (essn) -- ++(0,-1) -- ++(11,0) --
      ++($(0,\y2-\y1)+(0,-0.2)$)  [->] -| ($(ssn)+(-0.2cm,-0.35cm)$) ;
      
      \draw (pno) [->]-- (pnumber);
      
      \draw let \p1 = (dessn), \p2 = (ssn) in (dessn) -- ++(0,-1) -- ++(12.5,0)
      -- ++ (0,\y2-\y1) [->] -| (ssn);
      
    \end{tikzpicture}
  }
\end{center}

\end{document}
