\documentclass[12pt]{scrartcl}

\usepackage[top=1cm]{geometry} % Seitenrand oben verkleinern
\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
\vspace{-3cm}

\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}
